May 2022

Features

API QueryToExcel

This api allows exporting data, through a query or dataset, to an excel file formatting the data and the style.

• Multiple worksheets
• Data through query (SelectQuery) or DataSet
• Column data formatting
• Style definition
• Template - Table or Custom

What is supported by the templates

• Images (ex. logo)
• Titles
• The Grid supports this elements
  • Header
  • Lines
  • Totals

Table template

Custom template

Code examples

//dictionary of styles
//the string is the ID that later can be used in the sheets like classe names
var styles = new Dictionary<string, StyleFormat>();

//1. object responsible for export
QueryToExcel excel = new QueryToExcel(sp, user, styles);

//2. Define information for each sheet
//the styles IDs reference the dictionary of styles previously defined

//the items can be titles or images 
var items = new List<QueryItem>()
{ 
    //the interface will respect the order defined here
    new QueryItem("title", "style ID"),
    new QueryItem("title 2 ", "style ID")
};

//example of a query with different data types
SelectQuery sql = new SelectQuery()
    .Select(CSGenioAfunci.FldNumcontr, "Num")
    .Select(CSGenioAfunci.FldNome, "Nome")
    .Select(CSGenioAfunci.FldVencim, "Vencimento")
    .Select(CSGenioAfunci.FldEntrada, "Entrada")
    .From(Area.AreaFUNCI);

//optional
//information about columns, formats.
//If the formats are not defined, then will be applied the format according to the native data types
//If you use the placement the column will have a specific name allowing the merge of columns
//in the example the columns "Num" and "Nome" will became "Funcionário" and then custom template will merge the columns horizontally
//new QueryInfo.ColumnInfo("Num", new QueryInfo.ColumnPlacement("Funcionário", QueryInfo.PlacementMerge.OnEqual));
//new QueryInfo.ColumnInfo("Nome", new QueryInfo.ColumnPlacement("Funcionário", QueryInfo.PlacementMerge.OnEqual));

//data formatting
var columnsInfo = new List<QueryInfo.ColumnInfo>()
{
    new QueryInfo.ColumnInfo("Num", QueryInfo.ColumnFormat.IntField), //specific format
    new QueryInfo.ColumnInfo("Nome", QueryToExcel.GetColumnFormat(CSGenioAfunci.FldNome)), //automatic format base on Genio format
    new QueryInfo.ColumnInfo("Vencimento", QueryInfo.ColumnFormat.CurrencyField),
    new QueryInfo.ColumnInfo("Entrada", QueryInfo.ColumnFormat.DateField)
};

//the queryinfo can be defined through a SelectQuery or a manual DataSet
var info = new QueryInfo(sql, columnsInfo, items,
    //Template tye
    //we can use QueryInfo.CustomGridTemplate() or QueryInfo.TableTemplate()
    //the difference is in the behavior of each template
    //in the custom template we have control of the styles of all elements and it is possible to merge columns and data.
    //the table template uses the excel table formats,
    //it is an automatic template and supports more features than the custom template,
    //but have more restriction, for example, it's not possible to do merge header columns or data.
    new QueryInfo.TableTemplate(QueryInfo.QTableStyles.Medium2)
);

//each sheet can have multiple tables, just add a QueryInfo collection
var ws = new QueryToExcel.WorksheetQueries("sheet name", info);

//it is possible to add multiple worksheets
excel.AddWorksheet(ws);

//export data to a file
excel.Convert("File path with name and extension");