Purpose

 

This document is designed to provide the developers with a detailed perceptive on how to create a workbook to input data into a worksheet and apply some formatting to the cells in the rows and columns of the worksheet using Aspose.Cells component.

 

In this Article

 

Here is a quick look what the article is all about:

• Working with Worksheet Formatting
• Using Styles to quickly Format data
• Formatting Cells in Rows and Columns
• Using Borders and Colors to emphasize data
• Applying Number Formats to carry more weight to data
• Using Fonts and Attributes to bring data to light
• Formatting data in a named Range of Cells
• Changing the Alignment and Orientation of data
• Setting Row height and Column width to adjust the contents

 

Note: We demonstrate a project which will perform all these tasks using the API of Aspose.Cells component.

 

Data Formatting

To distinguish between different types of information on a worksheet, for the optimal display of the data on your worksheet and to make a worksheet easier to scan, you format the worksheet. A Format represents a style and is defined as a set of characteristics, such as fonts and font sizes, number formats, cell borders, cell shading with solid background color or a specific color pattern, indentation, alignment and text orientation in the cells.

For enhanced visibility to draw attention to specific data and to give your worksheet a professional look you should choose the right combination of fonts, font styles, size, color and other attributes which can make your data or numbers in the cells jump right off the worksheet. Similarly, borders provide you further ways to highlight information in the cells in a Worksheet. A Border is a line drawn around a cell or a group of cells. There is another way called Number Formats, which is also used to make your data more meaningful. By applying different Number Formats, you can change the appearance of numbers without changing the number behind the appearance.

You might try Office Automation but Office automation has its own drawbacks. There are several reasons and issues involved: E.g., Security, Stability, Scalability/Speed, Price, Features etc. In Short, there are many reasons, with the top one being that Microsoft themselves strongly recommends against Office automation from software solutions. You come across different solutions / components in the market with the assertion of performing the tasks. But the question is whether the component (you choose) can really perform the tasks with excellence. You might be looking at the component's speed to perform the tasks, performance in the diverse environment, quality of the results and more importantly reliability related your scenarios.

I use Aspose.Cells for the tasks mentioned above. Aspose.Cells provides you the flexibility and feasibility to draw borders around cells and range of cells with ease. Moreover the component is well versed to apply fonts with attributes and shade the cells. The component is efficient enough that you can format a complete row or column, set alignments, wrap and rotate the text in the cells. Aspose.Cells supports to apply all types of Number Formats including General format, numbers in Decimal notations, numbers with Currency symbols, numbers as a Percentage of 100, numbers in Scientific format, numbers in DateTime values and Custom Number format.

In this article, we create a console application in Visual Studio.Net that will generate the annual Sales Report. We create a workbook from the scratch, insert data into cells in a worksheet related the report and format the worksheet in it using Aspose.Cells API.

Aspose.Cells :  The Real Product

I would take this opportunity to introduce the product to you. Aspose.Cells is an Excel® spreadsheet reporting component that enables you to read and write Excel® spreadsheets without utilizing Microsoft Excel® installed either on the client or server side. Aspose.Cells is a very feature rich component that offers much more than just basic exporting of data. With Aspose.Cells developers can export data, format spreadsheets in every detail and at every level, import images, import charts, create charts, manipulate charts, stream Excel® data, save in various formats including XLS, CSV, SpreadsheetML, TabDelimited, TXT, XML (Aspose.Pdf integrated) and many more. All the Aspose components are totally independent and are not affiliated with, nor authorized, sponsored by Microsoft Corporation. Aspose.Cells has a huge list of features. To know more about the product information, features and for a programmer's guide, please check its summary of Features List , Aspose.Cells Decumentation and online featured demos.  So I strongly recommend you and invite you to try the component one time at least and see the difference. You can try its evaluation version, it is totally free without any time limitation what so ever. You may download its evaluation version for free.

 

Performing the Tasks

For demonstration, I develop a simple console application which creates an excel workbook from the scratch (you may utilize template file and format its data too.), insert sales data into the first worksheet in the excel book, format data into the cells and save the sales report excel file. I exhibit all the formatting tasks mentioned above using the simplest API of Aspose.Cells.

 

Format Cells in Rows and Columns

 

Below are the steps involved how to create a spreadsheet and format different cells in different rows and columns of a worksheet.

 

Download and Install Aspose.Cells

First, you need to download Aspose.Cells for .Net. Install it on your development computer. All Aspose components, when installed, work in evaluation mode. The evaluation mode has no time limit and it only injects watermarks into produced documents.

Create a Project

Start Visual Studio. Net and create a new console application. This example will show a C# console application, but you can use VB.NET too.

Add References

This project will use Aspose.Cells. So, you have to add reference to Aspose.Cells component in your project. E.g., add a reference to ….\Program Files\Aspose\Aspose.Cells\Bin\Net1.0\Aspose.Cells.dll

 

 

Code Snippet

 

