NPOIHelper.cs (NPOI 2.1.1)

  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 }