DataGrid内容导出Excel文件（C#）

    今天朋友问我有没有做过将多个DataGrid中的内容导出到一个Excel文件中，每个DataGrid放在一个Sheet中，在此之前，我只用JS导出过Excel文件，没有用Excel组件做过，正好今天比较空闲，就从网上查了资料（http://www.wsoft.net/Index/Catalog53/225.aspx），写得非常详细，就直接拿来用了

    只是有一点儿不太符合要求，需求是“多个DataGrid导入到同一个Excel文件中，且每个DataGrid放在一个Sheet中”。于是自己动手改了下，代码如下：（Excel文件中默认有三个Sheet，超出三个需要创建，可能我这种方法比较笨，哪位强人有好的方法不防提供下）

 

Code
        /// <summary>
        /// 将DataGrid中数据导出至Excel，生成标准的Excel文件
        /// （注：此方法只能生成在服务器上，如果需要生成在客户端，可以先在服务器上生成文件然后下载到客户端）
        /// </summary>
        /// <param name="al_grid">DataGrid控件列表</param>
        /// <param name="fileName">导出文件名</param>
        protected void ExportToExcel(ArrayList al_grid,string fileName)
        {
            int grid_count = al_grid.Count;
            string templetFilePath;
            templetFilePath = "e:\\test1\\";//这里指定要生成的文件存放位置
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application app;
            Microsoft.Office.Interop.Excel.Workbook workBook;
            Microsoft.Office.Interop.Excel.Worksheet[] workSheet = new Microsoft.Office.Interop.Excel.Worksheet[grid_count];
            Microsoft.Office.Interop.Excel.Range[] range = new Microsoft.Office.Interop.Excel.Range[grid_count];


            //创建一个Application对象并使其不可见
            app = new Microsoft.Office.Interop.Excel.ApplicationClass();
            app.Visible=false;

            
            //创建一个WorkBook对象
            workBook = app.Workbooks.Add(missing);

            for(int i=0;i<grid_count;++i)
            {
                System.Web.UI.WebControls.DataGrid grid = (System.Web.UI.WebControls.DataGrid)al_grid[i];
                //得到WorkSheet对象

                if(i >= 3)
                {
                    workSheet[i] = (Microsoft.Office.Interop.Excel.Worksheet)workBook.
                        Worksheets.Add(Type.Missing, workBook.ActiveSheet,
                        Type.Missing, Type.Missing);
                }
                else
                {
                    workSheet[i] = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(i+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[i] = (Microsoft.Office.Interop.Excel.Range)workSheet[i].Cells[1, 1]; //写入Exel的坐标
                range[i] = range[i].get_Resize(rowCount, colCount);
                range[i].Value2 = arr;
            }
            
            workBook.SaveAs(templetFilePath + fileName, missing, missing, missing, missing, missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing,missing,missing,missing);




            if (workBook.Saved)
            {
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Quit();
            }

            for(int i=0;i<grid_count;++i)
            {
                if (range[i] != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(range[i]);
                    range[i] = null;
                }

                if (workSheet[i] != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet[i]);
                    workSheet[i] = 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);
        }



        /// <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;
            }
        }



        //生成Excel
        private void btnExcel_Click(object sender, System.EventArgs e)
        {
            ArrayList al = new ArrayList();
            al.Add(dgInfo1);//把所有datagrid放入arraylist
            al.Add(dgInfo2);
            al.Add(dgInfo3);
            al.Add(dgInfo4);
            al.Add(dgInfo5);
            this.ExportToExcel(al,"userlist.xls");
        }

 

    当然，要在项目中增加Microsoft.Office.Interop.Excel组件:)

 

转载于:https://www.cnblogs.com/flycantus/archive/2008/12/04/1347683.html