Free Support Forum - aspose.com

Aspose.cells dynamic columns


#1

Hi team,

I am working on a report that would be created using the smart markers utility.

The final report output would have some dynamic number of columns within it.

The count and column of these dynamic columns would vary for every report.

These dynamic columns should be placed between the static columns.

PFA sample file which explains the requirement better.

SampleTemplate.PNG (86.0 KB)


#2

@Manesh_M,

Thanks for the screenshot.

Well, as I understand your scenario/ case a bit, I think you need to use Smart Markers for those dynamic columns (in the screenshot). You have data in rows under those static columns where as dynamic data would be filled for your dynamic columns from your data source. Now you want to retain your static data as it is (in the static columns) and dynamic data should be filled under dynamic columns in such a way that your existing data (in static columns) should not be messed up or deteriorated. If my understanding is right, you should use “noadd” parameter when placing your Smart Markers for your dynamic columns, e.g “&=Table1.Field1(noadd)”.

Please see the document for your reference on Smart Markers:
https://docs.aspose.com/display/cellsnet/Using+Smart+Markers

If you still have any confusion/issue, please create a simple console demo application (runnable). zip the project and provide us here, we will check it soon. Also attach your template file (input file containing smart markers) and output file. Please remove inter-dependencies for external database or data source and use dynamic dataset/datatables in code, so we could execute your project seamlessly. Moreover, attach a sample file with data as per your requirements (you may create the desired file manually in MS Excel). This will help us really to evaluate your issue precisely and to consequently figure it out soon.


#3

Hi Amjad,

Thanks for your quick reply.
Your understanding is correct to some extent. Both the dynamic and static columns will be populated from a datasource.

Considering that the below is the syntax for populating the records from datasource.
&=[datasource].[ColumnName]

I will be able to populate the static columns since I know the column names while designing the smart markers.

However, by dynamic, I mean column names and the number of columns are not known before hand. Hence, I am not sure how I can mention such columns in smart markers.

Hope I am able to explain the requirement.


#4

@Manesh_M,

If you do not know the column names before hand, then you may place Smart Markers dynamically in code. But, mind you, column/fields names are compulsory for smart markers to be processed. I think you may get those column names and other meta data using ADO.NET and other relevant .NET APIs (e.g using System.Data or/and using System.Data.OleDb, etc.) using your own code and then insert markers into the relevant cells for your dynamic columns in the template file easily, see the line of code for your reference;
e.g
Sample code:

worksheet.Cells[“F11”].PutValue("&=MyTable1.MyColumn");


#5

Thanks again will try and get back to you incase of any issue.

Also wanted to ask you if there is any option to pivot the records using smart markers.
I mean the dynamic columns that I spoke of are rows and I will to pivot it something like the tablix option available in SSRS.

Rows fetched from ADO.net getting converted to columns in aspose extract generated using smart markers.


#6

@Manesh_M,

I think you may create a template PivotTable (sort of blank table with little data) and place your markers (e.g for specific Page level items, etc.). If you find any issue, kindly do provide a sample console application (runnable), you may zip prior attaching here, we will check it soon. Also attach your input and output files to evaluate your issue properly.


#7

Hi Amjad,

Thanks for the reply.
Is there a link that explains the process to help me get started with the sample console application for both the cases?

Also, we are trying to generate the reports purely using the smart makers utility. Hence, we want to avoid having any custom C# code per report.

Can you provide a sample markup file or application to help me understand how this can be achieved?


#8

@Manesh_M,

We do not have specific samples for smart markers. We demonstrate the Smart markers feature in general way for the users and the users should understand it (using sample code and general files) and utilize it in their scenarios/ cases in their own way using their own code and logic, etc. We recommend you to kindly see the documents in the section for your reference on Smart Markers feature.
https://docs.aspose.com/display/cellsnet/Smart+Markers

Moreover, we recommend to evaluate and run the VS.NET solution (especially for Smart Markers feature using the sample file(s)), you may download the Examples at Gihut repos. You may download the project and open it into VS.NET and check sample code in different class files (.cs) for your complete reference.
https://github.com/aspose-cells/Aspose.Cells-for-.NET

Also, find attached a simple sample file (containing smart markers) for pivot table for reference.
files1.zip (11.2 KB)

Hope, this helps a bit.