PHPExcel基本操作:
定义EXCEL实体
即定义一个PHPEXCEL对象,并设置EXCEL对象内显示内容
三、输出文件
设置一列的宽度:
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
设置一行的高度
$objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(30);
合并单元格:
$objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
加粗居中
$styleArray1 = array(
'font' => array(
'bold' => true,
'size'=>12,
'color'=>array(
'argb' => '00000000',
),
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
),
);
// 将A1单元格设置为加粗,居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray1);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
给特定单元格中写入内容:
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello Baby');
放链接
$objPHPExcel->getActiveSheet()->setCellValue('H8', iconv('gbk', 'utf-8', '燕南天'));
$objPHPExcel->getActiveSheet()->getCell('H8')->getHyperlink()->setUrl('http://www.jb51.net/');
设边框
$styleThinBlackBorderOutline = array(
'borders' => array (
'outline' => array (
'style' => PHPExcel_Style_Border::BORDER_THIN, //设置border样式
//'style' => PHPExcel_Style_Border::BORDER_THICK, 另一种样式
'color' => array ('argb' => 'FF000000'), //设置border颜色
),
),
);
$objPHPExcel->getActiveSheet()->getStyle( 'A4:E10')->applyFromArray($styleThinBlackBorderOutline);
//添加一个新的worksheet
$objExcel->createSheet();
$objActSheet = $objExcel->getSheet($s);
$objActSheet->setTitle('表'.$GSheet);
水平/垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
设置单元格样式(黑色字体):
$objPHPExcel->getActiveSheet()->getStyle('H5')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK); // 黑色
设置单元格格式(背景):
$objPHPExcel->getActiveSheet()->getStyle('H5')->getFill()->getStartColor()->setARGB('00ff99cc'); // 将背景设置为浅粉色
设置单元格格式
$objPHPExcel->getActiveSheet()->getStyle('F'.$iLineNumber)->getNumberFormat()->setFormatCode('0.000');
单元格放图片
// 将数据中心图片放在J1单元格内
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('test.jpg');
$objDrawing->setWidth(400);
$objDrawing->setHeight(123);
$objDrawing->setCoordinates('J1');
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());