Aspose.Cells 24.10.0

.NET API for Excel® File Formats

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

Create and Format Pivot Tables

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

Convert Workbook to Different Formats

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.

Apply Styles and Formatting to Cells

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.

Conditional Formatting Features

Conditional Formatting in Worksheets

Use Aspose.Cells to apply conditional formatting based on cell value or formula.

Advanced Conditional Formatting

Apply data bars, color scales, and icon sets for enhanced conditional formatting.

Copy Method for Conditional Formatting

Copy conditional formatting settings from one cell to another using the Range.Copy() method.

Runtime Conditional Formatting

Add and remove conditional formatting dynamically using Aspose.Cells API.

Comments Features

Adding, Modifying, and Removing Comments

Add comments to specific cells, retrieve, modify, and remove them as needed.

Threaded Comments

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 SmartArt

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.

Header and Footer Features

Set Headers and Footers

Allows adding headers and footers to worksheets, displaying information like page number, author, or date.

Insert Image in Header or Footer

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.

Supported File Formats

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");

Tags

Aspose.Cells | Excel API | Spreadsheet Automation | Pivot Table | Conditional Formatting | Chart Customization | Data Manipulation | Grid Controls | Smart Markers | OLE Objects | Workbook Rendering | PDF Conversion | Excel to PDF | Excel Charts | SpreadsheetML | Header and Footer | Excel Themes | Zoom Factor | Worksheet Features | Page Setup | C# API | Excel Rendering | Spreadsheet Conversion | Excel File Formats | Cross-Platform | Excel Data Filter | Worksheet Protection | GridWeb | GridDesktop | Excel VBA | Worksheet Freeze Panes