Circular Reference Errors

Aspose.Cells Product Family
1

Laurence,

I’m generating multiple copies of a single worksheet for each hierarchical XML parent row. The first worksheet comes out perfectly, but each subsequent worksheet contains circular reference errors from invalid formulas. You’ll see on the 5th worksheet that row 23 mistakenly contains information that should only appear in row 27.

I’ve attached the project.

Thanks,
Natan

2

Hi Natan,

Please comment out this line of code in subCopyPatternBlock method.

'exlCells.CopyRow(exlCells, exlRow.Index, nRowIndex)

This line of code copies formulas and introduces this problem.

3

Laurence,

My program depends on that line of code to copy styles, formatting and formulas from one row to another row. Commenting out that line of code will change the behavior of the entire program. Why would the first worksheet come out ok but others not? Is there something I’m missing?

Thanks,
Natan

4

Hi Natan,

I found the problem: CopyRow method doesn't shift formulas while copying them. Please try this attached fix.

However, I think you still need to change some of your code for there are some invalid formulas after copying.

5

Laurence,

This hotfix removes the circular reference errors, but the original problem still persists. Notice that on worksheet PVA(2), rows 38 and 39 also appear on 31 and 32. This error occurs for all worksheets except for the first, and I’ve already confirmed that I’m inserting rows in the same places for all worksheets. It’s because of this that the formulas are producing errors.

Thanks,
Natan

6

Hi Natan,

I think this problem caused by that you copied unncessary rows. I moved the first block of data in your xml file to last and all work fine. So please check if you can avoid to copy redundant rows.

For security reason I don't post the changed xml file here. If you need, I can post it here.

You can verify it by modify the xml file by yourself.

7

Laurence,

The number of data rows definitely impacts the output, but changing or moving the data rows only creates the same problem in a different place. I moved the first block of data to the end, but now take a look at rows 17 and 18 on PVA(2). They mistakenly have the same style settings as rows 14 and 15. Now see that the same error occurs on all the worksheets except for the first worksheet.

Thanks,
Natan

8

Hi Natan,

Finally I found what caused your problem.

In PatternBlock class, you add Aspose.Cells.Row to alExcelRows. But the index of Row will be changed if you delete rows and insert rows. In the first worksheet, index is correct while you process data. However, index is changed after processing the first worksheet.

I posted the changed code and output file here.

9

Laurence,

You did it! I never would have found that.

Thanks,
Natan