Jumping right into developing with a new API can be daunting. Well, at least if you're me it can. Let's walk through the basics together and see what we can do. We're going to make a three-sheet XLSX file complete with tables, a pivot table, and a chart, all in code with NO Microsoft Excel dependency! Woot!
The Basics
There are half a dozen general steps involved here, so let's break it down:
- Install
- Set up your project
- Add logic
- Make it easy on the eyes
- Add sheets and visualization
- 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
Installation is pretty quick. But as a first-time dotnet CLI user, I have to say that opening a command prompt is so much faster than opening Visual Studio. (I also have to say that you want to create a project folder and not use your Documents folder. What a mess that would be! Ahem.)
Prerequisites
You can use Spread.Services in .NET Core, .NET Framework, or Mono projects. I'm using .NET Core.
- Install .NET Core. It has clear instructions, and only takes a couple of minutes.
Create a .NET Core Console Application in Visual Studio, or just use the dotnet CLI (command line interface).
dotnet new consoleIn the console, you'll get a nice "Welcome to .NET Core!" message and links to help.
Spread.Services
You can install the Spread.Services NuGet package using Visual Studio or the dotnet CLI.
Using Visual Studio
- Right-click your project file or Resources (Dependencies in VS17), and click "Manage NuGet Packages." (If you see "Manage NuGet Resources for Solution," you're right-clicking the wrong thing!)
- On the Browse tab, at the top right, set the Package source to nuget.org. (On mine, that was the default.)
- In the Search box, type spread.services.
- Click Spread.Services by GrapeCity, click the Install button to the right, and click OK.
Using dotnet CLI
- Open a cmd window under your project folder.
- Execute this command:
dotnet add package Spread.Services
Set up your project
Add Spread namespaces
From this point on, we're using pure code, which is the same in Visual Studio and dotnet CLI.
- Open the Program.cs file from your project folder.
- Paste these namespaces under the existing using directives.
using GrapeCity.Documents.Spread;
using GrapeCity.Documents.Spread.Drawing;
Create a new workbook
From this point on, all code goes in the main method, replacing Console.WriteLine("Hello World!");
Each subsequent bit of code can follow the last, except for the Save to XLSX bit at the bottom. You want that to come last so your code is processed before you save the Excel file.
In the Program.cs file's Main method, paste these three lines of code between the braces. (Okay, "curly brackets.")
//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";
Add logic
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. (Don't worry if the Evaluation Warning sheet shows up. You can get rid of it when you get a Spread.Services license.)
Make it easy on the eyes
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 need 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 voila! A lovely Tables worksheet complete with individual style elements and a DataBar!
Add sheets and visualization
Here's how you can add some sheets for a pivot table and a chart.
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["H4"], "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;
Voila! A complete pivot table. We could always make it look nicer, but since we covered that in the table, I'll skip that and not turn this article into a novella.
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 Spread.Sheets 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 and IChart members 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");
Well, it's been lovely learning to use Spread.Services with you. Enjoy discovering all of the squintillion variations you can create with it. If you make something cool, share it with us and we can show it off for you!
Kim Landis