PhpSpreadsheet的简单使用

日期:2020-09-09 浏览:32次

使用composer安装:

composer require phpoffice/phpspreadsheet
gitee:https://github.com/PHPOffice/PhpSpreadsheet

数据源
$data = [
        [],
    ];
    $title = ['', ''];

excel文件导出

require_once __DIR__ . '/vendor/autoload.php';
tp6直接: $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++;
    }
------------------------------------------------------------
//第二种
//设置单元格内容
$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++;
}
---------------------------------------------------------------
//直接输出浏览器需要携带头部
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;

----------------------------------------------------------------
//写入文件
 $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
 $writer->save('01simple.xlsx');

--------------------------------------------------------------------
  //本地文件导入
    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;
-----------------------------------------------------------------------
//浏览器上传文件导入
$files = $_FILES["excel"];
//$files["excel"]['tmp_name'] 上传临时路径
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($files["excel"]['tmp_name']);
$data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

没有评论

发表评论