Formula Evaluation error

Jan 13, 2015 at 4:58 PM
I have an xlsx file written by a customer. It contains some formulas using italian syntax.
From my vb net application I've to write some values into a worksheet of the file and then read some values of formula cells. The results are calculated using values written by me previously.

All work well using the excel file, not using EPPLUS library from visual studio

What I've done is this:
  1. Write values in 3 cells of the worksheet
  2. Call the calculate method of the worksheet
  3. Read value from a formula cell. This is the formula: =SE.ERRORE(CERCA.VERT(CONCATENA(1;E3);'MECCANISMI BLUM AVENTOS'!$A:$H;3;0);"")
... and this is the properties read from immediate window:
? cvExcelWorksheet_CalcoloMeccanismo.Cells(lvRiga, 6)
{F2}
_address: "F2"
_addresses: Nothing
_fromCol: 6
_fromRow: 2
_toCol: 6
_toRow: 2
_wb: Nothing
_workbook: {OfficeOpenXml.ExcelWorkbook}
_worksheet: {CALCOLO MECCANISMO}
_ws: "CALCOLO MECCANISMO"
Address: "F2"
Address (ExcelAddressBase): "F2"
Addresses: Nothing
AddressSpaceSeparated: "F2"
AutoFilter: False
Columns: 1
Comment: Nothing
ConditionalFormatting: {OfficeOpenXml.ConditionalFormatting.RangeConditionalFormatting}
Current: {"Riferimento a un oggetto non impostato su un'istanza di oggetto."}
DataValidation: {OfficeOpenXml.DataValidation.RangeDataValidation}
End: {OfficeOpenXml.ExcelCellAddress}
FirstAddress: "F2"
Formula: "IFERROR(VLOOKUP(CONCATENATE(1,E2),'MECCANISMI BLUM AVENTOS'!$A:$H,3,0),"")"
FormulaR1C1: "IFERROR(VLOOKUP(CONCATENATE(1,RC[-1]),'MECCANISMI BLUM AVENTOS'!$A:$H,3,0),"")"
FullAddress: "'CALCOLO MECCANISMO'!F2"
FullAddressAbsolute: "'CALCOLO MECCANISMO'!$F$2"
FullAddressAbsoluteNoFullRowCol: "'CALCOLO MECCANISMO'!$F$2"
Hyperlink: Nothing
IsArrayFormula: False
IsName: False
IsRichText: False
Item: In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
Item: In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
Item: In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
Merge: False
RichText: {OfficeOpenXml.Style.ExcelRichTextCollection}
Rows: 1
Start: {OfficeOpenXml.ExcelCellAddress}
Style: {OfficeOpenXml.Style.ExcelStyle}
StyleID: 22
StyleName: "Normale"
Table: Nothing
Text: "#NAME?"
TextForWidth: "#NAME?"
Value: "#NAME?" {String}
Worksheet: {CALCOLO MECCANISMO}
WorkSheet (ExcelAddressBase): "CALCOLO MECCANISMO"
Results: Expanding will process the collection
As you can see Value returned is #VALUE

Why?

Thank you in advance
Developer
Jan 13, 2015 at 6:19 PM
Since I dont have access to your workbook its not so easy to know exactly what is going wrong, but the functions IFERROR, VLOOKUP and CONCATENATE are allsupported by EPPlus. However your VLOOKUP call seems to refer to another worksheet and perhaps the range there contains an error. You say that the error is #VALUE, but it looks like it is #NAME? The #NAME? error normally means that there is a call to an Excel function not yet supported by EPPlus.

What you can try is the following:
  1. Call Calculate() on the workbook instead of the worksheet, to ensure that all worksheets are calculated in the right order.
  2. If this still does not work, make sure that the ranges that are referred does not contain unsupported functions. In the "Documentation" section above you can find a list of the functions supported by EPPlus.
  3. Download the latest code and compile, we have added more functions and fixed a few bugs lately.
