excel 导入 

 1  public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
 2         {
 3             IWorkbook workbook = null;
 4             ISheet sheet = null;
 5             DataTable data = new DataTable("DataTable");
 6             int startRow = 0;
 7 
 8             string fileExt = Path.GetExtension(fileName);    //获取文件的后缀名
 9             FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
10             if (fileExt == ".xlsx") // 2007版本
11             {
12                 workbook = new XSSFWorkbook(fs);
13             }
14             else if (fileExt == ".xls") // 2003版本
15             {
16                 workbook = new HSSFWorkbook(fs);
17             }
18             if (sheetName != null)
19             {
20                 sheet = workbook.GetSheet(sheetName);
21                 if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
22                 {
23                     sheet = workbook.GetSheetAt(0);
24                 }
25             }
26             else
27             {
28                 sheet = workbook.GetSheetAt(0);
29             }
30             if (sheet != null)
31             {
32                 IRow firstRow = sheet.GetRow(0);
33                 int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
34                 if (isFirstRowColumn)
35                 {
36                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
37                     {
38                         ICell cell = firstRow.GetCell(i);
39                         if (cell != null)
40                         {
41                             string cellValue = cell.StringCellValue;
42                             if (cellValue != null)
43                             {
44                                 DataColumn column = new DataColumn(cellValue);
45                                 data.Columns.Add(column);
46                             }
47                         }
48                     }
49                     startRow = sheet.FirstRowNum + 1;
50                 }
51                 else
52                 {
53                     startRow = sheet.FirstRowNum;
54                 }
55                 //最后一列的标号
56                 int rowCount = sheet.LastRowNum;
57                 DataRow dataRow;
58                 for (int i = startRow; i <= rowCount; ++i)
59                 {
60                     IRow row = sheet.GetRow(i);
61                     if (row == null)
62                     {
63                         continue; //没有数据的行默认是null　　
64                     }
65                     dataRow = data.NewRow();
66                     for (int j = row.FirstCellNum; j < cellCount; ++j)
67                     {
68                         if (row.GetCell(j).CellType == CellType.Numeric)
69                         {
70                             dataRow[j] = row.GetCell(j).NumericCellValue;
71                         }
72                         else
73                         {
74                             dataRow[j] = row.GetCell(j).ToString();
75                         }
76                     }
77                     data.Rows.Add(dataRow);
78                 }
79             }
80             return data;
81         }

调用： 

  dt = ExcelHelper.ExcelToDataTable(txtFilePath.Text, "", true);
//txtFilePath.Text：上传路径
上传：
 1 private void btnOpen_Click(object sender, EventArgs e)
 2         {
 3             OpenFileDialog fileDialog = new OpenFileDialog();
 4             fileDialog.Title = "请打开Excel文件";
 5             fileDialog.Filter = "(EXCEL 文件)|*.xls;*.xlsx"; //设置要选择的文件的类型
 6             if (fileDialog.ShowDialog() == DialogResult.OK)
 7             {
 8                 txtFilePath.Text = fileDialog.FileName;//返回文件的完整路径
 9             }
10         }

 

 
posted @ 2018-03-28 14:15  Bonnie_W  阅读(167)  评论(0)  编辑  收藏  举报