PHPExcel制作表格并且通过浏览器下载

最新推荐文章于 2023-10-19 09:34:55 发布

原创最新推荐文章于 2023-10-19 09:34:55 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#PHPExcel #php表格

PHP 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用 PHPExcel 库创建带有复杂样式的 Excel 文件。包括设置文档属性、定义单元格样式、自动调整列宽及填充数据等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
    die($cacheMethod . " 缓存方法不可用" . '<br>');
}
$objPHPExcel->getProperties()->setCreator("dcb3688")
    ->setLastModifiedBy("dcb3688")
    ->setTitle("xxxxxxxx新增用户")
    ->setSubject("xxxxx新增用户")
    ->setDescription("xxxxx新增用户")
    ->setKeywords("office 2007  php")
    ->setCategory("xxxxxxxx");

#设置表头单元格样式
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(30);//设置默认行高
$objPHPExcel->getActiveSheet()->getStyle("A1:H1")->getFont()->setSize(20)->setBold(true);//设置第一行字体大小和加粗
$objPHPExcel->getActiveSheet()->getStyle("B2:H2")->getFont()->setSize(14)->setBold(true);//设置第一行字体大小和加粗
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中
$objPHPExcel->getActiveSheet()->setCellValue('A1', '下载统计');
$objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(50);//设置第一行行高
$objPHPExcel->getActiveSheet()->getStyle("B2:H2")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getStyle("A2")->getFont()->setSize(16)->setBold(true);//设置第A2行字体大小和加粗
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('A2', "xxxxxxxx)");
$objPHPExcel->getActiveSheet()->setCellValue('B2', "xxxxxXXXX（单纯\nxxxxxxx，Xxxxx）xxxxxxxxxx");
$objPHPExcel->getActiveSheet()->setCellValue('C2', "xxxxxXXXX＋XXXXXXXXXX\n没开通过院外服务（新增）");
$objPHPExcel->getActiveSheet()->setCellValue('D2', "xxxxxxXXXX＋开通过院外服务（新增）");
$objPHPExcel->getActiveSheet()->setCellValue('E2', "xxxxxxxxxx（xxxxxxxx\n－xxxxxxxxxxxx）");
$objPHPExcel->getActiveSheet()->setCellValue('F2', "后台生成的用户（新增）（后台生成的\nxxxxxxxxxxxx）");
$objPHPExcel->getActiveSheet()->setCellValue('G2', "xxxxxxxxxxxxxxxxxxxxxx");
$objPHPExcel->getActiveSheet()->setCellValue('H2', "xxxxxxxxxxxxxxxxx\nxxxxxxxxxxxxxxxx");
$objPHPExcel->getActiveSheet()->getStyle('A2:H2')->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));

/**
 * 单元格宽度
 */
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);

//以该model=1为例
$model = 1;
if (!empty($model)) {
    //foreach ($model as $key => $value) {
       /* $Cellkey = $key + 2;
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $Cellkey, $value['id']);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $Cellkey, $value['user_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $Cellkey, xcrypt::decrypt($value['username']));
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $Cellkey, $value['hospital_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $Cellkey, xcrypt::decrypt($value['mobile']));
        $objPHPExcel->getActiveSheet()->setCellValue('F' . $Cellkey, $value['name']);
        $objPHPExcel->getActiveSheet()->setCellValue('G' . $Cellkey, $value['province']);
        $objPHPExcel->getActiveSheet()->setCellValue('H' . $Cellkey, $value['foregift']);*/
    $Cellkey = $key + 3;
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $Cellkey, 1);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $Cellkey, 2);
    $objPHPExcel->getActiveSheet()->setCellValue('C' . $Cellkey, 3);
    $objPHPExcel->getActiveSheet()->setCellValue('D' . $Cellkey, 4);
    $objPHPExcel->getActiveSheet()->setCellValue('E' . $Cellkey, 5);
    $objPHPExcel->getActiveSheet()->setCellValue('F' . $Cellkey, 6);
    $objPHPExcel->getActiveSheet()->setCellValue('G' . $Cellkey, 7);
    $objPHPExcel->getActiveSheet()->setCellValue('H' . $Cellkey, 8);
   // }
} else {
    die(" 暂无数据" . PHP_EOL);
}

#######################################################################################################

$objPHPExcel->getActiveSheet()->setTitle('下载统计');
/*生成文件并下载*/
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.'data.xlsx"'); 
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

#释放内存
unset($objWriter);
unset($objPHPExcel);