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

Question

I use EPPlus to export excel 2007 file. The file can export normally but i have some problem with setting column format. My string column with numeric style (Purchase Order No. ex. 49000001) be exported with green tag on the top left of the each cell, How can i remove it?

I try to set number format to "General" but it's not work

Please help.

p.s i use C#

Gabriellegabrielli

You can do it without modifying EPPlus - see https://mcmap.net/q/905257/-how-can-i-turn-background-error-checking-off-for-the-excel-app-object-in-epplus – Mince

Answer 1

EPPLus does not currently support disabling that green tag. However, it is possible to modify the project in order to suppress it. First you will 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);
            }
        }
    }
}

Next you will need to modify ExcelWorkSheet.cs, 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 will be able to remove the tags using code similar to 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;

Yakka

Note those last lines should be // Do not display the warning 'number stored as text' sheet.IgnoredError.NumberStoredAsText = True; And thanks for posting this! Works great! – Histology

Note that setting the Range in this workaround is important. I was careless about it and got an Excel error on opening the workbook. – Frilling

@Yakka I don't have the possibility to edit the source, is there a way to edit the XML "from outside"? – Charry

@Yakka if I use your code, will it only suppress the error or will it convert the underlying datatype? because when we implemented the solution it was mandatory for the client that the excel data be usable i.e. could be used as regular excel sheet for performing operations like AVG, SUM etc. Thanks in Advance. – Pederson

Answer 2

In addition to @briddums' answer, since EPPlus version 5 you can ignore errors and there is no need to touch the EPPlus source.

var p = new ExcelPackage();
var ws = p.Workbook.Worksheets.Add("IgnoreErrors");

ws.Cells["A1"].Value = "1";
ws.Cells["A2"].Value = "2";
var ie = ws.IgnoredErrors.Add(ws.Cells["A2"]);
ie.NumberStoredAsText = true;   // Ignore errors on A2 only

Chemush

Answer 3

this code works

private void removingGreenTagWarning(ExcelWorksheet template1, string address)
            {
                var xdoc = template1.WorksheetXml;
                //Create the import nodes (note the plural vs singular
                var ignoredErrors = xdoc.CreateNode(System.Xml.XmlNodeType.Element, "ignoredErrors", xdoc.DocumentElement.NamespaceURI);
                var ignoredError = xdoc.CreateNode(System.Xml.XmlNodeType.Element, "ignoredError", xdoc.DocumentElement.NamespaceURI);
                ignoredErrors.AppendChild(ignoredError);

                //Attributes for the INNER node
                var sqrefAtt = xdoc.CreateAttribute("sqref");
                sqrefAtt.Value = address;// Or whatever range is needed....

                var flagAtt = xdoc.CreateAttribute("numberStoredAsText");
                flagAtt.Value = "1";

                ignoredError.Attributes.Append(sqrefAtt);
                ignoredError.Attributes.Append(flagAtt);

                //Now put the OUTER node into the worksheet xml
                xdoc.LastChild.AppendChild(ignoredErrors);
            }

use it as

removingGreenTagWarning(template1, template1.Cells[1, 1, 100, 100].Address);

Paramagnet

Answer 4

Convert Purchase Order No value to Number, and then store in cell. The green tag on the top left of each cell, is coming because you are storing number as string there.

Involucre

Answer 5

Yes, EPPlus cannot treat the data as number depending on its value. If your underlying data is in string datatype then you can try to get it in numeric datatype. e.g. If you get data from a stored procedure try to get it as a numeric value. There was a problem we had when we implemented it. We used the same stored procedure for Web and to generate excel. Web UI needed it to be in string datatype for formatting reasons and EPPlus obviously needs it to be in the 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. So you need to write a conversion function to convert required fields in the DataTable into datatype you require (or implement conversion logic using cast or convert in your Stored Procedure).

In summary: - Write a C# function to convert datatypes of columns in the DataTable you obtain before sending it as excel sheet using EPPlus.

Pederson

Answer 6

I've solved this problem in much more easier way. E.g. if I define value "123" as object, not a string, then it stores into excel file OK.

Selfwill

Unfortunately storing the value 000123 as an object instead of a string will have it display in the excel file as 123 and not 000123. String is important for keeping the format of the text. – Yakka

Answer 7

You can use

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

Booted