1. Home
  2. Forum
  3. WinForms
  4. Sheet.ListObjects.Create throwing error only if excel file not manually opened first

Sheet.ListObjects.Create throwing error only if excel file not manually opened first

Hi, I have a strange issue that I am hoping you can help with. I am using the Microsoft Graph api to open an excel doc via a stream to xlsio where I am then editing the document and saving it back to graph (in a sharepoint site).

This works great the first time the code is run. Everytime the worksheet is edited it clears the sheet, clears the range, and clears the listobjects, updates the data, and then creates a new table under a standard name.

the table row looks like this:

  Syncfusion.XlsIO.IListObject tbl = sheet.ListObjects.Create("dev", rng);

When I run this for the first time it works great, but then if I run it a second time I get the following error on the line above:

"An exception of type 'System.ArgumentOutOfRangeException' occurred in Syncfusion.XlsIO.Base.dll but was not handled in user code
Additional information: Specified argument was out of the range of valid values."

The strange thing is, if I go to the sharepoint site and open the document and then close it and run the code again it runs fine. But if I run the code twice in a row without opening/closing the document in between each run it give this error. It is as if opening/closing the excel doc is resetting something. I need the user to be able to continually run this code without having to open the source document. Any ideas as to what is causing this?

9 Replies
KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team April 22, 2020 09:43 AM UTC

Hi Travis, 

Greetings from Syncfusion. 

We are unable to reproduce the reported issue at our end. It would be helpful for us to check the issue, if you could share us the following details. 

  1. The Excel document you are using at your end.
  2. Issue reproducing sample or Code snippet to reproduce the issue.
  3. Syncfusion XlsIO version you are using at your end.
  4. Also please confirm us whether you are saving the Excel document in same name.

Regards, 
Keerthi. 


TC Travis Chambers April 22, 2020 12:32 PM UTC

Hi, a sanitized version of the document is attached. 

What my app does is allow the user to upload a raw excel document into a GridControl where it is then cleaned to match the format of this document, which is then saved to the document on the Sharepoint site. It does save the document under the same name everytime.

I am using version 17.4.0.39.

The code to do this looks like this:


                ExcelEngine engine = new ExcelEngine();
                IApplication application = engine.Excel;

                application.UseFastRecordParsing = true;

           
                var stream = await graphClient.Sites["Example.sharepoint.com,c6df194a-5585-460792-9d79-tc6ff711a050,e7f5trc1f-abc-411h-abda-ggged76102c4"].Drive.Root.ItemWithPath(templateBox.Text + ".xlsx").Content.Request().GetAsync();

                stream.Position = 0;

                MemoryStream file = new MemoryStream();
                stream.CopyTo(file);

                file.Position = 0;

                //Load the stream into IWorkbook
                IWorkbook doc = application.Workbooks.Open(file);

                IWorksheet sheet = doc.Worksheets[0];

              
                sheet.Clear();
                sheet.ListObjects.Clear();
                sheet.UsedRange.Clear();

                MemoryStream outputStream = new MemoryStream();
                doc.SaveAs(outputStream);
                //Set the stream position as '0'
                outputStream.Position = 0;
                for (int n = 0; n < sheet.Names.Count; n++)
                {
                    sheet.Names.RemoveAt(n);
                }
                for (int i = 0; i <= gridControl4.RowCount; i++)
                {
                    for (int j = 1; j <= gridControl4.ColCount; j++)
                    {
                        sheet.Range[i + 1, j].Value = gridControl4[i, j].Text;
                    }
                }

                //currency formatting
                try
                {
                    doc.Styles.Remove("ColumnStyle");
                }
                catch { }
                Syncfusion.XlsIO.IStyle columnStyle = doc.Styles.Add("ColumnStyle");
                //columnStyle.IncludeNumberFormat = true;
                columnStyle.NumberFormat = "$#,##0.00";
                //columnStyle.Color = Color.Blue;
                sheet.SetDefaultColumnStyle(19, 19, columnStyle);
                sheet.SetDefaultColumnStyle(20, 20, columnStyle);
                sheet.SetDefaultColumnStyle(21, 21, columnStyle);
                sheet.SetDefaultColumnStyle(22, 22, columnStyle);
                sheet.SetDefaultColumnStyle(23, 23, columnStyle);
                sheet.SetDefaultColumnStyle(24, 24, columnStyle);
                sheet.SetDefaultColumnStyle(25, 25, columnStyle);
                sheet.SetDefaultColumnStyle(26, 26, columnStyle);
                sheet.SetDefaultColumnStyle(27, 27, columnStyle);
                sheet.SetDefaultColumnStyle(28, 28, columnStyle);
                sheet.SetDefaultColumnStyle(29, 29, columnStyle);
                sheet.SetDefaultColumnStyle(30, 30, columnStyle);

                    try
                    {
                        var rng = sheet.Range[1, 1, gridControl4.RowCount + 1, gridControl4.ColCount];
                       // address = rng.AddressGlobal.ToString();
                        Syncfusion.XlsIO.IListObject tbl = sheet.ListObjects.Create("dev", rng);
                 
                        tbl.BuiltInTableStyle = TableBuiltInStyles.TableStyleLight8;
                    }
                    catch (Exception ex) { MessageBox.Show(ex.Message); }
               
                //save
            
                doc.SaveAs(outputStream);
                //Set the stream position as '0'
                outputStream.Position = 0;

                await graphClient.Sites["Example.sharepoint.com,c6df194a-5585-460792-9d79-tc6ff711a050,e7f5trc1f-abc-411h-abda-ggged76102c4"].Drive.Root.ItemWithPath(templateBox.Text + ".xlsx").Content.Request().PutAsync<Microsoft.Graph.DriveItem>(outputStream);


                doc.Close(false);
                engine.Dispose();


