Free Support Forum - aspose.com

Template - chart - function problem[SOLVED ]

Hi,

I am almost able to do everything I wanted to do with aspose.excel , I am really happy with this product.
There is only one more thing that I am struggling with. After reviewing all the post, I did not find the solution.
My application create dynamically several worksheet based on the same template, it works great, thanks to your help.
Those worksheet are pretty simple, there is 3 column and a chart representing 2 of those column.
However, I have problem with the chart and the Total of one column.

Basically , I would like to show on the graph only the first 5 value, to do that I use this dynamic range names(ctrl+F3):
=OFFSET(Sheet1!$C$1,1,0,5,1) but when the excel spreadsheet is generated there is an error it becomes : =OFFSET(#REF!$C$1,1,0,5,1) . I do not know if using this function is the best way to show only the first 5 value, therefore any suggestion are welcome.

The second problem is with the Total of one column, when in the template I have this function:=SUM(D2:D3) but when the excel spread sheet is generated it becomes : =SUM(G13:G14)

I am attaching the template and the excel file generated.

Thanks

I didn’t find this problem in my place. Please download and try v3.5.3 at http://www.aspose.com/Downloads/Aspose.Excel/Default.aspx . If the problem still occurs, please post your sample code here. Thank you.

Hi Laurence,

With the v3.5.3, I do not have the problem with the total but I still have the problem with the graph. I do not think there is any problem with my code, I have been working on this report for the last 10 days and tested it.
However here is it:

private void Button3_Click(object sender, System.EventArgs e)
{
string path="C:\\Inetpub\\wwwroot\\validationsoftware\\EChart\\template.xls";
ExcelDesigner designer = new ExcelDesigner();
designer.Open(path);
//if DropDownList has only 2 elements
if(DDL_prog.Items.Count<2)
{
designer.SetDataSource(CreateDataSource(0,"sm_dla"));
designer.Excel.Worksheets[0].Name="result";
designer.Process();
}
else
{
designer.Excel.Worksheets.AddCopy(0);
designer.SetDataSource(CreateDataSource(0,"sm_dla"));
designer.Excel.Worksheets[1].Name="result";
designer.Process(1, true);
designer.ClearDataSource();
for (int k=1;k{
designer.Excel.Worksheets.AddCopy(0);

designer.SetDataSource(CreateDataSource(int.Parse(DDL_prog.Items[k].Value),"sm_dlaprog"));
designer.Excel.Worksheets[k+1].Name=DDL_prog.Items[k].Text;
designer.Process(k+1, true);
designer.ClearDataSource();
}
designer.Excel.Worksheets.RemoveAt(0);
}
designer.Save("result.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);
}

private DataSet CreateDataSource(int prog,string sp)
{
DataSet ds = new DataSet();
sqlDA.SelectCommand.CommandText = sp;
sqlDA.SelectCommand.Parameters["@project"].Value=Session["project"];
sqlDA.SelectCommand.Parameters["@start"].Value=Start.SelectedDate;
sqlDA.SelectCommand.Parameters["@end"].Value=End.SelectedDate;

sqlDA.SelectCommand.Parameters["@part"].Value=int.Parse(PartList.SelectedValue);
sqlDA.SelectCommand.Parameters["@etridprogram"].Value=prog;
sqlConnection1.Open();
sqlDA.Fill(ds);
sqlConnection1.Close();
return ds;
}

I am including again the template file and the result I get. In the result spread sheet ,you can see that there is a problem with the chart if you look at the Define Names (ctrl+F3) for the name "qc" you will see =OFFSET(#REF!$C$1,1,0,5,1) , if you change #ref by the name of the spreadsheet (result.xls for example) then the Chart will work.
I do not know if it is the best way to display the first 5 values, so if you have a better idea, let me know.

Thanks for you help

This problem is caused by copying defined names. This is really a complex issue. Could you try this workaround?

1. Create template files similar to your current template file but don't refer to a name. Just refer it to a cell range.

2. Process the smart markers with source data

3. Re-set the chart data source.

Following is my sample code:

static void Main()

{

ExcelDesigner designer = new ExcelDesigner();
designer.Open("d:\\test\\template.xls");

designer.Excel.Worksheets.AddCopy(0);
designer.SetDataSource(CreateDataSource(0, "sm_dla"));
designer.Excel.Worksheets[1].Name = "result1";
designer.Process(1, true);
designer.Excel.Worksheets[1].DesignCharts[0].NSeries.Add("result1!D2:D6", true);
designer.Excel.Worksheets[1].DesignCharts[0].NSeries.CategoryData = "result1!C2:C6";
for(int i = 1; i < 3; i ++)
{
designer.Excel.Worksheets.AddCopy(0);
designer.SetDataSource(CreateDataSource(i + 1, "sm_dlaprog"));
string sheetName = "result" + (i + 1).ToString();
designer.Excel.Worksheets[i + 1].Name = sheetName;
designer.Process(i + 1, true);
designer.Excel.Worksheets[i + 1].DesignCharts[0].NSeries.Add(sheetName + "!D2:D6", true);
designer.Excel.Worksheets[i + 1].DesignCharts[0].NSeries.CategoryData = sheetName + "!C2:C6";
designer.ClearDataSource();
}

designer.Excel.Worksheets.RemoveAt(0);
designer.Save("d:\\test\\result.xls");
}

static DataTable CreateDataSource(int index, string name)
{
DataTable dt = new DataTable(name);
dt.Columns.Add("jour", System.Type.GetType("System.Int32"));
dt.Columns.Add("NameQC");
dt.Columns.Add("reject", System.Type.GetType("System.Int32"));

for(int i = 0; i < 5; i ++)
{
DataRow row = dt.NewRow();
row[0] = i;
row[1] = "a" + i.ToString();
row[2] = 100 + i;
dt.Rows.Add(row);
}
return dt;
}

Thanks a lot, it works perfectly like that