Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Mon Feb 24, 2014 8:42 am

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
Attachments
SUBTOTAL_example.png
SUBTOTAL_example.png (20.94 KiB) Viewed 14151 times

l.iglesias
 
Posts: 3
Joined: Tue Jan 29, 2013 2:40 pm

Mon Feb 24, 2014 9:30 am

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
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Tue Feb 25, 2014 6:50 am

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
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Tue Feb 25, 2014 12:21 pm

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

Thank you SO MUCH!

That did the trick :P

l.iglesias
 
Posts: 3
Joined: Tue Jan 29, 2013 2:40 pm

Wed Feb 26, 2014 1:50 am

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
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Wed Jun 25, 2014 1:42 pm

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.

j.picornell
 
Posts: 3
Joined: Wed Jun 25, 2014 1:25 pm

Thu Jun 26, 2014 8:25 am

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
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Jul 01, 2014 9:08 am

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
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Jul 01, 2014 3:18 pm

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.

j.picornell
 
Posts: 3
Joined: Wed Jun 25, 2014 1:25 pm

Wed Jul 02, 2014 7:17 am

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
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Wed Jul 02, 2014 12:54 pm

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.

j.picornell
 
Posts: 3
Joined: Wed Jun 25, 2014 1:25 pm

Thu Jul 03, 2014 6:00 am

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
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Fri Jul 11, 2014 9:08 am

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
User avatar

Benjamin Du
 
Posts: 82
Joined: Thu Jul 25, 2013 2:38 am

Return to Spire.XLS