EPPlus(SQL导成Excel)
使用Epplus方法把sql数据库中表的数据导出到excel中去:
需要使用EPPlus.dll引用。
using System.IO; using OfficeOpenXml; public static string Create(string[] tables) { //获取文件夹路径 string path = System.Configuration.ConfigurationManager.ConnectionStrings["Path"].ConnectionString; Console.WriteLine(path); //数组存储表名 循环生产excel for (int i = 0; i < tables.Count(); i++) { string file = path + tables[i] + ".xlsx"; //DataSet读取数据库数据 DataTable dt = GetDataSet(string.Format("select * from {0}", tables[i])).Tables[0]; if (File.Exists(file)) File.Delete(file); FileInfo newFile = new FileInfo(file); //建xlsx using (ExcelPackage xlPackage = new ExcelPackage(newFile)) { //添加一个项 ExcelWorksheet wsMO_RETAIL = xlPackage.Workbook.Worksheets.Add(tables[i]); //怎么填 填的方式 var dataRange = wsMO_RETAIL.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Medium3); //填充数据到excel dataRange.AutoFitColumns(); //设置格式 wsMO_RETAIL.Cells[1, 1, 1, 4].Style.Numberformat.Format = "@"; xlPackage.Save(); } Console.WriteLine(i); } //放在一个excel中 string filesum = path + "SUM.xlsx"; if (File.Exists(filesum)) File.Delete(filesum); FileInfo newFilesum = new FileInfo(filesum); using (ExcelPackage xlPackagesum = new ExcelPackage(newFilesum)) { for (int i = 0; i < tables.Count(); i++) { ExcelWorksheet wsMO_RETAIL = xlPackagesum.Workbook.Worksheets.Add(tables[i]); DataTable dt = GetDataSet(string.Format("select * from {0}", tables[i])).Tables[0]; var dataRange = wsMO_RETAIL.Cells["A1"].LoadFromDataTable(dt, true); dataRange.AutoFitColumns(); wsMO_RETAIL.Cells[1, 1, 1, 4].Style.Numberformat.Format = "@"; } xlPackagesum.Save(); xlPackagesum.Dispose(); Console.WriteLine("完成"); } return tables.Count().ToString(); }