Cannot get a text value from a numeric cell NPOI

54 Views Asked by At

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);
        }
    }
0

There are 0 best solutions below