PHPExcel操作excel

1.导出文件基本操作

<?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');
}

2.基本样式控制

2.1文字对齐

// 默认样式
$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);

2.2 合并单元格

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

2.3 设置字体和大小

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

2.4 设置字体颜色

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

2.5 设置背景颜色

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

2.6 设置边框

$styleArr = [
    'borders' => [
        'outline' => [
            'style' => PHPExcel_Style_Border::BORDER_THICK,
            'color' => ['argb' => 'FF000000']
        ]
    ]
];

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

2.7 设置换行

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

2.8 显示单元格显示格式

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

3. 导入Excel文件

3.1 加载全部页

<?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/>';
}

3.2 部分加载页

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