Designer template with dynamic number of worksheets [SOLVED]

Aspose.Cells Product Family
1

Hi,

In reference at the post: http://www.aspose.com/community/Forums/10710/ShowPost.aspx

I would like to do the same thing with my web application. Having a template and then build dynamically a number of worksheet (the number of worksheet can change form one user to another) based on one unique template.
Here is my code:
DataSet ds = CreateDataSource(1);

ExcelDesigner designer = new ExcelDesigner();

designer.Open(path);

designer.SetDataSource(ds);
designer.Process(0, true);
designer.ClearDataSource();

designer.Excel.Worksheets.AddCopy(0);// if I add this line, it will copy the data of the worksheet[0] into the worksheet[1]. I have then 2 identical worksheet while I would like to have 2 different worksheet from 2 diferent datasource

designer.SetDataSource(CreateDataSource(2));
designer.Process(1, true);
designer.ClearDataSource();

designer.Save("result.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);


if I do not add this line :designer.Excel.Worksheets.AddCopy(0); , I am getting the following error.

Exception Details: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
Source Error: 
Line 385:			designer.SetDataSource(CreateDataSource(2));
Line 386:			//designer.Excel.Worksheets.Add();
Line 387:			designer.Process(1, true);
Line 388:			designer.ClearDataSource();


Thanks for your help
2

Could you please post your template file here? And which version of Aspose.Excel are you using?

3

Hi , I downloaded the demo version last week, so I think I have the latest one
I have this version :3.5.2.0

Thanks for your help

4

This issue may be caused by repeatedly copying charts. Please try this fix.

5

Hi thanks very much,

Should I use the designer.Excel.Worksheets.AddCopy(0); ?

6

You should copy the worksheet before process the data.

ExcelDesigner designer = new ExcelDesigner();

designer.Open(path);

//Copy smart markers before processing them. Otherwise, smart markers will be replaced.

designer.Excel.Worksheets.AddCopy(0);


designer.SetDataSource(ds);
designer.Process(0, true);
designer.ClearDataSource();

designer.SetDataSource(CreateDataSource(2));
designer.Process(1, true);
designer.ClearDataSource();

designer.Save("result.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);

7

Thanks very much for your help:

Here is my final version that works great!!!:
my createdatasource takes 2 argument , one parameter from my stored procedure and the stored procedure name.

ExcelDesigner designer = new ExcelDesigner();
designer.Open(path);

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<DDL_prog.Items.Count;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);

Thanks to the aspose.excel team