May 2022


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 templateTableTemplate
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")

//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

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