Tutorial: Introducing Spreadsheet Document API

The Spreadsheet Document Server is a powerful non visual .NET library, engineered to create and manage Spreadsheet documents natively, without the need for Microsoft Excel to be installed. With intuitive Spreadsheet API, you can produce: invoices, reports, payment schedules and a multitude of other documents, fast, avoiding the slow and very costly Microsoft Office automation.

Overview

To get started we will need to include four (4) assembly references to the project. They could be found in the GAC, or in the default installation, in C:\Program Files (x86)\DevExpress\DXperience 13.1\Bin\Framework folder.

Fig 1. Assembly References

The main object that represents a document is DevExpress.Spreadsheet.Workbook. Workbook gives access to the top level document from which we can apply document Options and Styles, access the Worksheets, as well as handy routines to Load a document from file or to Save it.

Each Workbook consists of multiple Worksheets, and in turn, Worksheets consist of Cells.

Fig 2. Workbook Structure

The following example demonstrates how to create the most basic XLSX file.

Workbook book = new Workbook();
book.Worksheets[0].Cells["B2"].Value = "Hello World";
book.SaveDocument(@"Document.xlsx");

Produced .xlsx File

Fig 3. “Hello World” document previewed in Microsoft Excel.

Working with Cells

From the example above, you might have noticed how intuitive it is to access or update a cell. A Worksheet Cell, may be referenced either via its named coordinates A1, B1, C1 etc… or alternatively via its direct coordinates:

Workbook book = new Workbook();
book.Worksheets[0].Cells[0, 0].Value = 42;

Nota bene: Direct coordinates are zero based.

Cell values accept all the default primitive types: Boolean, Text and Numeric. The built-in implicit operators will ensure the value is of the correct type when set from a .NET type.

book.Worksheets[0].Cells["B2"].Value = DateTime.Now;
book.Worksheets[0].Cells["B2"].NumberFormat = "m/d/yy";
book.Worksheets[0].Cells["B3"].Value = Math.PI;
book.Worksheets[0].Cells["B4"].Value = "Text value...";
book.Worksheets[0].Cells["B5"].Value = true;
book.Worksheets[0].Cells["B6"].Value = float.MaxValue;
book.Worksheets[0].Cells["B7"].Value = 'a';
book.Worksheets[0].Cells["B8"].Value = Int32.MaxValue;

Fig 4. Cell values

Cell Formatting

Each Worksheet Cell, exposes its formatting via the Formatting interface.

public interface Formatting {
    Alignment Alignment { get; }
    Borders Borders { get; }
    Fill Fill { get; }
    Font Font { get; }
}

The following example shows how to set various cell options.

book.Worksheets[0].Cells["B2"].Value = "...Text value";
book.Worksheets[0].Cells["B2"].Alignment.Horizontal = HorizontalAlignment.Right;
book.Worksheets[0].Cells["B2"].Borders.SetAllBorders(Color.Orange, BorderLineStyle.Thick);
book.Worksheets[0].Cells["B2"].Fill.BackgroundColor = Color.WhiteSmoke;
book.Worksheets[0].Cells["B2"].Font.Name = "Vivaldi";
book.Worksheets[0].Cells["B2"].Font.Color = Color.Navy;

You can download the sample from the following link:

https://mega.co.nz/#!iVGBECVE!BV3I-XLxmcxEclipGUN0hEE7F71LJvkseE7bEgnX7v3

• WinForms