# Why use xlswriter

Please refer to the image below. PHPExcel has been unable to work properly for memory reasons at 40,000 and 100000 points, but it can be resolved by modifying the ini configuration, but the time may take longer to complete the work;

![](/files/-LmmI9M0jyi4O3tl0luf)

xlswriter is a PHP C Extension that can be used to write text, numbers, formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file. It supports features such as:

* 100% compatible Excel XLSX files.
* Full Excel formatting.
* Merged cells.
* Defined names.
* Autofilters.
* Charts.
* Data validation and drop down lists.
* Worksheet PNG/JPEG images.
* Memory optimization mode for writing large files.
* Works on Linux, FreeBSD, OpenBSD, OS X, Windows.
* Compiles for 32 and 64 bit.
* FreeBSD License.
* The only dependency is on zlib.

## Benchmark

Test environment: Macbook Pro 13 inch, Intel Core i5, 16GB 2133MHz LPDDR3 Memory, 128GB SSD Storage.

#### Export

> Two memory modes export 1 million rows of data (27 columns, data is string):

* Normal mode: only 29S is needed, and the memory only needs 2083MB;
* Fixed memory mode: only need 52S, memory only needs <1MB;

#### Import

> 1 million rows of data (1 columns, data is inter):

* Full mode: Just 3S, the memory is only 558MB;
* Cursor mode: Just 2.8S, memory is only <1MB;


# Overview

xlswriter is a PHP C extension for reading and writing Excel 2007+ XLSX files.

The English documentation is organised by feature: install the extension, walk through the [quick start](/english/quick-start) for both writing and reading, then dive into the topic that matches the task — cell content, worksheet management, styling, charts, conditional formatting, data validation, Excel tables, page setup, workbook properties, defined names, sheet protection, the reader API, CSV conversion and helper utilities.

For the rationale behind the project and benchmarks, see [Why use xlswriter](/).


# Install

xlswriter is distributed as a PHP C extension. The recommended path is PECL; manual builds from source are also supported on every major platform.

This section covers:

* [Environment requirements](/english/install/requirements)
* [PECL (recommended)](/english/install/pecl)
* [Mac](/english/install/mac)
* [Alpine](/english/install/alpine)
* [Ubuntu](/english/install/ubuntu)
* [Windows](/english/install/windows)


# Env requirements

* Guaranteed **PHP** version is greater than or equal to **7.0**


# PECL (recommend)

```bash
pecl install xlswriter

# Add extension = xlswriter.so to ini configuration
```


# Mac

## Installation dependencies

```bash
brew install zlib
```

## Compiling extensions

```bash
git clone https://github.com/viest/php-ext-excel-export

cd php-ext-excel-export

git submodule update --init

phpize && ./configure --with-php-config=/path/to/php-config --enable-reader

make && make install
```

## Function test

```bash
make && make test
```

## Modify php.ini

```
extension = xlswriter.so
```


# Alpine

## Add Repositories

```bash
vi /etc/apk/repositories

# add Testing
# http://nl.alpinelinux.org/alpine/edge/testing
```

## APK

```bash
apk add php7-pecl-xlswriter
```


# Ubuntu

## Installation dependencies

```bash
apt-get install -y zlib1g-dev
```

## Compiling extensions

```bash
git clone https://github.com/viest/php-ext-excel-export

cd php-ext-excel-export

git submodule update --init

phpize && ./configure --with-php-config=/path/to/php-config --enable-reader

make && make install
```

## Function test

```bash
make && make test
```

## Modify php.ini

```
extension = xlswriter.so
```


# Windows

## Building a PHP build environment

