1 using Aspose.Cells;
2 using Microsoft.AspNetCore.Hosting;
3 using Microsoft.VisualBasic;
4 using NPOI.HSSF.UserModel;
5 using NPOI.SS.UserModel;
6 using NPOI.SS.Util;
7 using NPOI.XSSF.UserModel;
8 using System.Collections;
9 using System.Collections.Generic;
10 using System.Data;
11
12 namespace JobCommon
13 {
14 public class ExcelHelper
15 {
16
17 private readonly IHostingEnvironment _hostingEnvironment;
18
19 public ExcelHelper(IHostingEnvironment hostingEnvironment)
20 {
21 _hostingEnvironment = hostingEnvironment;
22 }
23
24 /// <summary>
25 /// Datable导出成Excel
26 /// </summary>
27 /// <param name="dt">导出存放文件夹路径</param>
28 /// <param name="Route">文件夹路径</param>
29 /// <param name="file">导出路径(包括文件名与扩展名),注：年月日时分秒+文件名</param>
30 public static void TableToExcel(DataTable dt, string Route, string file)
31 {
32 try
33 {
34 //string[] fileArr = file.Split(new char[] { '.' });
35 //file = fileArr[0] + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + fileArr[1];
36
37 IWorkbook workbook;
38 string fileExt = Path.GetExtension(file).ToLower();
39 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
40 if (workbook == null) { return; }
41 ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
42
43 //设置列宽
44 //sheet.SetColumnWidth(0, 15 * 256);
45 //sheet.SetColumnWidth(1, 15 * 256);
46 //sheet.SetColumnWidth(2, 15 * 256);
47 //sheet.SetColumnWidth(3, 15 * 256);
48 //sheet.SetColumnWidth(4, 15 * 256);
49 //sheet.SetColumnWidth(5, 25 * 256);
50 //sheet.SetColumnWidth(6, 25 * 256);
51 //sheet.SetColumnWidth(7, 30 * 256);
52 //sheet.SetColumnWidth(8, 15 * 256);
53 //sheet.SetColumnWidth(9, 15 * 256);
54 //sheet.SetColumnWidth(10, 15 * 256);
55 //sheet.SetColumnWidth(12, 15 * 256);
56
57 //for (int i = 0; i < dt.Rows.Count; i++)
58 //{
59 // sheet.SetColumnWidth(i, 20 * 256);
60 //}
61
62
63 //表头
64 IRow row = sheet.CreateRow(0);
65 for (int i = 0; i < dt.Columns.Count - 1; i++)
66 {
67 ICell cell = row.CreateCell(i);
68 cell.SetCellValue(dt.Columns[i].ColumnName);
69 }
70
71 HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
72 ICellStyle style = workbook.CreateCellStyle();
73 ICellStyle style2 = workbook.CreateCellStyle();
74 string val = "";
75 //数据
76 for (int i = 0; i < dt.Rows.Count; i++)
77 {
78 IRow row1 = sheet.CreateRow(i + 1);
79 for (int j = 0; j < dt.Columns.Count - 1; j++)
80 {
81 ICell cell = row1.CreateCell(j);
82
83 val = dt.Rows[i][j].ToString();
84
85 cell.SetCellValue(val);
86
87 }
88 }
89
90 //合并单元格
91 CellRangeAddress rowCellRangeAddress = new CellRangeAddress(1, 5, 0, 8);
92 sheet.AddMergedRegion(rowCellRangeAddress);
93
94 //设置列宽
95 sheet.SetColumnWidth(4, 20 * 256);
96 //设置行高
97 HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
98 IRow row2 = sheet.GetRow(2);
99 row2.HeightInPoints = 120;
100 row2.GetCell(2).CellStyle = cellStyle;
101 setPic((HSSFWorkbook)workbook, patriarch, "/FileManage/AA/20201102/2bf26b91-8c2b-4bcb-88f1-87c8ec001c96.jpg", sheet, 2, 4);
102
103
104 //转为字节数组
105 MemoryStream stream = new MemoryStream();
106 workbook.Write(stream, true);
107 var buf = stream.ToArray();
108
109 //如找不到文件夹则创建
110 if (!Directory.Exists(Route))
111 {
112 Directory.CreateDirectory(Route);
113 }
114
115
116 //保存为Excel文件
117 using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write))
118 {
119 fs.Write(buf, 0, buf.Length);
120 fs.Flush();
121 fs.Close();
122 fs.Dispose();
123 }
124
125
126 //下载部分 测试无效
127
128 //HttpResponse response = HttpContext.Current.Response;
129 //response.Clear();
130 //response.ClearHeaders();
131 //response.ClearContent();
132 //response.Buffer = true;
133 //response.AddHeader("content-disposition", string.Format("attachment; FileName={0}", file));
134 //response.Charset = "GB2312";
135 //response.ContentEncoding = Encoding.GetEncoding("GB2312");
136 //response.ContentType = MimeMapping.GetMimeMapping(file);
137 //response.WriteFile(Route + file);
138 //response.Flush();
139 //response.Close();
140
141 //HttpResponseBase response = httpContext.Response;
142 //response.Clear();
143 //response.Buffer = false;
144 //response.AddHeader("Accept-Ranges", "bytes");
145 //response.AddHeader("Content-Type", GetContentTypeByFileName(fileName));
146 //response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(fileName)));
147 //response.AddHeader("Content-Length", buffer.Length.ToString());
148 //response.OutputStream.Write(buffer, 0, buffer.Length);
149 //response.Flush();
150 //response.End();
151 }
152 catch (Exception ex)
153 {
154
155 //LoggerHelper._.Error(ex.ToString());
156 }
157
158 }
159
160
161 /// <summary>
162 /// Datable导出成Excel
163 /// </summary>
164 /// <param name="dt">导出存放文件夹路径</param>
165 /// <param name="Route">文件夹路径</param>
166 /// <param name="file">导出路径(包括文件名与扩展名),注：年月日时分秒+文件名</param>
167 public static void DailyTableToExcel(DataTable dt, DataTable dtWorkToday, DataTable dtTomorrowPlan, string Route, string file)
168 {
169 try
170 {
171 //string[] fileArr = file.Split(new char[] { '.' });
172 //file = fileArr[0] + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + fileArr[1];
173
174 IWorkbook workbook;
175 string fileExt = Path.GetExtension(file).ToLower();
176 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
177 if (workbook == null) { return; }
178 ISheet sheet;
179
180 DataRow[] dataRow;
181
182 //新建样式
183 ICellStyle style = workbook.CreateCellStyle();
184 //字体颜色
185 //创建字体
186 HSSFFont ffont = (HSSFFont)workbook.CreateFont();
187 //给字体加粗
188 ffont.IsBold = true;
189 style.Alignment = HorizontalAlignment.Center;
190 //给样式添加字体
191 style.SetFont(ffont);
192
193
194 ICellStyle styleContent = workbook.CreateCellStyle();
195 styleContent.VerticalAlignment = VerticalAlignment.Center;
196 styleContent.WrapText = true;
197
198 //ICellStyle styleBorder = workbook.CreateCellStyle();
199 //styleBorder.BorderBottom = BorderStyle.Medium;
200 //styleBorder.BorderLeft = BorderStyle.Medium;
201 //styleBorder.BorderRight = BorderStyle.Medium;
202 //styleBorder.BorderTop = BorderStyle.Medium;
203
204 //styleBorder.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
205 //styleBorder.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
206 //styleBorder.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
207 //styleBorder.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
208
209 object WechatId, DailyID, UserName, Position, ImgPath, CreateDate;
210 object WorkItem, Problem, ResolvedState, PlanDescription;
211 string[] ImgPathArr = new string[0];
212 string SheetName = "", ImgPath2 = "";
213 int RowsIndex = 0;
214 int SheetIndex = 2;
215 for (int i = 0; i < dt.Rows.Count; i++)
216 {
217 RowsIndex = 0;
218 WechatId = dt.Rows[i]["WechatId"];
219 DailyID = dt.Rows[i]["DailyID"];
220 UserName = dt.Rows[i]["UserName"];
221 Position = dt.Rows[i]["Position"];
222 ImgPath = dt.Rows[i]["ImgPath"];
223 if (ImgPath != null)
224 {
225 if (ImgPath.ToString() != "")
226 {
227 ImgPathArr = ImgPath.ToString().Split(new char[] { '|' });
228 }
229
230 }
231 CreateDate = dt.Rows[i]["CreateDate"];
232 CreateDate = DateTime.Parse(CreateDate.ToString()).ToString("yyyyMMdd");
233 if (workbook.GetSheet(CreateDate.ToString()) != null)
234 {
235 SheetName = CreateDate.ToString() + "-" + SheetIndex;
236 SheetIndex++;
237 }
238 else
239 {
240 SheetName = CreateDate.ToString();
241 SheetIndex = 2;
242 }
243
244 sheet = workbook.CreateSheet(SheetName);
245
246 IRow row = sheet.CreateRow(RowsIndex);
247 RowsIndex++;
248 ICell cell = row.CreateCell(0);
249 cell.SetCellValue("" + UserName + "（" + Position + "） " + CreateDate + "日报");
250 cell.CellStyle = style;//设置样式
251 sheet.SetColumnWidth(0, 50 * 256);
252
253 ////合并单元格
254 CellRangeAddress rowCellRangeAddress = new CellRangeAddress(0, 0, 0, 2);
255 sheet.AddMergedRegion(rowCellRangeAddress);
256
257 row = sheet.CreateRow(RowsIndex);
258 RowsIndex++;
259 cell = row.CreateCell(0);
260 cell.SetCellValue("今日工作");
261 cell.CellStyle = style;//设置样式
262 cell = row.CreateCell(1);
263 cell.SetCellValue("问题点");
264 cell.CellStyle = style;//设置样式
265 cell = row.CreateCell(2);
266 cell.SetCellValue("解决状态");
267 cell.CellStyle = style;//设置样式
268
269 sheet.SetColumnWidth(0, 50 * 256);
270 sheet.SetColumnWidth(1, 50 * 256);
271
272
273 dataRow = dtWorkToday.Select("DailyID='" + DailyID + "'");
274 //今日工作
275 for (int j = 0; j < dataRow.Length; j++)
276 {
277 WorkItem = dataRow[j]["WorkItem"];
278 Problem = dataRow[j]["Problem"];
279 ResolvedState = dataRow[j]["ResolvedState"];
280 //数据 循环填充内容部分
281 row = sheet.CreateRow(RowsIndex);
282 row.HeightInPoints = 60;
283 RowsIndex++;
284 cell = row.CreateCell(0);
285 cell.CellStyle = styleContent;
286 cell.SetCellValue(WorkItem.ToString());
287 cell = row.CreateCell(1);
288 cell.CellStyle = styleContent;
289 cell.SetCellValue(Problem.ToString());
290 cell = row.CreateCell(2);
291 cell.CellStyle = styleContent;
292 cell.SetCellValue(ResolvedState.ToString() + "%");
293 row = sheet.CreateRow(RowsIndex);//空行
294
295 //合并单元格
296 rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
297 RowsIndex++;
298 sheet.AddMergedRegion(rowCellRangeAddress);
299 }
300
301
302 row = sheet.CreateRow(RowsIndex);
303 cell = row.CreateCell(0);
304 cell.SetCellValue("明日计划");
305 cell.CellStyle = style;//设置样式
306 sheet.SetColumnWidth(0, 50 * 256);
307
308 //合并单元格
309 rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
310 RowsIndex++;
311 sheet.AddMergedRegion(rowCellRangeAddress);
312
313
314 dataRow = dtTomorrowPlan.Select("DailyID='" + DailyID + "'");
315 //明日计划
316 for (int k = 0; k < dataRow.Length; k++)
317 {
318 PlanDescription = dataRow[k]["PlanDescription"];
319 //数据 循环填充内容部分
320 row = sheet.CreateRow(RowsIndex);
321 row.HeightInPoints = 60;
322 cell = row.CreateCell(0);
323 cell.CellStyle = styleContent;
324 cell.SetCellValue(PlanDescription.ToString());
325 //合并单元格
326 rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
327 RowsIndex++;
328 sheet.AddMergedRegion(rowCellRangeAddress);
329
330
331 row = sheet.CreateRow(RowsIndex);//空行
332
333 //合并单元格
334 rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
335 RowsIndex++;
336 sheet.AddMergedRegion(rowCellRangeAddress);
337
338
339 }
340
341
342 row = sheet.CreateRow(RowsIndex);
343 cell = row.CreateCell(0);
344 cell.SetCellValue("图片列表");
345 cell.CellStyle = style;//设置样式
346 sheet.SetColumnWidth(0, 50 * 256);
347
348 //合并单元格
349 rowCellRangeAddress = new CellRangeAddress(RowsIndex, RowsIndex, 0, 2);
350 RowsIndex++;
351 sheet.AddMergedRegion(rowCellRangeAddress);
352
353 int rowline = RowsIndex, col = 0;
354 //图片列表
355 for (int z = 0; z < ImgPathArr.Length; z++)
356 {
357 col = z;
358 if (col > 1 && col % 2 == 0)
359 {
360 col = 0;
361 rowline += 10;
362 }
363
364 if (col > 1 && col % 2 == 1)
365 {
366 col = 1;
367
368 }
369
370 ImgPath2 = ImgPathArr[z];
371 ImgPath2 = ImgPath2.Substring(ImgPath2.IndexOf("FileManage") - 1);
372
373 //插入图片
374 HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
375 setPic((HSSFWorkbook)workbook, patriarch, ImgPath2, sheet, rowline, col);
376 //setPic((HSSFWorkbook)workbook, patriarch, "/FileManage/AA/20201102/2bf26b91-8c2b-4bcb-88f1-87c8ec001c96.jpg", sheet, rowline, col);
377
378 }
379
380 }
381
382
383
384
385 //新建工作表
386 //ISheet Sheet1 = workbook.CreateSheet("Sheet1");
387 //CreateSheet(workbook, "20201106");
388
389 //表头
390 //IRow row = sheet.CreateRow(0);
391 //for (int i = 0; i < dt.Columns.Count - 1; i++)
392 //{
393 // ICell cell = row.CreateCell(i);
394 // cell.SetCellValue(dt.Columns[i].ColumnName);
395 //}
396
397
398 //ICellStyle style = workbook.CreateCellStyle();
399 //ICellStyle style2 = workbook.CreateCellStyle();
400 //string val = "";
401 ////数据
402 //for (int i = 0; i < dt.Rows.Count; i++)
403 //{
404 // IRow row1 = sheet.CreateRow(i + 1);
405 // for (int j = 0; j < dt.Columns.Count - 1; j++)
406 // {
407 // ICell cell = row1.CreateCell(j);
408
409 // val = dt.Rows[i][j].ToString();
410
411 // cell.SetCellValue(val);
412
413 // }
414 //}
415
416 ////合并单元格
417 //CellRangeAddress rowCellRangeAddress = new CellRangeAddress(1, 5, 0, 8);
418 //sheet.AddMergedRegion(rowCellRangeAddress);
419
420 ////设置列宽
421 //sheet.SetColumnWidth(4, 20 * 256);
422 ////设置行高
423 //HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
424 //IRow row2 = sheet.GetRow(2);
425 //row2.HeightInPoints = 120;
426 //row2.GetCell(2).CellStyle = cellStyle;
427 ////插入图片
428 //HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
429 //setPic((HSSFWorkbook)workbook, patriarch, "/FileManage/AA/20201102/2bf26b91-8c2b-4bcb-88f1-87c8ec001c96.jpg", sheet, 2, 4);
430
431
432 //转为字节数组
433 MemoryStream stream = new MemoryStream();
434 workbook.Write(stream, true);
435 var buf = stream.ToArray();
436
437 //如找不到文件夹则创建
438 if (!Directory.Exists(Route))
439 {
440 Directory.CreateDirectory(Route);
441 }
442
443
444 //保存为Excel文件
445 using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write))
446 {
447 fs.Write(buf, 0, buf.Length);
448 fs.Flush();
449 fs.Close();
450 fs.Dispose();
451 }
452
453
454 }
455 catch (Exception ex)
456 {
457
458 //LoggerHelper._.Error(ex.ToString());
459 }
460
461 }
462
463 /// <summary>
464 /// 新建Sheet
465 /// </summary>
466 /// <param name="workbook"></param>
467 /// <param name="SheetName"></param>
468 public static void CreateSheet(IWorkbook workbook, string SheetName)
469 {
470
471 ISheet sheet = workbook.CreateSheet(SheetName);
472
473 //新建样式
474 ICellStyle style = workbook.CreateCellStyle();
475 //字体颜色
476 //创建字体
477 HSSFFont ffont = (HSSFFont)workbook.CreateFont();
478 //给字体加粗
479 ffont.IsBold = true;
480 style.Alignment = HorizontalAlignment.Center;
481 //给样式添加字体
482 style.SetFont(ffont);
483
484
485 IRow row = sheet.CreateRow(0);
486 ICell cell = row.CreateCell(0);
487 cell.SetCellValue("李儒鸿（.net工程师）");
488 cell.CellStyle = style;//设置样式
489 sheet.SetColumnWidth(0, 50 * 256);
490
491 ////合并单元格
492 CellRangeAddress rowCellRangeAddress = new CellRangeAddress(0, 0, 0, 2);
493 sheet.AddMergedRegion(rowCellRangeAddress);
494
495 row = sheet.CreateRow(1);
496 cell = row.CreateCell(0);
497 cell.SetCellValue("今日工作");
498 cell.CellStyle = style;//设置样式
499 cell = row.CreateCell(1);
500 cell.SetCellValue("问题点");
501 cell.CellStyle = style;//设置样式
502 cell = row.CreateCell(2);
503 cell.SetCellValue("解决状态");
504 cell.CellStyle = style;//设置样式
505
506 sheet.SetColumnWidth(0, 50 * 256);
507 sheet.SetColumnWidth(1, 50 * 256);
508
509 //数据 循环填充内容部分
510 row = sheet.CreateRow(2);
511 cell = row.CreateCell(0);
512 cell.SetCellValue("今日工作内容001");
513 cell = row.CreateCell(1);
514 cell.SetCellValue("问题点001");
515 cell = row.CreateCell(2);
516 cell.SetCellValue("100%");
517
518 row = sheet.CreateRow(3);//空行
519 //合并单元格
520 rowCellRangeAddress = new CellRangeAddress(3, 3, 0, 2);
521 sheet.AddMergedRegion(rowCellRangeAddress);
522
523 row = sheet.CreateRow(4);
524 cell = row.CreateCell(0);
525 cell.SetCellValue("明日计划");
526 cell.CellStyle = style;//设置样式
527 sheet.SetColumnWidth(0, 50 * 256);
528
529 //合并单元格
530 rowCellRangeAddress = new CellRangeAddress(4, 4, 0, 2);
531 sheet.AddMergedRegion(rowCellRangeAddress);
532
533 //数据 循环填充内容部分
534 row = sheet.CreateRow(5);
535 cell = row.CreateCell(0);
536 cell.SetCellValue("明日计划内容001");
537 //合并单元格
538 rowCellRangeAddress = new CellRangeAddress(5, 5, 0, 2);
539 sheet.AddMergedRegion(rowCellRangeAddress);
540
541
542 row = sheet.CreateRow(6);//空行
543 //合并单元格
544 rowCellRangeAddress = new CellRangeAddress(6, 6, 0, 2);
545 sheet.AddMergedRegion(rowCellRangeAddress);
546
547 row = sheet.CreateRow(7);
548 cell = row.CreateCell(0);
549 cell.SetCellValue("图片列表");
550 cell.CellStyle = style;//设置样式
551 sheet.SetColumnWidth(0, 50 * 256);
552
553 //合并单元格
554 rowCellRangeAddress = new CellRangeAddress(7, 7, 0, 2);
555 sheet.AddMergedRegion(rowCellRangeAddress);
556
557 //数据 循环填充内容部分
558 row = sheet.CreateRow(8);
559 cell = row.CreateCell(0);
560 //合并单元格
561 rowCellRangeAddress = new CellRangeAddress(8, 8, 0, 2);
562 sheet.AddMergedRegion(rowCellRangeAddress);
563
564 }
565
566 /// <summary>
567 /// Datable导出成Excel 设置样式示例
568 /// </summary>
569 /// <param name="dt">导出存放文件夹路径</param>
570 /// <param name="Route">文件夹路径</param>
571 /// <param name="file">导出路径(包括文件名与扩展名),注：年月日时分秒+文件名</param>
572
573 public static void TableToExcelStyle(DataTable dt, string Route, string file)
574 {
575 //string[] fileArr = file.Split(new char[] { '.' });
576 //file = fileArr[0] + DateTime.Now.ToString("yyyyMMddHHmmss") + "." + fileArr[1];
577
578 IWorkbook workbook;
579 string fileExt = Path.GetExtension(file).ToLower();
580 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
581 if (workbook == null) { return; }
582 ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
583
584 //设置列宽
585 //sheet.SetColumnWidth(0, 15 * 256);
586 //sheet.SetColumnWidth(1, 15 * 256);
587 //sheet.SetColumnWidth(2, 15 * 256);
588 //sheet.SetColumnWidth(3, 15 * 256);
589 //sheet.SetColumnWidth(4, 15 * 256);
590 //sheet.SetColumnWidth(5, 25 * 256);
591 //sheet.SetColumnWidth(6, 25 * 256);
592 //sheet.SetColumnWidth(7, 30 * 256);
593 //sheet.SetColumnWidth(8, 15 * 256);
594 //sheet.SetColumnWidth(9, 15 * 256);
595 //sheet.SetColumnWidth(10, 15 * 256);
596 //sheet.SetColumnWidth(12, 15 * 256);
597
598 //for (int i = 0; i < dt.Rows.Count; i++)
599 //{
600 // sheet.SetColumnWidth(i, 20 * 256);
601 //}
602
603
604 //表头
605 IRow row = sheet.CreateRow(0);
606 for (int i = 0; i < dt.Columns.Count - 1; i++)
607 {
608 ICell cell = row.CreateCell(i);
609 cell.SetCellValue(dt.Columns[i].ColumnName);
610 }
611
612
613 ICellStyle style = workbook.CreateCellStyle();
614 ICellStyle style2 = workbook.CreateCellStyle();
615 string val = "";
616 //数据
617 for (int i = 0; i < dt.Rows.Count; i++)
618 {
619 IRow row1 = sheet.CreateRow(i + 1);
620 for (int j = 0; j < dt.Columns.Count - 1; j++)
621 {
622 ICell cell = row1.CreateCell(j);
623
624 val = dt.Rows[i][j].ToString();
625
626 cell.SetCellValue(val);
627
628 if (val == "通过")
629 {
630 //设置单元格颜色
631 style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
632 style.FillPattern = FillPattern.SolidForeground;
633
634 //字体颜色
635 //创建字体
636 HSSFFont ffont = (HSSFFont)workbook.CreateFont();
637 //给字体设置颜色
638 ffont.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
639 //给样式添加字体
640 style.SetFont(ffont);
641
642 cell.CellStyle = style;//设置
643 }
644
645 if (val == "测试1报废")
646 {
647 //设置单元格颜色
648 style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Orange.Index;
649 style2.FillPattern = FillPattern.SolidForeground;
650
651 //字体颜色
652 //创建字体
653 HSSFFont ffont = (HSSFFont)workbook.CreateFont();
654 //给字体设置颜色
655 ffont.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
656 //给样式添加字体
657 style2.SetFont(ffont);
658
659 cell.CellStyle = style2;//设置
660 }
661
662 }
663 }
664
665
666
667 //转为字节数组
668 MemoryStream stream = new MemoryStream();
669 workbook.Write(stream, true);
670 var buf = stream.ToArray();
671
672 //如找不到文件夹则创建
673 if (!Directory.Exists(Route))
674 {
675 Directory.CreateDirectory(Route);
676 }
677
678
679 //保存为Excel文件
680 using (FileStream fs = new FileStream(Route + file, FileMode.Create, FileAccess.Write))
681 {
682 fs.Write(buf, 0, buf.Length);
683 fs.Flush();
684 fs.Close();
685 fs.Dispose();
686 }
687
688
689 }
690
691 /// <summary>
692 /// 复制文件
693 /// </summary>
694 /// <param name="sourceFilePath"></param>
695 /// <param name="newFilePath"></param>
696 public static void CopyTextToNewWorkbook(string sourceFilePath, string newFilePath)
697 {
698 /*
699 * 可读取excel的公式数据和引用外部数据
700 DataTable dt = new DataTable();
701 Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(sourceFilePath);
702 Worksheet ws = wk.Worksheets[0];
703 int a = ws.Cells.Rows.Count;
704 int b = ws.Cells.Columns.Count;
705 dt = ws.Cells.ExportDataTable(0, 0, 9, 9);
706 dt = ws.Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow + 1, ws.Cells.MaxDataColumn + 1, true);
707 dt = ws.Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow + 1, ws.Cells.MaxDataColumn + 1);
708 */
709
710
711 // 加载现有的工作簿
712 using (FileStream fileStream = new FileStream(sourceFilePath, FileMode.Open, FileAccess.Read))
713 {
714 XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
715
716
717 // 创建新的工作簿
718 XSSFWorkbook newWorkbook = new XSSFWorkbook();
719
720 // 复制每个工作表
721 for (int i = 0; i < workbook.NumberOfSheets; i++)
722 {
723 ISheet sheet = workbook.GetSheetAt(i);
724 ISheet newSheet = newWorkbook.CreateSheet(sheet.SheetName);
725
726 // 复制行
727 for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
728 {
729 IRow row = sheet.GetRow(rowNum);
730 IRow newRow = newSheet.CreateRow(rowNum);
731
732 // 复制单元格
733 if (row != null)
734 {
735 for (int cellNum = 0; cellNum < row.LastCellNum; cellNum++)
736 {
737 ICell cell = row.GetCell(cellNum);
738 ICell newCell = newRow.CreateCell(cellNum);
739
740 if (cell != null)
741 {
742 //newCell.CellStyle = cell.CellStyle;
743 //newCell.SetCellType(cell.CellType);
744
745 // 根据单元格类型复制数据
746 switch (cell.CellType)
747 {
748 case CellType.String:
749 newCell.SetCellValue(cell.StringCellValue);
750 break;
751 case CellType.Numeric:
752 newCell.SetCellValue(cell.NumericCellValue);
753 break;
754 // ...其他类型
755 default:
756 break;
757 }
758 }
759 }
760 }
761 }
762 }
763
764 // 保存新工作簿到文件
765 using (FileStream newFileStream = new FileStream(newFilePath, FileMode.Create, FileAccess.Write))
766 {
767 newWorkbook.Write(newFileStream);
768 }
769 }
770 }
771
772
773 /// <summary>
774 /// Excel导入成Datable
775 /// </summary>
776 /// <param name="file">导入路径(包含文件名与扩展名)</param>
777 /// <returns></returns>
778 public static DataTable ExcelToTable(string file)
779 {
780
781
782 DataTable dt = new DataTable();
783 IWorkbook workbook;
784 string fileExt = Path.GetExtension(file).ToLower();
785 using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
786 {
787 //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
788 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
789 if (workbook == null) { return null; }
790 ISheet sheet = workbook.GetSheetAt(0);
791
792 //表头
793 IRow header = sheet.GetRow(sheet.FirstRowNum);
794 List<int> columns = new List<int>();
795 for (int i = 0; i < header.LastCellNum; i++)
796 {
797 object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);// GetValueType(header.GetCell(i));
798
799 if (obj == null || obj.ToString() == string.Empty)
800 {
801 dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
802 }
803 else
804 {
805 obj = obj.ToString().Trim();
806 dt.Columns.Add(new DataColumn(obj.ToString()));
807 columns.Add(i);
808 }
809
810 }
811 //数据
812 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
813 {
814 DataRow dr = dt.NewRow();
815 bool hasValue = false;
816 foreach (int j in columns)
817 {
818 dr[j] = sheet.GetRow(i).GetCell(j);// GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);// GetValueType(sheet.GetRow(i).GetCell(j));
819 dr[j] = dr[j].ToString().Trim();
820 if (dr[j] != null && dr[j].ToString() != string.Empty)
821 {
822 hasValue = true;
823 }
824 }
825 if (hasValue)
826 {
827 dt.Rows.Add(dr);
828 }
829 }
830 }
831 return dt;
832 }
833
834 /// <summary>
835 /// Excel导入成Datable
836 /// 此方法可读取excel的公式数据和引用外部数据
837 /// </summary>
838 /// <param name="file">导入路径(包含文件名与扩展名)</param>
839 /// <param name="SheetName">指定Sheet名称，默认第一个Sheet</param>
840 /// <returns></returns>
841 public static DataTable ExportDataTable(string file, string SheetName = "")
842 {
843
844 DataTable dt = new DataTable();
845 Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(file);
846 Worksheet ws = wk.Worksheets[0];
847 if (!string.IsNullOrWhiteSpace(SheetName))
848 {
849 ws = wk.Worksheets[SheetName];
850 }
851
852 int row = ws.Cells.MaxDataRow + 1;
853 int col = ws.Cells.MaxDataColumn + 1;
854 dt = ws.Cells.ExportDataTable(0, 0, row, col, true);
855
856 return dt;
857 }
858
859
860 /// <summary>
861 /// Excel导入成多个Datable 批量
862 /// 此方法可读取excel的公式数据和引用外部数据
863 /// </summary>
864 /// <param name="file">导入路径(包含文件名与扩展名)</param>
865 public static Dictionary<string, DataTable> ExportDataTableList(string file)
866 {
867 Dictionary<string, DataTable> dic = new Dictionary<string, DataTable>();
868 DataTable dt = new DataTable();
869 Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(file);
870 int Count = wk.Worksheets.Count;
871 for (int i = 0; i < Count; i++)
872 {
873 Worksheet ws = wk.Worksheets[i];
874
875 int row = ws.Cells.MaxDataRow + 1;
876 int col = ws.Cells.MaxDataColumn + 1;
877 dt = ws.Cells.ExportDataTable(0, 0, row, col, true);
878 dic.Add(wk.Worksheets[i].Name, dt);
879 }
880
881
882 return dic;
883 }
884
885
886 /// <summary>
887 /// Excel导入成Datable
888 /// 此方法可读取excel的公式数据和引用外部数据
889 /// 此方法会将单元格内容全部转为字符串类型
890 /// </summary>
891 /// <param name="file">导入路径(包含文件名与扩展名)</param>
892 /// <param name="SheetName">指定Sheet名称，默认第一个Sheet</param>
893 /// <returns></returns>
894 public static DataTable ExportDataTableAsString(string file, string SheetName = "")
895 {
896
897 DataTable dt = new DataTable();
898 Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(file);
899 Worksheet ws = wk.Worksheets[0];
900
901 if (!string.IsNullOrWhiteSpace(SheetName))
902 {
903 ws = wk.Worksheets[SheetName];
904 }
905
906 int row = ws.Cells.MaxDataRow + 1;
907 int col = ws.Cells.MaxDataColumn + 1;
908 //dt = ws.Cells.ExportDataTable(0, 0, row, col, true);
909 //这个方法会将单元格内容全部转为字符串类型，以防有的列既有数字又有字符会报错
910 dt = ws.Cells.ExportDataTableAsString(0, 0, row, col, true);
911
912 return dt;
913 }
914
915
916 /// <summary>
917 /// Excel导入成多个Datable 批量
918 /// 此方法可读取excel的公式数据和引用外部数据
919 /// 此方法会将单元格内容全部转为字符串类型
920 /// </summary>
921 /// <param name="file">导入路径(包含文件名与扩展名)</param>
922 public static Dictionary<string, DataTable> ExportDataTableAsStringList(string file)
923 {
924 try
925 {
926
927
928 Dictionary<string, DataTable> dic = new Dictionary<string, DataTable>();
929 DataTable dt = new DataTable();
930 Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(file);
931 int Count = wk.Worksheets.Count;
932 for (int i = 0; i < Count; i++)
933 {
934 Worksheet ws = wk.Worksheets[i];
935 int row = ws.Cells.MaxDataRow + 1;
936 int col = ws.Cells.MaxDataColumn + 1;
937 //dt = ws.Cells.ExportDataTable(0, 0, row, col, true);
938
939 //行或列不为空才执行，如果全部空白会报错
940 if (row != 0 || col != 0)
941 {
942 //这个方法会将单元格内容全部转为字符串类型，以防有的列既有数字又有字符会报错
943 dt = ws.Cells.ExportDataTableAsString(0, 0, row, col, true);
944 dic.Add(wk.Worksheets[i].Name.Trim(), dt);
945 }
946 }
947
948
949 return dic;
950
951 }
952 catch (Exception)
953 {
954
955 throw;
956 }
957
958 }
959
960
961 public static void TemplateExcle()
962 {
963 FileStream file = new FileStream(@"template/book1.xls", FileMode.Open, FileAccess.Read);
964
965 HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
966 HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
967 sheet1.GetRow(1).GetCell(1).SetCellValue(200200);
968 sheet1.GetRow(2).GetCell(1).SetCellValue(300);
969 sheet1.GetRow(3).GetCell(1).SetCellValue(500050);
970 sheet1.GetRow(4).GetCell(1).SetCellValue(8000);
971 sheet1.GetRow(5).GetCell(1).SetCellValue(110);
972 sheet1.GetRow(6).GetCell(1).SetCellValue(100);
973 sheet1.GetRow(7).GetCell(1).SetCellValue(200);
974 sheet1.GetRow(8).GetCell(1).SetCellValue(210);
975 sheet1.GetRow(9).GetCell(1).SetCellValue(2300);
976 sheet1.GetRow(10).GetCell(1).SetCellValue(240);
977 sheet1.GetRow(11).GetCell(1).SetCellValue(180123);
978 sheet1.GetRow(12).GetCell(1).SetCellValue(150);
979
980 //Force excel to recalculate all the formula while open
981
982 sheet1.ForceFormulaRecalculation = true;
983
984 file = new FileStream(@"test.xls", FileMode.Create);
985 hssfworkbook.Write(file);
986 file.Close();
987
988 }
989
990 /// <summary>
991 /// 获取单元格类型(xlsx)
992 /// </summary>
993 /// <param name="cell"></param>
994 /// <returns></returns>
995 private static object GetValueTypeForXLSX(XSSFCell cell)
996 {
997 if (cell == null)
998 return null;
999 switch (cell.CellType)
1000 {
1001 case CellType.Blank: //BLANK:
1002 return null;
1003 case CellType.Boolean: //BOOLEAN:
1004 return cell.BooleanCellValue;
1005 case CellType.Numeric: //NUMERIC:
1006 return cell.DateCellValue;
1007
1008 //case CellType.Numeric: //NUMERIC:
1009 // return cell.NumericCellValue;
1010
1011 case CellType.String: //STRING:
1012 return cell.StringCellValue;
1013 case CellType.Error: //ERROR:
1014 return cell.ErrorCellValue;
1015 case CellType.Formula: //FORMULA:
1016 default:
1017 return "=" + cell.CellFormula;
1018 }
1019 }
1020
1021
1022 /// <summary>
1023 /// 插入图片
1024 /// </summary>
1025 /// <param name="workbook">工作表格</param>
1026 /// <param name="patriarch">图片路径</param>
1027 /// <param name="path"></param>
1028 /// <param name="sheet">表格</param>
1029 /// <param name="rowline">第几行</param>
1030 /// <param name="col">第几列</param>
1031 private static void setPic(HSSFWorkbook workbook, HSSFPatriarch patriarch, string path, ISheet sheet, int rowline, int col)
1032 {
1033 //string contentRootPath = _hostingEnvironment.ContentRootPath;
1034 path = "";// System.Web.HttpContext.Current.Server.MapPath(path);
1035 if (string.IsNullOrEmpty(path))
1036 {
1037
1038 return;
1039 }
1040
1041 if (!System.IO.File.Exists(path))
1042 {
1043 return;
1044 }
1045
1046 byte[] bytes = System.IO.File.ReadAllBytes(path);
1047
1048 //MemoryStream ms = new MemoryStream(bytes);
1049 //Image Img = Bitmap.FromStream(ms, true);
1050 //double ImageOriginalWidth = Img.Width;//原始图片的长度
1051 //double ImageOriginalHeight = Img.Height;//原始图片的宽度
1052
1053 int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
1054 // 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
1055 //关于HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)的参数
1056 //dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
1057 //dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
1058 //dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
1059 //dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
1060 //col1：起始单元格列序号，从0开始计算；
1061 //row1：起始单元格行序号，从0开始计算，如例子中col1 = 0,row1 = 0就表示起始单元格为A1；
1062 //col2：终止单元格列序号，从0开始计算；
1063 //row2：终止单元格行序号，从0开始计算，如例子中col2 = 2,row2 = 2就表示起始单元格为C3；
1064 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, rowline, col + 1, rowline + 1);
1065 //把图片插到相应的位置
1066 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
1067 //anchor.AnchorType = AnchorType.MoveDontResize;
1068 //ImageOriginalWidth = ImageOriginalWidth / 50;
1069 //ImageOriginalHeight = ImageOriginalHeight / 50;
1070
1071 pict.Resize(1, 10);
1072
1073 }
1074
1075
1076 }
1077 }
浙公网安备 33010602011771号