这个小功能包含:
1、设置字体加粗、字体大小、字体颜色;
2、设置单元格背景色、高度、宽度、边框、单元格的合并;
3、设置字体水平居中、垂直居中、自动换行
// 导出扣款报表
function exportDeduction($data, $datafield, $fileName, $title=''){
// 引入 PHPExcel 文件
include ROOT_PATH.'phpexcel/PHPExcel.php';
include_once ROOT_PATH.'phpexcel/PHPExcel/Writer/Excel2007.php';
include_once ROOT_PATH.'phpexcel/PHPExcel/IOFactory.php';
// 设置PHPExcel缓存机制
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_wincache;
$cacheSettings = array( 'cacheTime' => 600 );
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
//创建一个excel对象
$objPHPExcel = new PHPExcel();
//默认第一个sheet为当前的sheet
$objPHPExcel->setActiveSheetIndex(0);
$sheettmp = $objPHPExcel->getActiveSheet();
// 设置表格第一行的大标题
$sheettmp->setCellValue('A1', '测试 '.$fileName);
$objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFont() -> setBold(true);
// 合并单元格
$sheettmp->mergeCells('A1:Q1');
// 设置表格目录
$sheettmp->setCellValue('A2', '序号');$sheettmp->setCellValue('B2', '部门');$sheettmp->setCellValue('C2', '姓名');$sheettmp->setCellValue('D2', '事假');
$sheettmp->setCellValue('H2', '病假');$sheettmp->setCellValue('L2', '迟到');$sheettmp->setCellValue('N2', '忘打卡');$sheettmp->setCellValue('P2', '合计');
$sheettmp->setCellValue('Q2', '备注');$sheettmp->setCellValue('D3', '1-3天 (30元)');$sheettmp->setCellValue('E3', '3-7天 (50元)');$sheettmp->setCellValue('F3', '7天以上(100元)');
$sheettmp->setCellValue('G3', '合计');$sheettmp->setCellValue('H3', '1-3天 (30元)');$sheettmp->setCellValue('I3', '3-7天 (40元)');$sheettmp->setCellValue('J3', '7天以上 (50元)');
$sheettmp->setCellValue('K3', '合计');$sheettmp->setCellValue('L3', '次数 (30元/次)');$sheettmp->setCellValue('M3', '合计');
$sheettmp->setCellValue('N3', '次数 (30元/次)');$sheettmp->setCellValue('O3', '合计');
$sheettmp->mergeCells('A2:A3');$sheettmp->mergeCells('B2:B3');$sheettmp->mergeCells('C2:C3');$sheettmp->mergeCells('D2:G2');$sheettmp->mergeCells('H2:K2');
$sheettmp->mergeCells('L2:M2');$sheettmp->mergeCells('N2:O2');$sheettmp->mergeCells('P2:P3');$sheettmp->mergeCells('Q2:Q3');
// 设置单元格字体加粗
$objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFont() -> setBold(true);
$objPHPExcel -> getActiveSheet() -> getStyle('A2:Q2') -> getFont() -> setBold(true);
// 设置默认字体大小
$objPHPExcel -> getActiveSheet() -> getDefaultStyle() -> getFont() -> setSize(10);
// 设置单元格字体大小
$objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFont() -> setSize(14);
// 设置单元格字体颜色
$objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFont()->getColor()->setRGB('FF0000');
// 设置单元格背景色
$objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFill() -> setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel -> getActiveSheet() -> getStyle('A1') -> getFill() -> getStartColor() -> setRGB('99CCFF');
//设置单元格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6.5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(9);
$objPHPExcel->getActiveSheet()->getColumnDimension('D:E')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('G:K')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(11);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(11);
$objPHPExcel->getActiveSheet()->getColumnDimension('0')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(27);
// 设置单元格高度
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); // 设置默认
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20); // 设置指定行
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(30);
// 设置水平居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:Q2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置垂直居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:Q2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// 设置自动换行
$objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);
// 设置单元格边框
$styleThinBlackBorderOutline = array(
'borders' => array(
'allborders' => array( //设置全部边框
'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
),
),
);
$length = sizeof($data);
$objPHPExcel->getActiveSheet()->getStyle( 'A2:Q'.($length+4))->applyFromArray($styleThinBlackBorderOutline);
// 引入数据
foreach ($data as $index => $row){
$i = $index+4;
$colIndex='A';
// 列循环
foreach ($datafield as $field => $name)
{
$sheettmp->setCellValue($colIndex . $i, $row[$field]);
$colIndex++;
}
}
// 删除多余的最后一列
//$objPHPExcel->getActiveSheet()->removeColumn(++$colIndex,1);
$sheettmp -> setTitle($title);
$objPHPExcel-> setActiveSheetIndex(0);
$fileName = iconv("utf-8", "gb2312", $fileName);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(ROOT_PATH.'upload/file/'.$fileName.'.xlsx'); // 脚本方式运行,保存在在服务器上传目录
return iconv("gb2312", "utf-8", ROOT_URL.'upload/file/'.$fileName.'.xlsx');
导出Excel:就不放数据了