See [php.net](https://wiki.php.net/internals/windows/stepbystepbuild)

## Installation dependencies

```bash
cd PHP_BUILD_PATH/deps

DownloadFile http://zlib.net/zlib-1.2.11.tar.gz

7z x zlib-1.2.11.tar.gz > NUL
7z x zlib-1.2.11.tar > NUL

cd zlib-1.2.11

cmake -G "Visual Studio 14 2015" -DCMAKE_BUILD_TYPE="Release" -DCMAKE_C_FLAGS_RELEASE="/MT"
cmake --build . --config "Release"
```

## Compiling extensions

```bash
cd PHP_PATH/ext

git clone https://github.com/viest/php-ext-excel-export.git

cd php-ext-excel-export

git submodule update --init

phpize

configure.bat --with-xlswriter --with-extra-libs=PATH\zlib-1.2.11\Release --with-extra-includes=PATH\zlib-1.2.11

nmake
```


# Quick start

Two minimal walkthroughs that cover the most common use cases:

* [Create file](/english/quick-start/create) — build a new XLSX from scratch.
* [Read file](/english/quick-start/reader) — open an existing XLSX and iterate its rows.

Both examples use the `Vtiful\Kernel\Excel` class. Read-side features require building the extension with `--enable-reader` (the default in PECL packages).


# Create file

> If there is a file with the same name under the path, the new file will overwrite the old file.

```php
$config = ['path' => '/home/viest'];
$excel  = new \Vtiful\Kernel\Excel($config);

// fileName will automatically create a worksheet, 
// you can customize the worksheet name, the worksheet name is optional
$filePath = $excel->fileName('tutorial01.xlsx', 'sheet1')
    ->header(['Item', 'Cost'])
    ->data([
        ['Rent', 1000],
        ['Gas',  100],
        ['Food', 300],
        ['Gym',  50],
    ])
    ->output();
```


# Read file

* The file is not supported for the `windows` system.
* Extended version is greater than or equal to `1.2.7`;

## Compiling

add `--enable-reader` when compiling

```bash
./configure --enable-reader
```

## Example

```bash
$config   = ['path' => './tests'];
$excel    = new \Vtiful\Kernel\Excel($config);

$filePath = $excel->fileName('tutorial.xlsx')
    ->header(['Item', 'Cost'])
    ->output();

$data = $excel->openFile('tutorial.xlsx')
    ->openSheet()
    ->getSheetData();

var_dump($data)
```


# Cell

Per-cell write operations: text, links, formulas, dates, images, rich text and comments, plus cell-level structural features like auto filter, freeze panes, merged cells, and row / column styles.

Pages in this section:

* [Insert text](/english/cell/insert-text), [link](/english/cell/insert-link), [formula](/english/cell/insert-formula), [date](/english/cell/insert-date), [local image](/english/cell/insert-image), [rich text](/english/cell/rich-text), [comment](/english/cell/insert-comment)
* [Auto filter](/english/cell/auto-filter), [freeze panes](/english/cell/freeze-panes), [merge cells](/english/cell/merge-cells)
* [Row cell style](/english/cell/row-style), [column cell style](/english/cell/column-style)


# Insert text

## **Function Prototype**

```php
insertText(int $row, int $column, string|int|double $data[, string $format, resource $formatHandler])
```

### **int $row**

> cell row

### **int $column**

> cell column

### **string | int | double $data**

> What needs to be written

### **string $format**

> Content format

### **resource $formatHandler**

> cell style

## example

```php
$excel = new \Vtiful\Kernel\Excel($config);

$textFile = $excel->fileName("free.xlsx")
     ->header(['name', 'money']);

for ($index = 0; $index < 10; $index++) {
     $textFile->insertText($index+1, 0, 'viest');
     $textFile->insertText($index+1, 1, 10000, '#,##0'); // #,##0 is the cell data style
}

$textFile->output();
```

## Digital Style Example

For more styles, please refer to the Excel Microsoft documentation.

```php
"0.000"
"#,##0"
"#,##0.00"
"0.00"
"0 \"dollar and\" .00 \"cents\""
```


# Insert link

## **Function Prototype**

```php
insertUrl(int $row, int $column, string $url[, resource $formatHandler])
```

### **int $row**

> cell row

### **int $column**

> cell column

### **string $url**

> link address

### **resource $formatHandler**

> cell style

## example

```php
$excel = new \Vtiful\Kernel\Excel($config);

$urlFile = $excel->fileName("free.xlsx")
     ->header(['url']);

$fileHandle = $fileObject->getHandle();

$format   = new \Vtiful\Kernel\Format($fileHandle);
$urlStyle = $format->bold()
     ->underline(Format::UNDERLINE_SINGLE)
     ->toResource();

$urlFile->insertUrl(1, 0, 'https://github.com', $urlStyle);

$textFile->output();
```


# Insert formula

## **Function Prototype**

```php
insertFormula(int $row, int $column, string $formula [, resource $formatHandler])
```

### **int $row**

> cell row

### **int $column**

> cell column

### **string $formula**

> formula

## **resource $formatHandler**

> cell style

## example

```php
$excel = new \Vtiful\Kernel\Excel($config);

$freeFile = $excel->fileName("free.xlsx")
     ->header(['name', 'money']);

for($index = 1; $index < 10; $index++) {
     $textFile->insertText($index, 0, 'viest');
     $textFile->insertText($index, 1, 10);
}

$textFile->insertText(12, 0, "Total");
$textFile->insertFormula(12, 1, '=SUM(B2:B11)');

$freeFile->output();
```


# Insert date

## **Function Prototype**

```php
insertDate(int $row, int $column, int $timestamp[, string $dateFormat = 'yyyy-mm-dd hh:mm:ss', resource $formatHandler])
```

### **int $row**

> cell row

### **int $column**

> cell column

### **int $timestamp**

> Timestamp to write

### **string $dataFormat**

> time formatting characters
>
> Default: yyyy-mm-dd hh:mm:ss

### **resource $formatHandler**

> cell style

## example

```php
$excel = new \Vtiful\Kernel\Excel($config);

$dateFile = $excel->fileName("free.xlsx")
     ->header(['date']);

$dateFile->insertDate(1, 0, time(), 'mmm d yyyy hh:mm AM/PM');

$textFile->output();
```

## Time Format Character Example

For more styles, please refer to the Excel Microsoft documentation.

```php
"mm/dd/yy"
"mmm d yyyy"
"d mmmm yyyy"
"dd/mm/yyyy hh:mm AM/PM"
```


# Insert local image

Embed a PNG or JPEG inside a cell. Use the path-based variant when the image lives on disk, or the buffer-based variant when the bytes are already in memory (uploads, S3, streamed data, …).

## **Function Prototype**

```php
insertImage(int $row, int $column, string $localImagePath, double $widthScale = 1.0, double $heightScale = 1.0): self

insertImageBuffer(int $row, int $column, string $bytes, ?array $options = null): self
```

### **int $row**

> cell row

### **int $column**

> cell column

### **string $localImagePath**

> Path to a PNG or JPEG file.

### **double $widthScale**

> Scale the image on the X axis; the default is 1, maintaining the original width; when the value is 0.5, the image width becomes 1/2 of the original.

### **double $heightScale**

> Scale the image on the Y axis; the default is 1, maintaining the original height; when the value is 0.5, the image height becomes 1/2 of the original.

### **string $bytes**

> Raw image bytes, e.g. the return value of `file_get_contents()` or a stream read.

### **array $options** (optional, for `insertImageBuffer`)

> All keys are optional:
>
> * `x_offset` *int* — horizontal pixel offset
> * `y_offset` *int* — vertical pixel offset
> * `x_scale` *double* — horizontal scale factor (default `1.0`)
> * `y_scale` *double* — vertical scale factor (default `1.0`)
> * `object_position` *int* — anchor mode (default `2`)
> * `url` *string* — hyperlink the image points to
> * `description` *string* — accessibility / alt-text description

## Example — path

```php
$excel = new \Vtiful\Kernel\Excel($config);

$excel->fileName('tutorial.xlsx')
      ->insertImage(5, 0, '/vagrant/logo.png')
      ->output();
```

## Example — buffer

```php
$excel = new \Vtiful\Kernel\Excel($config);

$bytes = file_get_contents('/vagrant/logo.png');

$excel->fileName('tutorial.xlsx')
      ->insertImageBuffer(5, 0, $bytes, [
          'x_scale'     => 0.5,
          'y_scale'     => 0.5,
          'url'         => 'https://github.com/viest/php-ext-xlswriter',
          'description' => 'xlswriter logo',
      ])
      ->output();
```


# Insert rich text

To mix multiple styles inside a single cell, build each fragment with `\Vtiful\Kernel\RichString` (which pairs a piece of text with a `Format` resource) and pass an array of those instances to `insertRichText`.

## **Function Prototype**

```php
\Vtiful\Kernel\RichString::__construct(string $text, ?resource $formatHandle = null)

insertRichText(int $row, int $column, array $runs, ?resource $formatHandle = null): self
```

### **string $text**

> Text of one fragment.

### **resource $formatHandle**

> Style handle returned by `Format::toResource()` for that fragment. Pass `null` to inherit the cell style.

### **int $row**

> cell row

### **int $column**

> cell column

### **array $runs**

> An array of `\Vtiful\Kernel\RichString` instances, concatenated in order. Any element that is not a `RichString` instance will trigger an exception.

### **resource $formatHandle** (optional)

> Cell-level format (alignment, background, etc.) applied to the whole cell. Defaults to no style.

## Example

```php
$config = [
    'path' => './tests'
];

$excel = new \Vtiful\Kernel\Excel($config);

$file       = $excel->fileName('tutorial.xlsx');
$fileHandle = $file->getHandle();

$boldStyle = (new \Vtiful\Kernel\Format($fileHandle))
    ->bold()
    ->toResource();

$redStyle = (new \Vtiful\Kernel\Format($fileHandle))
    ->fontColor(\Vtiful\Kernel\Format::COLOR_RED)
    ->toResource();

$italicStyle = (new \Vtiful\Kernel\Format($fileHandle))
    ->italic()
    ->toResource();

$file->insertRichText(0, 0, [
    new \Vtiful\Kernel\RichString('Hello ',  $boldStyle),
    new \Vtiful\Kernel\RichString('World',   $redStyle),
    new \Vtiful\Kernel\RichString(' from ',  null),
    new \Vtiful\Kernel\RichString('xlswriter', $italicStyle),
])->output();
```


# Insert comment

## **Function Prototype**

```php
insertComment(int $row, int $column, string $text): self

insertCommentOpt(int $row, int $column, string $text, array $options): self

showComment(): self
```

### **int $row**

> cell row

### **int $column**

> cell column

### **string $text**

> comment text

### **array $options**

> Optional extended comment settings. All keys are optional:
>
> * `author` *string* — comment author
> * `font_name` *string* — font name
> * `font_size` *double* — font size
> * `color` *int* — background color (`0xRRGGBB` or a `Format::COLOR_*` constant)
> * `x_offset` *int* — horizontal pixel offset
> * `y_offset` *int* — vertical pixel offset
> * `x_scale` *double* — horizontal scale factor
> * `y_scale` *double* — vertical scale factor
> * `width` *double* — box width in pixels
> * `height` *double* — box height in pixels
> * `visible` *int* — comment display mode. Prefer the class constants below over raw integers:
>   * `Excel::COMMENT_DISPLAY_DEFAULT` — follow the workbook-wide default
>   * `Excel::COMMENT_DISPLAY_HIDDEN` — only shown on hover
>   * `Excel::COMMENT_DISPLAY_VISIBLE` — always pinned open
> * `start_row` *int* — anchor row of the box
> * `start_col` *int* — anchor column of the box

`showComment()` toggles the workbook-wide "show all comments" flag. Call it once to make every comment visible by default.

## Example

```php
$config = [
    'path' => './tests'
];

$excel = new \Vtiful\Kernel\Excel($config);

$file = $excel->fileName('tutorial.xlsx')
    ->header(['name', 'score']);

$file->insertText(1, 0, 'viest')
     ->insertText(1, 1, 99)
     ->insertComment(1, 1, 'Almost a perfect score!')
     ->insertCommentOpt(1, 0, 'Project owner', [
         'author'    => 'admin',
         'font_name' => 'Arial',
         'font_size' => 10,
         'color'     => \Vtiful\Kernel\Format::COLOR_YELLOW,
         'width'     => 200,
         'height'    => 80,
         'visible'   => \Vtiful\Kernel\Excel::COMMENT_DISPLAY_VISIBLE,
     ])
     ->showComment()
     ->output();
```


# Auto filter

## **Function Prototype**

```php
autoFilter(string $range): self
```

### **string $range**

> Filter/Filter Data Range

## Example

```php
$config = ['path' => './tests'];
$excel = new \Vtiful\Kernel\Excel($config);

$filePath = $excel->fileName("tutorial.xlsx")
     ->header(['name', 'age'])
     ->data([
         ['one', 10],
         ['two', 20],
         ['three', 30],
     ])
     ->autoFilter("A1:B3") // Add Filter/Filter
     ->output();
```


# Freeze panes

## **Function Prototype**

```php
freezePanes(int $row, int $column): self
```

### **int $row**

> Line number

### **int $column**

> column number

## example

```php
freezePanes(1, 0); // freeze the first line
freezePanes(0, 1); // Freeze the first column
freezePanes(1, 1); // Freeze the first row and first column
```


# Merge cells

## **Function Prototype**

```php
mergeCells(string $scope, string $data[, resource $formatHandler]): self
```

### **string $scope**

> Cell range

### **string $data**

> Data

### **resource $formatHandler**

> cell style

## example

```php
$excel->fileName("test.xlsx")
   ->mergeCells('A1:C1', 'Merge cells')
   ->output();
```


# Row cell style

## **Function Prototype**

```php
setRow(string $range, double $height [, resource $formatHandler]);
```

### **string $range**

> Cell range

### **double $height**

> cell height

### **resource $formatHandler**

> cell style

## example

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->fileName('tutorial01.xlsx');
$fileHandle = $fileObject->getHandle();

$format    = new \Vtiful\Kernel\Format($fileHandle);
$boldStyle = $format->bold()->toResource();

$fileObject->header(['name', 'age'])
     ->data([['viest', 21]])
     ->setRow('A1', 20, $boldStyle)
     ->output();
```


# Column cell style

## **Function Prototype**

```php
setColumn(string $range, double $width [, resource $formatHandler]);
```

### **string $range**

> Cell range

### **double $width**

> cell width

### **resource $formatHandler**

> cell style

## example

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->fileName('tutorial01.xlsx');
$fileHandle = $fileObject->getHandle();

$format    = new \Vtiful\Kernel\Format($fileHandle);
$boldStyle = $format->bold()->toResource();

$fileObject->header(['name', 'age'])
    ->data([['viest', 21]])
    ->setColumn('A:A', 200, $boldStyle)
    ->output();
```


# Worksheet

Worksheet-level operations — create and switch between sheets, and configure visual properties such as gridlines, zoom, visibility, tab colour and background image.

Pages in this section:

* [Create worksheet](/english/worksheet/create)
* [Switch worksheet](/english/worksheet/switch)
* [Gridlines](/english/worksheet/gridlines)
* [Zoom](/english/worksheet/zoom)
* [Hide current worksheet](/english/worksheet/hide)
* [Set as first worksheet](/english/worksheet/first)
* [Tab color](/english/worksheet/tab-color)
* [Background image](/english/worksheet/background-image)


# Create worksheet

## **Function Prototype**

```php
addSheet([string $sheetName]);
```

## Example

```php
$config = [
     'path' => './filePath'
];

$excel = new \Vtiful\Kernel\Excel($config);

// A worksheet is automatically created here
$fileObject = $excel->fileName("tutorial01.xlsx");

$fileObject->header(['name', 'age'])
     ->data([['viest', 21]]);

// append a worksheet to the file
$fileObject->addSheet()
     ->header(['name', 'age'])
     ->data([['wjx', 22]]);

// Finally, the output file
$filePath = $fileObject->output();
```


# Switch worksheet

### **Function Prototype**

```php
checkoutSheet(string $sheetName): self

activateSheet(string $sheetName): bool
```

> `checkoutSheet` makes a sheet the current write target. `activateSheet` marks a sheet as the one Excel opens by default when the workbook is loaded — this is purely cosmetic and doesn't change the write target.

### **Instance**

```php
$config = [
   'path' => './tests'
];

$excel = new \Vtiful\Kernel\Excel($config);
$fileObject = $excel->fileName("tutorial01.xlsx");

$fileObject->header(['name', 'age'])
     ->data([
     ['viest', 21],
     ['viest', 22],
     ['viest', 23],
     ]);

// Add a worksheet and insert data
$fileObject->addSheet('twoSheet')
     ->header(['name', 'age'])
     ->data([['vikin', 22]]);

/ / Switch back to the default work table, and append data
$fileObject->checkoutSheet('Sheet1')
     ->data([['sheet1']]);

$filePath = $fileObject->output();
```


# Check worksheet exists

## Function Prototype

```php
existSheet(string $sheetName): bool
```

## Example

```php
$config = ['path' => './tests'];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject->fileName('tutorial.xlsx')
    // add a worksheet named twoSheet
    ->addSheet('twoSheet');

var_dump($fileObject->existSheet('twoSheet'));
var_dump($fileObject->existSheet('notFoundSheet'));
```

## Example output

```php
bool(true)
bool(false)
```


# Gridlines

## **Function Prototype**

```php
gridline(int $option = \Vtiful\Kernel\Excel::GRIDLINES_SHOW_ALL): self
```

## Grid line type

```php
const GRIDLINES_HIDE_ALL    = 0; // hide screen grid lines and print grid lines
const GRIDLINES_SHOW_SCREEN = 1; // display screen grid lines
const GRIDLINES_SHOW_PRINT  = 2; // display printing grid lines
const GRIDLINES_SHOW_ALL    = 3; // display screen grid lines and print grid lines
```

## **Example**

```php
$config = ['path' =>'./tests'];
$excel = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->fileName("tutorial01.xlsx");

$fileObject->header(['name','age'])
    ->gridline(\Vtiful\Kernel\Excel::GRIDLINES_HIDE_ALL) // Set the grid line of the worksheet
    ->data([
        ['viest', 21],
        ['viest', 22],
        ['viest', 23],
    ])
    ->output();
```


# Zoom

## **Function Prototype**

```php
zoom(int $scale = 100): self
```

### **int $scale**

> sheet zoom
>
> Range: 10 <= $scale <= 400
>
> Default: 100
>
> Scale does not affect print scale

## **Instance**

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->fileName("tutorial01.xlsx");

$fileObject->header(['name', 'age'])
     ->zoom(200) // Set the worksheet zoom factor
     ->data([
     ['viest', 21],
     ['viest', 22],
     ['viest', 23],
     ])
     ->output();
```


# Hide current worksheet

## **Function Prototype**

```php
setCurrentSheetHide(): self
```

## **Example**

```php
$config = ['path' =>'./tests'];
$excel = new \Vtiful\Kernel\Excel($config);

$excel->fileName('hide.xlsx','sheet1') // Initialize the file and initialize the first sheet at the same time sheet1
    ->header(['sheet1'])    // insert data in sheet1 worksheet
    ->addSheet('sheet2')    // Add a new sheet sheet2, and set the current active sheet to sheet2
    ->setCurrentSheetHide() // The current active sheet is sheet2, and sheet2 is hidden
    ->output();
```


# Set as first worksheet

## **Function Prototype**

```php
setCurrentSheetIsFirst(): self
```

## **Example**

```php
$config = ['path' =>'./tests'];
$excel = new \Vtiful\Kernel\Excel($config);

$excel->fileName('hide.xlsx','sheet1') // Initialize the file and initialize the first sheet at the same time sheet1
    ->header(['sheet1'])       // insert data in sheet1 worksheet
    ->addSheet('sheet2')       // Add a new sheet sheet2, and set the current active sheet to sheet2
    ->setCurrentSheetIsFirst() // The current active sheet is sheet2, and set sheet2 as the first sheet
    ->output();
```


# Tab color

## **Function Prototype**

```php
setTabColor(int $rgb): self
```

### **int $rgb**

> Sheet tab color expressed as a `0xRRGGBB` integer. The `Format::COLOR_*` constants can be used as well. For example `0xFF0000` is red and `0x00B050` is green.

## Example

```php
$config = [
    'path' => './tests'
];

$excel = new \Vtiful\Kernel\Excel($config);

$excel->fileName('tutorial.xlsx', 'sheet1')
      ->setTabColor(0xFF0000)
      ->addSheet('sheet2')
      ->setTabColor(\Vtiful\Kernel\Format::COLOR_GREEN)
      ->output();
```


# Background image

Set a tiled background image for the current worksheet. Use the path-based variant when the image is on disk, or the buffer-based variant when the bytes already live in memory (streaming, S3, …).

## **Function Prototype**

```php
setBackgroundImage(string $imagePath): self

setBackgroundImageBuffer(string $imageBuffer): self
```

### **string $imagePath**

> Path to a PNG or JPEG file.

### **string $imageBuffer**

> Raw image bytes, e.g. the return value of `file_get_contents()`.

## Example

```php
$config = [
    'path' => './tests'
];

$excel = new \Vtiful\Kernel\Excel($config);

$excel->fileName('tutorial.xlsx', 'sheet1')
      ->setBackgroundImage('./assets/logo.png')
      ->output();
```

```php
$excel = new \Vtiful\Kernel\Excel($config);

$buffer = file_get_contents('./assets/logo.png');

$excel->fileName('tutorial.xlsx', 'sheet1')
      ->setBackgroundImageBuffer($buffer)
      ->output();
```


# Memory model

xlswriter offers two memory strategies for writing files:

* **Normal mode** — the entire workbook is held in memory; fastest but proportional to data size.
* [**Fixed memory mode**](/english/memory/fixed-memory) — rows are flushed to disk as they are written, keeping peak memory under \~1 MB regardless of file size. Recommended for exporting millions of rows.

Pick fixed memory mode whenever the dataset may not fit comfortably in PHP's `memory_limit`.


# Fixed memory mode

## **Memory**

Maximum memory usage = maximum one row of data usage

## **Function Prototype**

```php
constMemory(string $fileName);
```

## Example

```php
$config = ['path' => './tests'];
$excel = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->constMemory('tutorial01.xlsx');
$fileHandle = $fileObject->getHandle();

$format = new \Vtiful\Kernel\Format($fileHandle);
$boldStyle = $format->bold()->toResource();

$fileObject->header(['name', 'age'])
     ->data([['viest', 21]])
     ->setRow('A1', 10, $boldStyle)
     ->output();
```


# Style

Styles are built with `Vtiful\Kernel\Format` and applied to cells, rows or columns. A single `Format` handle can be reused across many cells.

Pages in this section:

* [Combination style](/english/style/combination) — chain multiple style methods on one handle.
* [Global default style](/english/style/default-format) — apply a style to every cell of a worksheet.

For the full catalogue of individual style attributes, see [Style list](/english/style-list).


# Combination style

Combine multiple styles into one new style applied to the cell

```php
// Combine bold and italic into one style
$format          = new \Vtiful\Kernel\Format($fileHandle);
$boldItalicStyle = $format->bold()->italic()->toResource();
```


# Global default style

Setting the global default style will affect the style of all written cells;

## **Function prototype**

```php
defaultFormat(resource $formatHandler)
```

### **resource $formatHandler**

> cell style

## Example

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$excel->fileName('tutorial.xlsx');

$format        = new \Vtiful\Kernel\Format($excel->getHandle());
$colorOneStyle = $format
    ->fontColor(\Vtiful\Kernel\Format::COLOR_ORANGE)
    ->border(\Vtiful\Kernel\Format::BORDER_DASH_DOT)
    ->toResource();

$format        = new \Vtiful\Kernel\Format($excel->getHandle());
$colorTwoStyle = $format
    ->fontColor(\Vtiful\Kernel\Format::COLOR_GREEN)
    ->toResource();

$filePath = $excel
    // Apply the first style as the default
    ->defaultFormat($colorOneStyle)
    ->header(['hello', 'xlswriter'])
    // Apply the second style as the default style
    ->defaultFormat($colorTwoStyle)
    ->data([
        ['hello', 'xlswriter'],
    ])
    ->output();
```


# Style list

Catalogue of every style attribute supported by `Vtiful\Kernel\Format`.

* Font: [italic](/english/style-list/italic), [bold](/english/style-list/bold), [underline](/english/style-list/underline), [strikethrough](/english/style-list/strikethrough), [size](/english/style-list/font-size), [color](/english/style-list/font-color), [family](/english/style-list/font)
* Layout: [align](/english/style-list/align), [text wrap](/english/style-list/text-wrap), [rotation](/english/style-list/rotation), [indent](/english/style-list/indent)
* Borders: [cell border](/english/style-list/cell-border), [border of the four sides](/english/style-list/border-four-sides), [border color](/english/style-list/border-color), [border style constants](/english/style-list/border-style-constants)
* Fill: [background color](/english/style-list/background-color), [color constants](/english/style-list/color-constants)
* Other: [number format](/english/style-list/number), [cell protection](/english/style-list/cell-protection)


# Italic

```php
$format      = new \Vtiful\Kernel\Format($fileHandle);
$italicStyle = $format->italic()->toResource();
```


# Align

## **Function Prototype**

```php
align(resource $resourchHandle, Format::const ...$style): \Vtiful\Kernel\Format
```

## Example

```php
$format     = new \Vtiful\Kernel\Format($fileHandle);
$alignStyle = $format
    ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER)
    ->toResource();
```

## **Style**

```php
Format::FORMAT_ALIGN_LEFT;                 // horizontally left aligned
Format::FORMAT_ALIGN_CENTER;               // Align in horizontal drama
Format::FORMAT_ALIGN_RIGHT;                // horizontal right alignment
Format::FORMAT_ALIGN_FILL;                 // horizontal fill alignment
Format::FORMAT_ALIGN_JUSTIFY;              // Horizontal justification
Format::FORMAT_ALIGN_CENTER_ACROSS;        // Horizontal center alignment
Format::FORMAT_ALIGN_DISTRIBUTED;          // Disperse alignment
Format::FORMAT_ALIGN_VERTICAL_TOP;         // top vertical alignment
Format::FORMAT_ALIGN_VERTICAL_BOTTOM;      // bottom vertical alignment
Format::FORMAT_ALIGN_VERTICAL_CENTER;      // Align in vertical drama
Format::FORMAT_ALIGN_VERTICAL_JUSTIFY;     // Vertical justification
Format::FORMAT_ALIGN_VERTICAL_DISTRIBUTED; // Vertically dispersing alignment
```


# Strikethrough

```php
$format = new \Vtiful\Kernel\Format($fileHandle);
$style  = $format->strikeout()->toResource();
```


# Underline

## **Function Prototype**

```php
Underline(resource $resourchHandle, Format::const $style): \Vtiful\Kernel\Format
```

## Example

```php
$format         = new \Vtiful\Kernel\Format($fileHandle);
$underlineStyle = $format->underline(Format::UNDERLINE_SINGLE)->toResource();
```

## **Style**

```php
Format::UNDERLINE_SINGLE;            // Single underline
Format::UNDERLINE_DOUBLE;            // double underline
Format::UNDERLINE_SINGLE_ACCOUNTING; // Accounting underline
Format::UNDERLINE_DOUBLE_ACCOUNTING; // Accounting double underline
```


# Text wrap

If the text inside the cell contains `\n` , the newline style will be processed.

```php
$format    = new \Vtiful\Kernel\Format($fileHandle);
$wrapStyle = $format->wrap()->toResource();
```


# Font color

## **Function Prototype**

```php
fontColor(int $color): self
```

### **int $color**

> RGB hexadecimal value or color constant

## Example

```php
$config = [
     'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

// Create a style resource
$format = new \Vtiful\Kernel\Format($fileHandle);
$colorStyle = $format->fontColor(\Vtiful\Kernel\Format::COLOR_ORANGE)->toResource();

$filePath = $fileObject->header(['name', 'age'])
     ->data([
         ['viest', 21],
         ['wjx', 21]
     ])
     ->setRow('A1', 50, $colorStyle) // Apply style
     ->output();
```


# Font size

## **Function Prototype**

```php
fontSize(double $size);
```

### **double $size**

> cell font size

## Example

```php
$config = [
     'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

// Create a style resource
$format = new \Vtiful\Kernel\Format($fileHandle);
$style = $format->fontSize(30)->toResource();

$filePath = $fileObject->header(['name', 'age'])
     ->data([
         ['viest', 21],
         ['wjx', 21]
     ])
     ->setRow('A1', 50, $style) // Apply style
     ->setRow('A2:A3', 50, $style) // Apply style
     ->output();
```


# Bold

```php
$format    = new \Vtiful\Kernel\Format($fileHandle);
$boldStyle = $format->bold()->toResource();
```


# Cell border

## **Function Prototype**

```php
Border(int $borderStyle): \Vtiful\Kernel\Format
```

## example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$data = [
    ['viest1', 21, 100, "A"],
    ['viest2', 20, 80, "B"],
    ['viest3', 22, 70, "C"],
];

