Smart Markers three rows subtotal

Hi,


I have a spreadsheet attached that contains the data and format I want to have when exporting to pdf with Aspose cells. The deal is that I don`t know how I should put the smart markers in a way that the final report is laid the out as the one attached.

Would you have some advice on how to proceed ?


Edgar Chernick.

Hi Edgar,

Thanks for your posting and using Aspose.Cells.

With the smart marker you can achieve the output Excel file as attached with this post. After that you will have to do processing yourself using Aspose.Cells.

Once, you have achieved this output, you will have to use normal Aspose.Cells APIs and add your subtotal rows. Let us know if you need any help in this regard. We will look into it and help you asap.

I used the following code to achieve the output and also attached the source Excel file used in this code for your reference.

C#


Workbook workbook = new Workbook(“test.xls”);


Worksheet worksheet = workbook.Worksheets[“Sheet2”];


DataTable dt = worksheet.Cells.ExportDataTable(0, 0, 13, 3, true);

dt.TableName = “data”;


WorkbookDesigner wd = new WorkbookDesigner();

wd.Workbook = workbook;


wd.SetDataSource(dt);


wd.Process();


workbook.Save(“output.xls”);

What about the subtotals and their formatting ?
I haven`t seen them in your example.

Edgar Chernick.

Hi Edgar,

Thanks for your posting and considering Aspose.Cells.

As I mentioned earlier, once you achieve the above output using smart markers, then you will have to apply formatting and subtotal using Aspose.Cells normal APIs.

Please see the following code, after processing smart marker, it applies formatting to header and sub total rows and add a subtotal formula in cell C6. You will have to write your own code/algorithm to make it dynamic.

I have attached the source and output Excel files and screenshot for your reference.

C#


Workbook workbook = new Workbook(“test.xls”);


Worksheet worksheet = workbook.Worksheets[“Sheet2”];


DataTable dt = worksheet.Cells.ExportDataTable(0, 0, 13, 3, true);

dt.TableName = “data”;


WorkbookDesigner wd = new WorkbookDesigner();

wd.Workbook = workbook;


wd.SetDataSource(dt);


wd.Process();


worksheet = workbook.Worksheets[“Sheet3”];


//Apply style to Header Row

Range range = worksheet.Cells.CreateRange(“A1:C1”);


Style style = workbook.CreateStyle();

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Black;

style.Font.Name = “Arial”;

style.Font.Color = Color.White;


StyleFlag flag = new StyleFlag();

flag.CellShading = true;

flag.Font = true;


range.ApplyStyle(style, flag);


//In a similar way, you can apply style to your other rows

range = worksheet.Cells.CreateRange(“A6:C8”);


style = workbook.CreateStyle();

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Gray;

style.Font.Name = “Arial”;

style.Font.Color = Color.Black;

style.Font.IsBold = true;


flag = new StyleFlag();

flag.CellShading = true;

flag.Font = true;


range.ApplyStyle(style, flag);


//Add sub total - Total delivery

Cell cell = worksheet.Cells[“B6”];

cell.PutValue(“Total Delivery”);


cell = worksheet.Cells[“C6”];

cell.Formula = “=Sum(C2,C4)”;


//Save workbook

workbook.Save(“output1.xls”);