Following is the actual code (Written in C#) used by the component to accomplish the tasks. You may see how Aspose.Cells feature rich API can be used to accomplish the underlying tasks. To understand you in a better way, comments are embedded with each line of code.

 

//--------------------------------------------------------------------

//--- AsposeFormatWorksheet

//--- A Console Appliation which describes how to format a worksheet.

//---

//--- The Application creates a workbook from the srcatch, input some

//--- data into a worksheet and apply formattings to the cells in the

//--- rows and columns of the worksheet.

//---

//--- © Aspose 2007. All rights reserved.

//--- Support: http://www.aspose.com/Community/forums/

//---

//--------------------------------------------------------------------

using System;

using System.Drawing;

using Aspose.Cells;

 

namespace AsposeFormatWorksheet

{

              /// <summary>

              /// AsposeFormatWorksheet

              /// Use Aspose.Cells to perform the task

              /// </summary>

              class Class1

              {

                            /// <summary>

                            /// The main entry point for the application.

                            /// </summary>

                            [STAThread]

                            static void Main(string[] args)

                            {

                                          string filename = @"D:\FormatWorksheet.xls";

                                          CreateSalesReport(filename);

                                         

                            }

                           

                            private static void CreateSalesReport(string filename)

                            {

                            // Uncomment the code below when you have purchased license

                            // for Aspose.Cells. You need to deploy the license in the

                            // same folder as your executable, alternatively you can add

                            // the license file as an embedded resource to your project.

                            //

                            // // Set license for Aspose.Cells

                            // Aspose.Cells.License cellsLicense = new

                            // Aspose.Cells.License();

                            // cellsLicense.SetLicense("Aspose.Cells.lic");

                                         

 

      //Create a new Workbook.

                            Workbook workbook = new Workbook();

                            //Note: Since Excel color palette has 56 colors on it.

                            //The colors are indexed 0-55.

                            //Please check: http://www.aspose.com/Products/Aspose.Cells/Api/Aspose.Cells.Workbook.ChangePalette.html

                            //If a color is not present on the palette, we have to add it

                            //to the palette, so that we may use.

                            //Add a few custom colors to the palette.

                            workbook.ChangePalette(Color.FromArgb(155,204,255),55);

                            workbook.ChangePalette(Color.FromArgb(0,51,105),54); 

                            workbook.ChangePalette(Color.FromArgb(250,250,200),53);

                            workbook.ChangePalette(Color.FromArgb(124,199,72),52);

                                         

                            CreateReportData(workbook);

                            CreateCellsFormatting(workbook);

                                         

                            //Get the first worksheet in the book.

                            Worksheet worksheet = workbook.Worksheets[0];

                            //Name the worksheet.

                            worksheet.Name = "Sales Report";

                            //Save the excel file.

                            workbook.Save(filename);

                                           

                           

                            }

             

                            private static void CreateReportData(Workbook workbook)

                            {

                                          //Obtain the cells of the first worksheet.

                                          Cells cells = workbook.Worksheets[0].Cells;

                                         

                                          //Input the title on B1 cell.

                                          cells["B1"].PutValue("Western Product Sales 2006");

                                          //Insert some column headings in the second row.

                                          Cell cell = cells["B2"];

                                          cell.PutValue("January");

                                          cell = cells["C2"];

                                          cell.PutValue("February");

                                          cell = cells["D2"];

                                          cell.PutValue("March");

                                          cell = cells["E2"];

                                          cell.PutValue("April");

                                          cell = cells["F2"];

                                          cell.PutValue("May");

                                          cell = cells["G2"];

                                          cell.PutValue("June");

                                          cell = cells["H2"];

                                          cell.PutValue("July");

                                          cell = cells["I2"];

                                          cell.PutValue("August");

                                          cell = cells["J2"];

                                          cell.PutValue("September");

                                          cell = cells["K2"];

                                          cell.PutValue("October");

                                          cell = cells["L2"];

                                          cell.PutValue("November");

                                          cell = cells["M2"];

                                          cell.PutValue("December");

                                          cell = cells["N2"];

                                          cell.PutValue("Total");

 

                                          //Insert product names.

                                          cells["A3"].PutValue("Biscuits");

                                          cells["A4"].PutValue("Coffee");

                                          cells["A5"].PutValue("Tofu");

                                          cells["A6"].PutValue("Ikura");

                                          cells["A7"].PutValue("Choclade");

                                          cells["A8"].PutValue("Maxilaku");

                                          cells["A9"].PutValue("Scones");

                                          cells["A10"].PutValue("Sauce");

                                          cells["A11"].PutValue("Syrup");

                                          cells["A12"].PutValue("Spegesild");

                                          cells["A13"].PutValue("Filo Mix");

                                          cells["A14"].PutValue("Pears");

                                          cells["A15"].PutValue("Konbu");

                                          cells["A16"].PutValue("Kaviar");

                                          cells["A17"].PutValue("Zaanse");

                                          cells["A18"].PutValue("Cabrales");

                                          cells["A19"].PutValue("Gnocchi");

                                          cells["A20"].PutValue("Wimmers");

                                          cells["A21"].PutValue("Breads");

                                          cells["A22"].PutValue("Lager");

                                          cells["A23"].PutValue("Gravad");

                                          cells["A24"].PutValue("Telino");

                                          cells["A25"].PutValue("Pavlova");

                                          cells["A26"].PutValue("Total");

 

                                          //Input porduct sales data (B3:M25).

                                          cells["B3"].PutValue(5000);

                                          cells["C3"].PutValue(4500);

                                          cells["D3"].PutValue(6010);

                                          cells["E3"].PutValue(7230);

                                          cells["F3"].PutValue(5400);

                                          cells["G3"].PutValue(5030);

                                          cells["H3"].PutValue(3000);

                                          cells["I3"].PutValue(6000);

                                          cells["J3"].PutValue(9000);

                                          cells["K3"].PutValue(3300);

                                          cells["L3"].PutValue(2500);

                                          cells["M3"].PutValue(5510);

 

                                          cells["B4"].PutValue(4000);

                                          cells["C4"].PutValue(2500);