cell formatting not applied after formula

Question

I'm using NPOI. I read in an Excel workbook with two sheets that acts as a "template" for the result workbook I'll be generating. Sheet 0 is empty aside from some header rows and sheet 1 has a number of rows of formulas. The formulas generally refer to the sheet 0 and pull data from it.

I can't write directly into the template workbook because I'd be having to move rows out of the way and it would get ugly fast, so in code I create a new result workbook with two sheets. Based on a preset configuration file, I populate the sheet 0 of this result workbook with data, row by row, cloning the style and formulas of certain rows from the template workbook. Sheet 0 ends up filled with data, and sheet 1 is basically a row of formulas copied and adjusted relatively, typically pulling data from sheet 0 with a small calculation here or there. All of my data in sheet 0 is text and cannot be interpreted as numeric, dates, etc. So I set my data cell types as String.

In code I then loop through all cells in sheet 1 and EvaluateInCell. I do this to obviously evaluate the formulas, but also to remove the formulas and leave the copied/calculated results. This is just a requirement of the work I'm doing. We deliver the end results and no formulas. I save the resulting workbook.

In general things look good, except where I have cell formatting, the formatting seems to not be applied. The formatting IS there when I get the cell formatting properties in Excel. For example I might have text data that is a date-time in my data that shows up as: 7/7/2016 9:54:55 AM this IS what the original data text is, but on my formula sheet I have a custom cell format yyyymmdd. And yet, the cell still shows: 7/7/2016 9:54:55 AM In Excel I then do something like manually edit the value, for example delete the last 'M' and retype 'M' and hit enter, that cell changes to the desired format and shows: 20160707 So again, the formatting IS there, it's just not applied.

I don't want to have to manually edit cells, or do anything once the workbook is opened in Excel by my customers. I want the resulting workbook to open up with the values formatted. I've tried a few things that seemed like a shot in the dark, like:

this.ResultWorkbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); ((XSSFWorkbook)this.ResultWorkbook).SetForceFormulaRecalculation(true);

But this didn't help. Any thoughts?

"For example I might have text data that is a date-time ": Date-time values are not text in Excel but double values having the integer part the date and the fractional part the time (1/24 = 1h, 1/24/60 = 1min, 1/24/60/60 = 1s, ...). And Excel will not format text as date. So you need input the date-time values using SetCellValue(DateTime value) and not as string. — Axel Richter
Unfortunately my code is not allowed to interpret the data. It's just a string to my code and is the result of separate processing and cannot be interpreted as a date-time at runtime. Sometimes the data is already formatted and sometimes it's not. When it's not, the Excel cell formatting is expected to be the solution. This worked somehow before I ripped the Excel interop code out and switched to NPOI. I get what you're saying about SetCellValue but that would require my code to know what the data is and the format it's already in. Which it doesn't. — Buzz
"Unfortunately my code is not allowed to interpret the data." Unfortunately I cannot help then. — Axel Richter

kumar chandraketu kumar chandraketu · Accepted Answer · 2017-12-13T15:31:32

can you try either of this apprioach while setting the formula for cell

Approach 1

ICellStyle dateCellStyle = workbook.CreateCellStyle();
dateCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyymmdd"); // Or prefix single quote for data when writing it to excel file like 'yyymmdd ex: '20160707

Approach 2

 XSSFCellStyle dateCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
 XSSFDataFormat dateDataFormat = (XSSFDataFormat)workbook.CreateDataFormat();
 dateCellStyle.SetDataFormat(dateDataFormat.GetFormat("yyyymmdd"));

cell formatting not applied after formula

2 Answers