美文网首页
PHPExcel-将JSON数据写入Excel

PHPExcel-将JSON数据写入Excel

作者: solozyx | 来源:发表于2016-08-17 14:47 被阅读1152次

    JSON数据格式:
    2016-08-17.json

    {
    "flights": [
        {
            "from": "Beijing", 
            "duration": "19h50m", 
            "class": "Economy", 
            "flight": "|DL128|DL129", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "2+ stops", 
            "airline": "Delta Air Lines", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 16:10", 
            "departureTime": "Wed Aug 17 11:20", 
            "price": "|7524|7995", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR|STU", 
            "flightType": "|UNCERTAIN|Boeing 737-800"
        }, 
        {
            "from": "Beijing", 
            "duration": "22h26m", 
            "class": "Economy", 
            "flight": "|DL128|DL2492", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "2+ stops", 
            "airline": "Delta Air Lines", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 18:46", 
            "departureTime": "Wed Aug 17 11:20", 
            "price": "|7524|7995", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR|STU", 
            "flightType": "|UNCERTAIN|Boeing 737-800"
        }, 
        {
            "from": "Beijing", 
            "duration": "20h55m", 
            "class": "Economy", 
            "flight": "|DL128|DL848", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "2+ stops", 
            "airline": "Delta Air Lines", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 17:15", 
            "departureTime": "Wed Aug 17 11:20", 
            "price": "|7524|7995", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR|STU", 
            "flightType": "|UNCERTAIN|Boeing 737-800"
        }, 
        {
            "from": "Beijing", 
            "duration": "15h5m", 
            "class": "Economy", 
            "flight": "|BR715|BR026", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "EVA Air", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 19:50", 
            "departureTime": "Wed Aug 17 19:45", 
            "price": "|7675|7689", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR|NOR", 
            "flightType": "|Boeing 747-400|Boeing 777-300ER"
        }, 
        {
            "from": "Beijing", 
            "duration": "11h32m", 
            "class": "Economy", 
            "flight": "|DL128", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "Nonstop", 
            "airline": "Delta Air Lines", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 07:52", 
            "departureTime": "Wed Aug 17 11:20", 
            "price": "|9332|10304|14104", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|STU|NOR|NOR", 
            "flightType": "|Boeing767-300"
        }, 
        {
            "from": "Beijing", 
            "duration": "15h58m", 
            "class": "Economy", 
            "flight": "|UA889|UA322", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "United Airlines", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 12:58", 
            "departureTime": "Wed Aug 17 12:00", 
            "price": "|9649", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing 747-400|Boeing 737-900"
        }, 
        {
            "from": "Beijing", 
            "duration": "30h20m", 
            "class": "Economy", 
            "flight": "|KE854|KE019", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "Korean Air", 
            "to": "Seattle", 
            "arriveTime": "Thu Aug 18 12:35", 
            "departureTime": "Wed Aug 17 21:15", 
            "price": "|9844|11822", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR|SEA", 
            "flightType": "|Airbus A330-300|Boeing 777-300ER"
        }, 
        {
            "from": "Beijing", 
            "duration": "13h54m", 
            "class": "Economy", 
            "flight": "|AC030|AC8097", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "Air Canada", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 14:59", 
            "departureTime": "Wed Aug 17 16:05", 
            "price": "|11626", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing 777-300ER|TBD"
        }, 
        {
            "from": "Beijing", 
            "duration": "10h55m", 
            "class": "Economy", 
            "flight": "|HU495", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "Nonstop", 
            "airline": "Hainan Airlines", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 11:35", 
            "departureTime": "Wed Aug 17 15:40", 
            "price": "|12996", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Airbus A330-300"
        }, 
        {
            "from": "Beijing", 
            "duration": "28h15m", 
            "class": "Economy", 
            "flight": "|CA983|VX781", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "", 
            "to": "Seattle", 
            "arriveTime": "Thu Aug 18 10:15", 
            "departureTime": "Wed Aug 17 21:00", 
            "price": "|13500", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing 777-300ER|Airbus A319"
        }, 
        {
            "from": "Beijing", 
            "duration": "26h39m", 
            "class": "Economy", 
            "flight": "|MU5160|DL588", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "", 
            "to": "Seattle", 
            "arriveTime": "Thu Aug 18 09:19", 
            "departureTime": "Wed Aug 17 21:40", 
            "price": "|13733", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Airbus A320|Boeing767-300"
        }, 
        {
            "from": "Beijing", 
            "duration": "24h20m", 
            "class": "Economy", 
            "flight": "|CA981|UA1858", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 22:20", 
            "departureTime": "Wed Aug 17 13:00", 
            "price": "|15409", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing 747|Airbus A320"
        }, 
        {
            "from": "Beijing", 
            "duration": "15h44m", 
            "class": "Economy", 
            "flight": "|CA985|UA424", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 16:24", 
            "departureTime": "Wed Aug 17 15:40", 
            "price": "|15409", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing 747|Airbus A320"
        }, 
        {
            "from": "Beijing", 
            "duration": "37h5m", 
            "class": "Economy", 
            "flight": "|LH7321|LH490", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "Lufthansa", 
            "to": "Seattle", 
            "arriveTime": "Thu Aug 18 12:05", 
            "departureTime": "Wed Aug 17 14:00", 
            "price": "|17922", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing 747|Boeing 747-400"
        }, 
        {
            "from": "Beijing", 
            "duration": "11h32m", 
            "class": "Economy", 
            "flight": "|CZ1155", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "Nonstop", 
            "airline": "China Southern Airlines", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 07:52", 
            "departureTime": "Wed Aug 17 11:20", 
            "price": "|17927", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing767-300"
        }, 
        {
            "from": "Beijing", 
            "duration": "14h40m", 
            "class": "Business", 
            "flight": "|NH956|DL166", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 09:30", 
            "departureTime": "Wed Aug 17 09:50", 
            "price": "|21086", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing-787|Boeing 747-400"
        }, 
        {
            "from": "Beijing", 
            "duration": "19h9m", 
            "class": "Business", 
            "flight": "|AA186|AA2674", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "American Airlines", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 14:19", 
            "departureTime": "Wed Aug 17 10:10", 
            "price": "|21644", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing787-800|Boeing 737-800"
        }, 
        {
            "from": "Beijing", 
            "duration": "16h35m", 
            "class": "Economy", 
            "flight": "|NH956|NH178", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "ANA", 
            "to": "Seattle", 
            "arriveTime": "Wed Aug 17 11:25", 
            "departureTime": "Wed Aug 17 09:50", 
            "price": "|23922", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Boeing787-800|Boeing787-800"
        }, 
        {
            "from": "Beijing", 
            "duration": "28h", 
            "class": "Business", 
            "flight": "|EK307|EK229", 
            "departureAirport": "Beijing Capital International Airport", 
            "stops": "1 stop", 
            "airline": "Emirates", 
            "to": "Seattle", 
            "arriveTime": "Thu Aug 18 12:55", 
            "departureTime": "Wed Aug 17 23:55", 
            "price": "|41458", 
            "arrivalAirport": "Seattle–Tacoma International Airport", 
            "passengerType": "|NOR", 
            "flightType": "|Airbus A380-800|Boeing 777-300ER"
        }
    ], 
    "count": 19, 
    "date": "2016-08-17"
}

    PHPExcel把JSON写入到Excel

    <?php
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

