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.

Thu Jun 27, 2019 9:05 am

Hi.

I tried using Spire.Xls, to generate .xlsx from .xltx.
However, copying sheet containing a named cell seems to cause issue.

Found a issue in the following scenario.
used 'Spire.XLS Pack (Hotfix) Version: 9.6.7'.


Named 'value' cell are defined in the 'data' sheet and have global scope.
the 'ng' sheet in defined a named 'group' with multiple cells and has a scope for the sheet.
reference a named 'value' cell, from in range of named 'group'.

Copy the 'ng' sheet, using Spire.Xls.
Code: Select all
using (var workbook = new Workbook())
{
    workbook.LoadTemplateFromFile("./origin.xltx");

    workbook.Worksheets.AddCopy(workbook.Worksheets["ng"]);
    workbook.CalculateAllValue();

    workbook.SaveToFile("./issue.xlsx");
}

in the copied sheet('ng_1'), the formula referring to 'value' is incorrect.
named 'value' cell scoped to the copied sheet is added, and referenced.


Is there a way to work around this problem?
Sorry for ugly English.

Thanks.
Attachments
xlsFiles.zip
Files used for test
(16.78 KiB) Downloaded 158 times

nf64
 
Posts: 24
Joined: Fri Aug 17, 2018 1:45 am

Thu Jun 27, 2019 10:00 am

Hello,

Thanks for your inquiry.
Do you mean that you want the cell text of copied sheet ('ng_1') displays "VALUE1" to keep the same as original sheet ('ng')? If yes, please comment out "workbook.CalculateAllValue();" to achieve. If there is any misunderstanding, please share us with your desired effect of output, then we will further look into it. Thanks in advance.

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Thu Jun 27, 2019 10:42 am

Hi Lisa,

Thanks for your reply.
That method is not a fundamental solution.

I want to refer to the same named 'value' cell as the copy source even for copied sheet 'ng_1'.
The result should not be changed by CaluclateAllValue().

In the copied 'ng_1' sheet, named 'value' cells that were not in the copy source are newly added.
This can be view with Ctrl + F3 by MS Excel.

And 'ng_1' sheet refers to the newly added incorrect cell, Therefore, CaluclateAllValue() changes the result.
This is different from the behavior when copying 'ng' sheet in MS Excel.

nf64
 
Posts: 24
Joined: Fri Aug 17, 2018 1:45 am

Fri Jun 28, 2019 2:16 am

Hello,

Thanks for your more details.
While copying "ng" sheet, I did notice that the defined name “value” is not applied correctly to the formula when defining the 'group' with multiple cells and with a scope for the sheet. This issue has been submitted to our Dev team for further investigating and fixing. If there is any update, we will let you know. Sorry for the inconvenience caused.

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Thu Jul 11, 2019 11:32 am

Hello,

Thanks for your patient waiting.
Glad to tell you that the reported issue has been resolved in the Spire.XLS Pack Version:9.7, welcome to download it from the following links and test.
Website:https://www.e-iceblue.com/Download/download-excel-for-net-now.html
Nuget: https://www.nuget.org/packages/Spire.XLS/

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Thu Jul 11, 2019 11:51 am

Hi,

I confirmed that this problem was solved.
Thank you for prompt response.

nf64
 
Posts: 24
Joined: Fri Aug 17, 2018 1:45 am

Thu Jul 11, 2019 11:54 am

Hello,

Thanks for your quick reply. Please do not hesitate to contact us if you have other questions.
Wish you all the best!

Sincerely,
Lisa
E-iceblue support team
User avatar

Lisa.Li
 
Posts: 1261
Joined: Wed Apr 25, 2018 3:20 am

Return to Spire.XLS