phpoffice/phpspreadsheet

PhpSpreadsheet is a pure PHP library for reading and writing spreadsheet files. It is the successor to the popular PHPExcel library and provides a robust, modern, and actively maintained solution for handling various spreadsheet formats. PhpSpreadsheet allows developers to automate tasks such as generating reports, importing data from Excel or ODS files, exporting data to these formats, and performing complex data manipulations within spreadsheets.

Key features and capabilities of PhpSpreadsheet include:

1. Supported Formats: It can read and write a wide range of formats, including Microsoft Excel (XLSX, XLS), OpenDocument Spreadsheet (ODS), Comma Separated Values (CSV), HTML, PDF (requires additional rendering libraries like mPDF or TCPDF), and more.
2. Spreadsheet Creation: Developers can create new spreadsheets from scratch, defining sheets, setting cell values, and applying various formatting options.
3. Reading Existing Files: It can parse existing spreadsheet files, allowing access to cell data, formulas, formatting, and other properties.
4. Cell Manipulation: Supports reading and writing data to specific cells, ranges, or columns. It handles various data types including strings, numbers, booleans, and dates.
5. Formulas: It can read and write formulas, and even calculate formula results within the PHP environment (though complex Excel-specific functions might require external calculation engines).
6. Styling and Formatting: Comprehensive support for styling cells and ranges, including fonts (bold, italic, size, color), borders, background colors, alignment, number formats, conditional formatting, and data validation.
7. Sheet Management: Allows adding, deleting, renaming, and reordering sheets within a workbook.
8. Images, Charts, and Comments: Capabilities to embed images, create charts (though charts are typically rendered as static images, not interactive Excel charts), and add comments to cells.
9. Protection: Features to protect sheets and cells from modifications.
10. Column and Row Manipulation: Adjusting column widths, row heights, merging cells, and hiding/unhiding rows and columns.

Installation: PhpSpreadsheet is typically installed via Composer, the PHP dependency manager. The command is `composer require phpoffice/phpspreadsheet`.

PhpSpreadsheet is an indispensable tool for any PHP application that needs to interact with spreadsheet data, providing a powerful and flexible API to handle almost any spreadsheet-related task.

Example Code

```php
<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

// 1. Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// 2. Set document properties (optional)
$spreadsheet->getProperties()->setCreator('Your Name')
    ->setLastModifiedBy('Your Name')
    ->setTitle('PhpSpreadsheet Demo Document')
    ->setSubject('A simple demonstration')
    ->setDescription('Generated using PhpSpreadsheet.')
    ->setKeywords('phpspreadsheet excel php')
    ->setCategory('Demo File');

// 3. Get the active sheet (default first sheet)
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('My Data Sheet');

// 4. Add some data to cells
$sheet->setCellValue('A1', 'Product ID');
$sheet->setCellValue('B1', 'Product Name');
$sheet->setCellValue('C1', 'Price');
$sheet->setCellValue('D1', 'Quantity');

$products = [
    ['P001', 'Laptop', 1200.00, 5],
    ['P002', 'Mouse', 25.00, 50],
    ['P003', 'Keyboard', 75.50, 20],
    ['P004', 'Monitor', 300.00, 10]
];

$row = 2;
foreach ($products as $product) {
    $sheet->setCellValue('A' . $row, $product[0]);
    $sheet->setCellValue('B' . $row, $product[1]);
    $sheet->setCellValue('C' . $row, $product[2]);
    $sheet->setCellValue('D' . $row, $product[3]);
    $row++;
}

// 5. Apply some basic styling
// Make header row bold and set background color
$sheet->getStyle('A1:D1')->getFont()->setBold(true);
$sheet->getStyle('A1:D1')->getFill()
    ->setFillType(Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFD9E1F2'); // Light blue background

// Set currency format for price column
$sheet->getStyle('C2:C' . ($row - 1))
    ->getNumberFormat()->setFormatCode('$#,0.00');

// Apply borders to all data
$sheet->getStyle('A1:D' . ($row - 1))->getBorders()->getAllBorders()
    ->setBorderStyle(Border::BORDER_THIN)
    ->setColor(new Color(Color::COLOR_BLACK));

// Auto-size columns for better readability
foreach (range('A', 'D') as $columnID) {
    $sheet->getColumnDimension($columnID)->setAutoSize(true);
}

// 6. Set the active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

// 7. Save the .xlsx file
$writer = new Xlsx($spreadsheet);
$filename = 'product_list.xlsx';

try {
    // Save the file to the server
    $writer->save($filename);
    echo "Spreadsheet '{$filename}' created successfully in the current directory.\n";

    // Optionally, force download to the user's browser
    // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    // header('Content-Disposition: attachment;filename="' . $filename . '"');
    // header('Cache-Control: max-age=0');
    // $writer->save('php://output');
    // exit();

} catch (Exception $e) {
    echo "Error saving spreadsheet: " . $e->getMessage() . "\n";
}

// Remember to install via Composer: composer require phpoffice/phpspreadsheet

?>
```

phpoffice/phpspreadsheet

Example Code

Related Topics