PHP导出Excel方法大全

导出EXCEL方法一

#xmlns即是xml的命名空间

$str = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\nxmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\nxmlns=\"http://www.w3.org/TR/REC-html40\">\r\n<head>\r\n<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">\r\n</head>\r\n<body>";
#以下构建一个html类型格式的表格
$str .= $title;
$str .= "<table border=1>";
foreach ($data as $key => $rt) {
   
    $str .= "<tr>";
    foreach ($rt as $k => $v) {
   
        $str .= "<td>{
     $v}</td>";
    }
    $str .= "</tr>\n";
}
$str .= "</table></body></html>";
header("Content-Type: application/vnd.ms-excel; name='excel'");   #类型
header("Content-type: application/octet-stream");     #告诉浏览器响应的对象的类型(字节流、浏览器默认使用下载方式处理)
header("Content-Disposition: attachment; filename=" . $filename);   #不打开此文件,刺激浏览器弹出下载窗口、下载文件默认命名
header("Cache-Control: must-revalidate, post-check=0, pre-check=0,max-age=0");
header("Pragma: no-cache");   #保证不被缓存或者说保证获取的是最新的数据
header("Expires: 0");
exit($str);

导出EXCEL方法二

导出excel并生成柱状图

        $objPHPExcel = new PHPExcel();
        $objSheet = $objPHPExcel->getActiveSheet();
        $objSheet->fromArray($data);

        $labels = array(new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$28',null,1),);//条数图例
        $xLabels = array(new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$C$1:$I$1',null,7),);//取x轴刻度
        $datas = array(new \PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$C$6:$I$6',null,7),);//取数据
        $series = array(new \PHPExcel_Chart_DataSeries(\PHPExcel_Chart_DataSeries::TYPE_BARCHART,//选择图表形式,柱状图,还可选择其他 折线图 饼图
           \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,
            range(0, count($datas)-1),
            $labels,
            $xLabels,
            $datas
        )
        );
        $layout=new \PHPExcel_Chart_Layout();
        $layout->setShowVal(true);
        $areas = new \PHPExcel_Chart_PlotArea($layout,$series);
        $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT,$layout,false);
//        $title = new \PHPExcel_Chart_Title("目标标识统计aaa");
        $title = null;
//        $ytitle = new \PHPExcel_Chart_Title("条数bbb");
        $ytitle = null;
        $chart = new \PHPExcel_Chart('line_chart',$title,$legend,$areas,true,false,null,$ytitle);
        $chart->setTopLeftPosition("A32")->setBottomRightPosition("K50"); //图表位置

        $objSheet->addChart($chart);

        $excel = 'Excel2007';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,$excel);
        $objWriter->setIncludeCharts(true); //图表必须加此行
        ob_clean();
        ob_end_clean();
//        ob_start();
//        flush();
        Header("Content-type: application/octet-stream;charset=utf-8");
        header("Content-Type: application/vnd.ms-excel; name='excel'");   #类型
        header("Content-Disposition: attachment; filename=" . $filename);   #不打开此文件,刺激浏览器弹出下载窗口、下载文件默认命名
        header('Cache-Control: max-age=0');
        $filePath = dirname(__FILE__) . rand(0, getrandmax()) . rand(0, getrandmax()) . ".xls";
        $objWriter->save($filePath);
        readfile($filePath);//???
        unlink($filePath);

导出EXCEL方法三

路由 router


$router->get('export_daily',  HangController::class.'@export_daily'); //导出
 

控制器controller

/**
     * 导出日报
     * 导出EXCEL方法三
     * @param Request $request
     * @return mixed
     */
    public function export_daily(Request $request){
   
  ob_end_clean();
        ob_start();
        $filename = $hang_date.'班组日产量汇总报表';
        return Excel::download(new HangDailyExport($data,$fact_name,$hang_date,$user_name), $filename.'.xlsx');
    }

视图

{
   {
   -- 导出 --}}
      <div >
           <a class="btn btn-primary" onclick="goToExports()" style="margin-right: 5px" >导出日产量汇总报表</a>
      </div>
                                        
<script>
/**
     * 导出表
     */
    function goToExports() {
   
        var hang_date = $('.hang_date').val();
        var fact_id = $('.factory').val();
        //var fact_name = $(".factory option:selected").text();
        window.open("/admin/hang/export_daily?fact_id="+fact_id+'&hang_date='+hang_date);
    }
</script>

HangDailyExport.php

<?php
namespace App\Exports;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;
class HangDailyExport implements FromView
{
   
    public function __construct($data,$fact_name,$hang_date,$user_name)
    {
   
        $this->data = $data;
        $this->fact_name = $fact_name;
        $this->hang_date = $hang_date;
        $this->user_name = $user_name;
    }
    public function width(): view{
   

    }
    public function view(): View
    {
   
        $arr_group = [];
        $arr_avg_salary = [];
        for ($i=2;$i<count($this->data[0])-1;$i++){
   
            $arr_group[$i-2] = $this->data[0][$i];  //柱状图X轴
            $arr_avg_salary[$i-2] = $this->data[27][$i];  //柱状图Y轴
        }
        $selects =[];
        //第一行
        $selects['fact_name'] = $this->fact_name; //工厂名称
        $selects['hang_date'] = $this->hang_date; //日期
        $selects['depart'] = '工业工程IE部'; //部门
        $selects['user_name'] = $this->user_name; //部门
        //第二行
        return view('tpl.hang.daily_excel',['selects' => $selects,'data'=>$this->data,'arr_group'=>json_encode($arr_group),'arr_avg_salary'=>json_encode($arr_avg_salary)]);
    }


}

daily_excel.blade.php

<?php
//因此我们可以在调用loadHtml方法之前,先规避这个问题
// enable user error handling
libxml_use_internal_errors(true);
// load the document
$doc = new DOMDocument();
if (!$doc->load('file.html')) {
   
    foreach (libxml_get_errors() as $error) {
   
        // handle errors here
    }
    libxml_clear_errors();
}
?>
<style>
    .table>tbody>tr>td, .table>tbody>tr>th, .table>tfoot>tr>td, .table>tfoot>tr>th, .table>thead>tr>td, .table>thead>tr>th{
   padding: 0px 0px 0px 2px}
</style>
<div class="box&
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值