.net上传文件，利用npoi读取文件信息到datatable里

整理代码，.net上传文件，利用npoi读取文件到datatable里，使用了FileUpload控件，代码如下：

 1 protected void Button1_Click(object sender, EventArgs e)
 2 {
 3     try
 4     {
 5         #region 上传文件
 6         if (FileUpload1.HasFile)//判断是否存在上传文件
 7         FileUpload1.SaveAs(Server.MapPath("~/") + FileUpload1.FileName);
 8         else
 9         {
10             ScriptManager.RegisterClientScriptBlock(this, GetType(), "aaa", "alert('上传文件不存在或上传文件为空，请检查文件');", true);
11             return;
12         }
13         #endregion
14         #region 读取文件
15         DataTable dt = null;
16         string fileEx = FileUpload1.FileName.Substring(FileUpload1.FileName.LastIndexOf(".") + 1);
17         if (fileEx == "xls")
18         {
19             dt = IO.ImportExcelFile(Server.MapPath("~/") + FileUpload1.FileName);
20         }
21         else if (fileEx == "xlsx")
22         {
23             dt = IO.ImportExcelFilexlsx(Server.MapPath("~/") + FileUpload1.FileName);
24         }
25         if (dt.Rows.Count < 1)
26         {
27             ScriptManager.RegisterClientScriptBlock(this, GetType(), "aaa", "alert('上传文件为空，请检查文件内容');", true);
28             return;
29         }
30         #endregion
31     }
32     catch (Exception ex)
33     {
34         ScriptManager.RegisterClientScriptBlock(this, GetType(), "aaa", "alert('系统异常，" + ex + "');", true);
35     }
36 }

npoi读取文件的方法：

 1 public static DataTable ImportExcelFile(string filePath)
 2 {
 3     HSSFWorkbook hssfworkbook;
 4     #region//初始化信息
 5     try
 6     {
 7         using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
 8         {
 9             hssfworkbook = new HSSFWorkbook(file);
10         }
11     }
12     catch (Exception e)
13     {
14         throw e;
15     }
16     #endregion
17     NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
18     System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
19     DataTable dt = new DataTable();
20     for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
21     {
22         dt.Columns.Add(sheet.GetRow(0).GetCell(j).ToString());
23     }
24     while (rows.MoveNext())
25     {
26         HSSFRow row = (HSSFRow)rows.Current;
27         if (row.RowNum != 0)
28         {
29             DataRow dr = dt.NewRow();
30             for (int i = 0; i < row.LastCellNum; i++)
31             {
32                 NPOI.SS.UserModel.ICell cell = row.GetCell(i);
33                 if (cell == null)
34                 {
35                     dr[i] = null;
36                 }
37                 else
38                 {
39                     dr[i] = cell.ToString();
40                 }
41             }
42             dt.Rows.Add(dr);
43         }
44     }
45     return dt;
46 }
47 public static DataTable ImportExcelFilexlsx(string filePath)
48 {
49     XSSFWorkbook hssfworkbook;
50     #region//初始化信息
51     try
52     {
53         using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
54         {
55             hssfworkbook = new XSSFWorkbook(file);
56         }
57     }
58     catch (Exception e)
59     {
60         throw e;
61     }
62     #endregion
63     NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
64     System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
65     DataTable dt = new DataTable();
66     for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
67     {
68         dt.Columns.Add(sheet.GetRow(0).GetCell(j).ToString());
69     }
70     while (rows.MoveNext())
71     {
72         XSSFRow row = (XSSFRow)rows.Current;
73         if (row.RowNum != 0)
74         {
75             DataRow dr = dt.NewRow();
76             for (int i = 0; i < row.LastCellNum; i++)
77             {
78                 NPOI.SS.UserModel.ICell cell = row.GetCell(i);
79                 if (cell == null)
80                 {
81                     dr[i] = null;
82                 }
83                 else
84                 {
85                     dr[i] = cell.ToString();
86                 }
87             }
88             dt.Rows.Add(dr);
89         }
90     }
91     return dt;
92 }

 

转载于:https://www.cnblogs.com/Lvkang/p/9358823.html