C# .Net :Excel NPOI导入导出操作教程之将Excel文件读取并写到数据库表,示例分享

 using (FileStream fileReader =
File.OpenRead(@”C:\Users\Administrator\Desktop\112.xls”))
        {
            //创设工作簿对象抽出文件流(Excel新闻)
            IWorkbook workbook = new
HSSFWorkbook(fileReader);
            //工作簿共有多少个表
            //int count = workbook.NumberOfSheets;
            //创造工作表读取专业簿表新闻
            //ISheet sheet= workbook.GetSheet(“表名称”);
            ISheet sheet =
workbook.GetSheetAt(0);
            string sql = @” insert into
T_ExcelIn(Name,Remarks) values (@Name,@Remarks)”;
            int ii = 0;
            //r = 1,剔除表头一行
            for (int r = 1; r <=
sheet.LastRowNum; r++)
            {
                //定义参数数组para
                SqlParameter[] para = new
SqlParameter[] {
                    new
SqlParameter(“Name”,SqlDbType.NVarChar,50),
                    new
SqlParameter(“Remarks”,SqlDbType.NVarChar,50)
                };
                //创造一行获取sheet行数据
                IRow row =
sheet.GetRow(r);
                List<ICell> listcell
= new List<ICell>();
                //int c = 1 即不读取自动编号的Id列
                for (int c = 1; c <
row.LastCellNum; c++)
                {
                    //将每行各种单元格的值增多带listcell集合中
                   
listcell.Add(row.GetCell(c));
                }
                //循环赋值给para
                for (int i = 0; i <
listcell.Count; i++)
                {
                    para[i].Value =
listcell[i].ToString();
                }
                //施行增添Sql语句
                ii +=
SqlHelper.ExecuteNonQuery(sql, para);
               
            }
            if (ii > 0)
            {
               
Response.Write(“<script>alert(‘已将Excel数据插入到数据库表’)</script>”);
            }
            else
            {
               
Response.Write(“<script>alert(‘Excel数据插入到多少库表战败!’)</script>”);
            }

        }

 

————————————参数字传送递不通过List<>会集————————————

using (FileStream fileReader =
File.OpenRead(@”C:\Users\Administrator\Desktop\112.xls”))
        {
           
//创立职业簿对象收取文件流(Excel新闻)
            IWorkbook workbook = new HSSFWorkbook(fileReader);
            //事业簿共有多少个表
            //int count =
workbook.NumberOfSheets;
           
//成立工作表读取职业簿表音讯
            //ISheet sheet= workbook.GetSheet(“表名称”);
            ISheet sheet = workbook.GetSheetAt(0);
            string sql = @” insert into T_ExcelIn(Name,Remarks) values
(@Name,@Remarks)”;
            int ii = 0;
            //r = 1,剔除表头一行
            for (int r = 1; r <= sheet.LastRowNum; r++)
            {
                //定义参数数组para
                SqlParameter[] para = new SqlParameter[] {
                    new SqlParameter(“Name”,SqlDbType.NVarChar,50),
                    new SqlParameter(“Remarks”,SqlDbType.NVarChar,50)
                };
                //创设一行获取sheet行数据
                IRow row = sheet.GetRow(r);
                //List<ICell>
listcell = new List<ICell>();
                //int c = 1
即不读取自动编号的Id列
                for (int c = 1; c < row.LastCellNum; c++)
                {
                    //将每行各样单元格的值增添带listcell集结中
                   
//listcell.Add(row.GetCell(c));
                   
//将每行的每一个单元格的数据增加到para中 c-1即从0 开首记录参数
                    para[c-1].Value = row.GetCell(c).ToString();
                }
                //循环赋值给para
                //for (int i = 0; i <
listcell.Count; i++)
                //{
                //    para[i].Value =
listcell[i].ToString();
                //}
                //实施增多Sql语句
                ii = SqlHelper.ExecuteNonQuery(sql, para);
            }
            if (ii > 0)
            {
               
Response.Write(“<script>alert(‘已将Excel数据插入到数据库表’)</script>”);
            }
            else
            {
               
Response.Write(“<script>alert(‘Excel数据插入到数量库表失败!’)</script>”);
            }

        }

 

相关文章