Introduction

Aspose.Cells FOSS for TypeScript provides a comprehensive set of spreadsheet management features beyond basic cell read/write. Developers can apply detailed styling, set up data validation rules, configure auto-filters, add conditional formatting, build charts, insert shapes, and protect workbooks – all from TypeScript code with no Microsoft Office dependency.

This post walks through the key spreadsheet management capabilities available in the library, with working code examples for each feature area.

Key Features

Data Validation

Add validation rules to cell ranges using the DataValidation class. Supported validation types include list, number range, and custom formulas. Apply validation to any range with worksheet.addDataValidation().

const workbook = new Workbook();
const worksheet = workbook.worksheets[0]!;
const validation = new DataValidation();
validation.type = "list";
validation.formula1 = '"Option1,Option2,Option3"';
worksheet.addDataValidation(validation, "B1:B10");
await workbook.save("validated.xlsx");

Auto-Filter

Enable auto-filter headers on a data range with worksheet.setAutoFilter(). This adds drop-down filter controls to column headers in the output XLSX file. Remove filters with worksheet.removeAutoFilter().

const workbook = new Workbook();
const worksheet = workbook.worksheets[0]!;
worksheet.putValue("A1", "Name");
worksheet.putValue("B1", "Age");
worksheet.putValue("C1", "City");
worksheet.putValue("A2", "Alice");
worksheet.putValue("B2", "25");
worksheet.putValue("C2", "New York");
worksheet.putValue("A3", "Bob");
worksheet.putValue("B3", "30");
worksheet.putValue("C3", "London");
worksheet.setAutoFilter("A1:C4");
await workbook.save("filtered.xlsx");

Conditional Formatting

Apply visual rules to cell ranges using ConditionalFormatCollection. The library supports ColorScaleRule, DataBarRule, and IconSetRule for highlighting cells based on their values.

const workbook = new Workbook();
const worksheet = workbook.worksheets[0]!;
for (let i = 1; i <= 10; i++) {
  worksheet.putValue(`A${i}`, i * 10);
}
await workbook.save("conditional.xlsx");

Attach hyperlinks to cells with cell.setHyperlink() supporting URLs, email addresses, and internal sheet references. Add comments with worksheet.addComment() using Comment objects.

const workbook = new Workbook();
const worksheet = workbook.worksheets[0]!;
worksheet.putValue("A1", "Click here");
worksheet.getCell2("A1").setHyperlink("https://www.example.com");
worksheet.putValue("A3", "Send email");
worksheet.getCell2("A3").setHyperlink("mailto:test@example.com");
await workbook.save("hyperlinks.xlsx");

Workbook and Cell Protection

Protect entire workbooks with a password using workbook.protect(). Lock individual cells with style.setLocked(true) and control formula visibility with style.setHidden().

const workbook = new Workbook();
const worksheet = workbook.worksheets[0]!;
worksheet.putValue("A1", "Protected Cell");
const style = new Style();
style.setLocked(true);
style.setHidden(false);
worksheet.getCell2("A1").setStyle(style);
workbook.protect(true, "password");
await workbook.save("protected.xlsx");

Column and Row Sizing

Set column widths with worksheet.setColumnWidth() and row heights with worksheet.setRowHeight(). Hide rows with worksheet.setRowHidden(). These settings persist in the saved XLSX file.

const workbook = new Workbook();
const worksheet = workbook.worksheets[0]!;
worksheet.putValue("A1", "Wide Column");
worksheet.setColumnWidth(0, 30);
worksheet.setRowHeight(0, 25);
await workbook.save("sized.xlsx");

Quick Start

Install with npm:

npm install @aspose/cells@1.0.0

Build a styled workbook with validation and auto-filter:

import { Workbook, Style, DataValidation } from "@aspose/cells";

const workbook = new Workbook();
const worksheet = workbook.worksheets[0]!;

worksheet.putValue("A1", "Status");
worksheet.putValue("B1", "Value");
const headerStyle = new Style();
headerStyle.setBold(true);
worksheet.getCell2("A1").setStyle(headerStyle);
worksheet.getCell2("B1").setStyle(headerStyle);

const validation = new DataValidation();
validation.type = "list";
validation.formula1 = '"Active,Inactive,Pending"';
worksheet.addDataValidation(validation, "A2:A20");

worksheet.setAutoFilter("A1:B20");
await workbook.save("managed.xlsx");

Open Source & Licensing

Aspose.Cells FOSS for TypeScript is released under the MIT license. The source code is available on GitHub. Commercial use is permitted under the MIT license terms.

Getting Started