Работа , формирование Excel файлов в PHP

Сначала: установите phpoffice / phpspreadsheet

composer require phpoffice/phpspreadsheet

Подробное описание интерфейса API phpoffice / phpspreadsheet

PhpSpreadsheet предоставляет богатый интерфейс API, который может устанавливать множество атрибутов ячейки и документа, включая стиль, изображение, дату, функцию и многие другие. Короче говоря, лубую таблицу Excel вы сможете сделать с PhpSpreadsheet.

Перед использованием интерфейса API phpoffice / phpspreadsheet убедитесь, что правильные файлы введены и созданы

Use PhpOfficePhpSpreadsheetSpreadsheet;//Introduce files

$spreadsheet = new PhpOffice PhpSpreadsheet Spreadsheet ();// Create a new excel document
$sheet = $spreadsheet - > getActiveSheet (); // Get the object of the current operation sheet

1: Установить шрифт:

$sheet->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial')
            -> SetSize (10); // Set the cells A7 to B7 to bold, Arial, 10
$sheet - > getStyle ('B1') - > getFont () - > setBold (true); // Set B1 cells to bold type

2: Установить цвет

$sheet - > getStyle ('A1') - > getFont () - > getColor () - > setARGB ( PhpOffice  PhpSpreadsheet  Style  Color:: COLOR_RED); //Set the text color of cell A1 to red

3: Установить длину колонки

$sheet - > getColumnDimension ('A') - > setWidth (20); // Set the width of column A to 20 (characters)
$sheet - > getColumnDimension ('B') - > setAutoSize (true); // Set the width of column B to automatic width
$sheet - > getDefaultColumnDimension () - > setWidth (12); // Set default column width to 12

4: Установить высоту строки

$sheet - > getRowDimension ('10') - > setRowHeight (100); /// Set the height of the tenth line to 100pt
$sheet - > getDefaultRowDimension () - > setRowHeight (15); // Set default row height to 15

5:Выравнивание

$sheet->getStyle('A:D')->getAlignment()
            -> SetVertical ( PhpOffice PhpSpreadsheet Style Alignment:: VERTICAL_CENTER) /// Set Vertical Centralization
            -> SetHorizontal ( PhpOffice PhpSpreadsheet Style Alignment:: HORIZONTAL_CENTER)/// Settings are in the middle
            -> setWrapText (true); // Set automatic line break

6: Объединение ячеек

$sheet - > mergeCells ('A1: D2'); //A1 to D2 merged into one cell

7: Разделить объединенные ячейки

$sheet - > unmergeCells ('A1: D2'); // Split the merged cells.

8: Используйте applyFromArray для реализации настроек стиля ячейки

// Style variables
$style = [
// Setting font style
'font' => [
        'name' => 'Arial',
        'bold' => true,
        'italic' => false,
        'underline' => Font::UNDERLINE_DOUBLE,
        'strikethrough' => false,
        'color' => [
            'rgb' => '808080'
        ]
    ],
// Set the border style
'borders' => [
         // All Borders Border Styles
         // Left border
       'bottom' => [
           'borderStyle' => Border::BORDER_DASHDOT,
           'color' => [
              'rgb' => '808080'
            ]
       ],
         // Upper border
       'top' => [
           'borderStyle' => Border::BORDER_DASHDOT,
           'color' => [
               'rgb' => '808080'
           ]
       ]
],
// Alignment Style
'alignment' => [
   'horizontal' => Alignment::HORIZONTAL_CENTER,
   'vertical' => Alignment::VERTICAL_CENTER,
   'wrapText' => true,
],
// Whether to use prefix
'quotePrefix'    => true
];
$sheet->getStyle('A1:D1')->applyFromArray($style);

9: Установка заголовка рабочего листа

$sheet - > setTitle ('Hello'); and // Set the current worksheet title.

10: Формат ячеек

$sheet->getStyle('D2')->getNumberFormat()
            -> setFormatCode ( PhpOffice PhpSpreadsheet Style NumberFormat:: FORMAT_TEXT); // Set the format of D2 cells to text format
$sheet->getStyle('A1:D2')->getNumberFormat()
            -> setFormatCode ( PhpOffice PhpSpreadsheet Style NumberFormat:: FORMAT_TEXT); // Set cells A1 to D2 to text format

11: Смена линии

$sheet - > getCell ('A4') - > setValue ("hello nworld"); and // wrap the hello and world lines of cells A4

12: Гиперссылки

// Set up the A2 cell content blog and click jump https://www.wj0511.com
$sheet->setCellValue('A2', 'blog');
$sheet->getCell('A2')->getHyperlink()->setUrl('https://www.wj0511.com');

13: Используйте функции

Общие функции : sum (SUM), maximum (MAX), minimum (MIN), average (AVERAGE).

$sheet - > setCellValue ('B5','= SUM (B1: B4)'); and // Set the contents of B5 cells to the sum of B1 to B4

14: Три: Простая реализация генерации Excel (здесь я загружаю фреймворк Yii методом загрузки)

