在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