C++读取excel表格

爱被打了一巴掌 2023-07-09 02:13 159阅读 0赞

1 创建mfc程序(这里以vs2013为例)

20141225111940943

20141225111946562

20141225111953517

到这里直接点击完成即可。

2 添加读取excel文件用到的类

2.1 打开类向导

20141225111959133

2.2 添加类

20141225112004312

将_Application、_Workbook、_Worksheet、Workbooks、Worksheets添加到“生成的类”中

3 添加完成后，找到相关头文件，注释/删除

  1.  #import "D:\\software\\office2010\\Office14\\EXCEL.EXE"no_namespace

4 再次编译还是有错误

定位错误信息，将DialogBox()改为_DialogBox()

5 添加源码(最重要的一步)

5.1.Excel.h

  1. //Excel.h
  2. #pragma once
  3. #include "CApplication.h"
  4. #include "CRange.h"
  5. #include "CWorkbook.h"
  6. #include "CWorkbooks.h"
  7. #include "CWorksheet.h"
  8. #include "CWorksheets.h"
  10. class Excel
  11. {
  12. private:
  13.     CString openFileName;
  14.     CWorkbook workBook;//当前处理文件
  15.     CWorkbooks books;//ExcelBook集合，多文件时使用
  16.     CWorksheet workSheet;//当前使用sheet
  17.     CWorksheets sheets;//Excel的sheet集合
  18.     CRange currentRange;//当前操作区域
  20.     bool isLoad;//是否已经加载了某个sheet数据
  22.     COleSafeArray safeArray;
  24. protected:
  25.     static CApplication application;//Excel进程实例
  27. public:
  28.     Excel();
  29.     virtual ~Excel();
  33.     void show(bool bShow);
  35.     //检查一个cell是否为字符串
  36.     bool isCellString(long iRow, long iColumn);
  38.     //检查一个cell是否为数值
  39.     bool isCellInt(long iRow, long iColumn);
  41.     //得到一个cell的string
  42.     CString getCellString(long iRow, long iColumn);
  44.     //得到一个cell的整数
  45.     int getCellInt(long iRow, long iColumn);
  47.     //得到一个cell的double数据
  48.     double getCellDouble(long iRow, long iColumn);
  50.     //取得行的总数
  51.     int getRowCount();
  53.     //取得列的整数
  54.     int getColumnCount();
  56.     //使用某个shell
  57.     bool loadSheet(long tableId, bool preLoaded = false);
  58.     bool loadSheet(CString sheet, bool preLoaded = false);
  61.     //通过序号取得某个shell的名称
  62.     CString getSheetName(long tableID);
  64.     //得到sheel的总数
  65.     int getSheetCount();
  70.     //打开excel文件
  71.     bool open(const char* fileName);
  73.     //关闭打开的excel文件
  74.     void close(bool ifSave = false);
  76.     //另存为一个excel文件
  77.     void saveAsXLSFile(const CString &xlsFile);
  79.     //取得打开文件的名称
  80.     CString getOpenFileName();
  82.     //取得打开sheel的名称
  83.     CString getOpenSheelName();
  85.     //向cell中写入一个int值
  86.     void setCellInt(long iRow, long iColumn,int newInt);
  88.     //向cell中写入一个字符串
  89.     void setCellString(long iRow, long iColumn, CString newString);
  92. public:
  93.     //初始化 Excel_OLE
  94.     static bool initExcel();
  96.     //释放Excel_OLE
  97.     static void release();
  99.     //取得列的名称
  100.     static char* getColumnName(long iColumn);
  102. protected:
  103.     void preLoadSheet();
  107. };

