Epplus导出Excel(DataTable)

1.先将dataTable转换成流

public Stream DataTableToExcel(DataTable dataTable, string[] columns, string sheetName)
        {
            try
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
                    ws.Cells["A1"].LoadFromDataTable(dataTable, true);
                    for (int i = 1; i <= columns.Length; i++) //设置excel列名
                    {
                        using (ExcelRange rng = ws.Cells[1, i])
                        {
                            rng.Style.Font.Bold = true;
                            rng.Value = columns[i - 1];
                        }
                    }
                    MemoryStream ms = new MemoryStream();
                    pck.SaveAs(ms);
                    ms.Flush();
                    ms.Position = 0;//指定当前流的位置从0开始
                    return ms;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

View Code

设置Excel单元格格式  ws.Column(1).Style.Numberformat.Format = "YYYY-MM-DD HH:mm:ss"

保存excel文件

public void ExportExcel(DataTable dt, string[] columns, string sheetName, DownResult result)
        {
           
            Stream stream = DataTableToExcel(dt, columns, sheetName);
            ExcelPackage package = new ExcelPackage(stream);
            fileName = sheetName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            filePath = HttpContext.Current.Server.MapPath("~/UploadFile/");//文件保存地址  
            if (!Directory.Exists(filePath))//如果不存在就创建file文件夹
            {
                Directory.CreateDirectory(filePath);
            }
            package.SaveAs(new FileInfo(filePath + fileName));

        }

View Code

 

 

2.直接将dataTable 保存为Excel文件

public void DataTableToExcel(DataTable dtResult,string sheetName)
        {
            string path = AppDomain.CurrentDomain.BaseDirectory + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
            FileInfo file = new FileInfo(path);
            using (ExcelPackage pck = new ExcelPackage(file))
            {
                ExcelWorksheet w = pack.Workbook.Worksheets[sheetName]; 
                if (w != null && w.Name.Equals(sheetName)) //判断是否存在该sheet表，存在则删除
                    pack.Workbook.Worksheets.Delete(w);
                ExcelWorksheet ws = pack.Workbook.Worksheets.Add(sheetName);
                ws.Cells["A1"].LoadFromDataTable(dtResult, true); //第二个参数设置为true则显示datable表头
                pck.Save();
            }
        }

View Code

 

转载于:https://www.cnblogs.com/ZJ199012/p/9284786.html