Free Support Forum - aspose.com

Using WorkBookDesigner for Dynamic Colums

Hi,


We have a licensed Aspose Cells(4.4.0.0) in our app which is using ASP.NET and C# 3.5. We are aware of having a template xls and read the same using WorkBookDesigner and finally do the ‘process’ so that the data table’s records are dynamically rendered.

However, we now have a case where in we do not know the name of the column in the data table. As per conditions, the data tables column name can be Col1, Col2. So how can this be mentioned when preparing the template and what code changes are required. Please let us know.

Thanks,
Gautham

Hi,


Well, the Smart Markers are created/inserted in the template File’s sheet cells based on the Tables and Field Names on the back end. I think it should be straight forward and easy to get column names from data tables. You may simply use DataColumn.ColumnName property. See the topic for you complete reference.
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.columnname.aspx

Thank you.

Thanks for the reply. However, i am afraid that was not we are looking for. I shall be precise time. Attached is the template that we are using. The last column in the excel template can be dynamic i.e. the data table used for processing can have any one column among Completed Date, Due Date and Reminder Date. So how can this be designed at template level so that if the data table has a Due Date column, the template and the excel should have the respective column.


Code Base
reportData.TableName = “MilestoneReport”;
WorkbookDesigner wDesigner = new WorkbookDesigner(); wDesigner.Open(@“Report_Templates\Milestone_Report_Template.xls”);
Workbook wBook = wDesigner.Workbook;
wDesigner.SetDataSource(reportData);
wDesigner.Process(true);
wDesigner.workBook.Save(stream, FileFormatType.Excel2003);

Hi,

The last column in the excel template can be dynamic i.e. the data table used for processing can have any one column among Completed Date, Due Date and Reminder Date. So how can this be designed at template level so that if the data table has a Due Date column, the template and the excel should have the respective column"

Well, you can do it, it is very easy. I think first you find out the last column in the data table, and then input the smart markers dynamically into the template file at run time accordingly.

E.g

//Find out the last column in the data table
//You code goes here.
//…

//Now You have found that it is DueDate Column
//…

//…


//Get the first worksheet(default sheet) in the workbook.
Aspose.Cells.Worksheet w = workbookDesigner.Workbook.Worksheets[0];
//Input/change the marker in the cell accordingly.
w.Cells[“D4”].PutValue(”&=MilestoneReport.DueDate");

// Setting Data Source for markers

//Processing the markers.

//…

For further reference, see the document on smart markers:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/smart-markers.html


Thank you.

Amjad,


Thanks a ton for the quick reply. We shall move on with your suggestions.

On the other side, can you let us know if we can apply styles dynamically in the excel based on the value in the cell i.e. if the value in a particular cell of the required column is less than 40, the cell should be filled with red color, if less than 60 and more than 40 cell color should be green etc. Please guide us.

Hi,


I think you may make use Conditional Formatting feature, see the document for your complete reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/conditional-formatting.html


Thank you.