无论你入行早或是入行晚。PHP对Excel文档的操作你都有一次邂逅

一、Excel数据导出

1、新建一个excel表格

实例化PHPExcel类

require_once('./libs/PHPExcel.php');
$objPHPExcel = new PHPExcel();

2、创建sheet内置表

• createSheet()方法
• setActiveSheetIndex方法 设置当前的活动的sheet
• getActiveSheet方法

$objPHPExcel->getProperties()
                ->setCreator('jack')
                ->setTitle('数据的导出测试')
                ->setSubject('数据Excel导出')
                ->setDescription('描述内容')
                ->setKeywords('excel')
                ->setCategory('file');
# 设置当前的表格
$objPHPExcel->setActiveSheetIndex(0);

3、填充数据

• setCellValue

# 设置表格第一行显示内容
    $objPHPExcel->getActiveSheet()
                ->setCellValue('A1','学生姓名')
                ->setCellValue('B1','密码')
                ->setCellValue('C1','手机号码')
                ->setCellValue('D1','地址')
              //设置第一行为红色字体
              ->getStyle('A1:D1')->getFont()
              ->getColor()>setARGB(PHPExcel_Style_Color::COLOR_RED);

• 合并单元格

  $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');

• 分离单元格

  $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');

4、保存文件

   //设置当前表格
    $objPHPExcel->setActiveSheetIndex(0);
    ob_end_clean();
    header('Content-Type: application/vnd.ms-excel'); //文件类型
    header('Content-Disposition: attachment;filename="'.$name.'.xls"'); //文件名
    header('Cache-Control: max-age=0');
    header('Content-Type: text/html; charset=utf-8'); //编码
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  //excel 2003
    $objWriter->save('php://output');

二、读取Excel

1、引入读取Excel的类文件

require_once('./libs/PHPExcel/IOFactory.php');

2、加载Excel文件

$filename = './jack.xls';
$objPHPExcel = PHPExcel_IOFactory::load($filename);

3、遍历数据

$sheetCount = $objPHPExcel->getSheetCount();
# 不适合数据量大的表
for ($i=0;$i<$sheetCount;$i++){
     $data=$objPHPExcel->getSheet($i)->toArray();//读取每个工作表 全部放入数组中
}

4、逐行、列遍历

foreach ($objPHPExcel->getWorksheetIterator() as $sheet){ //循环读取每个工作表
    foreach ($sheet->getRowIterator() as $row){  //处理行
        foreach ($row->getCellIterator() as $cell){ //处理列
            $data = $cell->getValue(); //获取单元格数据
            echo $data.' ';
        }
        echo '<hr/>';
    }
}

追加

`设置宽width`
`// Set column widths`
`$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);`
`$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);`
`设置font`
`$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');`
`$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);`
`$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);`
`$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);`
`$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);`
`$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);`
`$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);`
`$objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);`
`设置align`
`$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);`
`$objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);`
`$objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);`
`$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);`
`//垂直居中`
`$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);`
`设置column的border`
`$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);`
`$objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);`
`$objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);`
`$objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);`
`$objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);`
`设置border的color`
`$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');`
`$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');`
`$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');`
`$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');`
`$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');`
`$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');`
`设置填充颜色`
`$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);`
`$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');`
`$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);`
`$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');`
`加图片`
`$objDrawing = new PHPExcel_Worksheet_Drawing();`
`$objDrawing->setName('Logo');`
`$objDrawing->setDescription('Logo');`
`$objDrawing->setPath('./images/officelogo.jpg');`
`$objDrawing->setHeight(36);`
`$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());`
`$objDrawing = new PHPExcel_Worksheet_Drawing();`
`$objDrawing->setName('Paid');`
`$objDrawing->setDescription('Paid');`
`$objDrawing->setPath('./images/paid.png');`
`$objDrawing->setCoordinates('B15');`
`$objDrawing->setOffsetX(110);`
`$objDrawing->setRotation(25);`
`$objDrawing->getShadow()->setVisible(true);`
`$objDrawing->getShadow()->setDirection(45);`
`$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());`