How to generate Excel spreadsheets in code with Documents for Excel

MESCIUS's Documents for Excel API is a powerful tool for generating and modifying spreadsheets, all in code, with no Excel dependencies. Let's use it to make a three-sheet XLSX file complete with tables, a pivot table, and a chart, all in code.

Excel API basics

There are half a dozen general steps involved here, so let's break it down:

1. Install
2. Set up your project
3. Create tables
4. Apply styles and conditional formatting
5. Add a pivot table
6. Add a chart
7. Save it to XLSX

By the way, you might want to put in the Save to XLSX bit at the bottom early on, and insert the other code above it. Saving the workbook to XLSX format as you go lets you preview each bit of code and see how it looks. If you want to tweak something, you can look it up in the Assembly Reference available in the online documentation.

Install Documents for Excel

You can also watch the videos in "Get started with Documents for Excel in Windows, Mac, and Linux" to install Documents for Excel, set up your project, and create a document.

Set up your project

Once you've installed and set up your project and first workbook, we can begin adding a worksheet and a table:

//Create a new workbook.
Workbook workbook = new Workbook();
// Add a new worksheet to the workbook.
IWorksheet worksheet = workbook.Worksheets[0];
workbook.Worksheets[0].Name = "Tables";

Initialize data

To set up data for the worksheet, prepare a two-dimensional array and assign it to the Value property of your worksheet ranges.

NOTE: You can get the data from any external data source, like SQL Server or any back-end database, CRM system, barcode scanner, lab equipment, or whatever you use.

IRange.Value can take an object that contains an array of arrays of values to set the whole range at once. In your real projects, you can connect to any data source, grab a DataSet, and then use code like this to make a big two-dimensional array of type object, size the range to fit the data, and fill it in with the values from the dataset.

//This code isn't for our example, but you can use it in real-life scenarios
//Insert connection string and code to create a DataSet here
object[,] data = new object[rowCount, columnCount];

for (int row=0; row<rowCount; row++)
for (int column=0; column<columnCount; column++)
 data[row,column] = dataTable.Rows[row][column];

worksheet.Range[row, column, rowCount, columnCount].Value = data;

For this tutorial, you can just hard-code the data like this. (Paste this code in the Main method, below the workbook creation code.)

//Create data for the first table.
worksheet.Range["B3:C7"].Value = new object[,]
{
    { "ITEM", "AMOUNT" },
    { "Income 1", 2500 },
    { "Income 2", 1000 },
    { "Income 3", 250 },
    { "Other", 250 },
};

//Create data for the second table.
worksheet.Range["B10:C23"].Value = new object[,]
{
    { "ITEM", "AMOUNT" },
    { "Rent/mortgage", 800 },
    { "Electricity", 120 },
    { "Gas", 50 },
    { "Cell phone", 45 },
    { "Groceries", 500 },
    { "Car payment", 273 },
    { "Auto expenses", 120 },
    { "Student loans", 50 },
    { "Credit cards", 100 },
    { "Auto Insurance", 78 },
    { "Personal care", 50 },
    { "Entertainment", 100 },
    { "Miscellaneous", 50 },
};

//Add labels and calculations for each range.
worksheet.Range["B2:C2"].Merge();
worksheet.Range["B2"].Value = "MONTHLY INCOME";
worksheet.Range["B9:C9"].Merge();
worksheet.Range["B9"].Value = "MONTHLY EXPENSES";
worksheet.Range["E2:G2"].Merge();
worksheet.Range["E2"].Value = "PERCENTAGE OF INCOME SPENT";
worksheet.Range["E5:G5"].Merge();
worksheet.Range["E5"].Value = "SUMMARY";
worksheet.Range["E3:F3"].Merge();
worksheet.Range["E9"].Value = "BALANCE";
worksheet.Range["E6"].Value = "Total Monthly Income";
worksheet.Range["E7"].Value = "Total Monthly Expenses";

Create tables

Add two tables, Income and Expenses, and apply a built-in table style to each.

