This project has moved. For the latest updates, please go here.

tick mark for string cells

Aug 31, 2011 at 9:18 PM

I am trying to add string data to a cell. (Example:  012)

When it opens up in Excel, there is a green triangle in the upper left corner with the warning 'The number in this cell is formatted as text or preceded by an apostrophe'.

I cannot change the excel options on the users machine.

  • In VBA, you can do something like this to ignore the error:
    <tableObject>.Range("Q1:Q1000").Errors.Item(<errorNo>).Ignore = true
  • In Spreadsheeet XML you can do something like this to ignore the error:
    <Cell><Data ss:type="String" x:ticked="1">012</Data></Cell>

Is there something equivalent to this in EPPlus?

 

 

Coordinator
Sep 1, 2011 at 6:28 PM

Hi,

No there is no support for suppressing errors on individual cells in the current version, but its a good point, so I'lll add it to my TODO list.

Jan

Jul 31, 2012 at 6:38 PM

Any update on this issue? As I am also looking for a way to ignore these errors.

 

Thanks,

Rob

Editor
Jul 31, 2012 at 7:43 PM
Edited Jul 31, 2012 at 7:48 PM

I´m pretty sure that there is no news about this feature. But in our lastest version you can embed VBA code, maybe you can take a look at vba approach said in first post.

Nov 28, 2012 at 6:39 PM

The following code will remove the triangles for cell A1.  I could not get it to work for a range of cells.  I can then loop through all cells (ie rows and columns) to set it.

Workbook.CreateVBAProject();
var sb = new StringBuilder();
sb.AppendLine("Public Sub IgnoreErrorNumberAsText()");
sb.AppendLine(SheetName.Range(\"A1\").Errors.Item(xlNumberAsText).Ignore = True");
sb.AppendLine("End Sub");
var module = Workbook.VbaProject.Modules.AddModule("AnyModuleName");
module.Code = sb.ToString();
Workbook.CodeModule.code = "Private Sub Workbook_Open()\r\nCall IgnoreErrorNumberAstext()\r\nEndSub";

Nov 28, 2012 at 6:58 PM

The attached code only works in version 3.1 and the user must enable macros.

Jan 30, 2013 at 3:36 PM

An alternative, which does require getting the source and modifying it slightly

http://stackoverflow.com/questions/11858109/using-epplus-excel-how-to-ignore-excel-error-checking-or-remove-green-tag-on-t