Included with the ActiveReports Excel export filter is the SpreadBuilder API. With this utility, you can create Excel spreadsheets cell by cell for maximum control. This walkthrough illustrates how to create a simple custom spreadsheet cell by cell, and save it to an Excel file.

This walkthrough is split up into the following activities:

Adding the Excel export filter to your project
Adding an ActiveReport.Document reference to the project
Creating a Workbook using code
- Adding a sheet to the Workbook's Sheets collection
- Setting properties on columns and rows in the sheet
- Setting values of cells in the sheet
- Using the Save method to create an Excel file
Viewing the Excel File

When you have completed this walkthrough, a custom Excel file like the following is created in the Bin/Debug subfolder of your project's folder.

To add the export filter to your project

Drag the XlsExport export control from the appropriate Visual Studio toolbox tab onto a Windows Form. (See Adding ActiveReports Controls for information if you haven't added the control to your toolbox.)
The control is visually represented in a component tray below the Windows Form and a reference to ActiveReports.XlsExport is added to your class references.

To add an ActiveReport.Document reference to your project

From the Visual Studio Project menu, select Add New Item.
In the Add New Item window that appears, select ActiveReports 6 (code-based) File and click the Add button. This adds an ActiveReport to the project.
You can delete the ActiveReport, leaving the ActiveReports.Document reference in your class references.

To write the code to create a workbook in Visual Basic or C#

Double-click the title bar of the Windows Form to create an event-handling method for the Form_Load event. Add code to the handler to:

Create a Workbook, and add a sheet to the Workbook's Sheets collection
Set properties on columns and rows in the sheet
Set values of cells in the sheet
Use the Save method to create an Excel file

The following example shows what the code for the method looks like.

Show To write the code in Visual Basic.NET

Visual Basic.NET code. Paste inside the form Load event.	Copy Code
'Dimension a Workbook and add a sheet to its Sheets collection Dim sb As New DataDynamics.SpreadBuilder.Workbook() sb.Sheets.AddNew() 'Set up properties and values for columns, rows, and cells as desired With sb.Sheets(0) .Name = "Customer Call List" 'sets the name of the sheet .Columns(0).Width = 2 * 1440 'sets the width of the 1st column .Columns(1).Width = 1440 .Columns(2).Width = 1440 .Rows(0).Height = 1440 / 4 'Header row .Cell(0, 0).SetValue("Company Name") .Cell(0, 0).FontBold = True .Cell(0, 1).SetValue("Contact Name") .Cell(0, 1).FontBold = True .Cell(0, 2).SetValue("Phone") .Cell(0, 2).FontBold = True 'First row of data .Cell(1, 0).SetValue("GrapeCity") .Cell(1, 1).SetValue("Mortimer") .Cell(1, 2).SetValue("(425) 880-2601") End With 'Save the Workbook to an Excel file sb.Save(Application.StartupPath & "\x.xls") MessageBox.Show("Your Spreadsheet has been saved to " & Application.StartupPath & "\x.xls")

Visual Basic.NET code. Paste inside the form Load event.

Copy Code

     'Dimension a Workbook and add a sheet to its Sheets collection 
     Dim sb As New DataDynamics.SpreadBuilder.Workbook() 
     sb.Sheets.AddNew() 
                
     'Set up properties and values for columns, rows, and cells as desired 
     With sb.Sheets(0)
        .Name = "Customer Call List" 'sets the name of the sheet
        .Columns(0).Width = 2 * 1440 'sets the width of the 1st column
        .Columns(1).Width = 1440 
        .Columns(2).Width = 1440 
        .Rows(0).Height = 1440 / 4 
                 
        'Header row 
        .Cell(0, 0).SetValue("Company Name") 
        .Cell(0, 0).FontBold = True 
        .Cell(0, 1).SetValue("Contact Name") 
        .Cell(0, 1).FontBold = True 
        .Cell(0, 2).SetValue("Phone") 
        .Cell(0, 2).FontBold = True 
                 
        'First row of data 
        .Cell(1, 0).SetValue("GrapeCity") 
        .Cell(1, 1).SetValue("Mortimer") 
        .Cell(1, 2).SetValue("(425) 880-2601") 
     End With 
                
     'Save the Workbook to an Excel file 
     sb.Save(Application.StartupPath & "\x.xls") 
     MessageBox.Show("Your Spreadsheet has been saved to " & Application.StartupPath & "\x.xls")

Show To write the code in C#

C# code. Paste inside the form Load event.	Copy Code
//Dimension a Workbook and add a sheet to its Sheets collection DataDynamics.SpreadBuilder.Workbook sb = new DataDynamics.SpreadBuilder.Workbook(); sb.Sheets.AddNew(); //Set up properties and values for columns, rows and cells as desired sb.Sheets[0].Name = "Customer Call List"; sb.Sheets[0].Columns(0).Width = 2 * 1440; sb.Sheets[0].Columns(1).Width = 1440; sb.Sheets[0].Columns(2).Width = 1440; sb.Sheets[0].Rows(0).Height = 1440/4; //Header row sb.Sheets[0].Cell(0,0).SetValue("Company Name"); sb.Sheets[0].Cell(0,0).FontBold = true; sb.Sheets[0].Cell(0,1).SetValue("Contact Name"); sb.Sheets[0].Cell(0,1).FontBold = true; sb.Sheets[0].Cell(0,2).SetValue("Phone"); sb.Sheets[0].Cell(0,2).FontBold = true; //First row of data sb.Sheets[0].Cell(1,0).SetValue("GrapeCity"); sb.Sheets[0].Cell(1,1).SetValue("Mortimer"); sb.Sheets[0].Cell(1,2).SetValue("(425) 880-2601"); //Save the Workbook to an Excel file sb.Save (Application.StartupPath + @"\x.xls"); MessageBox.Show("Your Spreadsheet has been saved to " + Application.StartupPath + @"\x.xls");

C# code. Paste inside the form Load event.

Copy Code

  //Dimension a Workbook and add a sheet to its Sheets collection
  DataDynamics.SpreadBuilder.Workbook sb = new DataDynamics.SpreadBuilder.Workbook();
  sb.Sheets.AddNew();
  
  //Set up properties and values for columns, rows and cells as desired
  sb.Sheets[0].Name = "Customer Call List";
  sb.Sheets[0].Columns(0).Width = 2 * 1440;
  sb.Sheets[0].Columns(1).Width = 1440;
  sb.Sheets[0].Columns(2).Width = 1440;
  sb.Sheets[0].Rows(0).Height = 1440/4;
  
  //Header row
  sb.Sheets[0].Cell(0,0).SetValue("Company Name");
  sb.Sheets[0].Cell(0,0).FontBold = true;
  sb.Sheets[0].Cell(0,1).SetValue("Contact Name");
  sb.Sheets[0].Cell(0,1).FontBold = true;
  sb.Sheets[0].Cell(0,2).SetValue("Phone");
  sb.Sheets[0].Cell(0,2).FontBold = true;
  
  //First row of data
  sb.Sheets[0].Cell(1,0).SetValue("GrapeCity");
  sb.Sheets[0].Cell(1,1).SetValue("Mortimer");
  sb.Sheets[0].Cell(1,2).SetValue("(425) 880-2601");
  
  //Save the Workbook to an Excel file
  sb.Save (Application.StartupPath + @"\x.xls");
  MessageBox.Show("Your Spreadsheet has been saved to " + Application.StartupPath + @"\x.xls");

To view the Excel File

Press F5 to run the project. A message box informs you of the exact location of the exported x.xls file.
Navigate to the Bin/Debug subfolder of your project's folder and open the XLS file.

ActiveReports 6 Online Help
Basic Spreadsheet with SpreadBuilder
See Also Send comments on this topic.

To add the export filter to your project

To add an ActiveReport.Document reference to your project

To write the code to create a workbook in Visual Basic or C#

To view the Excel File

See Also