导入Excel输出DataTable

1、Excel帮助类

 1 public static class ExcelHelper
 2     {
 3         public static DataTable ReadToDataTable(string strFileName)
 4         {
 5             IWorkbook workbook;
 6             using (FileStream file = new(strFileName, FileMode.Open, FileAccess.Read))
 7             {
 8                 if (Path.GetExtension(strFileName).ToLower().Equals(".xlsx"))
 9                 {
10                     workbook = new XSSFWorkbook(file);
11                 }
12                 else
13                 {
14                     workbook = new HSSFWorkbook(file);
15                 }
16             }
17             //按照工作页的名称获取指定工作表数据 ISheet sheet = workbook.GetSheet(worksheetName);
18             //也可以按照工作页下标顺序获取,如：ISheet sheet=workbook.GetSheetAt(0);
19             ISheet sheet = workbook.GetSheetAt(0);
20             IRow headerRow = sheet.GetRow(0);//0行是获取的列名
21             int cellCount = headerRow.LastCellNum;
22             DataTable dt = new();
23             //循环获取列名
24             for (int j = 0; j < cellCount; j++)
25             {
26                 ICell cell = headerRow.GetCell(j);
27                 if (cell == null || string.IsNullOrEmpty(cell.ToString()))
28                 {
29                     continue;
30                 }
31                 dt.Columns.Add(cell.ToString());
32             }
33             //循环获取表格数据
34             for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
35             {
36                 IRow row = sheet.GetRow(i);
37                 if (row == null)
38                 {
39                     continue;
40                 }
41                 DataRow dataRow = dt.NewRow();
42                 for (int j = row.FirstCellNum; j < cellCount; j++)
43                 {
44                     if (row.GetCell(j) != null && !string.IsNullOrEmpty(row.GetCell(j).ToString()))
45                     {
46                         dataRow[j] = row.GetCell(j).ToString();
47                     }
48                 }
49                 dt.Rows.Add(dataRow);
50             }
51             return dt;
52         }
53     }

2、调用

 1 var dt = ExcelHelper.ReadToDataTable(inputDto.FilePath); 

posted @ 2022-05-18 13:31  进击的黑大帅  阅读(50)  评论(0)  编辑  收藏  举报