php使用phpSpreadsheet读写excel常用

安装

composer require phpoffice/phpspreadsheet

读文件

<?php
require './libs/phpoffice/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

#读文件
$file_name = './files/tips.xlsx';
$spreadSheet = IOFactory::load($file_name); //载入xlsx文件
$spreadSheet->getProperties()->setCreator("hello"); //设置xlsx作者

$count = $spreadSheet->getSheetCount(); //工作表总数
$sheet_names = $spreadSheet->getSheetNames(); //工作表名数组
$sheet = $spreadSheet->getSheetByName('Sheet1'); //根据表名获取工作表
$sheet = $spreadSheet->getSheet(0); //根据表索引获取工作表
$sheet = $spreadSheet->setActiveSheetIndex(0); //切换当前工作表
$sheet = $spreadsheet->setActiveSheetIndexByName('DataSheet'); //切换当前工作表
$sheet = $spreadSheet->getActiveSheet();

//最大行数
$row_num = $sheet->getHighestRow(); //最大行数 245
//最大列数
$col_num = $sheet->getHighestColumn(); //最大列数 G

//获取当前工作表所有有数据
$data = $sheet->toArray(); //二位数组

#单元格
$cell = $sheet->getCell('A1');
$value = $cell->getValue(); //获取单元格值
$coord = $cell->getCoordinate(); //获取行列信息 A1
$column = $cell->getColumn(); //获取列信息 A
$row = $cell->getRow(); //获取行信息 1
$data_type = $cell->getDataType(); //获取单元格数据类型 s
list($column, $row) = Coordinate::coordinateFromString($coord); //拆分成 ['A','1'];

写文件

<?php
require './libs/phpoffice/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Helper\Html as HtmlHelper;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\Title;

#写文件
$spreadsheet = new Spreadsheet();

//设置默认样式
$spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
$spreadsheet->getDefaultStyle()->getFont()->setSize(14);

#文档属性
$spreadsheet->getProperties()->setCreator('test')
    ->setLastModifiedBy('test')
    ->setTitle('test')
    ->setSubject('write xlsx')
    ->setDescription('Test')
    ->setKeywords('test')
    ->setCategory('Test result file');

#文档保护
$spreadsheet->getSecurity()->setWorkbookPassword('PhpSpreadsheet');

$worksheet = $spreadsheet->getActiveSheet();
#工作表重命名
$worksheet->setTitle('basic');
#设置工作表标签的颜色
$worksheet->getTabColor()->setRGB('FF0000');

#写入单元格值
$worksheet->setCellValue('A1', "hello");
//设置换行
$worksheet->setCellValue('B1', "hello\nphp");
$worksheet->getStyle('B1')->getAlignment()->setWrapText(true);

//设置日期格式 
$worksheet->getCell('A2')->setValue('2021-03-27 23:22:59');
$worksheet->getCell('B2')->setValue('2021-03-27 23:22:59');
$worksheet->getStyle('A2')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);
$worksheet->getStyle('B2')->getNumberFormat()->setFormatCode('dd/mm/yyyy');

//设置超链接
$worksheet->getCell('C3')->setValue("百度")->getHyperlink()->setUrl('https://www.baidu.com');

//公式
$worksheet->setCellValue('A4', 400);
$worksheet->setCellValue('B4', '=IF(A4>500,"profit","loss")');
$formula = $worksheet->getCell('B4')->getValue();
$value = $worksheet->getCell('B4')->getCalculatedValue();
$worksheet->setCellValue('C4', $formula);
$worksheet->setCellValue('D4', $value);

//设置单元格数据类型
$worksheet->getCell('C1')->setValueExplicit('25', DataType::TYPE_NUMERIC);

//富文本
$html = '<strong>bold</strong>, <em>italic</em>, <strong><em>bold+italic</em></strong>';;
$htmlHelper = new HtmlHelper();
$richText = $htmlHelper->toRichTextObject($html);
$worksheet->setCellValue('A5', $richText);

#合并拆分单元格
$worksheet->mergeCells('C3:D3');
$worksheet->mergeCells('A3:B3');
$worksheet->unmergeCells('A3:B3');

//插入删除行列
$worksheet->insertNewRowBefore(7, 2); //在第7行前插入2行
$worksheet->removeRow(7, 2); //从第7行开始删除2行

//设置字体粗细大小颜色
$worksheet->getCell('B5')->setValue("彩色字体");
$worksheet->getStyle('B5')->getFont()->setBold(true)->setName('Arial')->setSize(20);
$worksheet->getStyle('B5')->getFont()->getColor()->setRGB('AEEEEE'); //设置颜色

//设置单元格背景色
$worksheet->getStyle('A4:D4')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_GREEN);

