php使用phpSpreadsheet读写excel

标签：sheet resp 单元格 excel value phpSpreadsheet 设置 php drawing

本文转载自https://www.cnblogs.com/ryuma/p/14586372.html 和 https://www.cnblogs.com/woods1815/p/11372007.html

phpSpreadsheet

GitHub： https://github.com/PHPOffice/PhpSpreadsheet

composer require phpoffice/phpspreadsheet '1.19.0'

引入文件

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;//用于载入已有的xlsx文件
use PhpOffice\PhpSpreadsheet\Spreadsheet; 
use Phpoffice\PhpSpreadsheet\Shared\Date;//日期操作类 
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;//单元格格式类型
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;//保存xlsx文件

excel文档操作

$inputFileName = 'xabc.xlsx';
$spreadSheet = IOFactory::load($inputFileName);//载入xlsx文件
$spreadSheet = new Spreadsheet();//新建一个xlsx文件
$spreadSheet->getProperties()->setCreator("hellow");//设置xlsx作者
$resp = $spreadSheet->getSheetCount();//工作表总数
$resp = $spreadSheet->getSheetNames();//工作表名数组
$sheet = $spreadSheet->getSheetByName('Sheet1');//根据表名获取工作表
$sheet = $spreadSheet->getSheet(0);//根据表索引获取工作表
$sheet = $spreadSheet->setActiveSheetIndex(0);//切换当前工作表
$sheet = $spreadSheet->getActiveSheet();

工作表操作

//常用读取操作
$cell = $sheet->getCell('A1'); //获取单元格A1的值
$cell = $sheet->getCellByColumnAndRow(2, 1); //获取单元格 B1
$data = $sheet->toArray(); //获取文档所有值
$resp = $sheet->getHighestRow(); //最大行数 19
$resp = $sheet->getHighestColumn(); //最大列数 C
//常用写操作
$sheet->setTitle('Hello'); //设置标题
$sheet->mergeCells('E7:F10'); //单元格合并
$sheet->unmergeCells('B7:C10'); //拆分单元格
$resp = $sheet->setCellValue('A1', "hellow\nphp"); //设置值
$resp = $sheet->setCellValueByColumnAndRow(2, 1, '6666'); //设置B2的值
$resp = $sheet->fromArray($arr, null, 'D5'); //数组,空值时填充值,开始单元格坐标
$sheet->getColumnDimension('A')->setWidth(300); //设置A列的宽度
$sheet->getDefaultColumnDimension()->setWidth(50); //设置列默认宽度
$sheet->getRowDimension(10)->setRowHeight(300); //设置第一行的宽度
$sheet->getDefaultRowDimension()->setRowHeight(50); //设置行默认高度 但不会修改已设置过高度/已有值的行

$resp = $sheet->setCellValue('B2', "hellow\nphp"); //设置换行 双引号+\n+setWrapText
$resp = $sheet->getStyle('B2')->getAlignment()->setWrapText(true); //设置换行

$resp = $sheet->setCellValue('B2', "百度"); //设置A链接
$resp = $sheet->getCell('B3')->getHyperlink()->setUrl('https://www.baidu.com'); //设置换行

$resp = $sheet->getStyle('A1')->getFont()->setBold(true)->setName('Arial')->setSize(10); //设置字体加粗大小
$resp = $sheet->getStyle('A1')->getFont()->getName(); //字体名
$resp = $sheet->getStyle('B2')->getFont()->getColor()->setRGB('#AEEEEE'); //设置颜色
$resp = $sheet->getStyle('A1')->getFont()->getColor()->getRGB(); //获取颜色值
$sheet->getCell('D1')->setValue('2021-03-27 23:22:59');
$sheet->getCell('D2')->setValue(Date::PHPToExcel(time())); //设置日期
$sheet->getStyle('D2')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY); //设置日期格式 
$sheet->getStyle('D2')->getNumberFormat()->setFormatCode('dd/mm/yyyy');//设置日期格式 与上文相同

单元格操作　

//常用读
$cell = $sheet->getCell('A1');//获取单元格A1的值
$resp = $cell->getValue();//获取单元个值
$resp = $cell->getCoordinate();//获取行列信息 A1
$column = $cell->getColumn();//获取列信息 A
$row   = $cell->getRow();//获取行信息 1
list($column, $row) = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($coordi);//拆分成 ['A','1'];
$resp = $cell->getDataType();//获取数据类型
//常用写
$resp = $cell->setValue('8888');//设置值
$cell->getStyle()->getFont()->getName();//单元个样式设置 参考工作表设置 只是不需要设置单元格位置而已
//设置A标签与换行参考工作表设置

