php操作excel

在php中我们可以用phpexcel这个扩展来操作excel文档,这篇文章该介绍扩展的基本操作,简单的样式调整和导入导出文件。

基本操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<?php

$dir = dirname(__FILE__);

// 引入插件类
require $dir . '/Class/PHPExcel.php';

// 实例化类
$objExcel = new PHPExcel();

// 创建新页,此时有两个页,第一页是默认存在的
$objExcel->createSheet();

// 设置当前页
$objExcel->setActiveSheetIndex(0);

// 获取当前页对象,默认为0
$objSheet = $objExcel->getActiveSheet();

// 设置当前页的标题
$objSheet->setTitle('demo');

// 填充单元格的指
$objSheet->setCellValue('A1', '姓名')->setCellValue('B1', '分数');
$objSheet->setCellValue('A2', '张三')->setCellValue('B2', '90');

// 保存本地文件
// $objWrite = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5'); // .xls
$objWrite = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007'); // .xlsx
$objWrite->save($dir . '/demo.xlsx');

// 输出到浏览器
// set_header('Excel5', 'demo.xls');
// $objWrite->save('php://output');

function set_header($type, $filename) {
if($typ == 'Excel5') {
header('Content-Type: application/vnd.ms-excel');
}else {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
}

header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
}

基本样式控制

文字对齐

1
2
3
4
// 默认样式
$objExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objSheet->getStyle('A1:C3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

合并单元格

1
2
$objSheet->mergeCells('A1:C3');
$objSheet->unmergeCells('A1:C3');

设置字体和大小

1
$objSheet->getStyle('A1:Z1')->getFont()->setName('微软雅黑')->setSize(20);

设置字体颜色

1
$objSheet->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);

设置背景颜色

1
2
$objSheet->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()
->setARGB('FFC9C9C9');

设置边框

1
2
3
4
5
6
7
8
9
10
$styleArr = [
'borders' => [
'outline' => [
'style' => PHPExcel_Style_Border::BORDER_THICK,
'color' => ['argb' => 'FF000000']
]
]
];

$objSheet->getStyle('A1')->applyFromArray($styleArr);

设置换行

1
2
$objSheet->getStyle('A')->getAlignment()->setWrapText(true);
$objSheet->setCellValue('A1', "hello\nworld");

显示单元格显示格式

1
$objSheet->setCellValueExplicit ('A1', 162736712637162, PHPExcel_Cell_DataType::TYPE_STRING);

导入Excel文件

加载全部页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?php

$dir = dirname(__FILE__);

// 引入插件类
require $dir . '/Class/PHPExcel/IOFactory.php';

$filename = $dir . '/demo.xlsx';

// 全部加载
$objExcel = PHPExcel_IOFactory::load($filename);

// for循环加载数据
$sheetCount = $objExcel->getSheetCount();
for($i = 0; i < $sheetCount; $i++) {
$data = $objExcel->getSheet($i)->toArray();
print_r($data);
}

// 用迭代器加载数据
foreach($objExcel->getWorksheetIterator() as $sheet) {
foreach($sheet->getRowIterator() as $row) {
foreach($row->getCellIterator() as $cell) {
$data = $cell->getValue();
echo $data . ' ';
}
echo '<br/>';
}
echo '<br/>';
}

部分加载页

1
2
3
4
5
6
7
$filename = $dir . '/demo.xlsx';
$fileType = PHPExcel_IOFactory::identify($filename);
$objReader = PHPExcel_IOFactory::createReader($fileType);
$sheetName = ['成绩'];
$objReader->setLoadSheetsOnly($sheetName);
// 部分加载
$objExcel = $objReader->load($filename);