XlExport.CreateExporter(XlDocumentFormat, IXlFormulaParser) Method

Creates an exporter that performs data export to the specified Excel format using the specified formula parser.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v23.2.Core.dll

NuGet Package: DevExpress.Printing.Core

Declaration

C#

public static IXlExporter CreateExporter(
    XlDocumentFormat documentFormat,
    IXlFormulaParser formulaParser
)

VB.NET

Public Shared Function CreateExporter(
    documentFormat As XlDocumentFormat,
    formulaParser As IXlFormulaParser
) As IXlExporter

Parameters

Name	Type	Description
documentFormat	XlDocumentFormat	One of the XlDocumentFormat enumeration members that specifies the type of the output document.
formulaParser	DevExpress.Export.Xl.IXlFormulaParser	An object exposing the IXlFormulaParser interface that provides the capability to parse worksheet formulas. Required to validate worksheet formulas before export.

Returns

Type	Description
IXlExporter	An object exposing the IXlExporter interface.

Remarks

Use the CreateExporter method with the new instance of the XlFormulaParser passed as the parameter to parse and validate strings used as formulas in the IXlCell.SetFormula and IXlCell.SetSharedFormula methods.

If the formula parser is not specified,

• export to .XLSX format is performed without validation (i.e., a formula can contain unsupported defined names or syntax errors).
• export to .XLS format fails and throws the System.InvalidOperationException exception with a message detailing the missing parser.

Note

The XlFormulaParser class used as a formula parser requires a reference to the DevExpress.Spreadsheet.v23.2.Core.dll assembly.

Example

In this code, the exporter instance is created with the XlExport.CreateExporter method which also creates the formula parser. A worksheet contains a heading row and four rows populated with data. The last column in each data row contains a formula created by supplying a formula string to the IXlCell.SetFormula method.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/excel-export-api-examples

FormulaActions.cs

// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat, new XlFormulaParser());
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
    document.Options.Culture = CultureInfo.CurrentCulture;
    // Create a worksheet.
    using (IXlSheet sheet = document.CreateSheet()) {
        // Create worksheet columns and set their widths.
        for (int i = 0; i < 4; i++) {
            using (IXlColumn column = sheet.CreateColumn()) {
                column.WidthInPixels = 80;
            }
        }
        // Generate data for the document.
        string[] header = new string[] { "Description", "QTY", "Price", "Amount" };
        string[] product = new string[] { "Camembert", "Gorgonzola", "Mascarpone", "Mozzarella" };
        int[] qty = new int[] { 12, 15, 25, 10 };
        double[] price = new double[] { 23.25, 15.50, 12.99, 8.95 };
        double discount = 0.2;
        // Create the header row.
        using (IXlRow row = sheet.CreateRow()) {
            for (int i = 0; i < 4; i++) {
                using (IXlCell cell = row.CreateCell()) {
                    cell.Value = header[i];
                }
            }
        }
        // Create data rows using string formulas.
        for (int i = 0; i < 4; i++) {
            using (IXlRow row = sheet.CreateRow()) {
                using (IXlCell cell = row.CreateCell()) {
                    cell.Value = product[i];
                }
                using (IXlCell cell = row.CreateCell()) {
                    cell.Value = qty[i];
                }
                using (IXlCell cell = row.CreateCell()) {
                    cell.Value = price[i];
                }
                using (IXlCell cell = row.CreateCell()) {
                    // Set the formula to calculate the amount 
                    // applying 20% quantity discount on orders more than 15 items. 
                    cell.SetFormula(String.Format("=IF(B{0}>15,C{0}*B{0}*(1-{1}),C{0}*B{0})", i + 2, discount));
                }
            }
        }

    }
}

FormulaActions.vb

' Create an exporter instance.
Dim exporter As IXlExporter = XlExport.CreateExporter(documentFormat, New XlFormulaParser())
' Create a new document.
Using document As IXlDocument = exporter.CreateDocument(stream)
    document.Options.Culture = CultureInfo.CurrentCulture
    ' Create a worksheet.
    Using sheet As IXlSheet = document.CreateSheet()
        ' Create worksheet columns and set their widths.
        For i As Integer = 0 To 3
            Using column As IXlColumn = sheet.CreateColumn()
                column.WidthInPixels = 80
            End Using
        Next i
        ' Generate data for the document.
        Dim header() As String = { "Description", "QTY", "Price", "Amount" }
        Dim product() As String = { "Camembert", "Gorgonzola", "Mascarpone", "Mozzarella" }
        Dim qty() As Integer = { 12, 15, 25, 10 }
        Dim price() As Double = { 23.25, 15.50, 12.99, 8.95 }
        Dim discount As Double = 0.2
        ' Create the header row.
        Using row As IXlRow = sheet.CreateRow()
            For i As Integer = 0 To 3
                Using cell As IXlCell = row.CreateCell()
                    cell.Value = header(i)
                End Using
            Next i
        End Using
        ' Create data rows using string formulas.
        For i As Integer = 0 To 3
            Using row As IXlRow = sheet.CreateRow()
                Using cell As IXlCell = row.CreateCell()
                    cell.Value = product(i)
                End Using
                Using cell As IXlCell = row.CreateCell()
                    cell.Value = qty(i)
                End Using
                Using cell As IXlCell = row.CreateCell()
                    cell.Value = price(i)
                End Using
                Using cell As IXlCell = row.CreateCell()
                    ' Set the formula to calculate the amount 
                    ' applying 20% quantity discount on orders more than 15 items. 
                    cell.SetFormula(String.Format("=IF(B{0}>15,C{0}*B{0}*(1-{1}),C{0}*B{0})", i + 2, discount))
                End Using
            End Using
        Next i

    End Using
End Using

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the CreateExporter(XlDocumentFormat, IXlFormulaParser) method.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

excel-export-library-generate-an-invoice/CS/XLExportExample/Form1.cs#L141

// Create an exporter with the specified formula parser.
IXlExporter exporter = XlExport.CreateExporter(documentFormat, new XlFormulaParser());
// Create a new document and begin to write it to the specified stream.

how-to-use-the-excel-export-api-to-generate-a-list-of-employees/CS/XLExportExample/Form1.cs#L99

// Create an exporter with the specified formula parser.
IXlExporter exporter = XlExport.CreateExporter(documentFormat, new XlFormulaParser());
// Create a new document and begin to write it to the specified stream.

VB.NET

excel-export-library-generate-an-invoice/VB/XLExportExample/Form1.vb#L149

' Create an exporter with the specified formula parser.
Dim exporter As IXlExporter = XlExport.CreateExporter(documentFormat, New XlFormulaParser())
' Create a new document and begin to write it to the specified stream.

how-to-use-the-excel-export-api-to-generate-a-list-of-employees/VB/XLExportExample/Form1.vb#L93

' Create an exporter with the specified formula parser.
Dim exporter As IXlExporter = XlExport.CreateExporter(documentFormat, New XlFormulaParser())
' Create a new document and begin to write it to the specified stream.