C#匯出Excel報表

在使用前首先需要添加引用：Microsoft.Office.Interop.Excel，然後在調用頁添加如下語句：

 

using Excel = Microsoft.Office.Interop.Excel;

/**//// <summary>
/// 匯出為Excel格式檔案
/// </summary>
/// <param name="dt">作為資料來源的DataTable</param>
/// <param name="saveFile">帶路徑的儲存檔案名稱</param>
/// <param name="title">一個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，請檢查Excel是否可用或者是否安裝好Excel", "系統提示");
return;
}
int rowCount = dt.Rows.Count;//行數
int columnCount = dt.Columns.Count;//列數
float percent = 0;//匯出進度

this.Cursor = Cursors.WaitCursor;
//儲存文化環境
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 = "報表";//一個sheet的名稱

//rptExcel.Visible = true;//開啟匯出的Excel檔案

worksheet.Cells[1, 1] = title;//表標題

//填充欄位標題
for (int i = 0; i < columnCount; i++)
...{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
}

//建立對象數組儲存DataTable的資料，這樣的效率比直接將Datateble的資料填充worksheet.Cells[row,col]高
object[,] objData = new object[rowCount, columnCount];

//填充內容到對象數組
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;//顯示進度條
this.lblPercents.Text=percent.ToString("n") + "%";
this.progressBar1.Value = Convert.ToInt32(percent);

System.Windows.Forms.Application.DoEvents();
}
//將對象數組的值賦給Excel對象
Excel.Range range = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[rowCount + 2, columnCount]);
range.NumberFormat = "@";//設定數字文字格式設定
range.Value2 = objData;

//設定格式
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).MergeCells = true;//合併儲存格
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//置中對齊
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;//設定邊框
worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount, columnCount]).Columns.AutoFit();//設定儲存格寬度為自適應

//恢複文化環境
System.Threading.Thread.CurrentThread.CurrentCulture = currentCI;
try
...{
//rptExcel.Save(saveFile); //自動建立一個新的Excel文檔儲存在“我的文件”裡，如果不用SaveFileDialog就可用這種方法
workbook.Saved=true;
workbook.SaveCopyAs(saveFile);//以複製的形式儲存在已有的文檔裡
this.Cursor = Cursors.Default;
this.panelProgress.Visible = false;//隱藏進度條
MessageBox.Show("恭喜，資料已經成功匯出為Excel檔案！", "成功匯出");
}
catch (Exception ex)
...{
MessageBox.Show("匯出檔案出錯，檔案可能正被開啟，具體原因：" + ex.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>
/// 獲得所有的Excel進程
/// </summary>
/// <returns>所有的Excel進程</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();
}
}

來自: http://hi.baidu.com/