NPOI读取Excel文件 

 1 public class ExcelOperator
 2     {
 3         public static List<DataTable> Read(string fileName)
 4         {
 5             List<DataTable> tables = new List<DataTable>();
 6             if (!File.Exists(fileName))
 7                 return tables;
 8             var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
 9             IWorkbook workbook = null;
10             if (fileName.IndexOf(".xlsx") > 0)
11             {
12                 // 2007版本
13                 workbook = new XSSFWorkbook(fs);
14             }
15             else if (fileName.IndexOf(".xls") > 0)
16             {
17                 // 2003版本
18                 workbook = new HSSFWorkbook(fs);
19             }
20 
21             var workbooks = workbook.GetEnumerator();
22             while (workbooks.MoveNext())
23             {
24                 ISheet sheet = workbooks.Current as ISheet;
25                 DataTable dt = new DataTable(sheet.SheetName);
26                 var rows = sheet.GetRowEnumerator();
27                 while (rows.MoveNext())
28                 {
29                     IRow row = rows.Current as IRow;
30                     if (row.RowNum == 0)
31                     {
32                         row.Cells.ForEach(cell =>
33                         {
34                             dt.Columns.Add(cell.StringCellValue);
35                         });
36                     }
37                     else
38                     {
39                         var dr = dt.NewRow();
40                         for (int i = 0; i < row.Cells.Count; i++)
41                         {
42                             dr[i] = row.Cells[i].ToString();
43                         }
44                         dt.Rows.Add(dr);
45                     }
46                 }
47 
48                 tables.Add(dt);
49             }
50             return tables;
51         }
52     }
读取整个EXCEL方法

 

 1 public static DataTable ReadExcel(string fileName, string sheetname)
 2         {
 3             if (!File.Exists(fileName))
 4                 return null;
 5             var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
 6             IWorkbook workbook = null;
 7             if (fileName.IndexOf(".xlsx") > 0)
 8             {
 9                 // 2007版本
10                 workbook = new XSSFWorkbook(fs);
11             }
12             else if (fileName.IndexOf(".xls") > 0)
13             {
14                 // 2003版本
15                 workbook = new HSSFWorkbook(fs);
16             }
17             ISheet sheet = workbook.GetSheet(sheetname);
18             DataTable dt = new DataTable();
19             var rows = sheet.GetRowEnumerator();
20             while (rows.MoveNext())
21             {
22                 IRow row = rows.Current as IRow;
23                 if (row.RowNum == 0)
24                 {
25                     row.Cells.ForEach(cell =>
26                     {
27                         dt.Columns.Add(cell.StringCellValue);
28                     });
29                 }
30                 else
31                 {
32                     var dr = dt.NewRow();
33                     for (int i = 0; i < row.Cells.Count; i++)
34                     {
35                         dr[i] = row.Cells[i].ToString();
36                     }
37                     dt.Rows.Add(dr);
38                 }
39             }
40             return dt;
41         }
读取指定SheetName的表

 

posted @ 2018-12-11 16:55  鸑鷟虽孤  阅读(1195)  评论(0)  编辑  收藏  举报