$format = new \Vtiful\Kernel\Format($fileHandle);

// Create a border style
$borderStyle = $format
    ->border(\Vtiful\Kernel\Format::BORDER_THIN)
    ->toResource();

$fileObject->header(['name', 'age', 'score', 'level'])
    ->data($data)
    ->setRow('A1', 20, $borderStyle)
    ->output();
```


# Border of the four sides

Apply a different border style to each of the cell's four sides.

## **Function Prototype**

```php
borderOfTheFourSides(
    int $top    = Format::BORDER_NONE,
    int $right  = Format::BORDER_NONE,
    int $bottom = Format::BORDER_NONE,
    int $left   = Format::BORDER_NONE
): self
```

### **int $top / $right / $bottom / $left**

> Border style of each side, taken from the `Format::BORDER_*` constants (e.g. `BORDER_THIN`, `BORDER_MEDIUM`, `BORDER_DASHED`). Any argument that is omitted or `null` leaves that side blank.

Pair this with `borderColorOfTheFourSides()` when you also want each side painted in a different color.

## Example

```php
$config = [
    'path' => './tests'
];

$excel      = new \Vtiful\Kernel\Excel($config);
$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$borderStyle = (new \Vtiful\Kernel\Format($fileHandle))
    ->borderOfTheFourSides(
        \Vtiful\Kernel\Format::BORDER_THIN,    // top
        \Vtiful\Kernel\Format::BORDER_MEDIUM,  // right
        \Vtiful\Kernel\Format::BORDER_THIN,    // bottom
        \Vtiful\Kernel\Format::BORDER_DASHED   // left
    )
    ->toResource();

$fileObject->header(['name', 'score'])
    ->setRow('A1', 20, $borderStyle)
    ->output();
```


# Border color

## **Function Prototype**

```php
borderColor(int $color): self

borderColorOfTheFourSides(
    int $topColor    = -1,
    int $rightColor  = -1,
    int $bottomColor = -1,
    int $leftColor   = -1
): self
```

### **int $color**

> Set the color of all four border sides at once. Pass a `0xRRGGBB` integer or a `Format::COLOR_*` constant.

### **int $topColor / $rightColor / $bottomColor / $leftColor**

> Set the four sides individually. Any argument that is omitted or `null` keeps the default color for that side.

The border line itself must be configured first through `border()` or `borderOfTheFourSides()`; setting only the color will not draw any line.

## Example

```php
$config = [
    'path' => './tests'
];

$excel      = new \Vtiful\Kernel\Excel($config);
$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

// Same color on every side.
$singleColor = (new \Vtiful\Kernel\Format($fileHandle))
    ->border(\Vtiful\Kernel\Format::BORDER_THIN)
    ->borderColor(\Vtiful\Kernel\Format::COLOR_RED)
    ->toResource();

// Different color on each side.
$mixedColor = (new \Vtiful\Kernel\Format($fileHandle))
    ->borderOfTheFourSides(
        \Vtiful\Kernel\Format::BORDER_THIN,
        \Vtiful\Kernel\Format::BORDER_THIN,
        \Vtiful\Kernel\Format::BORDER_THIN,
        \Vtiful\Kernel\Format::BORDER_THIN
    )
    ->borderColorOfTheFourSides(
        \Vtiful\Kernel\Format::COLOR_RED,
        \Vtiful\Kernel\Format::COLOR_GREEN,
        \Vtiful\Kernel\Format::COLOR_BLUE,
        \Vtiful\Kernel\Format::COLOR_YELLOW
    )
    ->toResource();

$fileObject->header(['name', 'score'])
    ->setRow('A1', 20, $singleColor)
    ->setRow('A2', 20, $mixedColor)
    ->output();
```


# Border style constants

```php
BORDER_THIN                // thin border style
BORDER_MEDIUM              // Medium border style
BORDER_DASHED              // dashed border style
BORDER_DOTTED              // dashed border style
BORDER_THICK               // thick border style
BORDER_DOUBLE              // bilateral style
BORDER_HAIR                // hair border style
BORDER_MEDIUM_DASHED       // medium dashed border style
BORDER_DASH_DOT            // dash border style
BORDER_MEDIUM_DASH_DOT     // medium dotted line border style
BORDER_DASH_DOT_DOT        // Dash-dot-dot border style
BORDER_MEDIUM_DASH_DOT_DOT // medium dotted line border style
BORDER_SLANT_DASH_DOT      // slanted dotted line border style
```


# Background color

## **Function Prototype**

```php
background(int $color, int $pattern = self::PATTERN_SOLID): self
```

### **int $color**

> color const or RGB hex

### **int $pattern**

> pattern style

## Example

```php
$format = new \Vtiful\Kernel\Format($fileHandle);

