C # export an Excel report

Before use, add reference: Microsoft. Office. Interop. Excel, and then add the following statement on the call page:

 

Using Excel = Microsoft. Office. Interop. Excel;

/** // <Summary>
/// Export an Excel file
/// </Summary>
/// <Param name = "dt"> data source DataTable </param>
/// <Param name = "saveFile"> Save file name with path </param>
/// <Param name = "title"> title of an Excel sheet </param>
Private void exportExcel (DataTable dt, string saveFile, string title)
...{
RptExcel = new Microsoft. Office. Interop. Excel. Application ();
If (rptExcel = null)
...{
MessageBox. Show ("EXcel cannot be opened. Please check whether Excel is available or whether Excel is installed", "system prompt ");
Return;
}
Int rowCount = dt. Rows. Count; // number of Rows
Int columnCount = dt. Columns. Count; // Number of Columns
Float percent = 0; // export progress

This. Cursor = Cursors. WaitCursor;
// Save the cultural environment
System. Globalization. CultureInfo currentCI = System. Threading. Thread. CurrentThread. CurrentCulture;
System. Threading. Thread. CurrentThread. CurrentCulture = new System. Globalization. CultureInfo ("en-US ");

Excel. Workbook workbook = rptExcel. Workbooks. Add (Excel. XlWBATemplate. xlWBATWorksheet );
Excel. Worksheet worksheet = (Excel. Worksheet) workbook. Sheets. get_Item (1 );
Worksheet. Name = "report"; // Name of a sheet

// RptExcel. Visible = true; // open the exported Excel File

Worksheet. Cells [1, 1] = title; // table title

// Fill in the column title
For (int I = 0; I <columnCount; I ++)
...{
Worksheet. Cells [2, I + 1] = dt. Columns [I]. ColumnName;
}

// Create an object array to store the data of the DataTable. This is more efficient than directly filling the data of the Datateble into worksheet. Cells [row, col ].
Object [,] objData = new object [rowCount, columnCount];

// Fill in the content to the object Array
For (int r = 0; r <rowCount; r ++)
...{
For (int col = 0; col <columnCount; col ++)
...{
ObjData [r, col] = dt. Rows [r] [col]. ToString ();
}

Percent = (float) (r + 1) * 100)/rowCount;
This. panelProgress. Visible = true; // displays the progress bar.
This. lblPercents. Text = percent. ToString ("n") + "% ";
This. progressBar1.Value = Convert. ToInt32 (percent );

System. Windows. Forms. Application. DoEvents ();
}
// Assign the value of the object array to the Excel Object
Excel. Range range = worksheet. get_Range (worksheet. Cells [3, 1], worksheet. Cells [rowCount + 2, columnCount]);
Range. NumberFormat = "@"; // sets the digital text format.
Range. Value2 = objData;

// Set the format
Worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [1, columnCount]). MergeCells = true; // merge Cells
Worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [1, columnCount]). HorizontalAlignment = Excel. XlVAlign. xlVAlignCenter; // align
Worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [1, columnCount]). RowHeight = 38;
Worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [1, columnCount]). Font. Bold = true;
Worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [1, columnCount]). Font. Name = "";
Worksheet. get_Range (worksheet. Cells [1, 1], worksheet. Cells [1, columnCount]). Font. Size = 16;
Worksheet. get_Range (worksheet. Cells [2, 1], worksheet. Cells [rowCount + 2, columnCount]). Borders. LineStyle = 1; // set the border
Worksheet. get_Range (worksheet. Cells [2, 1], worksheet. Cells [rowCount, columnCount]). Columns. AutoFit (); // set the cell width to adaptive

// Restore the cultural environment
System. Threading. Thread. CurrentThread. CurrentCulture = currentCI;
Try
...{
// RptExcel. Save (saveFile); // automatically create a new Excel file and Save it in "My Documents". This method can be used without SaveFileDialog.
Workbook. Saved = true;
Workbook. SaveCopyAs (saveFile); // save as a copy in an existing document
This. Cursor = Cursors. Default;
This. panelProgress. Visible = false; // hide the progress bar
MessageBox. Show ("congratulations, the data has been successfully exported as an Excel file! "," Exported ");
}
Catch (Exception ex)
...{
MessageBox. Show ("An error occurred while exporting the file. The file may be being opened. Specific cause:" + ex. Message, "error Message ");
}
Finally
...{
Dt. Dispose ();
RptExcel. Quit ();
System. Runtime. InteropServices. Marshal. ReleaseComObject (rptExcel );
System. Runtime. InteropServices. Marshal. ReleaseComObject (worksheet );
System. Runtime. InteropServices. Marshal. ReleaseComObject (workbook );
GC. Collect ();
KillAllExcel ();
}
}
/** // <Summary>
/// Obtain all Excel Processes
/// </Summary>
/// <Returns> All Excel processes </returns>
Private List <Process> GetExcelProcesses ()
...{
Process [] processes = Process. GetProcesses ();
List <Process> excelProcesses = new List <Process> ();

For (int I = 0; I <processes. Length; I ++)
...{
If (processes [I]. ProcessName. ToUpper () = "EXCEL ")
ExcelProcesses. Add (processes [I]);
}

Return excelProcesses;
}
Private void KillAllExcel ()
...{
List <Process> excelProcess = GetExcelProcesses ();
For (int I = 0; I <excelProcess. Count; I ++)
...{
ExcelProcess [I]. Kill ();
}
}

From: http://hi.baidu.com/