5.2.Excel.cpp

  1. //Excel.cpp
  2. #include "stdafx.h"
  3. #include <tchar.h>
  4. #include "Excel.h"
  7. COleVariant
  8. covTrue((short)TRUE),
  9. covFalse((short)FALSE),
  10. covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
  12. CApplication Excel::application;
  14. Excel::Excel() :isLoad(false)
  15. {
  16. }
  19. Excel::~Excel()
  20. {
  21.     //close();
  22. }
  25. bool Excel::initExcel()
  26. {
  27.     //创建Excel 2000服务器(启动Excel)   
  28.     AfxOleInit();   //先初始化ole
  29.     if (!application.CreateDispatch(_T("Excel.application"),nullptr))
  30.     {
  31.         MessageBox(nullptr,_T("创建Excel服务失败,你可能没有安装EXCEL，请检查!"),_T("错误"),MB_OK);
  32.         return FALSE;
  33.     }
  35.     application.put_DisplayAlerts(FALSE);
  36.     return true;
  37. }
  40. void Excel::release()
  41. {
  42.     application.Quit();
  43.     application.ReleaseDispatch();
  44.     application = nullptr;
  45. }
  47. bool Excel::open(const char*  fileName)
  48. {
  50.     //先关闭文件
  51.     close();
  53.     //利用模板建立新文档
  54.     books.AttachDispatch(application.get_Workbooks(), true);
  57.     LPDISPATCH lpDis = nullptr;
  58.     lpDis = books.Add(COleVariant(CString(fileName)));
  60.     if (lpDis)
  61.     {
  62.         workBook.AttachDispatch(lpDis);
  64.         sheets.AttachDispatch(workBook.get_Worksheets());
  66.         openFileName = fileName;
  67.         return true;
  68.     }
  70.     return false;
  71. }
  73. void Excel::close(bool ifSave)
  74. {
  75.     //如果文件已经打开，关闭文件
  76.     if (!openFileName.IsEmpty())
  77.     {
  78.         //如果保存,交给用户控制,让用户自己存，如果自己SAVE，会出现莫名的等待  
  79.         if (ifSave)
  80.         {
  81.             //show(true);
  82.         }
  83.         else
  84.         {
  85.             workBook.Close(COleVariant(short(FALSE)), COleVariant(openFileName), covOptional);
  86.             books.Close();
  87.         }
  89.         //清空打开文件名称
  90.         openFileName.Empty();
  91.     }
  94.     sheets.ReleaseDispatch();
  95.     workSheet.ReleaseDispatch();
  96.     currentRange.ReleaseDispatch();
  97.     workBook.ReleaseDispatch();
  98.     books.ReleaseDispatch();
  99. }
  101. void Excel::saveAsXLSFile(const CString &xlsFile)
  102. {
  103.     workBook.SaveAs(COleVariant(xlsFile),
  104.         covOptional,
  105.         covOptional,
  106.         covOptional,
  107.         covOptional,
  108.         covOptional,
  109.         0,
  110.         covOptional,
  111.         covOptional,
  112.         covOptional,
  113.         covOptional,
  114.         covOptional);
  115.     return;
  116. }
  119. int Excel::getSheetCount()
  120. {
  121.     return sheets.get_Count();
  122. }
  124. CString Excel::getSheetName(long tableID)
  125. {
  126.     CWorksheet sheet;
  127.     sheet.AttachDispatch(sheets.get_Item(COleVariant((long)tableID)));
  128.     CString name = sheet.get_Name();
  129.     sheet.ReleaseDispatch();
  130.     return name;
  131. }
  134. void Excel::preLoadSheet()
  135. {
  136.     CRange used_range;
  138.     used_range = workSheet.get_UsedRange();
  141.     VARIANT ret_ary = used_range.get_Value2();
  142.     if (!(ret_ary.vt & VT_ARRAY))
  143.     {
  144.         return;
  145.     }
  146.     //  
  147.     safeArray.Clear();
  148.     safeArray.Attach(ret_ary);
  149. }
  151. //按照名称加载sheet表格，也可提前加载所有表格
  152. bool Excel::loadSheet(long tableId, bool preLoaded)
  153. {
  154.     LPDISPATCH lpDis = nullptr;
  155.     currentRange.ReleaseDispatch();
  156.     currentRange.ReleaseDispatch();
  157.     lpDis = sheets.get_Item(COleVariant((long)tableId));
  158.     if (lpDis)
  159.     {
  160.         workSheet.AttachDispatch(lpDis, true);
  161.         currentRange.AttachDispatch(workSheet.get_Cells(), true);
  162.     }
  163.     else
  164.     {
  165.         return false;
  166.     }
  168.     isLoad = false;
  169.     //如果进行预先加载  
  170.     if (preLoaded)
  171.     {
  172.         preLoadSheet();
  173.         isLoad = true;
  174.     }
  176.     return true;
  177. }
  180. bool Excel::loadSheet(CString sheet, bool preLoaded)
  181. {
  182.     LPDISPATCH lpDis = nullptr;
  183.     currentRange.ReleaseDispatch();
  184.     currentRange.ReleaseDispatch();
  186.     lpDis = sheets.get_Item(COleVariant(sheet));
  187.     if (lpDis)
  188.     {
  189.         workSheet.AttachDispatch(lpDis, true);
  190.         currentRange.AttachDispatch(workSheet.get_Cells(), true);
  191.     }
  192.     else
  193.     {
  194.         return false;
  195.     }
  197.     isLoad = false;
  198.     //如果进行预先加载  
  199.     if (preLoaded)
  200.     {
  201.         preLoadSheet();
  202.         isLoad = true;
  203.     }
  205.     return true;
  206. }
  209. int Excel::getColumnCount()
  210. {
  211.     CRange range;
  212.     CRange usedRange;
  214.     usedRange.AttachDispatch(workSheet.get_UsedRange(), true);
  215.     range.AttachDispatch(usedRange.get_Columns(), true);
  216.     int count = range.get_Count();
  218.     usedRange.ReleaseDispatch();
  219.     range.ReleaseDispatch();
  221.     return count;
  222. }
  224. int Excel::getRowCount()
  225. {
  226.     CRange range;
  227.     CRange usedRange;
  229.     usedRange.AttachDispatch(workSheet.get_UsedRange(), true);
  230.     range.AttachDispatch(usedRange.get_Rows(), true);
  232.     int count = range.get_Count();
  234.     usedRange.ReleaseDispatch();
  235.     range.ReleaseDispatch();
  237.     return count;
  238. }
  240. bool Excel::isCellString(long iRow, long iColumn)
  241. {
  242.     CRange range;
  243.     range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);
  244.     COleVariant vResult = range.get_Value2();
  245.     //VT_BSTR标示字符串  
  246.     if (vResult.vt == VT_BSTR)
  247.     {
  248.         return true;
  249.     }
  250.     return false;
  251. }
  254. bool Excel::isCellInt(long iRow, long iColumn)
  255. {
  257.     CRange range;
  258.     range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);
  259.     COleVariant vResult = range.get_Value2();
  260.     //VT_BSTR标示字符串  
  261.     if (vResult.vt == VT_INT || vResult.vt == VT_R8)
  262.     {
  263.         return true;
  264.     }
  265.     return false;
  266. }
  268. CString Excel::getCellString(long iRow, long iColumn)
  269. {
  270.     COleVariant vResult;
  271.     CString str;
  272.     //字符串  
  273.     if (isLoad == false)
  274.     {
  275.         CRange range;
  276.         range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);
  277.         vResult = range.get_Value2();
  278.         range.ReleaseDispatch();
  279.     }
  280.     //如果数据依据预先加载了  
  281.     else
  282.     {
  283.         long read_address[2];
  284.         VARIANT val;
  285.         read_address[0] = iRow;
  286.         read_address[1] = iColumn;
  287.         safeArray.GetElement(read_address, &val);
  288.         vResult = val;
  289.     }
  291.     if (vResult.vt == VT_BSTR)
  292.     {
  293.         str = vResult.bstrVal;
  294.     }
  295.     //整数  
  296.     else if (vResult.vt == VT_INT)
  297.     {
  298.         str.Format(_T("%d"), vResult.pintVal);
  299.     }
  300.     //8字节的数字   
  301.     else if (vResult.vt == VT_R8)
  302.     {
  303.         str.Format(_T("%0.0f"), vResult.dblVal);
  304.     }
  305.     //时间格式  
  306.     else if (vResult.vt == VT_DATE)
  307.     {
  308.         SYSTEMTIME st;
  309.         VariantTimeToSystemTime(vResult.date, &st);
  310.         CTime tm(st);
  311.         str = tm.Format(_T("%Y-%m-%d"));
  313.     }
  314.     //单元格空的  
  315.     else if (vResult.vt == VT_EMPTY)
  316.     {
  317.         str = "";
  318.     }
  320.     return str;
  321. }
  323. double Excel::getCellDouble(long iRow, long iColumn)
  324. {
  325.     double rtn_value = 0;
  326.     COleVariant vresult;
  327.     //字符串  
  328.     if (isLoad == false)
  329.     {
  330.         CRange range;
  331.         range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);
  332.         vresult = range.get_Value2();
  333.         range.ReleaseDispatch();
  334.     }
  335.     //如果数据依据预先加载了  
  336.     else
  337.     {
  338.         long read_address[2];
  339.         VARIANT val;
  340.         read_address[0] = iRow;
  341.         read_address[1] = iColumn;
  342.         safeArray.GetElement(read_address, &val);
  343.         vresult = val;
  344.     }
  346.     if (vresult.vt == VT_R8)
  347.     {
  348.         rtn_value = vresult.dblVal;
  349.     }
  351.     return rtn_value;
  352. }
  354. int Excel::getCellInt(long iRow, long iColumn)
  355. {
  356.     int num;
  357.     COleVariant vresult;
  359.     if (isLoad == FALSE)
  360.     {
  361.         CRange range;
  362.         range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);
  363.         vresult = range.get_Value2();
  364.         range.ReleaseDispatch();
  365.     }
  366.     else
  367.     {
  368.         long read_address[2];
  369.         VARIANT val;
  370.         read_address[0] = iRow;
  371.         read_address[1] = iColumn;
  372.         safeArray.GetElement(read_address, &val);
  373.         vresult = val;
  374.     }
  375.     //  
  376.     num = static_cast<int>(vresult.dblVal);
  378.     return num;
  379. }
  381. void Excel::setCellString(long iRow, long iColumn, CString newString)
  382. {
  383.     COleVariant new_value(newString);
  384.     CRange start_range = workSheet.get_Range(COleVariant(_T("A1")), covOptional);
  385.     CRange write_range = start_range.get_Offset(COleVariant((long)iRow - 1), COleVariant((long)iColumn - 1));
  386.     write_range.put_Value2(new_value);
  387.     start_range.ReleaseDispatch();
  388.     write_range.ReleaseDispatch();
  389. }
  391. void Excel::setCellInt(long iRow, long iColumn, int newInt)
  392. {
  393.     COleVariant new_value((long)newInt);
  394.     CRange start_range = workSheet.get_Range(COleVariant(_T("A1")), covOptional);
  395.     CRange write_range = start_range.get_Offset(COleVariant((long)iRow - 1), COleVariant((long)iColumn - 1));
  396.     write_range.put_Value2(new_value);
  397.     start_range.ReleaseDispatch();
  398.     write_range.ReleaseDispatch();
  399. }
  402. void Excel::show(bool bShow)
  403. {
  404.     application.put_Visible(bShow);
  405.     application.put_UserControl(bShow);
  406. }
  408. CString Excel::getOpenFileName()
  409. {
  410.     return openFileName;
  411. }
  413. CString Excel::getOpenSheelName()
  414. {
  415.     return workSheet.get_Name();
  416. }
  418. char* Excel::getColumnName(long iColumn)
  419. {
  420.     static char column_name[64];
  421.     size_t str_len = 0;
  423.     while (iColumn > 0)
  424.     {
  425.         int num_data = iColumn % 26;
  426.         iColumn /= 26;
  427.         if (num_data == 0)
  428.         {
  429.             num_data = 26;
  430.             iColumn--;
  431.         }
  432.         column_name[str_len] = (char)((num_data - 1) + 'A');
  433.         str_len++;
  434.     }
  435.     column_name[str_len] = '\0';
  436.     //反转  
  437.     _strrev(column_name);
  439.     return column_name;
  440. }

