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

Query SAP multiple database table sizes

Query SAP multiple database table sizes https://www.cnblogs.com/ken-yu/p/12973009.html Item code db02 Here are two approaches, In the first graphical interface, the results of the query data table are displayed in MB, and only one table can be queried at a time. SPACE—Segments—Detailed Analysis—Detailed Analysis  In the pop-up window segment / object, enter the name of the […]