R1C1RefersTo

Aspose.Cells Product Family
1

Hi

I have come across and issue where you cannot set a row value to a number above ~65500. When using the R1C1RefersTo property of a Name. If you use a value less than ~65000 all works.

If you try to following:

name[i].R1C1RefersTo = "=[WorkSheet]!R1C1:R66000C17"

You get the error "Invalid formula in cell A1: =DailyTrendingData!R1C1:R66000C17".

I am using Apose.Cells 5.2.0.0

Are there any work arounds?

Jeff Berardine

BNA Software

2

Hi,

Could you try the attached version.

I have tested with it using the following sample code and it works fine.
Workbook workbook = new Workbook();
Worksheet sheetRef = workbook.Worksheets[0];
Aspose.Cells.Cell startCell = sheetRef.Cells[0, 0];
NameCollection names = workbook.Worksheets.Names;
Name name = names[names.Add(“MyRange”)];
name.R1C1RefersTo = “=Sheet1!R1C1:R66000C17”;

workbook.Save(@“e:\test\out_test.xlsx”);

Thank you.

3

I have an existing excel file and and existing range. It does not work in this case. It is an excel 2007 file with xlxs file format.

4

Hi,

Kindly post your template Excel 2007 file, we will check your issue soon.

thank you.

5

I cant post the excel file. The contents are confidential.

It is simploe to recreaetd. Just

  • create a worksheet with 68000 rows
  • create a named ranged in excel that uses this data range, but set the rane to 32000

Use apose to update the existing range as I specified abve. There is definitly an issue. As I can update to values less than ~65000.

6

Hi,

I have a doubt that you are not using the latest version/fix v5.2.0.5 that I attached in my previous post. Please try it. I have tested your scenario with it using the following sample code, the output file is just fine with your desired updated range. I have also attached the input and output files here for your reference.

Sample code:

Workbook workbook = new Workbook(“e:\test\r1c1rng.xlsx”);
Worksheet sheetRef = workbook.Worksheets[0];
NameCollection names = workbook.Worksheets.Names;
Name name = names[“MyRange”];
name.R1C1RefersTo = “=Sheet1!R1C1:R66000C17”;

workbook.Save(@“e:\test\outr1c1rng_test.xlsx”);

If you still find any issue with the latest fix v5.2.0.5, kindly do create a simple dummy file and paste your code here. Also attach your output file. We will check it soon.

Thank you.

7

The last Apose.DLL you provided basically breaks updating names ranges. There are so many issues with using apose to update named ranges that I gave up and I used VBA code in excel.

The new DLL fixed the issue with the limit of ~65000 rows but some how corrupts the named ranges that contain dates; none of the names ranges with dates work correctly. Basically excel cannot use the date range or does not think it is a date; especially with using pivot tables. I reverted to the 5.1 DLL and confirmed it was an issue with the new DLL that was provided.

Also if you use the method RefersTo to set a named range it totally corrputs the named range collection.One of the many side effects of this property is that it adds a new item to the name collection and the excel file is corrupt.

8

Hi,

We appreciate if you could create a sample console demo application, zip it and post it here to reproduce the issues you mentioned. We will check it soon.

Thank you.

9

I do not have time right now to create a sample as it will be time consuming. I am going to go with my work around using Vba.

I will check the future updates to see if the issues are fixed. If I have time in the future I will create the sample. I hope the future updates will have better QA. The bugs are very obvious.

Just create a sample excel file that has lots of data > 65K rows and lots of columns > 50. Ensure the the data has a mix of dates, numbers and text. Also use custom formatting. The data in the each columns is of one type; i.e. only date. Then create a named rages for the data with less than the entire data set. Create a pivot table that uses this named range. Update the the named range to sue the entire data set using calls such as:

  • name[i].R1C1RefersTo
  • name[i].RefersTo (this call corrupts the names range colleciton by adding new names to the name collection)

Open the excel file and try to set a date filter on the pivot table or try to create a new pivot table with a date column. Notice that the pivot table behave differently. The issue is subtle but breaks some of the features relates to dates.

Both calls have issues that are subtle and require investigation but both seem to "corrupt" the names ranges.