npoi 执行公式_生成excel文件时NPOI无法计算公式
NPOI庫(kù)
. 已創(chuàng)建Excel文件,但未計(jì)算公式。這是我第一次使用NPOI庫(kù)。
經(jīng)過(guò)大量的谷歌搜索,我發(fā)現(xiàn)了一些代碼片段,我用在我的代碼計(jì)算公式,但仍然沒(méi)有運(yùn)氣。
此例程使用隨機(jī)數(shù)值和動(dòng)態(tài)組合的公式填充數(shù)據(jù)表。我的數(shù)據(jù)表屏幕截圖附在這里
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;
}
}
我用這種方法計(jì)算我的公式,但仍然不起作用。
if (workbook is XSSFWorkbook)
{
XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
}
else
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
}
請(qǐng)告訴我在我的代碼中添加或更改什么,結(jié)果公式應(yīng)該被計(jì)算,當(dāng)我打開(kāi)excel文件時(shí)將顯示正確的值。謝謝
總結(jié)
以上是生活随笔為你收集整理的npoi 执行公式_生成excel文件时NPOI无法计算公式的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： node mysql 事件循环_Node
- 下一篇： 用jsp_servlet实现在mysql