$backgroundStyle  = $format->background(
   \Vtiful\Kernel\Format::COLOR_RED
)->toResource();
```


# Color constants

```php
Format::COLOR_BLACK
Format::COLOR_BLUE
Format::COLOR_BROWN
Format::COLOR_CYAN
Format::COLOR_GRAY
Format::COLOR_GREEN
Format::COLOR_LIME
Format::COLOR_MAGENTA
Format::COLOR_NAVY
Format::COLOR_ORANGE
Format::COLOR_PINK
Format::COLOR_PURPLE
Format::COLOR_RED
Format::COLOR_SILVER
Format::COLOR_WHITE
Format::COLOR_YELLOW
```


# Font

## **Function Prototype**

```php
Font(string $fontName): self
```

### **string $fontName**

> font name, font must exist in this machine

## Example

```php
$format = new \Vtiful\Kernel\Format($fileHandle);
$fontStyle = $format->font('FontName')->toResource();
```


# Number format

## **Function Prototype**

```php
Number(string $format): self
```

### **string $format**

> array format string

```
"0.000"
"#,##0"
"#,##0.00"
"0.00"
```

## Example

```php
$config = [
     'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

// Create a style resource
$format = new \Vtiful\Kernel\Format($fileHandle);
$numberStyle = $format->number('#,##0')->toResource();

$filePath = $fileObject->header(['name', 'balance'])
     ->data([
         ['viest', 10000],
         ['wjx', 100000]
     ])
     ->setColumn('B:B', 50, $numberStyle) // Apply style
     ->output();
```


# Rotation

Rotate the text inside a cell.

## **Function Prototype**

```php
rotation(int $angle): self
```

### **int $angle**

> Angle in degrees.
>
> * A positive value rotates the text counter-clockwise (e.g. `45` tilts up-and-to-the-left).
> * A negative value rotates clockwise (e.g. `-45` tilts down-and-to-the-right).
> * Valid range is `-90..90`.
> * The special value `270` lays the text out vertically (each character stacked beneath the previous one).
>
> Values outside the valid range are silently ignored.

## Example

```php
$config = [
    'path' => './tests'
];

$excel      = new \Vtiful\Kernel\Excel($config);
$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$rotate45 = (new \Vtiful\Kernel\Format($fileHandle))
    ->rotation(45)
    ->toResource();

$vertical = (new \Vtiful\Kernel\Format($fileHandle))
    ->rotation(270)
    ->toResource();

$fileObject->header(['flat', 'tilted', 'vertical'])
    ->insertText(1, 0, 'normal')
    ->insertText(1, 1, 'tilted',   null, $rotate45)
    ->insertText(1, 2, 'vertical', null, $vertical)
    ->output();
```


# Indent

Add a left indent to the text inside a cell.

## **Function Prototype**

```php
indent(int $level): self
```

### **int $level**

> Indent level, in the range `0..15`. Each level is roughly the width of three spaces.

## Example

```php
$config = [
    'path' => './tests'
];

$excel      = new \Vtiful\Kernel\Excel($config);
$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$level1 = (new \Vtiful\Kernel\Format($fileHandle))
    ->indent(1)
    ->toResource();

$level3 = (new \Vtiful\Kernel\Format($fileHandle))
    ->indent(3)
    ->toResource();

$fileObject->header(['title'])
    ->insertText(1, 0, 'level 1', null, $level1)
    ->insertText(2, 0, 'level 3', null, $level3)
    ->output();
```


# Cell protection

Control whether a cell is locked and whether its formula is visible. These three flags only take effect once the worksheet itself has been protected through `protection()` — calling `locked()` / `unlocked()` alone does not turn protection on.

## **Function Prototype**

```php
locked(): self

unlocked(): self

hidden(): self
```

### **locked()**

> Mark the cell as locked. This is Excel's default; call it explicitly only when you need to override an earlier `unlocked()`.

### **unlocked()**

> Mark the cell as unlocked, so it remains editable after the worksheet has been protected.

### **hidden()**

> Hide the cell's formula from the formula bar once the worksheet is protected.

## Example

```php
$config = [
    'path' => './tests'
];

$excel      = new \Vtiful\Kernel\Excel($config);
$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$editable = (new \Vtiful\Kernel\Format($fileHandle))
    ->unlocked()
    ->toResource();

$secret = (new \Vtiful\Kernel\Format($fileHandle))
    ->locked()
    ->hidden()
    ->toResource();

$fileObject->header(['input', 'formula'])
    ->insertText(1, 0, 'editable', null, $editable)
    ->insertFormula(1, 1, '=A2*2', $secret)
    ->protection()
    ->output();
```


# Chart

Charts are built with `Vtiful\Kernel\Chart`, fed series via `series()` / `categories()`, then inserted into a worksheet.

> Build the `Chart` instance into a variable first, then call `output()` on the workbook. Chaining `(new Chart())->series()->toResource()` inline triggers a double-insert warning.

Pages in this section:

* [Chart type constants](/english/chart/chart-type-constants)
* [Data input](/english/chart/data-input)
* Examples: [doughnut](/english/chart/doughnut), [area](/english/chart/area), [histogram](/english/chart/histogram), [bar](/english/chart/bar)


# Chart type constants

## Namespace

```php
\Vtiful\Kernel\Chart
```

## Class Const

| Constant                                | Chart Type                           |
| --------------------------------------- | ------------------------------------ |
| CHART\_NONE                             | None                                 |
| CHART\_AREA                             | Area Chart                           |
| CHART\_AREA\_STACKED                    | Area Chart - Stacking                |
| CHART\_AREA\_STACKED\_PERCENT           | Area Chart - Percentage of Stacking  |
| CHART\_BAR                              | Bar Chart                            |
| CHART\_BAR\_STACKED                     | Bar Chart - Stacking                 |
| CHART\_BAR\_STACKED\_PERCENT            | Bar Chart - Percentage of Stacking   |
| CHART\_COLUMN                           | Histogram                            |
| CHART\_COLUMN\_STACKED                  | Histogram - Stacking                 |
| CHART\_COLUMN\_STACKED\_PERCENT         | Histogram - Percentage of Stacking   |
| CHART\_DOUGHNUT                         | Doughnut                             |
| CHART\_LINE                             | Line Chart                           |
| CHART\_PIE                              | Pie Chart                            |
| CHART\_SCATTER                          | Scatter Plot                         |
| CHART\_SCATTER\_STRAIGHT                | Scatter Plot - Straight Line         |
| CHART\_SCATTER\_STRAIGHT\_WITH\_MARKERS | Scatter Plot - Straight Link Mark    |
| CHART\_SCATTER\_SMOOTH                  | Scatter Plot - Smooth Line           |
| CHART\_SCATTER\_SMOOTH\_WITH\_MARKERS   | Scatter Plot - Smooth Line Link Mark |
| CHART\_RADAR                            | Radar Chart                          |
| CHART\_RADAR\_WITH\_MARKERS             | Radar Chart - with marker            |
| CHART\_RADAR\_FILLED                    | Radar Chart - Fill                   |


# Data input

## **Function Prototype**

```php
Series(string $value,[ string $categories])
```

### **string $value**

> Chart worksheet and cell span where individual category data is located

```php
Sheet1 ! $A$1 : $A$5
Worksheet ! Start cell : End cell
```

### **string $categories**

> Category Name

## example

```php
$config = ['path' => './tests'];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$chart = new \Vtiful\Kernel\Chart($fileHandle, \Vtiful\Kernel\Chart::CHART_COLUMN);

$chartResource = $chart->series('Sheet1!$A$1:$A$5')
     ->series('Sheet1!$B$1:$B$5')
     ->series('Sheet1!$C$1:$C$5')
     ->toResource();

$filePath = $fileObject->data([
     [1, 2, 3],
     [2, 4, 6],
     [3, 6, 9],
     [4, 8, 12],
     [5, 10, 15],
])->insertChart(0, 3, $chartResource)->output();
```


# Doughnut chart

## 2D ring chart

![](/files/-LngPPMOaT67LD1RjN6R)

```php
<?php declare(strict_types = 1);

$config = [
    'path' => './tests',
];

$dataHeader = [
    'Category', 'Values',
];

$dataRows = [
    ['Glazed', 50],
    ['Chocolate', 35],
    ['Cream', 15],
];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$chart = new \Vtiful\Kernel\Chart($fileHandle, \Vtiful\Kernel\Chart::CHART_DOUGHNUT);

$chartResource = $chart
    // series(string $value [, string $category])
    ->series('=Sheet1!$B$2:$B$4', '=Sheet1!$A$2:$A$4')
    ->seriesName('Doughnut sales data')
    ->title('Popular Doughnut Types')
    ->style(10)
    ->toResource();

$filePath = $fileObject
    ->header($dataHeader)
    ->data($dataRows)
    ->insertChart(0, 4, $chartResource)
    ->output();
```


# Area chart

![](/files/-LmmjnIV1MTnvVzYPtAB)

```php
$config = ['path' => './tests'];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$chart = new \Vtiful\Kernel\Chart($fileHandle, \Vtiful\Kernel\Chart::CHART_AREA);

$chartResource = $chart
    ->series('=Sheet1!$B$2:$B$7', '=Sheet1!$A$2:$A$7')
    ->seriesName('=Sheet1!$B$1')
    ->series('=Sheet1!$C$2:$C$7', '=Sheet1!$A$2:$A$7')
    ->seriesName('=Sheet1!$C$1')
    ->style(11)// Values ​​1 - 48, refer to 48 styles in the Excel 2007 Design tab
    ->axisNameX('Test number') // Set the X axis name
    ->axisNameY('Sample length (mm)') // Set the Y axis name
    ->title('Results of sample analysis') // Set the chart Title
    ->toResource();

$filePath = $fileObject->header(['Number', 'Batch 1', 'Batch 2'])
    ->data([
        [2, 40, 30],
        [3, 40, 25],
        [4, 50, 30],
        [5, 30, 10],
        [6, 25, 5],
        [7, 50, 10],
    ])->insertChart(0, 3, $chartResource)->output();
```


# Histogram chart

![](/files/-LmmjMRNNfPsMOB3Sk-0)

```php
$config = ['path' => './tests'];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$chart = new \Vtiful\Kernel\Chart($fileHandle, \Vtiful\Kernel\Chart::CHART_COLUMN);

$chartResource = $chart->series('Sheet1!$A$1:$A$5')
    ->series('Sheet1!$B$1:$B$5')
    ->series('Sheet1!$C$1:$C$5')
    ->toResource();

$filePath = $fileObject->data([
    [1, 2, 3],
    [2, 4, 6],
    [3, 6, 9],
    [4, 8, 12],
    [5, 10, 15],
])->insertChart(0, 3, $chartResource)->output();
```


# Bar chart

## Default Bar Chart

![](/files/-LngHZDmMjW5xsffnw8g)

```php
<?php declare(strict_types = 1);

$config = [
    'path' => './tests',
];

$dataHeader = [
    'Number', 'Batch 1', 'Batch 2',
];

$dataRows   = [
    [2, 10, 30],
    [3, 40, 60],
    [4, 50, 70],
    [5, 20, 50],
    [6, 10, 40],
    [7, 50, 30],
];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$chart = new \Vtiful\Kernel\Chart($fileHandle, \Vtiful\Kernel\Chart::CHART_BAR);

$chartResource = $chart
    // series(string $value [, string $category])
    ->series('=Sheet1!$B$2:$B$7', '=Sheet1!$A$2:$A$7')
    ->seriesName('=Sheet1!$B$1')
    ->series('=Sheet1!$C$2:$C$7', '=Sheet1!$A$2:$A$7')
    ->seriesName('=Sheet1!$C$1')
    ->axisNameX('Test number')
    ->axisNameY('Sample length (mm)')
    ->title('Results of sample analysis')
    ->style(11)
    ->toResource();

$filePath = $fileObject
    ->header($dataHeader)
    ->data($dataRows)
    ->insertChart(0, 4, $chartResource)
    ->output();
```

## stacked bar chart

![](/files/-LngJc12cxIwZZmOIaqo)

```php
<?php declare(strict_types = 1);

$config = [
    'path' => './tests',
];

$dataHeader = [
    'Number', 'Batch 1', 'Batch 2',
];

$dataRows   = [
    [2, 10, 30],
    [3, 40, 60],
    [4, 50, 70],
    [5, 20, 50],
    [6, 10, 40],
    [7, 50, 30],
];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$chart = new \Vtiful\Kernel\Chart($fileHandle, \Vtiful\Kernel\Chart::CHART_BAR_STACKED);

$chartResource = $chart
    // series(string $value [, string $category])
    ->series('=Sheet1!$B$2:$B$7', '=Sheet1!$A$2:$A$7')
    ->seriesName('=Sheet1!$B$1')
    ->series('=Sheet1!$C$2:$C$7', '=Sheet1!$A$2:$A$7')
    ->seriesName('=Sheet1!$C$1')
    ->axisNameX('Test number')
    ->axisNameY('Sample length (mm)')
    ->title('Results of sample analysis')
    ->style(12)
    ->toResource();

$filePath = $fileObject
    ->header($dataHeader)
    ->data($dataRows)
    ->insertChart(0, 4, $chartResource)
    ->output();
```

## percentage bar chart

![](/files/-LngL3fdBGRHU1E0-yzM)

```php
<?php declare(strict_types = 1);

$config = [
    'path' => './tests',
];

$dataHeader = [
    'Number', 'Batch 1', 'Batch 2',
];

$dataRows   = [
    [2, 10, 30],
    [3, 40, 60],
    [4, 50, 70],
    [5, 20, 50],
    [6, 10, 40],
    [7, 50, 30],
];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$chart = new \Vtiful\Kernel\Chart($fileHandle, \Vtiful\Kernel\Chart::CHART_BAR_STACKED_PERCENT);

$chartResource = $chart
    // series(string $value [, string $category])
    ->series('=Sheet1!$B$2:$B$7', '=Sheet1!$A$2:$A$7')
    ->seriesName('=Sheet1!$B$1')
    ->series('=Sheet1!$C$2:$C$7', '=Sheet1!$A$2:$A$7')
    ->seriesName('=Sheet1!$C$1')
    ->axisNameX('Test number')
    ->axisNameY('Sample length (mm)')
    ->title('Results of sample analysis')
    ->style(13)
    ->toResource();

$filePath = $fileObject
    ->header($dataHeader)
    ->data($dataRows)
    ->insertChart(0, 4, $chartResource)
    ->output();
```


# Conditional format

Conditional formatting applies styles or visualisations to cells whose values match a rule. Common use cases:

* Highlight cells that match a condition (e.g. paint the background red when the value is greater than 50)
* Draw data bars inside cells to compare magnitudes at a glance
* Apply a colour scale gradient across a range
* Mark buckets with an icon set such as a three-colour traffic light
* Filter cells by built-in rules: text / dates / duplicates / top-N / bottom-N

xlswriter builds a rule with `Vtiful\Kernel\ConditionalFormat`, then attaches it to a worksheet via `Excel::conditionalFormatCell` or `Excel::conditionalFormatRange`.

## Methods

```php
Excel::conditionalFormatCell(string $rangeA1, \Vtiful\Kernel\ConditionalFormat $cf): self
Excel::conditionalFormatRange(string $rangeA1, \Vtiful\Kernel\ConditionalFormat $cf): self
```

### **string $rangeA1**

> A single cell or a range in A1 notation, e.g. `"A1"`, `"A1:A10"`, `"B2:D8"`.

### **\Vtiful\Kernel\ConditionalFormat $cf**

> A rule object built with `new \Vtiful\Kernel\ConditionalFormat()` and configured via chained calls. Pass the object itself; there is no `toResource()` step.

## Quick start

The example below highlights every cell in `A2:A4` whose value is greater than `50` with a red background and white font:

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

// Style applied when the rule matches
$highlight = (new \Vtiful\Kernel\Format($fileHandle))
    ->background(\Vtiful\Kernel\Format::COLOR_RED)
    ->fontColor(\Vtiful\Kernel\Format::COLOR_WHITE)
    ->toResource();

// Build the rule: cell type + greater than 50 + apply $highlight
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_CELL)
   ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_GREATER_THAN)
   ->value(50)
   ->format($highlight);

$fileObject->header(['score'])
    ->data([[10], [60], [80]])
    ->conditionalFormatRange('A2:A4', $cf)
    ->output();
```

## Pages in this chapter

| Page                                                                 | Topic                                       |
| -------------------------------------------------------------------- | ------------------------------------------- |
| [Single cell rule](/english/conditional-format/cell)                 | Apply one rule to a single cell             |
| [Range rule](/english/conditional-format/range)                      | Apply one rule to an A1 range               |
| [Data bar](/english/conditional-format/data-bar)                     | Data bar configuration                      |
| [Icon set](/english/conditional-format/icons)                        | Icon set configuration                      |
| [Multi range](/english/conditional-format/multi-range)               | Apply one rule across non-contiguous ranges |
| [Type constants](/english/conditional-format/type-constants)         | `TYPE_*` constants                          |
| [Criteria constants](/english/conditional-format/criteria-constants) | `CRITERIA_*` constants                      |


# Single cell rule

Apply a conditional format rule to a single cell.

## Methods

```php
conditionalFormatCell(string $rangeA1, \Vtiful\Kernel\ConditionalFormat $cf): self
```

### **string $rangeA1**

> Target cell address in A1 notation, e.g. `"A1"` or `"C5"`.

### **\Vtiful\Kernel\ConditionalFormat $cf**

> A rule object configured via `type()` / `criteria()` / `value()` / `format()` etc.

## Greater-than

Highlight `A2` with a red background and white font when its value is greater than `50`:

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$highlight = (new \Vtiful\Kernel\Format($fileHandle))
    ->background(\Vtiful\Kernel\Format::COLOR_RED)
    ->fontColor(\Vtiful\Kernel\Format::COLOR_WHITE)
    ->toResource();

$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_CELL)
   ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_GREATER_THAN)
   ->value(50)
   ->format($highlight);

