NPOI读取Excel

项目环境：Webform framework4.0

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

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

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

            IWorkbook workbook = null;
            string fileExt = Path.GetExtension(path);
            try
            {
                using (var file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    if (fileExt == ".xls")
                        workbook = new HSSFWorkbook(file);
                    else if (fileExt == ".xlsx")
                        workbook = new XSSFWorkbook(file);
                    else
                    {

                    }
                }
            }
            catch (Exception ex)
            { }

//获取sheet页
            var sheet = workbook.GetSheetAt(0);
            //获取总条数
            int RowCount = sheet.LastRowNum;
            //获取sheet页的第一条数据
            IRow firstRow = sheet.GetRow(0);
            //获取总列数
            int CellCount = firstRow.LastCellNum;

            DataTable dt = new DataTable();
            for (int j = 0; j < CellCount; j++)
            {
                string value = firstRow.GetCell(j).StringCellValue;
                DataColumn dc = new DataColumn(value, typeof(String));
                dt.Columns.Add(dc);
            }

            for (int i = 1; i <= RowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dr = dt.NewRow();
                for (int j = 0; j < CellCount; j++)
                {
                    object obj = row.GetCell(j);
                    if (obj != null)
                        dr[j] = obj.ToString();
                    else
                        dr[j] = "";
                }
                dt.Rows.Add(dr);
            }

最后使用SqlBulkCopy

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