CExcelManager--操作Excel表格的类

#include "StdAfx.h" #include "ExcelManager.h" COleVariant covTrue((short)TRUE), covFalse((short)FALSE), covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); CExcelManager::CExcelManager() { m_bInit = FALSE; m_bStart = FALSE; m_pathName = _T(""); m_bOpenExcel = FALSE; m_bOpenSheet = FALSE; m_bDeleteSheet = FALSE; } CExcelManager::~CExcelManager() { m_range.ReleaseDispatch(); m_worksheet.ReleaseDispatch(); m_worksheets.ReleaseDispatch(); m_workbook.ReleaseDispatch(); m_workbooks.ReleaseDispatch(); } void CExcelManager::InitApp() { // 初始化COM if (!m_bInit) { if (::CoInitialize( NULL ) == E_INVALIDARG) AfxMessageBox(_T("初始化Com失败!")); else m_bInit = TRUE; } // 创建Excel 2000服务器(启动Excel) if (!m_bStart) { if (!m_app.CreateDispatch(_T("Excel.Application"), NULL)) { AfxMessageBox(_T("创建Excel服务失败!")); exit(1); } else m_bStart = TRUE; } } void CExcelManager::CloseSheet() { m_range.ReleaseDispatch(); m_worksheet.ReleaseDispatch(); m_worksheets.ReleaseDispatch(); m_range = NULL; m_worksheet = NULL; m_worksheets = NULL; m_bOpenSheet = FALSE; } int CExcelManager::CloseExcel() { if (!m_bOpenExcel) return RET_excel_is_not_open; m_workbook.SaveCopyAs(COleVariant(m_pathName)); if (m_bDeleteSheet) // after delete sheet function, 2003 format .xls will be update to 2007 format .xlsx { if (m_pathName.Right(1) != _T("x")) // save 2003 format .xls to 2007 format .xlsx { CString oldName= m_pathName; m_pathName += _T("x"); CFile::Rename(oldName, m_pathName); } m_bDeleteSheet = FALSE; } m_workbook.put_Saved(TRUE); // save change m_workbook.Close(covOptional, COleVariant(m_pathName), covFalse); m_workbooks.Close(); m_workbook.ReleaseDispatch(); m_workbooks.ReleaseDispatch(); m_workbook = NULL; m_workbooks = NULL; m_bOpenExcel = FALSE; m_pathName = _T(""); return RET_success_close; } void CExcelManager::ReleaseApp() { if (m_bOpenSheet) { CloseSheet(); } if (m_bOpenExcel) { CloseExcel(); } m_app.Quit(); m_app.ReleaseDispatch(); m_app = NULL; m_bInit = FALSE; m_bStart = FALSE; } // get workbooks, workbook, worksheets in turn int CExcelManager::OpenExcel(CString pathName) { if (!PathFileExists(pathName)) return RET_no_excel; else m_pathName = pathName; // 打开excel文件,利用模板文件建立新文档 m_workbooks.AttachDispatch(m_app.get_Workbooks(), TRUE); LPDISPATCH lpDis = NULL; lpDis = m_workbooks.Add(_variant_t(pathName)); if (lpDis) { m_workbook.AttachDispatch(lpDis); m_worksheets.AttachDispatch(m_workbook.get_Worksheets(), TRUE); // 得到Worksheets m_bOpenExcel = TRUE; return RET_success_open; } else { m_bOpenExcel = FALSE; return RET_fail_open; } } int CExcelManager::DeleteCurSheet() { if (!m_bOpenExcel) return RET_excel_is_not_open; if (!m_bOpenSheet) return RET_sheet_is_not_open; if (m_pathName.Right(1) != _T("x")) // save 2003 format .xls to 2007 format .xlsx m_workbook.Save(); m_worksheet.Delete(); // m_workbook.put_Saved(TRUE); m_bDeleteSheet = TRUE; m_bOpenSheet = FALSE; return RET_success_delete; } int CExcelManager::DeleteSheet(int index) { if (!m_bOpenExcel) return RET_excel_is_not_open; if (index < 1 || index > GetSheetCount()) return RET_outof_sheet_count; CWorksheet worksheet; worksheet.AttachDispatch(m_worksheets.get_Item(_variant_t(long(index))), TRUE); if (m_pathName.Right(1) != _T("x")) // save 2003 format .xls to 2007 format .xlsx m_workbook.Save(); worksheet.Delete(); m_bDeleteSheet = TRUE; return RET_success_delete; } int CExcelManager::DeleteSheet(CString name) { if (!m_bOpenExcel) return RET_excel_is_not_open; if (!SheetNameExists(name)) return RET_sheetname_exists; CWorksheet worksheet; worksheet.AttachDispatch(m_worksheets.get_Item(_variant_t(name)), TRUE); if (m_pathName.Right(1) != _T("x")) // save 2003 format .xls to 2007 format .xlsx m_workbook.Save(); worksheet.Delete(); m_bDeleteSheet = TRUE; return RET_success_delete; } CString CExcelManager::GetCurVersion() { /*判断当前Excel的版本*/ CString version = m_app.get_Version(); int iStart = 0; version = version.Tokenize(_T("."), iStart); if (_T("11") == version) { return _T("2003"); } else if (_T("12") == version) { return _T("2007"); } else { return _T("当前Excel的版本是其他版本"); } } CString CExcelManager::GetExcelName() { return m_pathName; } int CExcelManager::GetSheetCount() { if (!m_bOpenExcel) return RET_excel_is_not_open; return m_worksheets.get_Count(); } CString CExcelManager::GetSheetName(int index) { if (!m_bOpenExcel) return _T(""); //_T("warning: haven't opened excel!"); CWorksheet worksheet; worksheet.AttachDispatch(m_worksheets.get_Item(_variant_t((long)index)), TRUE); CString sheetName = worksheet.get_Name(); worksheet.ReleaseDispatch(); return sheetName; } CString CExcelManager::GetCurSheetName() { if (!m_bOpenExcel) return _T(""); // _T("warning: haven't loaded sheet!"); if (!m_bOpenSheet) return _T(""); // _T("warning: haven't opened sheet!"); CString sheetName = m_worksheet.get_Name(); m_worksheet.ReleaseDispatch(); return sheetName; } int CExcelManager::SetCurSheetName(CString name) { if (!m_bOpenExcel) return RET_excel_is_not_open; if (!m_bOpenSheet) return RET_sheet_is_not_open; if (SheetNameExists(name)) return RET_sheetname_exists; m_worksheet.put_Name(name); // set sheet name. return RET_success_set; } int CExcelManager::SetSheetName(int index, CString name) { if (!m_bOpenExcel) return RET_excel_is_not_open; if (SheetNameExists(name)) return RET_sheetname_exists; if (index < 1 || index > GetSheetCount()) return RET_outof_sheet_count; CWorksheet worksheet; worksheet.AttachDispatch(m_worksheets.get_Item(_variant_t(index)), TRUE); if (SheetNameExists(name)) return FALSE; worksheet.put_Name(name); worksheet.DetachDispatch(); return RET_success_set; } BOOL CExcelManager::SheetNameExists(CString name) { int sheetCnt = GetSheetCount(); for (int i=1; i <= sheetCnt; i++) { if (name == GetSheetName(i)) return RET_sheetname_exists; } return FALSE; } int CExcelManager::AddSheet(CString name) { if (!m_bOpenExcel) return RET_excel_is_not_open; LPDISPATCH lpDis = m_worksheets.Add(vtMissing, vtMissing, _variant_t((long)1), vtMissing); if (lpDis) { OpenSheet(1); // load the new sheet. SetCurSheetName(name); // set the new sheet name. return RET_success_add; } return RET_fail_add; } int CExcelManager::OpenSheet(CString name) { if (!SheetNameExists(name)) return RET_sheetname_exists; char* temp = UnicodeToAnsi((wchar_t*)name.GetString()); BOOL ret = OpenSheet(temp); delete[] temp; return ret; } int CExcelManager::OpenSheet(int index) { if (!m_bOpenExcel) return RET_excel_is_not_open; if (index < 1 || index > GetSheetCount()) return RET_outof_sheet_count; LPDISPATCH lpDis = NULL; m_range.ReleaseDispatch(); m_worksheet.ReleaseDispatch(); lpDis = m_worksheets.get_Item(_variant_t((long)index)); if (lpDis) { m_worksheet.AttachDispatch(lpDis, TRUE); m_range.AttachDispatch(m_worksheet.get_Cells(), TRUE); m_bOpenSheet = TRUE; return RET_success_open; } else { m_bOpenSheet = FALSE; return RET_fail_open; } } int CExcelManager::OpenSheet(char* name) { if (!m_bOpenExcel) return RET_excel_is_not_open; LPDISPATCH lpDis = NULL; m_range.ReleaseDispatch(); m_worksheet.ReleaseDispatch(); lpDis = m_worksheets.get_Item(_variant_t(name)); if (lpDis) { m_worksheet.AttachDispatch(lpDis, TRUE); m_range.AttachDispatch(m_worksheet.get_Cells(), TRUE); m_bOpenSheet = TRUE; return RET_success_open; } else