$fileObject->header(['score'])
    ->insertText(1, 0, 80) // A2 = 80, fires the rule
    ->conditionalFormatCell('A2', $cf)
    ->output();
```

## Between

Highlight `B2` when its value is between `60` and `90` (inclusive):

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$highlight = (new \Vtiful\Kernel\Format($fileHandle))
    ->background(\Vtiful\Kernel\Format::COLOR_YELLOW)
    ->toResource();

$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_CELL)
   ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_BETWEEN)
   ->minimum(60)
   ->maximum(90)
   ->format($highlight);

$fileObject->header(['name', 'score'])
    ->data([['viest', 75]])
    ->conditionalFormatCell('B2', $cf)
    ->output();
```

## Text contains

Highlight `A2` when its text contains `error`:

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_TEXT)
   ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_TEXT_CONTAINING)
   ->valueString('error')
   ->format($highlight);

$fileObject->conditionalFormatCell('A2', $cf);
```


# Range rule

Apply a conditional format rule to an A1 range. Each cell in the range is evaluated independently.

## Methods

```php
conditionalFormatRange(string $rangeA1, \Vtiful\Kernel\ConditionalFormat $cf): self
```

### **string $rangeA1**

> Target range in A1 notation, e.g. `"A2:A10"` or `"B2:D20"`.

### **\Vtiful\Kernel\ConditionalFormat $cf**

> A rule object built with `\Vtiful\Kernel\ConditionalFormat`.

## Greater-than across a range

Mark every cell in `A2:A10` greater than `50` with a red background and white font:

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$highlight = (new \Vtiful\Kernel\Format($fileHandle))
    ->background(\Vtiful\Kernel\Format::COLOR_RED)
    ->fontColor(\Vtiful\Kernel\Format::COLOR_WHITE)
    ->toResource();

$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_CELL)
   ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_GREATER_THAN)
   ->value(50)
   ->format($highlight);

$fileObject->header(['score'])
    ->data([[10], [40], [55], [60], [70], [80], [90], [100], [120]])
    ->conditionalFormatRange('A2:A10', $cf)
    ->output();
```

## Two-colour scale

Apply a white-to-green gradient across `A2:A10`:

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_2_COLOR_SCALE)
   ->minimumRule(\Vtiful\Kernel\ConditionalFormat::RULE_MINIMUM)
   ->minimumColor(0xFFFFFF) // white
   ->maximumRule(\Vtiful\Kernel\ConditionalFormat::RULE_MAXIMUM)
   ->maximumColor(0x63BE7B); // green

$fileObject->conditionalFormatRange('A2:A10', $cf);
```

## Three-colour scale

Low / mid / high red-yellow-green gradient:

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_3_COLOR_SCALE)
   ->minimumRule(\Vtiful\Kernel\ConditionalFormat::RULE_MINIMUM)
   ->minimumColor(0xF8696B)
   ->middleRule(\Vtiful\Kernel\ConditionalFormat::RULE_PERCENTILE)
   ->middle(50)
   ->middleColor(0xFFEB84)
   ->maximumRule(\Vtiful\Kernel\ConditionalFormat::RULE_MAXIMUM)
   ->maximumColor(0x63BE7B);

$fileObject->conditionalFormatRange('A2:A10', $cf);
```

## Formula rule

Match cells via an arbitrary Excel formula (here: highlight even rows):

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_FORMULA)
   ->valueString('=MOD(ROW(),2)=0')
   ->format($highlight);

$fileObject->conditionalFormatRange('A2:A10', $cf);
```


# Data bar

A data bar draws a horizontal coloured bar inside the cell whose length is proportional to the value. It is the easiest way to compare the magnitudes in a column at a glance.

## Methods

```php
ConditionalFormat::barColor(int $color): self
ConditionalFormat::barOnly(bool $on = true): self
ConditionalFormat::barSolid(bool $on = true): self
ConditionalFormat::dataBar2010(bool $on = true): self
ConditionalFormat::barNegativeColor(int $color): self
ConditionalFormat::barBorderColor(int $color): self
ConditionalFormat::barNegativeBorderColor(int $color): self
ConditionalFormat::barNoBorder(bool $on = true): self
ConditionalFormat::barDirection(int $direction): self
ConditionalFormat::barAxisPosition(int $position): self
ConditionalFormat::barAxisColor(int $color): self
```

### **int $color**

> Colour value as a `0xRRGGBB` integer, or one of the `\Vtiful\Kernel\Format::COLOR_*` constants.

### **bool $on**

> Toggle, defaults to `true`. `barOnly` hides the cell value and shows only the bar; `barSolid` paints a flat fill (Excel 2010 style); `dataBar2010` enables the Excel 2010 extension attributes; `barNoBorder` removes the bar border.

### **int $direction**

> Bar direction: `BAR_DIRECTION_CONTEXT` / `BAR_DIRECTION_LEFT_TO_RIGHT` / `BAR_DIRECTION_RIGHT_TO_LEFT`.

### **int $position**

> Axis position: `BAR_AXIS_AUTOMATIC` / `BAR_AXIS_MIDPOINT` / `BAR_AXIS_NONE`.

## Basic data bar

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_DATA_BAR)
   ->barColor(0x638EC6); // blue

$excel->fileName('tutorial.xlsx')
    ->header(['score'])
    ->data([[10], [40], [55], [60], [70], [80], [90], [100]])
    ->conditionalFormatRange('A2:A9', $cf)
    ->output();
```

## Data bar with positive and negative values

Enable the Excel 2010 extension so negative values use a different colour, and place the axis at the midpoint:

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_DATA_BAR)
   ->dataBar2010()
   ->barColor(0x63BE7B)            // green for positive
   ->barNegativeColor(0xF8696B)    // red for negative
   ->barAxisPosition(\Vtiful\Kernel\ConditionalFormat::BAR_AXIS_MIDPOINT)
   ->barAxisColor(0x000000)
   ->barSolid();

$excel->fileName('tutorial.xlsx')
    ->header(['delta'])
    ->data([[-30], [-10], [0], [20], [50], [70]])
    ->conditionalFormatRange('A2:A7', $cf)
    ->output();
```

## Bar only (hide the number)

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_DATA_BAR)
   ->barColor(0x638EC6)
   ->barOnly()       // hide the cell value
   ->barNoBorder();  // no border around the bar

$excel->conditionalFormatRange('A2:A9', $cf);
```


# Icon set

An icon set draws a small icon to the left of the cell value based on the cell's relative position within the range. Common styles include three-colour traffic lights and five-star ratings.

## Methods

```php
ConditionalFormat::iconStyle(int $style): self
ConditionalFormat::reverseIcons(bool $on = true): self
ConditionalFormat::iconsOnly(bool $on = true): self
```

### **int $style**

> Icon style constant. See `\Vtiful\Kernel\ConditionalFormat::ICONS_3_*` / `ICONS_4_*` / `ICONS_5_*`.

### **bool $on**

> Toggle, defaults to `true`. `reverseIcons` flips the icon order (so high values use the "low" icon); `iconsOnly` hides the cell value and shows only the icon.

## Three-colour traffic lights

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_ICON_SETS)
   ->iconStyle(\Vtiful\Kernel\ConditionalFormat::ICONS_3_TRAFFIC_LIGHTS_UNRIMMED);

$excel->fileName('tutorial.xlsx')
    ->header(['score'])
    ->data([[10], [40], [55], [60], [70], [80], [90], [100]])
    ->conditionalFormatRange('A2:A9', $cf)
    ->output();
```

## Five-star rating (icons only)

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_ICON_SETS)
   ->iconStyle(\Vtiful\Kernel\ConditionalFormat::ICONS_5_RATINGS)
   ->iconsOnly();

$excel->conditionalFormatRange('A2:A9', $cf);
```

## Reversed icon order

For example, three coloured arrows: by default "higher = green"; with `reverseIcons` it becomes "lower = green":

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_ICON_SETS)
   ->iconStyle(\Vtiful\Kernel\ConditionalFormat::ICONS_3_ARROWS_COLORED)
   ->reverseIcons();

$excel->conditionalFormatRange('A2:A9', $cf);
```

## Icon style constants

All defined under `\Vtiful\Kernel\ConditionalFormat`:

```php
// Three icons
const ICONS_3_ARROWS_COLORED;
const ICONS_3_ARROWS_GRAY;
const ICONS_3_FLAGS;
const ICONS_3_TRAFFIC_LIGHTS_UNRIMMED;
const ICONS_3_TRAFFIC_LIGHTS_RIMMED;
const ICONS_3_SIGNS;
const ICONS_3_SYMBOLS_CIRCLED;
const ICONS_3_SYMBOLS_UNCIRCLED;

