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; }