C # export data to an Excel template

When I made a report today, I encountered the problem of multiple headers, and the corresponding report formats were the same. Therefore, a report template is used.

Step 1: Introduce Microsoft. Office. InterOP. Excel; system. reflection; namespace to the current project.

Step 2: obtain the data to be exported;

Step 3: Create an Excel applicationProgram:

 
//You need to add Microsoft. Office. InterOP. Excel referenceMicrosoft. Office. InterOP. Excel. Application APP =NewMicrosoft. Office. InterOP. Excel. Application ();
 

If the app is null, it indicates that "the Excel component is missing on the server and office software needs to be installed ";

Step 4: Set app attributes and perform the following operations:

App. Visible = False  ; App. usercontrol = True  ; Microsoft. Office. InterOP. Excel. workbooks = App. workbooks; Microsoft. Office. InterOP. Excel. _ workbook = Workbooks. Add (server. mappath ( "  ~ /Template.xlsx  " )); //  Load Template Microsoft. 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 ; //  No worksheet in the workbook. 

Step 5: insert data (to an Excel template) based on the obtained data );

             //  Write data. The Excel Index starts from 1.              For ( Int I = 1 ; I <= rowcount; I ++ ){  Int Row _ = 2 + I; // In an Excel template, the header and the title line occupy two rows and need to be modified according to the actual template;                  Int Dt_row = I- 1 ; //  The row of the datatable starts from 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] [ "  Student ID  "  ]. Tostring ();} 

Step 6: Set the style of imported data:

//Adjust the Excel Style.Microsoft. Office. InterOP. Excel. Range Rg = worksheet. cells. get_range ("A3", Worksheet. cells [rowcount +2,8]); RG. Borders. linestyle=1;//Add a border to the cell.Worksheet. Columns. autofit ();//Automatically adjust the column width.
 

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

//Missing is in the system. Reflection namespace.StringSavapath ="~ /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 );
 

For details, see the source code.