Named Range interpreted as Function

tlb · June 12, 2006, 5:22am

When copying a sheet from an excel file to an excel object in memory, the named ranges in the formula's get interpreted as functions causing opening en closing brackets to be added: r100 becomes r100().

The code:

Aspose.Cells.Workbook newWorkbook = new Aspose.Cells.Workbook();
newWorkbook.Open(@"C:\new.xls");
Aspose.Cells.Workbook master = new Aspose.Cells.Workbook();
master.Open(@"C:\old.xls");
newWorkbook.Worksheets["Sheet1"].Copy(master.Worksheets["Sheet1"]);
newWorkbook.Save(@"C:\new.xls", FileFormatType.Excel2003);

Laurence · June 12, 2006, 7:19pm

Could you post your files here? “r100” is not a valid name when I try to create it in MS Excel.

tlb · June 13, 2006, 5:48am

The problem only occurs with sheet-specific named ranges i.e. a named range that is only available for a specific sheet. You can add such a named range by choosing Insert>Name>Define in Excel and then typing 'Sheet1'!r_100 in the name text box (this will add the named range r_100 to the sheet with name Sheet1) or you can do this programatically.

When a sheet containing such a local named range is copied to a new xls file by using Aspose, the sheet specific named range that is used in formula's is referenced as a formula i.e. Aspose.Cells adds '()' to the name (i.e. r_400 becomes r_400()).

See files: Sheet1 of file TestAspose.xls is copied into sheet1 of TestAspose_result.xls. The problem occurs for named range r_400.

Laurence · June 13, 2006, 9:58pm

Please try this attached fix.

tlb · June 14, 2006, 12:20am

Thanks! The support your company offers is great!

Will this fix be integrated in following releases?

Laurence · June 14, 2006, 12:55am

Yes. In the next public release it will be included.

tlb · August 30, 2006, 2:34am

Still an issue when referring to a sheet-specific named range from another sheet:

If we have a sheet [Sheet 1] with sheet specific named ranges and we now want to programatically add formulas referencing these named ranges, we get following problem:

Code:

wb.Open(@"C:\TestAspose.xls");

wb.Worksheets["Sheet 2"].Cells["A3"].PutValue(2);

wb.Worksheets["Sheet 2"].Cells["B3"].PutValue(2);

wb.Worksheets["Sheet 2"].Cells["C3"].Formula = "=('Sheet 1'!NamedRangeRow2 'Sheet 1'!NamedRangeCol2)";

wb.Save(@"C:\TestAsposeResult.xls");

The cell C3 contains the (wrong) formula:

=(''SHEET 1'!NAMEDRANGEROW2'SHEET 1'!NAMEDRANGECOL2'())

instead of

=('Sheet 1'!NamedRangeRow2 'Sheet 1'!NamedRangeCol2)

