Introduction
It is time for another development component review. Last June, I reviewed the Aspose.Email for .NET component and demonstrated how to work with email messages in PST files or via IMAP. Well, I am back again for a look at another Aspose package. This one is Aspose.Cells for .NET.
Aspose.Cells provides full access to hundreds of Excel file features without the need to install or distribute Microsoft Excel.
Aspose.Cells Features
For a full list of features, visit the Aspose.Cells site. Here is a summary of some of the highlights.
File Manipulation
There are so many file formats supported by Aspose.Cells including Excel (XLS, XLSM, etc.), HTML, CSV, Tab Delimited and PDF. Developers can open/save files, encrypt saved files, convert Excel documents to PDF or even save a worksheets as a SVG file. You can also manipulate the file properties of existing Excel files.
Need to capture part of a workbook into an image file? There are APIs available to export Charts and entire Worksheets to an image, which can then be saved or manipulated like any other bitmap in .NET.
Worksheets, Rows and Columns
Aspose.Cells has Worksheet APIs to add and remove worksheets from workbooks. New worksheets can also be added to an existing PDF file with these APIs. Within a sheet, developers can manipulate scrollbar visibility, tabs, zoom factor as well as freezing and splitting panes on a sheet.
You can also take advantage of the APIs for working with rows and columns on a sheet. Your application can insert, delete, copy, hide/unhide, and adjust height/width.
Data and Formatting
Aspose.Cells can import data into worksheets from many different sources, including:
- Arrays and ArrayLists
- Custom .NET Objects and Collections
- DataTable / DataView / DataColumn
- Manual Data Entry
Once data is in a worksheet, it can be sorted, accessed and searched upon.
Aspose.Cells includes a formula engine, which can run with formulas embedded in existing spreadsheets or with new formulas created at runtime.
Think of any kind of data formatting you can perform in a cell in Excel, and you can probably do it with these APIs ... fonts, colors, text formatting ... you name it.
Tables and Charts
Lists can easily be formatted as tables on a worksheet with Aspose.Cells. Once part of a table, the data can be styled, formatted, grouped, and summed.
Charts are one of the most powerful features in Excel and Aspose.Cells. I’ll be showing an example of the charting API in my sample application below. Additional features not shown in my app include 3D formatting, inserting controls into charts (labels, pictures, textboxes), and Excel Sparklines.
Multi-Platform Support
Not only are there tons of features in the Aspose.Cells library, but you can access those features from all of these platforms:
- .NET Framework
- PHP
- Python
- Mono
Sample App – Data and Pivots and Charts, Oh My!
Just like the last time I started out working on an application with Aspose, I immediately cracked open the Examples Dashboard to find out how to implement the features I needed for my app.
Other NuGet Packages
I used a couple of other NuGet packages to help make my application development a little bit simpler.
MVVM Light – This is my go-to MVVM helper framework. It makes binding and messaging a breeze in WPF and other XAML applications.
CsvHelper – This package provides handy APIs for working with CSV files. Because I wanted to import my data from a CSV file into a .NET collection. I could have imported the CSV data directly with Aspose.Cells, but I wanted to see how the library worked with collections in case my data was coming from another source like a REST service or other API.
The App
The application itself is relatively simple. I first load some data from a CSV file that contains a list of sites and authors and the number of visits each had during a particular quarter. I load this into a List<AuthorSummary>
collection with CsvHelper and import the collection to a sheet in a workbook I create in memory with Aspose.Cells.
_dataDir = Path.GetFullPath("../../../Data/");
bool IsExists = Directory.Exists(_dataDir);
if (!IsExists)
Directory.CreateDirectory(_dataDir);
_workbook = new Workbook();
Worksheet worksheet = _workbook.Worksheets[0];
var authorSummaries = new List<AuthorSummary>();
using (var csv = new CsvReader(new StreamReader("SitesChartSample.csv"), new CsvHelper.Configuration.CsvConfiguration { HasHeaderRecord = true, Delimiter = "," }))
{
while (csv.Read())
{
var authorSummary = new AuthorSummary
{
Site = csv.GetField<string>(0),
Author = csv.GetField<string>(1),
Visits = csv.GetField<int>(2),
Quarter = csv.GetField<string>(3)
};
authorSummaries.Add(authorSummary);
}
}
var options = new ImportTableOptions { InsertRows = true };
worksheet.Cells.ImportCustomObjects(authorSummaries, 1, 0, options);
The next step is to create a pivot table. I want a better visualization of the number of visits each site is getting per quarter, but I still want to see the breakdown by author. I add a new PivotTable
object with Aspose.Cells and point it to the range of cells to use as the source data. I then tell it which of the fields to use as the pivot Rows, Columns and Data.
Aspose.Cells.Pivot.PivotTableCollection pivotTables = worksheet.PivotTables;
int index = pivotTables.Add("=A2:D26", "F2", "VisitsBySiteAndQuarter");
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
pivotTable.RowGrand = false;
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);
pivotTable.CalculateData();
The final step is to create a chart based on the summary data in the pivot table. I use Aspose.Cells to add a new Chart object to the worksheet and then add each series of data elements that is to be part of the chart.
int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Pyramid, 2, 12, 14, 18);
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];
chart.NSeries.Add("H10:K10", false);
chart.NSeries[0].Name = "C#";
chart.NSeries.Add("H16:K16", false);
chart.NSeries[1].Name = "VB";
chart.NSeries.Add("H20:K20", false);
chart.NSeries[2].Name = "F#";
chart.NSeries.Add("H31:K31", false);
chart.NSeries[3].Name = "JavaScript";
chart.Title.Text = "Quarterly Visits By Site";
One last bit I threw in was some styling/formatting. I wanted to see how easily I could change the appearance of a chart. Most of this code is taken from one of the samples in the Examples Dashboard application installed with Aspose.Cells.
private void SetChartAppearance(Aspose.Cells.Charts.Chart chart)
{
chart.PlotArea.Area.ForegroundColor = Color.Blue;
chart.ChartArea.Area.ForegroundColor = Color.Yellow;
chart.NSeries[0].Area.ForegroundColor = Color.Red;
chart.NSeries[0].Points[0].Area.ForegroundColor = Color.Cyan;
chart.NSeries[3].Area.FillFormat.SetOneColorGradient(Color.Lime, 1, Aspose.Cells.Drawing.GradientStyleType.Horizontal, 1);
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent6, 0.6);
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
}
And here is the output of the chart when exported to an image and displayed on my WPF form:
Ok, so I’m no graphic designer. I am a developer after all.
Exporting Charts and Saving Files
When the WPF form loads and the ViewModel prepares the data for the chart, it then exports the chart to a Bitmap object which gets converted to a BitmapSource which can be bound to a WPF Image control on the form. I found this handy image conversion code on StackOverflow.
ChartImage = LoadBitmap(chart.ToImage());
[DllImport("gdi32")]
private static extern int DeleteObject(IntPtr o);
private static BitmapSource LoadBitmap(Bitmap image)
{
IntPtr pointer = image.GetHbitmap();
BitmapSource bitmapSource;
try
{
bitmapSource = System.Windows.Interop.Imaging.CreateBitmapSourceFromHBitmap(pointer,
IntPtr.Zero, Int32Rect.Empty,
BitmapSizeOptions.FromEmptyOptions());
}
finally
{
DeleteObject(pointer);
}
return bitmapSource;
}
Two buttons on the form are bound to commands to save the workbook as either an Excel file (.xls) or PDF document. The code to perform each of these actions is similar and very intuitive. I also added some code to launch each file after saving so I could view the results.
private void SavePDF()
{
_workbook.Save(_dataDir + "book1.pdf", SaveFormat.Pdf);
Process.Start(_dataDir + "book1.pdf");
}
private void SaveXLS()
{
_workbook.Save(_dataDir + "book1.xls");
Process.Start(_dataDir + "book1.xls");
}
The full source code for this project can be found here. The trial Aspose.Cells product is fully functional but adds watermarks to all of the files created.
Summary
If you’re in the market for a library to give your spreadsheet application a jump-start, Aspose.Cells should be at the top of your list. I found the APIs really intuitive and the documentation and examples are thorough and comprehensive. I have really enjoyed my second experience with an Aspose product.
Happy Coding!
Disclosure of Material Connection: I received one or more of the products or services mentioned above for free in the hope that I would mention it on my blog. Regardless, I only recommend products or services I use personally and believe my readers will enjoy. I am disclosing this in accordance with the Federal Trade Commission’s 16 CFR, Part 255: "Guides Concerning the Use of Endorsements and Testimonials in Advertising."