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>"); } }