C#--NPOI操作Excel总结
详细教程：
NPOI使用手册——c# - vv彭 - 博客园 (cnblogs.com)
1，NPOI读取Excel高低版本兼容性问题
报错1：NPOI.POIFS.FileSystem.OfficeXmlFileException HResult=0x80070057 Message=The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process
报错2：NPOI.POIFS.FileSystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
原因：这是因为NPOI读取2003时使用的是HSSFWorkbook，更高版本读取需要使用XSSFWorkbook
解决方式：
将代码中HSSFWorkbook修改为XSSFWorkbook，最好是导入文件时选择版本，这样处理更好
IWorkbook workbook = new XSSFWorkbook(file);
2，NPOI读取Excel带计算公式的值
/// <summary>
/// 根据填充后的综合判定表来综合判定是否OK（获取带公式列的值）
/// </summary>
/// <param name="filePath">填充后的综合判定表打印版的文件路径</param>
public static void Step4(FilePath filePath)
{
XSSFWorkbook workbook;
XSSFFormulaEvaluator evalor = null;
using (FileStream fs = File.Open(filePath.PrintMatchFilePath, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls文件读入workbook变量里，之后就可以关闭了
workbook = new XSSFWorkbook(fs);
//【带公式取值步骤1】创建当前sheet 的动态公式计算器
evalor = new XSSFFormulaEvaluator(workbook);
fs.Close();
}
ISheet sheet = workbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum;
int StandardTotalCount = 0;
int OKTotalCount = 0;
ICell cellObj = sheet.GetRow(21).GetCell(3);
var cellType = cellObj.CellType;
switch (cellType)
{
case CellType.Formula:
//【带公式取值步骤2】针对公式列 进行动态计算;注意：公式暂时只支持 数值 字符串类型
var formulaValue = evalor.Evaluate(cellObj);
if (formulaValue.CellType == CellType.Numeric)
{
double b = formulaValue.NumberValue;
StandardTotalCount = Convert.ToInt32(b);
}
else if (formulaValue.CellType == CellType.String)
{
string s = formulaValue.StringValue;
}
break;
default:
break;
}
ICell cellObj2 = sheet.GetRow(21).GetCell(4);
var cellType2 = cellObj2.CellType;
switch (cellType2)
{
case CellType.Formula:
//针对公式列 进行动态计算;注意：公式暂时只支持 数值 字符串类型
var formulaValue = evalor.Evaluate(cellObj2);
if (formulaValue.CellType == CellType.Numeric)
{
double b = formulaValue.NumberValue;
OKTotalCount = Convert.ToInt32(b);
}
else if (formulaValue.CellType == CellType.String)
{
string s = formulaValue.StringValue;
}
break;
default:
break;
}
if (StandardTotalCount == OKTotalCount)
{
sheet.GetRow(21).GetCell(6).SetCellValue("OK");
}
else
{
sheet.GetRow(21).GetCell(6).SetCellValue("NG");
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件 这种方式能保存.xls和.xlsx文件
using (FileStream fs = new FileStream(filePath.PrintMatchFilePath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
3，元格带有公式的值不能自动更新
ISheet sheet = workbook.GetSheetAt(0);
//解决单元格带有公式的值不能自动更新
//解决办法：在程序最后添加一句（关闭文件前），使得sheetdata自动重算【我测试过不用放在关闭文件前也可以的】
sheet.ForceFormulaRecalculation = true;
4，设置打印页面缩放和自适应列宽
/// <summary>
/// 自适应列宽和打印页缩放
/// </summary>
/// <param name="filePath">excel文件路径</param>
/// <param name="scale">缩放比例(77,80,100等）</param>
public static void AutoColumnWidth(string filePath,short scale=80)
{
//【1】打开excel文件的第几张表，第几行的数据，返回一个字典{列名：列的索引}
//字典的作用：可以根据列名快速找到对应的列索引
Dictionary<string, int> dicData = ExcelHelper.GetDataDictionary(filePath, 1, 1);
bool isCompatible = ExcelHelper.GetIsCompatible(filePath);
IWorkbook workbook = null;
using (FileStream fs = File.Open(filePath, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls文件读入workbook变量里，之后就可以关闭了
workbook = ExcelHelper.CreateWorkbook(isCompatible, fs);
fs.Close();
}
ISheet sheet = workbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum;
for (int col = 0; col < sheet.GetRow(0).LastCellNum; col++)
{
//自适应列宽
sheet.AutoSizeColumn(col);
}
//sheet.PrintSetup.FitWidth = 1;
//sheet.PrintSetup.FitHeight = 0;
//设置打印页面缩放比例
sheet.PrintSetup.Scale = scale;
#region 非常的耗时，不推荐使用
////开始遍历【遍历行操作】
//for (int r = 1; r <= rowCount; r++) //从第二行开始遍历
//{
// IRow currentRow = sheet.GetRow(r); //读取当前行数据
// if (currentRow == null) //如果为空，重新创建一行，防止null报错
// {
// sheet.CreateRow(r);
// currentRow = sheet.GetRow(r);
// }
// for (int i = 0; i < currentRow.LastCellNum; i++)
// {
// //sheet.AutoSizeColumn(i);//非常的耗时，不推荐用
// }
//}
#endregion
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件 这种方式能保存.xls和.xlsx文件
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
行或单元格为null报错问题：
private void Step6()
{
//【1】打开excel文件的第几张表，第几行的数据，返回一个字典{列名：列的索引}
//字典的作用：可以根据列名快速找到对应的列索引
Dictionary<string, int> dicData = ExcelHelper.GetDataDictionary(filePath.CheckFilePath, 1, 2);
bool isCompatible = ExcelHelper.GetIsCompatible(filePath.CheckFilePath);
IWorkbook workbook = null;
using (FileStream fs = File.Open(filePath.CheckFilePath, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls文件读入workbook变量里，之后就可以关闭了
workbook = ExcelHelper.CreateWorkbook(isCompatible, fs);
fs.Close();
}
ICellStyle sOK = workbook.CreateCellStyle(); //设置单元格的背景颜色需要用到的代码
sOK.FillForegroundColor = HSSFColor.SeaGreen.Index; //设置单元格的背景颜色需要用到的代码
sOK.FillPattern = FillPattern.SolidForeground; //设置单元格的背景颜色需要用到的代码
ICellStyle sNOK = workbook.CreateCellStyle(); //设置单元格的背景颜色需要用到的代码
sNOK.FillForegroundColor = HSSFColor.Red.Index; //设置单元格的背景颜色需要用到的代码
sNOK.FillPattern = FillPattern.SolidForeground; //设置单元格的背景颜色需要用到的代码
ICellStyle sCheck = workbook.CreateCellStyle(); //设置单元格的背景颜色需要用到的代码
sCheck.FillForegroundColor = HSSFColor.Yellow.Index; //设置单元格的背景颜色需要用到的代码
sCheck.FillPattern = FillPattern.SolidForeground; //设置单元格的背景颜色需要用到的代码
ISheet sheet = workbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum;
//开始遍历【遍历行操作】
for (int r = 1; r <= rowCount; r++) //从第二行开始遍历
{
IRow currentRow = sheet.GetRow(r); //读取当前行数据
if (currentRow == null) //如果为空，重新创建一行，防止null报错
{
sheet.CreateRow(r);
currentRow = sheet.GetRow(r);
}
//物料编码
if (currentRow.GetCell(dicData["物料编码"]) == null)
{
currentRow.CreateCell(dicData["物料编码"]);
};
string materialNumber = currentRow.GetCell(dicData["物料编码"]).ToString();
//订单编号
string orderNumber = currentRow.GetCell(dicData["订单编号"]).ToString();
//行号
string lineNumber = currentRow.GetCell(dicData["行号"]).ToString();
//未到货数量
string orderQuantity = currentRow.GetCell(dicData["未到货数量"]).ToString();
int iOrderQuantity;
iOrderQuantity=int.TryParse(orderQuantity, out iOrderQuantity)? iOrderQuantity : -1;
#region 避开第二步的标识
if (currentRow.GetCell(dicData["供应商是否更改供应商承诺交期(Y/N)"]) == null)
{
currentRow.CreateCell(dicData["供应商是否更改供应商承诺交期(Y/N)"]);
};
if (currentRow.GetCell(dicData["供应商是否更改供应商承诺交期(Y/N)"]).ToString() == "N") continue;
#endregion
//如果 现存量 中有 这个型号 .
if (dicLatestPrice.ContainsKey(materialNumber))
{
//获取客户的采购数量
string purchaseQuantity = currentRow.GetCell(dicData["采购订单总数量"]).ToString(); ;
int iPurchaseQuantity;
int.TryParse(purchaseQuantity, out iPurchaseQuantity);
//获取 可用量
int iAvailableQuantity = dicLatestPrice[materialNumber].AvailableQuantity;
// 获取 现存量
int iOnHandQuantity = dicLatestPrice[materialNumber].OnHandQuantity;
//如果品牌是欧姆龙的
if (dicLatestPrice[materialNumber].Brand == "欧姆龙")
{
//且该物料编码的 采购入库待入量 大于等于 未到货数量
if (dicLatestPrice[materialNumber].PurchaseQuantity > iOrderQuantity)
{
currentRow.GetCell(dicData["供应商是否更改供应商承诺交期(Y/N)"]).SetCellValue("N");
currentRow.GetCell(dicData["供应商是否更改供应商承诺交期(Y/N)"]).CellStyle = sOK;
if (currentRow.GetCell(dicData["目前物料的状态"]) == null)
currentRow.CreateCell(dicData["目前物料的状态"]);
currentRow.GetCell(dicData["目前物料的状态"]).SetCellValue(dicLatestPrice[materialNumber].Brand + "已提货");
currentRow.GetCell(dicData["目前物料的状态"]).CellStyle = sOK;
}
}
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
workbook.Close();//关闭工作薄
var buf = stream.ToArray();
//保存为Excel文件 这种方式能保存.xls和.xlsx文件
using (FileStream fs = new FileStream(filePath.CheckFilePath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
fs.Dispose();//释放文件流
}
}
