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