1

I have a requirement to read the contents of an excel sheet, write to it and allow for editing and re-importing. The problem I am having is that when user enters a number into the cells in the sheet, the numbers are, on the back, considered indexes on the SharedString table, an effect I do not need. What I would like to do is force the cells in the worksheet to be formatted as text before going ahead to read it. I believe that way, I am sure that my numbers will be treated as Strings. How do I accomplish this please?

I would appreciate examples using the OPENXML library to solve this problem please.

Matt Ellen
  • 11,268
  • 4
  • 68
  • 90
Kobojunkie
  • 6,375
  • 31
  • 109
  • 164

2 Answers2

2

The only way to be certain that what you retrieve from Excel will be a string is to call Range.Text (which will return the displayed text based on the current format), which can only be retrieved with a single cells.

However if you want to make the format Text, then just call:

Cells.NumberFormat = "@"

As further details to what I was saying above... Changing the format does not alter the .Value of the cell. Values are generally either Strings, Dates, or Doubles depending on their number format and the contents.

EDIT
Due to your last comment, I did some additional research for you. However, this is not tested by me. If this does not help you, I recommend updating your question to show what you've tried to help garner more response from the community.

You should be able to utilize the steps listed in this answer(make sure to upvote if it helps you).

The only variation, you should be able to skip creating a NumberingFormat. When you get to the code: cellFormat.NumberFormatId = nf2decimal.NumberFormatId; instead of the nf2decimal... just use 49. so... cellFormat.NumberFormatId = 49; 49 should correspond to the predefined numeberFormat of @ according to this source

Community
  • 1
  • 1
Daniel
  • 12,982
  • 3
  • 36
  • 60
  • Do I call this on the worksheet, the row, or each cell I extract from the row? – Kobojunkie Oct 19 '12 at 20:12
  • What's this? `Cells.Numberformat` applies to a range or Worksheet. If called on a worksheet like `Worksheet.Cells.Numberformat`, it will affect all cells on the sheet. – Daniel Oct 19 '12 at 20:19
  • What library are you using there? I am still yet to find the numberformat property on my cells, and worksheets. – Kobojunkie Oct 19 '12 at 20:32
  • Oops, I didn't realize you couldn't access Excel directly. This question may help you: [Applying % number format to a cell value using open xml](http://stackoverflow.com/q/7872116/1316573) – Daniel Oct 19 '12 at 21:33
  • It seems that form of formatting does not work to solve the problem at all. Some of the values still go to the SharedStringTable which is OK only problem is when I enter number values in my table, they are interepreted as indexes on the ShareString table and that results in bad data being displayed. – Kobojunkie Oct 20 '12 at 12:09
  • Anyone have any suggestions that take advantage of OpenXML? – Kobojunkie Nov 07 '12 at 22:28
1

Like this in VBA:

Cells.Select
Selection.NumberFormat = "@"
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • I am working in OPENXML and so, having a hard time finding the above in that library. If in OPENXML, what namespace do I look for this please? – Kobojunkie Oct 19 '12 at 20:54