柳暗花明笔记

    excelphp 复制表格及修改数据

    所属分类:[ php&web ] 猫哲学家 发表于:2017-02-25 18:21 浏览:160

    // Generate new excel file
    require_once dirname(__DIR__) . '/PHPExcel/PHPExcel.php';

    $create_file_name = date('YmdHis') . '-' . rand(1000,9999) . '.xlsx';

    $load_excel_file = __DIR__ . '/example.xls';
    $save_excel_file = __DIR__ . "/tmp/{$create_file_name}";

    $excel = \PHPExcel_IOFactory::createReader('Excel5'); // Excel5 | Excel2007
    $excel = $excel->load($load_excel_file);

    $index = 0;
    foreach ($final_data_arr as $data)
    {
    $table_name = (string)$data['number'];

    if ($index == 0)
    {
    $excel->setActiveSheetIndex(0);
    $excel->getActiveSheet()
    ->setTitle($table_name)
    ;
    }
    else
    {
    $excel->setActiveSheetIndex(0);
    $clone_sheet = $excel->getActiveSheet()->copy();
    $clone_sheet->setTitle($table_name);

    $excel->addSheet($clone_sheet);
    $excel->setActiveSheetIndex($index);
    }

    $excel->getActiveSheet()
    ->setCellValue('C4', $post['sender']) // 发件人
    ->setCellValue('C8', $post['sender_phone']) // 发件人电话
    ->setCellValue('G8', "{$data['weight']}G") // 重量
    ->setCellValue('G6', $data['number']) // 发件人电话
    ->setCellValue('C10', "{$data['firstname']} {$data['lastname']}") // 收件公司
    ->setCellValue('G10', $data['phone']) // 收件人电话
    ->setCellValue('C12', $data['address_str']) // 地址
    ->setCellValue('C14', "{$data['firstname']} {$data['lastname']}") // 收件人
    ->setCellValue('H14', date('Y/m/d')) // 日期
    ->setCellValue('C16', $data['city']) // 城市
    ->setCellValue('E16', $data['postcode']) // 邮编
    ->setCellValue('H16', $data['country']) // 国家
    ->setCellValue('F20', $data['qty']) // 数量
    ->setCellValue('G20', $data['price']) // 单价
    ->setCellValue('I20', $data['amt']) // 金额
    ->setCellValue('G25', $data['amt']) // 合计
    ;

    $index++;
    }

    $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
    $objWriter->save($save_excel_file);

    clear_3_hour_excel_file();

    // Now download it
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename='.$create_file_name);
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($save_excel_file));
    readfile($save_excel_file);
    exit;

    标签: php 
    上一编文章 php 删除目录下3小时前的文件 下一编文章 php 递归读取目录下文件
    最近文章
    发表评论