Using EPPlus Excel - How to ignore excel error checking or remove the green tag in the upper left of the cell

Question

I am using EPPlus to export an excel 2007 file. The file may export normally, but I have a problem setting the column format. My string column with digital style (order no. 49000001) will be exported with a green tag in the upper left corner of each cell. How to remove it?

I am trying to set the number format to General, but it does not work.

Please, help.

ps i am using c #

Answer 1

EPPLus does not currently support disabling this green tag. However, you can modify the project to suppress it. First you need to add a new class to the project, ExcelIgnoredError.cs:

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml; namespace OfficeOpenXml { public class ExcelIgnoredError : XmlHelper { private ExcelWorksheet _worksheet; /// <summary> /// Constructor /// </summary> internal ExcelIgnoredError(XmlNamespaceManager ns, XmlNode node, ExcelWorksheet xlWorkSheet) : base(ns, node) { _worksheet = xlWorkSheet; } public bool NumberStoredAsText { get { return GetXmlNodeBool("@numberStoredAsText"); } set { SetXmlNodeBool("@numberStoredAsText", value); } } public bool TwoDigitTextYear { get { return GetXmlNodeBool("@twoDigitTextYear"); } set { SetXmlNodeBool("@twoDigitTextYear", value); } } public string Range { get { return GetXmlNodeString("@sqref"); } set { SetXmlNodeString("@sqref", value); } } } } 

Then you will need to modify ExcelWorkSheet.cs by adding this code:

 public ExcelIgnoredError _ignoredError; public ExcelIgnoredError IgnoredError { get { if (_ignoredError == null) { // Check that ignoredErrors exists XmlNode node = TopNode.SelectSingleNode("d:ignoredErrors", NameSpaceManager); if (node == null) { CreateNode("d:ignoredErrors"); } //Check that ignoredError exists node = TopNode.SelectSingleNode("d:ignoredErrors/d:ignoredError", NameSpaceManager); if (node == null) { CreateNode("d:ignoredErrors/d:ignoredError"); node = TopNode.SelectSingleNode("d:ignoredErrors/d:ignoredError", NameSpaceManager); } _ignoredError = new ExcelIgnoredError(NameSpaceManager, node, this); } return (_ignoredError); } } 

Compile the EPPPlus solution, include it in your project, and you can remove the tags using code like this:

 //Get a reference to the worksheet ExcelWorkSheet sheet = package.WorkBook.WorkSheets(0); //Set the cell range to ignore errors on to the whole sheet sheet.IgnoredError.Range = Sheet.Dimension.Address; //Do not display the warning 'number stored as text' sheet.IgnoredError.NumberStoredAsText = true; 

Answer 2

Convert Purchase Order There is no value for the number, and then save it in the cell. The green tag in the upper left corner of each cell goes because you store the number there as a string.

Answer 3

Yes, EPPlus cannot process data as a number, depending on its value. If your underlying data is in a string data type, you can try to get it in a numeric data type. for example, if you get data from a stored procedure, try to get it as a numeric value. We had a problem when we implemented it. We used the same stored procedure for the Web and generated excel. The web interface had to be in a string data type for formatting, and EPPlus obviously needs to be in numeric format so that it can show it as a number. The solution was to convert the required data to numeric when exporting to Excel using EPPlus in C # code. Thus, you need to write a conversion function to convert the required fields in the DataTable to the required data type (or implement the conversion logic using conversion or conversion in a stored procedure).

In short: - Write a C # function to convert the column data types to DataTable, which you will get before sending it as an excel sheet using EPPlus.

Answer 4

I solved this problem much easier. For example. if I define the value "123" as an object, not a string, then it keeps the excel file in order.

Answer 5

you can use

 worksheet.Cell[1,1].Formula = "TEXT(" + cellvalue ")";