Forums Home / Document Solutions / Document Solutions for Excel, .NET Edition
Posted by: yyoshimura on 29 June 2025, 11:22 pm EST
yyoshimura
Posted 29 June 2025, 11:22 pm EST
Hi,
We noticed that after V7.1, the performance of copying some formulas are 10 times slower that V7.0.x. We tried the latest version V8.x but result is the same. Please refer attachment for reproduction. It takes 106 seconds at our end with version V7.1.2 and 10 seconds with V7.0.5.
Please help to look at this issue.
Thank you
Regards
chirag.gupta
Posted 30 June 2025, 1:26 am EST
Hi,
Apologies for the inconvenience caused.
We can replicate the behavior you mentioned on our end. We are currently discussing this behavior with the concerned team and will get back to you as soon as we get any update from their end.
Internal Tracking ID: DOCXLS-12672
Kind Regards,
Chirag Gupta
chirag.gupta
Posted 30 June 2025, 7:15 am EST
Hi,
Thanks for your patience.
The performance issue with formula copying has been confirmed as a bug. This issue is scheduled to be resolved in the DsExcel release v8.1.5.
We will keep you informed once the fix is available.
Best regards,
Chirag Gupta
yyoshimura
Posted 30 June 2025, 9:41 pm EST
Thank you for the swift response. We are looking forward to the new release.
chirag.gupta
Posted 21 July 2025, 1:02 am EST
Hi,
After thorough analysis, we have found that the original method for copying formulas - GrapeCityPackageAccessor::CopyFormula is splitting large ranges into many small ones, which caused repeated internal updates and significantly slowed down the operation in versions after 7.1.1.
To address this issue, we recommend that you update the CopyFormula implementation as follows:
public bool CopyFormula(string sourceRangeAddress, string destinationRangeAddress)
{
var sourceRange = getRange(sourceRangeAddress);
if (sourceRange == null) return false;
if (sourceRange.IsSingleCell() && !sourceRange.HasFormula)
// 単一セルかつセルに数式がない場合は何もしない
return false;
var hasFormulaSourceRange = sourceRange.SpecialCells(SpecialCellType.Formulas);
if (hasFormulaSourceRange == null)
// 数式がない場合は何もしない
return false;
var destinationRange = getRange(destinationRangeAddress);
if (destinationRange == null) return false;
sourceRange.Copy(destinationRange, new PasteOption {PasteType = PasteType.Formulas});
return true;
}This approach ensures that the entire range is handled in a single operation, which should significantly improve the performance because the null checks are performed internally by the DsExcel API.
Furthermore, the ETA for the resolution of the core issue is DsExcel Version 9.0.0 now. In the meantime, we recommend applying the code update above to improve the performance.
You can further refer to the attached sample that uses the above code snippet and improves the performance in the latest versions (see below).
Please let us know if you require any additional assistance.
Attachment: GcExcelPerformance.zip
Kind Regards,
Chirag Gupta
yyoshimura
Posted 22 July 2025, 10:03 pm EST
Hi,
Thank you for your response. It seems good and we’ll try the new code in our codebase.
chirag.gupta
Posted 22 July 2025, 10:46 pm EST
Hi,
Thank you for the update. Please let us know if you encounter any further issues or have any questions while integrating the new approach.
Kind Regards,
Chirag Gupta
yyoshimura
Posted 23 July 2025, 1:42 am EST
Hi,
We tried the new code and found that cells in sourceRange which do not contain formulas are also copied. We are expecting only the cells which contains formulas will be copied but it didn’t work like that. Is there any way to achieve this behavior?
Regards
chirag.gupta
Posted 23 July 2025, 11:57 pm EST - Updated 24 July 2025, 2:02 am EST
Hi,
Apologies for the inconvenience.
The root cause of the issue with the new approach is that the PasteType.Formulas option currently pastes both values and formulas (similar to Microsoft Excel’s behavior). Our developers are aware of this issue and are working on it. Unfortunately, there is no available workaround at this time.
We will update you as soon as we get any information from the developers.
Kind regards,
Chirag Gupta
chirag.gupta
Posted 28 July 2025, 8:03 am EST
Hi,
Thank you for your patience as we’ve investigated the issue further.
Based on our latest investigation and discussions with the concerned team, the best possible workaround is to set the formula in the destination range instead of copying the formulas. However, this applies only to the formulas that are equal in R1C1 mode; otherwise, it will not work. Please refer to the code below:
public bool CopyFormula(string sourceRangeAddress, string destinationRangeAddress)
{
var sourceRange = getRange(sourceRangeAddress);
if (sourceRange == null) return false;
if (sourceRange.IsSingleCell() && !sourceRange.HasFormula)
// 単一セルかつセルに数式がない場合は何もしない
return false;
var hasFormulaSourceRange = sourceRange.SpecialCells(SpecialCellType.Formulas);
if (hasFormulaSourceRange == null)
// 数式がない場合は何もしない
return false;
var destinationRange = getRange(destinationRangeAddress);
if (destinationRange == null) return false;
var destinationStartCell = destinationRange.Cells[0, 0];
// 単一セルの場合は.Areasで取ったレンジは別のセルの数式も返してしまうため、下記で明示的に判断を行う
IEnumerable<IRange> formulaSourceRanges =
sourceRange.IsSingleCell() ? new[] {sourceRange} : hasFormulaSourceRange.Areas;
foreach (var hasFormulaArea in formulaSourceRanges)
{
var rowOffset = hasFormulaArea.Row - sourceRange.Row;
var columnOffset = hasFormulaArea.Column - sourceRange.Column;
for (var colIndex = 0; colIndex < hasFormulaArea.ColumnCount; colIndex++)
{
var originalFormula = hasFormulaArea.Worksheet.Range[hasFormulaArea.Row, hasFormulaArea.Column + colIndex].Formula2;
var destinationArea =
destinationRange.Worksheet.Range[destinationStartCell.Row + rowOffset,
destinationStartCell.Column + columnOffset + colIndex,
hasFormulaArea.RowCount,
1];
destinationArea.Formula2 = originalFormula;
}
}
return true;
}Furthermore, as this issue can cause breaking changes in the product, the best possible ETA at this time is DsExcel Version 9.0.0.
Kind Regards,
Chirag Gupta
