Formulas Getting Messed Up When Using SmartMarkers

Aspose.Cells Product Family
1

Hi,
I'm testing out Aspose.Cells as a replacement for Officewriter; I already own Aspose.Words, and it is great. I've pretty much gotten Aspose.Cells to work with my reporting system, except for this one issue. At this point, if you can help me out with this, we'll definitely be making the purchase.

Issue:
DataTable is being used to populate a template excel sheet that has been converted from the Officewriter formatting (pretty simple global replace of "%%=" with "&="). Import works great, everything is as expected, except that the formulas which are "pushed" down occasionally work, and occasionally do not work. The odd thing is that it is always the same cells' formulas which do or do not work, and I can't see any discernable difference between them--and they all worked in Officewriter.

C# Code:
DataSet Data = this.GetData();//SP Call
WorkbookDesigner wbDesigner = new ac.WorkbookDesigner();
wbDesigner.Open( templatePath );
DataTable currentTable;
int currentTableIX = 2;
int maxTableIX = Data.Tables.Count;

while ( currentTableIX < maxTableIX )
{
currentTable = Data.Tables[currentTableIX];
currentTable.TableName = "D" + currentTableIX;
wbDesigner.SetDataSource( currentTable );
currentTableIX++;
}

wbDesigner.Process( false );
wbDesigner.Save( filePath );
Data.Dispose();

C# Code Notes:
-The first two datatables are used for something else not shown here.
-In this example, there are two datatables returned of note (D2 and D3 as named in the template).
-D2 has multiple rows, D3 only ever has one row.
-The functions overlap D2-based cells, but not the D3-based cell.
-I will attach both the template and the resultant output file (with financial data stripped).

Thanks in advance,
Chris


2

Okay, so I was only able to post the output file in the first post. Here’s the template.

3

Well, after spending some 6 hours on this, I finally found a workaround. There's still a bug that I hope you fix, but the attached template file demonstrates the workaround. You simply have to set the cell references for the start of the range to be static, and then the problem does not appear.

Note that I ran into this problem in more than one template file. I'll be updating the others with the workaround logic, and will report back if that does not fix the problem.

4

I tried this workaround on a vastly more complicated template that has 32 datatables being imported, along with a mix of counts and sums that are static versus stretched versus moved... with the workaround in place, this works great, whereas without it, it gives circular reference errors and crashes excel 2003. I think I'm all set with just the workaround, then, but I would still appreciate being notified when this issue is fixed.

Thanks,
Chris

5

Hi Chris,

Thanks for considering Aspose.

Thanks for your workaround too, We will sort out the issue and reponse you soon.

Regards

Amjad Sahi

Aspose Nanjing Team

6

Hello Chris,

Could you please create a sample project/solution with a template file and post here to show your problem.

We are also in the process of further enhancement of Smart Markers these days.

And are you using the latest Aspose.Cells (4.0.3)?

Thanks,

Amjad Sahi

Aspose Nanjing Team

7

Amjad,
Thanks for the response. I did post the template file (and the resultant output file) already. Unfortunately, the code snippet that I provided is the best that I can do as far as code goes at the present time; I've already sunk a lot of time into this problem, and now that I have a workaround my priority has to be finishing my own project on time. I hope you understand.

Also, I am indeed using version 4.0.3.

-Chris

8

Hi Chris,

Thank you for considering Aspose.Cells and providing us these information.

This is really a weird problem. Finally I figure it out. Please try this attached fix.

9

Laurence,

That seems to have done it. Thanks!

Chris