Updating Name.RefersTo sometimes gives incorrect results in .NET

Aspose.Cells Product Family
2

Hi Mishelle,

Thanks for posting.

Please create some console application to further illustrate your problem and attach here. We will check it soon.

3

Here is my cs file with the test case.

Thanks,

Mishelle

4

Hi Mishelle,

I tested it with this code and I think, there is no such cell as JJ or IV. I used the cells J5 and K7 and it worked fine.


namedRange.RefersTo = string.Format("=’{0}’!${1}:${2}", sheet.Name, “J5”, “K7”);


5

JJ and IV are not cells, they are columns. In that example I was trying to set the range to a bunch of whole columns. This works as long as the columns are not past column 256 (IV). Please see attached for new examples. Case 1 sets the range to whole columns IA:IH, which works.

Case 2 attempts to set the range to a set of cells (not whole columns) past column 256. As you say, J5:K7 works, because these columns are before column 256 (IV). My example uses IA5:JH7, which doesn't work.

Is there a way I can update a Name to refer to columns past column 256 (IV)?

Mishelle

6

Hi,

Thanks for providing us the sample project.

I have checked your project. I think the reason why you are getting invalid results for the range refers to the columns past 256 (IV) is that by default, Aspose.Cells creates a workbook in XLS (Excel 2003) mode. Therefore you need to explicitly specify the MS Excel 2007 format (you already aware that in MS Excel 2003 (XLS), we can only have 256 columns(IV)).

Please change your line of code i.e.,
var workbook = new Workbook();
to:
var workbook = new Workbook(FileFormatType.Xlsx);

it will work fine as I tested.

Thank you.

7

Thank you, that works perfectly. When I use xlsx format, I am able to manipulate the extra columns.

However, I have a new, somewhat related problem. When I save my workbook in SpreadsheetML format, it is truncated at 256 columns. And the named ranges have

ss:RefersTo=" 'sheet1'!#REF!"

I'm saving in xml format so that I can load the results using Microsoft Office Web Component (which supports 17576 columns). Is there any way to get my SpreadsheetML output to contain the additional columns?

Mishelle

8

Hi,

Well, The SpreadsheetML (xml) format is Excel 2003 format, so it won’t have more than 256 columns.
It is not an issue rather a limitation put forth by MS Excel 2003. If you need to pick more than 256 columns, you should have MS Excel 2007 formats e.g XLSX, XLSM etc.

Thanks for your understanding!

9

The SpreadsheetML format is not limited to 256 columns. I can create an xlsx spreadsheet in Excel 2007 which goes beyond this limit, and save it as SpreadsheetML without any loss of data. But if I open that same xlsx file in Aspose, and use Aspose to save as SpreadsheetML, the results are not the same. This is illustrated in the attached spreadsheets (data starts in column IA.)

Surely this is not the expected behaviour?

Mishelle

10

Hi,

Thanks for the files.

Well, when you try to convert an xlsx file (that has more than IV columns/data) to SpreadsheetML 2003, it gives error warning(s)/message(s) and does change the file to spreadsheetml 2003 .xml file format (you may confirm it in MS Excel). When you open this file into MS Excel 2003, it gives error message regarding Workbook Settings and Table but eventually opens the file but it shows only up to IV columns/data. So we think when you convert the file from xlsx to spreadsheetml (2003 .xml file), some features/attributes would be lost regarding the xlsx file, but MS Excel 2007 could show the xml file in a sort of compatibility mode and hence you may see the extra columns (more than IV column) or data but only in MS Excel 2007. But MS Excel 2003 cannot open this file fine initially and could only show up to IV columns/data which he should as the original SpreadsheetML 2003 does not have the capacity to go beyond IV columns/data and that’s what the SpreadsheetML 2003 (.xml) file format is all about.

Anyways, we will still look into it if something can be done accordingly and provides your more details regarding it.

Thank you.

11

Hello,

Any news on this?

Thanks,

Mishelle

12

Hi,

We found Ms Excel 2007 would keep that settings for SpreadsheetML format. We have created a ticket (logged as CELLSNET-25224) for it. We are looking into it and need sometime to evaluate it thoroughly. Once we have any update about it, we will let you know here.