// Four icons
const ICONS_4_ARROWS_COLORED;
const ICONS_4_ARROWS_GRAY;
const ICONS_4_RED_TO_BLACK;
const ICONS_4_RATINGS;
const ICONS_4_TRAFFIC_LIGHTS;

// Five icons
const ICONS_5_ARROWS_COLORED;
const ICONS_5_ARROWS_GRAY;
const ICONS_5_RATINGS;
const ICONS_5_QUARTERS;
```


# Multi range

A single conditional format rule may target several non-contiguous ranges. `stopIfTrue` controls whether subsequent rules are evaluated after this rule fires.

## Methods

```php
ConditionalFormat::multiRange(string $range): self
ConditionalFormat::stopIfTrue(bool $on = true): self
```

### **string $range**

> Multiple A1 ranges separated by spaces, e.g. `"A1:A5 C1:C5 E1:E10"`. This list replaces the contiguous range used by `conditionalFormatRange`, letting one rule cover several disjoint targets.

### **bool $on**

> Whether `stopIfTrue` is enabled, defaults to `true`. When several rules cover the same cell, enabling this prevents later rules from being evaluated once this rule matches.

## Multi-range example

Apply the "highlight values greater than 50" rule to three disjoint ranges `A2:A6`, `C2:C6`, and `E2:E6`:

```php
$config = ['path' => './tests'];
$excel  = new \Vtiful\Kernel\Excel($config);

$fileObject = $excel->fileName('tutorial.xlsx');
$fileHandle = $fileObject->getHandle();

$highlight = (new \Vtiful\Kernel\Format($fileHandle))
    ->background(\Vtiful\Kernel\Format::COLOR_RED)
    ->fontColor(\Vtiful\Kernel\Format::COLOR_WHITE)
    ->toResource();

$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_CELL)
   ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_GREATER_THAN)
   ->value(50)
   ->format($highlight)
   ->multiRange('A2:A6 C2:C6 E2:E6');

// conditionalFormatRange still needs a starting range for the first parameter;
// the actual targets come from the multiRange() string above.
$fileObject->header(['x', '_', 'y', '_', 'z'])
    ->data([
        [10, '', 60, '', 30],
        [70, '', 20, '', 80],
        [55, '', 45, '', 90],
        [40, '', 35, '', 25],
        [85, '', 65, '', 15],
    ])
    ->conditionalFormatRange('A2:A6', $cf)
    ->output();
```

## stopIfTrue example

Use this when one rule must take precedence over another:

```php
$first = new \Vtiful\Kernel\ConditionalFormat();
$first->type(\Vtiful\Kernel\ConditionalFormat::TYPE_CELL)
      ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_GREATER_THAN)
      ->value(90)
      ->format($highlightGold)
      ->stopIfTrue();

$second = new \Vtiful\Kernel\ConditionalFormat();
$second->type(\Vtiful\Kernel\ConditionalFormat::TYPE_CELL)
       ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_GREATER_THAN)
       ->value(60)
       ->format($highlightYellow);

$fileObject->conditionalFormatRange('A2:A10', $first)
           ->conditionalFormatRange('A2:A10', $second);
```

In this example cells greater than 90 receive only the gold style (because `stopIfTrue` blocks the second rule), while cells between 60 and 90 receive only the yellow one.


# Type constants

Constants accepted by `type()` to declare the overall kind of conditional format rule.

## Defining class

```php
\Vtiful\Kernel\ConditionalFormat
```

## Constants

```php
const TYPE_CELL          = ?; // Cell-value rule (combine with criteria + value/minimum/maximum)
const TYPE_TEXT          = ?; // Text rule (contains / not contains / begins with / ends with)
const TYPE_TIME_PERIOD   = ?; // Date/time rule (yesterday / today / this week / etc.)
const TYPE_AVERAGE       = ?; // Average rule (above / below average)
const TYPE_DUPLICATE     = ?; // Duplicate values
const TYPE_UNIQUE        = ?; // Unique values
const TYPE_TOP           = ?; // Top N or top N%
const TYPE_BOTTOM        = ?; // Bottom N or bottom N%
const TYPE_BLANKS        = ?; // Blank cells
const TYPE_NO_BLANKS     = ?; // Non-blank cells
const TYPE_ERRORS        = ?; // Error values
const TYPE_NO_ERRORS     = ?; // Non-error values
const TYPE_FORMULA       = ?; // Custom formula (set with valueString)
const TYPE_2_COLOR_SCALE = ?; // Two-colour scale
const TYPE_3_COLOR_SCALE = ?; // Three-colour scale
const TYPE_DATA_BAR      = ?; // Data bar
const TYPE_ICON_SETS     = ?; // Icon set
```

> The numeric values are decided by the extension internally. Always reference these constants by name; do not hard-code the integers.

## Example

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_CELL)
   ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_GREATER_THAN)
   ->value(50);
```


# Criteria constants

Constants accepted by `criteria()`. They refine the match for `TYPE_CELL` / `TYPE_TEXT` / `TYPE_TIME_PERIOD` / `TYPE_AVERAGE` / `TYPE_TOP` / `TYPE_BOTTOM` rules.

## Defining class

```php
\Vtiful\Kernel\ConditionalFormat
```

## Constants

```php
// Numeric comparison
const CRITERIA_EQUAL_TO;
const CRITERIA_NOT_EQUAL_TO;
const CRITERIA_GREATER_THAN;
const CRITERIA_LESS_THAN;
const CRITERIA_GREATER_THAN_OR_EQUAL_TO;
const CRITERIA_LESS_THAN_OR_EQUAL_TO;
const CRITERIA_BETWEEN;
const CRITERIA_NOT_BETWEEN;

// Text matching (with TYPE_TEXT and valueString)
const CRITERIA_TEXT_CONTAINING;
const CRITERIA_TEXT_NOT_CONTAINING;
const CRITERIA_TEXT_BEGINS_WITH;
const CRITERIA_TEXT_ENDS_WITH;

// Time periods (with TYPE_TIME_PERIOD)
const CRITERIA_TIME_PERIOD_YESTERDAY;
const CRITERIA_TIME_PERIOD_TODAY;
const CRITERIA_TIME_PERIOD_TOMORROW;
const CRITERIA_TIME_PERIOD_LAST_7_DAYS;
const CRITERIA_TIME_PERIOD_LAST_WEEK;
const CRITERIA_TIME_PERIOD_THIS_WEEK;
const CRITERIA_TIME_PERIOD_NEXT_WEEK;
const CRITERIA_TIME_PERIOD_LAST_MONTH;
const CRITERIA_TIME_PERIOD_THIS_MONTH;
const CRITERIA_TIME_PERIOD_NEXT_MONTH;

// Average (with TYPE_AVERAGE)
const CRITERIA_AVERAGE_ABOVE;
const CRITERIA_AVERAGE_BELOW;

// Ranking (with TYPE_TOP / TYPE_BOTTOM; use value() for N or N%)
const CRITERIA_TOP_OR_BOTTOM_PERCENT; // Interprets value() as a percentage
```

## Rule type constants (colour scales / data bars)

Accepted by `minimumRule()` / `middleRule()` / `maximumRule()`:

```php
const RULE_MINIMUM;     // Minimum of the data
const RULE_NUMBER;      // Literal number set with minimum/middle/maximum
const RULE_PERCENT;     // Percentage (0..100)
const RULE_PERCENTILE;  // Percentile (0..100)
const RULE_FORMULA;     // Formula (provided via minimumString etc.)
const RULE_MAXIMUM;     // Maximum of the data
```

## Bar direction constants

Accepted by `barDirection()`:

```php
const BAR_DIRECTION_CONTEXT;       // Follow the system default (typically LTR)
const BAR_DIRECTION_LEFT_TO_RIGHT;
const BAR_DIRECTION_RIGHT_TO_LEFT;
```

## Bar axis position constants

Accepted by `barAxisPosition()`:

```php
const BAR_AXIS_AUTOMATIC; // Auto
const BAR_AXIS_MIDPOINT;  // Midpoint (suitable for bars with positive and negative values)
const BAR_AXIS_NONE;      // No axis
```

## Example

```php
$cf = new \Vtiful\Kernel\ConditionalFormat();
$cf->type(\Vtiful\Kernel\ConditionalFormat::TYPE_CELL)
   ->criteria(\Vtiful\Kernel\ConditionalFormat::CRITERIA_BETWEEN)
   ->minimum(60)
   ->maximum(90);
```


# Data validation

Data validation restricts what users can type into a cell — for example only allowing integers, decimals, dates, or values picked from a list. When a user enters something that breaks the rule, Excel surfaces a warning when the file is opened. Note that the extension itself does **not** check values at write time; it just persists the rule in the workbook.

There are two steps:

1. Build a rule with `Vtiful\Kernel\Validation`.
2. Apply it to a range with `Excel::validation(string $range, $validationHandle)`. `$validationHandle` is the resource returned by `Validation::toResource()`.

## Function prototype

```
\Vtiful\Kernel\Excel::validation(string $range, resource $validationHandle): self
```

### **string $range**

> The cell range to apply the rule to, in A1 notation, for example `A1`, `A1:A10`, `B2:D8`.

### **resource $validationHandle**

> The resource handle returned by `Validation::toResource()`.

## Sub-sections

* [Drop-down list](/english/validation/type-list)
* [Range constraint](/english/validation/criteria-between)
* [Greater than constraint](/english/validation/criteria-greater-than)
* [Validation API reference](/english/validation/api-reference)

## Example

The most common case — restrict `A1:A10` to integers between 1 and 100.

```php
$config = ['path' => './'];
$excel  = new \Vtiful\Kernel\Excel($config);

$validation = new \Vtiful\Kernel\Validation();
$validation->validationType(\Vtiful\Kernel\Validation::TYPE_INTEGER)
    ->criteriaType(\Vtiful\Kernel\Validation::CRITERIA_BETWEEN)
    ->minimumNumber(1)
    ->maximumNumber(100);

$excel->fileName('tutorial.xlsx')
    ->header(['Score'])
    ->validation('A1:A10', $validation->toResource())
    ->output();
```


# Drop-down list

Use `TYPE_LIST` together with `valueList()` to render an in-cell drop-down whose items come from a PHP array of strings.

## Example

```php
$config = ['path' => './'];
$excel  = new \Vtiful\Kernel\Excel($config);

$validation = new \Vtiful\Kernel\Validation();
$validation->validationType(\Vtiful\Kernel\Validation::TYPE_LIST)
    ->valueList(['wjx', 'viest']);

$excel->fileName('tutorial.xlsx')
    ->validation('A1', $validation->toResource())
    ->output();
```

The drop-down items can also be sourced from a worksheet range with `TYPE_LIST_FORMULA` plus `valueFormula('=$E$1:$E$5')`.


# Range constraint

## Literal bounds

### Example

The validation type is `integer` and the criteria is `between`, with minimum `1` and maximum `10`. The value of `A1` therefore must be an integer in the closed range `1..10`.

```php
$config = [
    'path' => './'
];

$validation = new \Vtiful\Kernel\Validation();
$validation->validationType(\Vtiful\Kernel\Validation::TYPE_INTEGER)
    ->criteriaType(\Vtiful\Kernel\Validation::CRITERIA_BETWEEN)
    ->minimumNumber(1)
    ->maximumNumber(10);

$excel    = new \Vtiful\Kernel\Excel($config);
$filePath = $excel->fileName('tutorial.xlsx')
    ->header(['Value'])
    ->validation('A1', $validation->toResource())
    ->insertText(0, 0, 20) // Out-of-range; the write succeeds, Excel flags the cell on open.
    ->output();
```

## Bounds taken from cells

### Example

The validation type is `integer` and the criteria is `between`, with the lower bound read from cell `A1` and the upper bound from `B1`. The value of `C1` must therefore be an integer between the values of `A1` and `B1`.

```php
$config = [
    'path' => './'
];

$validation = new \Vtiful\Kernel\Validation();
$validation->validationType(\Vtiful\Kernel\Validation::TYPE_INTEGER)
    ->criteriaType(\Vtiful\Kernel\Validation::CRITERIA_BETWEEN)
    ->minimumFormula('=A1')
    ->maximumFormula('=B1');

$excel    = new \Vtiful\Kernel\Excel($config);
$filePath = $excel->fileName('tutorial.xlsx')
    ->header([1, 10])
    ->validation('C1', $validation->toResource())
    ->insertText(0, 2, 20)
    ->output();
```


# Greater than constraint

## Example

