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.

Wed Aug 04, 2021 10:48 am

Hi,

I getting error while trying to access the cell (row > 65535).
I have tried both code below:
Code: Select all
using (Workbook wBook = new Workbook())
using (Worksheet wSheet = wBook.Worksheets.Add("S1"))
{
// Method 1
wSheet[1, 100000].Value = "ABC";

// Method 2
wSheet["A100000"].Value = "ABC";
}

Both getting the same error (System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.).

alpacaheng
 
Posts: 2
Joined: Tue Jan 15, 2019 4:09 am

Thu Aug 05, 2021 6:26 am

Hello,

Thanks for your inquiry.
Kindly note that when using our Spire.XLS to create a new instance of the Workbook object, the default version is Excel2003, which is limited to 65536 rows. If you need to set more rows of data, please specific a higher version like the following sample code.
Besides, the code "wSheet[1, 100000]" addresses to (first row, 100000th column), not (100000th row, first column). And please note that the maximum number of columns in an excel file is 16384.
Code: Select all
            using (Workbook wBook = new Workbook())
            {
                wBook.Version = ExcelVersion.Version2016;
                using (Worksheet wSheet = wBook.Worksheets.Add("S1"))
                {
                    //wSheet[1, 100000].Value = "ABC";    // To set value of the cell (First row, 100000th column). The maximum number of columns is 16384, and this cannot be accessed.
                    //wSheet[1, 16384].Value = "ABC";    // To set value of the cell (First row, 16384th column). This is OK.

                    wSheet["A100000"].Value = "ABC";    // To set value of the cell (100000th row, First column). This is OK.
                    wSheet[100000,1].Value = "ABC";    // To set value of the cell (100000th row, First column). This is OK.
                }
            }


Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Mon Aug 09, 2021 9:04 am

Hello,

Greetings from E-iceblue!
How is your issue now? Could you please give us some feedback at your convenience?

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Wed Aug 11, 2021 2:24 am

Yes, with the code you provided, I can update the row that I want.
But there is another issue, I try to create an excel and insert 525600 row into the sheet, the memory (physical memory) consumption is very high (800 MB - 1 GB memory for every ~45k row).

Code: Select all
using (Workbook wBook = new Workbook())
{
    wBook.Version = ExcelVersion.Version2016;
    wBook.Worksheets.Clear();

   using (Worksheet wSheet = wBook.Worksheets.Add("DATA"))
   {
      for (int i = 0; i < 525600; i++)
      {
         wSheet[i + 1, 1].Value = "10";
         wSheet[i + 1, 2].Value = "0";
         wSheet[i + 1, 3].Value = "50.123456";
         wSheet[i + 1, 4].Value = "10737418240";
         wSheet[i + 1, 5].Value = "10737418240";
         wSheet[i + 1, 6].Value = "507374182400";
         wSheet[i + 1, 7].Value = "507374182400";
         wSheet[i + 1, 8].Value = "5";
         wSheet[i + 1, 9].Value = "5";
         wSheet[i + 1, 10].Value = "5";
         wSheet[i + 1, 11].Value = "5";
         wSheet[i + 1, 12].Value = "5";
         wSheet[i + 1, 13].Value = "5";
         wSheet[i + 1, 14].Value = "5";
         wSheet[i + 1, 15].Value = "5";
         wSheet[i + 1, 16].Value = "5";
         wSheet[i + 1, 17].Value = "5";
         wSheet[i + 1, 18].Value = "5";
         wSheet[i + 1, 19].Value = "5";
         wSheet[i + 1, 20].Value = "5";
         wSheet[i + 1, 21].Value = "5";
         wSheet[i + 1, 22].Value = "5";
         wSheet[i + 1, 23].Value = "5";
      }
    }
    wBook.SaveToFile($"SAMPLE.xlsx");
}

alpacaheng
 
Posts: 2
Joined: Tue Jan 15, 2019 4:09 am

Wed Aug 11, 2021 9:58 am

Hello,

Thanks for your inquiry.
I tested your case and did find that it consumes a lot of memory. I have posted this issue to our Dev team and they will further investigate whether this can be improved. If there is any update, we will let you know.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS