C++读取excel表格

                            C++读取excel文件

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

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

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

  2.1 打开类向导

   

   

2.2 添加类

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

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

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

4再次编译还是有错误

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

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

//Excel.h#pragma once#include "CApplication.h"#include "CRange.h"#include "CWorkbook.h"#include "CWorkbooks.h"#include "CWorksheet.h"#include "CWorksheets.h"class Excel{private:CString openFileName;CWorkbook workBook;//当前处理文件CWorkbooks books;//ExcelBook集合，多文件时使用CWorksheet workSheet;//当前使用sheetCWorksheets sheets;//Excel的sheet集合CRange currentRange;//当前操作区域bool isLoad;//是否已经加载了某个sheet数据COleSafeArray safeArray;protected:static CApplication application;//Excel进程实例public:Excel();virtual ~Excel();void show(bool bShow);    //检查一个cell是否为字符串bool isCellString(long iRow, long iColumn);//检查一个cell是否为数值bool isCellInt(long iRow, long iColumn);//得到一个cell的stringCString getCellString(long iRow, long iColumn);//得到一个cell的整数int getCellInt(long iRow, long iColumn);//得到一个cell的double数据double getCellDouble(long iRow, long iColumn);//取得行的总数int getRowCount();//取得列的整数int getColumnCount();//使用某个shellbool loadSheet(long tableId, bool preLoaded = false);bool loadSheet(CString sheet, bool preLoaded = false);//通过序号取得某个shell的名称CString getSheetName(long tableID);//得到sheel的总数int getSheetCount();//打开excel文件bool open(const char* fileName);//关闭打开的excel文件void close(bool ifSave = false);//另存为一个excel文件void saveAsXLSFile(const CString &xlsFile);//取得打开文件的名称CString getOpenFileName();//取得打开sheel的名称CString getOpenSheelName();//向cell中写入一个int值void setCellInt(long iRow, long iColumn,int newInt);//向cell中写入一个字符串void setCellString(long iRow, long iColumn, CString newString);public://初始化 Excel_OLEstatic bool initExcel();//释放Excel_OLEstatic void release();//取得列的名称static char* getColumnName(long iColumn);protected:void preLoadSheet();};

