EPPlusHelper

  1  public class EPPlusExcelHelper : IDisposable
  2     {
  3         public ExcelPackage ExcelPackage { get; private set; }
  4         private Stream fs;
  5 
  6         public EPPlusExcelHelper(string filePath)
  7         {
  8             if (File.Exists(filePath))
  9             {
 10                 var file = new FileInfo(filePath);
 11                 ExcelPackage = new ExcelPackage(file);
 12             }
 13             else
 14             {
 15                 fs = File.Create(filePath);
 16                 ExcelPackage = new ExcelPackage(fs);
 17 
 18             }
 19         }
 20         /// <summary>
 21         /// 获取sheet,没有时创建
 22         /// </summary>
 23         /// <param name="sheetName"></param>
 24         /// <returns></returns>
 25         public ExcelWorksheet GetOrAddSheet(string sheetName)
 26         {
 27             ExcelWorksheet ws = ExcelPackage.Workbook.Worksheets.FirstOrDefault(i => i.Name == sheetName);
 28             if (ws == null)
 29             {
 30                 ws = ExcelPackage.Workbook.Worksheets.Add(sheetName);
 31             }
 32             return ws;
 33         }
 34         /// <summary>
 35         /// 使用EPPlus导出Excel(xlsx)
 36         /// </summary>
 37         /// <param name="ExcelPackage">ExcelPackage</param>
 38         /// <param name="sourceTable">数据源</param>
 39         public void AppendSheetToWorkBook(DataTable sourceTable)
 40         {
 41             AppendSheetToWorkBook(sourceTable, true);
 42         }
 43         /// <summary>
 44         /// 使用EPPlus导出Excel(xlsx)
 45         /// </summary>
 46         /// <param name="ExcelPackage">ExcelPackage</param>
 47         /// <param name="sourceTable">数据源</param>
 48         /// <param name="isDeleteSameNameSheet">是否删除同名的sheet</param>
 49         public void AppendSheetToWorkBook(DataTable sourceTable, bool isDeleteSameNameSheet)
 50         {
 51             //Create the worksheet
 52 
 53             ExcelWorksheet ws = AddSheet(sourceTable.TableName, isDeleteSameNameSheet);
 54 
 55             //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
 56             ws.Cells["A1"].LoadFromDataTable(sourceTable, true);
 57 
 58             //Format the row
 59             FromatRow(sourceTable.Rows.Count, sourceTable.Columns.Count, ws);
 60 
 61         }
 62 
 63         /// <summary>
 64         /// 删除指定的sheet
 65         /// </summary>
 66         /// <param name="ExcelPackage"></param>
 67         /// <param name="sheetName"></param>
 68         public void DeleteSheet(string sheetName)
 69         {
 70             var sheet = ExcelPackage.Workbook.Worksheets.FirstOrDefault(i => i.Name == sheetName);
 71             if (sheet != null)
 72             {
 73                 ExcelPackage.Workbook.Worksheets.Delete(sheet);
 74             }
 75         }
 76         /// <summary>
 77         /// 导出列表到excel,已存在同名sheet将删除已存在的
 78         /// </summary>
 79         /// <typeparam name="T"></typeparam>
 80         /// <param name="ExcelPackage"></param>
 81         /// <param name="list">数据源</param>
 82         /// <param name="sheetName">sheet名称</param>
 83         public void AppendSheetToWorkBook<T>(IEnumerable<T> list, string sheetName)
 84         {
 85             AppendSheetToWorkBook(list, sheetName, true);
 86         }
 87         /// <summary>
 88         /// 导出列表到excel,已存在同名sheet将删除已存在的
 89         /// </summary>
 90         /// <typeparam name="T"></typeparam>
 91         /// <param name="ExcelPackage"></param>
 92         /// <param name="list">数据源</param>
 93         /// <param name="sheetName">sheet名称</param>
 94         /// <param name="isDeleteSameNameSheet">是否删除已存在的同名sheet,false时将重命名导出的sheet</param>
 95         public void AppendSheetToWorkBook<T>(IEnumerable<T> list, string sheetName, bool isDeleteSameNameSheet)
 96         {
 97             ExcelWorksheet ws = AddSheet(sheetName, isDeleteSameNameSheet);
 98 
 99             //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
100             ws.Cells["A1"].LoadFromCollection(list, true);
101 
102         }
103 
104         /// <summary>
105         /// 添加文字图片
106         /// </summary>
107         /// <param name="sheet"></param>
108         /// <param name="msg">要转换成图片的文字</param>
109         public void AddPicture(string sheetName, string msg)
110         {
111             Bitmap img = GetPictureString(msg);
112 
113             var sheet = GetOrAddSheet(sheetName);
114             var picName = "92FF5CFE-2C1D-4A6B-92C6-661BDB9ED016";
115             var pic = sheet.Drawings.FirstOrDefault(i => i.Name == picName);
116             if (pic != null)
117             {
118                 sheet.Drawings.Remove(pic);
119             }
120             pic = sheet.Drawings.AddPicture(picName, img);
121 
122             pic.SetPosition(3, 0, 6, 0);
123         }
124         /// <summary>
125         /// 文字绘制图片
126         /// </summary>
127         /// <param name="msg"></param>
128         /// <returns></returns>
129         private static Bitmap GetPictureString(string msg)
130         {
131             var msgs = msg.Split(new string[] { System.Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
132             var maxLenght = msgs.Max(i => i.Length);
133             var rowCount = msgs.Count();
134             var rowHeight = 23;
135             var fontWidth = 17;
136             var img = new Bitmap(maxLenght * fontWidth, rowCount * rowHeight);
137             using (Graphics g = Graphics.FromImage(img))
138             {
139                 g.Clear(Color.White);
140                 Font font = new Font("Arial", 12, (FontStyle.Bold));
141                 LinearGradientBrush brush = new LinearGradientBrush(new Rectangle(0, 0, img.Width, img.Height), Color.Blue, Color.DarkRed, 1.2f, true);
142 
143                 for (int i = 0; i < msgs.Count(); i++)
144                 {
145                     g.DrawString(msgs[i], font, brush, 3, 2 + rowHeight * i);
146                 }
147             }
148 
149             return img;
150         }
151 
152         /// <summary>
153         /// List转DataTable
154         /// </summary>
155         /// <typeparam name="T"></typeparam>
156         /// <param name="data"></param>
157         /// <returns></returns>
158         public DataTable ListToDataTable<T>(IEnumerable<T> data)
159         {
160             PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
161             DataTable dataTable = new DataTable();
162             for (int i = 0; i < properties.Count; i++)
163             {
164                 PropertyDescriptor property = properties[i];
165                 dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
166             }
167             object[] values = new object[properties.Count];
168             foreach (T item in data)
169             {
170                 for (int i = 0; i < values.Length; i++)
171                 {
172                     values[i] = properties[i].GetValue(item);
173                 }
174 
175                 dataTable.Rows.Add(values);
176             }
177             return dataTable;
178         }
179         /// <summary>
180         /// 插入行
181         /// </summary>
182         /// <param name="sheet"></param>
183         /// <param name="values">行类容,一个单元格一个对象</param>
184         /// <param name="rowIndex">插入位置,起始位置为1</param>
185         public void InsertValues(string sheetName, List<object> values, int rowIndex)
186         {
187             var sheet = GetOrAddSheet(sheetName);
188             sheet.InsertRow(rowIndex, 1);
189             int i = 1;
190             foreach (var item in values)
191             {
192                 sheet.SetValue(rowIndex, i, item);
193                 i++;
194             }
195         }
196 
197         /// <summary>
198         /// 保存修改
199         /// </summary>
200         public void Save()
201         {
202             ExcelPackage.Save();
203         }
204 
205         /// <summary>
206         /// 添加Sheet到ExcelPackage
207         /// </summary>
208         /// <param name="ExcelPackage">ExcelPackage</param>
209         /// <param name="sheetName">sheet名称</param>
210         /// <param name="isDeleteSameNameSheet">如果存在同名的sheet是否删除</param>
211         /// <returns></returns>
212         private ExcelWorksheet AddSheet(string sheetName, bool isDeleteSameNameSheet)
213         {
214             if (isDeleteSameNameSheet)
215             {
216                 DeleteSheet(sheetName);
217             }
218             else
219             {
220                 while (ExcelPackage.Workbook.Worksheets.Any(i => i.Name == sheetName))
221                 {
222                     sheetName = sheetName + "(1)";
223                 }
224             }
225 
226             ExcelWorksheet ws = ExcelPackage.Workbook.Worksheets.Add(sheetName);
227             return ws;
228         }
229 
230         private void FromatRow(int rowCount, int colCount, ExcelWorksheet ws)
231         {
232             ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
233             Color borderColor = Color.FromArgb(155, 155, 155);
234 
235             using (ExcelRange rng = ws.Cells[1, 1, rowCount + 1, colCount])
236             {
237                 rng.Style.Font.Name = "宋体";
238                 rng.Style.Font.Size = 10;
239                 rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
240                 rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
241 
242                 rng.Style.Border.Top.Style = borderStyle;
243                 rng.Style.Border.Top.Color.SetColor(borderColor);
244 
245                 rng.Style.Border.Bottom.Style = borderStyle;
246                 rng.Style.Border.Bottom.Color.SetColor(borderColor);
247 
248                 rng.Style.Border.Right.Style = borderStyle;
249                 rng.Style.Border.Right.Color.SetColor(borderColor);
250             }
251 
252             //Format the header row
253             using (ExcelRange rng = ws.Cells[1, 1, 1, colCount])
254             {
255                 rng.Style.Font.Bold = true;
256                 rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
257                 rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
258                 rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
259             }
260         }
261 
262         public void Dispose()
263         {
264             ExcelPackage.Dispose();
265             if (fs != null)
266             {
267                 fs.Dispose();
268                 fs.Close();
269             }
270 
271         }
272     }

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Vue+abp增加三级菜单

    title: "Vue+abp增加三级菜单" publishDate: 2019-12-26 17:28:38 +0800 date: 2019-12-26...

    用户6362579
  • silverlight属性改变事件通知

    工作中遇到silverlight本身没有提供的某些属性改变事件,但又需要在属性改变时得到通知,Google搬运stack overflow,原地址

    用户6362579
  • 动态创建类

    用户6362579
  • C#泛型方法解析

        C#2.0引入了泛型这个特性,由于泛型的引入,在一定程度上极大的增强了C#的生命力,可以完成C#1.0时需要编写复杂代码才可以完成的一些功能。但是作为开...

    彭泽0902
  • C#使用Xamarin开发可移植移动应用进阶篇(7.使用布局渲染器,修改默认布局),附源码

    前言 源码地址:https://github.com/l2999019/DemoApp 可以Star一下,随意 - - 说点什么.. 本篇..基本可以算是Xam...

    GuZhenYin
  • AS3和JS通信

    注意:先调用ExternalInterface.addCallback,再调用ExternalInterface.call

    用户2398817
  • Java基础系列(三十八):集合总览

    在我们日常的开发中,集合占据着举足轻重的地位。在不同的情况下,我们会去选择性能更佳(或更安全的)集合类作为一个容器去存储数据。在接下来的几节中,我会带着大家对于...

    Vi的技术博客
  • Imago-Forensics:Python实现的图像数字取证工具

    Imago是一个由python编写的图像数字取证工具,它可以从图像中递归提取数字证据。在整个数字取证调查中,这款工具非常有用。如果你需要提取图像中的数字证据且数...

    周俊辉
  • Django简单实例

    可以点击我们的web开发一班,进入之后连接变为127.0.0.1:8000/calssinfo/1

    不断折腾
  • 小猿看报告丨陌陌发布《中国人收礼送礼习惯》大数据报告

    《礼记》有云“礼尚往来,往而不来,非礼也;来而不往,亦非礼也。”我国自古就是一个崇尚礼节的国度。 近日,陌陌公布了一份关于中国人收礼送礼习惯的数据报告,数据表明...

    数据猿

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动