NPOI读取Excel

项目环境：Webform framework4.0

dll版本：NPOI2.0  dotnet2.0版本

这两天要做个excel导入的功能，想到以前用过NPOI，感觉很给力，今天写了个DEMO，写的时候还算顺利，毕竟以前用过，还是想记录下来，留着以后直接复制

把excel数据读取并拼接到DataTable中，为了使用SqlBulkCopy一次性拷贝到数据库中

 1             IWorkbook workbook = null;
 2             string fileExt = Path.GetExtension(path);
 3             try
 4             {
 5                 using (var file = new FileStream(path, FileMode.Open, FileAccess.Read))
 6                 {
 7                     if (fileExt == ".xls")
 8                         workbook = new HSSFWorkbook(file);
 9                     else if (fileExt == ".xlsx")
10                         workbook = new XSSFWorkbook(file);
11                     else
12                     {
13 
14                     }
15                 }
16             }
17             catch (Exception ex)
18             { }
View Code
 1 //获取sheet页
 2             var sheet = workbook.GetSheetAt(0);
 3             //获取总条数
 4             int RowCount = sheet.LastRowNum;
 5             //获取sheet页的第一条数据
 6             IRow firstRow = sheet.GetRow(0);
 7             //获取总列数
 8             int CellCount = firstRow.LastCellNum;
 9 
10             DataTable dt = new DataTable();
11             for (int j = 0; j < CellCount; j++)
12             {
13                 string value = firstRow.GetCell(j).StringCellValue;
14                 DataColumn dc = new DataColumn(value, typeof(String));
15                 dt.Columns.Add(dc);
16             }
17 
18             for (int i = 1; i <= RowCount; i++)
19             {
20                 IRow row = sheet.GetRow(i);
21                 DataRow dr = dt.NewRow();
22                 for (int j = 0; j < CellCount; j++)
23                 {
24                     object obj = row.GetCell(j);
25                     if (obj != null)
26                         dr[j] = obj.ToString();
27                     else
28                         dr[j] = "";
29                 }
30                 dt.Rows.Add(dr);
31             }
View Code

最后使用SqlBulkCopy

SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = "Table_1";//数据库中的表名
sqlbulkcopy.WriteToServer(dataset.Tables[0]);

 

posted on 2014-05-25 22:40  忙碌ing  阅读(786)  评论(1)  编辑  收藏  举报

导航