Simple use of phpspreadsheet

Time:2019-10-19

 

Since phpexcel is no longer maintained, phpspreadsheet is the next version of phpexcel. Phpspreadsheet is a library written in pure PHP, and introduces namespace, PSR specification, etc. Here is a brief introduction to the import and export functions of phpspreadsheet.

1, installation

  • Use composer to install:
composer require phpoffice/phpspreadsheet
  • GitHub Download:

   https://github.com/PHPOffice/PhpSpreadsheet

 

2. Excel file export

/**
 *Excel file export
 */
function export()
{
    require_once __DIR__ . '/vendor/autoload.php';
 
    $data = [
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222']
    ];
    $title = ['first row title', 'second row title'];
 
    // Create new Spreadsheet object
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
 
    //Method 1, use setcellvaluebycolumnandrow
    // header
    //Set cell contents
    foreach ($title as $key => $value) {
        //Cell content write
        $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
    }
    $row = 2; // start with the second line
    foreach ($data as $item) {
        $column = 1;
        foreach ($item as $value) {
            //Cell content write
            $sheet->setCellValueByColumnAndRow($column, $row, $value);
            $column++;
        }
        $row++;
    }
 
    //Method 2: use setcellvalue
    // header
    //Set cell contents
    $titCol = 'A';
    foreach ($title as $key => $value) {
        //Cell content write
        $sheet->setCellValue($titCol . '1', $value);
        $titCol++;
    }
    $row = 2; // start with the second line
    foreach ($data as $item) {
        $dataCol = 'A';
        foreach ($item as $value) {
            //Cell content write
            $sheet->setCellValue($dataCol . $row, $value);
            $dataCol++;
        }
        $row++;
    }
 
    // Redirect output to a client’s web browser (Xlsx)
    ('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    ('Content-Disposition: attachment;filename="01simple.xlsx"');
    ('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    ('Cache-Control: max-age=1');
 
    // If you're serving to IE over SSL, then the following may be needed
    ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    ('Last-Modified: ' . ('D, d M Y H:i:s') . ' GMT'); // always modified
    ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    ('Pragma: public'); // HTTP/1.0
 
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
    exit;
}

Result:

 

3. Save excel file to local

/**
 *Excel file saved locally
 */
function save()
{
    require_once __DIR__ . '/vendor/autoload.php';
 
    $data = [
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222']
    ];
    $title = ['first row title', 'second row title'];
 
    // Create new Spreadsheet object
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
 
    // header
    //Set cell contents
    $titCol = 'A';
    foreach ($title as $key => $value) {
        //Cell content write
        $sheet->setCellValue($titCol . '1', $value);
        $titCol++;
    }
    $row = 2; // start with the second line
    foreach ($data as $item) {
        $dataCol = 'A';
        foreach ($item as $value) {
            //Cell content write
            $sheet->setCellValue($dataCol . $row, $value);
            $dataCol++;
        }
        $row++;
    }
 
    // Save
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('01simple.xlsx');
}

 

4. Read the contents of Excel file

/**
 *Read excel file content
 */
function read()
{
    require_once __DIR__ . '/vendor/autoload.php';
    $inputFileName = (__FILE__) . '/01simple.xlsx';
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
    // method two
    $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
    return $sheetData;
}

Result:

 

Possible problems:

1、Fatal error: Uncaught Error: Class ‘PhpOffice\PhpSpreadsheet\Spreadsheet’ not found

This is because there is no automatic load. You can import the load file manually.

require_once __DIR__ . '/vendor/autoload.php';

Or:

require_once __DIR__ . '/vendor/phpoffice/phpspreadsheet/src/Bootstrap.php';

 

2、Fatal error: Interface ‘Psr\SimpleCache\CacheInterface’ not found

This is because there is no PSR file and the simple cache module is missing. If you use composer installation, it will be generated automatically. If not, you can download it manually.

GitHub download address: https://github.com/php-fig/simple-cache/releases

 

Recommended Today

Single and multiple buttons are styled with pictures

I’ve always seen people asking how to style and enlarge the buttons of radio buttons and multi buttons? Let’s share an example I did. 1. First make the button into a picture  2.html page Copy code The code is as follows: <!DOCTYPE HTML> <html> <head> <meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ /> <script type=”text/javascript” src=”jquery-1.10.2.min.js”></script> <script type=”text/javascript”> […]