Aspose.Cells for .NET Introduction (click to expand)
Aspose.Cells for .NET is a powerful spreadsheet management library that allows developers to create, format, and manipulate Excel files programmatically without the need for Microsoft Excel. It supports features like creating Pivot Tables, applying advanced conditional formatting, converting workbooks to multiple formats (PDF, HTML, JSON), working with charts, Smart Markers, OLE objects, and filtering data—all using C#. With extensive support for rendering and automation, it offers a seamless and versatile solution for Excel automation in .NET applications.
Aspose.Cells for .NET also provides various Grid solutions with GUI-based .NET controls; Aspose.Cells.GridDesktop, Aspose.Cells.GridWeb, and Aspose.Cells.GridJs.
PivotTable Features
Provides options to create, format, and remove Pivot Tables from a worksheet.
Support for Different Pivot Table Layouts
Offers predefined layouts (Compact, Outline, Tabular) for Pivot Tables.
Rendering and Conversion Features
Supports converting workbooks to formats like PDF, HTML, JSON, and more.
Track Document Conversion Progress
Enables tracking of conversion progress during rendering.
Set Image Resample Option for PDF Conversion
Allows resampling images during conversion to reduce PDF file size.
Cells Features
Access Cells of a Worksheet
Access and manipulate individual cells in the worksheet.
Add and Retrieve Data
Add data to specific cells and retrieve it as needed.
Insert and Delete Rows and Columns
Provides methods for inserting or deleting rows and columns.
Set different styles, alignment, and font properties to enhance cell formatting.
Chart Features
Create and Customize Charts
Supports creating and customizing different types of charts, such as Column, Line, Pie, Doughnut, Radar, and more.
Create Pivot Charts
Supports creating Pivot Charts along with Pivot Tables.
Workbook Views Features
Page Break Preview
Displays the worksheet as it will print, showing the page breaks.
Normal View Mode
Default view of a worksheet showing the content without page break indicators.
Zoom Factor
Allows setting the zoom factor to adjust the size of the worksheet contents.
Freeze and Split Panes
Keeps specific rows or columns visible while scrolling through the worksheet and splits the view for easier navigation and editing.
Defined Names Features
Create Named Ranges
Create and access named ranges to refer to specific cells or ranges for easier identification and calculations.
Filter Defined Names
Filter or remove defined names present inside the workbook using LoadDataFilterOptions.
Use Aspose.Cells to apply conditional formatting based on cell value or formula.
Apply data bars, color scales, and icon sets for enhanced conditional formatting.
Copy conditional formatting settings from one cell to another using the Range.Copy() method.
Add and remove conditional formatting dynamically using Aspose.Cells API.
Add comments to specific cells, retrieve, modify, and remove them as needed.
Add threaded comments, allowing for discussion-like conversations in cells, similar to Excel 365.
Themes Features
Excel Themes and Colors
Create, apply, and customize color schemes and themes in Excel worksheets.
Custom Color Themes
Use Aspose.Cells to create custom color themes, modifying only theme colors instead of individual cells.
SmartArt Features
Extract text from the Gear Type SmartArt Shape using Shape.GetResultOfSmartArt() and GroupShape.GetGroupedShapes().
Convert SmartArt to Group Shape
Convert SmartArt into group shapes for detailed manipulation.
Text Box Features
Add Text Box to Worksheet
Insert a text box into a worksheet and customize its properties.
Text Alignment in TextBox
Apply different text alignment settings within a text box for formatting.
Links Features
Hyperlinks
Create hyperlinks to link data within worksheets for easy navigation.
OleObject Features
Managing OLE Objects
Embed and manipulate OLE objects in worksheets using Aspose.Cells.
Page Setup Features
Set Margins
Allows setting top, left, right, and bottom margins for a worksheet.
Page Breaks
Allows inserting horizontal and vertical page breaks at specific cells.
Set Print Area and Print Titles
Provides options for setting the print area and print titles for a worksheet.
Custom Paper Size
Implement custom paper sizes for worksheet rendering and printing.
Insert Background Image
Add a background image to the worksheet using Aspose.Cells.
Filter Features
Auto-Filter and Custom Data Filter
Provides options for filtering data based on various conditions like text, numbers, and dates.
Fill Color Filter
Filter data based on the fill color of cells using the AddFillColorFilter function.
Date, Number, and Text Filters
Provides options for filtering rows based on dates, numbers, or text criteria.
Blanks and Non-Blanks Filters
Filters rows based on whether they contain blank or non-blank cells.
Outline Features
Group or Ungroup Rows and Columns
Allows grouping or ungrouping rows and columns in the worksheet for better data organization.
Controls Features
Workbook VBA Project
Allows managing VBA projects within the workbook, including adding and modifying VBA code.
Calculation Features
Tracing Precedents and Dependents
Provides tools for tracing precedent and dependent cells in complex worksheets to identify data relationships.
Allows adding headers and footers to worksheets, displaying information like page number, author, or date.
Enables inserting images into headers and footers using the PageSetup class.
Script Commands
Provides script commands to format headers and footers, such as &P for page number and &D for the current date.
Setting Different Headers and Footers for Different Pages
Allows setting different headers and footers for the first, odd, and even pages.
| Format | Description | Load | Save |
|---|
| XLS | Excel 95/5.0 - 2003 Workbook. | ✔️ | ✔️ |
| XLSX | The Office Open XML SpreadsheetML File Format. | ✔️ | ✔️ |
| XLSB | Excel Binary Workbook. | ✔️ | ✔️ |
| XLSM | Excel Macro-Enabled Workbook. | ✔️ | ✔️ |
| XLT | Excel 97 - Excel 2003 Template. | ✔️ | ✔️ |
| XLTX | Excel Template. | ✔️ | ✔️ |
| XLTM | Excel Macro-Enabled Template. | ✔️ | ✔️ |
| XLAM | An Excel Macro-Enabled Add-In file that’s used to add new functions to Excel. | | ✔️ |
| CSV | CSV (Comma Separated Value) file. | ✔️ | ✔️ |
| TSV | TSV (Tab-separated values) file. | ✔️ | ✔️ |
| TabDelimited | Tab-delimited text file, same with TSV file. | ✔️ | ✔️ |
| TXT | Delimited plain text file. | ✔️ | ✔️ |
| HTML | HTML format. | ✔️ | ✔️ |
| MHTML | MHTML file. | ✔️ | ✔️ |
| ODS | ODS (OpenDocument Spreadsheet). | ✔️ | ✔️ |
| SpreadsheetML | Excel 2003 XML file. | ✔️ | ✔️ |
| Numbers | The document is created by Apple’s “Numbers” application, part of Apple’s iWork office suite, running on Mac OS X and iOS. | ✔️ | |
| JSON | JavaScript Object Notation | ✔️ | ✔️ |
| DIF | Data Interchange Format. | | ✔️ |
| PDF | Adobe Portable Document Format. | | ✔️ |
| XPS | XML Paper Specification Format. | | ✔️ |
| SVG | Scalable Vector Graphics Format. | | ✔️ |
| TIFF | Tagged Image File Format | | ✔️ |
| PNG | Portable Network Graphics Format | | ✔️ |
| BMP | Bitmap Image Format | | ✔️ |
| EMF | Enhanced metafile Format | | ✔️ |
| JPEG | JPEG is a type of image format that is saved using the method of lossy compression. | | ✔️ |
| GIF | Graphical Interchange Format | | ✔️ |
| MARKDOWN | Represents a markdown document. | | ✔️ |
| SXC | An XML based format used by OpenOffice and StarOffice | ✔️ | ✔️ |
| FODS | This is an Open Document format stored as flat XML. | ✔️ | ✔️ |
| DOCX | A well-known format for Microsoft Word documents that is a combination of XML and binary files. | | ✔️ |
| PPTX | The PPTX format is based on the Microsoft PowerPoint open XML presentation file format. | | ✔️ |
| SqlScript | Structured Query Language. | | ✔️ |
| XHtml | The XHTML is a text based file format with markup in XML, using a reformulation of HTML 4.0. | ✔️ | ✔️ |
| Epub | Files with .epub extension are an e-book file format that provide a standard digital publication format for publishers. | ✔️ | ✔️ |
| Xml | XML stands for Extensible Markup Language that is similar to HTML but different in using tags for defining objects. | ✔️ | ✔️ |
| Ots | Open Document Template Sheet (OTS) file. | ✔️ | ✔️ |
| AZW3 | AZW is a digital ebook file format developed by Amazon for its Kindle devices. AZW3, also known as Kindle Format 8 (KF8). | ✔️ | ✔️ |
System Requirements
Supported Operating Systems
Windows
2000, XP, Server 2003-2019, Vista, 7-11, Azure
Linux
Ubuntu, openSUSE, CentOS, Debian, Alpine, others
Mac
OS X, macOS High Sierra, etc.
.NET Framework Versions Supported
- .NET Framework: 2.0 to 4.8.1
- .NET Standard: 2.0, .NET Core 2.1/3.1, .NET 5-8
- Mono Framework: 1.2 or newer
NuGet Packages Referenced
- .NET Standard: System.Drawing.Common, System.Text.Encoding.CodePages, System.Security.Cryptography.Pkcs
- .NET 6-8: System.Text.Encoding.CodePages, System.Security.Cryptography.Pkcs, System.Drawing.Common (Windows), SkiaSharp (non-Windows)
Development Environments
- Microsoft Visual Studio 2005-2022
Aspose.Cells Grid Suite System Requirements
- GridDesktop: Supports .NET Framework (2.0-4.8.1), .NET Core 3.1, .NET 7, Windows 2000-11
- GridWeb: Supports .NET Framework (2.0-4.8.1), .NET Core 6-7, Windows 2000-11, Chrome, Edge, Firefox, Safari, Opera
- GridJs: Supports .NET Standard 2.0, Windows 2000-11, Linux, Mac, Chrome, Edge, Firefox, Safari, Opera
Note
Microsoft Excel is not required for Aspose.Cells components. Microsoft Excel Viewer is needed only to view generated Excel files.
Install Aspose.Cells on Windows
1. Pre-Installation Checks
- Ensure IIS is installed (for Web Demos).
- Disable UAC (User Account Control).
2. Installation Methods
Command Line
- Run
cmd.exe as Administrator. - Execute:
msiexec /i Aspose.Cells.msi.
Control Panel
- Navigate to Control Panel > User Accounts > Turn User Account Control On/Off.
- Uncheck the box and click OK. Restart the system.
Install Aspose.Cells through NuGet
1. Package Manager GUI
- Open Visual Studio.
- Tools > Library Package Manager > Manage NuGet Packages.
- Search “Aspose.Cells” and click “Install”.
2. Package Manager Console
- Open Visual Studio.
- Tools > Library Package Manager > Package Manager Console.
- Execute:
Install-Package Aspose.Cells.
Referencing Aspose.Cells from a .NET Project
1. Add Reference
- Right-click “References” in Solution Explorer > Add Reference.
- Locate
Aspose.Cells.dll (from MSI or downloaded DLL) and click OK.
2. Client Profile Project
- Use DLL from the
net_ClientProfile folder for .NET Framework 3.5/4 Client Profile.
3. Grid Controls
- Add references for
Aspose.Cells.GridDesktop.dll and Aspose.Cells.GridWeb.dll similarly.
Convert Worksheet to Image
This code demonstrates how to convert a worksheet into an image format, which can be useful for various application needs such as embedding it into a document or a web page.
//Create a new Workbook object
//Open a template excel file
Workbook book = new Workbook("Sheet to Image.xls");
//Get the first worksheet.
Worksheet sheet = book.Worksheets[0];
//Define ImageOrPrintOptions
ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
//Specify the image type
imgOptions.ImageType = ImageType.Jpeg;
//Render the sheet with respect to specified image/print options
SheetRender sr = new SheetRender(sheet, imgOptions);
//Render the image for the sheet
Bitmap bitmap = sr.ToImage(0);
//Save the image file
bitmap.Save("SheetImage.jpg");
Using Smart Markers with Custom Objects
This snippet shows how to use Aspose.Cells’ Smart Markers feature with custom objects, which is great for dynamically generating reports in Excel.
//Instantiate the workbookdesigner object.
WorkbookDesigner report = new WorkbookDesigner();
//Get the first worksheet(default sheet) in the workbook.
Aspose.Cells.Worksheet w = report.Workbook.Worksheets[0];
//Input some markers to the cells.
w.Cells["A1"].PutValue("Test");
w.Cells["A2"].PutValue("&=MyProduct.Name");
w.Cells["B2"].PutValue("&=MyProduct.Age");
//Instantiate the list collection based on the custom class.
IList<MyProduct> list = new List<MyProduct>();
//Provide values for the markers using the custom class object.
list.Add(new MyProduct("Simon", 30));
list.Add(new MyProduct("Johnson", 33));
//Set the data source.
report.SetDataSource("MyProduct", list);
//Process the markers.
report.Process(false);
//Save the excel file.
report.Workbook.Save("Smart Marker Customobjects.xls");
Merging Cells in a Range
This example shows how to merge or unmerge a range of cells in a worksheet, which can be useful for formatting purposes or creating visually organized reports.
//Create a workbook
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//Create a range
Range range = worksheet.Cells.CreateRange("A1", "D4");
//Merge cells
range.Merge();
//Unmerge cells (optional)
range.UnMerge();
//Save the workbook
workbook.Save("MergedCells.xlsx");
Adding Chart Titles and Customizing Gridlines
This snippet illustrates how to add a chart title and customize the appearance of gridlines, which allows developers to create well-designed and professional-looking charts.
//Create a new workbook.
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//Adding a chart to the worksheet
int chartIndex = worksheet.Charts.Add(ChartType.Column, 5, 0, 15, 5);
Chart chart = worksheet.Charts[chartIndex];
//Setting the chart title.
chart.Title.Text = "Sales Chart";
//Hiding major gridlines.
chart.ValueAxis.MajorGridLines.IsVisible = false;
//Saving the Excel file
workbook.Save("ChartWithTitles.xlsx");