Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Sun Nov 21, 2021 6:48 pm

Hi,

We're using the Spire.XLS.dll with the license.
We're running a sql command to a dataset as per the example in the 'Import-Export Datatabvle to Excel from Database' sample.

but we're only getting a file back that hast 65,536 rows.

If I remove the license and try i get the same result but with the extra tab regarding it being a trial version.

Is the licensed version limited to 65k rows?

advirtus
 
Posts: 3
Joined: Mon Oct 07, 2019 2:38 pm

Mon Nov 22, 2021 3:49 am

Hello,

Thanks for your inquiry.
Kindly note that when creating a new instance of the Workbook object, the default version is Excel2003, which is limited to 65536 rows. To avoid this issue, please specific a higher version for workbook like the following code. If there is any question, please feel free to write back.
Code: Select all
//create a workbook
Workbook workbook = new Workbook();
//specify excel version
workbook. Version = ExcelVersion.Version2010;
Worksheet sheet = workbook.Worksheets[0];

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Tue Nov 23, 2021 4:27 pm

Thank you
That has worked. good to know.

We have one other issue now.

We're using the code below to extract direct from a dataTable.
But some items in column B are being formatted as dates once the file is saved down.

How can we prevent that from happening or keep the column with 'general' format?

Code: Select all
Workbook book = new Workbook();
book.Version = ExcelVersion.Version2013;
Worksheet sheet = book.Worksheets[0];
           
sheet.InsertDataTable(dt, true, 1, 1);

book.SaveToFile(path + "\\" + reportName + ".xlsx");

advirtus
 
Posts: 3
Joined: Mon Oct 07, 2019 2:38 pm

Wed Nov 24, 2021 3:01 am

Hello,

Thank you for your feedback.
Please use the following code to set the cell format of column B to text format, so as to avoid the data being formatted in date format.
Code: Select all
Workbook book = new Workbook();
book.Version = ExcelVersion.Version2013;
Worksheet sheet = book.Worksheets[0];

CellStyle style = book.Styles.Add("newStyle");
//format cells as text
style.NumberFormat = "@";
sheet.Columns[1].CellStyleName = style.Name;
         
sheet.InsertDataTable(dt, true, 1, 1);
book.SaveToFile(path + "\\" + reportName + ".xlsx");

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Fri Nov 26, 2021 6:43 am

Hello,

Hope you are doing well!
How is your issue going? Did the code we provided work for you? Any feedback will be greatly appreciated.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Return to Spire.XLS

cron