//设置单元格对齐方式
$worksheet->getStyle('A4:D4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$worksheet->getStyle('A4:D4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

//设置单元格边框
$worksheet->getStyle('A4:D4')->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THIN);

//设置格式
$styleArray = [
    'font' => [
        'bold' => true,
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_RIGHT,
    ],
    'borders' => [
        'top' => [
            'borderStyle' => Border::BORDER_THIN,
        ],
    ],
    'fill' => [
        'fillType' => Fill::FILL_GRADIENT_LINEAR,
        'rotation' => 90,
        'startColor' => [
            'argb' => 'FFA0A0A0',
        ],
        'endColor' => [
            'argb' => 'FFFFFFFF',
        ],
    ],
];
$worksheet->getStyle('A6:C6')->applyFromArray($styleArray);
$worksheet->setCellValue('A6', '渐变背景');

#设置行高
$worksheet->getDefaultRowDimension()->setRowHeight(10, 'mm');
$worksheet->getRowDimension(1)->setRowHeight(10, 'mm');
$worksheet->getRowDimension(6)->setRowHeight(30);

#设置列宽
$worksheet->getDefaultColumnDimension()->setWidth(20);
$worksheet->getColumnDimension('A')->setAutoSize(true);
$worksheet->getColumnDimension('B')->setWidth(30);

#设置打印选项
$worksheet->getPageSetup()->setFitToWidth(1);
$worksheet->getPageSetup()->setFitToHeight(0);
$worksheet->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE)->setPaperSize(PageSetup::PAPERSIZE_A4);;
$worksheet->getPageMargins()->setTop(1)->setRight(0.75)->setLeft(0.75)->setBottom(1);
$worksheet->getPageSetup()->setHorizontalCentered(true)->setVerticalCentered(false);
$worksheet->setPrintGridlines(true);
$worksheet->getPageSetup()->setPrintArea('A1:E5,G4:M20');

#设置缩放
$worksheet->getSheetView()->setZoomScale(75);

#新增一个工作表
$worksheet1 = $spreadsheet->createSheet(1);
$sheet_name = 'chart';
$worksheet1->setTitle('chart');
$data = [
    ['', 2010, 2011, 2012],
    ['Q1', 12, 15, 21],
    ['Q2', 56, 73, 86],
    ['Q3', 52, 61, 69],
    ['Q4', 30, 32, 0],
];
$worksheet1->fromArray($data);

#图表
$dataseriesLabels = array(
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $sheet_name . '!$A$2', NULL, 1),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $sheet_name . '!$A$3', NULL, 1),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $sheet_name . '!$A$4', NULL, 1),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $sheet_name . '!$A$5', NULL, 1),
);
$xAxisTickValues = array(
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $sheet_name . '!$B$1:$D$1', NULL, 3),
);
$dataSeriesValues = array(
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, $sheet_name . '!$B$2:$D$2', NULL, 3),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, $sheet_name .  '!$B$3:$D$3', NULL, 3),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, $sheet_name . '!$B$4:$D$4', NULL, 3),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, $sheet_name .  '!$B$5:$D$5', NULL, 3),
);
$series = new DataSeries(
    DataSeries::TYPE_BARCHART,
    DataSeries::GROUPING_STANDARD,
    range(0, count($dataSeriesValues) - 1),
    $dataseriesLabels,
    $xAxisTickValues,
    $dataSeriesValues
);
$series->setPlotDirection(DataSeries::DIRECTION_COL);
$plotarea = new PlotArea(NULL, array($series));
$legend = new Legend(Legend::POSITION_TOPRIGHT, NULL, false);
$title = new Title('Column Chart');
$yAxisLabel = new Title('Value');
$chart = new Chart(
    'chart1',
    $title,
    $legend,
    $plotarea,
    true,
    DataSeries::EMPTY_AS_GAP,
    NULL,
    $yAxisLabel
);
$chart->setTopLeftPosition('A8');
$chart->setBottomRightPosition('H20');
$worksheet1->addChart($chart);

$worksheet2 = $spreadsheet->createSheet(2);
$worksheet2->setTitle('image');
//写入图片
$drawing = new Drawing();
$drawing->setName('Logo')
    ->setDescription('Logo')
    ->setPath('./files/1.jpg')
    ->setHeight(30)
    ->setCoordinates('B2')
    ->setOffsetX(50)
    ->setOffsetY(6);
$drawing->setRotation(25);
$drawing->getShadow()->setVisible(true);
$drawing->getShadow()->setDirection(45);
$drawing->setWorksheet($worksheet2);

// 保存xlsx在本地
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(TRUE);
$writer->save('./files/demo.xlsx');