Hi,
I currently use smart markers to fill a grid with a calculated total at the end (with the SUM function). It work fine, but i would like to calculate (with a formula) the percentage in the last column. Then i tried :
&=&=B{r}/Total ("Total" is the total's cell's name because this cell is never the same)
But it had not worked. The cells' values are what i expected (as "=B2/Total", "=B3/Total", ...), the formulas are correct, but not calculated.
In the merge process, Aspose.Cells insert a new row for each source row. Could you add the feature which add source rows without inserting new rows (like the "bool insertRows" parameter of the ImportDataTable() method)
Finally, the total's cell's name point still point to my total's cell when i process the markers. But, when i Delete some rows above the grid, the total's cell is moved but the its name. The named range still point to the old cell and did not updated to the new.
Thank you for your work.
Tom.
Me again,
Is there some one who could reply ?
Thank you.
Tom.
Strange enough. I have answered your question but it seemed lost.
the formulas are correct, but not calculated.
Have you set formula calculation to automatic mode?
Tools->Options->Calculation
For not adding new rows, please try "noadd" parameter: Smart Markers.
Finally, the total's cell's name point still point to my total's cell when i process the markers. But, when i Delete some rows above the grid, the total's cell is moved but the its name. The named range still point to the old cell and did not updated to the new.
Which version are you using? Have you tried latest v4.1.2? Could you please give a simple sample for this problem? Thank you.
1) No problem, thank you for your answer
2) The formula calculation is in automatic mode. Like i said, the formulas are correct, but not calculated, they're just display in the cells. More over, when i edit a cell (F2 button) and validate it without any changes, the formula is well calculated.
3) You're right about the "noadd", i have forgot it. But, i use a SM dynamic formula in the same row :
&=&=IF(G1=0,0,B{r}/G1)
And without adding rows, the dynamic formula won't expand to the cells below. I were still curious and tried to place the "noadd" at several position, but no one was fine.
4) The named range is OK. The issue is corrected in the latest release. Thank you.
Tom.
For formula issue, could you please post your template file and output file here or send them to nanjing@aspose.com?
For 3, please set formulas with Cell.Formula. I will also check this issue.
Here is the ZIP file.
The template and the result spreadsheet are both in the ZIP.
Thanks for your help.
Tom.
I tried a piece of code with your files but didn’t find the problem. Maybe it’s a bug in old version. Please try this attached version.
I have tried v4.1.2.3 you've attached, but the same issue was occured. I think you tried the same code as me.
DataTable dtType = new DataTable();
bdd.RapportMensuel(dtType); //this method fills the DataTable and gives it the name "typeh"
WorkbookDesigner wbd = new WorkbookDesigner();
wbd.Open(fDesign); //fDesign is the path to the template file
wbd.SetDataSource(dtType);
wbd.Process();
wbd.Workbook.CalculateFormula(); //I try to calculate the processed formulas
wbd.Save("RapMens.xls", SaveType.OpenInExcel, FileFormatType.Default, response);
Laurence, when you have opened the result file, did you see the errors in the last column?
My system : WinXP Pro SP2, VS.Net 2003, Framework .Net 1.1, MS Excel 2003, Aspose.Cells 4.1.2.3
I hope these infos would be helpful.
Tom.
Could you please create a console application with following code?
WorkbookDesigner wb = new WorkbookDesigner();
wb.Open("d:\\templatenresult\\template.xls");
DataTable dt = new DataTable("typeh");
dt.Columns.Add("maintypelabel");
dt.Columns.Add("heures");
DataRow row = dt.NewRow();
row[0] = 1;
row[1] = "a";
dt.Rows.Add(row);
row = dt.NewRow();
row[0] = 2;
row[1] = "b";
dt.Rows.Add(row);
row = dt.NewRow();
row[0] = 3;
row[1] = "c";
dt.Rows.Add(row);
row = dt.NewRow();
row[0] = 4;
row[1] = "d";
dt.Rows.Add(row);
wb.SetDataSource(dt);
wb.Process();
wb.Workbook.CalculateFormula();
wb.Save("d:\\test\\abc.xls");
Attached is my output file.
I saw the problem in your previous posted file but it's fine in my file.
I have tested your code and the issue has been solved.
The difference is that you fill the DataTable from the code whereas i fill my DataTable from SQL Server. The same issue occur even if i use a simple query like "select 'a' as maintypelabel, 1 as heures".
I really don't know what is the problem, except that my DataTable columns are typed. (your two columns are string typed)
It may be the problem.
Tom.
No, I don't think that's the cause of problem. I change my code to make the first column type is integer and it still works fine. Please check the attached file.
dt.Columns.Add("maintypelabel", typeof(int));
Please remove reference in your project and re-add reference to the new dll. And please restart www service on your machine after rebuilding your application. That's to make sure that your aspnet program are using the new dll.
I have created a console test application. And it is the same issue as in a Web application. I have removed and re-added the reference to the new dll.
Sorry, but i confirm what i said.
You can't set int type to the column "maintypelabel" because it is a Label string. I know why you didn't see the problem. You have inversed the 2 columns. Your column "maintypelabel" contains 1,2,3,etc. and your column "heures" contains a,b,c,etc.
Please try the same test as me.
WorkbookDesigner wb = new WorkbookDesigner();
wb.Open(@"d:\templatenresult\template.xls");
DataTable dt = new DataTable("typeh");
//Please look at the 2 lines below, you've inversed them
dt.Columns.Add("heures", typeof(int));
dt.Columns.Add("maintypelabel");
DataRow row = dt.NewRow();
row[0] = 1;
row[1] = "a";
dt.Rows.Add(row);
row = dt.NewRow();
row[0] = 2;
row[1] = "b";
dt.Rows.Add(row);
row = dt.NewRow();
row[0] = 3;
row[1] = "c";
dt.Rows.Add(row);
row = dt.NewRow();
row[0] = 4;
row[1] = "d";
dt.Rows.Add(row);
wb.SetDataSource(dt);
wb.Process();
wb.Workbook.CalculateFormula();
wb.Save(@"d:\test\abc.xls");
Thank you.
Tom.
Thank you, Tom. I figure out the problem with your help. Please try this attached fix.
Thank you Laurence, everything is ok.