PHP写excel

wfkjxy

　　PHP写excel包括标题

/**
* 将联系人导出为excel
* @param $filename string 导出文件名
* @param $titles array 标题数组
* @param $elements array(array()) 元素数组
*/
public function exportFileContacts($filename, $titles, $elements){
try {
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0); //设置活动页面
$activeSheet = $objPHPExcel->getActiveSheet(); //获取当前活动页面
$activeSheet->getDefaultColumnDimension()->setWidth(16); //设置列宽16
$activeSheet->setTitle("联系人"); //设置sheet的名称
//设置标题，单元格第一列为0，第一行为1
$colNum = count($titles);
for($i = 0; $i < $colNum; $i++){
$activeSheet->getStyleByColumnAndRow($i, 1)->getFont()->setBold(true); //设置标题加粗
$activeSheet->setCellValueByColumnAndRow($i, 1, $titles[$i]);
}
//设置 body，row从2开始，第一行已经被设置为标题了
$rowNum = count($elements);
for($row = 0; $row < $rowNum; $row++){
for($col = 0; $col < $colNum; $col++){
$activeSheet->getStyle($this->numToEn($col).($row+2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$activeSheet->setCellValue($this->numToEn($col).($row+2)," ".$elements[$row][$col]);  //在写入Excels单元格的内容之前加一个空格，防止长数字被转化成科学计数法
$activeSheet->getStyle($this->numToEn($col).($row+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
}
//获取保存文件名称
$filetype = $this->getFileType($filename);
//根据文件类型读取excel文件
ob_end_clean();
            $objWriter = null;
if ($filetype == "xlsx") {
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename='.urlencode($filename));
header('Cache-Control: max-age=0');
ob_end_clean();
$objWriter->save('php://output');
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
exit;
} elseif ($filetype == "xls") {
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.urlencode($filename));
header('Cache-Control: max-age=0');
ob_end_clean();
$objWriter->save('php://output');
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
exit;
} else {
return false;
}
} catch (Exception $e) {
HWS::logErr('EXCEL ERROR:'.$e->getMessage());
$errText = "创建错误文件失败，请联系管理员，稍后再试！";
return array('createFileErr'=>$errText);
}
}
/**
* 获取文件类型
* @param $filenamePath 文件路径或者文件名
*/
private function getFileType($filenamePath){
if (!$filenamePath){
return false;
}
$filenameArr = explode('/', $filenamePath);
$count = count($filenameArr);
$filename = $filenameArr[$count-1];
$filetypeArr = explode('.', $filename);
$count = count($filetypeArr);
$filetype = $filetypeArr[$count-1];
return $filetype;   
}
/**
根据给定的数字生成至多两位对应EXCEL文件列的字母
*/
private function numToEn($num)
{
$asc = 0;
$en = "";
$num =(int)$num+1;
if($num<26)                      //判断指定的数字是否需要用两个字母表示
{
if((int)$num<10)
{
$asc = ord($num);
$en =chr($asc+16);
}
else
{
$num_g = substr($num,1,1);
$num_s = substr($num,0,1);
$asc = ord($num_g);
$en =chr($asc+16+10*$num_s);
}
}
else
{
$num_complementation = floor($num/26);
$en_q = $this->numToEn($num_complementation);
$en_h = $num%26 != 0 ? $this->numToEn($num-$num_complementation*26):"A";
$en = $en_q.$en_h;
}
return $en;
}
　　注：exportFileContacts方法中的两个ob_end_clean();$objPHPExcel->disconnectWorksheets(); unset($objPHPExcel);和是为了清楚缓冲区内容，防止导出的excel出错，打不开，报什么格式化错误。