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.

Fri Aug 23, 2019 1:50 pm

A workbook has two sheets , sheet1 is loaded from a *.xlsx file, with more than 200 columns, sheet2 is blank. I copy a column from sheet1 to sheet2 as index column, and vlookup the columns in sheet1, each time with one column. Before vlookup a new column, the older formula is deleted. I met two problems.

Firstly, if columns number in sheet1 > 256, the workbook.CalculateAllValue() will result in an outofrange error.
Secondly, after some column vlookup operatoin, the calculation get slower and slower. It seems like the former formula is not deleted and is also calculated.

How to solve these problem? May the CalculateAllValue() be applied to a particular column instead of the whole workbook?

DanaldLu
 
Posts: 5
Joined: Sat Aug 03, 2019 5:03 am

Mon Aug 26, 2019 7:54 am

Hi,

Thanks for your inquiry and sorry to reply late for weekend.
1. To help us investigate your issue accurately, please offer us your input Excel file and the complete code. You could upload them here or send us(support@e-iceblue.com) via email.
2. The method “CalculateAllValue()” is used to calculate all the formulas for the workbook, so it couldn’t be applied to a particular column.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Mon Sep 30, 2019 4:24 am

The data in the excel is none special. the only reason is column number exceeds 256.

DanaldLu
 
Posts: 5
Joined: Sat Aug 03, 2019 5:03 am

Mon Sep 30, 2019 10:15 am

Hi

Thanks for your reply.
I tested a sample Excel file with more than 256 columns using Spire.XLS Pack(Hotfix) Version:9.9.13 but didn't reproduce your issue. There won't be an "out of range" error when the program call the "CalculateAllValue()" method. I suggest you try to use the latest version of Spire.Xls and try again.
If the issue still exists, I am afraid we need your input Excel file and complete code to help us reproduce your issue on our side. Please upload it here or send us(support@e-iceblue.com) via email.
We promise to keep your file confidential and you could remove the sensitive information as long as your issue could still be reproduced.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Return to Spire.XLS

cron