We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

WorksheetImpl.InsertRow gives ArgumentException - Cannot find Formula token with code: None

Hi,
I am having a problem where I get an ArgumentException when I call InsertRow.

worksheet.InsertRow(insertAt, 1, ExcelInsertOptions.FormatDefault);

where insertAt = 541

The exception message is "Cannot find Formula token with code: None"

Now it is occurring a long way into the spreadsheet creation. It is up to row 541 (it starts at row 18). It is a large spreadsheet with lots of formulas. Could you please give some suggestions on what I should look for?

The full details of the exception is

System.ArgumentException occurred
Message="Cannot find Formula token with code: None"
Source="Syncfusion.XlsIO.Base"
StackTrace:
at Syncfusion.XlsIO.Implementation.FormulaUtil.CreatePtg(DataProvider provider, Int32& offset, ExcelVersion version)
at Syncfusion.XlsIO.Implementation.FormulaUtil.ParseExpression(DataProvider provider, Int32 offset, Int32 iExpressionLength, Int32& finalOffset, ExcelVersion version)
at Syncfusion.XlsIO.Parser.Biff_Records.ArrayRecord.ParseStructure(DataProvider provider, Int32 iOffset, Int32 iLength, ExcelVersion version)
at Syncfusion.XlsIO.Implementation.Collections.RowStorage.GetRecord(Int32 iColumnIndex, Int32 iBlockSize)
at Syncfusion.XlsIO.Implementation.Collections.RecordTable.get_Item(Int32 rowIndex, Int32 colIndex)
at Syncfusion.XlsIO.Implementation.Collections.CellRecordCollection.get_Item(Int32 iRow, Int32 iColumn)
at Syncfusion.XlsIO.Implementation.WorksheetImpl.InnerGetCell(Int32 column, Int32 row, Int32 iXFIndex)
at Syncfusion.XlsIO.Implementation.WorksheetImpl.InnerGetCell(Int32 column, Int32 row)
at Syncfusion.XlsIO.Implementation.RangeImpl.get_Item(Int32 row, Int32 column)
at Syncfusion.XlsIO.Implementation.WorksheetImpl.get_Item(Int32 row, Int32 column)
at Syncfusion.XlsIO.Parser.Biff_Records.Formula.Ref3DPtg.GetRange(IWorkbook book, IWorksheet sheet)
at Syncfusion.XlsIO.Implementation.Collections.WorkbookNamesCollection.InsertRemoveRow(IRangeGetterToken token, String strSheetName, Int32 index, Boolean bIsRemove, Boolean bIsRow, Int32 iCount, IWorksheet sheet)
at Syncfusion.XlsIO.Implementation.Collections.WorkbookNamesCollection.InsertRemoveRowColumn(String strSheetName, Int32 index, Boolean bIsRemove, Boolean bIsRow, Int32 iCount)
at Syncfusion.XlsIO.Implementation.WorksheetImpl.InsertRow(Int32 iRowIndex, Int32 iRowCount, ExcelInsertOptions insertOptions)
at FacDig.Symphony.BLL.MediaScheduleExport.FDExcelUtils.InsertRows(IWorksheet worksheet, Int32 insertAt, Int32 templateRow, Boolean clearValues, Int32 rowCount) in C:\Projects\Symphony\Symphony v2.0\FacDig.Symphony.BLL\ExportFramework\Exports\FDExcelUtils.cs:line 594
InnerException:


Thank you,

Regards Ben

14 Replies

LR Lokesh R Syncfusion Team May 26, 2010 05:22 AM UTC

Hi Ben,

Thank you very much for your interest on Syncfusion products.

I am afraid that i could not able to reproduce the issue at my side and it would be great, if you could able to send us the excel spreadsheet in which you have tried to perform the InsertRow function and also please update the below details which will be very helpful for further analysis on this issue.

Details required:

Current Syncfusion Essential Studio Product version.
Input sample sheet.
Framework version.
Excel version.

Please, let us know if you need any clarifications.

Thanks,
Lokesh.



BR Benjamin Rogers May 27, 2010 01:01 AM UTC

Hi Lokesh,
Thankyou for your reply. The version of

Syncfusion.XlsIO.Base
Syncfusion.core
Syncfusion.compression.base

is 7.303.0.20

I am using Microsoft Office 2007, but the template and created spreadsheet is 97-2003.

The .NET Framework verion is 3.5.

I have attached a save of the partially created spreadsheet at the time the exception was thrown. It is called CurrentNew10.xls. I also included the template used to create the spreadsheet. However once the spreadsheet file is saved and if you reopen it the problem does not occur. That is you can insert blank rows without getting the error. I only get the error as part of the full generation process.

I have removed all formulas from the creation of the spreadsheet, so I am stuck. Because of the message "Cannot find Formula token with code: None", I was expecting that the recalculation of a formula was causing the problem and if I systematically removed formulas from the spreadsheet creation I could identify which one is causing the problem. However I have now removed all of the formulas and I still get the exception.

Are you able to explain what the error message means? What is a formula token? Also in the context of inserting blank rows what sort of things could cause it?

Thank you for helping me with this problem.

Regards Ben



LR Lokesh R Syncfusion Team May 27, 2010 09:34 AM UTC

Hi Ben,

Thank you very much for the update.

Full generation process:

