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