C#Core查询数据库存储EXCEL文件

1：安装EPPLUS的NUGET包

实现源代码：

 1 using OfficeOpenXml;
 2 using System;
 3 using System.Collections.Generic;
 4 using System.IO;
 5 using System.Linq;
 6 
 7 namespace BigBeer.Core.Excel
 8 {
 9     public class Excel
10     {
11         /// <summary>
12         /// 只针对2003以上版本
13         /// </summary>
14         /// <param name="data">需要存储的数据</param>
15         /// <param name="path">存储地址</param>
16         /// <param name="filename">文件名即可，后缀.xlsx</param>
17         /// <param name="worksheetname">EXCEL文档下方的命名</param>
18         public static void SaveExcel(IList<object> data,string path = null, string filename = null, string worksheetname = null)
19         {
20             int count = data[0].ToString().Split(',').Count();
21             string sWebRootFolder = "D://Excel/";
22             if (!string.IsNullOrEmpty(path)) sWebRootFolder = path;
23             if (!Directory.Exists(sWebRootFolder))
24             {
25                 Directory.CreateDirectory(sWebRootFolder);
26             }
27             string sFileName = $"{Guid.NewGuid()}.xlsx";
28             if (!string.IsNullOrEmpty(filename)) sFileName = $"{filename}.xlsx";
29             FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
30             string worksheetName = "sheet";
31             if (!string.IsNullOrEmpty(worksheetname)) worksheetName = worksheetname;
32 
33             using (ExcelPackage package = new ExcelPackage(file))
34             {
35                 // 添加worksheet
36                 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetName);
37                 //添加头
38                 for (int i = 0; i < count; i++)
39                 {
40                     var key = data.ToList()[i].ToString().Split(',')[i].Split('=')[0].Replace("{", "").Replace("}", "");
41                     worksheet.Cells[1, i + 1].Value = key;
42                 }
43                 //添加值
44                 for (int i = 0; i < data.Count; i++)
45                 {
46                     var list = data[i];
47                     var shu = i + 2;
48                     for (int t = 0; t < count; t++)
49                     {
50                         var c = Convert.ToChar('A' + t);
51                         var value = list.ToString().Split(',')[t].Split('=')[1].Replace("{", "").Replace("}", "");
52                         worksheet.Cells[$"{c}{shu}"].Value = value;
53                     }
54                 }
55                 package.Save();
56                 GC.Collect();
57             }
58         }
59     }
60 }

3：使用方法：

1             var data = result.ToList<object>(); //result为查询出来的数据
2             data.SaveExcel();

 

posted @ 2017-12-03 19:19  假装攻城狮  阅读(304)  评论(0)    收藏  举报