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     }