This project has moved and is read-only. For the latest updates, please go here.

Transformation of cell value

Mar 7, 2012 at 2:46 PM

I recently converted a VB.Net application from using the Microsoft API to using Closed XML for Excel files.  The changes were implemented a couple of weeks ago.  The users promptly noted a defect with how the data was being formatted.  I went back and stepped through the code, and I've seen that, under some conditions, when setting cell.value, it is being transformed from the data specified by the String variable's value.  Here are two examples.

  • ·         Value of ‘9-16’ is getting translated to ‘9/16/2012’
  • ·         Value of ‘50,55,59,64,65,68’ is getting translated to ‘505559646568’ and displaying in scientific notation. 

I was able to correct this problem by making a small code change, but I'm not proud of the way I solved it.  Below is the before and after code examples that show how I corrected the problem.

Before:    objCell.Value = strCellValue

After:      objCell.Value = "'" & strCellValue

By putting the single quote in front of the text data, it forced the value to be saved as is, and formatted as 'General'. 

I'd love to know if there is a more elegant way of handling this situation.





Mar 7, 2012 at 5:08 PM

This on the top of my list after fixing the current bugs/issues (haven't had much time in the last few weeks). In the meantime you could use cell.SetValue("The String"), which is definitely more elegant. I will add a flag so you can tell how to treat strings, to interpret its contents or leave as string literals.

I can only think of two design decisions I deeply regret to have made at the beginning of this project. This is one of them (I was trying to ease the input from text sources but ended up making things worse, see FAQ). The other one is the use of the name "Style" instead of "Format". Other than that, I'm pretty happy with the way the API turned out.

Mar 7, 2012 at 5:20 PM

So, if I use SetValue(), will I still need to put the apostrophe in front of the string to force the text format, or will it handle it correctly?  If using SetValue fixes this problem, then there is no bug, just a poor implementation by me.  I'll definitely experiment with this and see if it helps.  An interesting side effect of what I did is that now, when you open the Excel file, many of the cells prompt the user (with the little triangle in the corner) to correct what is obviously incorrectly entered data, because Excel thinks that it should be entered as a number, or a date, etc.  I think it's likely that a lot of the trouble with this is that Microsoft has tried to get too smart in their auto formatting and auto correction of data entry.

As for wishing you had done things differently.  I want you to know that I started out trying to use Open XML directly.  For Word files, it wasn't too bad, but for Excel files, OMG!  All you had to do was get one little thing out of order and the file wouldn't even open.  Closed XML is a God-send.  Thank you very much for developing this priceless gem for creating Excel documents in code.

Mar 7, 2012 at 5:36 PM

You do not need to use apostrophes with cell.SetValue() because it leaves the strings as they are and not try to convert them to numbers/dates.

I consider it a design flaw (which I'll fix as soon as I have time).

I'm very happy you find it useful though =)