NPOI把Excel导入到数据库

二，把Excel中的数据导入到数据库的具体步骤：

        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                //文件流
                using (Stream stream = new FileStream(@"G:userInfo.xls", FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(stream);
                    HSSFSheet sheet = workbook.GetSheetAt(0);

                    //Execel第一行是标题，不是要导入数据库的数据
                    for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        HSSFRow row = sheet.GetRow(i);
                        UserInfo userinfo = new UserInfo();
                        userinfo.UserName = row.GetCell(0).StringCellValue;
                        //判断Excel中的Age的类型，根据不同的类型来用不同的方式取值
                        if (row.GetCell(1).CellType == HSSFCell.CELL_TYPE_NUMERIC)
                        {
                            userinfo.Age = row.GetCell(1).NumericCellValue;
                        }
                        else
                        {
                            userinfo.Age =Convert.ToInt32(row.GetCell(1).StringCellValue);
                        }
                        userinfo.Email = row.GetCell(2).StringCellValue;
                        //电话号码同样如此
                        if (row.GetCell(3).CellType == HSSFCell.CELL_TYPE_NUMERIC)
                        {
                            userinfo.Telephone = row.GetCell(3).NumericCellValue.ToString();
                        }
                        else
                        {
                            userinfo.Telephone = row.GetCell(3).StringCellValue;
                        }
                        userinfo.AddDate = row.GetCell(4).DateCellValue;
                        userinfo.Address = row.GetCell(5).StringCellValue;
                        //注意：Excel中可空的地方，Remark可以不填，因此我们需要判断。
                        if (row.GetCell(6)==null)
                        {
                            userinfo.Remarks = "";
                        }
                        else
                        {
                            userinfo.Remarks = row.GetCell(6).StringCellValue;
                        }
                        new UserInfoBLL().AddNew(userinfo);

                    }
                }
                 Response.Write("导入数据成功");
            }
            catch (Exception ex)
            {
                Response.Write("错误：" + ex.Message);
            }
        }

 另一个方法：

Aspose

NPOI把Excel导入到数据库

标签：