1

I'm using Softartisans OfficeWriter to generate Excel files. The problem I have is that whenver I assign an empty string ("" or String.Empty) to a cell, the cell's value type will turn out to be Blank instead of Text, which is what I want. I've tried someCell.ValueType, but it's a read-only property and I cannot set it to Text manually.

Any help will be much appreciated!

Saghir A. Khatri
  • 3,429
  • 6
  • 45
  • 76
Isaac_Zhu
  • 105
  • 10
  • Are you limited to this office writer? Closedxml does all of this for excel easily. – Jesse Jan 30 '14 at 05:30
  • @Saran Yes... I have to use Office Writer. – Isaac_Zhu Jan 30 '14 at 06:13
  • What do you need to do with the value type afterwards? – Aviva M. Jan 30 '14 at 14:47
  • They type of the cell changes based on the value of the cell. So in order to change the cell.ValueType you have to set the value of the cell. If you set the value to a string the cell will report the value type as text. However of you set the string to "", String.Empty, or null this is the equivalent of clearing a cell in ExcelWriter. The question I have for you is why do you need to set the ValueType directly? When you set the Cell.Value the type will automatically be detected. – Sam Plus Plus Jan 31 '14 at 05:32

3 Answers3

3

Note: I work for SoftArtisans

What file format are you using? In the BIFF file format (.xls), cells assigned to an empty string will be set to a null value when the file is saved, thus causing the ValueType to return blank.

In OOXML (.xlsx) the empty string value is preserved when the file is saved, and the ValueType will return Text for any cells assigned to an empty string. You can find more information in this KB article: http://blog.softartisans.com/2013/12/30/kb-why-do-some-blank-cells-return-different-values-in-ooxml/

Would using .xlsx files be possible for you?

Gabe Goodwin
  • 46
  • 1
0

A possible workaround would be to set the cell value to '.

That is a marker for Excel that the cell contains a text value.

Onots
  • 2,118
  • 21
  • 28
0

Have you tried adding escaped empty quotes to the string?

someCell.Value = "\"\"";
Seth Moore
  • 3,575
  • 2
  • 23
  • 33