How to import data from datatable

Aspose.Cells Product Family
1

Hi There,
we are using aspose.cells for .net.

My requirement is like I need to import data from datatable into an excel or csv. but the problem is if the datatable contains more that 1million records its importing only around 1million records not more than that.
Can you help me on this.

Regards,
Srikanth Gaddala.

2

Hi Srikanth,


Thank you for contacting Aspose support.

I have used the following piece of code against the latest version of Aspose.Cells for .NET (Latest Version) to import 1 million and 1 hundred rows of data without any issue. In case you are using any older revision of the API, please give the latest version a try on your side. Moreover, please confirm that you are setting the license before invoking an instance of Workbook in order to rule out the possibility of evaluation restriction.

C#

var table = new DataTable(“Sales”);
//Add columns to the newly created DataTable while specifying the column type
table.Columns.Add(“Year”, typeof(string));
table.Columns.Add(“Item1”, typeof(int));
table.Columns.Add(“Item2”, typeof(int));
table.Columns.Add(“Item3”, typeof(int));
table.Columns.Add(“Item4”, typeof(int));
table.Columns.Add(“Item5”, typeof(int));
table.Columns.Add(“Item6”, typeof(int));
table.Columns.Add(“Item7”, typeof(int));
table.Columns.Add(“Item8”, typeof(int));
table.Columns.Add(“Item9”, typeof(int));
table.Columns.Add(“Item10”, typeof(int));
table.Columns.Add(“Item11”, typeof(int));
table.Columns.Add(“Item12”, typeof(int));

for (int i = 0; i < 1000100; i++)
{
//Add some rows with data to the DataTable
table.Rows.Add(“2000”, 2310, 0, 110, 15, 20, 25, 30, 1222, 200, 421, 210, 133);
}

//Create an instance of Workbook
var book = new Workbook();
//Access the first, default Worksheet by passing its index
var dataSheet = book.Worksheets[0];
//Access the CellsCollection of first Worksheet
var cells = dataSheet.Cells;
//Import data
cells.ImportDataTable(table, true, “A1”);
book.Save(dir + “output.xlsx”, SaveFormat.Xlsx);

3

Hi Thanks for the quick reply…

could you please change 1000100 to 1100000

and try to run the code.

and open the saved excel u can see only 1048634 rows only…



4

Hi Srikanth,


Please note, this is the limitation of XLSX file format (as well as recent spreadsheet formats) that it can hold only 1048576 rows in a single worksheet so if you have more data rows than the specified limit, Aspose.Cells APIs will not complain about it however only allowed number of rows will be accessible when result is saved on disc and viewed in Excel application. Moreover, the aforementioned limitation is applicable for CSV format as well because Excel application cannot load more than 1048576 rows from CSV.