下载excel格式的数据

最新推荐文章于 2024-02-22 22:39:00 发布

新笙

最新推荐文章于 2024-02-22 22:39:00 发布

阅读量714

点赞数

分类专栏： asp.net 文章标签： excel c#

原文链接：https://www.cnblogs.com/yachao1120/p/9497935.html

版权

asp.net 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

 public static void DataTable2ExcelNew(System.Data.DataTable dtData, string fileName)
        {
             
            string sheetName = "Sheet1";
//descFile 给一个模板的文件用来存储列名 例如示范 在桌面创建一个111的xlsx文件
            string descFile = "C:\\Desktop\\111.xlsx";
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            FileInfo fileinfo = new FileInfo(descFile);
            ExcelPackage excel = new ExcelPackage(fileinfo);
            ExcelWorksheet sheet = excel.Workbook.Worksheets[sheetName];
            DataTable dt = dtData;
            int row = dt.Rows.Count;    //行 
            sheet.Cells[1, 1].Value = "区域";
            sheet.Cells[1, 2].Value = "产品名称";
            sheet.Cells[1, 3].Value = "规格";
            sheet.Cells[1, 4].Value = "型号";
            sheet.Cells[1, 5].Value = "厂家";
            sheet.Cells[1, 6].Value = "供应商";
            sheet.Cells[1, 7].Value = "单价";
            sheet.Cells[1, 8].Value = "单位";
            sheet.Cells[1, 9].Value = "采购描述";
            sheet.Cells[1, 10].Value ="数据来源"; 
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sheet.Cells[i + 2, 1].Value = dt.Rows[i]["区域"];
                sheet.Cells[i + 2, 2].Value = dt.Rows[i]["产品名称"];
                sheet.Cells[i + 2, 3].Value = dt.Rows[i]["规格"];
                sheet.Cells[i + 2, 4].Value = dt.Rows[i]["型号"];
                sheet.Cells[i + 2, 5].Value = dt.Rows[i]["厂家"];
                sheet.Cells[i + 2, 6].Value = dt.Rows[i]["供应商"];
                sheet.Cells[i + 2, 7].Value = dt.Rows[i]["单价"];
                sheet.Cells[i + 2, 8].Value = dt.Rows[i]["单位"];
                sheet.Cells[i + 2, 9].Value = dt.Rows[i]["采购描述"];
                sheet.Cells[i + 2, 10].Value = dt.Rows[i]["数据来源"]; 
            }
            byte[] b = excel.GetAsByteArray();
            HttpContext curContext = System.Web.HttpContext.Current;
            // curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentType = "text/plain";//Content-Disposition
            // curContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            curContext.Response.AddHeader("content-disposition", "attachment;  filename="+ fileName + ".xlsx");
            curContext.Response.Charset = "gb2312";  //必须写，否则会有乱码
            curContext.Response.ContentEncoding = System.Text.Encoding.UTF7;  //必须写，否则会有乱码

            curContext.Response.AddHeader("Content-Length", b.Length.ToString());
            curContext.Response.BinaryWrite(b);
            curContext.Response.End();
        }