The validation type is `integer` and the criteria is `greater than`, with target value `20`. The value of `A1` therefore must be an integer strictly greater than 20.

```php
$config = [
    'path' => './'
];

$validation = new \Vtiful\Kernel\Validation();
$validation->validationType(\Vtiful\Kernel\Validation::TYPE_INTEGER)
    ->criteriaType(\Vtiful\Kernel\Validation::CRITERIA_GREATER_THAN)
    ->valueNumber(20);

$excel    = new \Vtiful\Kernel\Excel($config);
$filePath = $excel->fileName('tutorial.xlsx')
    ->validation('A1', $validation->toResource())
    ->insertText(0, 0, 21) // Out-of-range; the write succeeds, Excel flags the cell on open.
    ->output();
```

The same pattern applies to the other one-sided criteria — `CRITERIA_LESS_THAN`, `CRITERIA_GREATER_THAN_OR_EQUAL_TO`, `CRITERIA_LESS_THAN_OR_EQUAL_TO`, `CRITERIA_EQUAL_TO`, `CRITERIA_NOT_EQUAL_TO` — each used together with `valueNumber()`, `valueFormula()` or `valueDatetime()`.


# Validation API reference

`Vtiful\Kernel\Validation` builds a single data-validation rule. Every method except `__construct()` and `toResource()` returns `$this`, so calls can be chained.

## Class constants

### Validation types — `TYPE_*`

```
TYPE_INTEGER          TYPE_INTEGER_FORMULA
TYPE_DECIMAL          TYPE_DECIMAL_FORMULA
TYPE_LIST             TYPE_LIST_FORMULA
TYPE_DATE             TYPE_DATE_FORMULA       TYPE_DATE_NUMBER
TYPE_TIME             TYPE_TIME_FORMULA       TYPE_TIME_NUMBER
TYPE_LENGTH           TYPE_LENGTH_FORMULA
TYPE_CUSTOM_FORMULA
TYPE_ANY
```

### Criteria — `CRITERIA_*`

```
CRITERIA_BETWEEN              CRITERIA_NOT_BETWEEN
CRITERIA_EQUAL_TO             CRITERIA_NOT_EQUAL_TO
CRITERIA_GREATER_THAN         CRITERIA_LESS_THAN
CRITERIA_GREATER_THAN_OR_EQUAL_TO
CRITERIA_LESS_THAN_OR_EQUAL_TO
```

### Error severity — `ERROR_TYPE_*`

```
ERROR_TYPE_STOP
ERROR_TYPE_WARNING
ERROR_TYPE_INFORMATION
```

## Function prototypes

```
\Vtiful\Kernel\Validation::__construct()
\Vtiful\Kernel\Validation::validationType(int $type): self
\Vtiful\Kernel\Validation::criteriaType(int $criteria): self
\Vtiful\Kernel\Validation::ignoreBlank(bool $ignore = true): self
\Vtiful\Kernel\Validation::showInput(bool $show = true): self
\Vtiful\Kernel\Validation::showError(bool $show = true): self
\Vtiful\Kernel\Validation::errorType(int $type): self
\Vtiful\Kernel\Validation::dropdown(bool $on = true): self

\Vtiful\Kernel\Validation::valueNumber(float $value): self
\Vtiful\Kernel\Validation::valueFormula(string $formula): self
\Vtiful\Kernel\Validation::valueList(array $values): self
\Vtiful\Kernel\Validation::valueDatetime(int $timestamp): self

\Vtiful\Kernel\Validation::minimumNumber(float $value): self
\Vtiful\Kernel\Validation::minimumFormula(string $formula): self
\Vtiful\Kernel\Validation::minimumDatetime(int $timestamp): self

\Vtiful\Kernel\Validation::maximumNumber(float $value): self
\Vtiful\Kernel\Validation::maximumFormula(string $formula): self
\Vtiful\Kernel\Validation::maximumDatetime(int $timestamp): self

\Vtiful\Kernel\Validation::inputTitle(string $title): self
\Vtiful\Kernel\Validation::inputMessage(string $message): self
\Vtiful\Kernel\Validation::errorTitle(string $title): self
\Vtiful\Kernel\Validation::errorMessage(string $message): self

\Vtiful\Kernel\Validation::toResource(): resource
```

### **int $type**

> Validation type — one of the `Validation::TYPE_*` constants.

### **int $criteria**

> Criteria — one of the `Validation::CRITERIA_*` constants. `CRITERIA_BETWEEN` and `CRITERIA_NOT_BETWEEN` require both `minimum*` and `maximum*` to be set; the other comparison criteria use `value*`.

### **bool $ignore**

> Whether blank cells are accepted. Defaults to `true`.

### **bool $show**

> Whether the input prompt (`showInput`) or error alert (`showError`) is displayed when the cell is selected or invalid. Defaults to `true`.

### **bool $on**

> Whether to show the in-cell drop-down arrow. Defaults to `true`. Only meaningful for `TYPE_LIST` / `TYPE_LIST_FORMULA`.

### **float $value**

> Target value for comparison criteria, e.g. `valueNumber(20)` compares against 20.

### **string $formula**

> Express the bound as an Excel formula, e.g. `valueFormula('=A1')`, `minimumFormula('=$A$1')`.

### **array $values**

> List of strings used for `TYPE_LIST`. Each element must be a non-empty string, otherwise `Vtiful\Exception` is thrown.

### **int $timestamp**

> Unix timestamp; the extension converts it to an Excel serial date.

### **string $title / $message**

> Title and body for the input prompt and error alert.

### **toResource()**

> Convert the current `Validation` builder into a resource handle that `Excel::validation()` accepts.

## Example

```php
$config = ['path' => './'];
$excel  = new \Vtiful\Kernel\Excel($config);

$validation = new \Vtiful\Kernel\Validation();
$validation->validationType(\Vtiful\Kernel\Validation::TYPE_INTEGER)
    ->criteriaType(\Vtiful\Kernel\Validation::CRITERIA_BETWEEN)
    ->minimumNumber(1)
    ->maximumNumber(100)
    ->ignoreBlank(true)
    ->errorType(\Vtiful\Kernel\Validation::ERROR_TYPE_STOP)
    ->inputTitle('Score')
    ->inputMessage('Integer between 1 and 100')
    ->errorTitle('Invalid value')
    ->errorMessage('Score must be an integer in 1..100');

$excel->fileName('tutorial.xlsx')
    ->header(['Score'])
    ->validation('A1:A10', $validation->toResource())
    ->output();
```


# Excel table

An Excel table marks a rectangular region of a worksheet as a "smart table" with header row, autofilter, banded rows, total row and so on. The cells themselves stay normal cells; the table just adds a metadata file under `xl/tables/tableN.xml`.

There are two steps:

1. Build the options with `Vtiful\Kernel\Table`.
2. Apply them with `Excel::addTable(string $rangeA1, ?Table $opts = null)`.

> Note: `Table` does **not** use `toResource()`. Pass the `Table` instance directly as the second argument to `addTable()`. Omit the second argument to use libxlsxwriter's default table style.

## Function prototype

```
\Vtiful\Kernel\Excel::addTable(string $rangeA1, ?\Vtiful\Kernel\Table $opts = null): self
```

### **string $rangeA1**

> The cell range covered by the table, in A1 notation, e.g. `A1:D11`. The range must include both the header row and at least one data row.

### **?Table $opts**

> Optional `Table` builder. When `null`, libxlsxwriter's defaults are used.

## Sub-sections

* [Columns](/english/table/columns)
* [Style](/english/table/style)
* [Options](/english/table/options)

## Example

```php
$config = ['path' => './'];
$excel  = new \Vtiful\Kernel\Excel($config);

$table = new \Vtiful\Kernel\Table();
$table->name('Performance')
      ->style(\Vtiful\Kernel\Table::STYLE_TYPE_LIGHT, 11)
      ->columns([
          ['header' => 'Name'],
          ['header' => 'Score'],
      ]);

$excel->fileName('tutorial.xlsx')
    ->data([
        ['Alice', 90],
        ['Bob',   80],
    ])
    ->addTable('A1:B3', $table)
    ->output();
```


# Columns

`Table::columns(array $columns)` configures the per-column metadata. `$columns` is an array; each element is an associative array whose keys describe one column. Columns left out fall back to libxlsxwriter's defaults (`Column1`, `Column2`, ...).

## Function prototype

```
\Vtiful\Kernel\Table::columns(array $columns): self
```

### **array $columns**

> Each entry supports the following keys:
>
> * **header** *(string)* — column title, replaces the default `ColumnN`.
> * **formula** *(string)* — column formula applied to every data row, e.g. `'=SUM(Table1[@[Q1]:[Q4]])'`.
> * **total\_string** *(string)* — string shown in the total row for this column, e.g. `'Total'`.
> * **total\_function** *(int)* — total-row aggregator, one of `Table::FUNCTION_*`.
> * **total\_value** *(float)* — explicit numeric value for the total row (normally derived from `total_function`).
> * **format** *(Format|resource)* — cell format for the data cells of this column. Accepts a `Format` instance or its resource handle.
> * **header\_format** *(Format|resource)* — format for the header cell.

`Table::FUNCTION_*` constants: `FUNCTION_NONE`, `FUNCTION_AVERAGE`, `FUNCTION_COUNT_NUMS`, `FUNCTION_COUNT`, `FUNCTION_MAX`, `FUNCTION_MIN`, `FUNCTION_STD_DEV`, `FUNCTION_SUM`, `FUNCTION_VAR`.

## Example

A sales table with four quarterly columns and a total row.

```php
$config = ['path' => './'];
$excel  = new \Vtiful\Kernel\Excel($config);

$fileHandle = $excel->fileName('tutorial.xlsx')->getHandle();
$bold       = new \Vtiful\Kernel\Format($fileHandle);
$boldFmt    = $bold->bold()->toResource();

$table = new \Vtiful\Kernel\Table();
$table->name('Sales')
      ->totalRow()
      ->columns([
          ['header' => 'Region', 'header_format' => $boldFmt, 'total_string' => 'Total'],
          ['header' => 'Q1',     'total_function' => \Vtiful\Kernel\Table::FUNCTION_SUM],
          ['header' => 'Q2',     'total_function' => \Vtiful\Kernel\Table::FUNCTION_SUM],
          ['header' => 'Q3',     'total_function' => \Vtiful\Kernel\Table::FUNCTION_SUM],
          ['header' => 'Q4',     'total_function' => \Vtiful\Kernel\Table::FUNCTION_SUM],
      ]);

$excel->data([
        ['East',  100, 110, 120, 130],
        ['West',   90,  95, 100, 105],
        ['North', 200, 210, 220, 230],
        // last row is reserved for the total row
        ['',        0,   0,   0,   0],
    ])
    ->addTable('A1:E5', $table)
    ->output();
```


# Style

The visual style of an Excel table is the combination of a *type* (default / light / medium / dark) and a *number* within that type. `Table::style(int $type, int $number)` takes those two values. `Table::name(string $name)` gives the table a unique name; if omitted, libxlsxwriter generates `Table1`, `Table2`, ...

## Function prototypes

```
\Vtiful\Kernel\Table::style(int $type, int $number): self
\Vtiful\Kernel\Table::name(string $name): self
```

### **int $type**

> The style family — one of:
>
> * `Table::STYLE_TYPE_DEFAULT`
> * `Table::STYLE_TYPE_LIGHT`
> * `Table::STYLE_TYPE_MEDIUM`
> * `Table::STYLE_TYPE_DARK`

### **int $number**

> The style index inside the family. Excel ships with 1..21 light, 1..28 medium and 1..11 dark variants; popular picks are `Light 11` and `Medium 9`. Pass `0` to apply no styling.

### **string $name**

> The table name. Must start with a letter or underscore and be unique inside the workbook. The name can be referenced from formulas, e.g. `Table1[@Column1]`.

## Example

```php
$config = ['path' => './'];
$excel  = new \Vtiful\Kernel\Excel($config);

$table = new \Vtiful\Kernel\Table();
$table->name('Performance')
      ->style(\Vtiful\Kernel\Table::STYLE_TYPE_MEDIUM, 9)
      ->columns([
          ['header' => 'Name'],
          ['header' => 'Score'],
      ]);

$excel->fileName('tutorial.xlsx')
    ->data([
        ['Alice', 90],
        ['Bob',   80],
    ])
    ->addTable('A1:B3', $table)
    ->output();
```


# Options

These methods toggle the common feature switches on an Excel table. Each accepts an optional `bool`; passing nothing turns the option **on**, passing `false` turns it off.

## Function prototypes

```
\Vtiful\Kernel\Table::noHeaderRow(bool $on = true): self
\Vtiful\Kernel\Table::noAutofilter(bool $on = true): self
\Vtiful\Kernel\Table::noBandedRows(bool $on = true): self
\Vtiful\Kernel\Table::bandedColumns(bool $on = true): self
\Vtiful\Kernel\Table::firstColumn(bool $on = true): self
\Vtiful\Kernel\Table::lastColumn(bool $on = true): self
\Vtiful\Kernel\Table::totalRow(bool $on = true): self
```