//Create the first table to show Income.
ITable incomeTable = worksheet.Tables.Add(worksheet.Range["B3:C7"], true);
incomeTable.Name = "tblIncome";
incomeTable.TableStyle = workbook.TableStyles["TableStyleMedium4"];

//Create the second table to show Expenses.
ITable expensesTable = worksheet.Tables.Add(worksheet.Range["B10:C23"], true);
expensesTable.Name = "tblExpenses";
expensesTable.TableStyle = workbook.TableStyles["TableStyleMedium4"];

For more information on what you can do with tables, see Use Table in the documentation.

Set formulas for the tables

First, create a name for each table's summary for the month:

worksheet.Names.Add("TotalMonthlyIncome", "=SUM(tblIncome[AMOUNT])");
worksheet.Names.Add("TotalMonthlyExpenses", "=SUM(tblExpenses[AMOUNT])");

Then add formulas to calculate the total monthly income, total monthly expenses, percentage of income spent, and balance.

worksheet.Range["E3"].Formula = "=TotalMonthlyExpenses";
worksheet.Range["G3"].Formula = "=TotalMonthlyExpenses/TotalMonthlyIncome";
worksheet.Range["G6"].Formula = "=TotalMonthlyIncome";
worksheet.Range["G7"].Formula = "=TotalMonthlyExpenses";
worksheet.Range["G9"].Formula = "=TotalMonthlyIncome-TotalMonthlyExpenses";

For more information on what you can do with formulas, see Manage Formulas in the documentation.

If you run your project now and open the saved Excel file, you can see that it's correct, but it doesn't look great.

Design your spreadsheet

You have control over every line, whisker, tick, position, size, font, and color. We don't promise that your choice of Showcard Gothic font in lime green on an orange background will actually BE easy on the eyes, but you'll have the opportunity to try it out and see. (Yeeks!)

Okay, now we can do some more reasonable types of formatting to make the spreadsheet look nicer.

Set row heights and column widths

Set a default row height and column width for the whole worksheet using StandardHeight and StandardWidth.

worksheet.StandardHeight = 26.25;
worksheet.StandardWidth = 8.43;

You can control heights and widths for specific rows and columns by setting the RowHeight and ColumnWidth of each Range.

worksheet.Range["2:24"].RowHeight = 27;
worksheet.Range["A:A"].ColumnWidth = 2.855;
worksheet.Range["B:B"].ColumnWidth = 33.285;
worksheet.Range["C:C"].ColumnWidth = 25.57;
worksheet.Range["D:D"].ColumnWidth = 1;
worksheet.Range["E:F"].ColumnWidth = 25.57;
worksheet.Range["G:G"].ColumnWidth = 14.285;

If you save off an XLSX file at this point, it's looking a little better, but still needs more tweaking.

Set styles

You can modify the built-in styles (or create custom styles) for your workbook to affect all cells using that named style.

//Change the currency style to your precise preferences
IStyle currencyStyle = workbook.Styles["Currency"];
currencyStyle.IncludeAlignment = true;
currencyStyle.HorizontalAlignment = HorizontalAlignment.Left;
currencyStyle.VerticalAlignment = VerticalAlignment.Bottom;
currencyStyle.NumberFormat = "$#,##0.00";

//Change the heading 1 style  
IStyle heading1Style = workbook.Styles["Heading 1"];
heading1Style.IncludeAlignment = true;
heading1Style.HorizontalAlignment = HorizontalAlignment.Center;
heading1Style.VerticalAlignment = VerticalAlignment.Center;
heading1Style.Font.Name = "Century Gothic";
heading1Style.Font.Bold = true;
heading1Style.Font.Size = 11;
heading1Style.Font.Color = Color.White;
heading1Style.IncludeBorder = false;
heading1Style.IncludePatterns = true;
heading1Style.Interior.Color = Color.FromRGB(32, 61, 64);

//Change the percent style
IStyle percentStyle = workbook.Styles["Percent"];
percentStyle.IncludeAlignment = true;
percentStyle.HorizontalAlignment = HorizontalAlignment.Center;
percentStyle.IncludeFont = true;
percentStyle.Font.Color = Color.FromRGB(32, 61, 64);
percentStyle.Font.Name = "Century Gothic";
percentStyle.Font.Bold = true;
percentStyle.Font.Size = 14;

