DataTable 导出到Excel

#region DataTable 导出到Excel
/// Author: jy Wang
/// Date: 2008-7-28
/// <summary>
/// DataTable 导出到Excel
/// </summary>
/// <param name="colName">列名数组</param>
/// <param name="colWidth">列宽数组</param>
/// <param name="reprotTitle">报表标题</param>
/// <param name="exprortFile">导出文件名</param>
/// <param name="logoPosition">图标的位置</param>
/// <param name="table">要导出的datatable</param>
/// <param name="response">HttpResponse</param>
public void Export(string[] colName, int[] colWidth, string reprotTitle, string exprortFile, string logoPosition, System.Data.DataTable table, HttpResponse response, HttpRequest request)
{
string fileName = "" + exprortFile + ".xls";//客户端保存的文件名 
string filePath = request.PhysicalApplicationPath +"Exported\\" + exprortFile + ".xls";//路径 

if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}

GC.Collect();
Excel.Application excel = new Excel.Application();
Excel.Workbook book = excel.Workbooks.Add(Missing.Value);
Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
// 
//取得标题 
// 
for (int col = 0; col < table.Columns.Count; col++)
{

sheet.Cells[5, col + 1] = colName[col];
sheet.get_Range(excel.Cells[5, col + 1], excel.Cells[5, col + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //设置标题格式为居中对齐 
sheet.get_Range(excel.Cells[5, col + 1], excel.Cells[5, col + 1]).Interior.ColorIndex = 15; //设置为浅灰色，共计有56种 
sheet.get_Range(excel.Cells[5, col + 1], excel.Cells[5, col + 1]).ColumnWidth = colWidth[col];
sheet.get_Range(excel.Cells[5, col + 1], excel.Cells[5, col + 1]).RowHeight = 25;
}

// 
//取得数据 
// 

for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
sheet.Cells[row + 7, col + 1] = table.Rows[row][col].ToString();
}
}

// 
//取得整个报表的标题 
// 
excel.Cells[2, 5] = reprotTitle;
// 
//设置整个报表的标题格式 
// 
sheet.get_Range(excel.Cells[2, 5], excel.Cells[2, 5]).Font.Bold = true;
sheet.get_Range(excel.Cells[2, 5], excel.Cells[2, 5]).Font.Size = 22;


//设置内容字体
sheet.get_Range(excel.Cells[5, 1], excel.Cells[table.Rows.Count + 6, table.Columns.Count + 1]).Font.Size = 9;
sheet.get_Range(excel.Cells[5, 1], excel.Cells[5, table.Columns.Count + 1]).Font.Bold = true;

//插入图标
try
{
sheet.Shapes.AddPicture(logoPosition, Microsoft.Office.Core.MsoTriState.msoCTrue, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 5, 60, 60);
}
catch (Exception ee)
{
throw new Exception("Summary Report: 插入图标失败 logoPosition::" + logoPosition +"-"+ ee.Message);

}
// 
//自动换行 
// 
sheet.get_Range(excel.Cells[6, 1], excel.Cells[table.Rows.Count + 6, table.Columns.Count]).Select();
sheet.get_Range(excel.Cells[6, 1], excel.Cells[table.Rows.Count + 6, table.Columns.Count]).WrapText = true;

// 
//绘制边框 
// 
sheet.get_Range(excel.Cells[5, 1], excel.Cells[table.Rows.Count + 6, table.Columns.Count]).Borders.LineStyle = 1;
sheet.get_Range(excel.Cells[5, 1], excel.Cells[table.Rows.Count + 6, table.Columns.Count]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//设置左边线加粗 
sheet.get_Range(excel.Cells[5, 1], excel.Cells[table.Rows.Count + 6, table.Columns.Count]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//设置上边线加粗 
sheet.get_Range(excel.Cells[5, 1], excel.Cells[table.Rows.Count + 6, table.Columns.Count]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//设置右边线加粗 
sheet.get_Range(excel.Cells[5, 1], excel.Cells[table.Rows.Count + 6, table.Columns.Count]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//设置下边线加粗 


//文件保存地点
book.Close(true, filePath, Missing.Value);
excel.Quit();
GC.Collect();

System.IO.FileInfo fileInfo = new System.IO.FileInfo(filePath);
response.Clear();
response.ClearContent();
response.ClearHeaders();
response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
response.AddHeader("Content-Length", fileInfo.Length.ToString());
response.AddHeader("Content-Transfer-Encoding", "binary");
response.ContentType = "application/octet-stream";
response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.WriteFile(fileInfo.FullName);
response.Flush();
System.IO.File.Delete(filePath);
response.End();
}
#endregion