c#NPOI使用简1

原创于 2023-02-01 20:59:35 发布 · 245 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#c# #asp.net

c#基础专栏收录该内容

3 篇文章

订阅专栏

这篇文章展示了如何在VisualStudio(VS)中通过NuGet安装NPOI库，然后使用NPOI读取Excel表格数据，判断表头并处理内容。同时，文章还提供了导出Excel的示例，包括设置单元格样式和内容。

在vs开发软件中通过nuget安装npoi

//读取excel表格数据，并判断表头是否符合要求 (电脑可不安装office)
        public static void ReadExcel()
        {
            string filePath = @"C:\Users\Administrator\Desktop\as.xlsx";
            FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            List<IRow> rowlist = new List<IRow>();
            //创建工作簿对象
            XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
            ISheet sheet = workbook.GetSheetAt(0);//获取第一张表
            for (int i = 0; i < sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                rowlist.Add(row);
                row = null;
            }
            IRow indexRow = rowlist[0];
            rowlist.Remove(indexRow);//移除表头，直接循环列表值
            //循环表体每一行数据
            for (int i = 0; i < rowlist.Count; i++)
            {
                //循环每一行中每个单元格数据
                for (int j = 0; j < rowlist[i].Cells.Count; j++)
                {
                    //判断当前循环单元格数据是否符合要求
                    if (indexRow.GetCell(j).ToString().Contains("oid"))
                    {
                        Console.WriteLine(rowlist[i].GetCell(j).ToString());
                    }
                    else if (indexRow.GetCell(j).ToString().Contains("卡卡西"))
                    {
                        Console.WriteLine(rowlist[i].GetCell(j).ToString());
                    }
                }
            }
        }

 public static void ExportExcel()
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheetx = workbook.CreateSheet("邮件信息");
            //第一个是列索引：1表示第二列单元格，第二个参数是表示单元格字符数 30 * 256 表示该单元格可存储30个字符
            sheetx.SetColumnWidth(1, 15 * 256);
            IRow row1 = sheetx.CreateRow(0);
            row1.CreateCell(0).SetCellValue("用户邮件信息");
            //row1.Height = 50 * 10;
            IRow rowx0 = sheetx.CreateRow(1);
            List<EmailModel> list = EmailDal.GetModelList("","emailId,UserId");
            rowx0.CreateCell(0).SetCellValue("邮件ID");
            rowx0.CreateCell(1).SetCellValue("用户ID");
            rowx0.Height = 30 * 10;
            XSSFCellStyle fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)workbook.CreateFont();
            //给字体设置颜色
            ffont.Color = HSSFColor.Red.Index;
            //给样式添加字体
            fCellStyle.SetFont(ffont);
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowdata = sheetx.CreateRow(i + 2);
                rowdata.CreateCell(0).SetCellValue(list[i].emailId);
                rowdata.Height = 30 * 10;
                rowdata.GetCell(0).CellStyle = fCellStyle;
                rowdata.CreateCell(1).SetCellValue(list[i].UserId.ToString());
            }
            var filePath = "";
            var fileName = Guid.NewGuid() + "-用户邮件表";            //如果是web，则注释该行代码
            filePath = @"C:\Users\Administrator\Desktop\" + fileName + ".xlsx";
            using (FileStream url = File.OpenWrite(filePath))
            {
                workbook.Write(url);
            }; 
 MemoryStream ms = new MemoryStream();            workbook.Write(ms);            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", fileName));            Response.BinaryWrite(ms.ToArray());            Response.End();            workbook = null;            ms.Close();            ms.Dispose();

        }


 
通过读取excel表格来判断表头数据NPOI安装dll文件下载：
链接：https://pan.baidu.com/s/1QiRVGsjr5qoLkgv64VfOKQ 提取码：oguw