//Excel.cpp#include "stdafx.h"#include <tchar.h>#include "Excel.h"COleVariantcovTrue((short)TRUE),covFalse((short)FALSE),covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);CApplication Excel::application;Excel::Excel() :isLoad(false){}Excel::~Excel(){//close();}bool Excel::initExcel(){//创建Excel 2000服务器(启动Excel)   if (!application.CreateDispatch(_T("Excel.application"),nullptr)){MessageBox(nullptr,_T("创建Excel服务失败,你可能没有安装EXCEL，请检查!"),_T("错误"),MB_OK);return FALSE;}application.put_DisplayAlerts(FALSE);return true;}void Excel::release(){application.Quit();application.ReleaseDispatch();application = nullptr;}bool Excel::open(const char*  fileName){//先关闭文件close();//利用模板建立新文档books.AttachDispatch(application.get_Workbooks(), true);LPDISPATCH lpDis = nullptr;lpDis = books.Add(COleVariant(CString(fileName)));if (lpDis){workBook.AttachDispatch(lpDis);sheets.AttachDispatch(workBook.get_Worksheets());openFileName = fileName;return true;}return false;}void Excel::close(bool ifSave){//如果文件已经打开，关闭文件if (!openFileName.IsEmpty()){//如果保存,交给用户控制,让用户自己存，如果自己SAVE，会出现莫名的等待  if (ifSave){//show(true);}else{workBook.Close(COleVariant(short(FALSE)), COleVariant(openFileName), covOptional);books.Close();}//清空打开文件名称openFileName.Empty();}sheets.ReleaseDispatch();workSheet.ReleaseDispatch();currentRange.ReleaseDispatch();workBook.ReleaseDispatch();books.ReleaseDispatch();}void Excel::saveAsXLSFile(const CString &xlsFile){workBook.SaveAs(COleVariant(xlsFile),covOptional,covOptional,covOptional,covOptional,covOptional,0,covOptional,covOptional,covOptional,covOptional,covOptional);return;}int Excel::getSheetCount(){return sheets.get_Count();}CString Excel::getSheetName(long tableID){CWorksheet sheet;sheet.AttachDispatch(sheets.get_Item(COleVariant((long)tableID)));CString name = sheet.get_Name();sheet.ReleaseDispatch();return name;}void Excel::preLoadSheet(){CRange used_range;used_range = workSheet.get_UsedRange();VARIANT ret_ary = used_range.get_Value2();if (!(ret_ary.vt & VT_ARRAY)){return;}//  safeArray.Clear();safeArray.Attach(ret_ary);}//按照名称加载sheet表格，也可提前加载所有表格bool Excel::loadSheet(long tableId, bool preLoaded){LPDISPATCH lpDis = nullptr;currentRange.ReleaseDispatch();currentRange.ReleaseDispatch();lpDis = sheets.get_Item(COleVariant((long)tableId));if (lpDis){workSheet.AttachDispatch(lpDis, true);currentRange.AttachDispatch(workSheet.get_Cells(), true);}else{return false;}isLoad = false;//如果进行预先加载  if (preLoaded){preLoadSheet();isLoad = true;}return true;}bool Excel::loadSheet(CString sheet, bool preLoaded){LPDISPATCH lpDis = nullptr;currentRange.ReleaseDispatch();currentRange.ReleaseDispatch();lpDis = sheets.get_Item(COleVariant(sheet));if (lpDis){workSheet.AttachDispatch(lpDis, true);currentRange.AttachDispatch(workSheet.get_Cells(), true);}else{return false;}isLoad = false;//如果进行预先加载  if (preLoaded){preLoadSheet();isLoad = true;}return true;}int Excel::getColumnCount(){CRange range;CRange usedRange;usedRange.AttachDispatch(workSheet.get_UsedRange(), true);range.AttachDispatch(usedRange.get_Columns(), true);int count = range.get_Count();usedRange.ReleaseDispatch();range.ReleaseDispatch();return count;}int Excel::getRowCount(){CRange range;CRange usedRange;usedRange.AttachDispatch(workSheet.get_UsedRange(), true);range.AttachDispatch(usedRange.get_Rows(), true);int count = range.get_Count();usedRange.ReleaseDispatch();range.ReleaseDispatch();return count;}bool Excel::isCellString(long iRow, long iColumn){CRange range;range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);COleVariant vResult = range.get_Value2();//VT_BSTR标示字符串  if (vResult.vt == VT_BSTR){return true;}return false;}bool Excel::isCellInt(long iRow, long iColumn){CRange range;range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);COleVariant vResult = range.get_Value2();//VT_BSTR标示字符串  if (vResult.vt == VT_INT || vResult.vt == VT_R8){return true;}return false;}CString Excel::getCellString(long iRow, long iColumn){COleVariant vResult;CString str;//字符串  if (isLoad == false){CRange range;range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);vResult = range.get_Value2();range.ReleaseDispatch();}//如果数据依据预先加载了  else{long read_address[2];VARIANT val;read_address[0] = iRow;read_address[1] = iColumn;safeArray.GetElement(read_address, &val);vResult = val;}if (vResult.vt == VT_BSTR){str = vResult.bstrVal;}//整数  else if (vResult.vt == VT_INT){str.Format(_T("%d"), vResult.pintVal);}//8字节的数字   else if (vResult.vt == VT_R8){str.Format(_T("%0.0f"), vResult.dblVal);}//时间格式  else if (vResult.vt == VT_DATE){SYSTEMTIME st;VariantTimeToSystemTime(vResult.date, &st);CTime tm(st);str = tm.Format(_T("%Y-%m-%d"));}//单元格空的  else if (vResult.vt == VT_EMPTY){str = "";}return str;}double Excel::getCellDouble(long iRow, long iColumn){double rtn_value = 0;COleVariant vresult;//字符串  if (isLoad == false){CRange range;range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);vresult = range.get_Value2();range.ReleaseDispatch();}//如果数据依据预先加载了  else{long read_address[2];VARIANT val;read_address[0] = iRow;read_address[1] = iColumn;safeArray.GetElement(read_address, &val);vresult = val;}if (vresult.vt == VT_R8){rtn_value = vresult.dblVal;}return rtn_value;}int Excel::getCellInt(long iRow, long iColumn){int num;COleVariant vresult;if (isLoad == FALSE){CRange range;range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);vresult = range.get_Value2();range.ReleaseDispatch();}else{long read_address[2];VARIANT val;read_address[0] = iRow;read_address[1] = iColumn;safeArray.GetElement(read_address, &val);vresult = val;}//  num = static_cast<int>(vresult.dblVal);return num;}void Excel::setCellString(long iRow, long iColumn, CString newString){COleVariant new_value(newString);CRange start_range = workSheet.get_Range(COleVariant(_T("A1")), covOptional);CRange write_range = start_range.get_Offset(COleVariant((long)iRow - 1), COleVariant((long)iColumn - 1));write_range.put_Value2(new_value);start_range.ReleaseDispatch();write_range.ReleaseDispatch();}void Excel::setCellInt(long iRow, long iColumn, int newInt){COleVariant new_value((long)newInt);CRange start_range = workSheet.get_Range(COleVariant(_T("A1")), covOptional);CRange write_range = start_range.get_Offset(COleVariant((long)iRow - 1), COleVariant((long)iColumn - 1));write_range.put_Value2(new_value);start_range.ReleaseDispatch();write_range.ReleaseDispatch();}void Excel::show(bool bShow){application.put_Visible(bShow);application.put_UserControl(bShow);}CString Excel::getOpenFileName(){return openFileName;}CString Excel::getOpenSheelName(){return workSheet.get_Name();}char* Excel::getColumnName(long iColumn){static char column_name[64];size_t str_len = 0;while (iColumn > 0){int num_data = iColumn % 26;iColumn /= 26;if (num_data == 0){num_data = 26;iColumn--;}column_name[str_len] = (char)((num_data - 1) + 'A');str_len++;}column_name[str_len] = '\0';//反转  _strrev(column_name);return column_name;}

//使用excel类Excel excl;bool bInit = excl.initExcel();char path[MAX_PATH];GetCurrentDirectoryA(MAX_PATH, path);//获取当前工作目录strcat_s(path, "\\data.xlsx");//设置要打开文件的完整路径bool bRet = excl.open(path);//打开excel文件CString strSheetName = excl.getSheetName(1);//获取sheet名bool bLoad = excl.loadSheet(strSheetName);//装载sheetint nRow = excl.getRowCount();//获取sheet中行数int nCol = excl.getColumnCount();//获取sheet中列数CString cell;for (int i = 1; i <= nRow; ++i){for (int j = 1; j <= nCol; ++j){cell = excl.getCellString(i, j);}}