【MFC】修改过的Excel处理类
来源：互联网 发布：单片机crc校验程序 编辑：程序博客网 时间：2024/05/29 07:57
1. 导入MFC Lib库中的 CApplication、CRange、CWorkBook、CWorkBooks、CWorkSheet、CWorkSheets
2. 将以上所有的h文件中的 #import 行 注释 并添加#include <afxdisp.h>
例如： //#import "C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE" no_namespace
// CApplication 包装类
#pragma once
#include <afxdisp.h>
3.处理类 .h 文件
#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 文件
#include "StdAfx.h"#include "HandleExcelFile.h"#include "../LogBulkSearchTool.h"
COleVariantcovTrue((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;}- 【MFC】修改过的Excel处理类
- MFC excel修改类
- MFC处理excel的有用的小函数
- 利用MFC处理Excel的几种方式
- 修改过的PHP购物车类
- 验证过的POI导入Excel的测试类
- Excel管理类 MFC
- 修改过的条码钩子
- 处理SQL Server 因修改过计算机名无法登陆的问题
- 修改MFC的图标
- MFC操作EXCEL的方法
- EXCEL表的MFC导入
- 四 MFC处理的三类消息
- MFC 的消息处理
- MFC OnIdle的处理
- 【Android】【问题处理】处理过的问题
- 用过的excel命令。记录一下
- 曾经做过的项目：C# Excel
- 程序测试大小端
- Java中的流、字符流和字节流
- java中常用的几个基础概念
- 在用 strtok函数的时候，在linux编译的问题
- Struts的图片的上传（只为未来自己用到的时候可以借鉴）。
- 【MFC】修改过的Excel处理类
- obj-c 自学随笔--- 加载UIBarButtonItem 到UINavigationController
- 如何查看和停止Linux启动的服务
- C++ Socket编程步骤
- GCD(一)
- java 的nio与io对比
- 简单内存泄漏检测方法，解决Detected memory leaks!问题 .
- ios开发之改变UIWebView文本字体的样式和大小
- shell算数运算
