php生成excel文件

Ezra
2023-03-16 / 0 评论 / 87 阅读 / 正在检测是否收录...
/**
 * Notes:导出数据库数据为excel,返回文件路由
 * @param $list 数据
 * @param $name 文件名称
 * @param $fields 表头,与$list对应好
 */
function exportUrl($list, $name, $fields)
{
    set_time_limit(0);
    $excel_title = [];

    if ($list) {
        $keys = array_keys($list[0]);
        foreach ($fields as $key => $field) {
            if (in_array($key, $keys)) {
                $excel_title[] = $field;
            }
        }
    } else {            //没有数据时导出表头
        foreach ($fields as $k => $v) {
            $excel_title[] = $v;
        }
    }

    vendor("PHPExcel.PHPExcel");
    $objPHPExcel = new \PHPExcel();

    $objPHPExcel->getProperties()->setCreator("ctos")
        ->setLastModifiedBy("ctos")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");


    $lie1 = [];
    $last = 'A';
    for ($i = 1; $i <= count($excel_title); $i++) {
        if (array_search($excel_title[$i - 1], $fields)) {
            $lie1[array_search($excel_title[$i - 1], $fields)] = numCovertLetter($i);
            $result                                            = numCovertLetter($i) . '1';
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($result, $excel_title[$i - 1]);
            $last = numCovertLetter($i);
        }
    }

    // 水平居中
    $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    // 垂直居中
    $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

    //设置行宽度
    $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
//    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);

    //设置行高度
    $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);

    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
    $objPHPExcel->getActiveSheet()->getStyle('A1:' . $last . '1')->getFont()->setBold(true);

    for ($j = 0; $j < count($list); $j++) {
        foreach ($lie1 as $key => $item) {
            $objPHPExcel->getActiveSheet(0)->setCellValue($item . ($j + 2), $list[$j][$key]);
        }
    }

    // 设置单元格换行
    $objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(True);

    //边框线
    $style_array = array(
        'borders' => array(
            'allborders' => array(
                'style' => \PHPExcel_Style_Border::BORDER_THIN
            )
        ));
    $objPHPExcel->getActiveSheet()->getStyle('A2:' . $last . (count($list) + 1))->applyFromArray($style_array);

    $dir = '../public/tableData/excel/' . date('Ymd');
    if (!is_dir($dir)) {
        mkdir($dir, 0777, true);
    }
    $fielName  = $name . '_' . date('YmdHis') . mt_rand(1111, 9999);
    $url       = $dir . '/' . $fielName . '.xlsx';
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save($url);
    $new_url = substr($url, 9);
    $data    = [
        //'url'  => 'http://' . $_SERVER['SERVER_NAME'] . ":" . $_SERVER['SERVER_PORT'] . $new_url,
        'url'  => $new_url,
        'name' => $fielName . '.xlsx'
    ];
    return setReturn(200, '', $data);
}
0

评论 (0)

取消