$spreadsheet->getProperties()
    -> setCreator ("author"//author)
    -> setLastModifiedBy ("last-author")// Last ModifiedBy
    -> setTitle ("title")// title
    -> setSubject ("subject"// subtitle
    -> setDescription ("description"//description)
    -> setKey ("keywords") // keyword
    -> setCategory ("category"); //classification

Three: Simple implementation of Excel generation (here I download the Yii framework with the download method)

<?php
/**
 * author: wangjian
 * date: 2019/7/15
 */
namespace app\controllers;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Yii;
use yii\web\Controller;
class ExcelController extends Controller
{
    /**
     * Digital transliteration (similar to Excel column labels)
     *@ Param Int $index index value
     *@ Param Int $start letter start value
     *@ Return String returns letters
     */
    public function intToChr($index, $start = 65)
    {
        $str = '';
        if (floor($index / 26) > 0) {
            $str .= $this->intToChr(floor($index / 26)-1);
        }
        return $str . chr($index % 26 + $start);
    }
    
    
    public function actionIndex()
    {
        // Header information
        $header = [
            'Name',
            'Gender',
            'Education',
            'Age',
            'Height',
        ];
        // Content
        $data = [
            [
                Xiaoming,
                'male',
                'Specialist',
                '18',
                '175'
            ],
            [
                'Xiaohong',
                'female',
                'Undergraduate',
                '18',
                '155'
            ],
            [
                'Little Blue',
                'male',
                'Specialist',
                '20',
                '170'
            ],
            [
                'Zhang San',
                'male',
                'Undergraduate',
                '19',
                '165'
            ],
            [
                'Li Si',
                'male',
                'Specialist',
                '22',
                '175'
            ],
            [
                Wang Er,
                'male',
                'Specialist',
                '25',
                '175'
            ],
            [
                'Mazi',
                'male',
                'Undergraduate',
                '22',
                '180'
            ],
        ];
        $header = array_values($header);
        $data = array_values($data);
        // Get column information
        $column = []; //['A','B','C','D','E']
        foreach ($header as $k => $item) {
            $column[$k] = $this->intToChr($k);
        }
        // Get the initial and final columns
        $firstColum = $column[0];
        $lastColum = $column[count($column) - 1];
        // Get the initial and final rows
        $firstRow = 1;
        $lastRow = count($data) + 1;
        $row = 1;
        $spreadsheet = new Spreadsheet ();// Create a new excel document
        $sheet = $spreadsheet - > getActiveSheet (); // Get the object of the current operation sheet
        $sheet - > setTitle ('Title'); // Set Title
        $sheet->getStyle("{$firstColum}:{$lastColum}")->getAlignment()
            -> SetVertical (Alignment:: VERTICAL_CENTER)// Set Vertical Centralization
            -> Set Horizontal (Alignment:: HORIZONTAL_CENTER)// Setting level is in the middle
            -> setWrapText (true); // Set automatic line break
        // Set width
        $sheet->getDefaultColumnDimension()->setWidth(20);
        $headerStyle = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
            'font' => [
                'bold' => true,
                'size' => 14,
            ],
        ];
        $cellStyle = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ]
            ],
            'font' => [
                'size' => 10,
            ],
        ];
        // Set Excel cell format to text format
        $sheet->getStyle("{$firstColum}{$firstRow}:{$lastColum}{$lastRow}")->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
        // Setting header information style
        $sheet - > getRowDimension ($row) - > setRowHeight (30); // Set line height
        $sheet->getStyle("{$firstColum}{$row}:{$lastColum}{$row}")->applyFromArray($headerStyle);
        // Setting header information
        foreach ($header as $key => $item) {
            $sheet->setCellValue("{$column[$key]}{$row}", $item);
        }
        $row++;
        foreach ($data as $key => $model) {
            $sheet - > getRowDimension ($row) - > setRowHeight (30); // Set line height
            $sheet->getStyle("{$firstColum}{$row}:{$lastColum}{$row}")->applyFromArray($cellStyle);
            $i = 0;
            foreach ($model as $value) {
                $sheet->setCellValue("{$column[$i]}{$row}", $value);
                $i++;
            }
            $row++;
        }
        $file = table'.'. xlsx'; // save address
        $writer = new Xlsx($spreadsheet);
        $writer - > save ($file); // generate excel file
        Yii: $app - > response - > sendFile ($file,'download excel name. xlsx') - > send ();
    }
}

4: Read Excel files

$title = []; //excel worksheet title
$info = []; //excel content
FileName = table. xlsx;
$spreadsheet = IOFactory::load($fileName);
// $worksheet = $spreadsheet - > getActiveSheet (); // Get the current file content
$sheetAllCount = $spreadsheet - > getSheetCount (); // Total worksheet
For ($index = 0; $index < $sheetAllCount; $index ++) {// worksheet title
    $title[] = $spreadsheet->getSheet($index)->getTitle();
}
// Read the first worksheet
$whatTable = 0;
$sheet = $spreadsheet->getSheet($whatTable);
$highest_row = $sheet - > get Highest Row (); // Get the total number of rows
$highest_column = $sheet - > getHighestColumn (); /// Gets the column numeral abc...
$highestColumnIndex = Coordinate:: ColumnIndexFromString ($highest_column); // Converted to a number;
for ($i = 1; $i <= $highestColumnIndex; $i++) {
    for ($j = 1; $j <= $highest_row; $j++) {
        $conent = $sheet->getCellByColumnAndRow($i, $j)->getCalculatedValue();
        $info[$j][$i] = $conent;
    }
}
var_dump($info);

Leave a Reply Cancel Reply