DataGrid内容导出标准的Excel格式文件

       用传统的导出方法：只是将DataGrid信息用html输出，文件名后辍是.xls而已。如果想将这个方法导入到Sql Server 中，会提示出错。因为它不是标准的Excel格式文件。

       用本例中的导出方法：会输出标准的Excel格式文件，非常稳定，不会死锁Excel进程，支持中文文件名，支持表头导出，支持大多数数据库导入。

实现算法：

   利用Excel组件将DataGrid控件内容生成Excel临时文件，并存放在服务器上，并用Response方法将生成的Excel文件下载到客户端，再将生成的临时文件删除。

具体步骤：

1.在项目中引用Excel组件
Interop.Excel.dll 文件版本1.3.0.0

2.项目中应有一个目录（本例中Template目录），以便存放Excel文件（名字自己定）

3.导入方法类

以下是代码片段：         /// <summary>         /// 将DataGrid中数据导出至Excel，生成标准的Excel文件         /// </summary>         /// <param name="grid">DataGrid控件ID</param>         /// <param name="fileName">导出文件名</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;             //创建一个Application对象并使其不可见             app = new Excel.ApplicationClass();             app.Visible=false;                         //打开模板文件，得到WorkBook对象             //workBook = app.Workbooks.Open(templetFilePath + "SuperTemplet.xls", missing, missing, missing, missing, missing,             //    missing, missing, missing, missing, missing, missing, missing);             //创建一个WorkBook对象             workBook = app.Workbooks.Add(missing);             //得到WorkSheet对象             workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);             int rowCount = grid.Items.Count + 1;  //DataTable行数+GirdHead             int colCount = grid.Columns.Count; //DataTable列数             //利用二维数组批量写入             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]; //写入Exel的坐标             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();//强制代码垃圾回收                         //下载文件             DownLoadFile(templetFilePath,fileName);         }

4.下载文件方法类

以下是代码片段：  /// <summary>         /// 下载服务器文件到客户端         /// </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();                 //删除服务器临时文件                 System.IO.File.Delete(_FilePath+_FileName);                 Response.Flush();                 Response.End();     return true;    }    catch(Exception ex)    {                 ex.Message.ToString();     return false;    }   }

5.应用方法

以下是代码片段： protected void btnExportToExcel_Click(object sender, EventArgs e)         {                         this.ExportToExcel(grdBudget,"中国石油大港油田油管厂发料记录.xls");//grdBudget 是DataGrid的ID                }