Use C ++ to read and write Excel

1. Import an Excel database

Use the extension command of Visual C ++ # import to import an Excel database:

 
# Import "C :\\ Program Files \ common files \ Microsoft shared \ office14 \ MSO. DLL "\ Rename (" RGB "," msorgb ") \ Rename (" searchpath "," msosearchpath ") # import" C: \ Program Files \ common files \ Microsoft shared \ VBA \ vba6 \ vbe6ext. olb "# import" C: \ Program Files \ Microsoft Office \ office14 \ excel. EXE "\ Rename (" dialogbox "," exceldialogbox ") \ Rename (" RGB "," excelrgb ") \ Rename (" copyfile "," excelcopyfile ") \ Rename ("replacetext", "excelreplacetext") \ exclude ("ifont", "ipicture") no_dual_interfaces
 

# The import command exports the Type Library (type Lib) from the specified executable file, dynamic link library, and Other COM components ), generate the corresponding Type Library header file in the debug and release temporary directories for C ++Program. For example, after the preceding three commands are compiled, the Excel. tlh, MSO. LH, and vbetext. olb files are generated, which can be found in the debug and release directories.

2. Access the COM object exposed by Excel

The following is a complete example of accessing ExcelCode. First, fill the cells with the generated data, and then use the data of these cells to generate a chart ):

Try {Excel: _ applicationptr pexcelapp; hresult hR = pexcelapp. createinstance (L "Excel. application "); atlassert (succeeded (HR); pexcelapp-> visible = true; // make Excel's main window visibleexcel :: _ workbookptr pworkbook = pexcelapp-> workbooks-> open (lpszpathname); // open Excel fileexcel: _ worksheetptr pworksheet = pworkbook-> activesheet; pworksheet-> name = l "chart data"; Excel: rangeptr Prange = pworksheet-> cells; const int nplot = 100; const double xlow = 0.0, xhigh = 20.0; double H = (xhigh-xlow)/(double) nplot; Prange-> item [1] [1] = l "X "; // read/write cell's dataprange-> item [1] [2] = l "f (x)"; for (INT I = 0; I <nplot; ++ I) {Double X = xlow + I * h; Prange-> item [I + 2] [1] = X; prange-> item [I + 2] [2] = sin (x) * exp (-x);} Excel :: rangeptr pbeginrange = Prange-> item [1] [1]; Excel: rangeptr pendrange = Prange-> item [nplot + 1] [2]; Excel :: rangeptr ptotalrange = pworksheet-> range [(Excel: range *) pbeginrange] [(Excel: range *) pendrange]; Excel :: _ chartptr pchart = pexcelapp-> activeworkbook-> charts-> Add (); // refer to: // http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart.chartwizard (V = vs.80 ). aspxpchart-> chartwizard (Excel: range *) ptotalrange, (long) Excel: xlxyscatter, 6l, (long) Excel: xlcolumns, 1l, 1l, true, L "My graph", l "X", l "f (x)"); pchart-> name = l "my data plot"; pworkbook-> close (variant_true ); // save changespexcelapp-> quit ();} catch (_ com_error & error) {atlassert (false); atltrace2 (error. errormessage ());}

In this Code, Excel: _ applicationptr, Excel: _ workbookptr and Excel: _ worksheetptr are all smart pointers automatically generated by the visual C ++ compiler according to the # import command, it is actually a typedef of the c ++ template class _ com_ptr_t <t>, which can be defined in Excel. the header files of library types such as tlh are found.

In addition, because the # import command does not specify the raw_interface_only modifier, Visual C ++ encapsulates the COM interface of EXCEL to simplify the calling of COM interface attributes and methods, in addition, the returned values of hresult are converted to C ++ exceptions. Therefore, the above Code does not need to stick to hresult in every step, but instead captures C ++ exceptions.