前言：
用傳統(tǒng)的導(dǎo)出方法：只是將DataGrid信息用html輸出，文件名后輟是.xls而已。如果想將這個(gè)方法導(dǎo)入到Sql Server 中，會(huì)提示出錯(cuò)。因?yàn)樗皇菢?biāo)準(zhǔn)的Excel格式文件。
用本例中的導(dǎo)出方法：會(huì)輸出標(biāo)準(zhǔn)的Excel格式文件，非常穩(wěn)定，不會(huì)死鎖Excel進(jìn)程，支持中文文件名，支持表頭導(dǎo)出，支持大多數(shù)數(shù)據(jù)庫(kù)導(dǎo)入。
實(shí)現(xiàn)算法：
利用Excel組件將DataGrid控件內(nèi)容生成Excel臨時(shí)文件，并存放在服務(wù)器上，并用Response方法將生成的Excel文件下載到客戶(hù)端，再將生成的臨時(shí)文件刪除。
具體步驟：
1.在項(xiàng)目中引用Excel組件
Interop.Excel.dll 文件版本1.3.0.0
2.項(xiàng)目中應(yīng)有一個(gè)目錄（本例中Template目錄），以便存放Excel文件（名字自己定）
3.導(dǎo)入方法類(lèi)
以下是代碼片段：
/// <summary>
/// 將DataGrid中數(shù)據(jù)導(dǎo)出至Excel，生成標(biāo)準(zhǔn)的Excel文件
/// </summary>
/// <param name="grid">DataGrid控件ID</param>
/// <param name="fileName">導(dǎo)出文件名</param>
protected void ExportToExcel(System.Web.UI.WebControls.DataGrid grid,string fileName)
{
string templetFilePath;
templetFilePath = Server.MapPath("../").ToString() + @"Template\";
object missing = Missing.Value;
Excel.Application app;
Excel.Workbook workBook;
Excel.Worksheet workSheet;
Excel.Range range;
//創(chuàng)建一個(gè)Application對(duì)象并使其不可見(jiàn)
app = new Excel.ApplicationClass();
app.Visible=false;
//打開(kāi)模板文件，得到WorkBook對(duì)象
//workBook = app.Workbooks.Open(templetFilePath + "SuperTemplet.xls", missing, missing, missing, missing, missing,
// missing, missing, missing, missing, missing, missing, missing);
//創(chuàng)建一個(gè)WorkBook對(duì)象
workBook = app.Workbooks.Add(missing);
//得到WorkSheet對(duì)象
workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
int rowCount = grid.Items.Count + 1; //DataTable行數(shù)+GirdHead
int colCount = grid.Columns.Count; //DataTable列數(shù)
//利用二維數(shù)組批量寫(xiě)入
string[,] arr = new string[rowCount, colCount];
for (int j = 0; j < rowCount; j++)
{
for (int k = 0; k < colCount; k++)
{
if (j == 0)
{
arr[j, k] = grid.Columns[k].HeaderText;
}
else
{
arr[j, k] = grid.Items[j - 1].Cells[k].Text.ToString();
}
}
}
range = (Excel.Range)workSheet.Cells[1, 1]; //寫(xiě)入Exel的坐標(biāo)
range = range.get_Resize(rowCount, colCount);
range.Value = arr;
workBook.SaveAs(templetFilePath + fileName, missing, missing, missing, missing, missing,Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
if (workBook.Saved)
{
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
}
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
workSheet = null;
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
workBook = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();//強(qiáng)制代碼垃圾回收
//下載文件
DownLoadFile(templetFilePath,fileName);
}
4.下載文件方法類(lèi)
以下是代碼片段：
/// <summary>
/// 下載服務(wù)器文件到客戶(hù)端
/// </summary>
/// <param name="_FilePath">文件路徑</param>
/// <param name="_FileName">文件名</param>
/// <returns>返回 bool型</returns>
private bool DownLoadFile(string _FilePath,string _FileName)
{
try
{
System.IO.FileStream fs = System.IO.File.OpenRead(_FilePath+_FileName);
byte[] FileData = new byte[fs.Length];
fs.Read(FileData, 0, (int)fs.Length);
Response.Clear();
Response.AddHeader("Content-Type", "application/ms-excel");
string FileName = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(_FileName));
Response.AddHeader("Content-Disposition", "inline;filename=" + System.Convert.ToChar(34) + FileName + System.Convert.ToChar(34));
Response.AddHeader("Content-Length", fs.Length.ToString());
Response.BinaryWrite(FileData);
fs.Close();
//刪除服務(wù)器臨時(shí)文件
System.IO.File.Delete(_FilePath+_FileName);
Response.Flush();
Response.End();
return true;
}
catch(Exception ex)
{
ex.Message.ToString();
return false;
}
}
5.應(yīng)用方法
以下是代碼片段：
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
this.ExportToExcel(grdBudget,"中國(guó)石油大港油田油管廠(chǎng)發(fā)料記錄.xls");//grdBudget 是DataGrid的ID
}