Now you can apply your customized styles to specific ranges (and turn off the grid lines for the sheet).

//Don't show grid lines
worksheet.SheetView.DisplayGridlines = false;

//Apply customized styles to ranges of cells
worksheet.Range["C4:C7, C11:C23, G6:G7, G9"].Style = currencyStyle;
worksheet.Range["B2, B9, E2, E5"].Style = heading1Style;
worksheet.Range["G3"].Style = percentStyle;

Run it now and it looks like this:

You can also set individual style elements for specific ranges.

//Set individual style elements for ranges
worksheet.Range["E6:G6"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Medium;
worksheet.Range["E6:G6"].Borders[BordersIndex.EdgeBottom].Color = Color.FromRGB(32, 61, 64);

worksheet.Range["E7:G7"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Medium;
worksheet.Range["E7:G7"].Borders[BordersIndex.EdgeBottom].Color = Color.FromRGB(32, 61, 64);

worksheet.Range["E9:G9"].Interior.Color = Color.FromRGB(32, 61, 64);
worksheet.Range["E9:G9"].HorizontalAlignment = HorizontalAlignment.Left;
worksheet.Range["E9:G9"].VerticalAlignment = VerticalAlignment.Center;
worksheet.Range["E9:G9"].Font.Name = "Century Gothic";
worksheet.Range["E9:G9"].Font.Bold = true;
worksheet.Range["E9:G9"].Font.Size = 11;
worksheet.Range["E9:G9"].Font.Color = Color.White;

worksheet.Range["E3:F3"].Borders.Color = Color.FromRGB(32, 61, 64);

Add conditional formatting

Here is some conditional formatting (although the data bar is only in one cell, so it's not as conditional as this example). Still, it works with the data we have so far, so I'll leave it.

//Add a data bar, and specify min and max
IDataBar dataBar = worksheet.Range["E3"].FormatConditions.AddDatabar();
dataBar.MinPoint.Type = ConditionValueTypes.Number;
dataBar.MinPoint.Value = 1;
dataBar.MaxPoint.Type = ConditionValueTypes.Number;
dataBar.MaxPoint.Value = "=TotalMonthlyIncome";

dataBar.BarFillType = DataBarFillType.Gradient;
dataBar.BarColor.Color = Color.Red;
dataBar.ShowValue = false;

And here you have it: A tables worksheet complete with individual style elements and a DataBar!

Create a pivot table

First we'll add a sheet for the pivot table.

//Add a new sheet to the workbook
IWorksheet worksheet2 = workbook.Worksheets.AddAfter(worksheet);
worksheet2.Name = "Pivot Table";

And create a data cache for it.

//Create a data cache for the pivot table on sheet2
object[,] sourceData = new object[,]
{
   { "Order ID", "Product",  "Category",   "Amount", "Date",                    "Country" },
   { 1,          "Carrots",  "Vegetables",  4270,    new DateTime(2017, 9, 6),  "United States" },
   { 2,          "Broccoli", "Vegetables",  8239,    new DateTime(2017, 9, 7),  "United Kingdom" },
   { 3,          "Banana",   "Fruit",       617,     new DateTime(2017, 9, 8),  "United States" },
   { 4,          "Banana",   "Fruit",       8384,    new DateTime(2017, 9, 10), "Canada" },
   { 5,          "Beans",    "Vegetables",  2626,    new DateTime(2017, 9, 10), "Germany" },
   { 6,          "Orange",   "Fruit",       3610,    new DateTime(2017, 9, 11), "United States" },
   { 7,          "Broccoli", "Vegetables",  9062,    new DateTime(2017, 9, 11), "Australia" },
   { 8,          "Banana",   "Fruit",       6906,    new DateTime(2017, 9, 16), "New Zealand" },
   { 9,          "Apple",    "Fruit",       2417,    new DateTime(2017, 9, 16), "France" },
   { 10,         "Apple",    "Fruit",       7431,    new DateTime(2017, 9, 16), "Canada" },
   { 11,         "Banana",   "Fruit",       8250,    new DateTime(2017, 9, 16), "Germany" },
   { 12,         "Broccoli", "Vegetables",  7012,    new DateTime(2017, 9, 18), "United States" },
   { 13,         "Carrots",  "Vegetables",  1903,    new DateTime(2017, 9, 20), "Germany" },
   { 14,         "Broccoli", "Vegetables",  2824,    new DateTime(2017, 9, 22), "Canada" },
   { 15,         "Apple",    "Fruit",       6946,    new DateTime(2017, 9, 24), "France" },
};

Here's the code to set up ranges, a pivot cache, and the pivot table.

//Set up ranges
worksheet2.Range["A1:F16"].Value = sourceData;
worksheet2.Range["A:F"].ColumnWidth = 15;
worksheet2.Range["H:I"].ColumnWidth = 15;

//Create a pivot cache and pivot table
var pivotcache = workbook.PivotCaches.Create(worksheet2.Range["A1:F16"]);
var pivottable = worksheet2.PivotTables.Add(pivotcache, worksheet2.Range["A21"], "pivottable1");

If you stop right here, you can see that you get the data, and a kind of DIY pivot table.

We can do better than that! Add code like this to add pivot table fields and set the orientation for each.

//Configure pivot table fields
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.RowField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;

var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.PageField;

Now we've finished a complete pivot table. We could always make it look nicer, but since we covered that in the table, we'll skip that part.

To learn more about pivot tables, see Use Pivot Table in the documentation, and the Pivot Table section of the online demo.

Add a chart

Before adding the chart, add a new sheet to put it on. See how this gets easier with repetition?

//Add a new sheet to the workbook
IWorksheet worksheet3 = workbook.Worksheets.AddAfter(worksheet2);
worksheet3.Name = "Chart";

A chart in Docs for Excel is essentially a shape object. To set up the type of chart you want to create, you first create a shape, then add a chart to it. The AddChart parameters let you specify the ChartType, and the location and size. Specify the location in points for the left, and top positions, and the size with width, and height.

//Set up chart
IShape shape = worksheet3.Shapes.AddChart(ChartType.ColumnClustered, 300, 10, 300, 300);

Now you can set the chart title, add data, and create the chart.

//Set chart title
shape.Chart.ChartTitle.Text = "Sales Increases Over Previous Quarter";

//Add data for chart
worksheet3.Range["A1:D6"].Value = new object[,]
{
   {null, "Q1", "Q2", "Q3"},
   {"Belgium", 10, 25, 25},
   {"France", -51, -36, 27},
   {"Greece", 52, -85, -30},
   {"Italy", 22, 65, 65},
   {"UK", 23, 69, 69}
};

//Create chart
shape.Chart.SeriesCollection.Add(worksheet3.Range["A1:D6"], RowCol.Columns, true, true);

Here's what we have at this point.

To make the data table look nicer and to format the numbers, you can apply formatting to specific ranges. Let's format the numbers on the chart's value axis while we're at it.

//Format data table
worksheet3.Range["B1:D1"].HorizontalAlignment = HorizontalAlignment.Right;
worksheet3.Range["B1:D1"].Font.Bold = true;
worksheet3.Range["B2:D6"].NumberFormat = "€#,##0";

//Format axis numbers
IAxis value_axis = shape.Chart.Axes.Item(AxisType.Value);
value_axis.TickLabels.NumberFormat = "€#,##0";

And here it is, our nicely formatted chart in our workbook of three sheets.

To learn more about charts, see Use Charts in the documentation, and the Charts section of the online demo.

Save it to XLSX

And finally, the payoff. Export it all to an Excel file so you can see what you've wrought. Be sure that this line of code comes AFTER all of your other code, so that all of it processes before it's saved.

//Save to an Excel file
 workbook.Save("3-sheet-sample.xlsx");

And that's how you can create a simple application in Document Solutions for Excel.

Try it today:

.NET

Java