Jan 13, 2015 at 10:15 PM
Edited Jan 13, 2015 at 10:30 PM
Thank you for the interest to my problem. You are right, the error is #NAME? I’ve tried the 3 solutions proposed but no one solve the problem. I've tried to enclose the excel file but I think it's not possible. What I do from my application is, for example, this: 1. Write value 357 in cell 2, 1 2. Write value 497 in cell 2, 2 3. Call workbook.calculate() 4. I read values in column F from row 2 to 8. When I read cell 2, 6 #NAME? is returned. If I compile manually in the same way the excel worksheet values in column F are calculated correctly. Denis Basei
Developer
Jan 14, 2015 at 6:53 AM
You can enclose the Excel file if you create an issue (see the "Issues" link in the top menu)..

/Mats
Jan 14, 2015 at 7:32 AM
Hy swmal

I create an issue a moment ago.


Thank you
Jan 19, 2015 at 9:27 AM
Hi swmal

Do you remember the problem with IFERROR of last week?
Today using the new release of EPPLUS I've noticed that the formula doen't return error but Always return empty string.

Now I open an issue.
Can you check it?


Thank you.
Jan 19, 2015 at 9:06 PM
Do to the urgency to find a solution I give you more details:

The formula giving the problem is this: IFERROR(VLOOKUP(CONCATENATE(1,E4),'MECCANISMI BLUM AVENTOS'!
A2:C26,3,FALSE),"")

The problem is that VLOOKUP Always return #N/D and consiquently string.empty
This happened only using EPPlus library, in the workbook the calculation is correct.


Thank you
Denis
Jan 20, 2015 at 6:36 AM
Do you think my problem will be examinated today?
Excuse me for the question.
I think EPPlus is a very good library and probably my problem is an oversight but I've to solve very urgently. If no I'll search another way.


Thanks a lot.
Developer
Jan 20, 2015 at 1:52 PM
Hi Denis,

I might find the time to take a look at this tonight CET. The time we spend on this project is sparetime (non-workrelated), so it is hard to make comittments.

/Mats
Developer
Jan 20, 2015 at 5:55 PM
Edited Jan 20, 2015 at 6:07 PM
I have now tried to recreate your problem. First I wrote two unittests that concatenated an int with a string using the CONCATENATE function. Both succeeds to concatenate the int and string.

Then I opened the Excel workbook that you have supplied with the issue - Excel02.xlsx. In cell F2 in the first worksheet I find the following formula:

=IFERROR(VLOOKUP(CONCATENATE(1;E2),'MECCANISMI BLUM AVENTOS'!$A:$H;3;FALSE),"")

...which I guess is the formula that fails. However this returns the empty string both opened by Excel and when I use the Calculate method of EPPlus.

The VLOOKUP searches for the string "1HF - PACCHETTO" in the range MECCANISMI BLUM AVENTOS'!$A:$H, which it - correctly - fails to find. Therefore IFERROR it returns the empty string.

I will try to help you, but I need your help with a clear description on how to recreate the error.
Developer
Jan 20, 2015 at 6:07 PM
When I change CONCATENATE(1,E2) to CONCATENATE(0,E2) it returns "20F2201". Dont know if this is the exptected result, but it seems like EPPlus can compile the formula.

I am using the latest commit of the EPPlus source code - 349b0e32e7c7.
Jan 20, 2015 at 9:45 PM

Hi swmal and thanks a lot for the answers

I try to explain with an example:

in the xlsx file try this: write 389 in A2 and 1000 in B2; automatically in F4 you read 20L2500

in the xlsx file try this: write 357 in A2 and 597 in B2; automatically in F4 you read 20L2300

If I made the same thing using EPPLUS in F4 I read always string empty

This is the peace of vb net code:

Try

Dim lvStrMorfologiaFiglio = Par_CodiceFiglio.Trim.Substring((Par_CodiceFiglio.Trim.Length - 2), 2)

