c executes the excel macro template method
- 2020-06-07 05:10:15
- OfStack
For projects that involve exporting report data using Excel report templates, VBA macros are commonly used in Excel reports. First, Excel is used to develop report templates, and then the template program is placed in a fixed directory, and these report templates are called in the management software. This eliminates the need for other reporting tools, as Excel is more powerful and can be used to develop more than just a one-app report. Moreover, the development cost is also very low, no need to buy other professional report development software can be developed directly with Office. Macro templates developed using Excel of course use the upper layer of the program to invoke the macro template. I have developed Excel templates before in my work and have invoked them using the C# program. The rule is to first flush the data required for the report into DataTable or DataSet, and then export the data from DataTable or DataSet to Excel.
The code for exporting 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 master data required for the report into dtHeader , put in the detailed data dtDetail , the template name of the call is strTemplateFileName , 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 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. Notice that the Header and Detail Corresponding to the template file Header and Detail two Sheet Page, the 2 a Sheet It is used to store master or detail 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 master data required for the report into dtHeader , put in the detailed data dtDetail , the template name of the call is strTemplateFileName , the exported report name is strOutFileName
public static bool FillContent(string strTemplateFileName, string strOutFileName, System.Data.DataSet dsdata)
{
bool flag = true;
FileInfo 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 corresponding template file is not indicated here Sheet Page, but specified dsdata.Tables[i].TableName for Sheet The name of the page, so that the flexibility of setting, and so that there can be more than one Sheet It is used to store master or detail 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;
}
// use Excel To export the report on the server Excel Processes tend to die and resources cannot be released, so you need to use this method to release dead processes
private static void MarshalReleaseComObject(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;
}
}
}