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 }
