c#NPOI使用简1

在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



 

posted @ 2020-07-24 15:08  程序原快递  阅读(2167)  评论(0编辑  收藏  举报