Thanks.

13

Hi,

Please try the Aspose.Cells for .NET v5.3.1.1.
Please try this fix with the following sample code:

Workbook workbook = new Workbook(@“D:\FileTemp\Book1.xlsx”);
SpreadsheetML2003SaveOptions saveOptions = new SpreadsheetML2003SaveOptions();
saveOptions.LimitAsXls = false;
workbook.Save(@“D:\FileTemp\dest.xml”,saveOptions);


But the maximum row and column is still in accordance with the limitation to XLS file.
We are still looking into this issue.

Thank you.

14

Hello,
We have since upgraded to version 7.0.1.3, and this is still a problem. The suggestion to use LimitAsXls = false does not work; named ranges are retained, but the column data is incorrect. In some cases this results in an invalid XML file. For examples, please see the attached files, created by the following code.

public void InsertColumnsOnBlankSheet_ColumnsAreTruncated()
{
var workbook = new Workbook(FileFormatType.Xlsx);
var sheet = workbook.Worksheets[0];

var range = sheet.Cells.CreateRange(“IA1”, “IB1”);
range.Name = “InsertColumnsHere”;
InsertColumns(range, 50);

//this creates a valid xml file, named range is correct but data stops at column IV
var saveOptions = new SpreadsheetML2003SaveOptions { LimitAsXls = false };
workbook.Save(string.Format("{0}SaveBlank.xml", TestHelper.OutputDirectory), saveOptions);

//for comparison
sheet.Workbook.Save(string.Format("{0}SaveBlank.xlsx", TestHelper.OutputDirectory), SaveFormat.Xlsx);
}

public void InsertColumnsOnNonBlankSheet_ColumnCountIsWrong()
{
var workbook = new Workbook(string.Format("{0}ManyColumns/NonBlank.xls", TestHelper.InputDirectory));
workbook.FileFormat = FileFormatType.Xlsx;
var sheet = workbook.Worksheets[0];

var range = sheet.Workbook.Worksheets.GetRangeByName(“InsertColumnsHere”);
InsertColumns(range, 50);

//this creates an invalid xml file: ExpandedColumnCount = 256 but 306 columns are defined
var saveOptions = new SpreadsheetML2003SaveOptions { LimitAsXls = false };
workbook.Save(string.Format("{0}SaveNonBlank.xml", TestHelper.OutputDirectory), saveOptions);

//for comparison
sheet.Workbook.Save(string.Format("{0}SaveNonBlank.xlsx", TestHelper.OutputDirectory), SaveFormat.Xlsx);
}

private static void InsertColumns(Range range, int columnsToInsert)
{
var sheet = range.Worksheet;
sheet.Cells.InsertColumns(range.FirstColumn + 1, columnsToInsert);
for (var column = 1; column <= columnsToInsert; column++)
{
sheet.Cells[5, range.FirstColumn + column].PutValue("column " + column);
}
}
15

Hi,


Thanks for the sample files and code segments.

I have initialized your existing issue with your new findings. We will look into it soon.

Thank you.
16

Hi,

If you do not want to limit spreadml as Excel 2003, please use SpreadsheetML2003SaveOptions.LimitAsXls = false

17

I believe I am using that setting, as you can see in the code I posted. It has partially solved the problem, in that the named ranges are preserved when they go beyond 256 columns, but the data is not. If you look at the results of the first test (SaveBlank.xml) you can see that it only contains 21 of the 50 inserted columns. The data stops at column IV, but the named range correctly goes to JZ.

The results of the second test (SaveNonBlank.xml) contains 306 columns (there seem to be styles that extend to 256 and these are pushed out by the 50 inserted columns), but it will not even open in Excel or OWC, because the ExpandedColumnRange attribute is incorrect. If I open in notepad and change that attribute to 306, the file opens and appears correct.

Please advise.
Mishelle

18

Hi,

Thanks for your feedback. I have added your comment. Once we will get any update, we will let you know asap.

19

Hi,

Please download the latest version: Aspose.Cells for .NET v7.0.1.5

Please try this fix. We have exported data beyond the limitation of xls file.

20

That seems to do the trick. Thanks!

21

The issues you have found earlier (filed as 25224) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.