C# ：Excel导入导出

最新推荐文章于 2024-08-26 15:35:39 发布

帽子—

最新推荐文章于 2024-08-26 15:35:39 发布

阅读量338

点赞数 1

分类专栏： c# 文章标签： c#

本文链接：https://blog.csdn.net/chuan_ling/article/details/117767747

版权

c# 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

引入NuGet包 NPOI 进行操作

//导入Excel
        public int ImportExcel()
        {
            var i = 0;
            // 1.从Excel文件中读取数据
            #region
            var path = "E:/Pro.xlsx";  
            IWorkbook workbook = null;
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                workbook = WorkbookFactory.Create(file);
                //使用接口，自动识别excel2003/2007格式主要就是这句话
            }
            var sheet = workbook.GetSheet("sheet1"); // 读取Excel中的sheet1表
            var rowNum = sheet.LastRowNum;//总行数

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();  // 取所有行集合

            List<Product> list = new List<Product>();  // 容器
            int indexNum = 0;
            while (rows.MoveNext())
            {
                if (indexNum == 0)
                {
                    indexNum += 1;
                    continue;
                }
                IRow row = rows.Current as XSSFRow;
                if (row == null)
                {
                    row = rows.Current as HSSFRow;
                }
                Product s = new Product();

                s.PName = row.GetCell(0).ToString();
                s.Price = row.GetCell(1).ToString();
                s.PCount = Convert.ToInt32(row.GetCell(2).ToString());

                db.Product.Add(s);
                i = db.SaveChanges();
            }
            workbook.Close();
            #endregion
            // 2.添加到表中

            return i;
        }

Excel导入的代码，进行本地指定路径导入，假如要选择文件上传导入，自行插入代码，上传文件，跟路径接一下就行了。

//Excel导出
        public void OutPut()
        {
            //1 获取产品信息的查询结果【集合】
            var plist = db.Product.ToList();

            //2 创建excel对象
            #region
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("some comments");

            var row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("产品编号");
            row0.CreateCell(1).SetCellValue("产品名称");
            row0.CreateCell(2).SetCellValue("产品单价");
            row0.CreateCell(3).SetCellValue("产品数量");

            int rowNum = 1;
            foreach (var item in plist)
            {
                var new_row = sheet.CreateRow(rowNum);
                new_row.CreateCell(0).SetCellValue(item.PID);
                new_row.CreateCell(1).SetCellValue(item.PName);
                new_row.CreateCell(2).SetCellValue(item.Price);
                new_row.CreateCell(3).SetCellValue(item.PCount);
                rowNum++;
            }

            #endregion

            //3 把excle对象反馈给请求者
            #region
            string filename = "产品信息表.xlsx";

            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
            //http://social.msdn.microsoft.com/Forums/en-US/3a7bdd79-f926-4a5e-bcb0-ef81b6c09dcf/responseoutputstreamwrite-writes-all-but-insetrs-a-char-every-64k?forum=ncl
            workbook.Write(Response.OutputStream); // cannot be used 
            Response.Flush();
            Response.End();
            #endregion
        }

Excel导出，请自行参考