function ExportExcelRecord($arrdata, $datatype, $numberRow, $objPHPExcel, $pageObj)
{
global $cCharset, $locale_info;
$col = -1;
$objASIndex = $objPHPExcel->setActiveSheetIndex(0);
$objASheet = $objPHPExcel->getActiveSheet();
$rowDim = $objASIndex->getRowDimension($numberRow + 1);
foreach ($arrdata as $field => $data) {
$col++;
$colLetter = PHPExcel_Cell::stringFromColumnIndex($col);
$colDim = $objASIndex->getColumnDimension($colLetter);
if ($datatype[$field] == "binary") {
if (!$data) {
continue;
}
if (!function_exists("imagecreatefromstring")) {
$objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, "LONG BINARY DATA - CANNOT BE DISPLAYED");
continue;
}
$error_handler = set_error_handler("empty_error_handler");
$gdImage = imagecreatefromstring($data);
if ($error_handler) {
set_error_handler($error_handler);
}
if ($gdImage) {
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setImageResource($gdImage);
$objDrawing->setCoordinates($colLetter . ($row + 1));
$objDrawing->setWorksheet($objASheet);
$width = $objDrawing->getWidth() * 0.143;
$height = $objDrawing->getHeight() * 0.75;
if ($rowDim->getRowHeight() < $height) {
$rowDim->setRowHeight($height);
}
$colDimSh = $objASheet->getColumnDimension($colLetter);
$colDimSh->setAutoSize(false);
if ($colDim->getWidth() < $width) {
$colDim->setWidth($width);
}
}
} elseif ($datatype[$field] == "file") {
$arr = my_json_decode($row[$field]);
if (count($arr) == 0) {
$data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset);
if ($data == "<img src=\"images/no_image.gif\" />") {
$arr[] = array("name" => "images/no_image.gif");
} else {
if (substr($data, 0, 1) == '=') {
$data = '="' . str_replace('"', '""', $data) . '"';
}
$objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, $data);
continue;
}
}
$offsetY = 0;
$height = 0;
foreach ($arr as $img) {
if (!file_exists($img["name"]) || !$img["name"]) {
$data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset);
if (substr($data, 0, 1) == '=') {
$data = '="' . str_replace('"', '""', $data) . '"';
}
$objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, $data);
continue;
}
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($img["name"]);
$objDrawing->setCoordinates($colLetter . ($numberRow + 1));
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objDrawing->setOffsetY($offsetY);
$width = $objDrawing->getWidth() * 0.143;
$height = $height + $objDrawing->getHeight() * 0.75;
$offsetY = $offsetY + $objDrawing->getHeight();
if ($rowDim->getRowHeight() < $height) {
$rowDim->setRowHeight($height);
}
$colDimSh = $objASheet->getColumnDimension($colLetter);
$colDimSh->setAutoSize(false);
if ($colDim->getWidth() < $width) {
$colDim->setWidth($width);
}
}
} else {
$data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset);
if (substr($data, 0, 1) == '=') {
$data = '="' . str_replace('"', '""', $data) . '"';
}
$objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, $data);
if ($datatype[$field] == "date") {
$objStyle = $objASIndex->getStyle($colLetter . ($numberRow + 1));
$objNumFrm = $objStyle->getNumberFormat();
$objNumFrm->setFormatCode($locale_info["LOCALE_SSHORTDATE"] . " hh:mm:ss");
}
}
}
}
/**
* Insert an image in a worksheet.
*
* @param integer $row The row we are going to insert the bitmap into
* @param integer $col The column we are going to insert the bitmap into
* @param string $bitmap The bitmap filename
* @param integer $x The horizontal position (offset) of the image inside the cell.
* @param integer $y The vertical position (offset) of the image inside the cell.
* @param integer $scale_x The horizontal scale
* @param integer $scale_y The vertical scale
*/
public function insert_bitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1)
{
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($bitmap);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($col) . ($row + 1));
$objDrawing->setOffsetX($x);
$objDrawing->setOffsetY($y);
$objDrawing->setWorksheet($this->worksheet);
if ($scale_x != 1) {
$objDrawing->setResizeProportional(false);
$objDrawing->getWidth($objDrawing->getWidth() * $scale_x);
}
if ($scale_y != 1) {
$objDrawing->setResizeProportional(false);
$objDrawing->setHeight($objDrawing->getHeight() * $scale_y);
}
}
/**
* Write drawings to XML format
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet_Drawing $pDrawing
* @param int $pRelationId
* @throws Exception
*/
public function _writeDrawing(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet_Drawing $pDrawing = null, $pRelationId = -1)
{
if ($pRelationId >= 0) {
// xdr:oneCellAnchor
$objWriter->startElement('xdr:oneCellAnchor');
// Image location
$aCoordinates = PHPExcel_Cell::coordinateFromString($pDrawing->getCoordinates());
$aCoordinates[0] = PHPExcel_Cell::columnIndexFromString($aCoordinates[0]);
// xdr:from
$objWriter->startElement('xdr:from');
$objWriter->writeElement('xdr:col', $aCoordinates[0] - 1);
$objWriter->writeElement('xdr:colOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetX()));
$objWriter->writeElement('xdr:row', $aCoordinates[1] - 1);
$objWriter->writeElement('xdr:rowOff', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getOffsetY()));
$objWriter->endElement();
// xdr:ext
$objWriter->startElement('xdr:ext');
$objWriter->writeAttribute('cx', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getWidth()));
$objWriter->writeAttribute('cy', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getHeight()));
$objWriter->endElement();
// xdr:pic
$objWriter->startElement('xdr:pic');
// xdr:nvPicPr
$objWriter->startElement('xdr:nvPicPr');
// xdr:cNvPr
$objWriter->startElement('xdr:cNvPr');
$objWriter->writeAttribute('id', $pRelationId);
$objWriter->writeAttribute('name', $pDrawing->getName());
$objWriter->writeAttribute('descr', $pDrawing->getDescription());
$objWriter->endElement();
// xdr:cNvPicPr
$objWriter->startElement('xdr:cNvPicPr');
// a:picLocks
$objWriter->startElement('a:picLocks');
$objWriter->writeAttribute('noChangeAspect', '1');
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
// xdr:blipFill
$objWriter->startElement('xdr:blipFill');
// a:blip
$objWriter->startElement('a:blip');
$objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
$objWriter->writeAttribute('r:embed', 'rId' . $pRelationId);
$objWriter->endElement();
// a:stretch
$objWriter->startElement('a:stretch');
$objWriter->writeElement('a:fillRect', null);
$objWriter->endElement();
$objWriter->endElement();
// xdr:spPr
$objWriter->startElement('xdr:spPr');
// a:xfrm
$objWriter->startElement('a:xfrm');
$objWriter->writeAttribute('rot', PHPExcel_Shared_Drawing::degreesToAngle($pDrawing->getRotation()));
$objWriter->endElement();
// a:prstGeom
$objWriter->startElement('a:prstGeom');
$objWriter->writeAttribute('prst', 'rect');
// a:avLst
$objWriter->writeElement('a:avLst', null);
$objWriter->endElement();
// a:solidFill
$objWriter->startElement('a:solidFill');
// a:srgbClr
$objWriter->startElement('a:srgbClr');
$objWriter->writeAttribute('val', 'FFFFFF');
/* SHADE
// a:shade
$objWriter->startElement('a:shade');
$objWriter->writeAttribute('val', '85000');
$objWriter->endElement();
*/
$objWriter->endElement();
$objWriter->endElement();
/*
// a:ln
$objWriter->startElement('a:ln');
$objWriter->writeAttribute('w', '88900');
$objWriter->writeAttribute('cap', 'sq');
// a:solidFill
$objWriter->startElement('a:solidFill');
// a:srgbClr
$objWriter->startElement('a:srgbClr');
$objWriter->writeAttribute('val', 'FFFFFF');
$objWriter->endElement();
$objWriter->endElement();
// a:miter
$objWriter->startElement('a:miter');
$objWriter->writeAttribute('lim', '800000');
$objWriter->endElement();
$objWriter->endElement();
*/
if ($pDrawing->getShadow()->getVisible()) {
// a:effectLst
$objWriter->startElement('a:effectLst');
// a:outerShdw
$objWriter->startElement('a:outerShdw');
$objWriter->writeAttribute('blurRad', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getShadow()->getBlurRadius()));
$objWriter->writeAttribute('dist', PHPExcel_Shared_Drawing::pixelsToEMU($pDrawing->getShadow()->getDistance()));
$objWriter->writeAttribute('dir', PHPExcel_Shared_Drawing::degreesToAngle($pDrawing->getShadow()->getDirection()));
$objWriter->writeAttribute('algn', $pDrawing->getShadow()->getAlignment());
$objWriter->writeAttribute('rotWithShape', '0');
// a:srgbClr
$objWriter->startElement('a:srgbClr');
$objWriter->writeAttribute('val', $pDrawing->getShadow()->getColor()->getRGB());
// a:alpha
$objWriter->startElement('a:alpha');
$objWriter->writeAttribute('val', $pDrawing->getShadow()->getAlpha() * 1000);
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
}
/*
// a:scene3d
$objWriter->startElement('a:scene3d');
// a:camera
$objWriter->startElement('a:camera');
$objWriter->writeAttribute('prst', 'orthographicFront');
$objWriter->endElement();
// a:lightRig
$objWriter->startElement('a:lightRig');
$objWriter->writeAttribute('rig', 'twoPt');
$objWriter->writeAttribute('dir', 't');
// a:rot
$objWriter->startElement('a:rot');
$objWriter->writeAttribute('lat', '0');
$objWriter->writeAttribute('lon', '0');
$objWriter->writeAttribute('rev', '0');
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
*/
/*
// a:sp3d
$objWriter->startElement('a:sp3d');
// a:bevelT
$objWriter->startElement('a:bevelT');
$objWriter->writeAttribute('w', '25400');
$objWriter->writeAttribute('h', '19050');
$objWriter->endElement();
// a:contourClr
$objWriter->startElement('a:contourClr');
// a:srgbClr
$objWriter->startElement('a:srgbClr');
$objWriter->writeAttribute('val', 'FFFFFF');
$objWriter->endElement();
$objWriter->endElement();
$objWriter->endElement();
*/
$objWriter->endElement();
$objWriter->endElement();
// xdr:clientData
$objWriter->writeElement('xdr:clientData', null);
$objWriter->endElement();
} else {
throw new Exception("Invalid parameters passed.");
}
}
public function test()
{
// ini_set("memory_limit","1024M");
// $html = file_get_contents('../'.EXPORT_TEMP_PATH.'13554680705609.html');;
// require_once dirname(__FILE__)."/../Util/MPDF54/mpdf.php";
// $mpdf=new mPDF();
// $mpdf->useAdobeCJK = true; // Default setting in config.php
// // You can set this to false if you have defined other CJK fonts
// $mpdf->SetAutoFont(AUTOFONT_CJK); // AUTOFONT_CJK | AUTOFONT_THAIVIET | AUTOFONT_RTL | AUTOFONT_INDIC // AUTOFONT_ALL
// // () = default ALL, 0 turns OFF (default initially)
// $mpdf->WriteHTML($html);
// $filename = '../'.EXPORT_TEMP_PATH.'13554680705609'.'_'.rand(100,999).'.pdf';
// $mpdf->Output($filename);
// echo $filename;
// Log::write(date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB" );
// $objReader = PHPExcel_IOFactory::createReader('Excel5');
// $objPHPExcel = $objReader->load(EXCEL_TEMPLATES_PATH."track_template.xls");
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load(EXCEL_TEMPLATES_PATH . "track_template.xlsx");
$row = 9;
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, date('Y-m-d H:i:s'))->setCellValue('B' . $row, '内蒙古自治区包头市东河区,西脑包南五道巷-巴彦塔拉西大街交叉路口,包头市东河区国家税务局,中国石油宁鹿大酒店,出租车上下客站附近')->setCellValue('C' . $row, 25.33)->setCellValue('D' . $row, '东北')->setCellValue('E' . $row, '正常');
$objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(-1);
// $ws = "\nwidth A:" . $objPHPExcel->getActiveSheet()->getColumnDimension('A')->getWidth();
// $ws .= "\nwidth B:" . $objPHPExcel->getActiveSheet()->getColumnDimension('B')->getWidth();
// $ws .= "\nwidth C:" . $objPHPExcel->getActiveSheet()->getColumnDimension('C')->getWidth();
// $ws .= "\nwidth D:" . $objPHPExcel->getActiveSheet()->getColumnDimension('D')->getWidth();
// $ws .= "\nwidth E:" . $objPHPExcel->getActiveSheet()->getColumnDimension('E')->getWidth();
// Log::write($ws);
$widthPT = ($objPHPExcel->getActiveSheet()->getColumnDimension('A')->getWidth() + $objPHPExcel->getActiveSheet()->getColumnDimension('B')->getWidth() + $objPHPExcel->getActiveSheet()->getColumnDimension('C')->getWidth() + $objPHPExcel->getActiveSheet()->getColumnDimension('D')->getWidth() + $objPHPExcel->getActiveSheet()->getColumnDimension('E')->getWidth()) * 6;
//经验：导出成Excel2007
// * 4.7499; //经验：导出成PDF
Log::write("\n-----------widthPT:" . $widthPT . "-----------");
$widthPX = round($widthPT * 4 / 3);
Log::write("\n-----------widthPX:" . $widthPX . "-----------");
$heightPT = 300;
$heightPX = 400;
$objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight($heightPT);
$rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
$rendererLibrary = 'mPDF5.4';
$rendererLibraryPath = dirname(__FILE__) . '/../Util/MPDF54';
PHPExcel_Settings::setPdfRenderer($rendererName, $rendererLibraryPath);
// $widthPX +=-2; //Excel5和Excel2007才需要
// $heightPX +=-2;
$url = 'http://api.map.baidu.com/staticimage?width=' . $widthPX . '&height=' . $heightPX . '¢er=116.468265,39.90692&zoom=11&markers=116.418822,39.859083|116.49586,39.960917&markerStyles=l,|l,';
$newfname = '../' . EXPORT_TEMP_PATH . 'staticimage.png';
$file = fopen($url, "rb");
if ($file) {
$newf = fopen($newfname, "wb");
if ($newf) {
while (!feof($file)) {
fwrite($newf, fread($file, 1024 * 8), 1024 * 8);
}
}
}
if ($file) {
fclose($file);
}
if ($newf) {
fclose($newf);
}
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('map');
$objDrawing->setDescription('Map');
$objDrawing->setPath('../' . EXPORT_TEMP_PATH . 'staticimage.png');
// $objDrawing->setOffsetX(1); //Excel5和Excel2007才需要
// $objDrawing->setOffsetY(1);
// $objDrawing->setHeight($heightPT);
$objDrawing->setCoordinates('A6');
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$wd = "\nWidth: " . $objDrawing->getWidth();
$wd .= "\nHeight: " . $objDrawing->getHeight();
Log::write($wd);
$file = EXPORT_TEMP_PATH . time() . rand(1000, 9999) . '.pdf';
$objPHPExcel->getActiveSheet()->setShowGridlines(false);
//要导出成PDF（或者HTML）这个比较重要，否则表格线会造成出来的结果不好
ini_set("memory_limit", "1024M");
//MPDF消耗内存比较厉害
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setImagesRoot('..');
$objWriter->setPrintParams('A4', 'P', '', '', '', '', 16, 16, 15, 15, 9, 9, true, null, null);
$objWriter->save('../' . $file);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
$objWriter->save('../' . str_replace('.pdf', '.html', $file));
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('../' . str_replace('.pdf', '.xls', $file));
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('../' . str_replace('.pdf', '.xlsx', $file));
}
$objExcel->getActiveSheet()->getStyle('A6:J' . ($startRow - 1))->applyFromArray($styleBorder);
// WRITE DIGITAL SIGNATURE HEADER
$startRow++;
$objExcel->getActiveSheet()->SetCellValue('H' . $startRow, "Printed By");
$objExcel->getActiveSheet()->mergeCells('H' . $startRow . ':I' . $startRow);
// PROVIDE SPACE FOR DIGITAL SIGNATURE
$objExcel->getActiveSheet()->mergeCells('H' . ($startRow + 1) . ':I' . ($startRow + 3));
// DRAW DIGITAL SIGNATURE
if ($ttdPrintedBy != "-") {
if (file_exists('../img/' . $ttdPrintedBy)) {
$objPrintedSign->setName('ttdOrdered');
$objPrintedSign->setDescription('ttd ordered image');
$objPrintedSign->setPath('../img/' . $ttdPrintedBy);
$objPrintedSign->setHeight(60);
$objPrintedSign->setCoordinates('H' . ($startRow + 1));
$offsetX = (137 - $objPrintedSign->getWidth()) / 2;
$objPrintedSign->setOffsetX($offsetX);
$objPrintedSign->setWorksheet($objExcel->getActiveSheet());
}
}
// WRITE DIGITAL SIGNATURE FOOTER
$objExcel->getActiveSheet()->SetCellValue('H' . ($startRow + 4), $printedBy);
$objExcel->getActiveSheet()->mergeCells('H' . ($startRow + 4) . ':I' . ($startRow + 4));
// APPLY STYLES IN DIGITAL SIGNATURE
$objExcel->getActiveSheet()->getStyle('H' . $startRow . ':I' . ($startRow + 4))->applyFromArray($styleBorder);
$objExcel->getActiveSheet()->getStyle('H' . $startRow . ':I' . ($startRow + 4))->applyFromArray($styleCenter);
unset($styleBorder);
unset($styleCenter);
$objExcel->getActiveSheet()->getProtection()->setPassword('Sukasari1234');
$objExcel->getActiveSheet()->getProtection()->setSheet(true);
header('Content-type: application/vnd.ms-excel');
function ExportExcelRecord($arrdata, $datatype, $row, $objPHPExcel, $pageObj)
{
global $cCharset, $locale_info;
$col = -1;
$objASIndex = $objPHPExcel->setActiveSheetIndex(0);
$objASheet = $objPHPExcel->getActiveSheet();
$rowDim = $objASIndex->getRowDimension($row + 1);
foreach ($arrdata as $field => $data) {
$col++;
$colLetter = PHPExcel_Cell::stringFromColumnIndex($col);
$colDim = $objASIndex->getColumnDimension($colLetter);
if ($datatype[$field] == "binary") {
if (!$data) {
continue;
}
if (!function_exists("imagecreatefromstring")) {
$objASIndex->setCellValueByColumnAndRow($col, $row + 1, "LONG BINARY DATA - CANNOT BE DISPLAYED");
continue;
}
$error_handler = set_error_handler("empty_error_handler");
$gdImage = imagecreatefromstring($data);
if ($error_handler) {
set_error_handler($error_handler);
}
if ($gdImage) {
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setImageResource($gdImage);
$objDrawing->setCoordinates($colLetter . ($row + 1));
$objDrawing->setWorksheet($objASheet);
$width = $objDrawing->getWidth() * 0.143;
$height = $objDrawing->getHeight() * 0.75;
if ($rowDim->getRowHeight() < $height) {
$rowDim->setRowHeight($height);
}
$colDimSh = $objASheet->getColumnDimension($colLetter);
$colDimSh->setAutoSize(false);
if ($colDim->getWidth() < $width) {
$colDim->setWidth($width);
}
}
} elseif ($datatype[$field] == "file") {
if (!file_exists($pageObj->pSet->getUploadFolder($field) . $data) || !$data) {
continue;
}
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($pageObj->pSet->getUploadFolder($field) . $data);
$objDrawing->setCoordinates($colLetter . ($row + 1));
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$width = $objDrawing->getWidth() * 0.143;
$height = $objDrawing->getHeight() * 0.75;
if ($rowDim->getRowHeight() < $height) {
$rowDim->setRowHeight($height);
}
$colDimSh = $objASheet->getColumnDimension($colLetter);
$colDimSh->setAutoSize(false);
if ($colDim->getWidth() < $width) {
$colDim->setWidth($width);
}
} else {
$data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset);
$objASIndex->setCellValueByColumnAndRow($col, $row + 1, $data);
if ($datatype[$field] == "date") {
$objStyle = $objASIndex->getStyle($colLetter . ($row + 1));
$objNumFrm = $objStyle->getNumberFormat();
$objNumFrm->setFormatCode($locale_info["LOCALE_SSHORTDATE"] . " hh:mm:ss");
}
}
}
}
private function _exportData2Excel($target = 'Excel2007')
{
require_once dirname(__FILE__) . "/../Util/PHPExcel/PHPExcel/IOFactory.php";
$session_id = $this->_request('session_id');
$filename = $this->_request('file');
$device_id = $this->_request('device') + 0;
if (empty($session_id) || empty($filename) || empty($device_id)) {
return;
}
session_id($session_id);
session_write_close();
ignore_user_abort(true);
set_time_limit(0);
R('File/setfilepercent', array($filename, '正在读取模版文件...'));
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load(EXCEL_TEMPLATES_PATH . "track_template.xlsx");
$activeSheet = $objPHPExcel->getActiveSheet();
$condition = $this->_parseCondition();
//先查一下Device信息，以便在没有定位数据的情况下仍然能填写表头数据
$Device = M('Device');
check_error($Device);
R('File/setfilepercent', array($filename, '正在查询设备信息...'));
$device = $Device->join('`department` on `department`.`id`=`device`.`department_id`')->field(array('`target_type`', '`target_name`', '`department`.`name`' => 'department_name'))->where("`device`.`id`={$device_id}")->find();
check_error($Device);
if (empty($device)) {
R('File/fileexit', array($filename, '设备id错误：无法查找到指定id的设备'));
}
R('File/setfilepercent', array($filename, '正在处理表头信息...'));
//写表头信息：
$activeSheet->setCellValue('A1', $device['target_type'] . '轨迹数据')->setCellValue('A2', '定位对象：' . $device['target_name'])->setCellValue('A3', '所属分组：' . $device['department_name'])->setCellValue('C2', '从：' . $_REQUEST['startTime']);
if (!empty($_REQUEST['endTime'])) {
$activeSheet->setCellValue('C3', '到：' . $_REQUEST['endTime']);
}
$Location = M('Location');
check_error($Location);
R('File/setfilepercent', array($filename, '正在查询数据库...'));
$locations = $Location->join('`device` on `device`.`id`=`location`.`device_id`')->join('`department` on `department`.`id`=`device`.`department_id`')->field(array('`location`.`id`', '`device`.`department_id`', '`department`.`name`' => 'department', '`location`.`device_id`', '`device`.`type`', '`device`.`label`', '`device`.`target_type`', '`device`.`target_id`', '`device`.`target_name`', '`location`.`time`', 'state', 'online', '`location`.`address`', 'baidu_lat', 'baidu_lng', 'speed', 'direction', 'mcc', 'mnc', 'lac', 'cellid', '`location`.`range`'))->where($condition)->order('`time` ASC')->select();
check_error($Location);
if (empty($locations)) {
$activeSheet->setCellValue('A6', '没有定位数据');
} else {
$center = $this->_request('center');
$width = $this->_request('width') + 0;
$height = $this->_request('height') + 0;
$zoom = $this->_request('zoom') + 0;
if (empty($center) || empty($width) || empty($height) || empty($zoom)) {
$activeSheet->setCellValue('A6', '地图参数不正确，无法创建地图');
} else {
$mapparams = array('center' => $center, 'width' => $width, 'height' => $height, 'zoom' => $zoom);
}
}
$total = count($locations);
R('File/setfilepercent', array($filename, '正在处理数据库查询结果...', $total, 0));
$lastTime = time();
$baseRow = 9;
$startMarker = null;
$endMarker = null;
$paths = array();
foreach ($locations as $r => $dataRow) {
$row = $baseRow + $r;
if ($r) {
$activeSheet->insertNewRowBefore($row, 1);
}
if (!empty($dataRow['baidu_lat']) && !empty($dataRow['baidu_lng'])) {
$endMarker = $p = $dataRow['baidu_lng'] . "," . $dataRow['baidu_lat'];
if (empty($startMarker)) {
$startMarker = $p;
}
$paths[] = $p;
}
$activeSheet->setCellValue('A' . $row, $dataRow['time'])->setCellValue('B' . $row, $dataRow['address'])->setCellValue('C' . $row, $dataRow['speed'])->setCellValue('D' . $row, $dataRow['direction'])->setCellValue('E' . $row, $dataRow['state']);
$activeSheet->getRowDimension($row)->setRowHeight(-1);
if (time() - $lastTime) {
//过了1秒
$lastTime = time();
R('File/setfilepercent', array($filename, '正在处理数据库查询结果...', $total, $r + 1));
}
}
if (!empty($mapparams)) {
R('File/setfilepercent', array($filename, '正在准备地图...'));
if (!empty($startMarker)) {
$mapparams['markers'] = $startMarker;
$mapparams['markerStyles'] = 'm,A';
}
if (!empty($endMarker) && !empty($mapparams['markers'])) {
$mapparams['markers'] .= '|' . $endMarker;
$mapparams['markerStyles'] = '|m,B';
}
if (!empty($paths)) {
$mapparams['paths'] = implode(";", $paths);
$mapparams['pathStyles'] = '0xff0000,3,1';
}
$img = R('File/getbaidumapstaticimage', array($mapparams));
if ($img) {
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('map');
$objDrawing->setDescription('Map');
$objDrawing->setPath($img);
$objDrawing->setCoordinates('A6');
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
//调整图片大小，使之适应实际大小
$widthPT = $activeSheet->getColumnDimension('A')->getWidth() + $activeSheet->getColumnDimension('B')->getWidth() + $activeSheet->getColumnDimension('C')->getWidth() + $activeSheet->getColumnDimension('D')->getWidth() + $activeSheet->getColumnDimension('E')->getWidth();
//根据经验调整$widthPT
if ($target == 'PDF') {
$widthPT *= 4.7499;
} else {
if ($target == 'Excel5') {
$widthPT *= 5.251282;
} else {
$widthPT *= 6;
}
}
//经验：导出成Excel2007
$widthPX = round($widthPT * 4 / 3);
$scale = $objDrawing->getWidth() / $widthPX;
$heightPX = round($objDrawing->getHeight() / $scale);
$heightPT = $heightPX * 0.75;
$objDrawing->setWidth($widthPX);
$objDrawing->setHeight($heightPX);
$activeSheet->getRowDimension('6')->setRowHeight($heightPT);
$objDrawing->setWorksheet($activeSheet);
R('File/setfilepercent', array($filename, '地图就绪...'));
} else {
R('File/setfilepercent', array($filename, '地图准备失败...'));
}
}
return $objPHPExcel;
}
