I am trying to Read/Write an Excel spreadsheet using NPOI. I have an existing Template Excel spreadsheet that I am trying to open, read, update certain cells. I am able to go through all of the code to update 2 sheets in the spreadsheet, but when I go to save the workbook, I get the error "Cannot get a text value from a numeric cell". Everything I have seen is trying to read a spreadsheet but nothing about reading and writing back to that spreadsheet. Unfortunately NPOI doesn't tell you which record is the issue, so troubleshooting is a pain. If anyone knows about this issue with reading and writing to an Excel spreadsheet
I have the following code (Condensed for this site). When the code gets to the workbook.Write() statement, it throws the error.
//Open the workbook and associate with an Excel file
StartWorkbook(fileName);
//This statement happens several times to update multiple columns/rows in the spreadsheet using either the Currency, Number, Decimal cell types
SetCellValue(sheet, rowNumber, column, , cellStyleCurrency);
//Save and close the workbook.
SaveWorkBook(fileName);
private void StartWorkbook(string fileName)
{
using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fs);
fs.Close();
}
font = workbook.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "Calibri";
cellStyleDecimal = workbook.CreateCellStyle();
cellStyleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("##,##0.0");
cellStyleDecimal.SetFont(font);
cellStyleDecimal.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleDecimal.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleDecimal.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleDecimal.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleNumber = workbook.CreateCellStyle();
cellStyleNumber.DataFormat = workbook.CreateDataFormat().GetFormat("##,##0");
cellStyleNumber.SetFont(font);
cellStyleNumber.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleNumber.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleNumber.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleNumber.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleCurrency = workbook.CreateCellStyle();
cellStyleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("$##,##0.##");
cellStyleCurrency.SetFont(font);
cellStyleCurrency.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleCurrency.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleCurrency.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleCurrency.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylePercent = workbook.CreateCellStyle();
cellStylePercent.DataFormat = workbook.CreateDataFormat().GetFormat("##0.00%");
cellStylePercent.SetFont(font);
cellStylePercent.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylePercent.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylePercent.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStylePercent.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleBorder = workbook.CreateCellStyle();
cellStyleBorder.SetFont(font);
cellStyleBorder.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleBorder.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleBorder.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleBorder.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
}
private void SaveWorkBook(string fileName)
{
File.Delete(fileName);
using (FileStream fsWrite = new FileStream(fileName, FileMode.CreateNew, FileAccess.Write))
{
workbook.Write(fsWrite);
fsWrite.Close();
}
}
private void SetCellValue(ISheet worksheet, int rowPosition, int columnPosition, String value, ICellStyle cellStyle = null)
{
IRow dataRow = worksheet.GetRow(rowPosition) ?? worksheet.CreateRow(rowPosition);
ICell cell = dataRow.GetCell(columnPosition) ?? dataRow.CreateCell(columnPosition);
double dblValue = 0;
if (cellStyle != null)
{
cell.CellStyle = cellStyle;
dblValue = double.Parse(value);
cell.SetCellValue(dblValue);
}
else
{
cell.SetCellValue(value);
}
}