' Scrivo le dimensioni nel foglio "CALCOLO MECCANISMO" in questo ordine:

' Cella A2 = Par_DimensioneZ

' Cella B2 = Par_DimensioneX

cvExcelWorksheet_CalcoloMeccanismo.Cells(2, 1).Value = Par_DimensioneZ.ToString

cvExcelWorksheet_CalcoloMeccanismo.Cells(2, 2).Value = Par_DimensioneX.ToString

' Ricerco una corrispondenza di modello nel foglio "MODELLO-TIPO PANNELLO"

Dim lvStrCodiceModelloAnta As String = ChiaveModello(Par_CodiceModello)

If (lvStrCodiceModelloAnta <> String.Empty) Then

cvExcelWorksheet_CalcoloMeccanismo.Cells(2, 3).Value = lvStrCodiceModelloAnta

cvExcelWorksheet_CalcoloMeccanismo.Calculate()

cvExcelWorksheet_MeccanismiBlum.Calculate()

'cvExcelPackage.Workbook.Calculate()

' ... a questo punto le formule impostate nel foglio scrivono nell'intervallo di celle F2:F8 i codici BLUM.

Dim lvListaMeccanismiBlum As New List(Of Class_MeccanismoBlum)

Dim lvListaMeccanismiBlumTraslati As New List(Of Class_MeccanismoBlum)

Dim lvListaMeccanismiBlumBasculanti As New List(Of Class_MeccanismoBlum)

' Variabile temporanea dove scrivo la lista di stringhe che ritornerà la funzione.

Dim lvStrMeccanismiBlum As New List(Of String)

For lvRiga As Byte = 4 To 8

'cvExcelWorksheet_CalcoloMeccanismo.Cells(lvRiga, 6).Calculate()

If Not (cvExcelWorksheet_CalcoloMeccanismo.Cells(lvRiga, 6).Value = String.Empty) Then

Dim lvCodiceBlum As String = cvExcelWorksheet_CalcoloMeccanismo.Cells(lvRiga, 6).Value.ToString.Trim

For lvIndMecc As Byte = 2 To 50

If (cvExcelWorksheet_MeccanismiBlum.Cells(lvIndMecc, 3).Value.ToString.Trim = lvCodiceBlum) AndAlso (cvExcelWorksheet_MeccanismiBlum.Cells(lvIndMecc, 5).Value = 1) Then

' Controllo se lvIndMecc cade nell'intervallo del tipo meccanismo "TRASLATO"

If ((lvIndMecc >= 5) AndAlso (lvIndMecc <= 18)) Then ………

……………………………..

highlighted line always return stirng empty.

Da: swmal [email removed]
Inviato: martedì 20 gennaio 2015 20:59
A: [email removed]
Oggetto: Re: Formula Evaluation error [EPPlus:577630]

From: swmal

I have now tried to recreate your problem. First I wrote two unittests that concatenated an int with a string using the CONCATENATE function. Both succeeds to concatenate the int and string.

Then I opened the Excel workbook that you have supplied with the issue - Excel02.xlsx. In cell F2 in the first worksheet I find the following formula:

=IFERROR(VLOOKUP(CONCATENATE(1;E2),'MECCANISMI BLUM AVENTOS'!$A:$H;3;FALSE),"")

...which I guess is the formula that fails. However this returns the empty string both opened by Excel and when I use the Calculate method of EPPlus.

The VLOOKUP searches for the string "1HF - PACCHETTO" in the range MECCANISMI BLUM AVENTOS'!$A:$H, which it - correctly - fails to find. Therefore IFERROR it returns the empty string.

I will try to help you, but I need your help with a clear description on how to recreate the error.

