"Unexpected token" error when trying to set SUBTOTAL

[l.iglesias]

Hi,

I'm trying to achieve something, i thought, quite simple: i'm trying to add a row with 5 columns of SUBTOTALs.

Attached is an example of what i'm trying to do.

The bold rows have the SUBTOTAL formula. For example, In the Test 4 bold row i have "SUBTOTAL(9;B7:B8)" for the B col, "SUBTOTAL(9;C7:C8)" for the C col, etc.

My problem is when i try to add the B col SUBTOTAL everything goes well, but when i try to add the C col SUBTOTAL i recieve an "Unexpected token" exception.

This is my code:

Code: Select all

formula = "SUBTOTAL(9,#COL#7:#COL#8)";
compras.Range[9, 2].Formula = formula.Replace("#COL#", "B");
compras.Range[9, 3].Formula = formula.Replace("#COL#", "C");
compras.Range[9, 4].Formula = formula.Replace("#COL#", "D");
compras.Range[9, 5].Formula = formula.Replace("#COL#", "E");
compras.Range[9, 6].Formula = formula.Replace("#COL#", "F");
compras.Range[9, 7].Formula = formula.Replace("#COL#", "G");
compras.Range[9, 8].Formula = formula.Replace("#COL#", "H");
compras.Range[9, 9].Formula = formula.Replace("#COL#", "I");


Error Message:

Unexpected token.Unexpected token type: Identifier, string value: C7:C8 . Formula: SUBTOTAL(9,C7:C8)

StackTrace of the error:

