Read Excel files and read the data into arrays

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_file']);
// Specify that the first worksheet is current
$data = $spreadsheet->getSheet(0)->toArray();
Copy the code

PhpSpreadsheet is a library of components written purely in PHP. It uses modern PHP writing methods and is a complete replacement for PHPExcel (which is no longer maintained) with much better code quality and performance. Use PhpSpreadsheet to easily read and write Excel documents and support all Excel operations.

Liverpoolfc.tv: phpspreadsheet. Readthedocs. IO/en/stable /

1. The first PhpSpreadsheet

Software depend on

To use a PhpSpreadsheet, satisfy the following criteria:

  • PHP5.6 or later, PHP7 is recommended
  • Support php_zip extension
  • Support phP_XML extension
  • Php_gd2 extension is supported

The installation

Now, create the project directory /PHPExcel and go to the project directory.

Install using Composer:

composer require phpoffice/phpspreadsheet
Copy the code

use

Create a new /public directory under the project directory, create the sample file test.php under the public directory, and edit test.php using the following code.

<? php require '.. /vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('A1', 'Welcome to Helloweba.'); $writer = new Xlsx($spreadsheet); $writer->save('hello.xlsx');Copy the code

Run the code and you’ll see a hello. XLSX file generated in the directory. Open the Excel file and you’ll see “Welcome to Helloweba.” in cell A1 in Excel. Of course you can set cell styles such as colors, backgrounds, widths, fonts, and so on, which will be covered in the next few sections.

PhpSpreadsheet features

  • Supports reading. XLS,. XLSX,. HTML,. CSV files and writing. XLS,. XLSX,. HTML,. CSV,. PDF files.
  • Provide a rich API, provide cell style Settings, Excel table properties, chart Settings and many other functions. Use PhpSpreadsheet to generate an Excel spreadsheet file with all the look and feel you need.
  • Excellent performance, especially in PHP7, much more powerful than PHPExcel.

2. Use PhpSpreadsheet to import Excel into MySQL database

Import Excel

Use a PhpSpreadsheet to read useful information from Excel spreadsheet, assemble it into SQL statements, and insert it into MySQL spreadsheet in bulk.

require 'vendor/autoload.php'; include('conn.php'); $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); $reader->setReadDataOnly(TRUE); $spreadsheet = $reader->load('students.xlsx'); $spreadsheet = $spreadsheet->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); $highestColumn = $worksheet->getHighestColumn(); / / the total number of columns $highestColumnIndex = \ PhpOffice \ PhpSpreadsheet \ Cell \ Coordinate: : columnIndexFromString ($highestColumn); // e.g. 5 $lines = $highestRow - 2; If ($lines <= 0) {exit(' no data in Excel '); } $sql = "INSERT INTO `t_student` (`name`, `chinese`, `maths`, `english`) VALUES "; for ($row = 3; $row <= $highestRow; ++$row) { $name = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); $Chinese = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); $maths = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); $English = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); / / $SQL language. = "(' $name ', '$Chinese', '$maths', '$'. English),"; } $sql = rtrim($sql, ","); $db->query($SQL); echo 'OK'; } catch (Exception $e) { echo $e->getMessage(); }Copy the code

Worksheet ->getCellByColumnAndRow(col, row)->getValue() ->getValue() ->getCellByColumnAndRow(col, row)->getValue()

3. Use a PhpSpreadsheet to export data into Excel files

First, set the table header

First we introduce the autoload SPREADSHEET library, then instantiate it, set the worksheet title name to: Student Spreadsheet, and then set the header content. The header of the table is divided into two rows. The first row contains the name of the table and the second row contains the column names of the table. Finally, we merge the first row of cells and set the header content styles: font, alignment, and so on.

require 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; include('conn.php'); $spreadsheet = new spreadsheet (); $worksheet = $spreadsheet->getActiveSheet(); $worksheet->setTitle(' student score '); / / headers / $/ set cell contents worksheet - > setCellValueByColumnAndRow (1, 1, 'student achievement table). $worksheet - > setCellValueByColumnAndRow (1, 2, 'name'); $worksheet - > setCellValueByColumnAndRow (2, 2, 'Chinese'); $worksheet - > setCellValueByColumnAndRow (3, 2, 'mathematics'); $worksheet - > setCellValueByColumnAndRow (4, 2, 'foreign language'); $worksheet - > setCellValueByColumnAndRow (5, 2, 'total'); $worksheet->mergeCells('A1:E1'); $styleArray = [ 'font' => [ 'bold' => true ], 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, ], ]; $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28); $worksheet->getStyle('A2:E2')->applyFromArray($styleArray)->getFont()->setSize(14);Copy the code

Second, read data

After we connect to the database, we directly read the student score table T_student, and then for loop, set the corresponding content of each cell, calculate the total score. Note that the data in the table starts at row 3, because rows 1 and 2 are occupied by the table header.

Then, we set the entire table style, border the table, and center it.

$sql = "SELECT id,name,chinese,maths,english FROM `t_student`"; $stmt = $db->query($sql); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); $len = count($rows); $j = 0; for ($i=0; $i < $len; $i++) { $j = $i + 3; / / start form line 3 $worksheet - > setCellValueByColumnAndRow (1, $j $rows [$I] [' name ']); $worksheet->setCellValueByColumnAndRow(2, $j, $rows[$i]['chinese']); $worksheet->setCellValueByColumnAndRow(3, $j, $rows[$i]['maths']); $worksheet->setCellValueByColumnAndRow(4, $j, $rows[$i]['english']); $worksheet->setCellValueByColumnAndRow(5, $j, $rows[$i]['chinese'] + $rows[$i]['maths'] + $rows[$i]['english']); } $styleArrayBody = [ 'borders' => [ 'allBorders' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['argb' => '666666'], ], ], 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, ], ]; $total_rows = $len + 2; $worksheet->getStyle('A1:E'.$total_rows)->applyFromArray($styleArrayBody);Copy the code

