r

#include "stdafx.h"
#include "CExcel.h"
using namespace TypelibExcel;
void CExcel::CreateExcel()
{
COleException pError;


if (!m_excelApp.CreateDispatch("Excel.Application", &pError))
{
pError.ReportError();
return;
}
else
{
m_excelBooks = m_excelApp.get_Workbooks();
m_excelBook  = m_excelBooks.Add(COleVariant((short)TRUE));
m_excelSheet = m_excelBook.get_ActiveSheet();
}
}


void CExcel::OpenExcel(CString fileName)
{        
COleException pError;


if (!m_excelApp.CreateDispatch("Excel.Application", &pError))
{
pError.ReportError();
return;
}
else
{
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
m_excelBooks = m_excelApp.get_Workbooks();
m_excelBook = m_excelBooks.Open(fileName,covOptional, covOptional, covOptional, 
covOptional, covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional, covOptional, covOptional);
m_excelSheet = m_excelBook.get_ActiveSheet();
out_file_name = fileName;
}
}


bool CExcel::SaveExcel()
{
if (out_file_name.GetLength()==0)
{
AfxMessageBox("Excel");
return false;
}
else
{
TRY 
{
if (_access(out_file_name, 0) == 0)
remove(out_file_name);


COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
m_excelBook.SaveAs(COleVariant(out_file_name),  covOptional, covOptional,
covOptional, covOptional, covOptional, 
0,covOptional, covOptional, covOptional, covOptional, covOptional);
}
CATCH (CException, e)
{
return false;
}
END_CATCH
}


return false;
}


void CExcel::CloseExcel()
{
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
m_excelBook.put_Saved(TRUE); 
m_excelBook.Close(COleVariant((short)FALSE), covOptional, covOptional);
m_excelBooks.Close();
m_excelRange.ReleaseDispatch(); 
m_excelSheet.ReleaseDispatch(); 
m_excelSheets.ReleaseDispatch(); 
m_excelBook.ReleaseDispatch(); 
m_excelBooks.ReleaseDispatch();
m_excelApp.Quit();




m_excelApp.ReleaseDispatch(); 


}




CWorksheet CExcel::GetSheet(CString SheetName)
{
bool has_the_sheet = false;


CWorksheets sheets = m_excelBook.get_Worksheets();
for (int si=1; si<=sheets.get_Count(); si++)
{
CWorksheet sheet = sheets.get_Item(COleVariant((long)si));
if (sheet.get_Name().CompareNoCase(SheetName) == 0)
{
has_the_sheet = true;
break;
}
}


if (!has_the_sheet)
{
AfxMessageBox("'");
return NULL;
}


TRY 
{
m_excelSheet = sheets.get_Item(COleVariant(SheetName));
}
CATCH (CException, e)
{
return NULL;
}
END_CATCH


return m_excelSheet;
}




CWorksheet CExcel::GetSheet(int index)
{
CWorksheets sheets = m_excelBook.get_Worksheets();
int sheet_num = sheets.get_Count();
if (index <= 0 || index > sheet_num)
{
char buf[BUFSIZ] = "";
sprintf_s(buf, "%d", sheet_num);
AfxMessageBox(buf);
return NULL;
}


TRY
{
m_excelSheet = sheets.get_Item(COleVariant((long)(index)));
}
CATCH (CException, e)
{
return NULL;
}
END_CATCH


return m_excelSheet;
}




CWorksheet CExcel::AddSheet(CString SheetName)
{
if( SheetName.Trim().GetLength() == 0 )
{
AfxMessageBox("sheet");
return NULL;
}


bool has_the_sheet = false;


CWorksheets sheets = m_excelBook.get_Worksheets();
for (int si=1; si<=sheets.get_Count(); si++)
{
CWorksheet sheet = sheets.get_Item(COleVariant((long)si));
if (sheet.get_Name().Compare(SheetName) == 0)
{
has_the_sheet = true;
break;
}
}


if( has_the_sheet )
{
AfxMessageBox("excelsheet");
return NULL;
}


CWorksheet new_sheet;
TRY 
{
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
new_sheet = sheets.Add(covOptional, covOptional, COleVariant((short)1), covOptional);
new_sheet.put_Name(SheetName);
}
CATCH (CException, e)
{
return NULL;
}
END_CATCH


return new_sheet;
}




