phpexcel导出

最新推荐文章于 2023-06-09 14:56:25 发布

TheSmallWell

最新推荐文章于 2023-06-09 14:56:25 发布

阅读量592

点赞数

本文链接：https://blog.csdn.net/qq_41991955/article/details/123379084

版权

$fileName = "亚马逊品类数据".date('Y-m-d');
$fileType = 'xlsx';
$sql = "select a.sku,b.product_typename,c.category_status,a.gender,a.sales_status,a.user_name,s.shop_name from amazon_skulist a left join amazon_category b on a.category_id=b.id left join amazon_category_status c on a.sales_status=c.id left join shop s on a.shop_id=s.Id where $where ORDER BY a.id desc";
$data = json_decode(json_encode(DB::select($sql)), true);
$obj = new \PHPExcel();

// 以下内容是excel文件的信息描述信息
$obj->getProperties()->setTitle('亚马逊商品品类信息'); //设置标题

// 设置当前sheet
$obj->setActiveSheetIndex(0);

// 设置当前sheet的名称
$obj->getActiveSheet()->setTitle('亚马逊商品品类信息');

// 列标
$list = ['A', 'B', 'C', 'D', 'E','F'];

// 填充第一行数据
$obj->getActiveSheet()
    ->setCellValue($list[0] . '1', '店铺')
    ->setCellValue($list[1] . '1', 'sku')
    ->setCellValue($list[2] . '1', '品类')
    ->setCellValue($list[3] . '1', '性别')
    ->setCellValue($list[4] . '1', '运营')
    ->setCellValue($list[5] . '1', '销售状态');


// 填充第n(n>=2, n∈N*)行数据
$length = count($data);
for ($i = 0; $i < $length; $i++) {
    $obj->getActiveSheet()->setCellValue($list[0] . ($i + 2), $data[$i]['shop_name'], \PHPExcel_Cell_DataType::TYPE_STRING);//将其设置为文本格式
    $obj->getActiveSheet()->setCellValue($list[1] . ($i + 2), $data[$i]['sku'],\PHPExcel_Cell_DataType::TYPE_STRING);
    $obj->getActiveSheet()->setCellValue($list[2] . ($i + 2), $data[$i]['product_typename'],\PHPExcel_Cell_DataType::TYPE_STRING);
    $obj->getActiveSheet()->setCellValue($list[3] . ($i + 2), $data[$i]['gender'],\PHPExcel_Cell_DataType::TYPE_STRING);
    $obj->getActiveSheet()->setCellValue($list[4] . ($i + 2), $data[$i]['user_name'],\PHPExcel_Cell_DataType::TYPE_STRING);
    $obj->getActiveSheet()->setCellValue($list[5] . ($i + 2), $data[$i]['category_status'],\PHPExcel_Cell_DataType::TYPE_STRING);
}

// 设置加粗和左对齐
foreach ($list as $col) {
    // 设置第一行加粗
    $obj->getActiveSheet()->getStyle($col . '1')->getFont()->setBold(true);
    // 设置第1-n行，左对齐
    for ($i = 1; $i <= $length + 1; $i++) {
        $obj->getActiveSheet()->getStyle($col . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    }
}

// 设置列宽
$obj->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$obj->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$obj->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$obj->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$obj->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$obj->getActiveSheet()->getColumnDimension('F')->setWidth(20);

// 导出
ob_clean();
if ($fileType == 'xls') {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $fileName . '.xls');
    header('Cache-Control: max-age=1');
    $objWriter = new \PHPExcel_Writer_Excel5($obj);
    $objWriter->save('php://output');
    exit;
} elseif ($fileType == 'xlsx') {
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
    header('Cache-Control: max-age=1');
    $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
    $objWriter->save('php://output');
    exit;
}