Group data in Excel worksheet using Smart Markers in .NET

Aspose.Cells Product Family
1

Hi,

I have a datatable with 5 rows for example. One of the column is the name of my excel file, so i have the name of the excel 5 times but i only need show it once time in my excel.

How can i do it?

If I put &=Datatable.Name i see it five.

Thanks,

IASOFT.

2

Hi,

I think, then you should use a variable. There are two types of variables i.e

  1. single variable (&=$VariableName)
  2. multi variable (&=$VariableArra)

You will use a single variable.

Please refer to this document for more help: Smart Markers

3

oK,

I know this option but I need get all the information of my DataTable.

Is it impossible?

And If i decide use a variable, How can I assign the value in c#?

Thanks,

IASOFT

4

Hi,

I have created an example/sample project for you to explain you how to use your custom variable or variables using the smartmarkers.

Please see the code below. Download the sample C# project attached by me. See the source and output xlsx files and the screenshot.

C#

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;


using Aspose.Cells;



namespace AsposeTest

{

class Program

{


class MyDataTable

{

string m_name;


public string Name

{

get { return m_name; }

set { m_name = value; }

}


internal MyDataTable(string name)

{

m_name = name;

}

}



static void SmartMarkerMultiOrSingeVariableExample()

{

string smartMarkerfile = @“F:\Shak-Data-RW\Downloads\SampleDesigner.xlsx”;



IList lst = new List();

lst.Add(new MyDataTable(“IASOFT”));


WorkbookDesigner report = new WorkbookDesigner();

report.Workbook = new Workbook(smartMarkerfile);


report.SetDataSource(“MyDataTable”, lst);

report.Process(false);


report.Workbook.Save(smartMarkerfile + “.out.xlsx”, SaveFormat.Xlsx);


}


static void Main(string[] args)

{

SmartMarkerMultiOrSingeVariableExample();

}

}

}



Screenshot:
5

Hi,


I think you may use Smart Markers’ “Grouping Data” feature for your need. See the “Grouping Data” sub-topic in the document:
Smart Markers.

So, you will input smart markers as:
&=Table1.Name(group:normal) --> this is the group by column. --> the value will be shown as once
&=Table1.Field2 --> Other column
&=Table1.Field2 --> Other column
etc.

Thank you.


6

Hi,

At first, thanks for answering me. However, it isnt exactly that I need.

I used "Grouping data" and i could show only one record, but my excels row grow up.

I attached and example of my code and my output. You can see it in the topic of de excel file. It multiply me the records in the excel.

Thanks,

IASOFT.

7

Hi,


Yes, your result is based on what you input smart markers, it is fine e.g
you place i.e…,
&=Datos.Nombre_Area(group:normal) &=Datos.Nombre_Area(group:normal,skip:0) &=Datos.Nombre_Area(group:merge)

As I suggest you that you should place the markers accordingly, e.g
&=Table1.Name(group:normal) &=Table1.Field2 &=Table1.Field2
Please do not attach group attribute in the detailed markers.

Thank you.
8

Sorry,

but I dont know if it is impossible or i dont know how to do it.

My datatable has 5 rows with one field, for example: "Nombre_Area" and the value is "Madrid".

I attach my output and the excel file I would like to have.

Can you write me the exactly instruction with my field?

Thanks

9

Hi,


I think you may try your SQL query with distinct keyword (that extract data from your data source), e.g

"Select distinct(Nombre_Area) from YourTable"

Thank you.

10

Thanks,

I suposse I cant do it with one datatble because i cant give back two datatables in the same dataset.