NPOIHelper.cs(NPOI2.1.1)

2019-11-14 16:20:55

字體：大中小

來源：轉載

供稿：網友

  1 using System;  2 using System.Collections.Generic;  3 using System.Linq;  4 using System.Text;  5 using System.Data;  6 using System.IO;  7 using NPOI.XSSF.UserModel;  8 using NPOI.SS.UserModel;  9  10 namespace NetLib 11 { 12     public static class NPOIHelper 13     { 14         public static void ExportToFile(DataSet dataSet, string fileFullPath) 15         { 16             List<DataTable> dts = new List<DataTable>(); 17             foreach (DataTable dt in dataSet.Tables) dts.Add(dt); 18             ExportToFile(dts, fileFullPath); 19         } 20         public static void ExportToFile(DataTable dataTable, string fileFullPath) 21         { 22             List<DataTable> dts = new List<DataTable>(); 23             dts.Add(dataTable); 24             ExportToFile(dts, fileFullPath); 25         } 26         public static void ExportToFile(IEnumerable<DataTable> dataTables, string fileFullPath) 27         { 28             IWorkbook workbook = new XSSFWorkbook(); 29             int i = 0; 30             foreach(DataTable dt in dataTables) 31             { 32                 string sheetName = string.IsNullOrEmpty(dt.TableName) 33                     ? "Sheet " + (++i).ToString() 34                     : dt.TableName; 35                 ISheet sheet = workbook.CreateSheet(sheetName); 36  37                 IRow headerRow = sheet.CreateRow(0); 38                 for (int j = 0; j < dt.Columns.Count; j++) 39                 { 40                     string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName) 41                         ? "Column " + j.ToString() 42                         : dt.Columns[j].ColumnName; 43                     headerRow.CreateCell(j).SetCellValue(columnName); 44                 } 45  46                 for (int a = 0; a < dt.Rows.Count; a++) 47                 { 48                     DataRow dr = dt.Rows[a]; 49                     IRow row = sheet.CreateRow(a + 1); 50                     for (int b = 0; b < dt.Columns.Count; b++) 51                     { 52                         row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty); 53                     } 54                 } 55             } 56  57             using (FileStream fs = File.Create(fileFullPath)) 58             { 59                 workbook.Write(fs); 60             } 61         } 62  63         public static List<DataTable> GetDataTablesFrom(string xlsxFile) 64         { 65             if (!File.Exists(xlsxFile)) 66                 throw new FileNotFoundException("文件不存在"); 67  68             List<DataTable> result = new List<DataTable>(); 69             Stream stream = new MemoryStream(File.ReadAllBytes(xlsxFile)); 70             IWorkbook workbook = new XSSFWorkbook(stream); 71             for (int i = 0; i < workbook.NumberOfSheets; i++) 72             { 73                 DataTable dt = new DataTable(); 74                 ISheet sheet = workbook.GetSheetAt(i); 75                 IRow headerRow = sheet.GetRow(0); 76  77                 int cellCount = headerRow.LastCellNum; 78                 for (int j = headerRow.FirstCellNum; j < cellCount; j++) 79                 { 80                     DataColumn column = new DataColumn(headerRow.GetCell(j).StringCellValue); 81                     dt.Columns.Add(column); 82                 } 83  84                 int rowCount = sheet.LastRowNum; 85                 for (int a = (sheet.FirstRowNum + 1); a < rowCount; a++) 86                 { 87                     IRow row = sheet.GetRow(a); 88                     if (row == null) continue; 89  90                     DataRow dr = dt.NewRow(); 91                     for (int b = row.FirstCellNum; b < cellCount; b++) 92                     { 93                         if (row.GetCell(b) == null) continue; 94                         dr[b] = row.GetCell(b).ToString(); 95                     } 96  97                     dt.Rows.Add(dr); 98                 } 99                 result.Add(dt);100             }101             stream.Close();102 103             return result;104         }105     }106 }