php导出文件,php导出ecxcel文件

/**

* $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);