Thanks!

Attachment: Testing_a14e1c7c.zip

KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team April 23, 2020 12:34 PM UTC

Hi Travis, 

Thanks for sharing the requested details. 

We have reproduced the issue using the shared Excel document and code snippet.  

As we do not have the code for GridControl for rows and columns, we have stored the data into DataTable using ExportDataTable method of XlsIO and then filled the data in this DataTable into worksheet using ImportDataTable method of XlsIO.  

This is to confirm you that, the issue is reproduced in simple console application also, without using Microsoft Graph API. The sample which we have tried at our end, to reproduce the issue, can be downloaded from the following link. 


The stack trace of the exception is as follows. 

Stack Trace: 

   at Syncfusion.XlsIO.Implementation.Collections.ExtendedFormatsCollection.get_Item(Int32 index) 
   at Syncfusion.XlsIO.Implementation.CellStyle.EndUpdate() 
   at Syncfusion.XlsIO.Implementation.ExtendedFormatWrapper.set_NumberFormat(String value) 
   at Syncfusion.XlsIO.Implementation.RangeImpl.set_NumberFormat(String value) 
   at Syncfusion.XlsIO.Implementation.RangeImpl.set_Text(String value) 
   at Syncfusion.XlsIO.Implementation.Tables.ListObjectColumn.SetName(String value) 
   at Syncfusion.XlsIO.Implementation.Tables.ListObject.UpdateColumnNames(List`1 columnNames) 
   at Syncfusion.XlsIO.Implementation.Tables.ListObject..ctor(String name, IRange location, Int32 index) 
   at Syncfusion.XlsIO.Implementation.Tables.ListObjectCollection.Create(String name, IRange range) 
   at ConsoleSample.Program.Main(String[] args) in D:\XlsIO - Incidents\F153527\ConsoleSample\ConsoleSample\Program.cs:line 64 

Kindly confirm us, if this is the exception stack trace of the issue you are facing at your end, which will be helpful for us in investigating the issue. 

Regards, 
Keerthi. 


TC Travis Chambers April 23, 2020 01:17 PM UTC

Hi Keerthi,

Thanks for looking into this for me. I can confirm that the stack trace I am getting is exactly the same as this one.

Thanks.

KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team April 24, 2020 11:47 AM UTC

Hi Travis, 

Thanks for the update. 

We will validate the issue and provide the validation details on 28th April 2020. 

Regards, 
Keerthi. 


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team April 28, 2020 04:28 PM UTC

Hi Travis, 

Thanks for the patience. 

We have validated the issue Argument exception is thrown on creating ListObjects in the Excel file that is already saved using XlsIO and logged a defect report. We will provide patch for this fix on 12th May 2020.  

Regards, 
Shamini 
 


TC Travis Chambers April 30, 2020 04:14 AM UTC

Thank you for the update. I look forward to the patch. In the meantime, do you know of any kind of work-around for the issue?

SK Shamini Kiruba Sobers Syncfusion Team April 30, 2020 01:20 PM UTC

Hi Travis, 

The issue occurs due to setting styles for columns after resaving in XlsIO. You can ignore the exception issue by removing the currency format setting for columns from your code. 

Regards, 
Shamini 


SK Shamini Kiruba Sobers Syncfusion Team May 12, 2020 02:33 PM UTC

Hi Travis, 

We appreciate your patience. 

We have fixed the issue Argument exception is thrown on creating ListObjects in the Excel file that is already saved using XlsIO and generated patch in the version v17.4.0.39. The patch with fix to resolve the reported issue can be downloaded from the following link. 

Recommended approach - exe will perform automatic configuration       
   
Please find the patch setup from below location:       
       
Advanced approach – use only if you have specific needs and can directly replace existing assemblies for your build environment       
   
Please find the patch assemblies alone from below location: 

     
Assembly Version: 17.4.0.39 

Installation Directions :        
This patch should replace the files “Syncfusion.XlsIO.Base.dll” under the following folder.       
$system drive:\ Files\Syncfusion\Essential Studio\$Version # \precompiledassemblies\$Version#\4.6        
Eg : $system drive:\Program Files\Syncfusion\Essential Studio\17.4.0.39\precompiledassemblies\17.4.0.39\4.6 
       
To automatically run the Assembly Manager, please check the Run assembly manager checkbox option while installing the patch. If this option is unchecked, the patch will replace the assemblies in precompiled assemblies’ folder only. Then, you will have to manually copy and paste them to the preferred location or you will have to run the Syncfusion Assembly Manager application (available from the Syncfusion Dashboard, installed as a shortcut in the Application menu) to re-install assemblies.       
       
Note :        
You can change how you receive bug fixes by navigating to the following link and updating your preferences.        
    
Disclaimer :   
Please note that we have created this patch for version 17.4.0.39 specifically to resolve the issues reported in forum 153527.      
              
If you have received other patches for the same version for other products, please apply all patches in the order received.       
      
This fix will be included in our release version 18.2 which will be rolled out by end of June 2020.  

Regards,     
Shamini 


Loader.
Up arrow icon