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); }