Aspose.Cells - Named Ranges

Aspose.Total Product Family
1

Hi,

When we convert Excel file into SpreadsheetML, If Excel file Named range, Excel Automation and Aspose.Cells gives different results for ss:RefersTo attribute value.

We are processing spreasheet xml file with RefersTO attribute and our current system fails.

I have attached sample excel file for your reference.

Excel Automation output:

<NamedRange ss:Name=“Print_Area”
ss:RefersTo="=table1[[#Headers],[#Data],[Personnel Number]:[ODI Amount]]"/>

Aspose.Cells Output:
ss:Names
<ss:NamedRange ss:Name=“Print_Area” ss:RefersTo="=#REF!" />
<ss:NamedRange ss:Name=“Print_Titles” ss:RefersTo="=‘32018 lt pay register’!R1" />
</ss:Names>

Code to generate spreadsheetml file is as below.

mXMLSpreadsheetFile += “Test.ss.xml”Sample2.zip (31.0 KB)

Me.XLSFile = “O:\Prerak\sample2.xlsx"
mXMLSpreadsheetFile = GetValidExcelPathName(mXMLSpreadsheetFile)
Dim FileFormatInfo As FileFormatInfo = Aspose.Cells.FileFormatUtil.DetectFileFormat(mXLSFile)
If FileFormatInfo.FileFormatType = FileFormatType.Excel95 Then
ConvertXLStoXML_Automation(targetPath)
Else
If FileFormatInfo.LoadFormat = LoadFormat.Unknown AndAlso FileFormatInfo.FileFormatType = FileFormatType.Unknown Then
Using fileReader As StreamReader = New StreamReader(mXLSFile)

                    Dim line() As String = fileReader.ReadLine().Split(CChar(vbTab))
                    If line.Length > 1 Then
                        loadOptions = New Aspose.Cells.LoadOptions(LoadFormat.TabDelimited)
                    Else
                        loadOptions = New Aspose.Cells.LoadOptions(LoadFormat.CSV)
                    End If
                End Using
            Else
                loadOptions = New LoadOptions(FileFormatInfo.LoadFormat)
            End If
            'workBook = New Aspose.Cells.Workbook(Me.XLSFile)
            workBook = New Aspose.Cells.Workbook(Me.XLSFile, loadOptions)
            workBook.Settings.CalcMode = Aspose.Cells.CalcModeType.Manual
            If workBook.CustomDocumentProperties IsNot Nothing AndAlso workBook.CustomDocumentProperties.Count > 0 Then
                'not properly handled by aspose so just get rid of them when create Spreadsheet XML
                workBook.CustomDocumentProperties.Clear()
            End If
            'Get rid of all external links since they don't get converted properly to Spreadsheet XML format
            workBook.RemoveExternalLinks()
            For Each worksheet As Aspose.Cells.Worksheet In workBook.Worksheets
                worksheet.RemoveAutoFilter()
            Next
            'Save spreadsheet as XML file using automation
            Dim saveOptions As SpreadsheetML2003SaveOptions = New SpreadsheetML2003SaveOptions()
            saveOptions.LimitAsXls = False
            workBook.Save(mXMLSpreadsheetFile, SaveFormat.SpreadsheetML)
        End If
    Catch ex As CellsException
        MsgBox(ex.Message)
    Finally
    End Try

Thanks,
Prerak Shah

2

@PrerakShah,

Thanks for your query.

We are working on this issue and will soon provide our feedback.

3

@PrerakShah,

Thanks for the template file, sample code and details.

After an initial test, I am able to observe the issue by using the following sample code with your template file as you mentioned. I found Aspose.Cells gives different results for named range’s “ss:RefersTo” attribute value in the converted SpreadsheetML file from an XLSX file:

Excel Automation output:
<NamedRange ss:Name=“Print_Area”
ss:RefersTo="=table1[[#Headers],[#Data],[Personnel Number]:[ODI Amount]]"/>

Aspose.Cells Output:
ss:Names
<ss:NamedRange ss:Name=“Print_Area” ss:RefersTo="=#REF!" />
<ss:NamedRange ss:Name=“Print_Titles” ss:RefersTo="=‘32018 lt pay register’!R1" />
</ss:Names>

e.g
Sample code:

Dim wb as Workbook = New Workbook("e:\\test2\\sample2.xlsx") 
wb.Save("e:\\test2\\out1.xml", SaveFormat.SpreadsheetML)

I have logged a ticket with an id “CELLSNET-46309” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

4

Hi Amjad,

I have notice that attribute is also missing from Aspose.Cells output. this Named Cell is inside in excel automation output.

5

@PrerakShah,

Which attribute you are talking about? Could you provide more details, screenshots or output contents, we will check it soon.

6

@PrerakShah,

We have evaluated your issue further.

  1. Aspose.Cells gives different results for named range’s “ss:RefersTo” attribute value - SpreadsheetML file does not support Table. Ms Excel Automation exported the RefersTo as you excepted, but you will get an error when opening xml in MS Excel. So we have no plan to fix this issue.

  2. This Named Cell is inside in excel automation output - The property is useless. And, we have to traversal all named range for each cells if we exported this property. For performance grounds, we have no plan to export it

We are sorry for any inconvenience caused!