$filename = "./jsonfile/2016-08-17.json";
$json_string = file_get_contents($filename);
//echo print_r($json_string,true);            //打印文件的内容
//echo "<br>";

$array = json_decode($json_string,true);
if (!is_array($array)){
    die('no successful');
}

// echo "<pre>";
// print_r($array);
// echo "</pre>";

$flightsArray = $array['flights'];
// echo "<pre>";
// print_r($flightsArray);
// echo "</pre>";

$sortFlightsArray = array();
for ($i=0; $i < count($flightsArray); $i++) { 
    # code...
    $tempArray = array();
    $tempArray['from'] = $flightsArray[$i]['from'];
    $tempArray['to'] = $flightsArray[$i]['to'];
    $tempArray['departureAirport'] = $flightsArray[$i]['departureAirport'];
    $tempArray['departureTime'] = $flightsArray[$i]['departureTime'];
    $tempArray['arrivalAirport'] = $flightsArray[$i]['arrivalAirport'];
    $tempArray['arriveTime'] = $flightsArray[$i]['arriveTime'];
    $tempArray['duration'] = $flightsArray[$i]['duration'];
    $tempArray['airline'] = $flightsArray[$i]['airline'];
    $tempArray['flightType'] = $flightsArray[$i]['flightType'];
    $tempArray['flight'] = $flightsArray[$i]['flight'];
    $tempArray['stops'] = $flightsArray[$i]['stops'];
    $tempArray['class'] = $flightsArray[$i]['class'];
    $tempArray['price'] = $flightsArray[$i]['price'];
    $tempArray['passengerType'] = $flightsArray[$i]['passengerType'];
    $sortFlightsArray[$i] = $tempArray;
}

// echo "<pre>";
// print_r($sortFlightsArray);
// echo "</pre>";

//********************************************************************************
    
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Fill worksheet from values in array
$objPHPExcel->getActiveSheet()->fromArray($sortFlightsArray, null, 'A1');

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Members');

// Set AutoSize for name and email fields
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);


// Save Excel 2007 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("./2016-08-17.xlsx");

?>

    生成的Excel文件

    PHPExcel生成的excel文件.png

    参考资料：

    https://github.com/PHPOffice/PHPExcel/issues/716

    Fatal error: 'break' not in the 'loop' or 'switch' context in 
<mypath>\PHPExcel\PHPExcel\Calculation\Functions.php on line 581

需要把PHPExcel官方库的Functions.php代码的581行break删掉
    需要把PHPExcel官方库的Functions.php代码的581行 break； 删掉.png

    http://stackoverflow.com/questions/21436949/phpexcel-writer-exception-with-message-could-not-close-zip-file-php-output

    相关文章

      网友评论

          本文标题：PHPExcel-将JSON数据写入Excel

          本文链接：https://www.haomeiwen.com/subject/znpmsttx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|PHPExcel-将JSON数据写入Excel|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！