Getting Started

• Nov 16, 2018
• 6 minutes to read

To get started with the Excel Export library, perform the steps below.

1. Start Visual Studio and create a new project by selecting FILE | New | Project… in the main menu. In the invoked New Project dialog, select Console Application, specify the project name and location, and click OK.

   

2. In the Solution Explorer, right-click the References node and select Add Reference… in the context menu.

   

3. In the invoked Reference Manager dialog, add references to the following libraries.

   • DevExpress.Data.v18.2.dll
   • DevExpress.Printing.v18.2.Core.dll

4. Paste the code listed below in the Main method of the Program.cs file (Main procedure of the Module1.vb file for Visual Basic).

   Note

   A complete sample project is available at https://github.com/DevExpress-Examples/xl-export-library-getting-started-t242354

   Program.cs

   using DevExpress.Export.Xl;
   using System.IO;
   // ...
   namespace XLExportExamples
   {
       class Program
       {
           static void Main(string[] args)
           {
               // Create an exporter instance. 
               IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xlsx);
   
               // Create the FileStream object with the specified file path. 
               using (FileStream stream = new FileStream("Document.xlsx", FileMode.Create, FileAccess.ReadWrite)) {
   
                   // Create a new document and begin to write it to the specified stream. 
                   using (IXlDocument document = exporter.CreateDocument(stream)) {
   
                       // Add a new worksheet to the document. 
                       using (IXlSheet sheet = document.CreateSheet()) {  
   
                           // Specify the worksheet name.
                           sheet.Name = "Sales report";
   
                           // Create the first column and set its width. 
                           using (IXlColumn column = sheet.CreateColumn()) {
                               column.WidthInPixels = 100;
                           }
   
                           // Create the second column and set its width.
                           using (IXlColumn column = sheet.CreateColumn()) {
                               column.WidthInPixels = 250;
                           }
   
                           // Create the third column and set the specific number format for its cells.
                           using (IXlColumn column = sheet.CreateColumn()) {
                               column.WidthInPixels = 100;
                               column.Formatting = new XlCellFormatting();
                               column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
                           }
   
                           // Specify cell font attributes.
                           XlCellFormatting cellFormatting = new XlCellFormatting();
                           cellFormatting.Font = new XlFont();
                           cellFormatting.Font.Name = "Century Gothic";
                           cellFormatting.Font.SchemeStyle = XlFontSchemeStyles.None;
   
                           // Specify formatting settings for the header row.
                           XlCellFormatting headerRowFormatting = new XlCellFormatting();
                           headerRowFormatting.CopyFrom(cellFormatting);
                           headerRowFormatting.Font.Bold = true;
                           headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
                           headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
   
                           // Create the header row.
                           using (IXlRow row = sheet.CreateRow()) {
                               using (IXlCell cell = row.CreateCell()) {
                                   cell.Value = "Region";
                                   cell.ApplyFormatting(headerRowFormatting);
                               }
                               using (IXlCell cell = row.CreateCell()) {
                                   cell.Value = "Product";
                                   cell.ApplyFormatting(headerRowFormatting);
                               }
                               using (IXlCell cell = row.CreateCell()) {
                                   cell.Value = "Sales";
                                   cell.ApplyFormatting(headerRowFormatting);
                               }
                           }
   
                           // Generate data for the sales report.
                           string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
                           int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 };
                           for (int i = 0; i < 8; i++)
                           {
                               using (IXlRow row = sheet.CreateRow()) {
                                   using (IXlCell cell = row.CreateCell()) {
                                       cell.Value = (i < 4) ? "East" : "West";
                                       cell.ApplyFormatting(cellFormatting);
                                   }
                                   using (IXlCell cell = row.CreateCell()) {
                                       cell.Value = products[i % 4];
                                       cell.ApplyFormatting(cellFormatting);
                                   }
                                   using (IXlCell cell = row.CreateCell()) {
                                       cell.Value = amount[i];
                                       cell.ApplyFormatting(cellFormatting);
                                   }
                               }
                           }
   
                           // Enable AutoFilter for the created cell range.
                           sheet.AutoFilterRange = sheet.DataRange;
   
                           // Specify formatting settings for the total row.
                           XlCellFormatting totalRowFormatting = new XlCellFormatting();
                           totalRowFormatting.CopyFrom(cellFormatting);
                           totalRowFormatting.Font.Bold = true;
                           totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent5, 0.6));
   
                           // Create the total row.
                           using (IXlRow row = sheet.CreateRow()) {
                               using (IXlCell cell = row.CreateCell()) { 
                                   cell.ApplyFormatting(totalRowFormatting); 
                               }
                               using (IXlCell cell = row.CreateCell()) {
                                   cell.Value = "Total amount";
                                   cell.ApplyFormatting(totalRowFormatting);
                                   cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
                               }
                               using (IXlCell cell = row.CreateCell()) {
                                   // Add values in the cell range C2 through C9 using the SUBTOTAL function. 
                                   cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(2, 1, 2, 8), XlSummary.Sum, true));
                                   cell.ApplyFormatting(totalRowFormatting);
                               }
                           }
                       }
                   }
               }
               // Open the XLSX document using the default application.
               System.Diagnostics.Process.Start("Document.xlsx");
           }
       }
   }
   

   Program.vb

   Imports DevExpress.Export.Xl
   Imports System.IO
   ' ...
   Namespace XLExportExamples
       Friend Class Program
           Shared Sub Main(ByVal args() As String)
               ' Create an exporter instance. 
               Dim exporter As IXlExporter = XlExport.CreateExporter(XlDocumentFormat.Xlsx)
   
               ' Create the FileStream object with the specified file path. 
               Using stream As New FileStream("Document.xlsx", FileMode.Create, FileAccess.ReadWrite)
   
                   ' Create a new document and begin to write it to the specified stream. 
                   Using document As IXlDocument = exporter.CreateDocument(stream)
   
                       ' Add a new worksheet to the document. 
                       Using sheet As IXlSheet = document.CreateSheet()
   
                           ' Specify the worksheet name.
                           sheet.Name = "Sales report"
   
                           ' Create the first column and set its width. 
                           Using column As IXlColumn = sheet.CreateColumn()
                               column.WidthInPixels = 100
                           End Using
   
                           ' Create the second column and set its width.
                           Using column As IXlColumn = sheet.CreateColumn()
                               column.WidthInPixels = 250
                           End Using
   
                           ' Create the third column and set the specific number format for its cells.
                           Using column As IXlColumn = sheet.CreateColumn()
                               column.WidthInPixels = 100
                               column.Formatting = New XlCellFormatting()
                               column.Formatting.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"
                           End Using
   
                           ' Specify cell font attributes.
                           Dim cellFormatting As New XlCellFormatting()
                           cellFormatting.Font = New XlFont()
                           cellFormatting.Font.Name = "Century Gothic"
                           cellFormatting.Font.SchemeStyle = XlFontSchemeStyles.None
   
                           ' Specify formatting settings for the header row.
                           Dim headerRowFormatting As New XlCellFormatting()
                           headerRowFormatting.CopyFrom(cellFormatting)
                           headerRowFormatting.Font.Bold = True
                           headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0)
                           headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0))
   
                           ' Create the header row.
                           Using row As IXlRow = sheet.CreateRow()
                               Using cell As IXlCell = row.CreateCell()
                                   cell.Value = "Region"
                                   cell.ApplyFormatting(headerRowFormatting)
                               End Using
                               Using cell As IXlCell = row.CreateCell()
                                   cell.Value = "Product"
                                   cell.ApplyFormatting(headerRowFormatting)
                               End Using
                               Using cell As IXlCell = row.CreateCell()
                                   cell.Value = "Sales"
                                   cell.ApplyFormatting(headerRowFormatting)
                               End Using
                           End Using
   
                           ' Generate data for the sales report.
                           Dim products() As String = { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" }
                           Dim amount() As Integer = { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 }
                           For i As Integer = 0 To 7
                               Using row As IXlRow = sheet.CreateRow()
                                   Using cell As IXlCell = row.CreateCell()
                                       cell.Value = If(i < 4, "East", "West")
                                       cell.ApplyFormatting(cellFormatting)
                                   End Using
                                   Using cell As IXlCell = row.CreateCell()
                                       cell.Value = products(i Mod 4)
                                       cell.ApplyFormatting(cellFormatting)
                                   End Using
                                   Using cell As IXlCell = row.CreateCell()
                                       cell.Value = amount(i)
                                       cell.ApplyFormatting(cellFormatting)
                                   End Using
                               End Using
                           Next i
   
                           ' Enable AutoFilter for the created cell range.
                           sheet.AutoFilterRange = sheet.DataRange
   
                           ' Specify formatting settings for the total row.
                           Dim totalRowFormatting As New XlCellFormatting()
                           totalRowFormatting.CopyFrom(cellFormatting)
                           totalRowFormatting.Font.Bold = True
                           totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent5, 0.6))
   
                           ' Create the total row.
                           Using row As IXlRow = sheet.CreateRow()
                               Using cell As IXlCell = row.CreateCell()
                                   cell.ApplyFormatting(totalRowFormatting)
                               End Using
                               Using cell As IXlCell = row.CreateCell()
                                   cell.Value = "Total amount"
                                   cell.ApplyFormatting(totalRowFormatting)
                                   cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom))
                               End Using
                               Using cell As IXlCell = row.CreateCell()
                                   ' Add values in the cell range C2 through C9 using the SUBTOTAL function. 
                                   cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(2, 1, 2, 8), XlSummary.Sum, True))
                                   cell.ApplyFormatting(totalRowFormatting)
                               End Using
                           End Using
                       End Using
                   End Using
               End Using
               ' Open the XLSX document using the default application.
               System.Diagnostics.Process.Start("Document.xlsx")
           End Sub
       End Class
   End Namespace
   

5. Run the project.

The following image shows the XLSX file generated after executing the code above (the document is opened in Microsoft® Excel®).