public function actionExcel($id) { $po = $this->loadModel($id); $poItemCriteria = new CDbCriteria(); $poItemCriteria->with = array('material'); $poItemCriteria->compare('po_number', $id); $poItemDataProvider = new CActiveDataProvider('PoItems', array('criteria' => $poItemCriteria, 'pagination' => false)); Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); $objReader = PHPExcel_IOFactory::createReader('Excel5'); $objPHPExcel = $objReader->load(Yii::app()->basePath . DIRECTORY_SEPARATOR . 'data' . DIRECTORY_SEPARATOR . "templates" . DIRECTORY_SEPARATOR . "PO_Template.xls"); $objPHPExcel->getProperties()->setCreator(Yii::app()->user->name)->setLastModifiedBy(Yii::app()->user->name)->setTitle("PO Order-" . $id); //->setSubject("Office 2007 XLSX Test Document") //->setDescription("Sales Order#") //->setKeywords("office 2007 openxml php") //->setCategory("Test result file"); // Add the data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', $po->po_number)->setCellValue('C3', Yii::app()->dateFormatter->formatDateTime($po->maturity_date, "short", null))->setCellValue('C4', Yii::app()->dateFormatter->formatDateTime($po->created, "short", null))->setCellValue('C5', Yii::app()->dateFormatter->formatDateTime($po->updated, "short", null))->setCellValue('E2', $po->comp->name)->setCellValue('E3', $po->contact)->setCellValue('E4', $po->contact_telephone)->setCellValue('C6', $po->paymentTerm->description)->setCellValue('C7', $po->is_open ? "Open" : "Closed")->setCellValue('A8', "Comments:\r" . $po->comments); $row = 13; //$i = 1; // Write the sale items now $items = $poItemDataProvider->getData(); foreach ($items as $item) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B" . $row, $item->material->cat->description . "-" . $item->material->description)->setCellValue("D" . $row, $item->qty)->setCellValue("E" . $row, $item->qty_units)->setCellValue("F" . $row, $item->unit_price)->setCellValue("G" . $row, $item->price_units)->setCellValue("H" . $row, $item->qty_recieved)->setCellValue("I" . $row, $item->qty_diff); //$i++; $row++; } $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); // Redirect output to a client’s web browser //header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename=' . '"PurchaseOrder-' . $id . '.xls"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); }
public function actionExcel() { Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); //$objReader = PHPExcel_IOFactory::createReader('Excel5'); //$objPHPExcel = $objReader->load(Yii::app()->basePath . DIRECTORY_SEPARATOR . 'data' . DIRECTORY_SEPARATOR."templates".DIRECTORY_SEPARATOR."PO_Template.xls"); $data = $_SESSION['reportdata-excel']; $data->setPagination(false); //$criteria = $_SESSION['reportcriteria-excel']; $objPHPExcel->getProperties()->setCreator(Yii::app()->user->name)->setLastModifiedBy(Yii::app()->user->name)->setTitle("Report"); //->setSubject("Office 2007 XLSX Test Document") //->setDescription("Sales Order#") //->setKeywords("office 2007 openxml php") //->setCategory("Test result file"); $row = 3; $i = 1; // Write the sale items now $items = $data->getData(); foreach ($items as $item) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B" . $row, $i)->setCellValue("C" . $row, $item["compName"])->setCellValue("D" . $row, $item["shippedSum"]); $i++; $row++; } $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); // Redirect output to a client’s web browser //header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="Report.xls"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); }
public function init() { $this->productRepository = new ProductRepository(); $this->objPHPExcel = XPHPExcel::createPHPExcel(); $this->activeList = $this->objPHPExcel->setActiveSheetIndex(0); $this->activeRow = 1; $this->activeLevel = 0; }
public function actionExportExcel() { $id = Yii::app()->request->getParam('id'); $model = $this->loadModel($id); $usuario = IdentificacionUsuario::model()->findByAttributes(array('checklist_id' => $id)); $informacionEquipo = InformacionEquipo::model()->findByAttributes(array('checklist_id' => $id)); $impresora = Impresora::model()->findByAttributes(array('checklist_id' => $id)); $foto = Foto::model()->findByAttributes(array('checklist_id' => $id)); $migracion = Migracion::model()->findByAttributes(array('checklist_id' => $id)); $configuracionRed = ConfiguracionRed::model()->findByAttributes(array('checklist_id' => $id)); $tareas = ChecklistHasTarea::model()->findAllByAttributes(array('checklist_id' => $id)); Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); $sheet = $objPHPExcel->getActiveSheet(0); $sheet->setCellValueByColumnAndRow(1, 2, "Checklist Usuario Final"); $sheet->mergeCells('B2:E2'); //INFORMACION USUARIO $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B3:E3')->setCellValue('B3', 'Informacion Usuario')->setCellValue('B4', 'Nombre')->setCellValue('C4', $usuario->nombre)->setCellValue('D4', 'Cliente')->setCellValue('E4', isset($model->cliente->nombre) ? $model->cliente->nombre : '')->setCellValue('B5', 'Departamento')->setCellValue('C5', isset($model->cliente->departamento->nombre) ? $model->cliente->departamento->nombre : '')->setCellValue('D5', 'Email')->setCellValue('E5', $usuario->email)->setCellValue('B6', 'Telefono')->setCellValue('C6', $usuario->telefono)->setCellValue('D6', 'Region')->setCellValue('E6', isset($model->cliente->region->nombre) ? $model->cliente->region->nombre : '')->setCellValue('B7', 'Comuna')->setCellValue('C7', isset($model->cliente->comuna->nombre) ? $model->cliente->comuna->nombre : '')->setCellValue('D7', 'Direccion')->setCellValue('E7', isset($model->cliente->direccion) ? $model->cliente->direccion : ''); $bold = array('font' => array('color' => array('rgb' => '000000'), 'bold' => true)); $background = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'ffffff'))); $background2 = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '2644a0')), 'font' => array('color' => array('rgb' => 'ffffff'))); $objPHPExcel->getActiveSheet(0)->getStyle('B4:E32')->applyFromArray($background); $objPHPExcel->getActiveSheet(0)->getStyle('B4:B32')->applyFromArray($bold); $objPHPExcel->getActiveSheet(0)->getStyle('D4:D32')->applyFromArray($bold); $objPHPExcel->getActiveSheet(0)->getStyle('B3:E3')->applyFromArray($background2); $objPHPExcel->getActiveSheet(0)->getStyle('B9:E9')->applyFromArray($background2); $objPHPExcel->getActiveSheet(0)->getStyle('B13:E13')->applyFromArray($background2); $objPHPExcel->getActiveSheet(0)->getStyle('B17:E17')->applyFromArray($background2); $objPHPExcel->getActiveSheet(0)->getStyle('B20:E20')->applyFromArray($background2); $objPHPExcel->getActiveSheet(0)->getStyle('B26:E26')->applyFromArray($background2); //INFORMACION EQUIPO $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B9', 'Informacion Equipo')->setCellValue('B10', 'Ram')->setCellValue('C10', $informacionEquipo->ram)->setCellValue('D10', 'Disco')->setCellValue('E10', $informacionEquipo->disco)->setCellValue('B11', 'Cpu')->setCellValue('C11', isset($informacionEquipo->cpu->modelo) ? $informacionEquipo->cpu->modelo : '')->setCellValue('D11', 'Monitor')->setCellValue('E11', isset($informacionEquipo->monitor->modelo) ? $informacionEquipo->monitor->modelo : ''); //IMPRESORA $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B13', 'Impresora')->setCellValue('B14', 'Puerto')->setCellValue('C14', $impresora->puerto)->setCellValue('D14', 'Pdfcmon')->setCellValue('E14', $impresora->pdfcmon)->setCellValue('B15', 'Usb001')->setCellValue('C15', $impresora->usb001)->setCellValue('D15', 'Ip')->setCellValue('E15', $impresora->ip); //SISTEMA OPERATIVO $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B17', 'Sistema Operativo')->setCellValue('B18', 'Sistema Operativo')->setCellValue('C18', isset($model->sistemaOperativo->version) ? $model->sistemaOperativo->version : ''); //CONFIGURACION RED $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B20', 'Configuracion Red')->setCellValue('B21', 'Usuario')->setCellValue('C21', $configuracionRed->usuario)->setCellValue('D21', 'Dominio')->setCellValue('E21', $configuracionRed->dominio)->setCellValue('B22', 'Nombre Maquina')->setCellValue('C22', $configuracionRed->nombre_maquina)->setCellValue('D22', 'Red')->setCellValue('E22', $configuracionRed->red)->setCellValue('B23', 'Mascara')->setCellValue('C23', $configuracionRed->mascara)->setCellValue('D23', 'Puerto Enlace')->setCellValue('E23', $configuracionRed->puerto_enlace)->setCellValue('B24', 'Dns Preferencial')->setCellValue('C24', $configuracionRed->dns_preferencial)->setCellValue('D24', 'Dns Alternativo')->setCellValue('E24', $configuracionRed->dns_alternativo); //MIGRACION $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B26', 'Migracion')->setCellValue('B27', 'Antes')->setCellValue('B28', 'Ram Antes')->setCellValue('C28', $migracion->ram_antes)->setCellValue('D28', 'Disco Antes')->setCellValue('E28', $migracion->disco_antes)->setCellValue('B29', 'Cpu Antes')->setCellValue('C29', isset($migracion->cpuIdAntes->modelo) ? $migracion->cpuIdAntes->modelo : '')->setCellValue('D29', 'Monitor Antes')->setCellValue('E29', isset($migracion->monitorIdAntes->modelo) ? $migracion->monitorIdAntes->modelo : '')->setCellValue('B30', 'Despues')->setCellValue('B31', 'Ram Despues')->setCellValue('C31', $migracion->ram_despues)->setCellValue('D31', 'Disco Despues')->setCellValue('E31', $migracion->disco_despues)->setCellValue('B32', 'Cpu Despues')->setCellValue('C32', isset($migracion->cpuIdDespues->modelo) ? $migracion->cpuIdDespues->modelo : '')->setCellValue('D32', 'Monitor Despues')->setCellValue('E32', isset($migracion->monitorIdDespues->modelo) ? $migracion->monitorIdDespues->modelo : ''); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Checklist'); $objPHPExcel->setActiveSheetIndex(0); foreach (range('A', 'I') as $columnID) { $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true); } ob_end_clean(); ob_start(); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="01simple.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); Yii::app()->end(); }
public static function exportExcel($data, $file) { ## add header if (count($data) > 0) { array_unshift($data, $data[0]); foreach ($data[0] as $k => $i) { $data[0][$k] = $k; } } ## generate excel Yii::import('ext.phpexcel.XPHPExcel'); $phpExcelObject = XPHPExcel::createPHPExcel(); $phpExcelObject->getActiveSheet()->fromArray($data, null, 'A1'); foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) { $phpExcelObject->getActiveSheet()->getColumnDimension($col)->setAutoSize(true); } Helper::generateExcel($phpExcelObject, $file); }
public function actionimportExcelTable($id, $issueId) { //step id if (isset($_FILES['Excel'])) { $post = Result::model()->importExcelTable($_FILES['Excel']); //var_dump($post); Yii::import('ext.phpexcel.IOFactory'); Yii::import('ext.phpexcel.*'); Yii::import('ext.phpexcel.XPHPExcel'); Yii::import('ext.phpexcel.shared.string'); $newsheet = XPHPExcel::createPHPExcel(); $inputFileName = $post; try { $objPHPExcel = PHPExcel_IOFactory::load($inputFileName); } catch (PHPExcel_Reader_Exception $e) { die('Error loadinng file: ' . $post); //$e->getMessage()); } $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumnIndex = $sheet->getHighestColumn(); $index = array('A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8); $highestColumn = $index[$highestColumnIndex]; $index = array_slice($index, 0, $highestColumn); $element = new Element(); $element->stepId = $id; $element->typeId = 13; $element->save(); $elementId = $element->id; $step = $this->loadModel($id); for ($row = 1; $row <= $highestRow; $row++) { $rows[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow('A', $row)->getValue(); } $columns = array(); foreach ($index as $key => $position) { $columns[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($position, 1)->getValue(); } //$salida=$step->addGrid($element, $rows, $columns); $issue = new Issue(); for ($row = 1; $row <= $highestRow; $row++) { foreach ($index as $key => $position) { if ($row >= 1 && $position > 0) { $result = new Result(); $result->elementId = $elementId; $result->issueId = $issueId; $result->value = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($position, $row)->getValue(); $result->colonne = $position - 1; $result->ligne = $row - 2; throw new CHttpException(403, 'row: ' . $result->ligne . '. Columna: ' . $result->colonne . '.Value: ' . $result->value); $result->save(); } } } //$this->redirect(array('view','id'=>$id)); } else { throw new CHttpException(403, "Failure loading file"); } }
public function actionexcelFileOutput14($id, $issueId) { Yii::import('ext.phpexcel.XPHPExcel'); $columnCont = 1; $rowCont = 1; $newsheet = XPHPExcel::createPHPExcel(); $element = Element::model()->findByPk($id); $i = 4; $r = 0; $resCol = Value::model()->findAll("elementId={$id} and colonne=1"); foreach ($element->columns as $colonne) { $newsheet->getActiveSheet()->setCellValueByColumnAndRow($columnCont, 1, $colonne->value); $i++; $columnCont++; } foreach ($element->rows as $row) { $rowCont++; $newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $rowCont, $row->value); for ($index = 0; $index < $columnCont; $index++) { $res = $element->getResultTableForExcel($issueId, $id, $index, $r); switch ($res) { case "off": $newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, "No"); break; case "checked": $newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, "Yes"); break; default: } } $r++; } $newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="myfile.xlsx'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007'); $objWriter->save('php://output'); }
public function actionExcel() { Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); // Set document properties $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("")->setCategory(""); if (Usuario::model()->findByPk(Yii::app()->user->id)->usu_rol == "admins") { $var = Evaluacion::model()->findAll(); } else { $var = Evaluacion::model()->findAllByAttributes(array('emp_rut' => Usuario::model()->findByPk(Yii::app()->user->id)->emp_rut)); } foreach ($var as $key => $value) { if (Telefono::model()->findByAttributes(array('tel_numero' => $value->tel_numero, 'emp_rut' => $value->emp_rut)) != null) { $fono = Telefono::model()->findByAttributes(array('tel_numero' => $value->tel_numero, 'emp_rut' => $value->emp_rut))->tel_mac; } else { $fono = null; } $var2 = EvaluacionPregunta::model()->findAllByAttributes(array('eva_id' => $value->eva_id)); // datos de las respuestas a la evaluacion a imprimir $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . ($key + 2), $key + 1)->setCellValue('B' . ($key + 2), $value->eva_fecha)->setCellValue('C' . ($key + 2), $value->usu_rut)->setCellValue('D' . ($key + 2), $value->emp_rut)->setCellValue('E' . ($key + 2), $value->emp_nombre)->setCellValue('F' . ($key + 2), $value->tel_numero)->setCellValue('G' . ($key + 2), $fono); $c = 7; $k = 0; $j = null; foreach ($var2 as $key2 => $value2) { if (Pregunta::model()->findByPk($value2->pre_id) != null) { $descripcion = Pregunta::model()->findByPk($value2->pre_id)->pre_descripcion; if ($value2->pre_respuesta) { $respuesta = "SI"; } else { $respuesta = "NO"; } } else { $descripcion = "Sin resultados"; } if ($c + $key2 > 25) { $j = intval(($c + $key2) / 26); $k = ($c + $key2) % 26; } if ($j == null) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($key2 + 65 + $c) . ($key + 2), $descripcion); } else { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($j + 64) . chr($k + 65) . ($key + 2), $descripcion); } $c++; if ($c + $key2 > 25) { $j = intval(($c + $key2) / 26); $k = ($c + $key2) % 26; } if ($j == null) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($key2 + 65 + $c) . ($key + 2), $respuesta); } else { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($j + 64) . chr($k + 65) . ($key + 2), $respuesta); } } } $j = null; $k = 0; $c = 1; for ($i = 7; $i < 107; $i++) { if ($i > 25) { $j = intval($i / 26); $k = $i - intval($i / 26) * 26; } if ($i % 2 != 0) { if ($j == null) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + 65) . '1', "Pregunta" . " " . $c); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(chr($i + 65))->setWidth(50); } else { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($j + 64) . chr($k + 65) . '1', "Pregunta" . " " . $c); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(chr($j + 64) . chr($k + 65))->setWidth(50); } } else { if ($j == null) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + 65) . '1', "Respuesta" . " " . $c); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(chr($i + 65))->setWidth(15); $c++; } else { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($j + 64) . chr($k + 65) . '1', "Respuesta" . " " . $c); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(chr($j + 64) . chr($k + 65))->setWidth(15); $c++; } } } $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'N°')->setCellValue('B1', 'fecha')->setCellValue('C1', 'Usuario')->setCellValue('D1', 'Rut Empresa')->setCellValue('E1', 'Empresa')->setCellValue('F1', 'N° Telefono')->setCellValue('G1', 'Mac Telefono'); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(20); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(20); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(20); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(20); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(20); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(25); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Simple'); // 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="informe.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'); exit; }
public function actionGenerateExcelTemplate() { $cols = json_decode($_GET['columns'], true); Yii::import('ext.phpexcel.XPHPExcel'); $phpExcelObject = XPHPExcel::createPHPExcel(); $phpExcelObject->getActiveSheet()->fromArray($cols, null, 'A1'); foreach (range('A', $phpExcelObject->getActiveSheet()->getHighestDataColumn()) as $col) { $phpExcelObject->getActiveSheet()->getColumnDimension($col)->setAutoSize(true); } $this->generateExcel($phpExcelObject, 'contoh-template'); }
public function actionAdminAbonos() { $model = new Contrato('search'); $model->unsetAttributes(); // clear any default values if (isset($_GET['Contrato'])) { $model->attributes = $_GET['Contrato']; } $meses = array(); for ($i = 1; $i <= 12; $i++) { $meses[] = array('id' => str_pad($i, 2, "0", STR_PAD_LEFT), 'nombre' => Tools::fixMes($i)); } $agnos = array(); $agnoInicio = 2000; $agnoFin = (int) date('Y') + 10; for ($i = $agnoInicio; $i < $agnoFin; $i++) { $agnos[] = array('id' => $i, 'nombre' => $i); } $filtroModel = new EstadoCuentaForm(); $filtroModel->agnoH = date('Y'); $filtroModel->mesH = date('m'); if ($filtroModel->mesH == '01') { $filtroModel->mesD = '12'; $filtroModel->agnoD = (int) $filtroModel->agnoH - 1; } else { $mes = (int) $filtroModel->mesH; $filtroModel->mesD = str_pad($mes - 1, 2, "0", STR_PAD_LEFT); $filtroModel->agnoD = $filtroModel->agnoH; } if (isset($_POST['EstadoCuentaForm'])) { $filtroModel->attributes = $_POST['EstadoCuentaForm']; Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); $sheet = $objPHPExcel->getActiveSheet(); $contrato = Contrato::model()->findByPk($filtroModel->contratoId); if ($contrato == null) { die; } if (!$contrato->estaAsociadoAPropietario(Yii::app()->user->id)) { die; } $sheet->setCellValue('A1', 'Movimientos de Cliente'); $sheet->mergeCells("A1:L1"); $sheet->getStyle("A1")->getFont()->setSize(15); $sheet->setCellValue('A3', 'Nombre: '); $sheet->setCellValue('B3', $contrato->cliente->usuario->nombre . " " . $contrato->cliente->usuario->apellido); $sheet->setCellValue('F3', 'Fecha Consulta: ' . date('d/m/Y')); $sheet->setCellValue('A4', 'Propiedad: '); $sheet->setCellValue('B4', $contrato->departamento->propiedad->nombre); $sheet->setCellValue('C4', "Departamento: "); $sheet->setCellValue('D4', $contrato->departamento->numero); $sheet->getStyle("A3")->getFont()->setSize(13); $sheet->getStyle("B3")->getFont()->setSize(13); $sheet->getStyle("F3")->getFont()->setSize(13); $sheet->getStyle("A4")->getFont()->setSize(13); $sheet->getStyle("B4")->getFont()->setSize(13); $sheet->getStyle("C4")->getFont()->setSize(13); $sheet->getStyle("D4")->getFont()->setSize(13); $sheet->setCellValue('A5', "Rango de fechas consultado"); $sheet->getStyle("A5")->getFont()->setSize(13); $sheet->setCellValue('A6', "Desde:"); $sheet->getStyle("A6")->getFont()->setSize(13); $sheet->setCellValue('B6', Tools::fixMes($filtroModel->mesD) . " " . $filtroModel->agnoD); $sheet->getStyle("B6")->getFont()->setSize(13); $fechaDesde = $filtroModel->agnoD . "-" . $filtroModel->mesD . "-" . "01"; if ($filtroModel->desdeInicio == '1') { $sheet->setCellValue('A6', "Desde inicio del Contrato:"); $sheet->setCellValue('B6', Tools::backFecha($contrato->fecha_inicio)); $fechaDesde = $contrato->fecha_inicio; } if ($filtroModel->desdeSaldo0 == '1') { $movimiento = Movimiento::model()->findByPk($contrato->cuentaCorriente->idMovUltimoSaldo0()); if ($movimiento != null) { $sheet->setCellValue('A6', "Desde último saldo 0:"); $sheet->setCellValue('B6', Tools::backFecha($movimiento->fecha)); $fechaDesde = $movimiento->fecha; } else { $sheet->setCellValue('A6', "Desde último saldo 0:"); $sheet->setCellValue('B6', "No hay saldo 0 "); $fechaDesde = $contrato->fecha_inicio; } } $fechaArr = explode("-", $fechaDesde); $filtroModel->mesD = $fechaArr[1]; $filtroModel->agnoD = $fechaArr[0]; $sheet->setCellValue('A7', "Hasta:"); $sheet->getStyle("A7")->getFont()->setSize(13); $sheet->setCellValue('B7', Tools::fixMes($filtroModel->mesH) . " " . $filtroModel->agnoH); $sheet->getStyle("B7")->getFont()->setSize(13); $sheet->setCellValue('A9', "Saldo Anterior:"); $sheet->getStyle("A9")->getFont()->setSize(13); $sheet->setCellValue('B9', $contrato->cuentaCorriente->saldoAFecha($fechaDesde)); $sheet->getStyle("B9")->getFont()->setSize(13); if ($filtroModel->conDetalle == "1") { $sheet->setCellValue('A11', "Mes/Año"); $sheet->setCellValue('B11', "Concepto"); $sheet->setCellValue('C11', "Cargos"); $sheet->mergeCells('C11:D11'); $sheet->setCellValue('E1', "Abonos"); $sheet->mergeCells('E11:F11'); $sheet->setCellValue('C12', "Fecha"); $sheet->setCellValue('D12', "Monto"); $sheet->setCellValue('E12', "Fecha"); $sheet->setCellValue('F12', "Monto"); $sheet->getStyle("A11:F12")->getFont()->setBold(true); $j = 13; } else { $sheet->setCellValue('A11', "Mes/Año"); $sheet->setCellValue('B11', "Cargos"); $sheet->setCellValue('C11', "Abonos"); $sheet->getStyle("A11:C11")->getFont()->setBold(true); $j = 12; } $meses = Tools::arregloMeses($filtroModel->mesD, $filtroModel->agnoD, $filtroModel->mesH, $filtroModel->agnoH); $abonos = 0; $cargos = 0; foreach ($meses as $mesArr) { $mes = $mesArr['mes']; $agno = $mesArr['agno']; $mesNombre = $mesArr['mesNombre']; if ($filtroModel->conDetalle == "1") { $sheet->setCellValue('A' . $j, $mesNombre . " " . $agno); $j++; $movimientosMes = $contrato->cuentaCorriente->movimientosDeMes($mes, $agno); foreach ($movimientosMes as $movimiento) { $sheet->setCellValue('B' . $j, $movimiento->detalle); if ($movimiento->tipo == Tools::MOVIMIENTO_TIPO_CARGO) { $sheet->setCellValue('C' . $j, Tools::backFecha($movimiento->fecha)); $sheet->setCellValue('D' . $j, $movimiento->monto); $cargos += $movimiento->monto; } else { $sheet->setCellValue('E' . $j, Tools::backFecha($movimiento->fecha)); $sheet->setCellValue('F' . $j, $movimiento->monto); $abonos += $movimiento->monto; } $j++; } } else { $saldoMes = $contrato->cuentaCorriente->saldoMes($mes, $agno); $sheet->setCellValue('A' . $j, $mesNombre . " " . $agno); $sheet->setCellValue('B' . $j, $saldoMes['cargos']); $sheet->setCellValue('C' . $j, $saldoMes['abonos']); $j++; } } if ($filtroModel->conDetalle == "1") { $sheet->setCellValue('A' . $j, "SUB TOTAL"); $sheet->setCellValue('D' . $j, $cargos); $sheet->setCellValue('F' . $j, $abonos); $j++; } $saldo = $contrato->cuentaCorriente->saldoAFecha(date('Y-m-d')); $sheet->setCellValue('A' . ($j + 1), "Saldo fecha consulta: "); $sheet->getStyle('A' . ($j + 1))->getFont()->setSize(13); $sheet->setCellValue('B' . ($j + 1), $saldo); $sheet->getStyle('B' . ($j + 1))->getFont()->setSize(13); $color = '00FF00'; if ($saldo < 0) { $color = 'FF0000'; } $sheet->getStyle("B" . ($j + 1))->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $color)))); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="Movimientos Cliente ' . $contrato->cliente->usuario->nombre . ' ' . $contrato->cliente->usuario->apellido . '.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(); } Yii::app()->user->returnUrl = array('//contrato/adminAbonos'); $this->render('adminAbonos', array('model' => $model, 'filtroModel' => $filtroModel, 'meses' => $meses, 'agnos' => $agnos)); }
private function exportEXCEL($excelname, $col, $title, $colAry) { // PHP EXCEL 初始化 XPHPExcel::init(); $fileTitle = "JIT Excel File"; $objPHPExcel = XPHPExcel::createPHPExcel(); $objPHPExcel->getProperties()->setCreator("JIT")->setLastModifiedBy("JIT")->setTitle($fileTitle)->setSubject("")->setDescription($fileTitle)->setKeywords("office 2007 openxml php")->setCategory("Excel File"); // 第一列 填入標題 $column = 0; // 第幾欄. 由第0欄開始 for ($i = 0; $i < count($col); $i++) { if (isset($title[$col[$i]])) { $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($column, 1, isset($title[$col[$i]]) ? $title[$col[$i]] : '', PHPExcel_Cell_DataType::TYPE_STRING); $column++; } } // 後續 填入內容 // 第幾列, 由第2列開始 $row = 2; for ($j = 0; $j < count($colAry); $j++) { if (isset($colAry[$j][$col[0]])) { $column = 0; // 第幾欄. 由第0欄開始 for ($i = 0; $i < count($col); $i++) { // 若符合篩選欄位. 才進行 if (isset($title[$col[$i]])) { /*$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row, (isset($colAry[$j][$col[$i]]))? $colAry[$j][$col[$i]]:'');*/ //避免(007002變7002) 0被吃掉,所以關鍵 setValueExplicit所有都轉字串 $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setValueExplicit(isset($colAry[$j][$col[$i]]) ? $colAry[$j][$col[$i]] : '', PHPExcel_Cell_DataType::TYPE_STRING); $column++; } } $row++; } } //CVarDumper::dump($colAry,10,true); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle($excelname . '-正航匯入表'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a web browser (Excel5) $webroot = Yii::getPathOfAlias('webroot'); //$fileName =$excelname.'-'.time().'.xls'; $fileName = 'PerformOut01' . '.xls'; $filePath = $webroot . '/' . "protected" . '/' . "tmp" . '/'; $fileUrl = $filePath . $fileName; // 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'); // $fileName = Yii::app()->request->baseUrl."/protected/tmp/".$fileName; // CVarDumper::dump($fileName); // $objWriter->save($fileName); $objWriter->save($fileUrl); // // Yii::app()->end(); // $objWriter->save(str_replace('.php', '.xls', __FILE__)); // $objWriter->save(str_replace(__FILE__,'/protected/tmp/'.$fileName,__FILE__)); // $objWriter->save('/protected/tmp/'.$fileName); // @readfile('/protected/tmp/'.$fileName); // spl_autoload_register(array('YiiBase','autoload'),true,true); return $fileName; }
public function actionExportResponseStatistics($id, $location) { $survey = $this->loadModel($id, $location); $allowedForUsers = array_map(function ($x) { return $x->userId; }, $survey->surveyUsers); if (!Yii::app()->user->checkAccess('survey.view') && !Yii::app()->user->checkAccess('survey.viewAndDownloadAllowed', array('user_ids' => $allowedForUsers))) { throw new Http403Exception(); } if ($survey->surveyInfo && $survey->surveyInfo->vicidialCampaign) { $statistics = $survey->surveyInfo->vicidialCampaign->getCallsStatistics(); Yii::import('ext.PHPExcel.XPHPExcel'); XPHPExcel::init(); $phpExcel = XPHPExcel::createPHPExcel(); $phpExcel->getProperties()->setCreator("BCGroup"); $phpExcel->getProperties()->setLastModifiedBy("BCGroup"); $phpExcel->getProperties()->setTitle("Статистика отвечаемости по проекту {$survey->title}"); $phpExcel->getProperties()->setSubject("Статистика отвечаемости"); $phpExcel->setActiveSheetIndex(0); $phpExcel->getActiveSheet()->SetCellValue('A1', 'Код статуса'); $phpExcel->getActiveSheet()->SetCellValue('B1', 'Название статуса'); $phpExcel->getActiveSheet()->SetCellValue('C1', 'Количество'); foreach ($statistics as $idx => $statisticsRow) { $phpExcel->getActiveSheet()->SetCellValue('A' . ($idx + 2), $statisticsRow['status']); $phpExcel->getActiveSheet()->SetCellValue('B' . ($idx + 2), $statisticsRow['status_name']); $phpExcel->getActiveSheet()->SetCellValue('C' . ($idx + 2), $statisticsRow['count']); } $phpExcel->getActiveSheet()->setTitle('Статистика отвечаемости'); $phpExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $phpExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $phpExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $phpExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setBold(true); $objWriter = new PHPExcel_Writer_Excel2007($phpExcel); $filePath = $survey->getResponseStatisticsSrc(); $objWriter->save($filePath); Yii::app()->request->sendFile(basename($filePath), file_get_contents($filePath)); } else { throw new CHttpException(404, 'The requested page does not exist.'); } Yii::app()->end(); }
public function actionListado() { $model = new ListadoPrestacionesForm(); $propiedades = Propiedad::model()->getDeUsuario(Yii::app()->user->id); $meses = array(); for ($i = 1; $i <= 12; $i++) { $meses[] = array('id' => str_pad($i, 2, "0", STR_PAD_LEFT), 'nombre' => Tools::fixMes($i)); } $agnos = array(); $agnoInicio = 2000; $agnoFin = (int) date('Y') + 10; for ($i = $agnoInicio; $i < $agnoFin; $i++) { $agnos[] = array('id' => $i, 'nombre' => $i); } $model->agnoH = date('Y'); $model->mesH = date('m'); if ($model->mesH == '01') { $model->mesD = '12'; $model->agnoD = (int) $model->agnoH - 1; } else { $mes = (int) $model->mesH; $model->mesD = str_pad($mes - 1, 2, "0", STR_PAD_LEFT); $model->agnoD = $model->agnoH; } if (isset($_POST['ListadoPrestacionesForm'])) { $model->attributes = $_POST['ListadoPrestacionesForm']; $propiedad = Propiedad::model()->findByPk($model->propiedad_id); $departamento = Departamento::model()->findByPk($model->departamento_id); Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); $sheet = $objPHPExcel->getActiveSheet(); $sheet->setCellValue('A1', 'Listado de Prestaciones por Propiedad'); $sheet->mergeCells("A1:K1"); $sheet->getStyle("A1")->getFont()->setSize(20); $sheet->setCellValue('A2', 'Rango de Fechas: Desde ' . Tools::fixMes($model->mesD) . " de " . $model->agnoD . " hasta " . Tools::fixMes($model->mesH) . " de " . $model->agnoH); $sheet->mergeCells("A2:K2"); $sheet->getStyle("A2")->getFont()->setSize(15); $i = 3; if ($propiedad != null) { $sheet->setCellValueByColumnAndRow(0, $i, 'Propiedad: ' . $propiedad->nombre); $sheet->mergeCellsByColumnAndRow(0, $i, 5, $i); $sheet->getStyleByColumnAndRow(0, $i)->getFont()->setSize(15); $i++; } if ($departamento != null && $propiedad != null) { $sheet->setCellValueByColumnAndRow(0, $i, 'Departamento: ' . $departamento->numero); $sheet->mergeCellsByColumnAndRow(0, $i, 5, $i); $sheet->getStyleByColumnAndRow(0, $i)->getFont()->setSize(15); $i++; } $i++; $sheet->setCellValueByColumnAndRow(0, $i, 'Fecha'); $sheet->setCellValueByColumnAndRow(1, $i, 'Propiedad'); $sheet->setCellValueByColumnAndRow(2, $i, 'Departamento'); $sheet->setCellValueByColumnAndRow(3, $i, 'General Prop'); $sheet->setCellValueByColumnAndRow(4, $i, 'Nro Cheque'); $sheet->setCellValueByColumnAndRow(5, $i, 'Monto'); $sheet->setCellValueByColumnAndRow(6, $i, 'C/S Cargo'); $sheet->setCellValueByColumnAndRow(7, $i, 'Concepto'); $sheet->setCellValueByColumnAndRow(8, $i, 'Maestro'); $sheet->setCellValueByColumnAndRow(9, $i, 'Tipo Prestación'); $sheet->getStyleByColumnAndRow(0, $i, 10, $i)->getFont()->setSize(15); $sheet->getStyleByColumnAndRow(0, $i, 10, $i)->getFont()->setBold(true); $i++; $prestaciones = Prestacionesadepartamentos::model()->getDePropiedadYDepartamento($propiedad, $departamento); foreach ($prestaciones as $prestacion) { $sheet->setCellValueByColumnAndRow(0, $i, Tools::backFecha($prestacion->fecha)); $propiedad = Propiedad::model()->findByPk($prestacion->propiedad_id); $departamento = Departamento::model()->findByPk($prestacion->departamento_id); if ($propiedad == null) { $propiedad = $departamento->propiedad; } $sheet->setCellValueByColumnAndRow(1, $i, $propiedad->nombre); $sheet->setCellValueByColumnAndRow(2, $i, $departamento != null ? $departamento->numero : "SIN DEPARTAMENTO"); $sheet->setCellValueByColumnAndRow(3, $i, $prestacion->general_prop == 1 ? "SÍ" : "NO"); $sheet->setCellValueByColumnAndRow(4, $i, $prestacion->documento); $sheet->setCellValueByColumnAndRow(5, $i, $prestacion->monto); $sheet->setCellValueByColumnAndRow(6, $i, $prestacion->genera_cargos == 1 ? "SÍ" : "NO"); $sheet->setCellValueByColumnAndRow(7, $i, $prestacion->descripcion); $sheet->setCellValueByColumnAndRow(8, $i, $prestacion->maestro); $sheet->setCellValueByColumnAndRow(9, $i, $prestacion->tipo); $i++; } $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="Prestaciones a Propiedades.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(); } $this->render('listado', array('model' => $model, 'meses' => $meses, 'agnos' => $agnos, 'propiedades' => $propiedades)); }
public function actionexcelComp($id, $elementId) { //issueId and elementId $element = Element::model()->findByPk($elementId); Yii::import('ext.phpexcel.XPHPExcel'); $title = Element::model()->findByPk($elementId); $newsheet = XPHPExcel::createPHPExcel(); switch ($element->typeId) { case 0: case 1: case 3: case 4: $criteria = "elementId={$elementId}"; $results = Result::model()->findAll($criteria); //$results=Result::model()->findAll($title->label); $newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Issue ID"); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 1, "User"); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, 1, "Fill date"); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, 1, $title->label); $newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getStyle('D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); $cont = 2; foreach ($results as $result) { $newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $cont, $result->issueId); $issue = $this->loadIssue($result->issueId); $user = Element::model()->getUserForExcel($result->issueId, $elementId); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, $cont, $user); // $newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, $cont, Yii::app()->dateFormatter->format("HH:mm d/M/y", $result->createTime)); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, $cont, $result->value); $cont++; } $traveler = Traveler::model()->findByPk($issue->travelerId); if (isset($issue)) { if (strlen($traveler->name) > 28) { $traveler->name = substr($traveler->name, 0, 28); } $newsheet->getActiveSheet()->setTitle($traveler->name); } //else throw new CHttpException(403, 'There is a problem with the data'); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="myfile.xlsx'); header('Cache-Control: max-age=0'); //metada: $newsheet->getProperties()->setCreator(Yii::app()->user->username); $objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007'); $objWriter->save('php://output'); break; case 5: //devuelve el value de result, que es el id de value!! //center 1st column $newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //name first column $newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Issue ID"); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 1, "User"); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, 1, "Fill date"); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, 1, $title->label); //center data cells $newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //color $newsheet->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getStyle('D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); //size $newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); $criteria = "elementId={$elementId}"; $preresults = Result::model()->findAll($criteria); $cont = 2; foreach ($preresults as $preresult) { $issue = $this->loadIssue($preresult->issueId); $user = Element::model()->getUserForExcel($preresult->issueId, $elementId); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $cont, $preresult->issueId); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, $cont, $user); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, $cont, Yii::app()->dateFormatter->format("HH:mm d/M/y", $preresult->createTime)); $value = Value::model()->findByPk($preresult->value); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, $cont, $value->value); $cont++; } if (isset($issue)) { $traveler = Traveler::model()->findByPk($issue->travelerId); $newsheet->getActiveSheet()->setTitle($traveler->name); } header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="myfile.xlsx'); header('Cache-Control: max-age=0'); //metada: $newsheet->getProperties()->setCreator(Yii::app()->user->username); $objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007'); $objWriter->save('php://output'); break; case 6: case 7: //center 1st column $newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //name first column $newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Issue ID"); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 1, "User"); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, 1, "Fill date"); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(3, 1, $title->label); //center data cells $newsheet->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $newsheet->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //color $newsheet->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); $newsheet->getActiveSheet()->getStyle('D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('AFDBFF'); //size $newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); //saber numero de issues $criteria = new CDbCriteria(); $criteria->distinct = true; $criteria->condition = "elementId=" . $elementId; $criteria->select = "issueId"; $issues = Result::model()->findAll($criteria); $contCol = 0; $cont = array(); $cont = 1; //looping through results by issues!!! foreach ($issues as $issue) { $user = null; $cont++; $results = Result::model()->findAll("elementId={$elementId} and issueId={$issue->issueId}"); $contCol = 3; $newest = Date('1970-01-21 00:00:00.0'); foreach ($results as $result) { //$user=Element::model()->getUserForExcel7($result->issueId, $elementId, $result->value); //$newsheet->getActiveSheet()->setCellValueByColumnAndRow(1+$userColumn,$cont+1,$user); $date = $result->createTime; if ($date > $newest) { $newest = $date; $user = $result->userId; $user = Element::model()->getUserForExcel($result->issueId, $elementId); } if (!is_numeric($result->value)) { $newsheet->getActiveSheet()->setCellValueByColumnAndRow($contCol, $cont, "NULL"); $contCol++; $newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $cont, $result->issueId); } else { $criteria = "elementId={$elementId} and id={$result->value}"; $values = Value::model()->find($criteria); $newsheet->getActiveSheet()->setCellValueByColumnAndRow($contCol, $cont, $values->value); $contCol++; $newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $cont, $result->issueId); } //else throw new CHttpException(404, 'There is a problem with the data.'); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, $cont, $user); } $newest = Yii::app()->dateFormatter->format("HH:mm d/M/y", $newest); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(1, $cont, $user); $newsheet->getActiveSheet()->setCellValueByColumnAndRow(2, $cont, $newest); /* //esto es para cargar el nombre del traveler en el nombre de la hoja de calculo, no funciona porque no está bien cargado el issue if(isset($issue)){ $traveler=Traveler::model()->findByPk($issue->travelerId); $newsheet->getActiveSheet()->setTitle($traveler->name); }*/ } if ($contCol <= 26 && $contCol > 0) { $columnLookup = array(1 => 'a', 2 => 'b', 3 => 'c', 4 => 'd', 5 => 'e', 6 => 'f', 7 => 'g', 8 => 'h', 9 => 'i', 10 => 'j', 11 => 'k', 12 => 'l', 13 => 'm', 14 => 'n', 15 => 'o', 16 => 'p', 17 => 'q', 18 => 'r', 19 => 's', 20 => 't', 21 => 'u', 22 => 'v', 23 => 'w', 24 => 'x', 25 => 'y', 26 => 'z'); $mergeCells = "d1:" . $columnLookup[$contCol] . "1"; $newsheet->setActiveSheetIndex(0)->mergeCells($mergeCells); } header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="myfile.xlsx'); header('Cache-Control: max-age=0'); //metada: $newsheet->getProperties()->setCreator(Yii::app()->user->username); $objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007'); $objWriter->save('php://output'); break; case 13: case 14: $criteria = new CDbCriteria(); $criteria->distinct = true; $criteria->condition = "elementId=" . $elementId; $criteria->select = "issueId"; $issues = Result::model()->findAll($criteria); $sheetNumber = 0; foreach ($issues as $issue) { $myWorkSheet = new PHPExcel_Worksheet($newsheet, $issue->issueId); $newsheet->addSheet($myWorkSheet); $newsheet->setActiveSheetIndex($sheetNumber + 1); $sheetNumber++; $criteria = "elementId={$elementId}"; $results = Result::model()->findAll($criteria); $cont = 1; $contCol = 0; $columnCont = 1; $rowCont = 1; $element = Element::model()->findByPk($elementId); $i = 4; $r = 0; $resCol = Value::model()->findAll("elementId={$elementId} and colonne=1"); foreach ($element->columns as $colonne) { $newsheet->getActiveSheet()->setCellValueByColumnAndRow($columnCont, 1, $colonne->value); $i++; $columnCont++; } foreach ($element->rows as $row) { $rowCont++; $newsheet->getActiveSheet()->setCellValueByColumnAndRow(0, $rowCont, $row->value); for ($index = 0; $index < $columnCont; $index++) { $res = $element->getResultTableForExcel($issue->issueId, $elementId, $index, $r); switch ($res) { case "off": $newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, "No"); break; case "checked": $newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, "Yes"); break; default: $newsheet->getActiveSheet()->setCellValueByColumnAndRow($index + 1, $r + 2, $res); } } $r++; } $newsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); $newsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); } header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml0sheet'); header('Content-Disposition: attachment;filename="myfile.xlsx'); header('Cache-Control: max-age=0'); $newsheet->removeSheetByIndex(0); $objWriter = PHPExcel_IOFactory::createWriter($newsheet, 'Excel2007'); $objWriter->save('php://output'); break; } }
public function actionInforme() { $form = new InformeForm(); if (isset($_POST['InformeForm'])) { $form->attributes = $_POST['InformeForm']; Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); $sheet = $objPHPExcel->getActiveSheet(); $styleCenter = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $sheet->setCellValue('A1', 'Informe de Movimientos de Propietario'); $sheet->getStyle("A1")->getFont()->setSize(20); $sheet->setCellValue('A2', 'Desde: ' . $form->fechaDesde . ' Hasta: ' . $form->fechaHasta); $sheet->getStyle("A2")->getFont()->setSize(16); $fechaDesdeArr = explode('-', $form->fechaDesde); if (count($fechaDesdeArr) == 3) { $mesInicio = $fechaDesdeArr[1]; $agnoInicio = $fechaDesdeArr[0]; } else { $mesInicio = date('m'); $agnoInicio = date('Y'); $form->fechaDesde = date('Y-m-d'); } $fechaHastaArr = explode('-', $form->fechaHasta); if (count($fechaHastaArr) == 3) { $mesFin = $fechaHastaArr[1]; $agnoFin = $fechaHastaArr[0]; } else { $mesFin = date('m'); $agnoFin = date('Y'); $form->fechaHasta = date('Y-m-d'); } $meses = Tools::arregloMeses($mesInicio, $agnoInicio, $mesFin, $agnoFin); $propietario_id = Propietario::model()->getId(Yii::app()->user->id); $propietario = Propietario::model()->findByPk($propietario_id); $propiedades = Propiedad::model()->findAllByAttributes(array('propietario_id' => $propietario_id)); $row = 4; foreach ($propiedades as $propiedad) { $sheet->setCellValueByColumnAndRow(0, $row, "PROPIEDAD: " . $propiedad->nombre); $row++; foreach ($propiedad->departamentos as $departamento) { if ($departamento->contrato != null) { $rmax = 0; $sheet->setCellValueByColumnAndRow(0, $row, "Departamento: " . $departamento->numero . ", Contrato: " . $departamento->contrato->folio . ", Arrendatario: " . $departamento->contrato->cliente->rut . " " . $departamento->contrato->cliente->usuario->nombre . " " . $departamento->contrato->cliente->usuario->apellido); $row++; $sheet->setCellValueByColumnAndRow(0, $row, "Movimientos de la cuenta"); $col = 0; $cuentaCorriente = $departamento->contrato->cuentaCorriente; $saldo = 0; foreach ($meses as $mesArreglo) { $nDias = cal_days_in_month(CAL_GREGORIAN, $mesArreglo['mes'], $mesArreglo['agno']); $sheet->mergeCellsByColumnAndRow($col, $row, $col + 1, $row); $sheet->getStyleByColumnAndRow($col, $row)->applyFromArray($styleCenter); $sheet->setCellValueByColumnAndRow($col, $row, $mesArreglo['mesNombre'] . " " . $mesArreglo['agno']); $sheet->setCellValueByColumnAndRow($col, $row + 1, Tools::MOVIMIENTO_TIPO_ABONO); $sheet->setCellValueByColumnAndRow($col + 1, $row + 1, Tools::MOVIMIENTO_TIPO_CARGO); $movimientos = Movimiento::model()->findAll(array('condition' => 'fecha >= :fIni and fecha <= :fFin and cuenta_corriente_id = :cta', 'params' => array(':fIni' => $mesArreglo['agno'] . "-" . $mesArreglo['mes'] . "-01", ':fFin' => $mesArreglo['agno'] . "-" . $mesArreglo['mes'] . "-" . $nDias, ':cta' => $cuentaCorriente->id))); if (count($movimientos) > $rmax) { $rmax = count($movimientos); } $iMov = 0; while ($iMov < count($movimientos)) { $movimiento = $movimientos[$iMov]; if ($movimiento->tipo == Tools::MOVIMIENTO_TIPO_ABONO) { if ($movimiento->validado == 1) { $sheet->setCellValueByColumnAndRow($col, $row + 2 + $iMov, $movimiento->monto); $saldo += $movimiento->monto; } } if ($movimiento->tipo == Tools::MOVIMIENTO_TIPO_CARGO) { $sheet->setCellValueByColumnAndRow($col + 1, $row + 2 + $iMov, $movimiento->monto); $saldo -= $movimiento->monto; } $iMov++; } $sheet->mergeCellsByColumnAndRow($col, $row + 2 + $iMov, $col + 1, $row + 2 + $iMov); $sheet->setCellValueByColumnAndRow($col, $row + 2 + $iMov, $saldo); $sheet->getStyleByColumnAndRow($col, $row + 2 + $iMov)->applyFromArray($styleCenter); if ($saldo < 0) { $sheet->getStyleByColumnAndRow($col, $row + 2 + $iMov)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0000')))); } else { $sheet->getStyleByColumnAndRow($col, $row + 2 + $iMov)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '0000FF')))); } $col += 2; } $row += $rmax + 1; } } $row += 3; } // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="Movimientos Propietario ' . $propietario->usuario->nombre . ' ' . $propietario->usuario->apellido . '.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 { $form->fechaHasta = date('Y-m-d'); } $this->render('informe', array('model' => $form)); }
public function actionExportarXLS() { // generate a resultset $morosos = Yii::app()->user->getState('morososFiltrados'); Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); $sheet = $objPHPExcel->getActiveSheet(); $sheet->setCellValue('A1', 'Clientes Morosos'); $sheet->mergeCells("A1:K1"); $sheet->getStyle("A1")->getFont()->setSize(20); $filtros = ""; if ($morosos->propiedad != "") { $filtros .= " Propiedad: " . $morosos->propiedad . "."; } if ($morosos->departamento != "") { $filtros .= " Departamento: " . $morosos->departamento . "."; } if ($morosos->nombre_ap != "") { $filtros .= " Nombre Cliente: " . $morosos->nombre_ap . "."; } if ($morosos->fecha != "") { $filtros .= " Fecha: " . $morosos->fecha . "."; } if ($morosos->dias != "") { $filtros .= " Días de mora: " . $morosos->dias . "."; } $i = 3; if ($filtros != "") { $sheet->setCellValue('A2', 'Criterios de búsqueda aplicados: ' . $filtros); $sheet->mergeCells("A2:K2"); $i++; $criteria = new CDbCriteria(); $criteria->compare('id', $morosos->id); if ($morosos->nombre_ap != "") { $arreglo = explode(" ", $morosos->nombre_ap); $nombreApellido = array(); foreach ($arreglo as $palabra) { if (trim($palabra) != '') { $nombreApellido[] = $palabra; } } $criteriaNombre = new CDbCriteria(); $palabras = count($nombreApellido); if ($palabras == 1) { $busqueda = $nombreApellido[0]; if (trim($busqueda) != '') { $criteriaNombre->compare('nombre', $busqueda, true); $criteriaNombre->compare('apellido', $busqueda, true, 'OR'); } } if ($palabras == 2) { $nombre = $nombreApellido[0]; $apellido = $nombreApellido[1]; $criteriaNombre->compare('nombre', $nombre, true); $criteriaNombre->compare('apellido', $apellido, true); } $criteria->mergeWith($criteriaNombre, 'AND'); } if ($morosos->propiedad != "") { $criteria->compare('propiedad', $morosos->propiedad, true); } if ($morosos->departamento != "") { $criteria->compare('departamento', $morosos->departamento, true); } if ($morosos->monto != "") { $criteria->compare('monto', $morosos->monto); } if ($morosos->fecha != "") { $criteria->compare('fecha', Tools::fixFecha($morosos->fecha)); } if ($morosos->dias != "") { $criteria->compare('dias', $morosos->dias); } $tempMorosos = TempMorosos::model()->findAll($criteria); } else { $tempMorosos = TempMorosos::model()->findAll(); } $sheet->setCellValue('A' . $i, 'Propiedad'); $sheet->setCellValue('B' . $i, 'Departamento'); $sheet->setCellValue('C' . $i, 'Nombre'); $sheet->setCellValue('D' . $i, 'Monto'); $sheet->setCellValue('E' . $i, 'Fecha'); $sheet->setCellValue('F' . $i, 'Dias'); $sheet->getStyle("A{$i}:F{$i}")->getFont()->setBold(true); $i++; foreach ($tempMorosos as $moroso) { $sheet->setCellValue('A' . $i, $moroso->propiedad); $sheet->setCellValue('B' . $i, $moroso->departamento); $sheet->setCellValue('C' . $i, $moroso->nombre . " " . $moroso->apellido); $sheet->setCellValue('D' . $i, $moroso->monto); $sheet->setCellValue('E' . $i, Tools::backFecha($moroso->fecha)); $sheet->setCellValue('F' . $i, $moroso->dias); $i++; } $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="Movimientos Propietario.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(); }
public function actionExcelEPC() { date_default_timezone_set('America/Tegucigalpa'); if (isset($_GET['Seccion'])) { $IdCurso = $_GET['Seccion']; $Curso = Curso::model()->findByPk($IdCurso); $AlumnosPorCurso = AlumnosPorCurso::model()->findAllByAttributes(array('Curso_IdCurso' => $IdCurso), array('order' => 'Activo DESC')); 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()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LEGAL); $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Lista de Estudiantes'); // FORMATO DE CELDAS (MERGE) $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B1:M1'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B2:M2'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('C3:D3'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('F3:G3'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('I3:J3'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('L3:M3'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B4:M4'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('B5:H5'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('I5:M5'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('C6:D6'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E6:F6'); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('G6:H6'); // BORDES $BordesEncabezado = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $objPHPExcel->getActiveSheet()->getStyle('B1:M3')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B5:M6')->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getStyle('B1:M2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8'); $objPHPExcel->getActiveSheet()->getStyle('B3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8'); $objPHPExcel->getActiveSheet()->getStyle('E3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8'); $objPHPExcel->getActiveSheet()->getStyle('H3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8'); $objPHPExcel->getActiveSheet()->getStyle('K3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cec8c8'); $objPHPExcel->getActiveSheet()->getStyle('B5:H6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('e89b9e'); $objPHPExcel->getActiveSheet()->getStyle('I5:M6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('b1dee0'); // FORMATOS DE TEXTO ( SIZE, FONT , BOLD) $styleArray = array('font' => array('bold' => true, 'size' => 14), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); // Negrita tamaño 14 $NormalTamanioDoce = array('font' => array('size' => 13), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); // Normal Tamaño 12 $NegritaTamanioDoce = array('font' => array('bold' => true, 'size' => 12), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); // Negrita Tamaño 12 // ESCRITURA DE TEXTO $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', $Curso->Nombre); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', "Listado de Estudiantes"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B5', "\tINFORMACIÓN ESTUDIANTE"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I5', "INFORMCIÓN EMPRESA"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B6', "Nº Cuenta"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C6', "Nombre Estudiante"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E6', "Correo Electrónico"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G6', "Carrera"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I6', "Empresa"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J6', "Jefe"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K6', "Puesto"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L6', "Correo"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M6', "Celular"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C3', $Curso->Seccion); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F3', $Curso->Codigo); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I3', $Curso->periodoAcademico->PeriodoConcatenado); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L3', date('Y-m-d H:i:s')); // APLICANDO FORMATOS $objPHPExcel->getActiveSheet()->getStyle('B1')->applyFromArray($styleArray); $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($NormalTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('E3')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('H3')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('K3')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('B5:I5')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('B6')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('C6')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('E6')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('G6')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('I6')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('J6')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('K6')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('L6')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('M6')->applyFromArray($NegritaTamanioDoce); $objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B3', 'Sección:')->setCellValue('E3', 'Código:')->setCellValue('H3', 'Periodo:')->setCellValue('K3', 'Emitido:'); // RECORRIENDO MODELO CON LOS ESTUDIANTES $contador = 7; foreach ($AlumnosPorCurso as $Alumno) { $PracticasPorAlumno = PracticasPorAlumno::model()->findAllByAttributes(array('UsuarioEstudiante_IdUsuarioEstudiante' => $Alumno->userEstudiante->IdUsuarioEstudiante, 'Curso_IdCurso' => $IdCurso)); if ($Alumno->userEstudiante->Activo == 1) { foreach ($PracticasPorAlumno as $practica) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $contador, $practica->practica->asesor->usuarioEmpresa->NombreEmpresa)->setCellValue('J' . $contador, $practica->practica->asesor->NombreCompleto)->setCellValue('K' . $contador, $practica->practica->asesor->PuestoEmpresa)->setCellValue('L' . $contador, $practica->practica->asesor->CorreoElectronico)->setCellValue('M' . $contador, $practica->practica->asesor->TelefonoCelular); } $objPHPExcel->setActiveSheetIndex(0)->mergeCells('C' . $contador . ':D' . $contador); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E' . $contador . ':F' . $contador); $objPHPExcel->setActiveSheetIndex(0)->mergeCells('G' . $contador . ':H' . $contador); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $contador, $Alumno->userEstudiante->NumeroDeCuenta)->setCellValue('C' . $contador, $Alumno->userEstudiante->NombreCompleto)->setCellValue('E' . $contador, $Alumno->userEstudiante->Email)->setCellValue('G' . $contador, $Alumno->userEstudiante->carrera->NombreCarrera); $contador += 1; } } $contador -= 1; $objPHPExcel->getActiveSheet()->getStyle('B7:M' . $contador)->applyFromArray($BordesEncabezado); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(27); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(23); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(18); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(18); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(18); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(18); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12); // 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= Lista de Estudiantes - ' . $Curso->Nombre . '.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 actionimportExcelTable($id, $issueId) { //step id if (isset($_FILES['Excel'])) { $post = Result::model()->importExcelTable($_FILES['Excel']); //throw new CHttpException(403,$_FILES['File']); Yii::import('ext.phpexcel.IOFactory'); Yii::import('ext.phpexcel.*'); Yii::import('ext.phpexcel.XPHPExcel'); Yii::import('ext.phpexcel.shared.string'); $newsheet = XPHPExcel::createPHPExcel(); //$inputFileName='c:\inetpub\wwwroot\vector2014\protected\extensions\examples\reader\sampledata\book1.xlsx'; $inputFileName = $post["tmp_name"]; Result::model()->alsjkd($inputFileName); //var_dump($post); try { $objPHPExcel = PHPExcel_IOFactory::load($inputFileName); } catch (PHPExcel_Reader_Exception $e) { die('Error loading file: ' . $e->getMessage()); } $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumnIndex = $sheet->getHighestColumn(); $index = array('A' => 1, 'B' => 2, 'C' => 3, 'D' => 4, 'E' => 5, 'F' => 6, 'G' => 7, 'H' => 8); $highestColumn = $index[$highestColumnIndex]; $index = array_slice($index, 0, $highestColumn); $element = new Element(); $element->stepId = $id; $element->typeId = 13; $element->save(); $elementId = $element->id; $step = $this->loadModel($id); for ($row = 1; $row <= $highestRow; $row++) { $rows[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow('A', $row)->getValue(); } $columns = array(); //for ($column =1 ; $column<=$highestColumn; $column++){ foreach ($index as $key => $position) { $columns[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($position, 1)->getValue(); } $salida = $step->addGrid($element, $rows, $columns); $issue = new Issue(); //$travelerId= $step->travelerId; for ($row = 1; $row <= $highestRow; $row++) { foreach ($index as $key => $position) { if ($row >= 1 && $position > 0) { $result = new Result(); $result->elementId = $elementId; $result->issueId = $issueId; $result->value = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($position, $row)->getValue(); //throw new CHttpException(403, 'row: '.$row.'. Columna: '.$position.'.Value: '.$result->value); $result->colonne = $position - 1; $result->ligne = $row - 2; $result->save(); } //throw new CHttpException (403,"exception: ". $iasdasdeId); //$objPHPExcel->getActiveSheet()->getCellByColumnAndRow('A',$row)->getValue(); } } $alsdjk = $LKSJAD; } else { $alsdjk = $LKSJAD; throw new CHttpException(403, "Error in the file"); } }
<?php Yii::import('ext.phpexcel.XPHPExcel'); $objPHPExcel = XPHPExcel::createPHPExcel(); // Set document properties $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("")->setCategory(""); // Add some data $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Hello')->setCellValue('B2', 'world!')->setCellValue('C1', 'Hello')->setCellValue('D2', 'world!'); // Miscellaneous glyphs, UTF-8 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', 'Miscellaneous glyphs')->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç'); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Simple'); // 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="archivo.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');