Four parse errors: two single quotes ['] at the start, no space between the two named ranges, a single quote after NAMEDRANGECOL2 and () added at the end.

I am using Aspose.Cells 4.0.2

amjad.sahi · August 30, 2006, 7:05am

Hi,

Thanks for considering Aspose.

Laurence will fix this issue. He is sick and hopefully will be back tomorrow.

Regards

Amjad Sahi

Aspose Nanjing Team

Laurence · August 31, 2006, 12:18am

Aspose.Cells doesn't support to use a single space as intersection operator yet.

I will check and add this feature in the future version.

tlb · August 31, 2006, 2:40am

How long will it take to add this feature?

We do not actually need Aspose to be able to calculate the formula.

But Aspose should leave the Formula as it is and not try and parse it and change it into a function.

Adding an additional option indicating not to change the formula would already be enough. Excel will deal with the formula correctly when the user opens the excel file.

We really need this functionality as soon as possible.

Kind regards,

TLB

Laurence · September 1, 2006, 2:08am

When setting the formulas at run time, we have to parse the formula to write it into the output file.

I will add this feature in the next week.

Laurence · September 4, 2006, 6:38pm

Please try this attached fix.

tlb · September 5, 2006, 8:09am

The excel crashes when using the new dll... (See attached file)

We also found out that setting a formula in Aspose is taken rather a lot of time compared to just setting the value:

dataWs.Cells[row, col].Formula = rule[col];

dataWs.Cells[row, col].PutValue(rule[col]);

On the other hand we can not set the formula by using PutValue, since then excel will see the formula as text ...

Is there a way to set formula's, without Aspose parsing them? We do not need Aspose to (re-)calculate or modify any of the formula's...

Thanks!

TLB

Laurence · September 5, 2006, 9:24pm

How do you create this file? Could you please post your project here?

When setting the formula, Aspose.Cells has to parse the formula to tokens. It takes much more time than setting the value. It's impossible to set formulas without parsing them. Formulas are saved as tokens in the Excel files, not the formula string.

blcpete · November 25, 2008, 10:45am

I was wondering how you guys got around this issue.

I am having difficulty inserting a formula into a named range using named ranges

ex) TestRange.formula = "=sum(Data.DataStruct1.2002,Data.DataStruct3.2006)"

in excel it shows up as =sum(Data.DataStruct1.2002(),Data.DataStruct3.2006())

i am not sure why the parenthesis are being added.

Thanks for the help

blcpete · November 25, 2008, 11:36am

I downloaded aspose.cells version 4.5.1.19 and it was able to resolve my parenthesis issue.

However, I am still having a problem where the formula does not want to calculate properly until i go into the formula bar, click with my mouse and press enter. Once I do that the formula calculates correctly.

It seems like its acting as if i put the formula in as a text value. However I did use the formula field of the named range ex) testrange.formula

Thanks for the help

nausherwan.aslam · November 25, 2008, 2:08pm

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, Assigning the formula to the range cells is working fine, like

range[3, 4].Formula = "=SUM(DATA.DATASTRUCT3.2005,DATA.DATASTRUCT3.2006)";

Also the cells in your template file containing the DataStruck2 are not working and are giving errors in Excel as well.

Can you please share the code and explain the scenario which is causing this problem?

Thank you & Best Regards,

blcpete · November 25, 2008, 2:31pm

this is my code for testing aspose abilities. just to get data into the cells

Private Sub Named_Ranges(ByVal sheet As Worksheet, ByVal dt As DataTable, ByVal o As Integer)
Dim r As Integer, c As Integer, rangeHolder As Range
r = 0
For Each RowItem In dt.Rows
c = 0
For Each ColItem In dt.Columns
rangeHolder = sheet.Cells.CreateRange(sheet.Cells(r + 1, c + 1).Name, sheet.Cells(r + 1, c + 1).Name)
rangeHolder.Name = sheet.Name & "." & dt.Rows(r).Item(0) & "." & sheet.Cells(0, c + 1).Value
If o = 1 Then
rangeHolder(0, 0).Formula = testFormulas(r)
End If
c = c + 1
Next
r = r + 1
Next
End Sub

when i open the excel file all of the cells have an error of #NAME?
when i click in the formula bar at the top of excel for that cell and press enter the formula calculates correctly
even for the datastruct2.

I am trying to create the 3 sheets. the first two sheets containing formulas and the third sheet containing data.
The formulas will use named ranges to refer to cells instead of A3,A4, and so forth. Which is why im going through all of the cells and creating named ranges

i have attached the code i was using. nothing special, hard coding a bunch of the data so i can test aspose.cells

thanks

amjad.sahi · November 26, 2008, 12:18am

Hi,

Thanks for providing us further details with sample code.

We found the issue and will figure it out soon.

Thank you.

amjad.sahi · November 27, 2008, 12:52am

Hi,

Please try this fix, it will solve your issue. Actually in the older versions, if you set a formula (which contains a Name) to the cell with the Name undefined as well, you will get this issue.

Thank you.