Handling > 65-536 Rows

Aspose.Cells Product Family
1

I am currently evaluating various tools that would allow us to create Excel spreadsheets from within our ASP.NET application.

I’m trying to determine how I might handle a situation where I want to use ImportDataTable (or ImportFromDataReader) to import a DataTable (or SqlDataReader) where the source contains > 65,536 rows (the maximum that can be put into a worksheet).

I’ve created a test case and am trying to use the following form of the method:

public int ImportDataTable(
DataTable dataTable,
bool isFieldNameShown,
int firstRow,
byte firstColumn,
int maxRows,
int maxColumns
);

However, the following lines of code is giving me an ArgumentOutOfRangeException, but unfortunately isn’t telling me which argument is the problem.

int j = cells.ImportDataTable( dt, true, 1, 1, 10, 10 );

Any suggestions on the specific problem I’m having, and the more general question of handling DataTables with > 65,536 rows?

2

This exception is caused by the rows of data in your DataTable exceeding 65536. I will shape the ImportDataTable method when meeting DataTables with > 65536 rows.

3

@craig_wagner,
Aspose.Cells has replaced Aspose.Excel that is no more under active development now. Aspose.Cells contains all the features of its predecessor along with the support for the latest advanced features in different versions of MS Excel. You can now import more than 65536 rows into a workbook according to the row limits in the latest Excel file versions. The following sample code demonstrates this feature:

// Create a new DataTable.    
DataTable custTable = new DataTable("Customers");
DataColumn dtColumn;
DataRow myDataRow;

// Create id column  
dtColumn = new DataColumn();
dtColumn.DataType = typeof(Int32);
dtColumn.ColumnName = "id";
dtColumn.Caption = "Cust ID";
dtColumn.ReadOnly = false;
dtColumn.Unique = true;
// Add column to the DataColumnCollection.  
custTable.Columns.Add(dtColumn);

// Create Name column.    
dtColumn = new DataColumn();
dtColumn.DataType = typeof(String);
dtColumn.ColumnName = "Name";
dtColumn.Caption = "Cust Name";
dtColumn.AutoIncrement = false;
dtColumn.ReadOnly = false;
dtColumn.Unique = false;
/// Add column to the DataColumnCollection.  
custTable.Columns.Add(dtColumn);

// Create Address column.    
dtColumn = new DataColumn();
dtColumn.DataType = typeof(String);
dtColumn.ColumnName = "Address";
dtColumn.Caption = "Address";
dtColumn.ReadOnly = false;
dtColumn.Unique = false;
// Add column to the DataColumnCollection.    
custTable.Columns.Add(dtColumn);

          
// Add data rows to the custTable using NewRow method    
// I add three customers with their addresses, names and ids
for(int i = 0; i< 100000;i++)
{
    myDataRow = custTable.NewRow();
    myDataRow["id"] = 1000 + i;
    myDataRow["Address"] = $"43 Lanewood Road, cito, CA-{i+1}";
    myDataRow["Name"] = $"George Bishop-{i+1}";
    custTable.Rows.Add(myDataRow);
}
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
ImportTableOptions importTableOptions = new ImportTableOptions();
worksheet.Cells.ImportData(custTable, 0, 0, importTableOptions);
workbook.Save("output.xlsx");

For more information on importing data into a workbook, follow the link below:
Import Data into Worksheet

The latest version of this new product can be downloaded here:
Aspose.Cells for .NET(Latest version)

Here is a runnable complete solution that contains a lot of examples to test different features of Aspose.Cells.