PHPExcel 导出用到的基本方法

PHPExcel 导出用到的基本方法，普通的方法应该都有了，
如果有遗漏，那欢迎补充，谢谢！

//设置行高

$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);

/*单元格宽度*/

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);

/*设置表格对其方式，setVertical-垂直*/
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

居中和字体样式，默认和单个

$format = array(
    'font' => array('size'=>13,'bold' => true),
    'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
);
$objPHPExcel->getDefaultStyle()->applyFromArray($format);
$objPHPExcel->getActiveSheet(0)->getStyle("C".$i)->applyFromArray($format);

单个对齐

$objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

是否加粗

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

设置单元格格式，要‘C2’单元格为设置才行，否则失败

$objPHPExcel->getActiveSheet()->getCell('C2')->setValueExplicit('1234567890123456123', PHPExcel_Cell_DataType::TYPE_NUMERIC);

单元格换行

$title_order = "TM
请发电子\r\n到**报名";
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$start_row, $title_order);
$objPHPExcel->getActiveSheet()->mergeCells('A'.$start_row.':G'.$start_row);
/*满一行后自动换行，如需要手动，则家\r\n，或者直接回城代替。*/
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);

导入图片

$objDrawing = new PHPExcel_Worksheet_Drawing();
/*名字和描述*/
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
/*图片地址，必须是本地*/
$objDrawing->setPath("./template/images/logo.png");
/*图片高度,宽度自动改变*/
$objDrawing->setHeight(50);
$objDrawing->setCoordinates("A1");
/*离左上角的宽度和旋转的角度*/
$objDrawing->setOffsetX(0);
$objDrawing->setRotation(0);
/*是否显示明显，false看起来像背景，true看起来想图片*/
$objDrawing->getShadow()->setVisible(false);
/**/
$objDrawing->getShadow()->setDirection(0);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet(0));

合并单元格和分离单元格

$objPHPExcel->getActiveSheet()->mergeCells('A1:E2');
$objPHPExcel->getActiveSheet()->unmergeCells('A1:E2');

插入数据，多条插入时，建议用for语句插入

$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$start_row, "姓名")

foreach($data as $key=>$d){
    //项目基本信息
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('G'.$start_row, "房型");
    //项目申报情况
    $start_row++;
}

header("Content-type: application/octet-stream");
header("Accept-Ranges: bytes");
header("Accept-Length:".$file_size);
// header("Content-Disposition: attachment; filename=".$file_name);
header ( "Content-type:application/vnd.ms-excel" );
header ( "Content-Disposition:filename=订单详情表.xls" );

设置sheet的值

$objPHPExcel->getActiveSheet()->setTitle('phpexcel demo');

隐藏F列

$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setVisible(false);

固定第一行，A3代表固定第二行

$objPHPExcel->getActiveSheet()->freezePane('A2');

设置指定范围单元格表框

$objPHPExcel->getActiveSheet()->getStyle('A1:G20')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

单元格样式和颜色填充

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');

添加链接

$objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.baidu.com');
$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
$objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

日期，测试可行，但是好像不设置也正常（时间戳返回格式化失败返回###）

$objPHPExcel->getActiveSheet()->setCellValue('K2', '2008-12-31');
$objPHPExcel->getActiveSheet()->getStyle('K2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);