图像操作

//图片读取与存储
$draws = $sheet->getDrawingCollection();//获取所有图像
$imageFilePath = 'uploads/';
foreach ($draws as $drawing) {//$drawing 为 PhpOffice\PhpSpreadsheet\Worksheet\Drawing类的实例;
    $coordi = $drawing->getCoordinates();//获取图像坐标 eg A4
    list($startColumn, $startRow) = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($coordi);//拆分成 ['A','4'];
    $imageFileName = $imageFilePath.$coordi.'_'. mt_rand(1000, 9999);
    switch ($drawing->getExtension()) {
        case 'jpg':
        case 'jpeg':
            $imageFileName .= '.jpg';
            $source = imagecreatefromjpeg($drawing->getPath());
            $res = imagejpeg($source,  $imageFileName);
            break;
        case 'gif':
            $imageFileName .= '.gif';
            $source = imagecreatefromgif($drawing->getPath());
            imagegif($source,  $imageFileName);
            break;
        case 'png':
            $imageFileName .= '.png';
            $source = imagecreatefrompng($drawing->getPath());
            $res = imagepng($source, $imageFileName);
            break;
    }
}

//图像写入操作
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setWorksheet($sheet);
$drawing->setPath(dirname(__FILE__).'/'.$imageFileName);
$drawing->setWidth(200);//图片宽
$drawing->setHeight(200);//图片高
$drawing->setOffsetX(100);//设置图片偏移量
$drawing->setCoordinates('A5');//将图片放置于单元格

excel文件导出实例

/**
 * excel文件导出
 */
function export()
{
    require_once __DIR__ . '/vendor/autoload.php';
 
    $data = [
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222']
    ];
    $title = ['第一行标题', '第二行标题'];
 
    // Create new Spreadsheet object
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
 
    // 方法一，使用 setCellValueByColumnAndRow
    //表头
    //设置单元格内容
    foreach ($title as $key => $value) {
        // 单元格内容写入
        $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
    }
    $row = 2; // 从第二行开始
    foreach ($data as $item) {
        $column = 1;
        foreach ($item as $value) {
            // 单元格内容写入
            $sheet->setCellValueByColumnAndRow($column, $row, $value);
            $column++;
        }
        $row++;
    }
 
    // 方法二，使用 setCellValue
    //表头
    //设置单元格内容
    $titCol = 'A';
    foreach ($title as $key => $value) {
        // 单元格内容写入
        $sheet->setCellValue($titCol . '1', $value);
        $titCol++;
    }
    $row = 2; // 从第二行开始
    foreach ($data as $item) {
        $dataCol = 'A';
        foreach ($item as $value) {
            // 单元格内容写入
            $sheet->setCellValue($dataCol . $row, $value);
            $dataCol++;
        }
        $row++;
    }
 
    // Redirect output to a client’s web browser (Xlsx)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="01simple.xlsx"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');
 
    // If you're serving to IE over SSL, then the following may be needed
    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header('Pragma: public'); // HTTP/1.0
 
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
    exit;
}

excel文件保存到本地

/**
 * excel文件保存到本地
 */
function save()
{
    require_once __DIR__ . '/vendor/autoload.php';
 
    $data = [
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222'],
        ['title1' => '111', 'title2' => '222']
    ];
    $title = ['第一行标题', '第二行标题'];
 
    // Create new Spreadsheet object
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
 
    //表头
    //设置单元格内容
    $titCol = 'A';
    foreach ($title as $key => $value) {
        // 单元格内容写入
        $sheet->setCellValue($titCol . '1', $value);
        $titCol++;
    }
    $row = 2; // 从第二行开始
    foreach ($data as $item) {
        $dataCol = 'A';
        foreach ($item as $value) {
            // 单元格内容写入
            $sheet->setCellValue($dataCol . $row, $value);
            $dataCol++;
        }
        $row++;
    }
 
    // Save
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('01simple.xlsx');
}

读取excel文件内容

/**
 * 读取excel文件内容
 */
function read()
{
    require_once __DIR__ . '/vendor/autoload.php';
    $inputFileName = dirname(__FILE__) . '/01simple.xlsx';
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
    // 方法二
    $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
    return $sheetData;
}

标签：sheet,resp,单元格,excel,value,phpSpreadsheet,设置,php,drawing
From： https://www.cnblogs.com/caroline2016/p/16905680.html