PHPExcel-将JSON数据写入Excel
2016-08-17 本文已影响1152人
solozyx
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
