Sunday, February 24, 2013

Using Aspose.Cells

I use a couple 3rd party add-ons for my software.  Being able to import and create spreadsheets is a huge productivity boost for my company.  I began using Aspose products in 2008 and our company has added Words and Project to our libraries.  The Aspose website can be found by clicking here.  Their documentation is excellent, and you can download their libraries and use them without paying for them for evaluation purposes.  The Aspose.Cells library will create an excel spreadsheet with some text in the first tab indicating that this is for evaluation purposes only.  When you purchase a license, you can read the license file using the following code:

License license = new License();

I normally create a "license" folder inside my deployable project so that I can keep them neat and tidy (and away from the bin folder).

The Aspose libraries come with an installer and you can then add the reference using the "Assemblies" section of the reference add window in Visual Studio.  Once you have added the reference you'll need to add the using:

using Aspose.Cells;

If you go to Aspose's website, you can find the documentation called "Your first application.." under the "Programmers Guide."  An example from this documentation looks like this:

//Instantiate a Workbook object that represents Excel file.
Workbook wb = new Workbook();

//Note when you create a new workbook, a default worksheet //"Sheet1" is

//added (by default) to the workbook.
//Access the first worksheet "Sheet1" in the book.
Worksheet sheet = wb.Worksheets[0];

//Access the "A1" cell in the sheet.

Cell cell = sheet.Cells["A1"];

//Input the "Hello World!" text into the "A1" cell

cell.PutValue("Hello World!");

//Save the Excel file.

wb.Save("c:\\MyBook.xls", SaveFormat.Excel97To2003);

This example will save a file named "MyBook.xls" in the root directory of the "C:" drive of your PC.  If you're calling this from a website and you want it to pop-up from your site, you can replace the "Save" method above with the following code:

wb.Save("MyBook.xls", FileFormatType.Excel2003, SaveType.OpenInExcel, HttpContext.Current.Response);

So now it will send the excel file to the browser which will ask if you want to open or save to the download folder.

Now for a couple of tricks.  First, if you want to use a custom color for your cells (I use a different color for the background of my header row), you'll need to declare the color first (Color is contained in "System.Drawing" so you'll need to include that in your using statement):

wb.ChangePalette(Color.FromArgb(221, 208, 190), 55);

Then you can use this color to paint your cell (Excel calls it the foreground color):

sheet.Cells["A1"].Style.ForegroundColor = Color.FromArgb(255, 221, 208, 190);

Another trick is that you can freeze a row (or column).  The code to freeze the top row looks like this:

sheet.FreezePanes(0, 0, 1, 50);

This will freeze the top row so that it doesn't scroll when the user scrolls the data in the spreadsheet.

There are many more features that are convenient for producing a spreadsheet include column widths, row heights, cell formats (you can duplicate any Excel data format using information on this page).  Finally, you can read Excel workbooks.  Our company uses this feature to transfer data from one screen to another, or read raw data exported from other systems.  Our company also produces pie charts using this product.

I hope this information helps you with your project.  Drop me a message if you have questions.