/**
* $data = array(
* array(NULL, 2010, 2011, 2012),
* array('Q1', 12, 15, 21),
* array('Q2', 56, 73, 86),
* array('Q3', 52, 61, 69),
* array('Q4', 30, 32, 0),
* );
* $data 第一个数组下为一个元素的话设置成表头
*/
function excel_xls($data, $filename)
{
//文件名称
$filename=str_replace('.xls', '', $filename).'.xls';
//引入phpexe文件
include './Classes/PHPExcel.php';
$phpexcel = new PHPExcel();
$objWriter = new \PHPExcel_Writer_Excel5($phpexcel);
if (count($data[0]) == 1 && count($data[1]) > 1) {
//设置表头
$count=count($data[1])-1;
$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M',
'N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE',
'AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU'
,'AV','AW','AX','AY','AZ');
$title = $data[0];
if (is_array($data[0])){
$title = $data[0][0];
}
//>mergeCells('a1:'.$cellName[$count].'1') 合并单元格
$phpexcel->getActiveSheet()->mergeCells('a1:'.$cellName[$count].'1')->setCellValue('A1',$title);
}
$phpexcel->getActiveSheet()->fromArray($data);
$phpexcel->getActiveSheet()->setTitle('Sheet1');
//全部居中显示
//$phpexcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//单个单元格居中
$phpexcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$phpexcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=$filename");
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$objwriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
$objwriter->save('php://output');
};
$data = array(
array(21),
array('Q1', 12, 15, 21),
array('Q2', 56, 73, 86),
array('Q3', 52, 61, 69),
array('Q4', 30, 32, 0));
$filename = 'sadadsa.xls';
excel_xls($data,$filename);//合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
//拆分单元格
//$objPHPExcel->getActiveSheet()->unmergeCells('A1:H1');
//设置单元格字体
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体') //字体
->setSize(12) //字体大小
->setBold(true); //字体加粗
//设置行的高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(31.5);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(31.5);
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(69.75);
//长度不够显示的时候 是否自动换行
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
//设置打印 页面 方向与大小(此为横向)
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//冻结标题
//第一种方式
//$objPHPExcel->getActiveSheet()->freezePane('A1');
//$objPHPExcel->getActiveSheet()->freezePane('A2');
//第二种方式
$objPHPExcel->getActiveSheet()->freezePaneByColumnAndRow(8,4);//(列,行)冻结的行数比你设置的要多一行(例 我需要设置的 是 3行 那么 参数就要写 4)
//设置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//左对齐
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
//右对齐
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
// 设置页面边距为0.5厘米 (1英寸 = 2.54厘米)
$margin = 1.78 / 2.54; //phpexcel 中是按英寸来计算的,所以这里换算了一下
$marginright = 1 / 2.54; //phpexcel 中是按英寸来计算的,所以这里换算了一下
//$pageMargins->setTop($margin); //上边距
//$pageMargins->setBottom($margin); //下
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($margin); //左
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(marginright); //右
//$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth('1');//自动填充到页面的宽度
//$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight('1');//自动填充到页面的高度
//设置 标题索引 (点击 标题 跳转到对应的工作簿中)
$objPHPExcel->getActiveSheet()->getCell('B4')->getHyperlink()->setUrl("sheet://'"计算机软件技术"'!A1");
//设置边框
$objPHPExcel->getActiveSheet()->getStyle('A1:H8')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);