datatable导出EXCEL

  /// <summary>
    /// 导出
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void cmdExportExcel_Click(object sender, EventArgs e)
    {
        string templateFileName = System.Configuration.ConfigurationManager.AppSettings["CSV_OUT_PATH"] +
   "余额统计报告" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
        templateFileName = System.Web.HttpContext.Current.Server.MapPath(templateFileName);

        if (excelWrite(templateFileName))
        {
            excelDownload(templateFileName);
        }
    }
    private bool excelWrite(string fileName)
    {
        enddt = Session["enddt"] as DataTable;
        DataTable dataTable = new DataTable();
        if (enddt == null)
        {
            return false;
        }
        foreach (DataColumn dc in enddt.Columns)
        {
            dataTable.Columns.Add(dc.ColumnName, typeof(string));
        }
        for (int i = 0; i < enddt.Rows.Count; i++)
        {
            DataRow dr = dataTable.NewRow();
            for (int j = 0; j < enddt.Columns.Count; j++)
            {
                dr[j] = enddt.Rows[i][j].ToString();
            }
            dataTable.Rows.Add(dr);
        }

        DataTable exportTable = dataTable.Copy();
        DataRow title = exportTable.NewRow();
        title[0] = "余额统计报告";
        exportTable.Rows.InsertAt(title, 0);

        title = exportTable.NewRow();
        exportTable.Rows.InsertAt(title, 1);

        title = exportTable.NewRow();
        exportTable.Rows.InsertAt(title, 2);

        title = exportTable.NewRow();
        exportTable.Rows.InsertAt(title, 3);

        title = exportTable.NewRow();
        exportTable.Rows.InsertAt(title, 4);
        title = exportTable.NewRow();
        for (int i = 0; i < exportTable.Columns.Count; i++)
        {
            title[i] = "'" + exportTable.Columns[i].ColumnName;
        }
        exportTable.Rows.InsertAt(title, 5);

        exportTable.AcceptChanges();
        return this.writeTableDataToExcel(fileName, exportTable);
    }
    /// <summary>
    /// 写Excel文件。
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="dataTable"></param>
    /// <returns></returns>
    private bool writeTableDataToExcel(string fileName, System.Data.DataTable dataTable)
    {
        if (String.IsNullOrEmpty(fileName))
        {
            return false;
        }

        //创建Application对象
        Microsoft.Office.Interop.Excel.Application xlsApp = null;
        try
        {
            xlsApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlsApp == null)
            {
                return false;
            }

            xlsApp.Visible = false;

            //得到WorkBook对象, 打开已有的文件
            Microsoft.Office.Interop.Excel.Workbook xlsBook = xlsApp.Workbooks.Add(Missing.Value);
            xlsBook.Application.DisplayAlerts = false;
            xlsBook.Application.ActiveWindow.DisplayGridlines = false;

            while (xlsBook.Sheets.Count > 1)
            {
                Microsoft.Office.Interop.Excel.Worksheet delSheet =
                    (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.Sheets[xlsBook.Sheets.Count];
                delSheet.Delete();
            }
            //指定要操作的Sheet
            Microsoft.Office.Interop.Excel.Worksheet xlsSheet =
                (Microsoft.Office.Interop.Excel.Worksheet)xlsBook.Sheets[1];

            Microsoft.Office.Interop.Excel.Range dataRange = xlsSheet.get_Range("A1", Type.Missing);
            int rowCount = dataTable.Rows.Count;
            int columnCount = dataTable.Columns.Count;
            dataRange.get_Resize(rowCount, columnCount + 1);

            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 0; j < columnCount; j++)
                {
                    dataRange.Cells[i + 1, j + 2] = dataTable.Rows[i][j];
                }
            }

            // 首行合并
            Microsoft.Office.Interop.Excel.Range range = xlsSheet.get_Range(dataRange.Cells[1, 2],
                dataRange.Cells[2, columnCount + 1]);
            range.MergeCells = true;
            range.HorizontalAlignment = 3;
            range.Font.Size = 22;
            range.Font.Bold = true;
            range.RowHeight = 20;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = 9;

            // 加边线
            range = xlsSheet.get_Range(dataRange.Cells[5, 2], dataRange.Cells[rowCount, columnCount + 1]);
            range.Borders.LineStyle = 1;
            //标题粗体
            range = xlsSheet.get_Range(dataRange.Cells[6, 2], dataRange.Cells[6, columnCount + 1]);
            range.Font.Bold = true;

            // 第一行表头
            range = xlsSheet.get_Range(dataRange.Cells[5, 2], dataRange.Cells[5, columnCount + 1]);
            range.MergeCells = true;
            range.Borders.LineStyle = 1;
            range.HorizontalAlignment = 3;
            range.Font.Bold = true;
            //格式化
            for (int a = 3; a < columnCount + 2; a++)
            {
                if (a != 5 && a != 8)
                {
                    range = xlsSheet.get_Range(dataRange.Cells[7, a], dataRange.Cells[rowCount + 1, a]);
                    range.NumberFormat = "#,##0.00";
                }
            }
            // 加边线
            range = xlsSheet.get_Range(dataRange.Cells[5, 2], dataRange.Cells[rowCount, columnCount + 1]);
            range.Borders.LineStyle = 1;

            // 列宽自动调整
            range = xlsSheet.get_Range(dataRange.Cells[5, 2], dataRange.Cells[rowCount, columnCount + 1]);
            range.Columns.AutoFit();

            //保存，关闭
            xlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            xlsBook.Close(false, Type.Missing, Type.Missing);
        }
        catch (Exception ex)
        {
            Log.SaveException(ex);
            return false;
        }
        finally
        {
            if (xlsApp != null)
            {
                xlsApp.Quit();
            }
            GC.Collect();
        }
        return true;

 

    }
    /// <summary>
    /// excel下载
    /// </summary>
    /// <param name="templateFileName"></param>
    private void excelDownload(string templateFileName)
    {
        FileInfo downloadFile = new FileInfo(templateFileName);
        if (downloadFile.Exists == true)
        {
            Response.Clear();
            Response.ClearHeaders();
            Response.Buffer = false;
            Encoding encodeShiftJIS = Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/octet-stream";
            Response.AppendHeader("Content-Disposition", "attachment;filename="
                + HttpUtility.UrlEncode(downloadFile.Name, encodeShiftJIS));
            Response.AppendHeader("Content-Length", downloadFile.Length.ToString());
            Response.WriteFile(downloadFile.FullName);
            Response.Flush();
            downloadFile.Delete();
            Response.End();
        }
    }