6.使用示例

  1. //使用excel类
  2.     Excel excl;
  3.     bool bInit = excl.initExcel();
  4.     char path[MAX_PATH];
  5.     GetCurrentDirectoryA(MAX_PATH, path);//获取当前工作目录
  6.     strcat_s(path, "\\data.xlsx");//设置要打开文件的完整路径
  9.     bool bRet = excl.open(path);//打开excel文件
  11.     CString strSheetName = excl.getSheetName(1);//获取sheet名
  12.     bool bLoad = excl.loadSheet(strSheetName);//装载sheet
  13.     int nRow = excl.getRowCount();//获取sheet中行数
  14.     int nCol = excl.getColumnCount();//获取sheet中列数
  17.     CString cell;
  18.     for (int i = 1; i <= nRow; ++i)
  19.     {
  20.         for (int j = 1; j <= nCol; ++j)
  21.         {
  22.             cell = excl.getCellString(i, j);
  23.         }
  24.     }
  26. 

补充：

在原文中作者初始化excel服务时，在比如xp机器上会出现创建excel服务失败的情况，作为补充，我在初始化excel服务时添加了ole的初始化方法，

  1. AfxOleInit();

原文链接：https://blog.csdn.net/u013507368/article/details/42143913

发表评论

表情：
评论列表 （有 0 条评论，159人围观）

还没有评论，来说两句吧...

相关阅读