DataTable Excel

这里是使用Microsoft.Office.Interop.Excel类进行Excel表格操作，核心代码就这些了。麻烦的是服务器上的一些关于access的权限配置。             

   string file = System.AppDomain.CurrentDomain.BaseDirectory + "对账单\\" + filename;

                if (ExcelHelper.DataTableToCSV(dt, file + System.Web.HttpContext.Current.Session["us_Name"] + ".csv", new string[] { "rowId" }))
                {
                    HttpResponse resp;
                    resp = System.Web.HttpContext.Current.Response;
                    resp.Clear();
                    resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                    resp.ContentType = "application/vnd.ms-excel";
                    resp.Charset = "GB2312";

                    resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8) + ".csv");
                    resp.WriteFile(file + System.Web.HttpContext.Current.Session["us_Name"] + ".csv");
                }

 

        public static bool DataTableToCSV(System.Data.DataTable excelTable, string filePath,string[] removeFileds)
        {

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
            app.Visible = false;

            Workbooks workBooks = app.Workbooks; //创建Excel工作簿
            Workbook wBook = workBooks.Add(XlWBATemplate.xlWBATWorksheet);

            Worksheet wSheet = wBook.Worksheets[1] as Worksheet;

            foreach (string item in removeFileds)
            {
                if (excelTable.Columns.Contains(item))
                {
                    excelTable.Columns.Remove(item);
                }
            }

            try
            {


                if (excelTable.Rows.Count > 0)
                {
                    int row = 0;
                    row = excelTable.Rows.Count;
                    int col = excelTable.Columns.Count;

                    //关键性代码，直接关系到性能问题

　　　　　　　　　　　　Array arr = Array.CreateInstance(typeof(String), row, col);

                    for (int i = 0; i < row; i++)
                    {

                        for (int j = 0; j < col; j++)
                        {

                            arr.SetValue(excelTable.Rows[i][j].ToString(), i, j);

                        }

                    }

                    Microsoft.Office.Interop.Excel.Range range = wSheet.get_Range(wSheet.Cells[2, 1], wSheet.Cells[row + 1, col]);
                    range.Value2 = arr; 
                }

                int size = excelTable.Columns.Count;
                for (int k = 0; k < size; k++)
                {

                    wSheet.Cells[1, 1 + k] = excelTable.Columns[k].ColumnName;
                    Range xlsRang = (Range)wSheet.Cells[1, 1 + k];
                    xlsRang.EntireColumn.AutoFit();
                }

                //设置禁止弹出保存和覆盖的询问提示框   
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;
                //保存工作簿   

                wBook.Save();
                wBook.SaveCopyAs(filePath); //自定义保存的文件名称

            }
            catch (Exception err)
            {
                return false;
            }
            finally
            {

                workBooks.Close(); //关闭工作簿
                if (app != null)
                {
                    app.Workbooks.Close();
                    app.Quit(); //退出Excel
                    int intGenerate = System.GC.GetGeneration(app);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //将XLSAPP从内存托管堆中移除
                    app = null;
                    System.GC.Collect(intGenerate);
                }
                GC.Collect(); //强行退出Excel
                app = null;

            }

            return true;
        }