衛斯理不理學習心得與技術日常

/**
 * 匯出PHPExcel欄位資料小於A-Z 26個欄位可用自動生成欄位與值
 * @param  string $OA_LIB_PATH 路徑
 * @param  string $file_name   檔案名稱
 * @param  string $title       頁籤名稱
 * @param  array $field_rows   欄位名稱
 * @param  array $data_rows    資料陣列
 * @param  string $rows_width  欄位寬度
 */
function exportListExcel($LIB_PATH,$file_name = '',$title = '',$field_rows,$data_rows,$rows_width = ''){
    if(!is_array($field_rows) || !is_array($data_rows)){
        return false;
    }

    include_once("$LIB_PATH/PHPExcel/PHPExcel.php");

    //初始個人設定
    $objPHPExcel = new PHPExcel();

    //設定標頭
    $objPHPExcel->setActiveSheetIndex(0)->setTitle($title);

    //設定第一列欄位名稱不可超過26欄
    $row = 1;
    $col = 0;
    foreach ($field_rows as $key => $title)
    {
        $objPHPExcel->getActiveSheet()->setCellValue(chr(65+$col).$row, $title);
        $col++;
    }

    foreach ($data_rows as $info) {
        $row++;

        $col = 0;
        foreach ($info as $v){
            $objPHPExcel->getActiveSheet()->setCellValue(chr(65+$col).$row, $v);
            $col++;
        }
    }

    //加上欄位框線
    $field_rows_count = count($field_rows);
    $end_field = chr(65+$field_rows_count-1);
    $objPHPExcel->getActiveSheet()->getStyle('A1:'.$end_field.$row)->getBorders()->getAllborders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

    //設定固定欄寬寬度值陣列
    if(is_array($rows_width) && $field_rows_count == count($rows_width)){
        $set_width_array = $rows_width;
        $i = 0;
        foreach(range('A',$end_field) as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($set_width_array[$i]);
            $i++;
        }
    }

    if(empty($file_name)){
        $file_name = date('YmdHis').'.xlsx';
    }else{
        $file_name = $file_name.date('YmdHis').'.xlsx';
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename='.$file_name);
    header('Cache-Control: max-age=0');
    $PHPExcelWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $PHPExcelWriter->save('php://output');
}

/*常用設定

//自斷換行
$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getAlignment()->setWrapText(true);

//水平與垂直
$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//字體FONT
$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);//紅色

//列高
$objPHPExcel->getActiveSheet(0)->getRowDimension('1')->setRowHeight(50);

//欄寬
$objPHPExcel->getActiveSheet(0)->getColumnDimension('A')->setWidth(10);

//框線
$objPHPExcel->getActiveSheet(0)->getStyle('A1:C16')->getBorders()->getAllborders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

*/