ImportData with empty datatable produces Index Error on save

[weissa]

Hello,

Importing a DataTable that has zero rows will produce an Index Error on workbook.Save(). We’re working around by checking and avoiding the ImportData call if the DataTable is empty. Thought you should know.

        sheet.Cells.ImportData(dataTable, rng.FirstRow, rng.FirstColumn, new ImportTableOptions
        {
            IsFieldNameShown = showHeaders,
            InsertRows = false,
            ConvertNumericData = true,
        });

Thanks!
-Andy

[amjad.sahi]

@weissa,

Which version of the APIs you are using? I did test your scenario/case using our latest version/fix: Aspose.Cells for .NET v22.7 (Download | NuGet) and it works fine. I am using the following sample code. Also, please find attached the output file for your reference:
e.g.
Sample code:

            DataTable dt = new DataTable("MyTable");
            dt.Columns.Add("Col_ID", typeof(Int32));
            dt.Columns.Add("Col_no", typeof(Int32));
            dt.Columns.Add("Col_Formula1", typeof(String));
            dt.Columns.Add("Col_Formula2", typeof(String));

            //Create workbook object
            Workbook wb = new Workbook();

            //Access first worksheet
            Worksheet ws = wb.Worksheets[0];

            //Specify import table options
            ImportTableOptions opts = new ImportTableOptions();
            opts.InsertRows = false;
            opts.IsFieldNameShown = true;
            opts.ConvertNumericData = true;

            //Import custom objects
            ws.Cells.ImportData(dt, 0, 0, opts);

            //Save the output Excel file
            wb.Save("e:\\test2\\out1.xlsx");

files1.zip (6.0 KB)

[weissa]

@Amjad_Sahi,

Sorry I was wrong on what was causing the error. We have code that imports a datatable into a list object and lines up matching columns between the datatable and the list object. If the datatable was empty then our code would attempt to insert -1 rows using the InsertRows method below. After inserting the rows into the listobject we have to re-apply listObject column formulas because this doesn’t happen automatically using InsertRows. I’ve found that when applying both of these steps will result in the Index error on save. If I skip either the insert rows or re-applying the column formulas then there is no problem saving.

The only trouble is I can’t reproduce this with just the file and a test case. It only happens when we run our entire process and I haven’t been able to figure out the missing piece. I would say let it be for now since it was an edge condition to begin with and we’ll write back if we hone in on it further.

As always thank you for your support.

listObject.DataRange.Worksheet.Cells.InsertRows(listObject.StartRow + 1, numRows);
foreach (var column in listObject.ListColumns)
{
    if (!string.IsNullOrEmpty(column.Formula))
    {
        column.Formula = column.Formula;
    }
}

-Andy

[amjad.sahi]

@weissa,

@weissa

If the datatable was empty then our code would attempt to insert -1 rows using the InsertRows method below. After inserting the rows into the listobject we have to re-apply listObject column formulas because this doesn’t happen automatically using InsertRows. I’ve found that when applying both of these steps will result in the Index error on save

To avoid the exception, I guess you may simply evaluate if the underlying datatable is empty or not then decide which step to do and which to skip and write code accordingly. For example, see the line of code to evaluate if the datatable is empty or not:
e.g.
Sample code:

......
int recordsCount = dt.Rows.Count;
//check if datatable is empty or not.
if(recordsCount>0)
{
  //........
  //your code goes here
  //....
}
......

@weissa

I would say let it be for now since it was an edge condition to begin with and we’ll write back if we hone in on it further

Sure, please take your time to evaluate it and share the sample to reproduce the issue. And, in the mean time you may workaround the issue accordingly.