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