I have tried to insert a row in the template which you have posted in the last post, i am afraid that i could not able to see any exceptions thrown and also you have mentioned that you are getting the exception while you are performing full generation process. It would be great if you can able to update us with the simplified issue reproducing sample(which should include the insert row function and the index where you have inserted). It will be helpful for us in further analysis.

Programmatically removed:

Could you please update us with the code snippet that you have used to remove the formulae from the worksheet. We hope that the formulas are not completely removed from the worksheet. So XlsIO will think that there is a formula in the range and tries to retrieve the token associated for the formula, if it could not able to retrieved back it will be returned as none, So an exception will be thrown.

Formula token:
In XlsIO, internally we have assigned token for a group of formulas, with that we used to make manipulations. If the particular token is not retrieved back properly, then it will be assigned to none.

Insert Blank row causes:

While inserting the rows internally we check few conditions.
1.We won’t separate the array formulas
2.Inserted cells won’t be moved out of the worksheet
3.Check whether rows can be inserted.
other than the above conditions, we don’t have any specific causes that could affect the serialization of worksheet.

Please, let us know if you need any more clarifications.

Thanks,
Lokesh





BR Benjamin Rogers May 28, 2010 05:46 AM UTC

Hi Lokesh,
Thankyou for your reply. I had a breakthrough. I can now give you a very simple set of steps to reproduce the problem. I have included all the relevant files in ArgumentExceptionFiles.zip.

To reproduce the problem all you need to do is open either one of the xlt files and Insert 600 rows. The exception will be thrown on the 544th insert.

The files I have included in the zip file are:

MECMediaScheduleExport.xlt - the Excel template with all formulas removed

MECMediaScheduleExportCurrent.xlt - the Excel template that still has all the formulas

PictureOfArgumentException.bmp - print screen of the exception on my computer

Program.cs - C# source file for the small program that opens the template and tries to add 600 rows

SyncfusionPlay.csproj - C# project for small program

Thank you again for your help.

Regards Ben



LR Lokesh R Syncfusion Team May 28, 2010 09:04 AM UTC

Hi Ben,

Thank you very much for the detailed description.

We can be able to reproduce the issue at our side with the assemblies (7.3.0.20),but this specific issue has been already fixed in our latest version(8.2.0.18),while testing with our latest version we found that there are some related issues are thrown. So, we have logged an issue report and forwarded to the development team for further analysis.

We request you to open a directtrac incident to further follow up on this issue.

Direct trac link:
https://www.syncfusion.com/Support/DirectTrac/logon.aspx?URL=/Support/DirectTrac/default.aspx

Please, let us know if you have any queries.

Thanks,
Lokesh.


BR Benjamin Rogers May 29, 2010 11:39 PM UTC

Hi Lokesh,
Thank you for testing my demo of the issue. We are looking at upgrading to version 8.2 in the near future. However in the meantime is there a workaround? Is there a change I could make to the Excel spreadsheet template that would bypass this problem? Thank you again for your time.
Regards Ben


LR Lokesh R Syncfusion Team May 31, 2010 11:28 AM UTC

Hi Ben,

Thank you very much for the update.

I have analyzed your sample to provide a workaround, if i try to bypass this exception it causes the output sample to get corrupted. So i strongly recommend you to upgrade your current essential studio version to the latest version(8.2.0.18).I have attached the sample and the latest beta version assemblies, could you please try the sample with the assemblies from the below link and let me know if this helps you.


Please, let me know if you have any queries.

Thanks,
Lokesh.



BR Benjamin Rogers June 15, 2010 01:57 AM UTC

Hi Lokesh,
Is there any way I can remove the zip files from this thread?
Regards Ben


LR Lokesh R Syncfusion Team June 15, 2010 10:52 AM UTC

Hi Ben,

We have forwarded your request to the directtrac team for further process. We will update you with more details in one business days(i.e.,16th june 2010).

Please,let me know if you need any clarifications.

Thanks,
Lokesh.


LR Lokesh R Syncfusion Team June 15, 2010 04:19 PM UTC

Hi Ben,

Could you please update us the zip file posted dates that need to be removed, so that we can proceed further on this process.

please, let us know if you need any clarifications.

Thanks,
Lokesh.



BR Benjamin Rogers June 15, 2010 11:35 PM UTC

Hi Lokesh,
Could you please remove the following

Benjamin Rogers at 5/26/2010 9:01:27 PM
InsertRowProblem_b6def2d2.zip


Benjamin Rogers at 5/28/2010 1:46:12 AM
ArgumentExceptionFiles_a738b6a9.zip


Lokesh R[Syncfusion] at 5/31/2010 7:28:32 AM
ConsoleApplication2_96f8b21.zip

They are all on this thread.

Regards Ben


LR Lokesh R Syncfusion Team June 16, 2010 11:47 AM UTC

Hi Ben,

Thank you very much for the details.

As you have reported, We have removed the mentioned zip files from the forum thread.

Please, let us know if you need any clarifications.

Thanks,
Lokesh.


BR Benjamin Rogers June 16, 2010 11:09 PM UTC

Hi Lokesh,
Thankyou for removing the zip files from the thread. I am currently evaluating version 8.203.0.18.
Regards Ben


LR Lokesh R Syncfusion Team June 17, 2010 04:05 AM UTC

Hi Ben,

Thank you very much for your update. Please let us know immediately if you have any concerns in the evaluation of 8.203.0.18 version.

Thanks,
Lokesh

Loader.
Live Chat Icon For mobile
Up arrow icon