static function importCartshop($tmp_file, $file, &$out) { global $CONFIG, $ST; $dir = 'shared/php/'; include "{$dir}PHPExcel.php"; include "{$dir}PHPExcel/IOFactory.php"; PHPExcel_Settings::setLocale('ru'); if (file_ext($file) == 'xlsx') { $objReader = new PHPExcel_Reader_Excel2007(); } elseif (file_ext($file) == 'xls') { $objReader = new PHPExcel_Reader_Excel5(); } $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($tmp_file); /*$objPHPExcel->setActiveSheetIndex(0); $objWorksheet = $objPHPExcel->getActiveSheet(); $keys=array(); $keys_flg=array('category_id'=>'id', 'parent_id'=>'parentid', 'name'=>'name', 'description'=>'description', 'image_name'=>'img', 'sort_order'=>'main_sort', ); $out=array(); foreach ($objWorksheet->getRowIterator() as $row) { $data=array(); $field=array(); $field_ext=array(); $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $fld_flg=false;//Строка поля $no_user_itm=''; foreach ($cellIterator as $cell) { $val=trim(u2w($cell->getValue())); if(!$fld_flg && $val=='category_id'){ $fld_flg=true; } if($fld_flg){//Грузим поля if(isset($keys_flg[$val])){ $keys[$cell->getColumn()]=$keys_flg[$val]; } // print_r($keys); continue; } if($keys && isset($keys[$cell->getColumn()])){ if(is_integer($keys[$cell->getColumn()])){ $field["{$keys[$cell->getColumn()]}"]=$val; }else{ $field_ext["{$keys[$cell->getColumn()]}"]=$val; } } } if(!$keys){ continue; } if(!empty($field_ext['id'])){ if($field_ext['img']){ if($img=@file_get_contents('http://www.farmcosmetica.ru/image/'.$field_ext['img'])){ $img_name=preg_replace('|^data/|','',$field_ext['img']); $img_name=str_replace('/','_',$img_name); $img_name="storage/catalog/".$img_name; if(!file_exists($img_name)){ file_put_contents($img_name,$img); } $field_ext['img']="/".$img_name; }else{ $field_ext['img']=""; } } $rs=$ST->select("SELECT * FROM sc_shop_catalog WHERE id={$field_ext['id']}"); if($rs->next()){ $ST->update('sc_shop_catalog',$field_ext,"id={$field_ext['id']}"); }else{ $ST->insert('sc_shop_catalog',$field_ext); } $out[$field_ext['id']]=$field_ext; } } */ $objPHPExcel->setActiveSheetIndex(1); $objWorksheet = $objPHPExcel->getActiveSheet(); $keys = array(); $keys_flg = array('product_id' => 'id', 'categories' => 'categories', 'name' => 'name', 'quantity' => 'in_stock', 'image_name' => 'img', 'additional image names' => 'img_add', 'price' => 'price', 'date_added' => 'insert_time', 'date_modified' => 'update_time', 'viewed' => 'views', 'description' => 'html'); $out = array(); foreach ($objWorksheet->getRowIterator() as $row) { $data = array(); $field = array(); $field_ext = array(); $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $fld_flg = false; //Строка поля $no_user_itm = ''; // echo "\n"; foreach ($cellIterator as $cell) { $val = trim(u2w($cell->getValue())); if (!$fld_flg && $val == 'product_id') { //$cell->getColumn()=='B' && $fld_flg = true; } if ($fld_flg) { //Грузим поля if (isset($keys_flg[$val])) { $keys[$cell->getColumn()] = $keys_flg[$val]; } // print_r($keys); continue; } if ($keys && isset($keys[$cell->getColumn()])) { if (is_integer($keys[$cell->getColumn()])) { $field["{$keys[$cell->getColumn()]}"] = $val; } else { $field_ext["{$keys[$cell->getColumn()]}"] = $val; } } } if (!$keys) { continue; } if (!empty($field_ext['id'])) { if ($field_ext['img']) { if ($img = @file_get_contents('http://www.farmcosmetica.ru/image/' . iconv('cp1251', 'utf-8', $field_ext['img']))) { $img_name = preg_replace('|^data/|', '', $field_ext['img']); $img_name = str_replace('/', '_', $img_name); $img_name = "storage/catalog/goods/" . $img_name; if (!file_exists($img_name)) { file_put_contents($img_name, $img); } $field_ext['img'] = "/" . $img_name; } else { $field_ext['img'] = ""; } } if ($field_ext['img_add']) { $images = explode(',', $field_ext['img_add']); $field_ext['img_add'] = array(); foreach ($images as $i) { if ($img = @file_get_contents('http://www.farmcosmetica.ru/image/' . iconv('cp1251', 'utf-8', $i))) { $img_name = preg_replace('|^data/|', '', $i); $img_name = str_replace('/', '_', $img_name); $img_name = "storage/catalog/goods/" . $img_name; if (!file_exists($img_name)) { file_put_contents($img_name, $img); } $field_ext['img_add'][] = "/" . $img_name; } } $field_ext['img_add'] = implode(',', $field_ext['img_add']); } $rs = $ST->select("SELECT * FROM sc_shop_item WHERE id={$field_ext['id']}"); $categories = $field_ext['categories']; unset($field_ext['categories']); if ($rs->next()) { $ST->update('sc_shop_item', $field_ext, "id={$field_ext['id']}"); } else { $ST->insert('sc_shop_item', $field_ext); } // if($categories){ // $categories=explode(',',$categories); // $ST->delete('sc_shop_item2cat',"itemid={$field_ext['id']}"); // foreach ($categories as $c) { // $ST->insert('sc_shop_item2cat',array('itemid'=>$field_ext['id'],'catid'=>$c)); // } // // } // if($field_ext['manufacturer']){ // $rs=$ST->select("SELECT * FROM sc_manufacturer WHERE name='".SQL::slashes($field_ext['manufacturer'])."'"); // if($rs->next()){ // $man_id=$rs->getInt('id'); // }else{ // $man_id=$ST->insert("sc_manufacturer",array('name'=>$field_ext['manufacturer'])); // } // $ST->update('sc_shop_item',array('manufacturer_id'=>$man_id),"id={$field_ext['id']}"); // // // } // if($field_ext['Объем']){ // $rs=$ST->select("select * FROM sc_shop_prop_val WHERE prop_id=1 AND item_id={$field_ext['id']}"); // if($rs->next()){ // $ST->update('sc_shop_prop_val',array('value'=>$field_ext['Объем']),"id={$rs->get('id')}"); // }else{ // $ST->insert('sc_shop_prop_val',array('value'=>$field_ext['Объем'],'prop_id'=>1,"item_id"=>$field_ext['id'])); // } // } $out[$field_ext['id']] = $field_ext; } } }
$nombrearchivo = "SAAC_" . quitarSimbolos($materiaabreviado) . "_" . quitarSimbolos($cursoabreviado) . "_" . quitarSimbolos($periodoabreaviado) . "_" . date("Y"); $valores = array("NombreArchivo" => "'{$nombrearchivo}'", "Codigo" => "'" . md5($codigocasilleros) . "'", "CodDocenteMateriaCurso" => "'{$codigodocentemateriacurso}'", "CodCasilleros" => "'{$codigocasilleros}'", "CodDocente" => "'{$codigodocente}'", "CodMateria" => "'{$codigomateria}'", "CodCurso" => "'{$codigocurso}'", "Direccion" => "'{$direccion}'", "Ubicacion" => "'Descarga'"); $registronotasexcel->insertarRegistro($valores); /** Mostrar Errores */ /*error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE);*/ date_default_timezone_set('America/La_Paz'); /** Incluir PHPExcel */ require_once 'Classes/PHPExcel.php'; require_once 'Classes/PHPExcel/Worksheet/Drawing.php'; require_once 'funciones/funciones.php'; $nfx = adicionar("A", $cantidadcasilleros + 4 + 2); $doc = new PHPExcel(); $locale = 'es'; $validLocale = PHPExcel_Settings::setLocale($locale); //Propiedades del Documento $doc->getProperties()->setCreator("Desarrollado por Ronald Franz Nina Layme - Cel:73230568")->setLastModifiedBy("Desarrollado por Ronald Franz Nina Layme - Cel:73230568")->setTitle("Registro de Notas - Sistema Académico Administrativo para Colegios Desarrollado por Ronald Franz Nina Layme - Cel:73230568")->setSubject("Sistema Académico Administrativo para Colegios")->setDescription("Sistema Académico Administrativo para Colegios, Registro de Notas")->setKeywords("Sistema Académico Administrativo para Colegios")->setCategory("Sistema Académico Administrativo para Colegios"); $doc->getActiveSheet()->getPageSetup()->setOrientation('landscape'); $doc->getActiveSheet()->getPageSetup()->setPaperSize(1); $doc->getActiveSheet()->getPageMargins()->setRight(0.39); $doc->getActiveSheet()->getPageMargins()->setLeft(0.39); $doc->getActiveSheet()->getPageSetup()->setHorizontalCentered(true); //Insertar Logo $oLogo = new PHPExcel_Worksheet_Drawing(); $oLogo->setName('Logo'); $oLogo->setDescription('Logo'); $oLogo->setPath("../../imagenes/logos/" . $archivologo); $oLogo->setHeight(80); $oLogo->setCoordinates('B1'); $oLogo->setWorksheet($doc->getActiveSheet());
/** * Create $this->objPHPExcel * Set document name, title, author and locale. * * @param string $name Document name (not used as filename) * @param string $title Document title * @param string $author Author name * @param string $locale Document locale (default fr_FR) * @return ExcelWriter * @throws \Exception */ public function createDocument($name = '', $title = '', $author = '', $locale = 'fr_FR') { $this->objPHPExcel = new \PHPExcel(); $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; $cacheSettings = array(' memoryCacheSize ' => '1024MB'); if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings)) { throw new \Exception('Unable to change cache storage method. Please contact your administrator.'); } $this->objPHPExcel->getProperties()->setCreated($author); $this->objPHPExcel->getProperties()->setLastModifiedBy($author); $this->objPHPExcel->getProperties()->setTitle($title); $this->objPHPExcel->getProperties()->setCreator($author); $validLocale = \PHPExcel_Settings::setLocale($locale); $this->gotoSheetByIndex(0); $this->rowIndex = 1; $this->isLoaded = true; return $this; }
/** * */ public function __construct($locale) { //Create the new instance of the document: $this->setDocument(new sfPHPExcel()); PHPExcel_Settings::setLocale($locale); $this->_locale = $locale; }
/** * @param ReportDataModel $model * @param PersonsRecord $person * @return string * @throws \PHPExcel_Exception * @throws \PHPExcel_Reader_Exception */ private function _create(ReportDataModel $model, PersonsRecord $person = null) { $formatter = \Yii::$app->formatter; \PHPExcel_Shared_ZipStreamWrapper::register(); \PHPExcel_Shared_String::buildCharacterSets(); \PHPExcel_Settings::setLocale('ru'); $moneyFormat = '## ### ##0"грн."'; $fillColor = 'eaeaea'; $objPHPExcel = new \PHPExcel(); $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(12); // $objPHPExcel->getProperties() // ->setCreator('1') // ->setLastModifiedBy("Maarten Balliauw") // ->setTitle("Товары") // ->setSubject("Office Document"); // ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") // ->setCategory("Result file"); //Лист с данными - Data $objPHPExcel->getActiveSheet()->setTitle('Data')->setCellValue('A1', $model->getAttributeLabel('sumIncome'))->setCellValue('B1', $model->sumIncome)->setCellValue('A2', $model->getAttributeLabel('sumExpense'))->setCellValue('B2', $model->sumExpense)->setCellValue('A3', $model->getAttributeLabel('income'))->setCellValue('B3', $model->income)->setCellValue('A4', $model->getAttributeLabel('orderCount'))->setCellValue('B4', $model->orderCount)->setCellValue('C4', 'учитываются все доходы кроме внесения залога и регистрации')->setCellValue('A5', $model->getAttributeLabel('orderMin'))->setCellValue('B5', $model->orderMin)->setCellValue('A6', $model->getAttributeLabel('orderMax'))->setCellValue('B6', $model->orderMax)->setCellValue('A7', $model->getAttributeLabel('orderAvg'))->setCellValue('B7', $model->orderAvg)->setCellValue('A8', $model->getAttributeLabel('cardRegistrationCount'))->setCellValue('B8', $model->cardRegistrationCount)->setCellValue('A9', $model->getAttributeLabel('cardGiftRegistrationCount'))->setCellValue('B9', $model->cardGiftRegistrationCount)->setCellValue('A10', $model->getAttributeLabel('totalIncomeFromGiftCardRegistration'))->setCellValue('B10', $model->totalIncomeFromGiftCardRegistration)->setCellValue('A11', $model->getAttributeLabel('countServiceCardWasCreated'))->setCellValue('B11', $model->countServiceCardWasCreated)->setCellValue('A12', $model->getAttributeLabel('totalIncomeFromCardRegistration'))->setCellValue('B12', $model->totalIncomeFromCardRegistration)->setCellValue('A13', $model->getAttributeLabel('averageRegistrationChargeAmount'))->setCellValue('B13', $model->averageRegistrationChargeAmount)->setCellValue('C13', 'Считается по сумме которая пападает на счет, без залога')->setCellValue('A14', $model->getAttributeLabel('chargeCount'))->setCellValue('B14', $model->chargeCount)->setCellValue('A15', $model->getAttributeLabel('totalCharge'))->setCellValue('B15', $model->totalCharge)->setCellValue('A16', $model->getAttributeLabel('averageChargeAmount'))->setCellValue('B16', $model->averageChargeAmount)->setCellValue('A18', $model->getAttributeLabel('totalMoneyBackFromCard'))->setCellValue('B18', $model->totalMoneyBackFromCard)->setCellValue('A19', $model->getAttributeLabel('currentBonusesSpend'))->setCellValue('B19', $model->currentBonusesSpend)->setCellValue('A20', $model->getAttributeLabel('currentBonusesEmitted'))->setCellValue('B20', $model->currentBonusesEmitted)->setCellValue('A21', $model->getAttributeLabel('totalCardDebt'))->setCellValue('B21', $model->totalCardDebt)->setCellValue('A22', $model->getAttributeLabel('totalSpend'))->setCellValue('B22', $model->totalSpend)->setCellValue('A23', $model->getAttributeLabel('totalSpendWithCard'))->setCellValue('B23', $model->totalSpendWithCard)->setCellValue('A24', $model->getAttributeLabel('currentTicketsEmitted'))->setCellValue('B24', $model->currentTicketsEmitted)->setCellValue('A25', $model->getAttributeLabel('currentTicketsSpend'))->setCellValue('B25', $model->currentTicketsSpend)->setCellValue('A26', $model->getAttributeLabel('currentTicketsSpend'))->setCellValue('B26', $model->totalTickets)->setCellValue('A29', $model->getAttributeLabel('fromDate'))->setCellValue('B29', $formatter->asDatetime($model->fromDate, 'medium'))->setCellValue('A30', $model->getAttributeLabel('toDate'))->setCellValue('B30', $formatter->asDatetime($model->toDate, 'medium')); $objPHPExcel->getActiveSheet()->getStyle('B1:B25')->getNumberFormat()->setFormatCode('# ##0'); //"грн." $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15); $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); //1 Лист - Статистика $activeSheet = $objPHPExcel->createSheet(0); $activeSheet->getDefaultColumnDimension()->setWidth(15); $activeSheet->getDefaultRowDimension()->setRowHeight(20); $activeSheet->getStyle('B2:B32')->getNumberFormat()->setFormatCode($moneyFormat); $activeSheet->getStyle('C25:C30')->getNumberFormat()->setFormatCode($moneyFormat); $activeSheet->getStyle('C16')->getNumberFormat()->setFormatCode($moneyFormat); $activeSheet->getStyle('B7')->getNumberFormat()->setBuiltInFormatCode(1); $activeSheet->getStyle('B12')->getNumberFormat()->setBuiltInFormatCode(1); $activeSheet->getStyle('B13')->getNumberFormat()->setBuiltInFormatCode(1); $activeSheet->getStyle('B15')->getNumberFormat()->setBuiltInFormatCode(1); $activeSheet->getStyle('B20')->getNumberFormat()->setBuiltInFormatCode(1); $activeSheet->setTitle('Статистика' . ($person !== null ? ' для ' . $person->fullName : ''))->mergeCells('A1:B1')->setCellValue('A1', $formatter->asDatetime($model->fromDate, 'long') . ' - ' . $formatter->asDatetime($model->toDate, 'long'))->mergeCells('A2:B2')->setCellValue('A2', 'Общая статистика по залу')->getStyle('A2:B2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor); $activeSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $activeSheet->setCellValue('A3', $model->getAttributeLabel('sumIncome'))->setCellValue('B3', $model->sumIncome)->setCellValue('A4', $model->getAttributeLabel('sumExpense'))->setCellValue('B4', $model->sumExpense)->setCellValue('A5', $model->getAttributeLabel('income'))->setCellValue('B5', $model->income)->mergeCells('A6:B6')->setCellValue('A6', 'Статистика по чекам')->getStyle('A6:B6')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor); $activeSheet->setCellValue('A7', $model->getAttributeLabel('orderCount'))->setCellValue('B7', $model->orderCount)->setCellValue('A8', $model->getAttributeLabel('orderMin'))->setCellValue('B8', $model->orderMin)->setCellValue('A9', $model->getAttributeLabel('orderMax'))->setCellValue('B9', $model->orderMax)->setCellValue('A10', $model->getAttributeLabel('orderAvg'))->setCellValue('B10', $model->orderAvg)->mergeCells('A11:B11')->setCellValue('A11', 'Статистика по картам')->getStyle('A11:B11')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor); $activeSheet->setCellValue('A12', $model->getAttributeLabel('cardRegistrationCount'))->setCellValue('B12', $model->cardRegistrationCount)->setCellValue('A13', $model->getAttributeLabel('cardGiftRegistrationCount'))->setCellValue('B13', $model->cardGiftRegistrationCount)->setCellValue('A14', $model->getAttributeLabel('totalIncomeFromGiftCardRegistration'))->setCellValue('B14', $model->totalIncomeFromGiftCardRegistration)->setCellValue('A15', $model->getAttributeLabel('countServiceCardWasCreated'))->setCellValue('B15', $model->countServiceCardWasCreated)->setCellValue('A16', $model->getAttributeLabel('totalIncomeFromCardRegistration'))->setCellValue('B16', $model->totalIncomeFromCardRegistration)->setCellValue('A17', $model->getAttributeLabel('totalCharge'))->setCellValue('B17', $model->totalCharge)->mergeCells('C16:C17')->setCellValue('C16', $model->totalIncomeFromCardRegistration + $model->totalCharge)->setCellValue('A18', $model->getAttributeLabel('averageRegistrationChargeAmount'))->setCellValue('B18', $model->averageRegistrationChargeAmount)->setCellValue('A20', $model->getAttributeLabel('chargeCount'))->setCellValue('B20', $model->chargeCount)->setCellValue('A19', $model->getAttributeLabel('averageChargeAmount'))->setCellValue('B19', $model->averageChargeAmount)->setCellValue('A21', $model->getAttributeLabel('totalMoneyBackFromCard'))->setCellValue('B21', $model->totalMoneyBackFromCard)->mergeCells('A22:B22')->setCellValue('A22', 'Статистика по бонусам')->getStyle('A22:B22')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor); $activeSheet->setCellValue('A23', $model->getAttributeLabel('currentBonusesEmitted'))->setCellValue('B23', $model->currentBonusesEmitted)->setCellValue('A24', $model->getAttributeLabel('currentBonusesSpend'))->setCellValue('B24', $model->currentBonusesSpend); $activeSheet->mergeCells('A25:B25')->setCellValue('A25', 'Статистика по залу')->getStyle('A25:B25')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor); $activeSheet->setCellValue('A26', $model->getAttributeLabel('totalSpend'))->setCellValue('B26', $model->totalSpend)->setCellValue('A27', $model->getAttributeLabel('totalSpendWithCard'))->setCellValue('B27', $model->totalSpendWithCard)->setCellValue('A28', $model->getAttributeLabel('totalCardDebt'))->setCellValue('B28', $model->totalCardDebt)->setCellValue('A29', $model->getAttributeLabel('totalCardBonusDebt'))->setCellValue('B29', $model->totalCardBonusDebt)->setCellValue('A30', $model->getAttributeLabel('totalCardDebtPlusBonus'))->setCellValue('B30', $model->totalCardDebtPlusBonus); // ->setCellValue('C29', $model->currentCardDebt)->setCellValue('D29', $model->getAttributeLabel('currentCardDebt')) // ->setCellValue('C30', $model->currentCardDebtPlusBonus)->setCellValue('D30', $model->getAttributeLabel('currentCardDebtPlusBonus')); $activeSheet->mergeCells('A32:B32')->setCellValue('A32', 'Статистика по билетам')->getStyle('A32:B32')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor); $activeSheet->setCellValue('A33', $model->getAttributeLabel('currentTicketsEmitted'))->setCellValue('B33', $model->currentTicketsEmitted)->setCellValue('A34', $model->getAttributeLabel('currentTicketsSpend'))->setCellValue('B34', $model->currentTicketsSpend)->setCellValue('A35', $model->getAttributeLabel('totalTickets'))->setCellValue('B35', $model->totalTickets); $activeSheet->getStyle('C16:C17')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $activeSheet->getStyle('C16:C17')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $activeSheet->getColumnDimension('A')->setAutoSize(true); $activeSheet->getColumnDimension('B')->setAutoSize(true); $activeSheet->getStyle('A1:B1')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_NONE]); $activeSheet->getStyle('A2:B2')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]); $activeSheet->getStyle('A6:B6')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]); $activeSheet->getStyle('A11:B11')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]); $activeSheet->getStyle('A22:B22')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]); $activeSheet->getStyle('A25:B25')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]); $activeSheet->getStyle('A32:B32')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]); $activeSheet->getStyle('B4')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]); $activeSheet->getStyle('C16')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]); $activeSheet->getStyle('B22')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]); $activeSheet->getStyle('B28:C30')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]); //2 Лист - Статистика по объетам $objectsSheet = $objPHPExcel->createSheet(1); $objectsSheet->setTitle('По Объектам')->setCellValue('A2', 'Адрес')->setCellValue('B2', 'Объект')->setCellValue('C2', 'Всего'); $objectsSheet->getStyle('A2:Z2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor); $objectsSheet->getStyle('A2:Z2')->getBorders()->applyFromArray(['bottom' => ['style' => \PHPExcel_Style_Border::BORDER_MEDIUM]]); if (!empty($model->objectsByDays)) { $endObjectRow = count($model->objects) + 3; $values = []; $objectsSum = 0; foreach ($model->objects as $objID => $value) { $values[] = [$value['HardID'], $value['Name'], $value['cash']]; $objectsSum += $value['cash']; } $values[] = ['', 'Итого', $objectsSum]; $objectsSheet->fromArray($values, NULL, 'A3'); /** * В массиве objectsByDays расположены ВСЕ существующие объекты в отсортированном порядке так же как и в objects */ $colN = 3; foreach ($model->objectsByDays as $day => $array) { // $columnName = array_shift($cols); $columnName = PHPExcel_Cell::stringFromColumnIndex($colN++); if (!$columnName) { break; } $objectsSheet->setCellValue($columnName . '2', $day); $column = []; $objtotal = 0; $column[] = [$day]; foreach ($array as $oID => $oStat) { $objtotal += $oStat['cash']; $column[] = [$oStat['cash']]; } $column[] = [$objtotal]; $objectsSheet->fromArray($column, 0, $columnName . '2', true); $objectsSheet->getColumnDimension($columnName)->setAutoSize(true); } if (isset($columnName)) { $objectsSheet->getStyle("A2:{$columnName}2")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objectsSheet->getStyle("A2:{$columnName}2")->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]); $objectsSheet->getStyle("A{$endObjectRow}:{$columnName}{$endObjectRow}")->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]); $objectsSheet->getStyle("C2:{$columnName}" . $endObjectRow)->getNumberFormat()->setFormatCode('### ### ##0"грн."'); $objectsSheet->getColumnDimension('A')->setAutoSize(true); $objectsSheet->getColumnDimension('B')->setAutoSize(true); $objectsSheet->getColumnDimension('C')->setAutoSize(true); } } //3 Лист - Услуги $servicesSheet = $objPHPExcel->createSheet(2); $servicesSheet->setTitle('По Услугам')->setCellValue('B2', 'Услуга')->setCellValue('C2', 'Сумма'); $servicesSheet->getStyle('A2:C2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor); $servicesSheet->getStyle('A2:C2')->getBorders()->applyFromArray(['bottom' => ['style' => \PHPExcel_Style_Border::BORDER_MEDIUM]]); $servicesSheet->getStyle('A2:E2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $servicesSheet->getStyle('A2:C2')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]); $endObjectRow = count($model->soldService) + 3; $servicesSheet->getStyle("A{$endObjectRow}:C{$endObjectRow}")->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]); $serviceValues = []; $servicesSum = 0; $_row = 0; foreach ($model->soldService as $rowId => $value) { $serviceValues[] = [++$_row, $value['Comment'], $value['cash']]; $servicesSum += $value['cash']; } $serviceValues[] = ['', 'Итого', $servicesSum]; $servicesSheet->fromArray($serviceValues, NULL, 'A3'); $servicesSheet->getColumnDimension('A')->setAutoSize(true); $servicesSheet->getColumnDimension('B')->setAutoSize(true); $servicesSheet->getColumnDimension('C')->setAutoSize(true); $servicesSheet->getStyle('C2:C1000')->getNumberFormat()->setFormatCode('# ##0"грн."'); //4 Лист - Статьи $actionsSheet = $objPHPExcel->createSheet(3); $actionsSheet->setTitle('По Статьям')->setCellValue('B2', 'Статья')->setCellValue('C2', '')->setCellValue('D2', 'Количество')->setCellValue('E2', 'Сумма'); $actionsSheet->getStyle('A2:E2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor); $actionsSheet->getStyle('A2:E2')->getBorders()->applyFromArray(['bottom' => ['style' => \PHPExcel_Style_Border::BORDER_MEDIUM]]); $actionsSheet->getStyle('B2:E2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $actionsSheet->getStyle('C2:C100')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $actionsSheet->getStyle('D2:D100')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $actionsSheet->getStyle('A2:E2')->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true, 'underline' => \PHPExcel_Style_Font::UNDERLINE_DOUBLE]); $endRow = count($model->actions) + 3; $actionsSheet->getStyle("A{$endRow}:E{$endRow}")->getFont()->applyFromArray(['name' => 'Arial', 'bold' => true]); $actionsValues = []; $actionsSum = 0; $_row = 0; foreach ($model->actions as $rowId => $value) { if ($value['Type'] === FinanceClassRecord::TYPE_OUT) { $value['cash'] = $value['cash'] * -1; } $actionsValues[] = [++$_row, $value['Name'], $value['Type'] === FinanceClassRecord::TYPE_OUT ? '-' : '+', $value['count'], $value['cash']]; $actionsSum += $value['cash']; } $actionsValues[] = ['', '', '', 'Итого', $actionsSum]; $actionsSheet->fromArray($actionsValues, NULL, 'A3'); $actionsSheet->getColumnDimension('A')->setAutoSize(true); $actionsSheet->getColumnDimension('B')->setAutoSize(true); $actionsSheet->getColumnDimension('C')->setAutoSize(true); $actionsSheet->getColumnDimension('D')->setAutoSize(true); $actionsSheet->getColumnDimension('E')->setAutoSize(true); $actionsSheet->getStyle('E2:E1000')->getNumberFormat()->setFormatCode('# ##0"грн."'); //Фокус на листе № $objPHPExcel->setActiveSheetIndex(0); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->setPreCalculateFormulas(false); //Создание файла $file = tempnam(sys_get_temp_dir(), 'excel'); $objWriter->save($file); return $file; }
public function createXlsxPdf($reportId, $arReportParams){ $templFile = 'template-2.xlsx'; $xlsxOutputFileName = $arReportParams['excel_file']; $row = 15; // первая строка таблицы данных // Инициализация PHPExcel $inputFileName = "templates/{$templFile}"; // Файл шаблона $objPHPExcel = PHPExcel_IOFactory::load($inputFileName); // Загружаем шаблон $locale = 'ru_ru'; // Локаль русская PHPExcel_Settings::setLocale($locale); // устанавливаем локаль $sheet = $objPHPExcel->getActiveSheet(); // устанавливаем активный лист // Дополняем массив с данными по отчету данными по объекту отчета // и сортированным и группированным массивом записей $arReportParams = $this->expandReportData($arReportParams); // Заполняем отчет $sheet->setCellValue('C5', $reportId); $sheet->setCellValue('C6', $reportId); $sheet->setCellValue('C7', "с {$arReportParams['first_day']} по {$arReportParams['last_day']}"); $sheet->setCellValue('I5', $arReportParams['create_date']); $sheet->setCellValue('C9', $arReportParams['object_name']); $sheet->setCellValue('C10', $arReportParams['object_address']); $sheet->setCellValue('C11', $arReportParams['object_phone']); $sheet->setCellValue('C12', $arReportParams['object_email']); // выводим записи по строкам начиная с $row // Вставлять самую первую строку нужно указывая $row + 1, В конце удалим лишние foreach ($arReportParams['sortedItems'] as $key => $value) { $sheet->insertNewRowBefore($row + 1, 1); // Вставляем пустую строку в таблицу // Начало группы записей по ОДНОЙ задаче // Выводим Строку с альтернативным названием Задачи if ($value['id'] > 0) { $sheet->mergeCells("A{$row}:I{$row}"); // объединение ячеек $sheet->getStyle("A{$row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID) ->getStartColor()->setARGB('A7C0DC'); $sheet->getStyle("A{$row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $str = $value['objReportName']; $h = $this->getXlsxRowHeight($str, 100, 15); // Вычисляем высоту строки $sheet->getRowDimension($row)->setRowHeight($h); $sheet->getStyle("B{$row}")->getAlignment()->setWrapText(true); // переносить по словам $sheet->setCellValue("A{$row}", $str); $row++; } // Выводим записи по ОДНОЙ задаче $itemNum = 1; foreach ($value['items'] as $index => $item) { $sheet->insertNewRowBefore($row + 1, 1); // Вставляем пустую строку в таблицу $sheet->mergeCells("B{$row}:G{$row}"); // объединение ячеек $sheet->getStyle("B{$row}")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); $str = $item['item_action'] . "-\n" . $item['item_result']; $h = $this->getXlsxRowHeight($str, 150, 15); $sheet->getRowDimension($row)->setRowHeight($h); $sheet->getStyle("B{$row}")->getAlignment()->setWrapText(true); // переносить по словам $sheet->setCellValue("A{$row}", $itemNum); $sheet->setCellValue("B{$row}", $str); $sheet->setCellValue("H{$row}", $item['correct_item_date']); $sheet->setCellValue("I{$row}", $item['item_elapsed_hr'] ." ч. " . $item['item_elapsed_min'] . " мин."); $row++; $itemNum++; } } $sheet->removeRow($row, 3); $hours = floor($arReportParams['hours']) . " ч. " . intval((round($arReportParams['hours'], 2) - floor($arReportParams['hours'])) * 60) . " мин."; $sheet->setCellValue("I{$row}", $hours); // Запись xlxs в файл $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save("reports/{$xlsxOutputFileName}"); // Записываем в файл // //Инициализация PDF Creator // $pdfOutputFileName = $arReportParams['pdf_file']; // $rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF; // $rendererLibrary = 'mpdf'; // Папка с библиотекой // $rendererLibraryPath = $_SERVER['DOCUMENT_ROOT'] . '/apps/vendor/appotter/' . $rendererLibrary; // if (!PHPExcel_Settings::setPdfRenderer( // $rendererName, // $rendererLibraryPath // ) // ) { // die('Please set the $rendererName and $rendererLibraryPath values' . // PHP_EOL . ' as appropriate for your directory structure'); // } // // Создание PDF и запись в файл // $objWriter = new PHPExcel_Writer_PDF($objPHPExcel); // $objWriter->save("reports/{$pdfOutputFileName}"); // //Вызов родного PDF creator для примера // //Saves file on the server as 'filename.pdf' // $mpdf = new mPDF(); // $mpdf->WriteHTML($html); // $mpdf->Output('filename.pdf', 'F'); }