PhpSpreadsheet 電子表格(excel) PHP處理筆記

1 安裝

composer require phpoffice/phpspreadsheet
           

Github位址：PHPOffice/PhpSpreadsheet

文檔位址：PhpSpreadsheet’s documentation

2 基本用法

// 這裡是以thinkphp5環境為例
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet(); // 建立新表格
$spreadsheet->createSheet(); // 建立sheet
$sheet = $spreadsheet->getActiveSheet(); // 擷取目前sheet
$sheet->setCellValue('A1', 'Hello World222 !'); // 設定單元格A1的值

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//浏覽器輸出07Excel檔案
//header('Content-Type:application/vnd.ms-excel');//浏覽器将要輸出Excel03版本檔案
header('Content-Disposition: attachment;filename="'.time().'.xlsx"');//浏覽器輸出浏覽器名稱
header('Cache-Control: max-age=0'); //禁止緩存
$writer = new Xlsx($spreadsheet);
$writer->save('php://output'); // 直接下載下傳excel
// $writer->save('1.xlsx');  預設儲存到根目錄，thinkphp5裡預設是public目錄

// 斷開連接配接，銷毀對象
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
           

3 添加sheet

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

// 添加sheet
$myWorkSheet = new Worksheet($spreadsheet, 'My Data'); // sheet名稱
$spreadsheet->addSheet($myWorkSheet, 0); // 添加sheet位置index
           

4 擷取sheet下标/名稱，切換sheet

$sheet = $spreadsheet->getSheet(1); // 根據下标擷取sheet
$sheet = $spreadsheet->getSheetByName('Worksheet 1'); //根據name擷取sheet
// 切換目前sheet
$spreadsheet->setActiveSheetIndex(1); // 按sheet下标切換
$spreadsheet->setActiveSheetIndexByName('Sheet 1'); // 按sheet name切換
           

5 批量設定單元格資料

// 設定單元格資料
$arrayData = [
   [NULL, 2010, 2011, 2012],
   ['Q1',   12,   15,   21],
   ['Q2',   56,   73,   86],
   ['Q3',   52,   61,   69],
   ['Q4',   30,   32,    0],
];
$sheet->fromArray(
   $arrayData,  // The data to set 
   NULL,        // Array values with this value will not be set
   'C3'         // Top left coordinate of the worksheet range where
                // we want to set these values (default is A1)
);
           

如果是設定

一行

，可以設定

一維數組

；

如果想設定

一列

資料，可以這樣：

$arrayData = [1,2,3,4];
$columnArray = array_chunk($rowArray, 1);
           

6 加載表格

方式1：

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'C:\Users\Administrator\Document\1.xlsx';
/**  獲得文檔類型(xls/xlsx)  **/
$inputFileType = IOFactory::identify($inputFileName);
/**  Create a new Reader of the type that has been identified  **/
$reader = IOFactory::createReader($inputFileType);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);
           

方式2：

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'C:\Users\Administrator\Document\1.xlsx';
$spreadsheet = IOFactory::load($inputFileName);
           

因第一種方式得到了具體的檔案類型，是以處理起來會比較快。

7 周遊單元格

use PhpOffice\PhpSpreadsheet\IOFactory;

//1. 使用疊代器周遊單元格
$reader = IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load('1.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
echo '<table>' . PHP_EOL;
foreach ($worksheet->getRowIterator() as $row) {
    echo '<tr>' . PHP_EOL;
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(FALSE);
    foreach ($cellIterator as $cell) {
        echo '<td>' .
            $cell->getValue() .
            '</td>' . PHP_EOL;
    }
    echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;

// 2.使用索引循環周遊單元格
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
// Get the highest row and column numbers referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // e.g. 5

echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
    echo '<tr>' . PHP_EOL;
    for ($col = 1; $col <= $highestColumnIndex; ++$col) {
        $value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
        echo '<td>' . $value . '</td>' . PHP_EOL;
    }
    echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;
           

8 删除sheet

$sheetIndex = $spreadsheet->getIndex(
	$spreadsheet->getSheetByName('Worksheet 1')
);
$spreadsheet->removeSheetByIndex($sheetIndex);
           

9 設定文檔中繼資料（摘要資訊）

$spreadsheet->getProperties()
            ->setCreator("Zhang Ying")
            ->setLastModifiedBy("Zhang Ying")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
           

10 小技巧

(1) 單元格内

換行

$sheet->getCell('B1')->setValue("hello\nworld");
$sheet->getStyle('B1')->getAlignment()->setWrapText(true);
           

(2) 強制設定單元格格式為

string

格式，設定正常

數字

$sheet->setCellValueExplicit('A8',"0123456",DataType::TYPE_STRING);
$sheet->getStyle('A9')->getNumberFormat()->setFormatCode('00000000000');
           

(3) 設定

列寬

度

自适應

(4) 按

行

、

列

下标

設定

和

查詢

單元格資料

// 按列和行設定單元格值
$sheet->setCellValueByColumnAndRow(1, 3, 'PhpSpreadsheet Demo');
// 按列和行查詢單元格值
$sheet->getCellByColumnAndRow(2, 5)->getValue();
           

(5) 開啟

篩選

功能

(6) 設定

列印

格式

use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;

$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);