C# .Net :Excel NPOI导入导出操作教程之将Excel文件读

作者: www.9159.com  发布:2019-11-05

 using (FileStream fileReader = File.OpenRead(@"C:UsersAdministratorDesktop112.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集合的数据写到一个Excel文件并导出示例:

        }

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;

 

 

————————————参数传递不经过List<>集合————————————

  List<UserInfo> listUser = new List<UserInfo>()
           {
               new UserInfo { name="1", id="1", phone="1r" },
               new UserInfo { name="2", id="2", phone="2r" },
               new UserInfo { name="3", id="3", phone="3r" },
               new UserInfo { name="4", id="4", phone="4r" },
               new UserInfo { name="5", id="5", phone="5r" },
           };
        1、//创建工作簿对象
       IWorkbook workbook = new HSSFWorkbook();
        2、//创建工作表
        ISheet sheet = workbook.CreateSheet("onesheet");
        IRow row0 = sheet.CreateRow(0);
        row0.CreateCell(0).SetCellValue("用户Id");
        row0.CreateCell(1).SetCellValue("用户名称");
        row0.CreateCell(2).SetCellValue("用户备注信息");
        for (int r = 1; r < listUser.Count; r++)
        {
            3、//创建行row
            IRow row = sheet.CreateRow(r);
            row.CreateCell(0).SetCellValue(listUser[r].id);
            row.CreateCell(1).SetCellValue(listUser[r].name);
            row.CreateCell(2).SetCellValue(listUser[r].phone);
            row.CreateCell(3).SetCellValue(listUser[r].pwd);
        }

using (FileStream fileReader = File.OpenRead(@"C:UsersAdministratorDesktop112.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>");
            }

    //创建流对象并设置存储Excel文件的路径
        using (FileStream url = File.OpenWrite(@"C:UsersAdministratorDesktop写入excel.xls"))
        {

        }

    //导出Excel文件
            workbook.Write(url);
            Response.Write("<script>alert('写入成功!')</script>");
        };

 

——————————分享End——————————

本文由9159.com发布于www.9159.com,转载请注明出处:C# .Net :Excel NPOI导入导出操作教程之将Excel文件读

关键词:

上一篇:本周记录,time 为今天内
下一篇:没有了