C + + read Excel table

C + + read Excel file

1 Creating MFC programs (Take vs2013 as an example)

Click here to complete the direct.

2 adding classes to read Excel files

2.1 Opening the Class Wizard

2.2 Adding classes

will be _application , _workbook , _worksheet , Workbooks , Worksheets add to "generated classes"

3 When you are finished, locate the relevant header file, comment / delete

#import"D:\\software\\office2010\\office14\\excel. EXE "no_namespace

4 compile again or there is an error

Locate the error message and DialogBox () instead _DialogBox ()

5 Add Source ( the most important step )

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;//Current Processing file Cworkbooks Books;//excelbook collection, multiple files using Cworksheet worksheet;//currently using sheetcworksheets sheets //excel Sheet Collection CRange currentrange;//the current operation area BOOL isload;//has already loaded some sheet data COleSafeArray safearray;protected:    Static CApplication Application;//excel Process Instance public:excel (); virtual ~excel (); void Show (bool bShow); Check if a cell is a string bool Iscellstring (long iRow, long iColumn);//Check whether a cell is a numeric bool Iscellint (long iRow, long iColumn);// Get a cell of stringcstring getcellstring (long iRow, long iColumn);//Get a cell integer int getcellint (long iRow, long iColumn);// Get a double data for a cell double getcelldouble (long iRow, long iColumn);//Gets the total number of rows int getrowcount ();//Gets the column integer int getcolumncount ( );//Use a shellbool loadsheet (long tableId, bool preloaded = FALSE), bool Loadsheet (CString sheet, bool preloaded = FALSE);// Get an item by ordinalThe name of the shell is CString getsheetname (long TableID);//Gets the total number of Sheel int getsheetcount ();//Open Excel file bool Open (const char* FileName );//close open Excel file void Close (bool Ifsave = false);//Save as an Excel file void Saveasxlsfile (const CString &xlsfile);// Gets the name of the open file CString getopenfilename ();//Gets the name of the open Sheel CString getopensheelname ();//writes an int value to the cell void Setcellint (long IRow, long icolumn,int newint);//write a string to the cell void setcellstring (long iRow, long iColumn, CString newstring);p ublic:// Initialize excel_olestatic bool Initexcel ();//release excel_olestatic void Release ();//Gets the name of the column static char* getColumnName (long IColumn);p rotected: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 () {//Create Excel 2000 Server (Start Excel) if (!application. CreateDispatch (_t ("Excel.Application"), nullptr)) {MessageBox ("Create Excel service failed, you may not have Excel installed, please check!"), nullptr,_t ("error "), 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) {//closes file close () First,//creates a new document books using a template. 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 the file is already open, closeFile if (!openfilename.isempty ()) {//if saved, given to the user to control, let the user save themselves, if their own save, will appear inexplicable wait if (ifsave) {//show (true);} Else{workbook.close (COleVariant (FALSE)), COleVariant (openfilename), covoptional); books. Close ();} Empty open File name 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::p reloadsheet () {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);} Load sheet table by name, or load all tables in advance 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 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 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 flag string 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 flag String if (VRESULT.VT = = Vt_int | | vresult.vt = VT_R8) {return true;} return false;} CstrinG Excel::getcellstring (Long iRow, long iColumn) {COleVariant vresult; CString str;//String if (Isload = = False) {CRange Range;range. AttachDispatch (Currentrange.get_item (COleVariant (long) iRow), COleVariant ((long) iColumn). Pdispval, True); Vresult = Range.get_value2 (); Range. ReleaseDispatch ();} If the data is pre-loaded 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;} Integer else if (vresult.vt = = vt_int) {str. Format (_t ("%d"), vresult.pintval);} The 8-byte number else if (VRESULT.VT = = VT_R8) {str. Format (_t ("%0.0f"), Vresult.dblval);} Time format Else if (VRESULT.VT = = vt_date) {SYSTEMTIME st; Varianttimetosystemtime (Vresult.date, &st); CTime TM (ST); str = TM. Format (_t ("%y-%m-%d"));} Cell Empty for else if (VRESULT.VT = = vt_empty) {str = "";} return str;} Double excel::getcelldouble (Long iRow, long IColumn) {double rtn_value = 0; COleVariant vresult;//String if (Isload = = False) {CRange ranGe;range. AttachDispatch (Currentrange.get_item (COleVariant (long) iRow), COleVariant ((long) iColumn). Pdispval, True); Vresult = Range.get_value2 (); Range. ReleaseDispatch ();} If the data is pre-loaded 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_da TA = 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] = ' + ';//Invert _strrev (column_name); return column_name;}

Use Excel class Excel Excl;bool Binit = Excl.initexcel (); Char Path[max_path]; Getcurrentdirectorya (MAX_PATH, path);//Gets the current working directory strcat_s (Path, "\\data.xlsx");//sets the full path of the file to be opened bool BRet = Excl.open ( path);//Open Excel file CString strsheetname = Excl.getsheetname (1);//Get sheet name bool Bload = Excl.loadsheet (strSheetName);// Load Sheetint nrow = Excl.getrowcount ();//Gets the number of rows in the sheet int ncol = Excl.getcolumncount ();//Gets the number of columns in sheet CString cell;for (int i = 1 ; I <= nrow; ++i) {for (int j = 1; j <= Ncol; ++j) {cell = Excl.getcellstring (i, j);}}



C + + read Excel table