Three, download and save

Force the browser to download the data and save it as an Excel file

$filename = 'test.xlsx '; header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment; filename="'.$filename.'"'); header('Cache-Control: max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output');Copy the code

If you want to save as an.xls file, you can change the header code:

$filename = 'test.xlsx '; header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="'.$filename.'"'); header('Cache-Control: max-age=0'); $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'xls'); $writer->save('php://output');Copy the code

4. Use a SPREADSHEET format to set up cells

Spreadsheet is a comprehensive API that allows you to set up a wide range of cell and document properties, including styles, images, dates, functions, and more.

The correct files are imported and instantiated:

use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
Copy the code

The font

Line 1 sets cells A7 to B7 to bold, Arial, and 10 point font; Line 2 sets the B1 cell to bold.

$spreadsheet->getActiveSheet()->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial')
    ->setSize(10);;
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
Copy the code

color

Set the text color to red

$spreadsheet->getActiveSheet()->getStyle('A4')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
Copy the code

The picture

You can load images into Excel

$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Logo');
$drawing->setDescription('Logo');
$drawing->setPath('./images/officelogo.jpg');
$drawing->setHeight(36);
Copy the code

Column width

Set column A width to 30 (characters) :

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
Copy the code

If you need to calculate the column width automatically, you can do this:

$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
Copy the code

Set the default column width to 12:

$spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);
Copy the code

Line height

Set line 10 to 100pt height:

$spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100);
Copy the code

Set the default row height:

$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
Copy the code

alignment

Set cell A1 to horizontally centered:

$styleArray = [
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
];
$worksheet->getStyle('A1')->applyFromArray($styleArray);
Copy the code

merge

Merge A18 to E22 into a single cell:

$spreadsheet->getActiveSheet()->mergeCells('A18:E22');
Copy the code

Break up

Split the merged cells:

$spreadsheet->getActiveSheet()->unmergeCells('A18:E22');
Copy the code

A border

Add a red border to areas B2 to G8:

$styleArray = [
    'borders' => [
        'outline' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => ['argb' => 'FFFF0000'],
        ],
    ],
];
$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);
Copy the code

Worksheet title

Set the current worksheet title:

$spreadsheet->getActiveSheet()->setTitle('Hello');
Copy the code

Date/time

Set the date format:

$spreadsheet->getActiveSheet()
    ->setCellValue('D1', '2018-06-15');

$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2);
Copy the code

A newline

Use \n to wrap cells, equivalent to (ALT+”Enter”):

$spreadsheet->getActiveSheet()->getCell('A4')->setValue("hello\nworld");
$spreadsheet->getActiveSheet()->getStyle('A4')->getAlignment()->setWrapText(true);
Copy the code

hyperlinks

Set the cell to hyperlink:

$spreadsheet->getActiveSheet()->setCellValue('E6', 'www.helloweba.net');
$spreadsheet->getActiveSheet()->getCell('E6')->getHyperlink()->setUrl('https://www.helloweba.net');
Copy the code

Using the function

Use SUM to calculate the SUM of cells between B5 and C5. Other functions are the same: maximum number (MAX), minimum number (MIN), AVERAGE (AVERAGE):

$spreadsheet->getActiveSheet()
    ->setCellValue('B7', '=SUM(B5:C5)');
Copy the code

Setting document Properties

You can set the Excel document properties:

$spreadsheet->getProperties() ->setCreator("Helloweba") ->setLastModifiedBy("Yuegg") ->setTitle("Office" 2007 XLSX Test Document") // title ->setDescription(" Office 2007 XLSX Test Document") // subtitle ->setDescription("Test Document for Office 2007 XLSX, Generated using PHP classes.") // description ->setKeywords(" Office 2007 openXML PHP ") // 标 准 ->setCategory("Test result file"); / / classificationCopy the code

In addition to providing a rich Excel file processing interface, PhpSpreadshee also provides CSV, PDF, HTML and XML file processing interface.

Author: calm _b99e links: www.jianshu.com/p/10e1f047f… The copyright of the book belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please indicate the source.