en ᣛ.ᜀ(String A_0, Exception A_1)
en ᣛ.ᜀ(String A_0)
en ᧕.ᜀ(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
en ᧕.ᜀ(ParseFormulaOptions A_0, ParseParameters A_1, ExcelFunction A_2)
en ᧕.ᜀ(ExcelFunction A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
en ᧕.ᜀ(Dictionary`2 A_0, Int32 A_1, ParseFormulaOptions A_2, ParseParameters A_3)
en ᧕.ᜀ(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions& A_3, ParseParameters A_4)
en ᧕.ᜀ(Priority A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
en ᧕.ᜁ(String A_0, Dictionary`2 A_1, Int32 A_2, ParseFormulaOptions A_3, ParseParameters A_4)
en Spire.Xls.Core.Spreadsheet.FormulaUtil.ᜀ(String A_0, IWorksheet A_1, Dictionary`2 A_2, Int32 A_3, Int32 A_4, Boolean A_5)
en Spire.Xls.Core.Spreadsheet.XlsRange.SetFormula(String value, Dictionary`2 hashWorksheetNames, Boolean bR1C1)
en Spire.Xls.Core.Spreadsheet.XlsRange.SetFormula(String value)
en Spire.Xls.Core.Spreadsheet.XlsRange.OnValueChanged(String old, String value)
en Spire.Xls.Core.Spreadsheet.XlsRange.set_Value(String value)
en Spire.Xls.Core.Spreadsheet.XlsRange.set_Formula(String value)

My Spire.XLS.dll version is 7.0.0.7040

[amy.zhao]

Hello,

Thanks for your inquiry.
Did you get the error when ran the line "compras.Range[9, 3].Formula = formula.Replace("#COL#", "C");" ?
Would you please provide us your test data? Because we didn't reproduce the error with some test data.
Thanks for your feedback.

Best wishes,
Amy
E-iceblue support team

[amy.zhao]

Hello,

The error may be caused by the decimal separator of your local culture.
Please try the following solution. If you still have the error, would you please provide us your excel file? It will help us to reproduce quickly and resolve your problem.

Code: Select all

 CultureInfo cc = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\sample.xlsx");
            Worksheet compras = workbook.Worksheets[0];
            string formula = "SUBTOTAL(9,#COL#7:#COL#8)";
            compras.Range[9, 2].Formula = formula.Replace("#COL#", "B");
            compras.Range[9, 3].Formula = formula.Replace("#COL#", "C");
            compras.Range[9, 4].Formula = formula.Replace("#COL#", "D");
            compras.Range[9, 5].Formula = formula.Replace("#COL#", "E");
            compras.Range[9, 6].Formula = formula.Replace("#COL#", "F");
            compras.Range[9, 7].Formula = formula.Replace("#COL#", "G");
            compras.Range[9, 8].Formula = formula.Replace("#COL#", "H");
            compras.Range[9, 9].Formula = formula.Replace("#COL#", "I");

            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);

            Thread.CurrentThread.CurrentCulture = cc;

Thanks.

Best wishes and have a nice day,
Amy
E-iceblue support team

[l.iglesias]

I was uploading the example when i saw the cultureinfo solution.

Thank you SO MUCH!

That did the trick

[amy.zhao]

Hello,

Thanks for your feedback.
We are glad to hear from you that it resolve your issue.
Please don't hesitate to contact us if you have any problems.

Have a nice day.

Best wishes,
Amy
E-iceblue support team

[j.picornell]

Hello,

Sorry if I reopen the post, but I'm a mate of l.iglesias and we are working on the same environment. The thing is, we cannot change the culture because it's conflicting with some drivers of DDBB, causing the whole server to crash (not being able to connect to out DDBB). Is there some other fix we could do to change the culture that spire is getting?

We are on a spanish culture, and the formula we are trying to run is "IF(4,2,2)", got from the Demo's page of Spire.XLS.

Thank you for the support.

Greetings,
Joan Picornell.

[Gary.zhang]

Hello,

Thanks for your inquiry.
We suggest you could use multi-thread. The method to change the culture only change the culture of current thread.

If there are any questions, welcome to get it back to us.
Sincerely,
Gary
E-iceblue support team

[Gary.zhang]

Hello,

Has the issue been resolved? Could you please give us some feedback if convenience?

If there are any questions, welcome to get it back to us.

Thanks,
Gary
E-iceblue support team

[j.picornell]

Hello,

I've been tested with the multithreading options, but the fix of the InvariantCulture isn't working . Now I'm testing changing the CurrentCulture of the Current thread without multithreading and i'm getting the same error.

Here is the code:

Code: Select all

              Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
                MemoryStream xlsDoc = gestorReport.GenerateReport();

And it's throwing me the exception at this formula (that is extracted from the Formulas Example pages)

Code: Select all

tab1.Range[fila, x].Formula = "=IF(4,2,2)";

I'm getting the same error:

Code: Select all

Unexpected token.Unexpected token type: tNumber, string value: ,2  at position 7. Formula: IF(4,2,2), Position: 9

Thank you for the support!

Greetings,
Joan Picornell.

[Gary.zhang]

Hello,

Thanks for your feedback.
When you use the method about our product, please make sure that it is in the InvariantCulture like the above code Amy provided.
Sincerely,
Gary
E-iceblue support team

[j.picornell]

Hello Gary,

I'm getting the same error even if I change the culture to InvariantCulture, as I explained on the post above.

Thank you.

Greetings,
Joan Picornell.

[Gary.zhang]

Hello,

Thanks for your response.
We have tested it again, there is no the error you mentioned when I change the culture to InvariantCulture, the below codes are our test code. Please rebuild the project after changing the culture.

Code: Select all

CultureInfo cc = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
Workbook workbook = new Workbook();
workbook.LoadFromFile("test.xlsx");
Worksheet compras = workbook.Worksheets[0];
compras.Range[9, 4].Formula = "=IF(4,2,2)";
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);
Thread.CurrentThread.CurrentCulture = cc;


If the issue persists, please provide us your test codes or provide us a simple project that can reproduce the issue, which will helpful to work out the solution for you ASAP.
Thanks,
Gary
E-iceblue support team

[Benjamin Du]

Hello,

Have you tested the code provided by my colleague Gary? Has the issue been resolved?
Could you please provide us some feedback if convenience?
If you have any questions, welcome to get it back to us.

Regards,
Benjamin
E-iceblue support team