int CExcel::GetSheetNum()
{
CWorksheets sheets = m_excelBook.get_Worksheets();
return sheets.get_Count();
}


CString CExcel::GetCell(int ColNum, int RowNum)
{
char buf[BUFSIZ] = "";


//µ¥Ôª¸ñÁкÅÊý×é
CString m_colString[] = {
"",
"A","B","C","D","E",
"F","G","H","I","J",
"K","L","M","N","O",
"P","Q","R","S","T",
"U","V","W","X","Y",
"Z" };


int row = RowNum;
if (ColNum <= 0 || ColNum > 255)
{
AfxMessageBox("tt");
return "";
}


if (ColNum <= 26)
{
sprintf_s(buf, "%s%d", m_colString[ColNum], RowNum);
return CString(buf);
}
else
{
int i0 = 0, i1 = 0;
i0 = (int)(ColNum / 26);
i1 = ColNum % 26;


if (i1 != 0)
{
sprintf_s(buf, "%s%s%d", m_colString[i0], m_colString[i1], row);
return CString(buf);
}
else //Èç¹ûColNum = 52¡¢78¡¢104
{
sprintf_s(buf, "%s%s%d", m_colString[i0-1], m_colString[i1+26], row);
return CString(buf);
}
}
}




void CExcel::SetRange(int ColNum, int RowNum)
{
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
m_excelRange = m_excelSheet.get_Range(COleVariant(GetCell(ColNum, RowNum)), covOptional);
}


void CExcel::SetRange(int StartColNum, int StartRowNum, int EndColNum, int EndRowNum)
{
m_excelRange = m_excelSheet.get_Range(COleVariant(GetCell(StartColNum, StartRowNum)),
COleVariant(GetCell(EndColNum, EndRowNum)));
}




void CExcel::Merge(int startRowIndex, int startColIndex, int endRowIndex, int endColIndex)
{
SetRange(startColIndex, startRowIndex, endColIndex, endRowIndex);
m_excelRange.Merge(COleVariant((short)FALSE));
}




void CExcel::SetCellValue(CString value)
{
if (m_excelRange == NULL) { AfxMessageBox("jghj"); return; }
m_excelRange.put_Value2(COleVariant(value));
}


void CExcel::SetCellValue(int row, int col, CString value)
{
SetRange(col, row);
m_excelRange.put_NumberFormatLocal(COleVariant("@"));
m_excelRange.put_Value2(COleVariant(value));
}


void CExcel::SetCellStringFormat()
{
m_excelRange.put_NumberFormatLocal(COleVariant("@"));
}


void CExcel::SetCellValue( int startRow,int startCol, int endRow, int endCol, CString value )
{
Merge(startRow, startCol, endRow, endCol);
m_excelRange.put_NumberFormatLocal(COleVariant("@"));
m_excelRange.put_Value2(COleVariant(value));
}


CString CExcel::GetCellValue( int row, int col )
{
SetRange(col, row);
return (CString)m_excelRange.get_Text();
}


void CExcel::SetBoldFontAndSize( int size )
{
CFont0 font = m_excelRange.get_Font();
font.put_Bold(COleVariant((short)TRUE));
font.put_Size(COleVariant((short)size));
}




void CExcel::SetAutoFitColumns( int startRow, int startCol, int endRow, int endCol )
{
SetRange(startCol, startRow, endCol, endRow);
CRange cols = m_excelRange.get_EntireColumn();
cols.AutoFit();
}


void CExcel::SetCenterAlign()
{
m_excelRange.put_HorizontalAlignment(COleVariant((short)0));
}