引入NPOI
想要使用npoi,必须要对其进行引入:
- 方法一 ,使用vstudio自带的引入方式,
点击项目–>管理NuGet程序包–>搜索对应的NPOI版本进行下载,引入; - 方法二,使用代码,进入npoi官网、
https://www.nuget.org/packages/NPOI
点击红圈的地方进行复制,
打开VStudio–>工具–>NuGet管理程序包–>程序包管理控制台–>将刚复制的内容粘贴到PM>下
PM> Install-Package NPOI -Version 2.5.6 执行后便会引入NPOI
如果无法加载,点击工程项目 右键进入将其引入
导入
1、创建workbook
IWorkbook wb = new HSSFWorkbook();
2、创建sheet(至少存在一个sheet)
ISheet sheet1 = wb.CreateSheet("Sheet1");
3、创建行及单元格
IRow row = sheet1.CreateRow(0);
ICell cell1 = row.CreateCell(0);
4、可以进行一些单元格样式或字体的设置
ICellStyle style = wb.CreateCellStyle();
style.SetFont(font);
//font.FontName = "华文行楷";
font.FontName = "宋体";
font.Color = HSSFColor.Blue.Index;
font.FontHeightInPoints = 22;
style.Alignment = HorizontalAlignment.Center;//居中
5、完整代码
(此处为简易的循环输入数字,可以根据自己的业务进行输入)
public static void WriteSheet() {
IWorkbook wb = new HSSFWorkbook();
IFont font = wb.CreateFont();
ICellStyle style = wb.CreateCellStyle();
style.SetFont(font);
//font.FontName = "华文行楷";
font.FontName = "宋体";
font.Color = HSSFColor.Blue.Index;
font.FontHeightInPoints = 22;
style.Alignment = HorizontalAlignment.Center;//居中
ISheet sheet1 = wb.CreateSheet("Sheet1");
IRow row = sheet1.CreateRow(0);
ICell cell1 = row.CreateCell(0);
cell1.SetCellValue("姓名");
row.CreateCell(1).SetCellValue("B");
row.CreateCell(2).SetCellValue("C");
cell1.CellStyle = style;
// sheet1.SetColumnWidth(0,100*256);//列宽(索引,宽度(单位为1/256个字符宽度))
// row.Height = 200 * 20;
//sheet1.DefaultColumnWidth =100 * 256;
// sheet1.DefaultRowHeightInPoints = 30 * 20;
int x = 1;
for (int i = 1; i <= 3; i++)
{
row = sheet1.CreateRow(i);
for (int j = 0; j < 3; j++)
{
cell1 = row.CreateCell(j);
cell1.SetCellValue(x++);
}
}
FileStream sw = File.Create("../../../test.xlsx");
wb.Write(sw);
sw.Close();
}
导出
1、写出完整路径
string path = "../../../aaa.xlsx";
2、创建workbook
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
var workBook = WorkbookFactory.Create(fs);
注意:文件类型的判断
如果为“.xlsx”便创建HSSFWorkBook
如果为“.xls”便创建XSSFWorkBook
3、创建sheet
ISheet sheet = workBook.GetSheetAt(0);
4、创建单元格并获取其中的值
Formula类型
注意,其中的值类型要进行区分判断
尤其Formula类型的vlookup函数
(种类一:引用本工作簿的不同sheet内容)
(种类二:引用外部工作簿的sheet内容)
(种类三:引用不存在的工作簿的sheet内容)
(种类四:引用错误函数的内容)
可以通过以下方法显示
IFormulaEvaluator evaMain = new XSSFFormulaEvaluator(workBook);
evaMain.IgnoreMissingWorkbooks=true;
cell = evaMain.EvaluateInCell(cell);
if (cell.CellType == CellType.Numeric)
{
cellName = cell.NumericCellValue.ToString();
}
if (cell.CellType == CellType.String)
{
cellName = cell.StringCellValue.ToString();
}
5、完整代码
public static void ReadExcel() {
string path = "../../../aaa.xlsx";
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
var workBook = WorkbookFactory.Create(fs);
ISheet sheet = workBook.GetSheetAt(0);
string cellName ;
foreach (IRow row in sheet)
{
for (var i = 0; i < row.LastCellNum; i++)
{
var cell = row.GetCell(i);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.String:
cellName = cell.StringCellValue;
break;
case CellType.Numeric:
cellName = cell.NumericCellValue.ToString();
break;
case CellType.Formula:
if (workBook != null) {
IFormulaEvaluator evaMain = new XSSFFormulaEvaluator(workBook);
evaMain.IgnoreMissingWorkbooks=true;
cell = evaMain.EvaluateInCell(cell);
if (cell.CellType == CellType.Numeric)
{
cellName = cell.NumericCellValue.ToString();
}
if (cell.CellType == CellType.String)
{
cellName = cell.StringCellValue.ToString();
// cellName = cell.RichStringCellValue.ToString();
}
}
break;
case CellType.Boolean:
cellName = cell.BooleanCellValue.ToString();
break;
case CellType.Error:
cellName = cell.ErrorCellValue.ToString();
break;
case CellType.Blank:
cellName = string.Empty;
break;
default:
cellName = cell.ToString();
break;
}
Console.WriteLine(cell.ToString());
Console.Write("\n");
}
}
Console.WriteLine();
}
}