MFC excel修改类

来源：互联网发布：漫步者h297 淘宝假货编辑：程序博客网时间：2024/06/05 10:23

本文转载于http://blog.csdn.net/timothy721/article/details/8692126

1. 导入MFC Lib库中的 CApplication、CRange、CWorkBook、CWorkBooks、CWorkSheet、CWorkSheets2. 将以上所有的h文件中的 #import 行 注释 并添加#include <afxdisp.h>   例如： //#import "C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE" no_namespace// CApplication 包装类#pragma once#include <afxdisp.h>3.处理类 .h 文件[cpp] view plain copy#pragma once    #include "CApplication.h"  #include "CWorkbook.h"  #include "CWorkbooks.h"  #include "CWorksheet.h"  #include "CWorksheets.h"  #include "CRange.h"  #include <map>  #include "../Control/InputInfo.h"  using namespace std;    class HandleExcelFile  {  public:      HandleExcelFile(void);      ~HandleExcelFile(void);  protected:      ///打开的EXCEL文件名称      CString  m_strFilePath;        //EXCEL BOOK集合，（多个文件时）      CWorkbooks   m_books;       //当前使用的BOOK，当前处理的文件      CWorkbook    m_wordBook;       //EXCEL的sheets集合      CWorksheets   m_sheets;       //当前使用sheet      CWorksheet    m_workSheet;       //当前的操作区域      CRange        m_range;       //进程实例      CApplication m_app;  public:      //初始化EXCEL OLE      BOOL InitExcel();      //打开Excel文件，故障判断标准      BOOL OpenExcelFile(const CString & strFilePath, MAP_FAILURE_CRITERIA &mapFailureCriteria);       //打开Excel文件，存量信息      BOOL OpenExcelFile(const CString & strFilePath, MAP_INVENTORY_INFO  &mapInventoryInfo);      //通过名称使用某个sheet      BOOL LoadSheetAndRange(CString strSheet);      //得到一个CELL的String      CString GetCellString(long iRow, long iColumn);      //得到一个CELL的int      int GetCellInt(long irow, long icolumn);      //得到一个CELL的Time      CString GetCellTime(long irow, long icolumn);      // 关闭Excel文件，释放资源      void CloseExcelFile();      //得到列的总数      int GetColumnCount();      //得到行的总数      int GetRowCount();      //导出ClistCtrl中的数据      bool ExportDataFromListCtrl(CListCtrl &listCtrl);  };  4.处理类 .cpp 文件[cpp] view plain copy#include "StdAfx.h"  #include "HandleExcelFile.h"  #include "../LogBulkSearchTool.h"  [cpp] view plain copyCOleVariant  covTrue((short)TRUE),  covFalse((short)FALSE),  covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);        HandleExcelFile::HandleExcelFile(void)  {      m_strFilePath = "";  }    HandleExcelFile::~HandleExcelFile(void)  {      CloseExcelFile();  }    BOOL HandleExcelFile::InitExcel()  {      if(CoInitialize(NULL)==S_FALSE)       {           AfxMessageBox("初始化COM支持库失败！");           return FALSE;       }      //创建Excel 2000服务器(启动Excel)       if (!m_app.CreateDispatch("Excel.Application", NULL))       {           AfxMessageBox("创建Excel服务失败!");          return FALSE;      }      //m_app.put_DisplayAlerts(FALSE);       return TRUE;  }    //关闭打开的Excel 文件,默认情况不保存文件  void HandleExcelFile::CloseExcelFile()  {      //如果已经打开，关闭文件      if (!m_strFilePath.IsEmpty())      {          COleVariant openExcelFile(m_strFilePath);          m_wordBook.Close(COleVariant(short(FALSE)),openExcelFile,covOptional);          m_books.Close();            //打开文件的名称清空          m_strFilePath.Empty();      }      m_app.Quit();      //m_app=NULL;        m_range.ReleaseDispatch();      m_sheets.ReleaseDispatch();      m_workSheet.ReleaseDispatch();           m_wordBook.ReleaseDispatch();      m_books.ReleaseDispatch();      m_app.ReleaseDispatch();      CoUninitialize();  }  ///打开判断标准文件  BOOL HandleExcelFile::OpenExcelFile(const CString & strFilePath, MAP_FAILURE_CRITERIA  &mapFailureCriteria)  {       if (strFilePath.IsEmpty())      {          AfxMessageBox("判断文件路径为空！");          return FALSE;      }      CFileStatus fs;        if (!CFile::GetStatus(strFilePath, fs))      {          AfxMessageBox("判断文件路径不存在！");          return FALSE;       }      /*得到工作簿容器*/      m_books = m_app.get_Workbooks();        LPDISPATCH   lpDisp=NULL;      try      {          lpDisp = m_books.Open(strFilePath,               vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,              vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,               vtMissing, vtMissing, vtMissing, vtMissing);          }      catch(CException*)      {          AfxMessageBox("无法打开判断文件！");          return FALSE;      }        if (lpDisp)      {          m_wordBook.AttachDispatch(lpDisp);          m_sheets = m_wordBook.get_Worksheets();          CString sheetName = "GULS";          if (FALSE == LoadSheetAndRange(sheetName))          {               AfxMessageBox("无法打开判断文件 ：sheet不存在");               return FALSE;          }                      //获取数据存储在内存          //FailureCriteriaClass failureCriteriaClass;          //int colTotalCount = GetColumnCount();          int rowTotalCount = GetRowCount();          //忽略列头开始          for (int i = 2; i <= rowTotalCount; ++i)          {              FailureCriteriaClass *tempStruct = new FailureCriteriaClass();              //ASSERT(260 == sizeof(FailureCriteriaClass) || 112 == sizeof(FailureCriteriaClass));                  tempStruct->ID = GetCellInt(i, 1);              tempStruct->boardType = GetCellString(i,2);              tempStruct->name = GetCellString(i, 3);              tempStruct->reason= GetCellString(i, 4);              tempStruct->affect= GetCellString(i, 5);                tempStruct->level= (VOS_UINT8)GetCellInt(i, 6);              tempStruct->rule= GetCellString(i, 7);              tempStruct->relatedID= GetCellInt(i, 8);              tempStruct->notes= GetCellString(i, 9);              tempStruct->suggestion = GetCellString(i, 10);              tempStruct->terraceNotes = GetCellString(i, 11);              if (tempStruct->ID == 0)              {                  delete tempStruct;                  continue;              }                mapFailureCriteria.insert(make_pair(tempStruct->ID, tempStruct));          }      }        //记录打开的文件名称      m_strFilePath = strFilePath;        //m_books.Close();      return TRUE;  }    ///打开信息文件  BOOL HandleExcelFile::OpenExcelFile(const CString & strFilePath, MAP_INVENTORY_INFO  &mapInventoryInfo)  {       if (strFilePath.IsEmpty())      {          AfxMessageBox("信息文件路径为空！");          return FALSE;      }      CFileStatus fs;        if (!CFile::GetStatus(strFilePath, fs))      {          AfxMessageBox("信息文件路径不存在！");          return FALSE;       }      /*得到工作簿容器*/      m_books = m_app.get_Workbooks();        LPDISPATCH   lpDisp=NULL;      try      {          lpDisp = m_books.Open(strFilePath,               vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,              vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,               vtMissing, vtMissing, vtMissing, vtMissing);          }      catch(CException*)      {          AfxMessageBox("无法打开信息文件！");          return FALSE;      }        if (lpDisp)      {          m_wordBook.AttachDispatch(lpDisp);          m_sheets = m_wordBook.get_Worksheets();            int posFileName = strFilePath.ReverseFind('\\');          int posFileNameEnd = strFilePath.Find('.', posFileName + 1);          CString sheetName = strFilePath.Mid(posFileName + 1, posFileNameEnd - posFileName - 1);            if (FALSE == LoadSheetAndRange(sheetName))          {              AfxMessageBox("无法打开信息文件 ：sheet不存在");              return FALSE;          }            //获取数据存储在内存          //int colTotalCount = GetColumnCount();          int rowTotalCount = GetRowCount();          //忽略列名行          for (int i = 2; i < rowTotalCount; ++i)          {              InventoryInfoClass *tempStruct = new InventoryInfoClass();              //ASSERT(260 == sizeof(InventoryInfoClass) || 112 == sizeof(InventoryInfoClass));                tempStruct->NEName = GetCellString(i, 3);              tempStruct->BoardName = GetCellString(i, 6);              tempStruct->ManufactureDate = GetCellTime(i, 9);              tempStruct->FrameNo = GetCellString(i, 10);              tempStruct->BOMCode = GetCellString(i, 14);              tempStruct->RackNo= GetCellString(i, 20);              tempStruct->SerialNumber= GetCellString(i, 21);               tempStruct->SlotNo= GetCellString(i, 22);                if (tempStruct->NEName == "" || tempStruct->FrameNo == "" || tempStruct->RackNo == "" || tempStruct->SlotNo == "")              {                  delete tempStruct;                  continue;              }              CString key = tempStruct->NEName + '_'  + tempStruct->RackNo + '_'+ tempStruct->FrameNo + '_' + tempStruct->SlotNo;                {//just for test                  MAP_INVENTORY_INFO::iterator itInfo = mapInventoryInfo.find(key);                  if (itInfo != mapInventoryInfo.end())                  {                      delete tempStruct;                      continue;                      //ASSERT(0);                  }              }              mapInventoryInfo.insert(make_pair(key, tempStruct));          }      }        //记录打开的文件名称      m_strFilePath = strFilePath;      return TRUE;  }    BOOL HandleExcelFile::LoadSheetAndRange(CString strSheet)  {      m_range.ReleaseDispatch();      m_workSheet.ReleaseDispatch();        LPDISPATCH  lpDisp=NULL;      lpDisp = m_sheets.get_Item(COleVariant(strSheet));      if (lpDisp)      {          m_workSheet.AttachDispatch(lpDisp, TRUE);          lpDisp = m_workSheet.get_Cells();          m_range.AttachDispatch(lpDisp,TRUE);          return TRUE;      }      return FALSE;  }    CString HandleExcelFile::GetCellString(long Row,long Col)  {      CString strValue="";      long lRow=0,lCol=0;      lRow=GetRowCount();      lCol=GetColumnCount();      if(Row>lRow ||Col>lCol)      {          return strValue;      }      VARIANT lpDisp = m_range.get_Item(_variant_t(Row), _variant_t(Col));      CRange rgRgeValue;      rgRgeValue.AttachDispatch(lpDisp.pdispVal, TRUE);      _variant_t vtVal = rgRgeValue.get_Value2();      if (vtVal.vt == VT_EMPTY)      {          rgRgeValue.ReleaseDispatch();          strValue = _T("");          rgRgeValue.ReleaseDispatch();          return strValue;      }      vtVal.ChangeType(VT_BSTR);      strValue= vtVal.bstrVal;      rgRgeValue.ReleaseDispatch();      return strValue;  }    int HandleExcelFile::GetCellInt(long irow, long icolumn)  {      int num = 0;      COleVariant vresult;      CRange range;      range.AttachDispatch(m_range.get_Item(COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);      vresult = range.get_Value2();      range.ReleaseDispatch();      num = static_cast<int>(vresult.dblVal);        return num;  }    CString HandleExcelFile::GetCellTime(long irow, long icolumn)  {      CString str = "";      COleVariant vResult;      CRange range;      range.AttachDispatch(m_range.get_Item(COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);      vResult = range.get_Value2();      range.ReleaseDispatch();      if(VT_R8 == vResult.vt)            {            SYSTEMTIME st;            VariantTimeToSystemTime(vResult.date, &st);            CTime tm(st);             str=tm.Format("%Y-%m-%d");      }      return str;  }    //得到列的总数  int HandleExcelFile::GetColumnCount()  {      CRange range;      CRange usedRange;      usedRange.AttachDispatch(m_workSheet.get_UsedRange(), true);      range.AttachDispatch(usedRange.get_Columns(), true);      int count = range.get_Count();      usedRange.ReleaseDispatch();      range.ReleaseDispatch();      return count;  }    //得到行的总数  int HandleExcelFile::GetRowCount()  {      CRange range;      CRange usedRange;      usedRange.AttachDispatch(m_workSheet.get_UsedRange(), true);      range.AttachDispatch(usedRange.get_Rows(), true);      int count = range.get_Count();      usedRange.ReleaseDispatch();      range.ReleaseDispatch();      return count;  }  static void   GetCellName(int nRow, int nCol, CString &strName)  {      CString strRow;      char cCell = (char)('A' + nCol - 1);        strName.Format(_T("%c"), cCell);      strRow.Format(_T( "%d "), nRow);      strName += strRow;  }  bool HandleExcelFile::ExportDataFromListCtrl(CListCtrl &listCtrl)  {      // 默认保存文件名      CTime tm = CTime::GetCurrentTime();      CString time = tm.Format("%Y%m%d");      CString outPutName;      outPutName.Format("输出结果_%s", time);         CFileDialog FileDialog(FALSE,"xlsx",outPutName,OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT,            _T("Microsoft Excel 2007(*.xlsx)|*.xlsx|所有文件(*.*)"),NULL);       if(FileDialog.DoModal()!=IDOK)       {          return false;      }      CString cStrFile=FileDialog.GetPathName();  //选择保存路径名称              if(::PathFileExists(cStrFile))       {          DeleteFile(cStrFile);       }        int   m_rows = listCtrl.GetItemCount();  //获取行数      if (m_rows == 0)      {          AfxMessageBox("请分析以后进行导出操作");          return false;      }      CHeaderCtrl   *pmyHeaderCtrl= listCtrl.GetHeaderCtrl(); //获取表头      int   m_cols   = pmyHeaderCtrl-> GetItemCount(); //获取列数        m_books = m_app.get_Workbooks();      m_wordBook = m_books.Add(covOptional);        m_sheets = m_wordBook.get_Worksheets();      m_workSheet = m_sheets.get_Item(COleVariant((short)1));  //得到第一个工作表           TCHAR     lpBuffer[256];            HDITEM   hdi;       hdi.mask   =   HDI_TEXT;      hdi.pszText   =   lpBuffer;      hdi.cchTextMax   =   256;         CString   colname;      CString strTemp;        int   iRow,iCol;      for(iCol=0;   iCol <m_cols;   iCol++)//将列表的标题头写入EXCEL       {           GetCellName(1 ,iCol + 1, colname); //(colname就是对应表格的A1,B1,C1,D1)          m_range   =   m_workSheet.get_Range(COleVariant(colname),COleVariant(colname));            pmyHeaderCtrl-> GetItem(iCol,   &hdi); //获取表头每列的信息          m_range.put_Value2(COleVariant(hdi.pszText));  //设置每列的内容          int   nWidth   =   listCtrl.GetColumnWidth(iCol)/6;             //得到第iCol+1列             m_range.AttachDispatch(m_range.get_Item(_variant_t((long)(iCol+1)),vtMissing).pdispVal,true);               //设置列宽            m_range.put_ColumnWidth(_variant_t((long)nWidth));      }         m_range   =   m_workSheet.get_Range(COleVariant( _T("A1 ")),   COleVariant(colname));       m_range.put_RowHeight(_variant_t((long)30));//设置行的高度       m_range.put_VerticalAlignment(COleVariant((short)-4108));//xlVAlignCenter   =   -4108         COleSafeArray   saRet; //COleSafeArray类是用于处理任意类型和维数的数组的类      DWORD   numElements[]={m_rows,m_cols};       //行列写入数组      saRet.Create(VT_BSTR,   2,   numElements); //创建所需的数组        m_range = m_workSheet.get_Range(COleVariant( _T("A2 ")),covOptional); //从A2开始      m_range = m_range.get_Resize(COleVariant((short)m_rows),COleVariant((short)m_cols)); //表的区域        long   index[2] = {0};          for(   iRow   =   1;   iRow   <=   m_rows;   iRow++)//将列表内容写入EXCEL       {           for   (   iCol   =   1;   iCol   <=   m_cols;   iCol++)            {               index[0]=iRow-1;               index[1]=iCol-1;                 CString   szTemp;               szTemp=listCtrl.GetItemText(iRow-1,iCol-1); //取得m_list控件中的内容                BSTR   bstr   =   szTemp.AllocSysString();               saRet.PutElement(index,bstr); //把m_list控件中的内容放入saRet                SysFreeString(bstr);          }       }        //记录打开的文件名称      m_strFilePath = cStrFile;        m_range.put_Value2(COleVariant(saRet)); //将得到的数据的saRet数组值放入表格      m_wordBook.SaveCopyAs(COleVariant(cStrFile)); //保存到cStrFile文件      m_wordBook.put_Saved(true);      return true;  }

0 0