C#.NET Excel汇出功能

来源：互联网发布：淘宝办公用品店铺编辑：程序博客网时间：2024/04/27 23:14

    public void OutputExcel(System.Data.DataTable dt, string str)
    {
        //dv为要输出到Excel的数据，str为标题名称
        GC.Collect();
        Excel.Application excel;// = new Application();
        int rowIndex = 4;
        int colIndex = 1;

Excel._Workbook xBk;
Excel._Worksheet xSt;

excel = new Excel.ApplicationClass();

xBk = excel.Workbooks.Add(true);

xSt = (Excel._Worksheet)xBk.ActiveSheet;

        //
        //取得标题
        //
        int r = 0;
        int c = 0;
        for (c = 0; c < dt.Columns.Count; c++)
        {
            colIndex++;
            excel.Cells[4, colIndex] = dt.Columns[c].Caption;
            xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
        }

        //
        //取得表格中的数据
        //

        for (r = 0; r < dt.Rows.Count; r++)
        {
            rowIndex++;
            colIndex = 1;
            for (c = 0; c < dt.Columns.Count; c++)
            {
                colIndex++;

                    excel.Cells[rowIndex, colIndex] = dt.Rows[r][c].ToString();
            }
        }
        //
        ////加载一个合计行
        ////
        int rowSum = rowIndex;
        int colSum = 2;

        //取得整个报表的标题
        //
        excel.Cells[2, 2] = str;
        //
        //设置整个报表的标题格式
        //
        xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
        xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
        //
        //设置报表表格为最适应宽度
        //
        xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
        xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
        //
        //设置整个报表的标题为跨列居中
        //
        xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
        xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
        //
        //绘制边框
        //
        xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
        xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
        xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
        xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
        xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗
        //
        //显示效果
        //
        excel.Visible = true;

//xSt.Export(Server.MapPath(".")+""+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
xBk.SaveCopyAs(Server.MapPath("..\\File\\") + "" + "Account_Export" + ".xls");

//ds = null;
xBk.Close(false, null, null);

        excel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
        xBk = null;
        excel = null;
        xSt = null;
        GC.Collect();
        string path = Server.MapPath("..\\File\\Account_Export.xls");

        System.IO.FileInfo file = new System.IO.FileInfo(path);
        Response.Clear();
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
        // 添加头信息，指定文件大小，让浏览器能够显示下载进度
        Response.AddHeader("Content-Length", file.Length.ToString());
        // 指定返回的是一个不能被客户端读取的流，必须被下载
        Response.ContentType = "application/ms-excel";
        // 把文件流发送到客户端
        Response.WriteFile(file.FullName);
        // 停止页面的执行

Response.End();
}