Implementation code for writing excel files in php

This article introduces how to use PHP to implement the code for writing excel files, including titles and content. Friends in need can refer to it.

PHP writes excel files, including titles. This is often used in daily development. For example, some operations on contact lists and company address books will use the function of PHP to operate excel files.

Code:

<?php
/** 
     * 将联系人导出为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;  
    }

Code description: The two ob_end_clean();$objPHPExcel->disconnectWorksheets();unset($objPHPExcel); in the exportFileContacts method; This is to clear the buffer contents and prevent the exported excel from errors, inability to open, and formatting errors.