xlsxwriter.class.php导出表时设置单元格格式

不惜年少枉少年

已于 2025-04-10 09:19:52 修改

阅读量446

点赞数

分类专栏： php 文章标签： php excel

于 2023-03-07 15:19:39 首次发布

本文链接：https://blog.csdn.net/weixin_39950258/article/details/129382776

版权

php 专栏收录该内容

2 篇文章

订阅专栏

文章展示了如何在使用XLSXWriter库时，通过修改writeSheetRow函数的循环来设置单元格的时间格式。通过检查style[num_format]，可以标准化不同的时间格式，并将其应用于工作表。示例代码中，创建了一个Excel文件并设置了时间列的格式为日期时间，使Excel能正确解析为时间格式。

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

找到writeSheetRow函数的循环

 foreach ($row as $v) {
            $number_format = $sheet->columns[$c]['number_format'];
            $number_format_type = $sheet->columns[$c]['number_format_type'];
            $cell_style_idx = empty($style) ? $sheet->columns[$c]['default_cell_style'] : $this->addCellStyle( $number_format, json_encode(isset($style[0]) ? $style[$c] : $style) );
            $this->writeCell($sheet->file_writer, $sheet->row_count, $c, $v, $number_format_type, $cell_style_idx);
            $c++;
}

替换为

    foreach ($row as $k=>$v) {
            if(isset($style['num_format'][$k])){
                $number_format = self::numberFormatStandardized($style['num_format'][$k]);
                $number_format_type =self::determineNumberFormatType($number_format);
            }else{
                $number_format = $sheet->columns[$c]['number_format'];
                $number_format_type = $sheet->columns[$c]['number_format_type'];
            }
            $cell_style_idx = empty($style) ? $sheet->columns[$c]['default_cell_style'] : $this->addCellStyle( $number_format, json_encode(isset($style[0]) ? $style[$c] : $style) );
            $this->writeCell($sheet->file_writer, $sheet->row_count, $c, $v, $number_format_type, $cell_style_idx);
            $c++;
}

下载测试

$writer = new \XLSXWriter();
$writer->writeSheetRow('sheet1',['时间'=>'2023-03-07 15:07:13'],[
            'num_format'=>['时间'=>'datetime']
]);
header('Content-disposition: attachment; filename="test-'.date('Ymd',time()).'.xlsx"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');
$writer->writeToStdOut();

类型就变为excel的时间格式

手动变为常规就是这样

格式参考

if      ($num_format=='string')   $num_format='@';
else if ($num_format=='integer')  $num_format='0';
else if ($num_format=='date')     $num_format='YYYY-MM-DD';
else if ($num_format=='datetime') $num_format='YYYY-MM-DD HH:MM:SS';
else if ($num_format=='time')     $num_format='HH:MM:SS';
else if ($num_format=='price')    $num_format='#,##0.00';
else if ($num_format=='dollar')   $num_format='[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00';
else if ($num_format=='euro')     $num_format='#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]';

$new_data =$new_data->toArray();
                require_once  app()->getRootPath()."extend/class/php_xlsxwriter/xlsxwriter.class.php";
                $writer = new \XLSXWriter();
                $sheet_name = 'Sheet1';//设置工作簿名称

                $header = ['名称','来源','sku','品牌','一级分类','二级分类'];
                $header2 = [];
                $widths = [] ;
                foreach ($header as $kkk=>$vvv){
                    $header2[$vvv] = 'string';
                    $widths[] = 26.38;
                }
      


                $writer->writeSheetHeader($sheet_name, $header2, ['suppress_row'=>true,'widths'=>$widths] );
//            $writer->markMergedCell($sheet_name, $start_row=0, $start_col=0, $end_row=0, $end_col=77);//合并单元格
//                $writer->writeSheetRow($sheet_name, ['订单列表'],
//                    ['font'=>'宋体','height'=>13.5,'font-size'=>10,'font-style'=>'bold','halign'=>'center','valign'=>'center',
//                        'border'       => 'left,right,top,bottom', # 边界 left, right, top, bottom, or multiple ie: 'top,left'
//                        'border-style' => 'thin',  # 边框样式 thin, medium, thick, dashDot, dashDotDot, dashed, dotted, double, hair, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot
//                        'border-color' => '#000',  # 边框颜色 #RRGGBB, ie: #ff99cc or #f9c
//                    ]);

                $styles1 = array(
                    'font'=>'宋体',
                    'height'=>16,
                    'font-size'=>10,
                    'font-style'=>'bold',
                    'fill'=>'#808080',
                    'color'=>'#FFFFFF',
                    'halign'=>'center',
                    'valign'=>'center',
                    'border'       => 'left,right,top,bottom', # 边界 left, right, top, bottom, or multiple ie: 'top,left'
                    'border-style' => 'thin',  # 边框样式 thin, medium, thick, dashDot, dashDotDot, dashed, dotted, double, hair, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot
                    'border-color' => '#000',  # 边框颜色 #RRGGBB, ie: #ff99cc or #f9c
                );//设置样式
                $writer->writeSheetRow($sheet_name,$header,$styles1);//写入数据
                //数据
                if(!empty($new_data)){
                    foreach ($new_data as $val) {
                        $writer->writeSheetRow($sheet_name,$val,[
                            'number_format'=>'datetime',
                            'font'=>'Arial',
                            'height'=>16,
                            'font-size'=>10,
                            'border'       => 'left,right,top,bottom', # 边界 left, right, top, bottom, or multiple ie: 'top,left'
                            'border-style' => 'thin',  # 边框样式 thin, medium, thick, dashDot, dashDotDot, dashed, dotted, double, hair, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot
                            'border-color' => '#000',  # 边框颜色 #RRGGBB, ie: #ff99cc or #f9c
                            'halign'=>'center',
                            'valign'=>'center',
                            'num_format'=>[
                                '下单时间'=>'datetime', '发货时间'=>'datetime', '签收时间'=>'datetime',
                            ]]);//写入数据
                    }
                }
                header('Content-disposition: attachment; filename="商品'.date('YmdHis',time()).'.xlsx"');
                header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                header('Content-Transfer-Encoding: binary');
                header('Cache-Control: must-revalidate');
                header('Pragma: public');
                $writer->writeToStdOut();die;//输出
            }else{
                $this->error('无数据');
            }