<?php

set_time_limit(0);

include './plugin/PHPExcel/PHPExcel.php';

include './plugin/PHPExcel/PHPExcel/IOFactory.php';

include './plugin/PHPExcel/PHPExcel/Reader/Excel5.php';

$inputFileName = "./20190514-165802.xlsx";

$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);

$sheet = $objPHPExcel->getSheet(0); // 读取第一個工作表

$highestColumm = $sheet->getHighestColumn(); // 取得总列数

$highestRow = $sheet->getHighestRow(); // 取得总行数

$file = pathinfo($inputFileName);

$saveTo = $file['filename'] . "_convert." . $file['extension'];

for ($i = 2; $i <= $highestRow; $i++) {

    //获取经纬度

    $lng = (string)$objPHPExcel->setActiveSheetIndex()->getCell("K" . $i);

    $lat = (string)$objPHPExcel->setActiveSheetIndex()->getCell("L" . $i);

    if (empty($lng) || empty($lat)) {

        continue;

    }

    //转换为百度地图经纬度

    $convert = Convert_GCJ02_To_BD09(doubleval($lat), doubleval($lng));

    $objPHPExcel->setActiveSheetIndex()->setCellValue("K" . $i, (string)$convert['lng'] . "\t");

    $objPHPExcel->setActiveSheetIndex()->setCellValue("L" . $i, (string)$convert['lat'] . "\t");

    //保存excel

    /** 输出到指定目录 */

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

    $objWriter->save($saveTo);//文件保存路径

}

echo "转换成功";

/**

 * 中国正常GCJ02坐标---->百度地图BD09坐标

 * 腾讯地图用的也是GCJ02坐标

 * @param double $lat 纬度

 * @param double $lng 经度

 */

function Convert_GCJ02_To_BD09($lat, $lng)

{

    $x_pi = 3.14159265358979324 * 3000.0 / 180.0;

    $x = $lng;

    $y = $lat;

    $z = sqrt($x * $x + $y * $y) + 0.00002 * sin($y * $x_pi);

    $theta = atan2($y, $x) + 0.000003 * cos($x * $x_pi);

    $lng = $z * cos($theta) + 0.0065;

    $lat = $z * sin($theta) + 0.006;

    return array('lng' => $lng, 'lat' => $lat);

}

/**

 * 百度地图BD09坐标---->中国正常GCJ02坐标

 * 腾讯地图用的也是GCJ02坐标

 * @param double $lat 纬度

 * @param double $lng 经度

 * @return array();

 */

function Convert_BD09_To_GCJ02($lat, $lng)

{

    $x_pi = 3.14159265358979324 * 3000.0 / 180.0;

    $x = $lng - 0.0065;

    $y = $lat - 0.006;

    $z = sqrt($x * $x + $y * $y) - 0.00002 * sin($y * $x_pi);

    $theta = atan2($y, $x) - 0.000003 * cos($x * $x_pi);

    $lng = $z * cos($theta);

    $lat = $z * sin($theta);

    return array('lng' => $lng, 'lat' => $lat);

}