### **noHeaderRow()**

> Hide the header row; the entire range passed to `addTable()` is treated as data.

### **noAutofilter()**

> Disable the autofilter drop-downs in the header.

### **noBandedRows()**

> Disable the alternating row banding.

### **bandedColumns()**

> Enable alternating column banding (independent of row banding).

### **firstColumn() / lastColumn()**

> Highlight the first or last column — useful for emphasising a label or summary column.

### **totalRow()**

> Add a total row as the last row of the range. Pair it with `total_string` / `total_function` on the column definitions. The range passed to `addTable()` must include the total row.

## Example

```php
$config = ['path' => './'];
$excel  = new \Vtiful\Kernel\Excel($config);

$table = new \Vtiful\Kernel\Table();
$table->name('Sales')
      ->totalRow()
      ->bandedColumns()
      ->firstColumn()
      ->columns([
          ['header' => 'Region', 'total_string' => 'Total'],
          ['header' => 'Amount', 'total_function' => \Vtiful\Kernel\Table::FUNCTION_SUM],
      ]);

$excel->fileName('tutorial.xlsx')
    ->data([
        ['East', 100],
        ['West',  90],
        ['',       0], // reserved for the total row
    ])
    ->addTable('A1:B4', $table)
    ->output();
```


# Page setup

Configure how a worksheet is laid out for printing — paper size, margins, orientation, scale, headers / footers, repeat rows / columns, print area, page breaks, fit-to-pages.

Topics:

* Paper: [Paper size](/english/page-setup/paper), [Margins](/english/page-setup/margins), [Orientation](/english/page-setup/orientation)
* Scale & direction: [Print scale](/english/page-setup/scale), [Landscape](/english/page-setup/landscape), [Portrait](/english/page-setup/portrait), [Fit to pages](/english/page-setup/fit-to-pages)
* Content: [Header and footer](/english/page-setup/header-footer), [Repeat rows](/english/page-setup/repeat-rows), [Repeat columns](/english/page-setup/repeat-columns), [Print area](/english/page-setup/print-area), [Page breaks](/english/page-setup/page-breaks)


# Paper size

## Function Prototype

```php
setPaper(int $paper): self
```

## Paper size constants

### Class

```php
\Vtiful\Kernel\Excel
```

### Constant list

```php
const PAPER_DEFAULT              = 0;
const PAPER_LETTER               = 1;
const PAPER_LETTER_SMALL         = 2;
const PAPER_TABLOID              = 3;
const PAPER_LEDGER               = 4;
const PAPER_LEGAL                = 5;
const PAPER_STATEMENT            = 6;
const PAPER_EXECUTIVE            = 7;
const PAPER_A3                   = 8;
const PAPER_A4                   = 9;
const PAPER_A4_SMALL             = 10;
const PAPER_A5                   = 11;
const PAPER_B4                   = 12;
const PAPER_B5                   = 13;
const PAPER_FOLIO                = 14;
const PAPER_QUARTO               = 15;
const PAPER_NOTE                 = 18;
const PAPER_ENVELOPE_9           = 19;
const PAPER_ENVELOPE_10          = 20;
const PAPER_ENVELOPE_11          = 21;
const PAPER_ENVELOPE_12          = 22;
const PAPER_ENVELOPE_14          = 23;
const PAPER_C_SIZE_SHEET         = 24;
const PAPER_D_SIZE_SHEET         = 25;
const PAPER_E_SIZE_SHEET         = 26;
const PAPER_ENVELOPE_DL          = 27;
const PAPER_ENVELOPE_C3          = 28;
const PAPER_ENVELOPE_C4          = 29;
const PAPER_ENVELOPE_C5          = 30;
const PAPER_ENVELOPE_C6          = 31;
const PAPER_ENVELOPE_C65         = 32;
const PAPER_ENVELOPE_B4          = 33;
const PAPER_ENVELOPE_B5          = 34;
const PAPER_ENVELOPE_B6          = 35;
const PAPER_ENVELOPE_1           = 36;
const PAPER_MONARCH              = 37;
const PAPER_ENVELOPE_2           = 38;
const PAPER_FANFOLD              = 39;
const PAPER_GERMAN_STD_FANFOLD   = 40;
const PAPER_GERMAN_LEGAL_FANFOLD = 41;
```

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->setPaper(\Vtiful\Kernel\Excel::PAPER_A3)
    ->setLandscape()
    ->output();
```


# Margins

## Function Prototype

```php
setMargins(double $left, double $right, double $top, double $bottom): self
```

The unit is inches.

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->setPaper(\Vtiful\Kernel\Excel::PAPER_A3)
    ->setLandscape()
    ->setMargins(1, 1, 2, 2)
    ->output();
```


# Orientation

## Landscape

```php
setLandscape(): self
```

### Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->setPaper(\Vtiful\Kernel\Excel::PAPER_A3)
    ->setLandscape()
    ->output();
```

## Portrait

```php
setPortrait(): self
```

### Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->setPaper(\Vtiful\Kernel\Excel::PAPER_A3)
    ->setPortrait()
    ->output();
```


# Print scale

## Function Prototype

```php
setPrintScale(int $scale): self
```

### **int $scale**

> Printing scale percentage.
>
> Range: 10 <= $scale <= 400

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->setPaper(\Vtiful\Kernel\Excel::PAPER_A3)
    ->setLandscape()
    ->setMargins(1, 1, 2, 2)
    ->setPrintScale(180)
    ->output();
```


# Landscape

## **Function Prototype**

```php
setLandscape(): self
```

## **Example**

```php
$config = ['path' =>'./tests'];
$excel = new \Vtiful\Kernel\Excel($config);

$excel->fileName('printed_landscape.xlsx','sheet1')
    ->setLandscape() // Set the printing direction to landscape
    ->output();
```


# Portrait

## **Function Prototype**

```php
setPortrait(): self
```

## **Example**

```php
$config = ['path' =>'./tests'];
$excel = new \Vtiful\Kernel\Excel($config);

$excel->fileName('printed_portrait.xlsx','sheet1')
    ->setPortrait() // Set the printing direction to portrait
    ->output();
```


# Header and footer

Set the printed header and footer text. The text supports libxlsxwriter's format codes such as `&L` (left), `&C` (center), `&R` (right), `&P` (page number), `&N` (total pages), `&D` (date), `&T` (time), `&"Arial,Bold"` (font), `&14` (size).

## Function Prototype

```php
setHeader(string $value, ?array $options = null): self
setFooter(string $value, ?array $options = null): self
```

### **string $value**

> Header / footer text with optional format codes.

### **array $options**

> Optional. Supported keys:
>
> * `margin` — distance from the page edge in inches, default `0.3`
> * `image_left` — left-side image path, used together with the `&G` placeholder in the text
> * `image_center` — center image path
> * `image_right` — right-side image path

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->setHeader('&L&"Arial,Bold"&14Sales Report&R&D')
    ->setFooter('&CPage &P of &N', ['margin' => 0.4])
    ->output();
```


# Repeat rows

Mark one or more rows as title rows that are repeated at the top of every printed page.

## Function Prototype

```php
repeatRows(string $rangeA1): self
```

### **string $rangeA1**

> A1-style row range.
>
> A single row (e.g. `"1"`) or a closed interval (e.g. `"1:3"`), 1-based.
>
> An invalid format throws an exception (error code `220`).

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->repeatRows('1:1') // Repeat row 1 on every printed page
    ->output();
```


# Repeat columns

Mark one or more columns as title columns that are repeated on the left of every printed page.

## Function Prototype

```php
repeatColumns(string $rangeA1): self
```

### **string $rangeA1**

> A1-style column range.
>
> A single column (e.g. `"A"`) or a closed interval (e.g. `"A:C"`).
>
> An invalid format throws an exception (error code `221`).

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age', 'city'])
    ->data([
        ['viest', 21, 'Beijing'],
        ['wjx',   21, 'Shanghai']
    ])
    ->repeatColumns('A:A') // Repeat column A on every printed page
    ->output();
```


# Print area

Restrict the printed output to a single rectangular range. Cells outside the range are not printed.

## Function Prototype

```php
printArea(string $rangeA1): self
```

### **string $rangeA1**

> A1-style rectangular range, e.g. `"A1:F20"`.

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->printArea('A1:B3') // Only A1:B3 is printed
    ->output();
```


# Page breaks

Insert horizontal / vertical page breaks before specific rows / columns to force Excel to start a new page at that position.

## Function Prototype

```php
horizontalPageBreaks(array $rows): self
verticalPageBreaks(array $cols): self
```

### **array $rows**

> 1-based row numbers. Each value inserts a horizontal break **before** that row.

### **array $cols**

> 1-based column numbers. Each value inserts a vertical break **before** that column.

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$rows = [];
for ($i = 1; $i <= 100; $i++) {
    $rows[] = ["row{$i}", $i];
}

$filePath = $fileObject->header(['name', 'value'])
    ->data($rows)
    ->horizontalPageBreaks([20, 40, 60, 80]) // New page every 20 rows
    ->verticalPageBreaks([3])                // Page break before column 3
    ->output();
```


# Fit to pages

Automatically scale the worksheet so it prints inside a fixed number of pages horizontally and vertically. Common use case: squeeze a wide table down to one page wide.

## Function Prototype

```php
fitToPages(int $width, int $height): self
```

### **int $width**

> Number of pages horizontally. `1` means fit to one page wide.

### **int $height**

> Number of pages vertically. `0` means no vertical limit.

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age', 'city', 'email'])
    ->data([
        ['viest', 21, 'Beijing',  'viest@example.com'],
        ['wjx',   21, 'Shanghai', 'wjx@example.com']
    ])
    ->fitToPages(1, 0) // Fit to 1 page wide, unlimited height
    ->output();
```


# Workbook properties

Write document properties into the xlsx. They appear in Excel's "File → Info → Properties" panel. There are two kinds:

* **Standard properties** — fixed fields defined by OOXML (title, author, company, etc.), written in one call to `Excel::setProperties`.
* **Custom properties** — user-defined name / value pairs, written one at a time via `Excel::setCustomProperty`.

## Function Prototype

```php
setProperties(array $properties): self
setCustomProperty(string $name, mixed $value, ?string $type = null): self
```

Topics:

* [Standard properties](/english/properties/standard)
* [Custom properties](/english/properties/custom)


# Standard properties

Write standard document properties through the associative array passed to `Excel::setProperties`. All keys are optional; missing keys are not written.

## Function Prototype

```php
setProperties(array $properties): self
```

### **array $properties**

> Supported keys:
>
> * `title` — title
> * `subject` — subject
> * `author` — author
> * `manager` — manager
> * `company` — company
> * `category` — category
> * `keywords` — keywords
> * `comments` — comments
> * `status` — status
> * `hyperlink_base` — hyperlink base
> * `created` — creation time, **Unix timestamp** (integer seconds). Defaults to the current time when omitted.

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->setProperties([
        'title'    => 'Q1 Report',
        'subject'  => 'Sales',
        'author'   => 'viest',
        'manager'  => 'wjx',
        'company'  => 'Vtiful',
        'category' => 'Finance',
        'keywords' => 'sales, q1, 2025',
        'comments' => 'Generated by xlswriter',
        'status'   => 'Draft',
        'created'  => time(),
    ])
    ->output();
```


# Custom properties

Write user-defined workbook properties. Each call writes one name / value pair.

## Function Prototype

```php
setCustomProperty(string $name, mixed $value, ?string $type = null): self
```

### **string $name**

> Property name.

### **mixed $value**

> Property value.

### **string $type**

> Property type. Allowed values:
>
> * `string` — string
> * `number` — integer or floating-point number
> * `boolean` — boolean
> * `datetime` — date / time; `$value` must be a **Unix timestamp**
>
> When omitted the type is inferred from PHP's type of `$value` (string / int / double / bool). `datetime` must always be specified explicitly.
>
> An unsupported type throws an exception (error code `222`).

## Example

```php
$config = [
    'path' => './tests'
];

$fileObject = new \Vtiful\Kernel\Excel($config);
$fileObject = $fileObject->fileName('tutorial.xlsx');

$filePath = $fileObject->header(['name', 'age'])
    ->data([
        ['viest', 21],
        ['wjx',   21]
    ])
    ->setCustomProperty('Department',   'Sales')          // inferred as string
    ->setCustomProperty('Confidential', true)             // inferred as boolean
    ->setCustomProperty('Revision',     3)                // inferred as number
    ->setCustomProperty('Reviewed',     time(), 'datetime') // datetime must be explicit
    ->output();
```




---

[Next Page](/llms-full.txt/1)