Write Data to the Excel template file and Insert a new row

In many cases, we need to fill in some data generated by the program in the Excel file template format required by some users, and write the specified data item to the specified cell. In some specific situations, dynamic row insertion may also be required. For example, when a number of uncertain vertex coordinate information is required for each row to record one vertex coordinate information. In addition, files generated using templates must be saved separately to avoid overwriting the template files.

Based on the above purpose, I have adopted the general network search method. After several twists and turns, I have tested many times and finally completed the above functions. I will share them with you here.

Previously, I used the cspreadsheet class for Excel reading and writing. However, it seems that this class cannot be used to fill in the template Excel and can only be used to create an Excel file.

Let's talk about how to complete the above work.

1. Ole needs to be initialized in the project

If (! Afxoleinit () // initialize the com Library

{

Afxmessagebox ("failed to initialize com ");

Return false;

}

This code is usually placed at the beginning of the initinstance function of the application class.

2. Insert Excel Module

In classwizard, click "addclass..." on the right and select "from a type library..." from the drop-down menu ..."

In the displayed directory selection dialog box, select the Excel. EXE file under the Office installation directory.

The module selection window to be imported will pop up. If it is too troublesome, select all the modules in the list. You can drag the selected item directly or press the Shift key to select the first and last one. (Damn it, why can't Ctrl + A be supported)

Click "OK" to insert all modules into the project. Take a look at the project file and you will find two more files, Excel. h and Excel. cpp.

3. Write code

The environment is ready. Assume that the current test project is to click a button to complete the template file cell data filling, dynamic row insertion, and file storage in the button event.

First, you must include the Excel. h file:

# Include "comdef. H" // This is also required, otherwise _ variant_t does not know
# Include "Excel. H"

Here we assume that the EXCEL object is used as a member variable: (this mainly depends on the specific application in the Project)

Range m_exlrge;
_ Worksheet m_exlsheet;
Worksheets m_exlsheets;
_ Workbook m_exlbook;
Workbooks m_exlbooks;
_ Application m_exlapp;

 

Then add the processing code to the Click Event of the button. For more information about the meaning of the Code, see comments.

Void ctttdlg: onbutton1 () {// use the m_exlapp object to create the excel2003 process if (! M_exlapp.createdispatch ("Excel. Application", null) {afxmessagebox ("An error occurred while creating the Excel service! "); Return;} // set to invisible, so that the Excel window m_exlapp.setvisible (false) is not displayed ); //// // The path to which the application is located ////////////// //// cstring theapppath; //// the code for obtaining the root directory of the application is omitted /////////////////////////// /// // cstring temppath = theapppath + "shipinfotemp.xls "; // excel template path m_exlbooks.attachdispatch (m_exlapp.getworkbooks (), true); m_exlbook.attachdispatch (m_exlbooks.add (_ variant_t) T Emppath), true); // load the Excel template m_exlsheets.attachdispatch (m_exlbook.getsheets (), true); // load the sheet page // modify the name of the sheet page. The first page starts from 1, not 0! This applies to numbers. Note that a m_exlsheet.attachdispatch (m_exlsheets.getitem (_ variant_t (long) 1), true); m_exlsheet.setname ("Typhoon 1106 "); // dynamically change the number of lines of ship information based on the number of ships affected by the actual typhoon. Here, we assume that only one line is added, and the range rangeselect; rangeselect is inserted before the 12th line. attachdispatch (m_exlsheet.getrange (_ variant_t ("A12"), _ variant_t ("D12"), true); range rangeinsert = rangeselect. getentirerow (); // select 12th rows. // here we will describe-4121, in ASP. net, VB and other tools, indicates xlshiftdown, indicates to squeeze the row down, VC does not find, so you can only use a direct value of rangeinsert. insert (colevariant (long)-4121), vtmissing); // insert rangeselect before 12 rows. releasedispatch (); rangeinsert. releasedispatch (); // start to fill in m_exlrge.attachdispatch (m_exlsheet.getcells (), true) to each cell; // This segment can be cyclically inserted into the ship information, here we will insert a ship as an example m_exlrge.setitem (_ variant_t (long) 8), _ variant_t (long) 1), _ variant_t ("1 ")); m_exlrge.setitem (_ variant_t (long) 8), _ variant_t (long) 2), _ variant_t ("Snow Dragon"); m_exlrge.setitem (_ variant_t (long) 8), _ variant_t (long) 3), _ variant_t ("201106270800"); m_exlrge.setitem (_ variant_t (long) 8), _ variant_t (long) 4), _ variant_t ("/Nansha"); m_exlrge.setitem (_ variant_t (long) 8), _ variant_t (long) 5 ), _ variant_t ("3901n"); m_exlrge.setitem (_ variant_t (long) 8), _ variant_t (long) 6), _ variant_t ("12138e "));

// Save the edited content as a new file. The file name here can be dynamically generated as needed. Extract (theapppath + "newdata.xls", vtmissing, vtmissing); // release the object merge (); m_exlsheets.releasedispatch (); m_exlbook.releasedispatch (); m_exlbooks.releasedispatch (); // m_exlapp must exit the program; otherwise, an Excel process will reside in the memory after the program ends, in addition, when the program runs repeatedly, m_exlapp.quit (); m_exlapp.releasedispatch ();} The code is successfully tested in the Windows XP system, Excel 2003, and vc6 environments.