Dev Direct Solution Center

Creating an Excel spreadsheet XLS file direct from your .Net app with SpeadsheetGear

Download the complete source code for this solution.

 Note: This solution requires the component to be installed first.
To download the installer, click here

Introduction

In this simple example we show how you can use SpreadsheetGear to output directly from your .Net app into a .XLS file which is 100% MS Excel compatible, without resorting to office automation.

Detail

We will show you how to use C# & VB.Net with SpreadsheetGear to programatically create a workbook (aka group of spreadsheets), add a worksheet to it (the actual spreadsheet with rows and columns) and then add values and formulae into the cells of the sheet before saving it to disk in a Microsoft Excel compatible file.

The program that we create will capture product sales data into a standard datagrid control and then we can save the data into a spreadsheet, adding column totals along the way.

Setup

To access the code for this demo you will need to download zip file linked at the top of this article.

Unpack the zip files (perhaps beneath your Visual Studio "Projects" folder) and then open the file SpreadsheetCreation.csproj for C# or SpreadsheetCreationVB.sln for VB.Net.

Te demos will run without the full evaluation version installed, however, we recommend that you install the evaluatio in order to get SpreadsheetGear working correctly with Visual Studio with full help and Intellisense etc

Creating the Spreadsheet

All of the code that creates and saves the spreadsheet is located in the "save" event of the button control. The datagrid is just a standard datagridview control with predefined columns for Product Name and the 4 quarters.

All of the work is done by the class librray spreadsheetgear.dll, which is already referenced in the project. This gives us the namespace SpreadsheetGear and it it is from here that we can declare an instance of the IWorkbook object which is the conceptual container for a group of IWorksheet objects that actually contain spreadsheet values and formulae.

We create our workbook & worksheet instance as follows:

C#
  
// Create a new workbook.
  SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
  SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
  SpreadsheetGear.IRange cells = worksheet.Cells;

VB
  
' Create a new workbook.
  Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
  Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets("Sheet1")
  Dim cells As SpreadsheetGear.IRange = worksheet.Cells

Note that in the above code we also create an object called cells, which is used throughout the remaining code to provide references into individual cells.

Having set up our basic workbook structure we can now start adding content into the cells on the worksheet.We start off by adding headings into the columns (A, B, C etc) of the first row (1) via the cells object, passing a cell address in the form of "A1" meaning column A, row 1:

C#
  
// Set the worksheet name.
  worksheet.Name = DateTime.Now.Year.ToString() + " Sales";

  
//Adding a title to the sheet
  cells["A1"].Formula = worksheet.Name;

  
// Load column titles and center.
  cells["B1"].Formula = "Q1";
  cells["C1"].Formula = "Q2";
  cells["D1"].Formula = "Q3";
  cells["E1"].Formula = "Q4";

VB
  
' Set the worksheet name. 
  worksheet.Name = DateTime.Now.Year.ToString() + " Sales"

  
'Adding a title to the sheet 
  cells("A1").Formula = worksheet.Name

  
' Load column titles and center. 
  cells("B1").Formula = "Q1"
  cells("C1").Formula = "Q2"
  cells("D1").Formula = "Q3"
  cells("E1").Formula = "Q4"

Note that in the above code we first set the worksheet Name property which sets the value that appears in the tab at the bottom of the worksheet when viewed in MS Excel.

Values are added into each cell using the Formula property and passing in the specific address of each cell .

Next we apply some formating to differentiate the cells that we have just filled as headers. The font of each cell can be controlled via the Font property and alignment is set via HorizontalAlignment property and the SpreadsheetGear.HAlign enumerator:

C#
  
//Format the header cells
  cells["A1:E1"].HorizontalAlignment = SpreadsheetGear.HAlign.Center;
  cells["A1:E1"].Font.Bold = true;

VB
  
'Format the header cells 
  cells("A1:E1").HorizontalAlignment = SpreadsheetGear.HAlign.Center
  cells("A1:E1").Font.Bold = True

This time the cells were addressed as a range - A1 to E1 in a single operation.

Having set up the headers for the sheet, we can now add in the data from the datagridview, which we do by simply looping the rows and columns of the datagridview passing each value to the sheet.

C#
  
//Transfer value from the grid to the spreadsheet
  int y;
  for (y = 0; y < this.dataGridView1.Rows.Count - 1; y++)
    for (int x = 0; x < 5; x++)
      cells[y+1, x].Formula = this.dataGridView1.Rows[y].Cells[x].Value.ToString();

VB
  
'Transfer value from the grid to the spreadsheet 
  Dim y As Integer
  For y = 0 To Me.dataGridView1.Rows.Count - 2
    For x As Integer = 0 To 4
      cells(y + 1, x).Formula = Me.dataGridView1.Rows(y).Cells(x).Value.ToString()
    Next
  Next

In this case note that we have addressed the cells using numerical co-ordinates rather than the column/row reference used previously.

Having added all the data we can now add an equation to the foot of each column so that the sheet itself can be made to do the totalling:

C#
  y++;
  
//Add totals to the foot of each quater
  cells[y, 0].Formula = "Totals";
  cells[y, 1].Formula = "=sum(B2:B" + y.ToString() + ")";
  cells[y, 2].Formula = "=sum(C2:C" + y.ToString() + ")";
  cells[y, 3].Formula = "=sum(D2:D" + y.ToString() + ")";
  cells[y, 4].Formula = "=sum(E2:E" + y.ToString() + ")";

VB
  y += 1
  
'Add totals to the foot of each quater 
  cells(y, 0).Formula = "Totals"
  cells(y, 1).Formula = "=sum(B2:B" + y.ToString() + ")"
  cells(y, 2).Formula = "=sum(C2:C" + y.ToString() + ")"
  cells(y, 3).Formula = "=sum(D2:D" + y.ToString() + ")"
  cells(y, 4).Formula = "=sum(E2:E" + y.ToString() + ")"

We use the row counter y from the previous exercise to specify the location of the totals row. We use this as the y co-ordinate while specifying the x co-ordinate literally to generate a reference to the cell whose formula we then set to use the Sum() function.

The speadsheet is now complete and all we have to do is save the output. We use a standard SaveDialog control to enable the user to locate the output and then we call the SaveAs method of the Workbook object generate the .xls file.

C#
  
//Save the workbook to a file
  dlgSave.CheckPathExists = true;
  dlgSave.DefaultExt = "xls";
  dlgSave.Filter = "Microsoft Excel|*.xls";
  dlgSave.InitialDirectory = Application.StartupPath;
  if (dlgSave.ShowDialog() == DialogResult.OK)
    workbook.SaveAs(dlgSave.FileName, SpreadsheetGear.FileFormat.XLS97);

VB
  
'Save the workbook to a file 
  dlgSave.CheckPathExists = True
  dlgSave.DefaultExt = "xls"
  dlgSave.Filter = "Microsoft Excel|*.xls"
  dlgSave.InitialDirectory = Application.StartupPath
  If dlgSave.ShowDialog() = DialogResult.OK Then
      workbook.SaveAs(dlgSave.FileName, SpreadsheetGear.FileFormat.XLS97)
  End If

Conclusion

In this example we have created a spreadsheet from scratch from within our C#/Vb.Net code using the SpreadsheetGear class library. We have added a worksheet and populated cells with values and formulae. We have also performed some minor formating. Finally we generated a fully Microsoft Excel-compatible worksheet file contianing our results.

In addition to what we have demostrated here, the SpreadsheetGear component is capable doing more or less eveything that can you can do in a normal spreadsheet application.