foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
// echo "<br>Worksheet " . $worksheetTitle . " memiliki ";
// echo $nrColumns . ' kolom (A-' . $highestColumn . ') ';
// echo ' dan ' . $highestRow . ' baris.';
// echo '<br>Data: <table border="1"><tr>';
for ($row = 1; $row <= $highestRow; ++$row) {
// echo '<tr>';
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
// echo '<td>' . $val . '<br>(Typ ' . $dataType . ')</td>';
}
// echo '</tr>';
}
// echo '</table>';
}
//penambahan status jumlah
$i = 1;
$j = 1;
$k = 1;
$l = 1;
$m = 1;
// Proses perulangan baris file excel yang diupload
for ($row = 2; $row <= $highestRow; ++$row) {
$val = array();
/**
* Bind value to a cell
*
* @param PHPExcel_Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
* @return boolean
*/
public function bindValue(PHPExcel_Cell $cell, $value = null)
{
// Set value explicit
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::dataTypeForValue($value));
// Done!
return true;
}
public function parsePrices()
{
require_once Yii::getAlias('@frontend') . '/components/third_party/PHPExcel/IOFactory.php';
$this->reader = \PHPExcel_IOFactory::load($this->uploaded_file->tempName);
$column_titles = [];
foreach ($this->reader->getWorksheetIterator() as $worksheet) {
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, 1);
$val = $cell->getValue();
if (!in_array($val, $this->all_titles)) {
continue;
}
$column_titles[] = $val;
}
if (!$this->validateExcelFormat($column_titles)) {
return false;
}
for ($row = 2; $row <= $highestRow; ++$row) {
$product = [];
$valid_row = true;
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$title_cell = $worksheet->getCellByColumnAndRow($col, 1);
$title_cell_value = $title_cell->getValue();
if (!in_array($title_cell_value, $this->all_titles)) {
continue;
}
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
if (in_array($title_cell_value, $this->required_titles) && empty($val)) {
$valid_row = false;
break;
}
$dataType = \PHPExcel_Cell_DataType::dataTypeForValue($val);
$product[$column_titles[$col]] = $val;
}
if (!$valid_row) {
continue;
}
$product['organization_id'] = $this->organization->id;
$this->products_data[] = $product;
}
}
$column_titles[] = 'organization_id';
$category_array = $this->addCategories();
foreach ($this->products_data as &$product) {
foreach ($category_array as $id => $category_name) {
if ($product['category'] == $category_name) {
$product['category'] = $id;
}
}
}
return OrganizationsProducts::insertFromExcel($this->products_data, $column_titles);
}
/**
* Bind value to a cell
*
* @param PHPExcel_Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
* @return boolean
*/
public function bindValue(PHPExcel_Cell $cell, $value = null)
{
// sanitize UTF-8 strings
if (is_string($value)) {
$value = PHPExcel_Shared_String::SanitizeUTF8($value);
}
// Set value explicit
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::dataTypeForValue($value));
// Done!
return true;
}
/**
* Set cell value
*
* This clears the cell formula.
*
* @param mixed $pValue Value
* @param bool $pUpdateDataType Update the data type?
*/
public function setValue($pValue = null, $pUpdateDataType = true)
{
$this->_value = $pValue;
if ($pUpdateDataType) {
$this->_dataType = PHPExcel_Cell_DataType::dataTypeForValue($pValue);
}
}
public function upload_excel_file()
{
$user = User_helper::get_user();
if ($_FILES["file"]['name'] != "") {
$arr = explode(".", $_FILES["file"]['name']);
$ext = $arr[sizeof($arr) - 1];
}
$size = $_FILES["file"]['size'];
$this->load->library('upload');
$fileName = 'template_' . $user->id . '_' . date('Ymdhis');
if ($ext == 'xls' || $ext == 'xlsx') {
System_helper::upload_excel_file($fileName, $save_dir = "uploads/excel", $max_size = 60000, $types = 'xls|xlsx');
if ($ext == 'xls') {
$path = "uploads/excel/" . $fileName . '.xls';
} else {
$path = "uploads/excel/" . $fileName . '.xlsx';
}
if ($size < 60000) {
$i = 0;
$totalcount = 0;
$objPHPExcel = PHPExcel_IOFactory::load($path);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
for ($row = 1; $row <= $highestRow; ++$row) {
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
}
}
}
$total_men = 0;
$total_women = 0;
$total_tribe = 0;
$total_disability = 0;
$total_services = 0;
$total_income = 0;
$check_income = false;
for ($row = 3; $row <= $highestRow; ++$row) {
$val = array();
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val[] = $cell->getValue();
}
if ($val[3] != "" && $val[3] != null) {
$serial[$i] = System_helper::Get_Bng_to_Eng($val[0]);
$customer_name[$i] = $val[1];
$gender[$i] = $val[2];
$service_name[$i] = $val[3];
$amount[$i] = System_helper::Get_Bng_to_Eng(abs($val[4]));
if (strlen($amount[$i]) > 6) {
$check_income = true;
}
if ($this->Service_template_model->check_uisc_service_existence($service_name[$i])) {
$totalcount = $totalcount + $i;
$total_services++;
$total_income = $total_income + System_helper::Get_Bng_to_Eng($amount[$i]);
if ($gender[$i] == $this->lang->line('MALE_VAL')) {
$total_men++;
} elseif ($gender[$i] == $this->lang->line('FEMALE_VAL')) {
$total_women++;
} elseif ($gender[$i] == $this->lang->line('TRIBE_VAL')) {
$total_tribe++;
} elseif ($gender[$i] == $this->lang->line('DISABILITY_VAL')) {
$total_disability++;
}
}
++$i;
}
}
$DateCell = $worksheet->getCellByColumnAndRow('1', '1');
$invDateRaw = $DateCell->getValue();
if (is_float($invDateRaw)) {
$newDate = System_helper::ExcelToPHPDate($invDateRaw);
$invDate = date('Y-m-d', $newDate);
} else {
$invDate = $invDateRaw;
}
if ($this->Service_template_model->chk_existing_uploded_excel_file($invDate) < 2) {
$invoice_data = array();
$zilla_invoice_data = array();
$invoice_details_data = array();
$zilla_invoice_details_data = array();
$user_zilla = $user->zilla;
$zilla_table_invoice = str_pad($user_zilla, 2, "0", STR_PAD_LEFT) . '_invoices';
$zilla_table_invoice_details = str_pad($user_zilla, 2, "0", STR_PAD_LEFT) . '_invoice_details';
$uisc_id = $user->uisc_id;
$user_group_id = $user->user_group_id;
$division = $user->division;
$zilla = $user->zilla;
$upazila = $user->upazila;
$unioun = $user->unioun;
$citycorporation = $user->citycorporation;
//$citycorporationward = $user->citycorporationward;
$municipal = $user->municipal;
//$municipalward = $user->municipalward;
$invoice_date = $invDate;
$customerPost = $customer_name;
$servicePost = $service_name;
$genderPost = $gender;
$earningPost = $amount;
$count = sizeof($customerPost);
$invoice_data['uisc_id'] = $uisc_id;
$invoice_data['unionid'] = $unioun;
$invoice_data['municipalid'] = $municipal;
$invoice_data['citycorporationid'] = $citycorporation;
$invoice_data['upazilaid'] = $upazila;
$invoice_data['zillaid'] = $zilla;
$invoice_data['divid'] = $division;
$invoice_data['type'] = $user_group_id;
$invoice_data['invoice_date'] = $invoice_date;
$invoice_data['total_income'] = $total_income;
$invoice_data['total_service'] = $total_services;
$invoice_data['total_men'] = $total_men;
$invoice_data['total_women'] = $total_women;
$invoice_data['total_tribe'] = $total_tribe;
$invoice_data['total_disability'] = $total_disability;
$zilla_invoice_data['uisc_id'] = $uisc_id;
$zilla_invoice_data['unionid'] = $unioun;
$zilla_invoice_data['municipalid'] = $municipal;
$zilla_invoice_data['citycorporationid'] = $citycorporation;
$zilla_invoice_data['upazilaid'] = $upazila;
$zilla_invoice_data['zillaid'] = $zilla;
$zilla_invoice_data['divid'] = $division;
$zilla_invoice_data['type'] = $user_group_id;
$zilla_invoice_data['invoice_date'] = $invoice_date;
$zilla_invoice_data['total_income'] = $total_income;
$zilla_invoice_data['total_service'] = $total_services;
$zilla_invoice_data['total_men'] = $total_men;
$zilla_invoice_data['total_women'] = $total_women;
// echo $zilla_table_invoice;
// print_r($zilla_invoice_data);exit;
if (!$check_income) {
if ($total_services > 0) {
$this->db->trans_start();
//DB Transaction Handle START
//$delete_invoice_data['invoice_date'] = $invoice_date;
//Query_helper::delete('invoices', $delete_invoice_data);
if ($this->Service_template_model->delete_invoice_data($invoice_date)) {
$invoice_id = Query_helper::add('invoices', $invoice_data);
$zilla_invoice_id = Query_helper::add($zilla_table_invoice, $zilla_invoice_data);
for ($i = 0; $i < $count; $i++) {
if ($this->Service_template_model->check_uisc_service_existence($servicePost[$i])) {
list($service_id, $service_name) = $this->Service_template_model->check_uisc_service_existence($servicePost[$i]);
$invoice_details_data['invoice_id'] = $invoice_id;
$invoice_details_data['receiver_name'] = $customerPost[$i];
$invoice_details_data['receiver_sex'] = $genderPost[$i];
$invoice_details_data['service_id'] = $service_id;
//$this->Service_template_model->get_service_id($servicePost[$i]);
$invoice_details_data['income'] = System_helper::Get_Bng_to_Eng($earningPost[$i]);
$invoice_details_data['service_name'] = $service_name;
//$this->Service_template_model->get_service_name($servicePost[$i]);
$zilla_invoice_details_data['invoice_id'] = $zilla_invoice_id;
$zilla_invoice_details_data['receiver_name'] = $customerPost[$i];
$zilla_invoice_details_data['receiver_sex'] = $genderPost[$i];
$zilla_invoice_details_data['service_id'] = $service_id;
//$this->Service_template_model->get_service_id($servicePost[$i]);
$zilla_invoice_details_data['income'] = System_helper::Get_Bng_to_Eng($earningPost[$i]);
$zilla_invoice_details_data['service_name'] = $service_name;
//$this->Service_template_model->get_service_name($servicePost[$i]);
Query_helper::add('invoice_details', $invoice_details_data);
Query_helper::add($zilla_table_invoice_details, $zilla_invoice_details_data);
}
}
$fileInfo = array('user_id' => $user->id, 'uisc_id' => $uisc_id, 'file_name' => $fileName, 'upload_date' => strtotime($invDate), 'create_date' => time());
Query_helper::add($this->config->item('table_excel_history'), $fileInfo);
$this->db->trans_complete();
//DB Transaction Handle END
if ($this->db->trans_status() === TRUE) {
$this->message = $this->lang->line("MSG_CREATE_SUCCESS");
$this->dcms_add();
} else {
$ajax['status'] = false;
$ajax['system_message'] = $this->lang->line("MSG_CREATE_FAIL");
$this->jsonReturn($ajax);
}
} else {
$ajax['status'] = false;
$ajax['system_message'] = $this->lang->line("MSG_CREATE_FAIL");
$this->jsonReturn($ajax);
}
} else {
$ajax['status'] = false;
$ajax['system_message'] = $this->lang->line("NO_SERVICES_IN_UISC");
$this->jsonReturn($ajax);
}
} else {
$ajax['status'] = false;
$ajax['system_message'] = $this->lang->line("MSG_INCOME_AMOUNT_INVALID");
$this->jsonReturn($ajax);
}
} else {
$ajax['status'] = false;
$ajax['system_message'] = $this->lang->line("MSG_MAXIMUM_NUMBER_OF_FILES");
$this->jsonReturn($ajax);
}
} else {
$ajax['status'] = false;
$ajax['system_message'] = $this->lang->line("MSG_MAX_SIZE");
$this->jsonReturn($ajax);
}
} else {
$ajax['status'] = false;
$ajax['system_message'] = $this->lang->line("MSG_EXCEL_ONLY");
$this->jsonReturn($ajax);
}
}
/**
* Generates the sheet's workbook...
*
* @param String format extension
*/
function _generateSheets($format){
$sheets= array();
$sheets= $this->book->getSheets();
$i= 0;
if ($format=="ods"){
foreach($sheets as $sheet){
$cells= array();
$cells= $sheet->getCells();
foreach($cells as $cellarray){
foreach($cellarray as $cell){
$col= $cell->getDataColumn();
$row= $cell->getDataRow();
$data= $cell->getFormula();
$fontId= $cell->getFontStyleId();
$fontStyle= new FontStyle();
$fontStyle= $this->book->getFontStyle($fontId);
if (substr($data, 0, 1)== '=')
$this->objPHPOds->addCell($i,$row,$col,substr($data, 1),'float');
//TODO
else /*OJO CON ESTO DISCERNIR ENTRE LOS DIFERENTES TIPOS*/
$this->objPHPOds->addCell($i,$row,$col,$data,'string');
}
$this->objPHPOds->addStyle($fontStyle, $cell);
}
$i++;
}
}
else{
foreach($sheets as $sheet){
if ($i>0)
$this->objPHPExcel->createSheet();
$this->objPHPExcel->setActiveSheetIndex($i);
$j= $i + 1;
$this->objPHPExcel->getActiveSheet()->setTitle("Sheet $j");
$cells= array();
$cells= $sheet->getCells();
foreach($cells as $cellarray){
$cell= new Cell();
foreach ($cellarray as $cell){
$col= $cell->getDataColumn();
$row= $cell->getDataRow();
$row++;
$data= $cell->getFormula();
// $this->objPHPExcel= new PHPExcel();
$fontId= $cell->getFontStyleId();
$fontStyle= new FontStyle();
$fontStyle= $this->book->getFontStyle($fontId);
$fontName= $fontStyle->getFontName();
$fcolor= substr($fontStyle->getFontColor(),1);
if ($fcolor == "000000"){
//echo "$row $col $fcolor<hr>";
$ncolor= new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK);
$ncolor->setRGB($fcolor);
}
else{
//echo "$row $col $fcolor<hr>";
$ncolor= new PHPExcel_Style_Color();
$ncolor->setRGB($fcolor);
}
$style= new PHPExcel_Style();
$style->getFont()->setColor($ncolor);
$style->getFont()->setName($fontName);
$style->getFont()->setBold($fontStyle->getFontBold()== 1);
$style->getFont()->setItalic($fontStyle->getFontItalic()==1);
$style->getFont()->setSize($fontStyle->getFontSize());
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
switch ($fontStyle->fontHAlign){
case 0:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
break;
case 1:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
break;
case 2:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
break;
case 3:
$HzAlign= PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
break;
}
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
switch ($fontStyle->fontVAlign){
case 0:
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
break;
case 1:
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_CENTER;
break;
case 2:
$VlAlign= PHPExcel_Style_Alignment::VERTICAL_TOP;
}
$style->getAlignment()->setHorizontal($HzAlign);
$style->getAlignment()->setVertical($VlAlign);
if ($fontStyle->getFontUnderline()!= 0){
$style->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
}
$this->objPHPExcel->getActiveSheet()->duplicateStyle($style, PHPExcel_Cell::stringFromColumnIndex($col) . $row);
$this->objPHPExcel->getActiveSheet()->getCellByColumnAndRow($col, $row)->setValueExplicit($data, PHPExcel_Cell_DataType::dataTypeForValue($data));
}
}
$i++;
}
}
}
function importXls()
{
$this->uri->segment(3);
$this->load->library('excel');
$this->load->library('PHPExcel/iofactory');
error_reporting(E_ALL ^ E_NOTICE);
/*
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
}
echo "\n";
*/
$objPHPExcel = PHPExcel_IOFactory::load('./upload/update_sbu_all 2012.xls');
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow();
// e.g. 10
$highestColumn = $worksheet->getHighestColumn();
// e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
echo "<br>The worksheet " . $worksheetTitle . " has ";
echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
echo ' and ' . $highestRow . ' row.';
echo '<br>Data: <table border="1"><tr>';
for ($row = 1; $row <= $highestRow; ++$row) {
echo '<tr>';
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo '<td>' . $val . '<br>(Typ ' . $dataType . ')</td>';
}
echo '</tr>';
}
echo '</table>';
}
}
public function cargar_excel_lector($curso_select)
{
$this->load->library('excel');
$name = $_FILES['archivo']['name'];
$tname = $_FILES['archivo']['tmp_name'];
$html = '';
$contenedor = '';
$objPHPExcel = PHPExcel_IOFactory::load($tname);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow();
// e.g. 10
$highestColumn = $worksheet->getHighestColumn();
// e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
$boton = '<a class="btn btn-default btn-sm btn-primary" id="capturar" value="">Capturar</a>';
$html .= '<table class="table table-hover table-striped table-condensed"> ';
for ($row = 2; $row <= $highestRow; ++$row) {
$id_usuario = 0;
$contador = $row - 1;
$html .= '<tr fila=' . $contador . ' id=tr' . $contador . ' >';
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
if ($col == 0) {
$rut = $val;
$respuestas = array();
}
if ($val == null) {
$val = 'x';
}
$estado = 'disabled';
if ($usuario = $this->alumno_model->get_id("{$rut}")) {
foreach ($usuario as $key => $value) {
$id_usuario = $value->id_usuario;
}
if ($this->alumno_model->curso_has_usuario($id_usuario, $curso_select)) {
array_push($respuestas, $val . ",");
$html .= '<td class="user' . $id_usuario . '">' . $val . '</td>';
$estado = '';
} else {
$html .= '<td style="color: aqua;background-color: yellow;">' . $val . '</td>';
}
} else {
$html .= '<td style="color: aqua;background-color: #FFA97F;">' . $val . '</td>';
}
}
$contenedor .= '<div id=div' . $id_usuario . ' style="display: none;" id_usuario=' . $id_usuario . '>' . implode($respuestas) . '</div>';
$html .= '<td><button ' . $estado . ' id_usuario=' . $id_usuario . ' type="button" class="btnRecorrer btn btn-default btn-sm " data-fancybox-type="iframe" style="color: #f8f8f8; background-color: #2AA92E;"><i class="glyphicon glyphicon-cloud-upload"></i></button></td>';
$html .= '</tr>';
//print_r("$respuestas");
}
$html .= '</table>';
}
// var_dump($respuestas);
$data['filas'] = $html;
$data['contenedor'] = $contenedor;
//$data['boton'] = $boton;
echo json_encode($data);
}
