DataTables Imports and Subtotals with Formatting

Is it possible to get the Data from a DataTable and insert into a sheet and have it apply the style and subtotals defined already in the sheet?


I want to format the table already in excel the way the table should be treated when using DataTable Import:

This image contains my data in the DataTable.
http://i.imgur.com/r5GFjbb.jpg

Then I want to Import that DataTable into the excel Template that would produce This:
http://i.imgur.com/6NLaP0K.jpg

But the formatting does not happen unless the table is pre-formatted completely inside of excel, the problem is that my original data table could have unknown rows and unknown number of columns depending on the user selecting the years or locations.

Is this possible with your product?

Hi Mario,


Thank you for contacting Aspose support.

You may consider using Smart Markers for your requirement. Please note, you may define the formatting in the template file where data is filled on the runtime while keeping the formatting intact. Please check the following piece of code as well as attached input/output spreadsheets for your reference.

C#

//Create DataTable dynamically in code for demonstration purposes
var table = new DataTable(“Sales”);
//Add columns to the newly created DataTable while specifying the column type
table.Columns.Add(“Year”, typeof(string));
table.Columns.Add(“Item1”, typeof(int));
table.Columns.Add(“Item2”, typeof(int));
table.Columns.Add(“Item3”, typeof(int));
table.Columns.Add(“Item4”, typeof(int));
table.Columns.Add(“Item5”, typeof(int));
table.Columns.Add(“Item6”, typeof(int));
table.Columns.Add(“Item7”, typeof(int));

for (int i = 0; i < 10; i++)
{
//Add some rows with data to the DataTable
table.Rows.Add(“2000”, 2310, 0, 110, 15, 20, 25, 22);
}

//Create an instance of WorkbookDesigner class
var designer = new WorkbookDesigner();
//Assign the Workbook property to the instance of Workbook (template with markers)
designer.Workbook = new Workbook(dir + “template.xlsx”);
//Set the data source
designer.SetDataSource(table);
//Call Process method to populate data
designer.Process();
//Save result on disc
designer.Workbook.Save(dir + “output.xlsx”);

Hi,


This is in the right direction. I will test using the Designer Class. What happened to the 7th item? it never made it into the excel output since the input excel file has only defined 6 columns. This is my same scenario as well. It would have been great if the 7th item came into the output.xlsx file even though you only defined 6 items in the template.

Do all the items and columns need to be predefined in order for it to work?
Thank you,

Hi,

Thanks for your posting and using Aspose.Cells.

Yes, you will have to predefine the columns you want to display in the template file. Please check the template excel file attached with this post. I have added the column 7 and now in the output excel file shows data in column 7.

I used the same code as given above to generate the output excel file.

C#

string dir = @"F:\Shak-Data-RW\Downloads";

//Create DataTable dynamically in code for demonstration purposes
var table = new DataTable(“Sales”);
//Add columns to the newly created DataTable while specifying the column type
table.Columns.Add(“Year”, typeof(string));
table.Columns.Add(“Item1”, typeof(int));
table.Columns.Add(“Item2”, typeof(int));
table.Columns.Add(“Item3”, typeof(int));
table.Columns.Add(“Item4”, typeof(int));
table.Columns.Add(“Item5”, typeof(int));
table.Columns.Add(“Item6”, typeof(int));
table.Columns.Add(“Item7”, typeof(int));

for (int i = 0; i < 10; i++)
{
//Add some rows with data to the DataTable
table.Rows.Add(“2000”, 2310, 0, 110, 15, 20, 25, 22);
}

//Create an instance of WorkbookDesigner class
var designer = new WorkbookDesigner();
//Assign the Workbook property to the instance of Workbook (template with markers)
designer.Workbook = new Workbook(dir + “template.xlsx”);
//Set the data source
designer.SetDataSource(table);
//Call Process method to populate data
designer.Process();
//Save result on disc
designer.Workbook.Save(dir + “output.xlsx”);