Сначала: установите 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);