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 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
• Creating a Workbook, and 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, you will have created a custom Excel file which can be found in the Bin/Debug (C#) or Bin (VB.NET) subfolder of your project's folder.

Adding the export filter to your project

To add the export filter to your project

1. Drag the XlsExport export control from the appropriate toolbox tab and onto the Windows Form.
2. The control is visually represented in the component tray underneath the Windows Form.

Creating a Workbook

To write the code 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.

  'Visual Basic
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ 
     Handles MyBase.Load
     '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"
         .Columns(0).Width = 2 * 1440
         .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("Data Dynamics")
         .Cell(1, 1).SetValue("Mortimer")
         .Cell(1, 2).SetValue("(614) 895-3142")
      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")
  End Sub
  
  //C#
  private void Form1_Load(object sender, System.EventArgs e)
  {
    //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("Data Dynamics");
    sb.Sheets[0].Cell(1,1).SetValue("Mortimer");
    sb.Sheets[0].Cell(1,2).SetValue("(614) 895-3142");
    //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");
  }
  

Viewing the Excel File

To view the Excel File

1. Press F5 to run the project.
2. Navigate to the Bin/Debug (C#) or Bin (VB.NET) subfolder of your project's folder and open the XLS file.

See Also

 

 

---

Copyright © 2004-2007 Data Dynamics, Ltd. All rights reserved.