[Stick] C # How to execute an Excel macro Template

If a project involves exporting report data using a report template developed in Excel, it is generally called by using VBA as a macro in an Excel report. That is, the Report Template is developed using the VBA provided by Excel, and then the template program is placed in a fixed directory. These report templates are called in the management software. In this way, you do not need to use other report tools for development. Because Excel is powerful, it is more than enough for general application reports to be used for development. In addition, the development cost is very low, and you do not need to purchase other professional report development software to directly use office development. The macro template developed in Excel must use upper-layer programs to call the macro template. I have previously developed Excel templates at work and used C # programs to call these templates. The rule is to first enter the data required by the report to the able or dataset, and then export the data in the datatable or dataset to the Excel file.

The code for exporting an Excel template is as follows:

Namespace exceltest
{
Public class exeltemplate
{
Private Static excel. Application excelapp; // define a Excel application object
Private Static excel. _ workbook excelwb; // define a Excel Workbook object
Private Static excel. _ worksheet excelws; // define a Excel Workbook Worksheet

// Put the main data required by the report into dtheader, detailed data into dtdetail, The called Template Name Is strtemplatefilename, And the exported report name is stroutfilename

Public static bool fillcontent (string strtemplatefilename, string stroutfilename, system. Data. datatable dtheader, system. Data. datatable dtdetail)
{

Bool flag = true;
Fileinfo = new fileinfo (strtemplatefilename );

System. Globalization. cultureinfo currentci = system. Threading. thread. currentthread. currentculture;
System. Threading. thread. currentthread. currentculture = new system. Globalization. cultureinfo ("En-us ");

Try
{
// Start Excel and get application object.
Excelapp = new excel. Application ();
Excelapp. Visible = false;

// Get a new workbook.
Excelwb = (Excel. _ workbook) (excelapp. workbooks. Add (strtemplatefilename ));

// Fill content. Note that the header and detail correspond to the header and detail sheet pages in the template file respectively. These two sheets are used to store the master data or detailed data.
If (! Fillworksheet ("Header", dtheader) return false;
If (! Fillworksheet ("detail", dtdetail) return false;

// Run macro.

Excelapp. run ("setdata", missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value,
Missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value );

Excelapp. displayalerts = false;
// ================================================ ========================================================== ======================================
// Delete header and detail.
// (Excel. _ worksheet) excelwb. Sheets ["header information"]). Delete ();
// (Excel. _ worksheet) excelwb. Sheets ["detail information"]). Delete ();

 

// ================================================ ========================================================== ======================================
Excelapp. displayalerts = true;

// Delete old file.
File. Delete (stroutfilename );
// Save Excel file.
Excelwb. saveas (stroutfilename, missing. value, missing. value, missing. value, missing. value, missing. value, xlsaveasaccessmode. xlexclusive, missing. value, missing. value, missing. value, missing. value, missing. value );

// Quit excel.
Excelwb. Close (false, stroutfilename, missing. Value );//*******************

Marshalreleasecomobject (excelwb );

Excelapp. Quit ();


// Kill Excel application.
// Killprocess ("Excel ");//******************

}
Catch (exception ex)
{
Throw ex;
Flag = false;
}
Finally
{
Marshalreleasecomobject (excelapp );
GC. Collect ();
}

Return flag;
}

// Put the main data required by the report into dtheader, detailed data into dtdetail, The called Template Name Is strtemplatefilename, And the exported report name is stroutfilename

Public static bool fillcontent (string strtemplatefilename, string stroutfilename, system. Data. dataset dsdata)
{

Bool flag = true;
Fileinfo = new fileinfo (strtemplatefilename );

System. Globalization. cultureinfo currentci = system. Threading. thread. currentthread. currentculture;
System. Threading. thread. currentthread. currentculture = new system. Globalization. cultureinfo ("En-us ");

Try
{
// Start Excel and get application object.
Excelapp = new excel. Application ();
Excelapp. Visible = false;

// Get a new workbook.
Excelwb = (Excel. _ workbook) (excelapp. workbooks. Add (strtemplatefilename ));
// Fill content.
// If (! Fillworksheet ("Header", dtheader) return false;
// If (! Fillworksheet ("detail", dtdetail) return false;

// Fill content. note that the sheet page in the corresponding template file is not specified here, But dsdata is specified. tables [I]. tablename is the name of the sheet page, which is convenient and flexible, and multiple sheet can be used to store the master data or detailed data.
For (INT I = 0; I <dsdata. Tables. Count; I ++)
{
If (! Fillworksheet (dsdata. Tables [I]. tablename, dsdata. Tables [I])
Return false;
}

// Run macro.

Excelapp. run ("setdata", missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value,
Missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value, missing. value );

Excelapp. displayalerts = false;
// ================================================ ========================================================== ======================================
// Delete header and detail.
// (Excel. _ worksheet) excelwb. Sheets ["header information"]). Delete ();
// (Excel. _ worksheet) excelwb. Sheets ["detail information"]). Delete ();

 

// ================================================ ========================================================== ======================================
Excelapp. displayalerts = true;

// Delete old file.
File. Delete (stroutfilename );
// Save Excel file.

Excelwb. saveas (stroutfilename, missing. value, missing. value, missing. value, missing. value, missing. value, xlsaveasaccessmode. xlexclusive, missing. value, missing. value, missing. value, missing. value, missing. value );

// Quit excel.
Excelwb. Close (false, stroutfilename, missing. Value );//*******************

Marshalreleasecomobject (excelwb );

Excelapp. Quit ();

}
Catch (exception ex)
{
Throw ex;
Flag = false;
}
Finally
{
Marshalreleasecomobject (excelapp );
GC. Collect ();
}

Return flag;
}

// When using EXCEL to export a report, the Excel process on the server is easy to die, causing the resource to be released. Therefore, you need to use this method to release the dead process.

Private Static void externalreleasecomobject (Object objcom)
{
Try
{
Int I = 1;
If (objcom! = NULL & system. runtime. interopservices. Marshal. iscomobject (objcom ))
{
Do
{
I = system. runtime. interopservices. Marshal. releasecomobject (objcom );
} While (I> 0 );
}
}
Finally
{
Objcom = NULL;
}
}

}