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();
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);
}
/**
* DataType for value
*
* @param mixed $pValue
* @return int
*/
public static function dataTypeForValue($pValue = null)
{
// Match the value against a few data types
if (is_null($pValue)) {
return PHPExcel_Cell_DataType::TYPE_NULL;
} elseif ($pValue === '') {
return PHPExcel_Cell_DataType::TYPE_STRING;
} elseif ($pValue instanceof PHPExcel_RichText) {
return PHPExcel_Cell_DataType::TYPE_STRING;
} elseif ($pValue[0] === '=') {
return PHPExcel_Cell_DataType::TYPE_FORMULA;
} elseif (is_bool($pValue)) {
return PHPExcel_Cell_DataType::TYPE_BOOL;
} elseif (preg_match('/^\\-?[0-9]*\\.?[0-9]*$/', $pValue)) {
return PHPExcel_Cell_DataType::TYPE_NUMERIC;
} elseif (array_key_exists($pValue, PHPExcel_Cell_DataType::getErrorCodes())) {
return PHPExcel_Cell_DataType::TYPE_ERROR;
} else {
return PHPExcel_Cell_DataType::TYPE_STRING;
}
}
/**
* Write a formula to the specified row and column (zero indexed).
* The textual representation of the formula is passed to the parser in
* Parser.php which returns a packed binary string.
*
* Returns 0 : normal termination
* -1 : formula errors (bad formula)
* -2 : row or column out of range
*
* @param integer $row Zero indexed row
* @param integer $col Zero indexed column
* @param string $formula The formula text string
* @param mixed $xfIndex The XF format index
* @param mixed $calculatedValue Calculated value
* @return integer
*/
private function _writeFormula($row, $col, $formula, $xfIndex, $calculatedValue)
{
$record = 0x6;
// Record identifier
// Initialize possible additional value for STRING record that should be written after the FORMULA record?
$stringValue = null;
// calculated value
if (isset($calculatedValue)) {
// Since we can't yet get the data type of the calculated value,
// we use best effort to determine data type
if (is_bool($calculatedValue)) {
// Boolean value
$num = pack('CCCvCv', 0x1, 0x0, (int) $calculatedValue, 0x0, 0x0, 0xffff);
} elseif (is_int($calculatedValue) || is_float($calculatedValue)) {
// Numeric value
$num = pack('d', $calculatedValue);
} elseif (is_string($calculatedValue)) {
if (array_key_exists($calculatedValue, PHPExcel_Cell_DataType::getErrorCodes())) {
// Error value
$num = pack('CCCvCv', 0x2, 0x0, self::_mapErrorCode($calculatedValue), 0x0, 0x0, 0xffff);
} elseif ($calculatedValue === '') {
// Empty string (and BIFF8)
$num = pack('CCCvCv', 0x3, 0x0, 0x0, 0x0, 0x0, 0xffff);
} else {
// Non-empty string value (or empty string BIFF5)
$stringValue = $calculatedValue;
$num = pack('CCCvCv', 0x0, 0x0, 0x0, 0x0, 0x0, 0xffff);
}
} else {
// We are really not supposed to reach here
$num = pack('d', 0x0);
}
} else {
$num = pack('d', 0x0);
}
$grbit = 0x3;
// Option flags
$unknown = 0x0;
// Must be zero
// Strip the '=' or '@' sign at the beginning of the formula string
if ($formula[0] == '=') {
$formula = substr($formula, 1);
} else {
// Error handling
$this->_writeString($row, $col, 'Unrecognised character for formula');
return -1;
}
// Parse the formula using the parser in Parser.php
try {
$error = $this->_parser->parse($formula);
$formula = $this->_parser->toReversePolish();
$formlen = strlen($formula);
// Length of the binary string
$length = 0x16 + $formlen;
// Length of the record data
$header = pack("vv", $record, $length);
$data = pack("vvv", $row, $col, $xfIndex) . $num . pack("vVv", $grbit, $unknown, $formlen);
$this->_append($header . $data . $formula);
// Append also a STRING record if necessary
if ($stringValue !== null) {
$this->_writeStringRecord($stringValue);
}
return 0;
} catch (PHPExcel_Exception $e) {
// do nothing
}
}
/**
* Set the value for a cell, with the explicit data type passed to the method (bypassing any use of the value binder)
*
* @param mixed $pValue Value
* @param string $pDataType Explicit data type
* @return PHPExcel_Cell
* @throws PHPExcel_Exception
*/
public function setValueExplicit($pValue = NULL, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
{
// set the value according to data type
switch ($pDataType) {
case PHPExcel_Cell_DataType::TYPE_STRING2:
$pDataType = PHPExcel_Cell_DataType::TYPE_STRING;
case PHPExcel_Cell_DataType::TYPE_STRING:
case PHPExcel_Cell_DataType::TYPE_NULL:
case PHPExcel_Cell_DataType::TYPE_INLINE:
$this->_value = PHPExcel_Cell_DataType::checkString($pValue);
break;
case PHPExcel_Cell_DataType::TYPE_NUMERIC:
$this->_value = (double) $pValue;
break;
case PHPExcel_Cell_DataType::TYPE_FORMULA:
$this->_value = (string) $pValue;
break;
case PHPExcel_Cell_DataType::TYPE_BOOL:
$this->_value = (bool) $pValue;
break;
case PHPExcel_Cell_DataType::TYPE_ERROR:
$this->_value = PHPExcel_Cell_DataType::checkErrorCode($pValue);
break;
default:
throw new PHPExcel_Exception('Invalid datatype: ' . $pDataType);
break;
}
// set the datatype
$this->_dataType = $pDataType;
return $this->notifyCacheController();
}
/**
* DataType for value
*
* @param mixed $pValue
* @return int
*/
public static function dataTypeForValue($pValue = null) {
// Match the value against a few data types
if (is_null ( $pValue )) {
return PHPExcel_Cell_DataType::TYPE_NULL;
} elseif ($pValue === '') {
return PHPExcel_Cell_DataType::TYPE_STRING;
} elseif ($pValue instanceof PHPExcel_RichText) {
return PHPExcel_Cell_DataType::TYPE_STRING;
} elseif ($pValue {0} === '=' && strlen ( $pValue ) > 1) {
return PHPExcel_Cell_DataType::TYPE_FORMULA;
} elseif (is_bool ( $pValue )) {
return PHPExcel_Cell_DataType::TYPE_BOOL;
} elseif (is_float ( $pValue ) || is_int ( $pValue )) {
return PHPExcel_Cell_DataType::TYPE_NUMERIC;
} elseif (preg_match ( '/^\-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)$/', $pValue )) {
return PHPExcel_Cell_DataType::TYPE_NUMERIC;
} elseif (is_string ( $pValue ) && array_key_exists ( $pValue, PHPExcel_Cell_DataType::getErrorCodes () )) {
return PHPExcel_Cell_DataType::TYPE_ERROR;
} else {
return PHPExcel_Cell_DataType::TYPE_STRING;
}
}
/**
* DataType for value
*
* @param mixed $pValue
* @return string
*/
public static function dataTypeForValue($pValue = null)
{
// Match the value against a few data types
if ($pValue === null) {
return PHPExcel_Cell_DataType::TYPE_NULL;
} elseif ($pValue === '') {
return PHPExcel_Cell_DataType::TYPE_STRING;
} elseif ($pValue instanceof PHPExcel_RichText) {
return PHPExcel_Cell_DataType::TYPE_INLINE;
} elseif ($pValue[0] === '=' && strlen($pValue) > 1) {
return PHPExcel_Cell_DataType::TYPE_FORMULA;
} elseif (is_bool($pValue)) {
return PHPExcel_Cell_DataType::TYPE_BOOL;
} elseif (is_float($pValue) || is_int($pValue)) {
return PHPExcel_Cell_DataType::TYPE_NUMERIC;
} elseif (preg_match('/^[\\+\\-]?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)([Ee][\\-\\+]?[0-2]?\\d{1,3})?$/', $pValue)) {
$tValue = ltrim($pValue, '+-');
if (is_string($pValue) && $tValue[0] === '0' && strlen($tValue) > 1 && $tValue[1] !== '.') {
return PHPExcel_Cell_DataType::TYPE_STRING;
} elseif (strpos($pValue, '.') === false && $pValue > PHP_INT_MAX) {
return PHPExcel_Cell_DataType::TYPE_STRING;
}
return PHPExcel_Cell_DataType::TYPE_NUMERIC;
} elseif (is_string($pValue) && array_key_exists($pValue, PHPExcel_Cell_DataType::getErrorCodes())) {
return PHPExcel_Cell_DataType::TYPE_ERROR;
}
return PHPExcel_Cell_DataType::TYPE_STRING;
}
/**
* 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);
}
}
/**
* Get cell value
*
* Get the value for a specific data type
*
* @todo Check the actual usefulness of this method
*
* @param $value
* @param string $cellDataType
* @return bool|float|mixed|string
* @throws Exception
*/
public function getCellValue($value, $cellDataType = '')
{
if ($cellDataType == '') {
return $value;
}
switch ($cellDataType) {
case PHPExcel_Cell_DataType::TYPE_STRING2:
case PHPExcel_Cell_DataType::TYPE_STRING:
case PHPExcel_Cell_DataType::TYPE_NULL:
case PHPExcel_Cell_DataType::TYPE_INLINE:
return PHPExcel_Cell_DataType::checkString($value);
case PHPExcel_Cell_DataType::TYPE_NUMERIC:
return (double) $value;
case PHPExcel_Cell_DataType::TYPE_FORMULA:
return '=FORMULA(' . (string) $value . ')';
case PHPExcel_Cell_DataType::TYPE_BOOL:
return (bool) $value;
case PHPExcel_Cell_DataType::TYPE_ERROR:
return PHPExcel_Cell_DataType::checkErrorCode($value);
default:
throw new Exception('Invalid datatype: ' . $cellDataType);
break;
}
}
/**
* 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);
}
