关于fastadmin后台导入和导出，订单包含多商品订单的excel表格（合并单元格）和二维码图片导出

导入的excel表格格式

 

导出excel表格效果

 

 

 

导出和导入的html代码

                        <a href="javascript:;" class="btn btn-info btn-import {:$auth->check('order/order/import')?'':'hide'}"  title="{:__('导入订单')}" id="btn-import-file" data-mimetype="csv,xls,xlsx" data-multiple="false"><i class="fa fa-upload"></i> {:__('导入订单')}</a>
                        <a href="/order_template.xlsx" class="btn {:$auth->check('order/order/import')?'':'hide'}"  title="{:__('订单模板下载')}"><i class="fa fa-download"></i> {:__('订单模板下载')}</a>
                        
                        <a href="javascript:;" class="btn btn-success btn-export"  title="{:__('导出订单')}" id="btn-export-file"><i class="fa fa-download"></i> {:__('导出订单')}</a>

导出和导入的js代码

 // 初始化表格参数配置
            Table.api.init({
                extend: {
                    index_url: 'order/order/index' + location.search,
                    add_url: 'order/order/add',
                    edit_url: 'order/order/edit',
                    del_url: 'order/order/del',
                    multi_url: 'order/order/multi',
                    import_url: 'order/order/import',
                    table: 'delivery_order',
                }
            });

            var table = $("#table");
            
            //导出
            $(document).on("click", ".btn-export", function () {
                var ids = Table.api.selectedids(table);
                var page = table.bootstrapTable('getData');
                var all = table.bootstrapTable('getOptions').totalRows;
                console.log(ids, page, all);
                Layer.confirm("请选择导出的选项<form action='" + Fast.api.fixurl("order/order/export") + "' method='post' target='_blank'><input type='hidden' name='ids' value='' /><input type='hidden' name='filter' ><input type='hidden' name='op'><input type='hidden' name='search'><input type='hidden' name='columns'></form>", {
                    title: '导出数据',
                    btn: ["选中项(" + ids.length + "条)", "本页(" + page.length + "条)", "全部(" + all + "条)"],
                    success: function (layero, index) {
                        $(".layui-layer-btn a", layero).addClass("layui-layer-btn0");
                    }
                    , yes: function (index, layero) {
                        submitForm(ids.join(","), layero);
                        return false;
                    }
                    ,
                    btn2: function (index, layero) {
                        var ids = [];
                        $.each(page, function (i, j) {
                            ids.push(j.id);
                        });
                        submitForm(ids.join(","), layero);
                        return false;
                    }
                    ,
                    btn3: function (index, layero) {
                        submitForm("all", layero);
                        return false;
                    }
                })
            });
            var submitForm = function (ids, layero) {
                var options = table.bootstrapTable('getOptions');
                console.log(options);
                var columns = [];
                $.each(options.columns[0], function (i, j) {
                    if (j.field && !j.checkbox && j.visible && j.field != 'operate') {
                        columns.push(j.field);
                    }
                });
                var search = options.queryParams({});
                $("input[name=search]", layero).val(options.searchText);
                $("input[name=ids]", layero).val(ids);
                $("input[name=filter]", layero).val(search.filter);
                $("input[name=op]", layero).val(search.op);
                $("input[name=columns]", layero).val(columns.join(','));
                $("form", layero).submit();
            };

导入和导出的php代码

namespace app\admin\controller\order;

use app\common\controller\Backend;
use app\admin\model\Order\Goods;
use think\Db;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
//use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

use PhpOffice\PhpSpreadsheet\Shared\Date;
use app\admin\model\Platform\Warehouse;


class Order extends Backend
{

    /**
     * Order模型对象
     * @var \app\admin\model\Order\Order
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\Order\Order;
        $this->view->assign("statusList", $this->model->getStatusList());
    }



    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */


    /**
     * 查看
     */
    public function index()
    {
        //当前是否为关联查询
        $this->relationSearch = true;
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $list = $this->model
                    ->with(["warehouse"])
                    ->where($where)
                    ->order($sort, $order)
                    ->paginate($limit);

            foreach ($list as $row) {
                // $row->visible(['id','warehouse_id','goods_owner','goods_owner_mobile','store_name','delivery_company','delivery_number_first','delivery_number_second','accept_time','receipt_address','receipt_name','receipt_mobile','pick_name','check_name','goods_number','goods_weight','remark','expect_date','status','createtime','driver_id','qrcode_image']);
                $row->getRelation('warehouse')->visible(['name']);
                
            }

            $result = array("total" => $list->total(), "rows" => $list->items());

            return json($result);
        }
        return $this->view->fetch();
    }
    
        /**
     * 导入Excel数据
     */
    public function import()
    {
        $file = $this->request->param('file');
        if (!$file) {
            $this->error('请选择要上传的文件');
        }
        
        try {
            // 读取Excel文件
            $filePath = ROOT_PATH . 'public' . $file;
            if (!file_exists($filePath)) {
                $this->error('文件不存在');
            }
            
            // 使用PhpSpreadsheet读取文件
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($filePath);
            $spreadsheet = $reader->load($filePath);
            $sheet = $spreadsheet->getSheet(0);
            
            $data = $sheet->toArray();
            
            // 移除表头
            array_shift($data);
            
            // 开始事务
            Db::startTrans();
            
            $currentOrder = null;
            $successOrderCount = 0;
            $successGoodsCount = 0;
            
            foreach ($data as $row) {
                // 跳过空行
                if (empty(array_filter($row))) {
                    continue;
                }
                
                // 处理物流订单信息（当有物流单号时创建新订单）
                if (!empty($row[4])) { // E列：物流单号(主)
                    $delivery_number_first = trim($row[5]);
                    
                    //查询仓库是否匹配
                    $exist_warehouse = Warehouse::where(['name'=>$row[0]])->find();
                    if($exist_warehouse) {
                        $warehouse_id = $exist_warehouse['id'];
                    } else {
                        $this->error('仓库-'.$row[0].'未匹配到!');
                    }
                    // 检查是否已存在
                    $order = $this->model->where('delivery_number_first', $delivery_number_first)->find();
                    if (!$order) {
                        
                        //$qrcode = 'https://www.shiwangdaoju.cn?scode='.'sw'.uniqid();
                        
                        $qrcode = $delivery_number_first;
                        //生成二维码
                        $qrcode_image = $this->erCode($qrcode);

                        $orderData = [
                            'warehouse_id' => $warehouse_id??'',
                            'goods_owner' => trim($row[1] ?? ''),
                            'goods_owner_mobile' => trim($row[2] ?? ''),
                            'store_name' => trim($row[3] ?? ''),
                            'delivery_company' => trim($row[4] ?? ''),
                            'delivery_number_first' => $delivery_number_first,
                            'delivery_number_second' => trim($row[6] ?? ''),
                            'accept_time' => $this->parseDateTime($row[7] ?? ''),
                            'receipt_address' => trim($row[8] ?? ''),
                            'receipt_name' => trim($row[9] ?? ''),
                            'receipt_mobile' => trim($row[10] ?? ''),
                            'pick_name' => trim($row[11] ?? ''),
                            'check_name' => trim($row[12] ?? ''),
                            'goods_number' => intval($row[13] ?? 0),
                            'goods_weight' => floatval($row[14] ?? 0),
                            'delivery_type' => trim($row[15] ?? ''),
                            'batch_type' => trim($row[16] ?? ''),
                            'remark' => trim($row[17] ?? ''),
                            'expect_date' => $this->parseDateTime($row[18] ?? ''),
                            'qrcode_image' => $qrcode_image,
                            'createtime' => time()
                        ];
                        
                        $order = $this->model->create($orderData);
                        
                        $successOrderCount++;
                    } else {
                        continue;
                    }
                    
                    $currentOrder = $order;
                }
                
                // 处理商品信息（当有条码和产品名称时）
                if ($currentOrder && !empty($row[20]) && !empty($row[21])) {
                    $goodsData = [
                        'order_id' => $currentOrder->id,
                        'buy_number' => trim($row[19] ?? ''),
                        'bar_code' => trim($row[20] ?? ''),
                        'goods_name' => trim($row[21] ?? ''),
                        'number' => intval($row[22] ?? 0),
                        'goods_model' => trim($row[23] ?? ''),
                        'goods_unit' => trim($row[24] ?? ''),
                        'produce_date' => $this->parseDateTime($row[25] ?? ''),
                        'package_type' => trim($row[26] ?? ''),
                        'box_spec' => trim($row[27] ?? ''),
                        'box_number' => intval($row[28] ?? 0),
                        'goods_location' => trim($row[29] ?? ''),
                        'goods_batch' => trim($row[30] ?? ''),
                        'total_size' => floatval($row[31] ?? 0),
                        'total_gross_weight' => floatval($row[32] ?? 0),
                        'createtime' => time()
                    ];
                    
                    Goods::create($goodsData);
                    $successGoodsCount++;
                }
            }
            
            // 提交事务
            Db::commit();

            //$this->success("导入成功，共导入 {$successOrderCount} 个订单，{$successGoodsCount} 个商品");
            
        } catch (\Exception $e) {
            // 回滚事务
            Db::rollback();
            
            $this->error('导入失败：' . $e->getMessage());
        }
        
        $this->success("导入成功，共导入 {$successOrderCount} 个订单，{$successGoodsCount} 个商品");
    }
    
    /*
     * 生成二维码
     */
    public function erCode($qrcode)
    {

        Vendor('phpqrcode.phpqrcode');//加载类库
        $QRcode = new \QRcode();//实例化对象


        $url = config('site.api_url');
   
        $value = $qrcode;                                        //二维码内容
        $errorCorrectionLevel = 'H';        //容错级别
        $matrixPointSize = 6;                        //生成图片大小
        //生成二维码图片
        $filename="";
        if(empty($filename))
        {
            $filename = time().rand(10000,99999);
        }
        $root = ROOT_PATH;

        $QR = $root . 'public/qr/' . $filename . '.png';

        $qrCode = $QRcode::png($value,$QR , $errorCorrectionLevel, $matrixPointSize, 2);

        //输出图片
        $filename = '/qr/' .$filename . '.png';

        return $filename;
  
    }
    
    /**
     * 解析日期时间
     */
    private function parseDateTime($dateTimeString)
    {
        if (empty($dateTimeString)) {
            return null;
        }
        
        // 如果是 Excel 日期序列号
        if (is_numeric($dateTimeString)) {
            return \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($dateTimeString)->format('Y-m-d H:i:s');
        }
        
        // 如果是字符串日期
        $timestamp = strtotime($dateTimeString);
        if ($timestamp !== false) {
            return date('Y-m-d H:i:s', $timestamp);
        }
        
        return null;
    }
    
    
    
        /**
     * 导出订单
     */
    public function export()
    {
        set_time_limit(0);
        $search = $this->request->post('search');
        $ids = $this->request->post('ids');
        $filter = $this->request->post('filter');
        $op = $this->request->post('op');
        
        $this->request->get(['search' => $search, 'ids' => $ids, 'filter' => $filter, 'op' => $op]);
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();
        
        
        if (empty($ids)) {
            $this->error('请选择要导出的订单');
        }
        
        if($ids == 'all') {
            // 查询订单数据
            $orderList = Db::name('delivery_order')
                ->where($where)
                ->select();
        } else {
            $idArr = explode(',', $ids);
            
            // 查询订单数据
            $orderList = Db::name('delivery_order')
            ->where($where)
            ->where('id', 'in', $idArr)
            ->select();
        }
        
        if (empty($orderList)) {
            $this->error('未找到订单数据');
        }

        // 查询商品数据
        $orderIds = array_column($orderList, 'id');
        $goodsList = Db::name('delivery_goods')
            ->where('order_id', 'in', $orderIds)
            ->select();
            
        // 按订单ID分组商品
        $goodsGroup = [];
        foreach ($goodsList as $goods) {
            $goodsGroup[$goods['order_id']][] = $goods;
        }

        try {
            // 创建Spreadsheet对象
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('Sheet1');

            // 设置表头
            $this->setHeaders($sheet);

            // 填充数据并记录合并信息
            $row = 2; // 从第2行开始（第1行是表头）
            $mergeInfo = []; // 记录需要合并的单元格信息
            
            foreach ($orderList as $order) {
                $orderGoods = isset($goodsGroup[$order['id']]) ? $goodsGroup[$order['id']] : [];
                $goodsCount = count($orderGoods);
                
                // 如果没有商品数据，创建一行空商品
                if (empty($orderGoods)) {
                    $goodsCount = 1;
                    $orderGoods = [null];
                }

                $startRow = $row;
                
                foreach ($orderGoods as $index => $goods) {
                    $this->fillOrderRow($sheet, $row, $order, $goods, $index === 0);
                    $row++;
                }
                
                $endRow = $row - 1;
                
                // 记录合并信息（如果商品数量大于1才需要合并）
                if ($goodsCount > 1) {
                    $mergeInfo[] = [
                        'startRow' => $startRow,
                        'endRow' => $endRow,
                        'order' => $order
                    ];
                }
            }

            // 合并单元格
            $this->mergeOrderCells($sheet, $mergeInfo);

            // 设置样式
            $this->setStyles($sheet, $row - 1);

            // 输出文件
            $filename = '订单导出_' . date('YmdHis') . '.xlsx';
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');

            $writer = new Xlsx($spreadsheet);
            $writer->save('php://output');
            exit;

        } catch (\Exception $e) {
            $this->error('导出失败：' . $e->getMessage());
        }
    }

    /**
     * 设置表头
     */
    private function setHeaders($sheet)
    {
        $headers = [
            'A' => '仓库',
            'B' => '货主全称',
            'C' => '货主手机号',
            'D' => '店铺名称',
            'E' => '物流公司',
            'F' => '物流单号(主)',
            'G' => '物流单号(次)',
            'H' => '接单时间',
            'I' => '收件人地址',
            'J' => '收件人姓名',
            'K' => '收件人手机号',
            'L' => '拣货人',
            'M' => '复核人',
            'N' => '发货箱数',
            'O' => '预估重量',
            'P' => '装货方式',
            'Q' => '批次类型',
            'R' => '客服备注',
            'S' => '预计送达日期',
            'T' => '订单状态',
            'U' => '二维码',
            'V' => '采购单号',
            'W' => '条码',
            'X' => '产品名称',
            'Y' => '数量',
            'Z' => '型号',
            'AA' => '单位',
            'AB' => '生产日期',
            'AC' => '包装方式',
            'AD' => '箱规',
            'AE' => '箱数',
            'AF' => '货位',
            'AG' => '批次',
            'AH' => '总体积',
            'AI' => '总毛重'
        ];

        foreach ($headers as $col => $header) {
            $sheet->setCellValue($col . '1', $header);
        }

        // 设置列宽
        $widths = [
            'A' => 10, 'B' => 15, 'C' => 15, 'D' => 15, 'E' => 15,
            'F' => 20, 'G' => 15, 'H' => 18, 'I' => 30, 'J' => 12,
            'K' => 15, 'L' => 10, 'M' => 10, 'N' => 10, 'O' => 10,
            'P' => 10, 'Q' => 10, 'R' => 20, 'S' => 15, 'T' => 10,
            'U' => 15, 'V' => 15, 'W' => 15, 'X' => 20, 'Y' => 10,
            'Z' => 10, 'AA' => 8, 'AB' => 15, 'AC' => 10, 'AD' => 10,
            'AE' => 10, 'AF' => 10, 'AG' => 12, 'AH' => 12, 'AI' => 12
        ];

        foreach ($widths as $col => $width) {
            $sheet->getColumnDimension($col)->setWidth($width);
        }
    }

    /**
     * 填充订单行数据
     */
    private function fillOrderRow($sheet, $row, $order, $goods = null, $isFirstRow = true)
    {
        // 订单基本信息（只在第一行显示）
        if ($isFirstRow) {
            $sheet->setCellValue('A' . $row, $this->getWarehouseName($order['warehouse_id']));
            $sheet->setCellValue('B' . $row, $order['goods_owner']);
            $sheet->setCellValue('C' . $row, $order['goods_owner_mobile']);
            $sheet->setCellValue('D' . $row, $order['store_name']);
            $sheet->setCellValue('E' . $row, $order['delivery_company']);
            $sheet->setCellValue('F' . $row, $order['delivery_number_first']);
            $sheet->setCellValue('G' . $row, $order['delivery_number_second']);
            $sheet->setCellValue('H' . $row, $order['accept_time']);
            $sheet->setCellValue('I' . $row, $order['receipt_address']);
            $sheet->setCellValue('J' . $row, $order['receipt_name']);
            $sheet->setCellValue('K' . $row, $order['receipt_mobile']);
            $sheet->setCellValue('L' . $row, $order['pick_name']);
            $sheet->setCellValue('M' . $row, $order['check_name']);
            $sheet->setCellValue('N' . $row, $order['goods_number']);
            $sheet->setCellValue('O' . $row, $order['goods_weight']);
            $sheet->setCellValue('P' . $row, $order['delivery_type']);
            $sheet->setCellValue('Q' . $row, $order['batch_type']);
            $sheet->setCellValue('R' . $row, $order['remark']);
            $sheet->setCellValue('S' . $row, $order['expect_date']);
            $sheet->setCellValue('T' . $row, $this->getStatusText($order['status']));
            
            // 插入二维码图片
            $this->insertQrCode($sheet, $order['qrcode_image'], 'U' . $row);
        } else {
            // 非第一行，清空订单信息单元格（为合并做准备）
            $orderColumns = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U'];
            foreach ($orderColumns as $col) {
                $sheet->setCellValue($col . $row, '');
            }
        }

        // 商品信息
        if ($goods) {
            $sheet->setCellValue('V' . $row, $goods['buy_number']);
            $sheet->setCellValue('W' . $row, "\t".$goods['bar_code']."\t");
            $sheet->setCellValue('X' . $row, $goods['goods_name']);
            $sheet->setCellValueExplicit('Y' . $row, $goods['number'], DataType::TYPE_NUMERIC);
            $sheet->setCellValue('Z' . $row, $goods['goods_model']);
            $sheet->setCellValue('AA' . $row, $goods['goods_unit']);
            $sheet->setCellValue('AB' . $row, $goods['produce_date']);
            $sheet->setCellValue('AC' . $row, $goods['package_type']);
            $sheet->setCellValue('AD' . $row, $goods['box_spec']);
            $sheet->setCellValue('AE' . $row, $goods['box_number']);
            $sheet->setCellValue('AF' . $row, $goods['goods_location']);
            $sheet->setCellValue('AG' . $row, $goods['goods_batch']);
            $sheet->setCellValue('AH' . $row, $goods['total_size']);
            $sheet->setCellValue('AI' . $row, $goods['total_gross_weight']);
        }
    }

    /**
     * 合并订单信息单元格
     */
    private function mergeOrderCells($sheet, $mergeInfo)
    {
        foreach ($mergeInfo as $info) {
            $startRow = $info['startRow'];
            $endRow = $info['endRow'];
            
            // 需要合并的订单信息列（从A到U）
            $mergeColumns = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U'];
            
            foreach ($mergeColumns as $col) {
                // 只有当起始行和结束行不同时才需要合并
                if ($startRow < $endRow) {
                    $sheet->mergeCells($col . $startRow . ':' . $col . $endRow);
                    
                    // 设置合并后单元格的垂直居中
                    $sheet->getStyle($col . $startRow . ':' . $col . $endRow)
                          ->getAlignment()
                          ->setVertical(Alignment::VERTICAL_CENTER);
                }
            }
            
            // 设置合并区域的行高（根据商品数量调整）
            $rowCount = $endRow - $startRow + 1;
            $baseHeight = 25; // 基础行高
            $minHeight = 60; // 最小行高（确保二维码能显示）
            
            for ($i = $startRow; $i <= $endRow; $i++) {
                $currentHeight = max($baseHeight * $rowCount, $minHeight);
                $sheet->getRowDimension($i)->setRowHeight($currentHeight);
            }
        }
    }

    /**
     * 插入二维码图片
     */
    private function insertQrCode($sheet, $qrcodePath, $cell)
    {
        if (empty($qrcodePath)) {
            return;
        }

        // 构建完整的图片路径
        $imagePath = ROOT_PATH . 'public' . $qrcodePath;
        
        if (!file_exists($imagePath)) {
            // 如果图片不存在，尝试使用默认路径
            $imagePath = ROOT_PATH . 'public/uploads/' . $qrcodePath;
            if (!file_exists($imagePath)) {
                return;
            }
        }

        try {
            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $drawing->setName('QRCode');
            $drawing->setDescription('QR Code');
            $drawing->setPath($imagePath);
            $drawing->setCoordinates($cell);
            $drawing->setOffsetX(5);
            $drawing->setOffsetY(5);
            $drawing->setWidth(50);
            $drawing->setHeight(50);
            $drawing->setWorksheet($sheet);

            // 设置行高以适应图片
            $row = (int) filter_var($cell, FILTER_SANITIZE_NUMBER_INT);
            $sheet->getRowDimension($row)->setRowHeight(60);
        } catch (\Exception $e) {
            // 图片插入失败，记录日志但不中断导出
            \think\Log::error('二维码图片插入失败：' . $e->getMessage());
        }
    }

    /**
     * 设置表格样式
     */
    private function setStyles($sheet, $lastRow)
    {
        // 设置表头样式
        $headerStyle = [
            'font' => [
                'bold' => true,
                'color' => ['rgb' => 'FFFFFF']
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'color' => ['rgb' => '4472C4']
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];
        $sheet->getStyle('A1:AI1')->applyFromArray($headerStyle);

        // 设置数据区域样式
        $dataStyle = [
            'alignment' => [
                'vertical' => Alignment::VERTICAL_CENTER
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => 'DDDDDD']
                ]
            ]
        ];
        
        if ($lastRow >= 2) {
            $sheet->getStyle('A2:AI' . $lastRow)->applyFromArray($dataStyle);
        }

        // 设置自动换行和居中对齐
        $sheet->getStyle('I2:I' . $lastRow)->getAlignment()->setWrapText(true);
        $sheet->getStyle('R2:R' . $lastRow)->getAlignment()->setWrapText(true);
        
        // 设置订单信息列的水平居中
        $orderColumns = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'S', 'T'];
        foreach ($orderColumns as $col) {
            $sheet->getStyle($col . '2:' . $col . $lastRow)
                  ->getAlignment()
                  ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        }
    }

    /**
     * 获取仓库名称
     */
    private function getWarehouseName($warehouseId)
    {
        $warehouses = Warehouse::where(['deletetime'=>null])->column('name','id');
        
        return isset($warehouses[$warehouseId]) ? $warehouses[$warehouseId] : '未知仓库';
    }

    /**
     * 获取状态文本
     */
    private function getStatusText($status)
    {
        $statusMap = [
            0 => '备货完成',
            1 => '已交接',
            2 => '运输中',
            3 => '已签收'
        ];
        
        return isset($statusMap[$status]) ? $statusMap[$status] : '未知状态';
    }