• NPOI常用功能工具类

      1     public class NPOIHelper
  2     {
  3         /// <summary>
  4         /// DataTable导出到Excel文件
  5         /// </summary>
  6         /// <param name="dtSource">源DataTable</param>
  7         /// <param name="strHeaderText">表头文本</param>
  8         /// <param name="strFileName">保存位置</param>
  9         public static void ExportByServer(DataTable dtSource, string strHeaderText, string strFileName)
 10         {
 11             using (MemoryStream ms = Export(dtSource, strHeaderText))
 12             {
 13                 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
 14                 {
 15                     byte[] data = ms.ToArray();
 16                     fs.Write(data, 0, data.Length);
 17                     fs.Flush();
 18                 }
 19             }
 20         }
 21 
 22 
 23         /// <summary>
 24         /// 用于Web导出
 25         /// </summary>
 26         /// <param name="dtSource">源DataTable</param>
 27         /// <param name="strHeaderText">表头文本</param>
 28         /// <param name="strFileName">文件名</param>
 29         public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
 30         {
 31             HttpContext curContext = HttpContext.Current;
 32 
 33             // 设置编码和附件格式
 34             curContext.Response.ContentType = "application/vnd.ms-excel";
 35             curContext.Response.ContentEncoding = Encoding.UTF8;
 36             curContext.Response.Charset = "";
 37             curContext.Response.AppendHeader("Content-Disposition",
 38                 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
 39 
 40             curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
 41             curContext.Response.End();
 42         }
 43 
 44         /// <summary>读取excel
 45         /// 默认第一行为标头
 46         /// </summary>
 47         /// <param name="strFileName">excel文档路径</param>
 48         /// <returns></returns>
 49         public static DataTable Import(string strFileName)
 50         {
 51             DataTable dt = new DataTable();
 52 
 53             HSSFWorkbook hssfworkbook;
 54             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
 55             {
 56                 hssfworkbook = new HSSFWorkbook(file);
 57             }
 58             ISheet sheet = hssfworkbook.GetSheetAt(0);
 59             System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
 60 
 61             IRow headerRow = sheet.GetRow(0);
 62             int cellCount = headerRow.LastCellNum;
 63 
 64             for (int j = 0; j < cellCount; j++)
 65             {
 66                 ICell cell = headerRow.GetCell(j);
 67                 dt.Columns.Add(cell.ToString());
 68             }
 69 
 70             for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
 71             {
 72                 IRow row = sheet.GetRow(i);
 73                 DataRow dataRow = dt.NewRow();
 74 
 75                 for (int j = row.FirstCellNum; j < cellCount; j++)
 76                 {
 77                     if (row.GetCell(j) != null)
 78                         dataRow[j] = row.GetCell(j).ToString();
 79                 }
 80 
 81                 dt.Rows.Add(dataRow);
 82             }
 83             return dt;
 84         }
 85 
 86         
 87         /// <summary>
 88         /// DataTable导出到Excel的MemoryStream
 89         /// </summary>
 90         /// <param name="dtSource">源DataTable</param>
 91         /// <param name="strHeaderText">表头文本</param>
 92         public static MemoryStream Export(DataTable dtSource, string strHeaderText)
 93         {
 94             HSSFWorkbook workbook = new HSSFWorkbook();
 95             ISheet sheet = workbook.CreateSheet();
 96 
 97             #region 右击文件 属性信息
 98             {
 99                 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
100                 dsi.Company = "Sohu";
101                 workbook.DocumentSummaryInformation = dsi;
102 
103                 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
104                 si.Author = "文件作者信息"; //填加xls文件作者信息
105                 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
106                 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
107                 si.Comments = "作者信息"; //填加xls文件作者信息
108                 si.Title = "标题信息"; //填加xls文件标题信息
109                 si.Subject = "主题信息";//填加文件主题信息
110                 si.CreateDateTime = DateTime.Now;
111                 workbook.SummaryInformation = si;
112             }
113             #endregion
114 
115             ICellStyle dateStyle = workbook.CreateCellStyle();
116             IDataFormat format = workbook.CreateDataFormat();
117             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
118 
119             //取得列宽
120             int[] arrColWidth = new int[dtSource.Columns.Count];
121             foreach (DataColumn item in dtSource.Columns)
122             {
123                 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
124             }
125             for (int i = 0; i < dtSource.Rows.Count; i++)
126             {
127                 for (int j = 0; j < dtSource.Columns.Count; j++)
128                 {
129                     int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
130                     if (intTemp > arrColWidth[j])
131                     {
132                         arrColWidth[j] = intTemp;
133                     }
134                 }
135             }
136             int rowIndex = 0;
137             foreach (DataRow row in dtSource.Rows)
138             {
139                 #region 新建表，填充表头，填充列头，样式
140                 if (rowIndex == 65535 || rowIndex == 0)
141                 {
142                     if (rowIndex != 0)
143                     {
144                         sheet = workbook.CreateSheet();
145                     }
146 
147                     #region 表头及样式
148                     {
149                         IRow headerRow = sheet.CreateRow(0);
150                         headerRow.HeightInPoints = 25;
151                         headerRow.CreateCell(0).SetCellValue(strHeaderText);
152 
153                         ICellStyle headStyle = workbook.CreateCellStyle();
154                         headStyle.Alignment = HorizontalAlignment.Center;
155                         IFont font = workbook.CreateFont();
156                         font.FontHeightInPoints = 20;
157                         font.Boldweight = 700;
158                         headStyle.SetFont(font);
159                         headerRow.GetCell(0).CellStyle = headStyle;
160                         sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
161 
162                     }
163                     #endregion
164 
165 
166                     #region 列头及样式
167                     {
168                         IRow headerRow = sheet.CreateRow(1);
169 
170                         ICellStyle headStyle = workbook.CreateCellStyle();
171                         headStyle.Alignment = HorizontalAlignment.Center;
172 
173 
174                         IFont font = workbook.CreateFont();
175                         font.FontHeightInPoints = 10;
176                         font.Boldweight = 700;
177                         headStyle.SetFont(font);
178                         foreach (DataColumn column in dtSource.Columns)
179                         {
180                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
181                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
182 
183                             //设置列宽
184                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
185                         }
186 
187                     }
188                     #endregion
189 
190                     rowIndex = 2;
191                 }
192                 #endregion
193 
194 
195                 #region 填充内容
196                 IRow dataRow = sheet.CreateRow(rowIndex);
197                 foreach (DataColumn column in dtSource.Columns)
198                 {
199                     ICell newCell = dataRow.CreateCell(column.Ordinal);
200 
201                     string drValue = row[column].ToString();
202 
203                     switch (column.DataType.ToString())
204                     {
205                         case "System.String"://字符串类型
206                             newCell.SetCellValue(drValue);
207                             break;
208                         case "System.DateTime"://日期类型
209                             DateTime dateV;
210                             DateTime.TryParse(drValue, out dateV);
211                             newCell.SetCellValue(dateV);
212 
213                             newCell.CellStyle = dateStyle;//格式化显示
214                             break;
215                         case "System.Boolean"://布尔型
216                             bool boolV = false;
217                             bool.TryParse(drValue, out boolV);
218                             newCell.SetCellValue(boolV);
219                             break;
220                         case "System.Int16"://整型
221                         case "System.Int32":
222                         case "System.Int64":
223                         case "System.Byte":
224                             int intV = 0;
225                             int.TryParse(drValue, out intV);
226                             newCell.SetCellValue(intV);
227                             break;
228                         case "System.Decimal"://浮点型
229                         case "System.Double":
230                             double doubV = 0;
231                             double.TryParse(drValue, out doubV);
232                             newCell.SetCellValue(doubV);
233                             break;
234                         case "System.DBNull"://空值处理
235                             newCell.SetCellValue("");
236                             break;
237                         default:
238                             newCell.SetCellValue("");
239                             break;
240                     }
241 
242                 }
243                 #endregion
244 
245                 rowIndex++;
246             }
247             using (MemoryStream ms = new MemoryStream())
248             {
249                 workbook.Write(ms);
250                 ms.Flush();
251                 ms.Position = 0;
252 
253                 //sheet.Dispose();
254                 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
255                 return ms;
256             }
257         }
258 
259 
260         /// <summary>
261         /// DataTable按模板导出到Excel文件
262         /// </summary>
263         /// <param name="dtSource">源DataTable</param>
264         /// <param name="TempletFileName">模板文件</param>
265         /// <param name="strFileName">保存位置</param>
266         public static void ExportByTemplate(string TempletFileName, string strFileName, DataTable dt, string Type)
267         {
268             using (MemoryStream ms = ExportByTemp(TempletFileName, dt, Type))
269             {
270                 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
271                 {
272                     byte[] data = ms.ToArray();
273                     fs.Write(data, 0, data.Length);
274                     fs.Flush();
275                 }
276             }
277         }
278         /// <summary>
279         /// DataTable导出到Excel的MemoryStream
280         /// </summary>
281         /// <param name="dt">数据源</param>
282         /// <param name="TempletFileName">Excel模板</param>
283         /// <returns></returns>
284         public static MemoryStream ExportByTemp(string TempletFileName, DataTable dt,string Type)
285         {
286             FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
287 
288             HSSFWorkbook workbook = new HSSFWorkbook(file);
289             ISheet sheet = workbook.GetSheetAt(1);
290 
291             //ICellStyle dateStyle = workbook.CreateCellStyle();
292             //dateStyle.BorderBottom = BorderStyle.Thin;
293             //dateStyle.BorderLeft = BorderStyle.Thin;
294             //dateStyle.BorderRight = BorderStyle.Thin;
295             //dateStyle.BorderTop = BorderStyle.Thin;
296 
297             int rowIndex = 0;
298 
299             foreach (DataRow entity in dt.Rows)
300             {
301                 IRow dataRow = sheet.CreateRow(rowIndex);
302 
303                 if (Type.Equals("SaleType"))
304                 {
305                     ICell newCell0 = dataRow.CreateCell(0);
306                     newCell0.SetCellValue(entity[2].ToString());
307                     //newCell0.CellStyle = dateStyle;
308                 }
309                 else {
310                     ICell newCell0 = dataRow.CreateCell(0);
311                     newCell0.SetCellValue(entity[1].ToString());
312                     //newCell0.CellStyle = dateStyle;
313                 }
314                 //ICell newCell7 = dataRow.CreateCell(7);
315                 //if (type.Equals("excel"))
316                 //{
317                 //    newCell7.SetCellValue(entity.Location == "" ? "" : "第" + entity.Location + "行");
318                 //}
319                 //else if (type.Equals("word"))
320                 //{
321                 //    newCell7.SetCellValue(entity.Location == "" ? "" : "第" + entity.Location + "页");
322                 //}
323                 //newCell7.CellStyle = dateStyle;
324 
325 
326 
327                 rowIndex++;
328             }
329             if (dt.Rows.Count == 1)
330             {
331                 IRow dataRow = sheet.CreateRow(rowIndex);
332                 ICell newCelllast = dataRow.CreateCell(0);
333                 newCelllast.SetCellValue(" ");
334             }
335 
336              
337 
338             using (MemoryStream ms = new MemoryStream())
339             {
340                 workbook.Write(ms);
341                 ms.Flush();
342                 ms.Position = 0;
343 
344                 //sheet.Dispose();
345                 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
346                 return ms;
347             }
348         }
349 
350 
351         public static void ExportByNodeData(string TfileName, string StrfileName, DataTable dt, string Type,double Rate)
352         {
353             using (MemoryStream ms = ExportByNode(TfileName, dt, Type,Rate))
354             {
355                 using (FileStream fs = new FileStream(StrfileName, FileMode.Create, FileAccess.Write))
356                 {
357                     byte[] data = ms.ToArray();
358                     fs.Write(data, 0, data.Length);
359                     fs.Flush();
360                 }
361             }
362         }
363         public static MemoryStream ExportByNode(string TempletFileName, DataTable dt, string Type, double Rate)
364         {
365             FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
366 
367             HSSFWorkbook workbook = new HSSFWorkbook(file);
368             ISheet sheet = workbook.GetSheetAt(0);
369 
370             ICellStyle dateStyle = workbook.CreateCellStyle();
371             dateStyle.BorderBottom = BorderStyle.Thin;
372             dateStyle.BorderLeft = BorderStyle.Thin;
373             dateStyle.BorderRight = BorderStyle.Thin;
374             dateStyle.BorderTop = BorderStyle.Thin;
375 
376             int rowIndex = 1;
377 
378             foreach (DataRow entity in dt.Rows)
379             {
380                 IRow dataRow = sheet.CreateRow(rowIndex);
381 
382                 if (Type.Equals("SaleTypeExport"))
383                 {
384                     ICell newCell0 = dataRow.CreateCell(0);
385                     newCell0.SetCellValue(rowIndex);
386                     newCell0.CellStyle = dateStyle;
387 
388                     ICell newCell1 = dataRow.CreateCell(1);
389                     newCell1.SetCellValue(entity["new_nodename"].ToString());
390                     newCell1.CellStyle = dateStyle;
391 
392                     ICell newCell2 = dataRow.CreateCell(2);
393                     newCell2.SetCellValue(Convert.ToDouble(entity["new_lastyear_total"].ToString()));
394                     newCell2.CellStyle = dateStyle;
395 
396                     ICell newCell3 = dataRow.CreateCell(3);
397                     newCell3.SetCellValue(Convert.ToDouble(entity["new_total_assign2me"].ToString()));
398                     newCell3.CellStyle = dateStyle;
399 
400                     ICell newCell4 = dataRow.CreateCell(4);
401                     newCell4.SetCellValue(Convert.ToInt32(entity["new_accountadd"].ToString()));
402                     newCell4.CellStyle = dateStyle;
403 
404                     ICell newCell5 = dataRow.CreateCell(5);
405                     newCell5.SetCellValue(Convert.ToDouble(entity["new_total_assign2me"].ToString()) - (Convert.ToDouble(entity["new_total_assign2me"].ToString()) * Rate));
406                     newCell5.CellStyle = dateStyle;
407 
408                     ICell newCell6 = dataRow.CreateCell(6);
409                     newCell6.SetCellValue((Convert.ToDouble(entity["new_total_assign2me"].ToString()) * Rate));
410                     newCell6.CellStyle = dateStyle;
411 
412                     ICell newCell7 = dataRow.CreateCell(7);
413                     newCell7.SetCellValue(Convert.ToDouble(entity["RefeAmount"].ToString()));
414                     newCell7.CellStyle = dateStyle;
415 
416                     ICell newCell8 = dataRow.CreateCell(8);
417                     newCell8.SetCellValue(entity["new_description"].ToString());
418                     newCell8.CellStyle = dateStyle;
419                 }
420                 else if (Type.Equals("AccountTypeExport"))
421                 {
422                     ICell newCell0 = dataRow.CreateCell(0);
423                     newCell0.SetCellValue(rowIndex);
424                     newCell0.CellStyle = dateStyle;
425 
426                     ICell newCell1 = dataRow.CreateCell(1);
427                     newCell1.SetCellValue(entity["new_name"].ToString());
428                     newCell1.CellStyle = dateStyle;
429 
430                     ICell newCell2 = dataRow.CreateCell(2);
431                     newCell2.SetCellValue(Convert.ToDouble(entity["new_lastyear_total"].ToString()));
432                     newCell2.CellStyle = dateStyle;
433 
434                     ICell newCell3 = dataRow.CreateCell(3);
435                     newCell3.SetCellValue(Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()));
436                     newCell3.CellStyle = dateStyle;
437 
438                     ICell newCell4 = dataRow.CreateCell(4);
439                     newCell4.SetCellValue(Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) - (Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) * Rate));
440                     newCell4.CellStyle = dateStyle;
441 
442                     ICell newCell5 = dataRow.CreateCell(5);
443                     newCell5.SetCellValue((Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) * Rate));
444                     newCell5.CellStyle = dateStyle;
445 
446                     ICell newCell6 = dataRow.CreateCell(6);
447                     newCell6.SetCellValue(Convert.ToDouble(entity["RefeAmount"].ToString()));
448                     newCell6.CellStyle = dateStyle;
449 
450                     ICell newCell7 = dataRow.CreateCell(7);
451                     newCell7.SetCellValue(entity["new_nodetemdesc_description"].ToString());
452                     newCell7.CellStyle = dateStyle;
453                 }
454                 else if (Type.Equals("NodeDescByAccountExport"))
455                 {
456                     ICell newCell0 = dataRow.CreateCell(0);
457                     newCell0.SetCellValue(rowIndex);
458                     newCell0.CellStyle = dateStyle;
459 
460                     ICell newCell1 = dataRow.CreateCell(1);
461                     newCell1.SetCellValue(entity["new_name"].ToString());
462                     newCell1.CellStyle = dateStyle;
463 
464                     ICell newCell2 = dataRow.CreateCell(2);
465                     newCell2.SetCellValue(Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()));
466                     newCell2.CellStyle = dateStyle;
467 
468                     ICell newCell3 = dataRow.CreateCell(3);
469                     newCell3.SetCellValue(Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) - (Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) * Rate));
470                     newCell3.CellStyle = dateStyle;
471 
472                     ICell newCell4 = dataRow.CreateCell(4);
473                     newCell4.SetCellValue((Convert.ToDouble(entity["new_nodetemdesc_amount"].ToString()) * Rate));
474                     newCell4.CellStyle = dateStyle;
475 
476                     ICell newCell5 = dataRow.CreateCell(5);
477                     newCell5.SetCellValue(Convert.ToDouble(entity["RefeAmount"].ToString()));
478                     newCell5.CellStyle = dateStyle;
479 
480                     ICell newCell6 = dataRow.CreateCell(6);
481                     newCell6.SetCellValue(entity["new_nodetemdesc_description"].ToString());
482                     newCell6.CellStyle = dateStyle;
483                 }
484                 else if (Type.Equals("NodeDescBySaleExport"))
485                 {
486                     ICell newCell0 = dataRow.CreateCell(0);
487                     newCell0.SetCellValue(rowIndex);
488                     newCell0.CellStyle = dateStyle;
489 
490                     ICell newCell1 = dataRow.CreateCell(1);
491                     newCell1.SetCellValue(entity["new_name"].ToString());
492                     newCell1.CellStyle = dateStyle;
493 
494                     ICell newCell2 = dataRow.CreateCell(2);
495                     newCell2.SetCellValue(Convert.ToDouble(entity["new_distributeamount"].ToString()));
496                     newCell2.CellStyle = dateStyle;
497 
498                     ICell newCell3 = dataRow.CreateCell(3);
499                     newCell3.SetCellValue(Convert.ToDouble(entity["new_distributeamount"].ToString()) - (Convert.ToDouble(entity["new_distributeamount"].ToString()) * Rate));
500                     newCell3.CellStyle = dateStyle;
501 
502                     ICell newCell4 = dataRow.CreateCell(4);
503                     newCell4.SetCellValue((Convert.ToDouble(entity["new_distributeamount"].ToString()) * Rate));
504                     newCell4.CellStyle = dateStyle;
505 
506                     ICell newCell5 = dataRow.CreateCell(5);
507                     newCell5.SetCellValue(Convert.ToDouble(entity["RefeAmount"].ToString()));
508                     newCell5.CellStyle = dateStyle;
509 
510                     ICell newCell6 = dataRow.CreateCell(6);
511                     newCell6.SetCellValue(entity["new_description"].ToString());
512                     newCell6.CellStyle = dateStyle;
513                 }
514                 rowIndex++;
515             }
516 
517             using (MemoryStream ms = new MemoryStream())
518             {
519                 workbook.Write(ms);
520                 ms.Flush();
521                 ms.Position = 0;
522 
523                 //sheet.Dispose();
524                 //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
525                 return ms;
526             }
527         }
528 
529     }
    View Code
  • 相关阅读:
    深度学习优化方法比较
    调参
    Numpy/Pytorch之数据类型与强制转换
    numpy：维度问题
    js模板引擎-juicer
    js模板引擎-腾讯artTemplate 简洁语法例子
    canva绘制时钟
    js中的break ,continue, return
    JavaScript奇技淫巧44招
    数据类型
  • 原文地址：https://www.cnblogs.com/servant/p/5611024.html
Copyright © 2020-2023  润新知