function orderexcel($data, $title) { $phpexcel_root = ROOT . '/extends/PHPExcel'; include_once $phpexcel_root . '/PHPExcel.php'; $objPHPExcel = new \PHPExcel(); foreach ($title as $index => $string) { $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index) . '1', $string); } foreach ($data as $line => $value) { $objPHPExcel->getActiveSheet()->getRowDimension($line + 4)->setRowHeight(-3); $objPHPExcel->getActiveSheet()->getStyle('D' . ($line + 4))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00); $objPHPExcel->getActiveSheet()->getStyle('Q' . ($line + 4))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00); $objPHPExcel->getActiveSheet()->getStyle('R' . ($line + 4))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER); $objPHPExcel->getActiveSheet()->getStyle('F' . ($line + 4))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT); //$objPHPExcel->getActiveSheet()->getRowDimension($line+4)->setHeight(200); $index = 0; $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['orderno']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['paytype']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['paynumber']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['ordertotalamount']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['ordertaxamount']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['ordergoodsamount']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['feeamount']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['tradetime']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['totalamount']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['consigneetel']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['consignee']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['zipcode']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['consigneeprovince']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['consigneecity']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['consigneecounty']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['consigneeaddress']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['postmode']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['username']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['sku']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['productname']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['unitprice']); $objPHPExcel->getActiveSheet()->setCellValue(chr(ord('A') + $index++) . ($line + 4), $value['num']); if (!empty($value['img'])) { $objDrawing = new \PHPExcel_Worksheet_Drawing(); $objDrawing->setName($value['productname']); $objDrawing->setPath($value['img'][0]['thumbnail_path']); $objDrawing->setCoordinates(chr(ord('A') + $index++) . ($line + 4)); $objDrawing->setResizeProportional(true); $objDrawing->setWidthAndHeight(100, 100); $objDrawing->setOffsetX(100); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); } } $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $filename = time('Y-m-d H:i:s') . '.xls'; $objWriter->save($filename); return $filename; }
private function getTeaserImage(Teasers $teaser) { $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName($teaser->title); $path = Yii::app()->params->imageBasePath . DIRECTORY_SEPARATOR . $teaser->picture; if (!file_exists($path)) { $path = $path = Yii::app()->params->imageBasePath . DIRECTORY_SEPARATOR . 'notfound.png'; } $objDrawing->setPath($path); $objDrawing->setWidthAndHeight(95, 95); $objDrawing->setOffsetX(10); $objDrawing->setOffsetY(2); return $objDrawing; }
public function postAction() { $post = $this->getRequest()->getPost(); if ($post['file_type'] == 'csv') { $dir = $post['filetype'] . '_' . date('Y-m-d'); mkdir('image/' . $dir, 0777); $name = $dir . '.csv'; header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename=csv/' . $name); header('Pragma: no-cache'); header("Expires: 0"); touch('/csv'); chmod('/csv', 0777); define('SAVE_FEED_LOCATION', 'csv/' . $name); set_time_limit(1800); try { $handle = fopen(SAVE_FEED_LOCATION, 'w'); $heading = array('STYLE NUMBER', 'STYLE NAME', 'LONG DESCRIPTION', 'GENDER', 'IMAGES PROVIDED', 'IMAGE FILENAME', 'COMPOSITION: STONES', 'COMPOSITION: SETTING', 'COMPOSITION: OTHER', 'SECONDARY COLOR', 'STONE CUT', 'CLARITY', 'COLOUR', 'SETTING TYPE', 'RING SIZE 6', 'RING SIZE 7', 'RING SIZE 7.5', 'RING SIZE 8', 'RING SIZE 8.5', 'RING SIZE 9', 'RING SIZE 9.5', 'RING SIZE 10', 'RING SIZE 10.5', 'RING SIZE 11', 'RING SIZE 11.5', 'RING SIZE 12', 'RING SIZE 12.5', 'RING SIZE 13', 'LENGTH', 'HEIGHT', 'WIDTH', 'SPECIAL CARE', 'MADE IN "COUNTRY"', 'WHOLESALE', 'RETAIL', 'SPECIAL PRICE', 'INVENTORY'); $feed_line = implode(",", $heading) . "\r\n"; fwrite($handle, $feed_line); $products = Mage::getModel('catalog/product')->getCollection(); $products->addAttributeToSelect('*'); $prodIds = $products->getAllIds(); $product = Mage::getModel('catalog/product'); $counter_test = 0; foreach ($post['id'] as $productId) { if (++$counter_test < 30000) { $product->load($productId); $product_data = array(); $product_data['style_number'] = str_replace('"', '""', $product->getSku()); $product_data['style_name'] = $product->getName(); $product_data['description'] = htmlspecialchars(iconv("UTF-8", "UTF-8//IGNORE", $product->getDescription())); $product_data['gender'] = $product->getResource()->getAttribute('filter_for')->getFrontend()->getValue($product); if ($product->getImage()) { if ($product->getImage() == 'no_selection') { $product_data['image_provided'] = 'No'; } else { $product_data['image_provided'] = 'Yes'; } } else { $product_data['image_provided'] = 'No'; } if ($product->getImage() == 'no_selection') { $product_data['image_link'] = ''; } else { $product_data['image_link'] = $product->getImage(); } $image_break = explode("/", $product->getImage()); $imagename = end($image_break); $file = Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_MEDIA) . 'catalog/product' . $product->getImage(); $newfile = 'image/' . $dir . '/' . $imagename; if (!copy($file, $newfile)) { echo "failed to copy {$file}...\n"; } $product_data['stone'] = $product->getAttributeText('product_stones'); $product_data['setting'] = ''; $product_data['other'] = ''; $product_data['s_color'] = ''; $product_data['stone_cut'] = ''; $product_data['clarity'] = ''; $product_data['color'] = $product->getAttributeText('product_color'); $product_data['setting_type'] = ''; $product_data['ring_size6'] = ''; $product_data['ring_size7'] = ''; $product_data['ring_size75'] = ''; $product_data['ring_size8'] = ''; $product_data['ring_size85'] = ''; $product_data['ring_size9'] = ''; $product_data['ring_size95'] = ''; $product_data['ring_size10'] = ''; $product_data['ring_size105'] = ''; $product_data['ring_size11'] = ''; $product_data['ring_size115'] = ''; $product_data['ring_size12'] = ''; $product_data['ring_size125'] = ''; $product_data['ring_size13'] = ''; $product_data['length'] = str_replace('"', '""', $product->getLengthDimensions()); $product_data['height'] = str_replace('"', '""', $product->getHeightDimensions()); $product_data['width'] = str_replace('"', '""', $product->getWidthDimensions()); $product_data['special_care'] = ''; $product_data['made_in'] = ''; $product->setCustomerGroupId(2); if (number_format($product->getPriceModel()->getFinalPrice(1, $product), 2) == number_format($product->getPrice(), 2)) { $product_data['wholesale'] = ''; } else { $product_data['wholesale'] = number_format($product->getPriceModel()->getFinalPrice(1, $product), 2); } $price_temp = round($product->getPrice(), 2); $product_data['price'] = round($product->getPrice(), 2); if ($product->getSpecialPrice() == '') { $product_data['special_price'] = ''; } else { $product_data['special_price'] = number_format($product->getSpecialPrice(), 2); } $product_data['qty'] = $product->getStockItem()->getQty(); foreach ($product_data as $k => $val) { $product_data[$k] = '"' . $val . '"'; } $feed_line = implode(",", $product_data) . "\r\n"; fwrite($handle, $feed_line); fflush($handle); } } fclose($handle); $dir12 = 'image/' . $dir; $archive = 'csv/' . $dir . '.zip'; $zip = new ZipArchive(); $zip->open($archive, ZipArchive::CREATE); $files = scandir($dir12); unset($files[0], $files[1]); foreach ($files as $file) { $zip->addFile($dir12 . '/' . $file); } $zip->close(); header('Content-Type: application/zip'); header('Content-disposition: attachment; filename=' . $archive); header('Content-Length: ' . filesize($archive)); $dirPath = 'image/' . $dir; if (!is_dir($dirPath)) { throw new InvalidArgumentException("{$dirPath} must be a directory"); } if (substr($dirPath, strlen($dirPath) - 1, 1) != '/') { $dirPath .= '/'; } $files = glob($dirPath . '*', GLOB_MARK); foreach ($files as $file) { if (is_dir($file)) { self::deleteDir($file); } else { unlink($file); } } rmdir($dirPath); } catch (Exception $e) { die($e->getMessage()); } } if ($post['file_type'] == 'xls') { error_reporting(E_ALL); /** Include PHPExcel */ require_once 'Classes/PHPExcel.php'; // Create new PHPExcel object //echo date('H:i:s') , " Create new PHPExcel object" , EOL; $objPHPExcel = new PHPExcel(); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel_IOFactory */ require_once 'Classes/PHPExcel/IOFactory.php'; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); //********* CUSTOM CODE **************************** // Create a first sheet, representing sales data //echo date('H:i:s') , " Add some data" , EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->getCell('B1')->setValue("500 Sauve West\nMontreal, Quebec\nCanada\nH3L 1Z8"); $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getCell('C1')->setValue("info@italgemjewellers.com\nTel (514) 388-5777\nFax (514) 384-5777"); $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setWrapText(true); // Merge cells //echo date('H:i:s') , " Merge cells" , EOL; $objPHPExcel->getActiveSheet()->mergeCells('F1:G1'); $objPHPExcel->getActiveSheet()->getCell('F1')->setValue("Quotation preparred for \nClientABCD"); $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->setCellValue('A3', 'STYLE CODE #'); $objPHPExcel->getActiveSheet()->setCellValue('B3', 'PICTURE'); $objPHPExcel->getActiveSheet()->setCellValue('C3', 'DESCRIPTION'); $objPHPExcel->getActiveSheet()->setCellValue('D3', 'RETAIL'); $objPHPExcel->getActiveSheet()->setCellValue('E3', 'WHOLE SALE PRICE'); $objPHPExcel->getActiveSheet()->setCellValue('F3', 'YOUR PRICE'); $objPHPExcel->getActiveSheet()->setCellValue('G3', 'Quanity '); // Set column widths //echo date('H:i:s') , " Set column widths" , EOL; $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(98); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(80); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20); // Set fonts //echo date('H:i:s') , " Set fonts" , EOL; $objPHPExcel->getActiveSheet()->getStyle("B1")->getFont()->setName('Times New Roman')->setSize(15)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle("C1")->getFont()->setName('Times New Roman')->setSize(15)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle("F1")->getFont()->setBold(true)->setName('Times New Roman')->setSize(18)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); // Set thin black border outline around column //echo date('H:i:s') , " Set thin black border outline around column" , EOL; $styleThinBlackBorderOutline = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF000000')))); $objPHPExcel->getActiveSheet()->getStyle('A1:D1')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('G1')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('C3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('D3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('E3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('F3')->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('G3')->applyFromArray($styleThinBlackBorderOutline); // Set fills //echo date('H:i:s') , " Set fills" , EOL; $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->getStartColor()->setARGB('FFFFFF'); $objPHPExcel->getActiveSheet()->getStyle('A2:G2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A2:G2')->getFill()->getStartColor()->setARGB('FFFFFF'); // Set style for header row using alternative method //echo date('H:i:s') , " Set style for header row using alternative method" , EOL; $objPHPExcel->getActiveSheet()->getStyle('A3:G3')->applyFromArray(array('font' => array('bold' => true), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)))); // Add a drawing to the worksheet //echo date('H:i:s') , " Add a drawing to the worksheet" , EOL; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath('./images/logo.png'); $objDrawing->setHeight(133); $objDrawing->setWidth(281); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //$post['id'] = array("1","2","3","4","5"); $products = Mage::getModel('catalog/product')->getCollection(); $products->addAttributeToSelect('*'); $prodIds = $products->getAllIds(); $product = Mage::getModel('catalog/product'); $counter_test = 0; $i = 4; foreach ($post['id'] as $productId) { if (++$counter_test < 30000) { $product->load($productId); $style_number = str_replace('"', '', $product->getSku()); $image_link = $product->getImage(); $description = htmlspecialchars(iconv("UTF-8", "UTF-8//IGNORE", $product->getDescription())); $product->setCustomerGroupId(2); $wholesale = number_format($product->getPriceModel()->getFinalPrice(1, $product), 2); $price_temp = round($product->getPrice(), 2); $price = round($product->getPrice(), 2); $special_price = number_format($product->getSpecialPrice(), 2); $qty = $product->getStockItem()->getQty(); $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(190); //Infromation 1 $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $style_number); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':G' . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':G' . $i)->getFill()->getStartColor()->setARGB('FFFFFF'); $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->applyFromArray($styleThinBlackBorderOutline); //Extra Block $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->applyFromArray($styleThinBlackBorderOutline); //2 $objPHPExcel->getActiveSheet()->getStyle('B' . $i)->applyFromArray($styleThinBlackBorderOutline); $objDrawing = new PHPExcel_Worksheet_Drawing(); if (file_exists('./media/catalog/product' . $image_link)) { if ($product->getImage() == 'no_selection') { $objDrawing->setPath('./images/logo.png'); $objDrawing->setOffsetX(5); $objDrawing->setOffsetY(50); } else { $objDrawing->setPath('./media/catalog/product' . $image_link); $objDrawing->setOffsetX(5); $objDrawing->setOffsetY(5); } } else { $objDrawing->setPath('./images/logo.png'); $objDrawing->setOffsetX(5); $objDrawing->setOffsetY(50); } $objDrawing->setCoordinates('B' . $i); //$objDrawing->setHeight(190); //$objDrawing->setWidth(340); $objDrawing->setResizeProportional(true); $objDrawing->setWidthAndHeight(340, 210); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //3 Cell $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $description); $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('C' . $i)->getFont()->setBold(true); //4 Cell $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->applyFromArray($styleThinBlackBorderOutline); if ($price_temp == '0') { $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, ''); } else { $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getNumberFormat()->applyFromArray(array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD)); $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $price_temp); } $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('D' . $i)->getFont()->setBold(true); //5 Cell $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->applyFromArray($styleThinBlackBorderOutline); if ($wholesale == $price_temp) { $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, ''); } else { $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getNumberFormat()->applyFromArray(array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD)); $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $wholesale); } $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('E' . $i)->getFont()->setBold(true); //6 Cell $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->applyFromArray($styleThinBlackBorderOutline); if ($special_price == '0') { $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, ''); } else { $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getNumberFormat()->applyFromArray(array('code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD)); $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $special_price); } $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('000000'); $objPHPExcel->getActiveSheet()->getStyle('F' . $i)->getFont()->setBold(true); //7 Cell $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->applyFromArray($styleThinBlackBorderOutline); $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $qty); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->getAlignment()->setIndent(9); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->getFont()->setName('Arial')->setSize(10)->getColor()->setRGB('FF0000'); $objPHPExcel->getActiveSheet()->getStyle('G' . $i)->getFont()->setBold(true); } $i++; } //echo "come"; //die(); $name = $post['filetype'] . '_' . date('Y-m-d') . '.xlsx'; $changeFilename = $name; $allvalues_array = explode('/', $changeFilename); $lastvalues = count($allvalues_array) - 1; $finalname_array = array(); $finalname = ''; for ($pl = 0; $pl < count($allvalues_array); $pl++) { if ($pl == $lastvalues) { $finalname_array[] = 'xls'; $finalname_array[] = $allvalues_array[$pl]; } else { $finalname_array[] = $allvalues_array[$pl]; } } $finalname = implode('/', $finalname_array); //********* CUSTOM CODE **************************** $objWriter->save($finalname); } try { if (empty($post)) { Mage::throwException($this->__('Invalid form data.')); } /* here's your form processing */ if ($post['file_type'] == 'csv') { $message = $this->__('Your CSV successfully exported.'); } if ($post['file_type'] == 'xls') { $message = $this->__('Your XLSX successfully exported.'); } Mage::getSingleton('adminhtml/session')->addSuccess($message); } catch (Exception $e) { Mage::getSingleton('adminhtml/session')->addError($e->getMessage()); } $this->_redirect('*/*'); }
/** * Добавляет лого на страницу * * @param PHPExcel_Worksheet $activeSheet * @param string|null $rightColumn */ protected function addLogo(PHPExcel_Worksheet $activeSheet, $rightColumn = null) { $logo_path = dirname(Yii::app()->basePath) . DIRECTORY_SEPARATOR . 'htdocs/images' . DIRECTORY_SEPARATOR . 'logo2.png'; $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setWorksheet($activeSheet); $objDrawing->setName("Лого"); $objDrawing->setPath($logo_path); $objDrawing->setCoordinates('A1'); $objDrawing->setWidthAndHeight(308.3, 75.90000000000001); if (null === $rightColumn) { $rightColumn = $activeSheet->getHighestDataColumn(); } $activeSheet->mergeCells('A5:' . $rightColumn . '5'); $activeSheet->setCellValue('A5', $this->title); $activeSheet->getStyle('A5')->applyFromArray(array('font' => array('color' => array('rgb' => '376091'), 'bold' => true), 'alignment' => array('indent' => 10))); $activeSheet->getStyle('A1:' . $rightColumn . '6')->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_NONE)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FFFFFF')))); $activeSheet->getStyle('A6:' . $rightColumn . '6')->applyFromArray(array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('rgb' => '316194'))))); }
public function actionExcelDDC() { date_default_timezone_set('America/Tegucigalpa'); if (isset($_GET['Estudiante'], $_GET['Practica'])) { $IdEstudiante = $_GET['Estudiante']; $IdPractica = $_GET['Practica']; $Estudiante = UsuarioEstudiante::model()->findByPk($IdEstudiante); $PracticaProfesional = PracticaProfesional::model()->findByPk($IdPractica); Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); $objPHPExcel->getProperties()->setCreator("Henry Arévalo")->setLastModifiedBy("Henry Arévalo")->setTitle("Sistema de Gestión de Prácticas Profesionales")->setSubject("UNITEC")->setDescription("SGPP")->setKeywords("HAF")->setCategory("SGPP"); $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.5)->setRight(0.25)->setLeft(0.25)->setBottom(0.5); // Bordes $BordesEncabezado = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); // Agregando imagen $objDrawingPType = new PHPExcel_Worksheet_Drawing(); $objDrawingPType->setWorksheet($objPHPExcel->setActiveSheetIndex(0)); $objDrawingPType->setName("Pareto By Type"); $objDrawingPType->setPath(Yii::app()->basePath . DIRECTORY_SEPARATOR . "../assets/img/logounitec.png"); $objDrawingPType->setCoordinates('H1'); $objDrawingPType->setOffsetX(3); $objDrawingPType->setOffsetY(0); $objDrawingPType->setWidthAndHeight(180, 90); $objDrawingPType->setResizeProportional(true); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Lista de Estudiantes'); // COMBINANDO CELDAS $objPHPExcel->setActiveSheetIndex(0)->mergeCells('C1:H1'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('C2:H2'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B5:D5'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B6:D6'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E6:G6'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H6:J6'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B7:D7'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E7:G7'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H7:J7'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B8:D8'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E8:J8'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B9:D9'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('F9:G9'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('I9:J9'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B10:J10'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B12:J12'); // EMPIEZA EMPRESA INSTITUCIÓN $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B13:J13'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B14:G14'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H14:J14'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B15:G15'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H15:J15'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B16:D16'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E16:G16'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H16:J16'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B17:D17'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E17:G17'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H17:J17'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B18:D18'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E18:G18'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H18:J18'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B19:D19'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E19:G19'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('H19:J19'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B20:J20'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B22:J28'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B29:J29'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B29:J29'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B31:J42'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B43:J43'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B45:J46'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B49:F49'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B50:F50'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('G49:J49'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('G50:J50'); // APLICANDO BORDES $objPHPExcel->getActiveSheet()->getStyle('B6:D7')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('E6:G7')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('H6:J7')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B8:D9')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('E8:J9')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B12:J13')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B14:G15')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('H14:J15')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B16:D17')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B18:D19')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('E16:G17')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('E18:G19')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('H16:J17')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('H18:J19')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B22:J28')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B31:J42')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B45:J46')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B49:F50')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('G49:J50')->applyFromArray($BordesEncabezado); // FORMATOS DE TEXTO ( SIZE, FONT , BOLD) $NegritaTamanioCatorce = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); // Negrita tamaño 14 $NormalTamanioDoce = array('font' => array('bold' => true, 'size' => 12), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); // Negrita tamaño 12 $NormalTamanioOnce = array('font' => array('bold' => true, 'size' => 11), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)); // Negrita tamaño 12 $NormalTamanioDiez = array('font' => array('bold' => true, 'size' => 10), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // Negrita tamaño 12 $NormalTamanioNueve = array('font' => array('size' => 9), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // Negrita tamaño 12 $AlieadoArriba = array('font' => array('bold' => false), 'alignment' => array('vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY)); // Negrita Tamaño 12 $Normal = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); // Negrita tamaño 12 // ESCRIBIENDO DATOS $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', "DESCRIPCIÓN DEL CARGO"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', "ESTUDIANTE EN PRÁCTICA PROFESIONAL"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', "FO-GR-002"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I4', "FECHA:"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J4', date('Y-m-d')); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B5', "1._DATOS DEL ESTUDIANTE"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B6', "APELLIDO(S)"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E6', "NOMBRE(S)"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H6', "NÚMERO DE CUENTA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B8', "CARRERA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E8', "PERÍODO DE PRÁCTICA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E9', "DESDE"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H9', "HASTA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B11', "2._ EMPRESA/INSTITUCIÓN"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B12', "NOMBRE"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B14', "COORDINADOR DE PRÁCTICA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H14', "CARGO"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B16', "DIRECCIÓN"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E16', "CIUDAD"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H16', "TELEFONO"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B18', "FAX"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E18', "E-MAIL"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H18', "WEBSITE"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B21', "3._ OBJETIVO DE CARGO"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B30', "4._ RESPONSABILIDADES"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B44', "5._ OBSERVACIONES"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B48', "5._ FIRMAS"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B49', "COORDINADOR PRÁCTICA (EMPRESA) - " . ucwords(strtolower($PracticaProfesional->asesor->NombreCompleto))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G49', "ESTUDIANTE - " . ucwords(strtolower($Estudiante->NombreCompleto))); // LLENANDO DATOS CON EL MODELO $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B7', ucwords(strtolower($Estudiante->PrimerApellido . " " . $Estudiante->SegundoApellido))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E7', ucwords(strtolower($Estudiante->Nombre))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H7', $Estudiante->NumeroDeCuenta); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B9', $Estudiante->carrera->NombreCarrera); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B9', $Estudiante->carrera->NombreCarrera); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B13', ucwords(strtolower($PracticaProfesional->asesor->usuarioEmpresa->NombreEmpresa))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B15', ucwords(strtolower($PracticaProfesional->asesor->NombreCompleto))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H15', ucwords(strtolower($PracticaProfesional->asesor->PuestoEmpresa))); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H17', "Tel: " . $PracticaProfesional->asesor->TelefonoFijo . " Cel: " . $PracticaProfesional->asesor->TelefonoCelular); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E19', $PracticaProfesional->asesor->CorreoElectronico); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E19', $PracticaProfesional->asesor->CorreoElectronico); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H19', $PracticaProfesional->asesor->usuarioEmpresa->SitioWebEmpresa); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B22', $PracticaProfesional->ObjetivoDelCargo); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B45', $PracticaProfesional->Observaciones); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B31', str_replace(",", " ", $PracticaProfesional->Reponsabilidades)); $objPHPExcel->getActiveSheet()->getStyle('B22:J28')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('B45:J46')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('B31:J42')->getAlignment()->setWrapText(true); // APLICANDO FORMATO $objPHPExcel->getActiveSheet()->getStyle('C1')->applyFromArray($NegritaTamanioCatorce); $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($NormalTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($NormalTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('I4')->applyFromArray($NormalTamanioOnce); $objPHPExcel->getActiveSheet()->getStyle('B5')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B6')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E6')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H6')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B8')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E8')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E9')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H9')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B11')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B12')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B14')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H14')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B16')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E16')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H16')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B18')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('E18')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('H18')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B21')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B30')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B44')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B48')->applyFromArray($NormalTamanioDiez); $objPHPExcel->getActiveSheet()->getStyle('B49')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('G49')->applyFromArray($NormalTamanioNueve); $objPHPExcel->getActiveSheet()->getStyle('B22')->applyFromArray($AlieadoArriba); $objPHPExcel->getActiveSheet()->getStyle('B45')->applyFromArray($AlieadoArriba); $objPHPExcel->getActiveSheet()->getStyle('B31')->applyFromArray($AlieadoArriba); $objPHPExcel->getActiveSheet()->getStyle('B7')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('E7')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('H7')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('B9')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('B13')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('B15')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('H15')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('H17')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('E19')->applyFromArray($Normal); $objPHPExcel->getActiveSheet()->getStyle('H19')->applyFromArray($Normal); //COLOREANDO EN BLANCO $objPHPExcel->getActiveSheet()->getStyle('B6:J7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('E6:G7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B6:J7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B8:D9')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('E8:J9')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B12:J13')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B14:G15')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('H14:J15')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B16:J19')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B23:J28')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); $objPHPExcel->getActiveSheet()->getStyle('B49:J50')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffff'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename= Descripción del cargo.xls'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); Yii::app()->end(); } else { $this->redirect(Yii::app()->request->urlReferrer); } }
public function checkoutCpi($examinee, $objActSheet) { $objActSheet->getDefaultRowDimension()->setRowHeight(16); $objActSheet->getDefaultColumnDimension()->setWidth(12); $objActSheet->mergeCells('A1:F1'); $objActSheet->setCellValue('A1', '青年性格问卷(CPI)测试结果'); $objActSheet->getStyle('A1')->getFont()->setBold(true); $this->position($objActSheet, 'A1'); $objActSheet->setCellValue('A2', '分类号'); $this->position($objActSheet, 'A2'); $objActSheet->mergeCells('B2:F2'); $objActSheet->setCellValue('A3', '编号'); $this->position($objActSheet, 'A3'); $objActSheet->setCellValue('B3', $examinee->number); $this->position($objActSheet, 'B3'); $objActSheet->setCellValue('C3', '姓名'); $this->position($objActSheet, 'C3'); $objActSheet->setCellValue('D3', $examinee->name); $this->position($objActSheet, 'D3'); $objActSheet->setCellValue('E3', '性别'); $this->position($objActSheet, 'E3'); $objActSheet->setCellValue('F3', $examinee->sex == "1" ? "男" : "女"); $this->position($objActSheet, 'F3'); $objActSheet->setCellValue('A4', '年龄'); $this->position($objActSheet, 'A4'); $age = floor(FactorScore::calAge($examinee->birthday, $examinee->last_login)); $objActSheet->setCellValue('B4', $age); $this->position($objActSheet, 'B4'); $objActSheet->setCellValue('C4', '职业'); $this->position($objActSheet, 'C4'); $objActSheet->setCellValue('D4', $examinee->duty); $this->position($objActSheet, 'D4'); $objActSheet->setCellValue('E4', '测试日期'); $this->position($objActSheet, 'E4'); $date = explode(' ', $examinee->last_login)[0]; $objActSheet->setCellValue('F4', $date); $this->position($objActSheet, 'F4'); $styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $objActSheet->getStyle('A2:F4')->applyFromArray($styleArray); $objActSheet->mergeCells('A5:F5'); $objActSheet->setCellValue('B6', '因子名称'); $this->position($objActSheet, 'B6'); $objActSheet->setCellValue('C6', '代号'); $this->position($objActSheet, 'C6'); $objActSheet->setCellValue('D6', '原始分'); $this->position($objActSheet, 'D6'); $objActSheet->setCellValue('E6', 'T分'); $this->position($objActSheet, 'E6'); $styleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $objActSheet->getStyle('A6:F6')->applyFromArray($styleArray); $data = CheckoutData::getCPIdata($examinee); if (empty($data)) { return; } $array1 = array('do', 'cs', 'sy', 'sp', 'sa', 'wb'); $array2 = array('re', 'so', 'sc', 'po', 'gi', 'cm'); $array3 = array('ac', 'ai', 'ie'); $array4 = array('py', 'fx', 'fe'); $objActSheet->mergeCells("A7:F7"); $objActSheet->setCellValue('A7', '第一类 人际关系适应能力的测验'); $objActSheet->getStyle('A7')->getFont()->setBold(true); $this->position($objActSheet, 'A7', 'left'); $startRow = 7; $lastRow = $startRow; foreach ($array1 as $value) { if (isset($data[$value])) { $startRow++; $lastRow = $startRow; $objActSheet->setCellValue('B' . $startRow, $data[$value]['chs_name']); $this->position($objActSheet, 'B' . $startRow); $objActSheet->setCellValue('C' . $startRow, ucwords($value)); $this->position($objActSheet, 'C' . $startRow); $objActSheet->setCellValue('D' . $startRow, $data[$value]['score']); $this->position($objActSheet, 'D' . $startRow); $objActSheet->setCellValue('E' . $startRow, $data[$value]['std_score']); $this->position($objActSheet, 'E' . $startRow); } } $objActSheet->getStyle('A7:F' . $lastRow)->applyFromArray($styleArray); $startRow++; $firstRow = $startRow; $lastRow = $startRow; $objActSheet->mergeCells('A' . $startRow . ':F' . $startRow); $objActSheet->setCellValue('A' . $startRow, '第二类 社会化、成熟度、责任心及价值观念的测验'); $objActSheet->getStyle('A' . $startRow)->getFont()->setBold(true); $this->position($objActSheet, 'A' . $startRow, 'left'); foreach ($array2 as $value) { if (isset($data[$value])) { $startRow++; $lastRow = $startRow; $objActSheet->setCellValue('B' . $startRow, $data[$value]['chs_name']); $this->position($objActSheet, 'B' . $startRow); $objActSheet->setCellValue('C' . $startRow, ucwords($value)); $this->position($objActSheet, 'C' . $startRow); $objActSheet->setCellValue('D' . $startRow, $data[$value]['score']); $this->position($objActSheet, 'D' . $startRow); $objActSheet->setCellValue('E' . $startRow, $data[$value]['std_score']); $this->position($objActSheet, 'E' . $startRow); } } $objActSheet->getStyle('A' . $firstRow . ':F' . $lastRow)->applyFromArray($styleArray); $startRow++; $firstRow = $startRow; $lastRow = $startRow; $objActSheet->mergeCells("A" . $startRow . ":F" . $startRow); $objActSheet->setCellValue('A' . $startRow, '第三类 成就能力与智能效率的测验'); $objActSheet->getStyle('A' . $startRow)->getFont()->setBold(true); $this->position($objActSheet, 'A' . $startRow, 'left'); foreach ($array3 as $value) { if (isset($data[$value])) { $startRow++; $lastRow = $startRow; $objActSheet->setCellValue('B' . $startRow, $data[$value]['chs_name']); $this->position($objActSheet, 'B' . $startRow); $objActSheet->setCellValue('C' . $startRow, ucwords($value)); $this->position($objActSheet, 'C' . $startRow); $objActSheet->setCellValue('D' . $startRow, $data[$value]['score']); $this->position($objActSheet, 'D' . $startRow); $objActSheet->setCellValue('E' . $startRow, $data[$value]['std_score']); $this->position($objActSheet, 'E' . $startRow); } } $objActSheet->getStyle('A' . $firstRow . ':F' . $lastRow)->applyFromArray($styleArray); $startRow++; $firstRow = $startRow; $lastRow = $startRow; $objActSheet->mergeCells("A" . $startRow . ":F" . $startRow); $objActSheet->setCellValue('A' . $startRow, '第四类 个人的生活态度与倾向的测验'); $objActSheet->getStyle('A' . $startRow)->getFont()->setBold(true); $this->position($objActSheet, 'A' . $startRow, 'left'); foreach ($array4 as $value) { if (isset($data[$value])) { $startRow++; $lastRow = $startRow; $objActSheet->setCellValue('B' . $startRow, $data[$value]['chs_name']); $this->position($objActSheet, 'B' . $startRow); $objActSheet->setCellValue('C' . $startRow, ucwords($value)); $this->position($objActSheet, 'C' . $startRow); $objActSheet->setCellValue('D' . $startRow, $data[$value]['score']); $this->position($objActSheet, 'D' . $startRow); $objActSheet->setCellValue('E' . $startRow, $data[$value]['std_score']); $this->position($objActSheet, 'E' . $startRow); } } $objActSheet->getStyle('A' . $firstRow . ':F' . $lastRow)->applyFromArray($styleArray); $startRow++; $startRow++; //edit bruce_w 2015-12-9 分页打印 $startRow += 20; $objActSheet->mergeCells("A" . $startRow . ":F" . $startRow); $objActSheet->setCellValue('A' . $startRow, '青年性格问卷测试(CPI)剖析图'); $this->position($objActSheet, 'A' . $startRow); $objActSheet->getStyle('A' . $startRow)->getFont()->setBold(true); $startRow++; $filePath = WordChart::scatter_horiz_Graph_cpi($data, $examinee); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setPath($filePath); $width = 15 * 39; $height = 20 * 39; $objDrawing->setWidthAndHeight($width, $height); $objDrawing->setCoordinates("A" . $startRow); $objDrawing->setWorksheet($objActSheet); }
// Create new PHPExcel object $objPHPExcel = new PHPExcel(); // Set document propertiesz $objPHPExcel->getProperties()->setCreator("Maarten Balliauw") ->setLastModifiedBy("Maarten Balliauw") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); // for exl logo $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('PHPExcel logo'); $objDrawing->setDescription('PHPExcel logo'); $objDrawing->setPath('../image/LOGO.png'); $objDrawing->setWidthAndHeight(200,100); $objDrawing->setResizeProportional(true); $objDrawing->setCoordinates('E1'); $objDrawing->setOffsetX(6); $objDrawing->setOffsetY(8); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); /** Create a new PHPExcel object 1.0 */ $activeempname=mysqli_query($con,"SELECT ULD_ID,ULD_WORKER_NAME FROM LMC_USER_LOGIN_DETAILS WHERE ULD_USERNAME='******'"); if($row=mysqli_fetch_array($activeempname)) { $activeemp=$row["ULD_ID"]; } // COMMOM DATA if($_REQUEST['option']=='COMMON_DATA') { //MEETING TOPIC