C # Export data to an Excel template

Today, when the report encountered a multi-header problem, and the corresponding report format is the same. So the report template is used in the way to do it.

First step: Introduce: microsoft.office.interop.excel;system.reflection; namespaces in the current project of development.

The second step: get the data to be exported;

Step three: Create an Excel application:

new Microsoft.Office.Interop.Excel.Application ();   

If the app = null, the Excel component on the server is missing on the server and the Office software needs to be installed;

Fourth step: Set the app properties and do the following:

App. Visible =false; App. UserControl=true; Microsoft.Office.Interop.Excel.Workbooks Workbooks=app.  Workbooks; Microsoft.office.interop.excel._workbook Workbook= Workbooks. ADD (Server.MapPath ("~/template.xlsx"));//Loading TemplatesMicrosoft.Office.Interop.Excel.Sheets Sheets =workbook.  Sheets; Microsoft.office.interop.excel._worksheet Worksheet= (Microsoft.office.interop.excel._worksheet) Sheets.get_item (1);//The first working thin.   if(Worksheet = =NULL)      return;//There are no worksheets in the workbook.

Fifth step: Insert data (into Excel template) according to the obtained data;

//writes the data, and the Excel index starts at 1.              for(inti =1; I <= RowCount; i++)            {                intRow_ =2+ i;//Excel template on the table header and header row accounted for 2 lines, according to the actual template needs to be modified;                intDt_row = i-1;//The row for the DataTable starts at 0. Worksheet. Cells[row_,1] =i.tostring (); Worksheet. Cells[row_,2] = dt. rows[dt_row]["name"].                ToString (); Worksheet. Cells[row_,3] = dt. rows[dt_row]["School Number"].            ToString (); }

Sixth step: Set the style of the imported data:

// adjusts the style of Excel.             Microsoft.Office.Interop.Excel.Range RG = worksheet. Cells.get_range ("A3"28]);             1 // cell and border.             // adjust column widths automatically. 

Seventh Step: Save the exported Excel report to the server for download.

            // Missing under the System.Reflection namespace.             string"~/temp/t1_" + DateTime.Now.ToString ("  YYYYMMDDHHMMSS"". xlsx";            Workbook. SaveAs (Server.MapPath (Savapath), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

C # Export data to Excel templates (go)