npoi 执行公式_生成excel文件时NPOI无法计算公式

NPOI库

. 已创建Excel文件,但未计算公式。这是我第一次使用NPOI库。

经过大量的谷歌搜索,我发现了一些代码片段,我用在我的代码计算公式,但仍然没有运气。

此例程使用随机数值和动态组合的公式填充数据表。我的数据表屏幕截图附在这里

data table screen shot

public DataTable GetDataTable()

{

string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";

int startsum = 0;

int currow = 0;

bool firstTimeSum = true;

int NumRows = 6;

int NumColumns = 5;

DataTable dt = new DataTable();

for (int col = 0; col < NumColumns; col++)

{

strColName = GenerateColumnText(col);

DataColumn datacol = new DataColumn(strColName, typeof(object));

dt.Columns.Add(datacol);

}

for (int row = 0; row < NumRows; row++)

{

dt.Rows.Add();

for (int col = 0; col < NumColumns; col++)

{

if (row < 2)

{

dt.Rows[row][col] = Convert.ToInt32(new Random().Next(1, NumRows));

}

else

{

if (firstTimeSum)

{

if (row - currow == 2)

{

currow = row;

startsum = 0;

firstTimeSum = false;

}

else

{

startsum = 1;

}

}

else

{

if (row - currow == 3)

{

currow = row;

startsum = 0;

}

}

if (startsum == 0)

{

strColName = GenerateColumnText(col);

strImmediateOneUp = strColName + ((row + 1) - 1).ToString();

strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();

strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);

dt.Rows[row][col] = strSum;

}

else

{

dt.Rows[row][col] = Convert.ToInt32(new Random().Next(1, NumRows));

}

}

}

startsum = 1;

}

return dt;

}

public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)

{

int i = 0;

int j = 0;

int count = 0;

ISheet sheet = null;

IWorkbook workbook=null;

double d;

string fileName = @"d:\SpreadsheetLight_npoi.xlsx";

FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);

if (fileName.IndexOf(".xlsx") > 0) // 2007

workbook = new XSSFWorkbook();

else if (fileName.IndexOf(".xls") > 0) // 2003

workbook = new HSSFWorkbook();

try

{

if (workbook != null)

{

sheet = workbook.CreateSheet(sheetName);

}

else

{

return -1;

}

if (isColumnWritten == true)

{

IRow row = sheet.CreateRow(0);

for (j = 0; j < data.Columns.Count; ++j)

{

row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);

}

count = 1;

}

else

{

count = 0;

}

for (i = 0; i < data.Rows.Count; ++i)

{

IRow row = sheet.CreateRow(count);

for (j = 0; j < data.Columns.Count; ++j)

{

if (Double.TryParse(data.Rows[i][j].ToString(), out d))

{

row.CreateCell(j).SetCellValue(d);

//row.CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

}

else

{

row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString().Replace("=",string.Empty));

//row.CreateCell(j).SetCellFormula(data.Rows[i][j].ToString().Replace("=",string.Empty));

}

}

++count;

}

if (workbook is XSSFWorkbook)

{

XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);

}

else

{

HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);

}

workbook.Write(fs);

return count;

}

catch (Exception ex)

{

Console.WriteLine("Exception: " + ex.Message);

return -1;

}

}

我用这种方法计算我的公式,但仍然不起作用。

if (workbook is XSSFWorkbook)

{

XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);

}

else

{

HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);

}

请告诉我在我的代码中添加或更改什么,结果公式应该被计算,当我打开excel文件时将显示正确的值。谢谢