# XLSX Manager

A powerful TypeScript library for Excel file manipulation with advanced table detection, formatting, and data extraction capabilities.

## Features

- **Smart Table Detection** - Automatically detect table boundaries and structure
- **Multi-level Header Support** - Handle complex headers with merged cells
- **Schema-based Data Extraction** - Transform Excel data to JSON with custom schemas
- **Conditional Formatting** - Preserve and reapply Excel conditional formatting rules
- **Cell Operations** - Get/set cells by value, index, or address
- **Row/Column Operations** - Read, clear, and manipulate rows and columns
- **Meta Data Extraction** - Extract related cell values (left/right neighbors)
- **Merge Cell Handling** - Detect and work with merged cells

## Installation

```bash
npm install xlsx-manager
```

## Quick Start

```typescript
import { XlsxTable } from 'xlsx-manager';

// Load an Excel file
const xlsx = new XlsxTable();
await xlsx.loadXlsx('data.xlsx');

// Detect table structure starting from a cell
const table = xlsx.detectTable('A1');
console.log(table.range); // { startRow, endRow, startCol, endCol }

// Convert to JSON with auto-detection
const data = xlsx.toJson('A1');
console.log(data);
```

## API Reference

### XlsxTable

The main class for table operations.

#### Constructor

```typescript
new XlsxTable(options?: DetectTableOptions)
```

**Options:**
- `maxEmptyRows` (default: 2) - Max consecutive empty rows before table end
- `maxEmptyCols` (default: 1) - Max consecutive empty columns before table end
- `maxRows` (default: 1000) - Maximum rows to scan
- `maxCols` (default: 100) - Maximum columns to scan
- `initialRowScan` (default: 20) - Initial rows to scan for quick boundary detection

#### Methods

##### loadXlsx(source)

Load an Excel file from path or File object.

```typescript
await xlsx.loadXlsx('path/to/file.xlsx');
// or
await xlsx.loadXlsx(fileObject);
```

##### detectTable(startCell)

Detect table structure from a starting cell.

```typescript
const table = xlsx.detectTable('A1');
// Returns: { range, headerRows, dataStartRow, columns }
```

##### toJson(startCell, schema?)

Convert table data to JSON array.

```typescript
// Auto-detect structure
const data = xlsx.toJson('A1');

// With custom schema
const schema = {
  studentId: "Student ID",
  name: "Name",
  grades: ["Subject", "Grade"]  // Nested array
};
const data = xlsx.toJson('A1', schema);
```

**Schema Format:**
- Simple mapping: `{ outputKey: "Header Name" }`
- Nested arrays: `{ outputKey: ["GroupHeader", "ItemHeader"] }`

### Xlsx (XlsxManager)

Lower-level Excel operations.

#### Cell Operations

```typescript
// Get cell by address
const cell = xlsx.getCell('A1');

// Get cell by value
const cell = xlsx.getCell('Student Name');

// Get cell with merge info
const cell = xlsx.getCell('A1', { includeMergeInfo: true });
```

#### Row Operations

```typescript
// Get row by index
const row = xlsx.getRow(1);

// Get row by value
const row = xlsx.getRow('Student Name');

// Get row values only
const values = xlsx.getRowValues(1);

// Clear row
xlsx.clearRow(1);
```

#### Column Operations

```typescript
// Get column by letter
const col = xlsx.getCol('A');

// Get column by index
const col = xlsx.getCol(1);

// Get column values
const values = xlsx.getColValues('A');

// Clear column
xlsx.clearCol('A');
```

#### Meta Data Extraction

Extract values from neighboring cells:

```typescript
// Get value to the right
const value = xlsx.meta('Label:');  // Returns value next to "Label:"

// Get value to the left
const value = xlsx.metaR('Value');

// With custom distance
const value = xlsx.meta('Label:', { maxDistance: 10 });
```

#### Range Operations

```typescript
// Get range of cells
const range = xlsx.getRange('A1:C10');
range.eachCell((cell) => {
  console.log(cell.value);
});
```

### XlsxCore

Core worksheet operations.

```typescript
// Check if row/column is empty
const isEmpty = xlsx.isRowEmpty(1);
const isEmpty = xlsx.isColEmpty('A');

// Check within range
const isEmpty = xlsx.isRowEmptyInRange(1, 1, 5);
```

### XlsxFormatting

Handle Excel formatting and styles.

```typescript
// Save with formatting preserved
await xlsx.saveXlsx('output.xlsx');

// Get buffer
const buffer = await xlsx.writeBuffer();
```

### Utility Functions

```typescript
import {
  isCellAddress,
  isColumnLetter,
  extractRowNumber,
  extractColNumber,
  parseRange,
  toCamelCase,
  nStr
} from 'xlsx-manager';

// Check if string is a cell address
isCellAddress('A1');  // true

// Extract components
extractRowNumber('A10');  // 10
extractColNumber('AB5');  // 28

// Parse range
parseRange('A1:C10', rowCount, colCount);

// Utilities
toCamelCase('Student Name');  // "studentName"
nStr('  text  ');  // "text" (normalized)
```

## Advanced Examples

### Complex Table with Nested Headers

```typescript
const xlsx = new XlsxTable();
await xlsx.loadXlsx('grades.xlsx');

const schema = {
  studentId: "Student ID",
  name: "Name",
  birthDate: "Birth Date",
  grades: ["Subject", "Score"]  // Will create array of {Subject, Score}
};

const students = xlsx.toJson('A1', schema);
// Output:
// [
//   {
//     studentId: "001",
//     name: "John Doe",
//     birthDate: "2000-01-01",
//     grades: [
//       { Subject: "Math", Score: 95 },
//       { Subject: "English", Score: 88 }
//     ]
//   }
// ]
```

### Custom Table Detection

```typescript
const xlsx = new XlsxTable({
  maxEmptyRows: 3,
  maxEmptyCols: 2,
  maxRows: 5000,
  initialRowScan: 50
});

await xlsx.loadXlsx('large-file.xlsx');
const table = xlsx.detectTable('B5');  // Start from specific cell
```

### Working with Merged Cells

```typescript
const cell = xlsx.getCell('A1', { includeMergeInfo: true });
if (cell.isMerged) {
  console.log(`Spans ${cell.rowSpan} rows and ${cell.colSpan} columns`);
  console.log(`Is top-left: ${cell.isTopLeft}`);
}
```

### Preserving Conditional Formatting

```typescript
// Load file
await xlsx.loadXlsx('formatted.xlsx');

// Modify data
xlsx.getCell('A1').value = 'New Value';

// Save with formatting preserved
await xlsx.saveXlsx('output.xlsx');
```

## TypeScript Support

Full TypeScript support with comprehensive type definitions:

```typescript
import type {
  TableRange,
  DetectTableOptions,
  ColumnInfo,
  HeaderStructure,
  CellInfo,
  MergeInfo
} from 'xlsx-manager';
```

## License

MIT

## Contributing

Contributions are welcome! Please feel free to submit a Pull Request.