Walkthrough: Using SpreadBuilder

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

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

Click on the XlsExport export control in the appropriate toolbox to select it.
Drag the control onto the Windows Form.
The control will appear in the tray underneath the Windows Form.

Creating a Workbook

To write the code in Visual Basic

Right-click on Form1, and click on View Code to display the code view for the form. At the top left of the code view for Form1, click the drop-down arrow and select (Base Class Events). At the top right of the code window, click the drop-down arrow and select Load. This creates an event-handling method for the Form1_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

To write the code in C#

Click on the blue section at the top of Form1 to select the form. Click on the events icon in the Properties window to display available events for Form1. Double-click Load. This creates an event-handling method for the Form1_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")
	MsgBox("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");
}