I’m trying to test how long it takes Aspose.Excel to import a record set of this dimension: 464 rows and 41 columns ( 19024 records in total). But when I try to set the max number of rows and columns of the data table to be 1000, I get an “out of range” error. This is the line of code I used: Excel1.Worksheets[0].Cells.ImportDataTable(_dataTable, true, 0, 0,1000,1000, true); Even when I changed the numbers to be 100 columns and 500 rows, it doesn’t work. Did you set an upper limit for the number of rows and columns allowed for a datable import? We really need this method to be able to handle huge datasets. Can you please give me feedback ASAP on this issue? Thank you. Here’s the error I got:
Server Error in ‘/ExcelTest’ Application.
Specified argument was out of the range of valid values.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
Source Error:
Line 41: //Cell Cell1 = Excel1.Worksheets[0].Cells; Line 42: Line 43: Excel1.Worksheets[0].Cells.ImportDataTable(_dataTable, true, 0, 0,100,500, true); Line 44: NumofColumns=_dataTable.Columns.Count; Line 45: NumofRows=_dataTable.Rows.Count;
@Kristy,
Aspose.Excel is no more available now and is discarded. It is replaced by Aspose.Cells that contains all the features of its predecessor as well as supports the latest features of different versions of MS Excel. You can now import data table having rows up to 1048576 and columns up to 16384 without any error. Here is a sample code that can be used to import data table having large number of rows and columns.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
int columns = 16384;
int rows = 1048576;
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Instantiating a "Products" DataTable object
DataTable dataTable = new DataTable("Products");
// Adding columns to the DataTable object
for(int i = 0; i < columns; i++)
{
dataTable.Columns.Add($"Col{i}", typeof(Int32));
}
// Creating an empty row in the DataTable object
for(int r = 0; r < rows; r++)
{
DataRow dr = dataTable.NewRow();
for(int c = 0; c < columns; c++)
{
dr[c] = r;
}
dataTable.Rows.Add(dr);
Console.WriteLine(r);
}
// Importing the contents of DataTable to the worksheet starting from "A1" cell,
// Where true specifies that the column names of the DataTable would be added to
// The worksheet as a header row
worksheet.Cells.ImportData(dataTable, 0,0,new ImportTableOptions() { });
// Saving the Excel file
workbook.Save("DataImport.out.xlsx");