Developer
Jan 21, 2015 at 5:02 AM
Thank you. I have now managed to recreate the error, but it is quite a long calculation chain... I can now see that EPPlus fails to calculate cell H11 on the second worksheet. I will continue trying to resolve this asap.
Developer
Jan 21, 2015 at 12:55 PM
I think I have found the error now. You have a worksheet named MODELLO-TIPO PANNELLO. If you remove the - in other words rename the worksheet to MODELLO TIPO PANNELLO it seems to work.

It seems like EPPlus currently cannot handle the - character in worksheet names.
Jan 27, 2015 at 8:25 AM

Good morning swmal

I’ve used Office.Interop library for the urgency to deliver my application.

Formulas are very complex and I can’t change it because excel file was developed by my customer.

This time I prefer to use Office.Interop.

Thank you

Da: swmal [email removed]
Inviato: lunedì 26 gennaio 2015 22:46
A: [email removed]
Oggetto: Re: Formula Evaluation error [EPPlus:577630]

From: swmal

I think I have found the error now. You have a worksheet named MODELLO-TIPO PANNELLO. If you remove the - in other words rename the worksheet to MODELLO TIPO PANNELLO it seems to work.

It seems like EPPlus currently cannot handle the - character in worksheet names.

Developer
Jan 27, 2015 at 9:05 AM
Ok, thanks for your feedback anyway. Thanks to the workbook you supplied we managed to fix a few bugs and performance issues- next version of EPPlus will be able to parse your entire workbook in some seconds and the problem with worksheet names is fixed.
Jan 27, 2015 at 9:14 AM

I’ve downloaded source code, recompiled end referenced the EPPLus library to my project, just to try the library

Honestly it still does not work.

By.

Da: swmal [email removed]
Inviato: martedì 27 gennaio 2015 11:07
A: [email removed]
Oggetto: Re: Formula Evaluation error [EPPlus:577630]

From: swmal

Ok, thanks for your feedback anyway. Thanks to the workbook you supplied we managed to fix a few bugs and performance issues- next version of EPPlus will be able to parse your entire workbook in some seconds and the problem with worksheet names is fixed.

Developer
Jan 29, 2015 at 11:40 AM
Sorry to hear that this did not work out. Just wanted to share the tests that I performed on the workbooks you enclosed with the issue you created.

These tests both succeeds. If you have the time it would be valuable if you provided more detailed information about the error.
        [TestMethod]
        public void Issue15173_1()
        {
            using (var pck = new ExcelPackage(new FileInfo(@"c:\temp\EPPlusIssues\Excel01.xlsx")))
            {
                var sw = new Stopwatch();
                pck.Workbook.FormulaParser.Configure(x => x.AttachLogger(LoggerFactory.CreateTextFileLogger(new FileInfo(@"c:\Temp\log1.txt"))));
                sw.Start();
                var ws = pck.Workbook.Worksheets.First();
                //ws.Calculate();
                pck.Workbook.Calculate();
                Assert.AreEqual("20L2300", ws.Cells["F4"].Value);
                Assert.AreEqual("20K2E01", ws.Cells["F5"].Value);
                sw.Stop();
                Console.WriteLine(sw.Elapsed.TotalSeconds); // approx. 10 seconds

            }
        }

        [TestMethod]
        public void Issue15173_2()
        {
            using (var pck = new ExcelPackage(new FileInfo(@"c:\temp\EPPlusIssues\Excel02.xlsx")))
            {
                var sw = new Stopwatch();
                pck.Workbook.FormulaParser.Configure(x => x.AttachLogger(LoggerFactory.CreateTextFileLogger(new FileInfo(@"c:\Temp\log1.txt"))));
                sw.Start();
                var ws = pck.Workbook.Worksheets.First();
                //ws.Calculate();
                pck.Workbook.Calculate();
                Assert.AreEqual("20L2300", ws.Cells["F4"].Value);
                Assert.AreEqual("20K2E01", ws.Cells["F5"].Value);
                sw.Stop();
                Console.WriteLine(sw.Elapsed.TotalSeconds); // approx. 10 seconds

            }
        }