Free Support Forum - aspose.com

Group data in Excel worksheet using Smart Markers in .NET

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.

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

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

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:

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.


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.

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.

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

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.

Thanks,

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