php操作excel文件 基于phpexcel

PHP操作Excel文件 基于PHPExcel

PHPExcel是一个开源的PHP类库，用于操作Excel文件。使用PHPExcel可以实现将数据导出Excel、将Excel文件读取到PHP数组中等功能。
以下为PHPExcel的安装及基本用法。

安装

PHPExcel最新版已经停止更新，建议使用替代类库“PhpSpreadsheet”，安装方法如下：

1. 使用composer进行安装

composer require phpoffice/phpspreadsheet

基本用法

1.在PHP中导出Excel文件

在PHP中使用PHPExcel来导出Excel的步骤如下：

1. 加载PHPExcel类库

require('/path/to/PHPExcel.php');

1. 创建一个新的PHPExcel对象并设置默认属性

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()
    ->setCreator("Your Name")
    ->setLastModifiedBy("Your Name")
    ->setTitle("Title")
    ->setSubject("Subject")
    ->setDescription("Description")
    ->setKeywords("Keywords")
    ->setCategory("Category");

1. 插入数据并设置单元格格式

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello World!');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

1. 输出Excel文件

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="test.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

1. 示例代码

require_once '/path/to/vendor/autoload.php';
// 创建PHPExcel对象
$objPHPExcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
// 获取当前活动的工作表
$objSheet = $objPHPExcel->getActiveSheet();
// 设置当前工作表的属性
$objSheet->setTitle('标题');
// 设置表头
$objSheet->setCellValueByColumnAndRow(1, 1, '学号');
$objSheet->setCellValueByColumnAndRow(2, 1, '姓名');
$objSheet->setCellValueByColumnAndRow(3, 1, '班级');
// 设置样式
$objStyle = $objSheet->getStyle('A1:C1');
$objStyle->getFont()->setBold(true);
// 设置对齐方式
$objAlignment = $objStyle->getAlignment();
$objAlignment->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
// 填充数据
$data = [
    [1, '张三', '一班'],
    [2, '李四', '一班'],
    [3, '王五', '二班'],
];
$objSheet->fromArray($data, null, 'A2');
// 输出Excel文件
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="example.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($objPHPExcel, 'Xls');
$objWriter->save('php://output');

2.在PHP中读取Excel文件

在PHP中使用PHPExcel来读取Excel文件的步骤如下：

1. 加载PHPExcel类库

require('/path/to/PHPExcel.php');

1. 加载Excel文件

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load('/path/to/file.xlsx');

1. 读取Excel文件中的数据

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle = $worksheet->getTitle();
    $highestRow = $worksheet->getHighestRow();
    $highestColumn = $worksheet->getHighestColumn();
    for ($row = 1; $row <= $highestRow; $row++) {
        $rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, false);
        // $rowData为读取到的一行数据
    }
}

1. 示例代码

require_once '/path/to/vendor/autoload.php';
// 加载Excel文件
$objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$objPHPExcel = $objReader->load('example.xlsx');
// 读取Excel文件中的数据
$data = [];
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle = $worksheet->getTitle();
    $highestRow = $worksheet->getHighestRow();
    $highestColumn = $worksheet->getHighestColumn();
    for ($row = 2; $row <= $highestRow; $row++) {
        $rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, false);
        $data[] = [
            'id' => $rowData[0][0],
            'name' => $rowData[0][1],
            'class' => $rowData[0][2],
        ];
    }
}
print_r($data);