public static function excelParsing($fileExcel) { // $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3; /* here i added */ // $cacheEnabled = \PHPExcel_Settings::setCacheStorageMethod($cacheMethod); // if (!$cacheEnabled) { // echo "### WARNING - Sqlite3 not enabled ###" . PHP_EOL; // } $objPHPExcel = new \PHPExcel(); //$fileExcel = Yii::getAlias('@webroot/templates/operator.xls'); $inputFileType = \PHPExcel_IOFactory::identify($fileExcel); $objReader = \PHPExcel_IOFactory::createReader($inputFileType); $objReader->setReadDataOnly(true); /** Load $inputFileName to a PHPExcel Object * */ $objPHPExcel = $objReader->load($fileExcel); $total_sheets = $objPHPExcel->getSheetCount(); $allSheetName = $objPHPExcel->getSheetNames(); $objWorksheet = $objPHPExcel->setActiveSheetIndex(0); $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); for ($row = 1; $row <= $highestRow; ++$row) { for ($col = 0; $col < $highestColumnIndex; ++$col) { $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); $arraydata[$row - 1][$col] = $value; } } return $arraydata; }
public function ProcessFileContent() { $objPHPExcel = PHPExcel_IOFactory::load($this->file); // Format is as follows: // (gray bg) [ <description of data> ], <relation1>, <relationN> // <srcConcept>, <tgtConcept1>, <tgtConceptN> // <srcAtomA>, <tgtAtom1A>, <tgtAtomNA> // <srcAtomB>, <tgtAtom1B>, <tgtAtomNB> // <srcAtomC>, <tgtAtom1C>, <tgtAtomNC> // Output is function call: // InsPair($relation,$srcConcept,$srcAtom,$tgtConcept,$tgtAtom) // Loop over all worksheets foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { // Loop through all rows $highestrow = $worksheet->getHighestRow(); $highestcolumn = $worksheet->getHighestColumn(); $highestcolumnnr = PHPExcel_Cell::columnIndexFromString($highestcolumn); $row = 1; // Go to the first row where a table starts. for ($i = $row; $i <= $highestrow; $i++) { $row = $i; if (substr($worksheet->getCell('A' . $row)->getValue(), 0, 1) === '[') { break; } } // We are now at the beginning of a table or at the end of the file. $line = array(); // Line is a buffer of one or more related (subsequent) excel rows while ($row <= $highestrow) { // Read this line as an array of values $values = array(); // values is a buffer containing the cells in a single excel row for ($columnnr = 0; $columnnr < $highestcolumnnr; $columnnr++) { $columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr); $cell = $worksheet->getCell($columnletter . $row); $cellvalue = (string) $cell->getCalculatedValue(); // overwrite $cellvalue in case of datetime // the @ is a php indicator for a unix timestamp (http://php.net/manual/en/datetime.formats.compound.php), later used for typeConversion if (PHPExcel_Shared_Date::isDateTime($cell) && !empty($cellvalue)) { $cellvalue = '@' . (string) PHPExcel_Shared_Date::ExcelToPHP($cellvalue); } $values[] = $cellvalue; } $line[] = $values; // add line (array of values) to the line buffer $row++; // Is this relation table done? Then we parse the current values into function calls and reset it $firstCellInRow = (string) $worksheet->getCell('A' . $row)->getCalculatedValue(); if (substr($firstCellInRow, 0, 1) === '[') { // Relation table is complete, so it can be processed. $this->ParseLines($line); $line = array(); } } // Last relation table remains to be processed. $this->ParseLines($line); $line = array(); } }
function read_ilias_users($fname) { $xls = PHPExcel_IOFactory::load($fname); $xls->setActiveSheetIndex(0); $sheet = $xls->getActiveSheet(); $nRow = $sheet->getHighestRow(); $nColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); $arr = []; for ($i = 5; $i <= $nRow; $i++) { for ($j = 0; $j <= $nColumn; $j++) { $row[$j] = trim($sheet->getCellByColumnAndRow($j, $i)->getValue()); } if ($row[0] != '' and $row[1] != '') { $fname = explode(' ', trim($row[0])); $lname = explode(' ', trim($row[1])); foreach ($fname as $item) { if ($item != '') { $arr[$i][] = $item; } } foreach ($lname as $item) { if ($item != '') { $arr[$i][] = $item; } } } } return $arr; }
/** * */ public function parseResource() { $configuration = $this->getConfiguration(); if (!ExtensionManagementUtility::isLoaded('phpexcel_library')) { throw new \Exception('phpexcel_library is not loaded', 12367812368); } $filename = GeneralUtility::getFileAbsFileName($this->filepath); GeneralUtility::makeInstanceService('phpexcel'); $objReader = \PHPExcel_IOFactory::createReaderForFile($filename); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($filename); if ($configuration['sheet'] >= 0) { $objWorksheet = $objPHPExcel->getSheet($configuration['sheet']); } else { $objWorksheet = $objPHPExcel->getActiveSheet(); } $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); for ($row = 1 + $configuration['skipRows']; $row <= $highestRow; ++$row) { $rowRecord = []; for ($col = 0; $col <= $highestColumnIndex; ++$col) { $rowRecord[] = trim($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()); } $this->content[] = $rowRecord; } }
public function excel2array($filename) { //把excel转化成数组 $Reader = $this->getReader($filename); if (!$Reader) { return false; } set_time_limit(0); @ini_set('memory_limit', '256M'); $Reader->setReadDataOnly(true); //只取出数据 $PHPExcel = $Reader->load($filename); $WorkSheet = $PHPExcel->getActiveSheet(); $highestRow = $WorkSheet->getHighestDataRow(); //行数 $highestCol = $WorkSheet->getHighestDataColumn(); //列数 $highestColIndex = PHPExcel_Cell::columnIndexFromString($highestCol); //列名转化为索引 $data = array(); for ($row = 1; $row <= $highestRow; ++$row) { for ($col = 0; $col <= $highestColIndex; ++$col) { $cell_value = $WorkSheet->getCellByColumnAndRow($col, $row)->getValue(); $data[$row][$col] = $cell_value; } } $PHPExcel->disconnectWorksheets(); unset($PHPExcel); return $data; }
public function select($source) { $path = $this->connection; $excel = PHPExcel_IOFactory::createReaderForFile($path); $excel = $excel->load($path); $excRes = new ExcelResult(); $excelWS = $excel->getActiveSheet(); $addFields = true; $coords = array(); if ($source->get_source() == '*') { $coords['start_row'] = 0; $coords['end_row'] = false; } else { $c = array(); preg_match("/^([a-zA-Z]+)(\\d+)/", $source->get_source(), $c); if (count($c) > 0) { $coords['start_row'] = (int) $c[2]; } else { $coords['start_row'] = 0; } $c = array(); preg_match("/:(.+)(\\d+)\$/U", $source->get_source(), $c); if (count($c) > 0) { $coords['end_row'] = (int) $c[2]; } else { $coords['end_row'] = false; } } $i = $coords['start_row']; $end = 0; while ($coords['end_row'] == false && $end < $this->emptyLimit || $coords['end_row'] !== false && $i < $coords['end_row']) { $r = array(); $emptyNum = 0; for ($j = 0; $j < count($this->config->text); $j++) { $col = PHPExcel_Cell::columnIndexFromString($this->config->text[$j]['name']) - 1; $cell = $excelWS->getCellByColumnAndRow($col, $i); if ($cell->getDataType() == 'f') { $r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getCalculatedValue(); } else { $r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getValue(); } if ($r[PHPExcel_Cell::stringFromColumnIndex($col)] == '') { $emptyNum++; } } if ($emptyNum < count($this->config->text)) { $r['id'] = $i; $excRes->addRecord($r); $end = 0; } else { if (DHX_IGNORE_EMPTY_ROWS == false) { $r['id'] = $i; $excRes->addRecord($r); } $end++; } $i++; } return $excRes; }
public function ukAmazonFees() { $this->layout = ''; $this->autoRender = false; $this->loadModel('AmazonFee'); $this->loadModel('Location'); App::import('Vendor', 'PHPExcel/IOFactory'); $objPHPExcel = new PHPExcel(); $objReader = PHPExcel_IOFactory::createReader('CSV'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load('files/uk_amazon_fees.csv'); $objWorksheet = $objPHPExcel->setActiveSheetIndex(0); $lastRow = $objPHPExcel->getActiveSheet()->getHighestRow(); $colString = $highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn(); $colNumber = PHPExcel_Cell::columnIndexFromString($colString); for ($i = 2; $i <= $lastRow; $i++) { $this->request->data['category'] = $objWorksheet->getCellByColumnAndRow(0, $i)->getValue(); $this->request->data['referral_fee'] = $objWorksheet->getCellByColumnAndRow(1, $i)->getValue(); $this->request->data['app_min_referral_fee'] = $objWorksheet->getCellByColumnAndRow(2, $i)->getValue(); $country = $this->Location->find('first', array('conditions' => array('Location.county_name' => $objWorksheet->getCellByColumnAndRow(3, $i)->getValue()))); $this->request->data['country'] = $country['Location']['id']; $this->request->data['platform'] = $objWorksheet->getCellByColumnAndRow(4, $i)->getValue(); $this->AmazonFee->create(); $this->AmazonFee->save($this->request->data); } }
function read_ou($fname) { $xls = PHPExcel_IOFactory::load($fname); $xls->setActiveSheetIndex(0); $sheet = $xls->getActiveSheet(); $nRow = $sheet->getHighestRow(); $nColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); //$cat = ''; $arr = []; for ($i = 2; $i <= $nRow; $i++) { for ($j = 0; $j <= $nColumn; $j++) { $row[$j] = trim($sheet->getCellByColumnAndRow($j, $i)->getValue()); } if ($row[0] != '' and $row[1] != '' and $row[3] != '') { $code = trim($row[0]); $mr = trim($row[1]); $name = trim($row[3]); if ($mr = validate_mr($mr)) { //$arr[] = ['code'=>$code, 'mr'=>$mr, 'mr_new'=>validate_mr($mr), 'name'=>$name, 'name_new'=>validate_ou($name)]; $arr[] = ['code' => $code, 'mr' => $mr, 'name' => $name]; } } else { //категория //$cat = $row['1']; } } return $arr; }
public function start($data = '') { if (!$this->settings['display_column_names'] or !$data) { return; } if ($this->mode == 'preview') { $this->rows[] = $data; return; } foreach ($data as $pos => $text) { $this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($pos, $this->last_row, $text); } //make first bold $last_column = $this->objPHPExcel->getActiveSheet()->getHighestDataColumn(); $this->objPHPExcel->getActiveSheet()->getStyle("A1:" + $last_column + "1")->getFont()->setBold(true); //rename $this->objPHPExcel->getActiveSheet()->setTitle(__('Orders', 'woocommerce-order-export')); //adjust width for all columns $max_columns = PHPExcel_Cell::columnIndexFromString($last_column); foreach (range(0, $max_columns) as $col) { $this->objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setAutoSize(true); } //freeze $this->objPHPExcel->getActiveSheet()->freezePane('A2'); //save only header on init $objWriter = new PHPExcel_Writer_Excel2007($this->objPHPExcel); $objWriter->save($this->filename); }
function excelToArray($file) { $objReader = PHPExcel_IOFactory::createReader('Excel5'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($file); //读取文件 $objWorksheet = $objPHPExcel->getActiveSheet(0); //读取excel文件中的第一个工作表 $highestRow = $objWorksheet->getHighestRow(); //计算总行数 $highestColumn = $objWorksheet->getHighestColumn(); //取得列数中最大的字母。如(J) $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //通过字母计算总列数 $excelData = array(); //存放读取的数据 for ($row = 2; $row <= $highestRow; ++$row) { //从第二行开始读取数据 for ($col = 0; $col <= $highestColumnIndex; ++$col) { //读取每行中的各列 //把读取的数据放入数组中 $excelData[$row - 2][] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); } } return $excelData; }
public function getExcelContent($filename) { $fixedType = explode(".", basename($filename)); $fixedType = $fixedType[count($fixedType) - 1]; $objReader = \PHPExcel_IOFactory::createReader($fixedType == "xlsx" ? 'Excel2007' : "Excel5"); $objPHPExcel = $objReader->load($filename); $objWorksheet = $objPHPExcel->getActiveSheet(); $totalrow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $totalcolumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //总列数 $data = array(); $total_row = $totalrow; $totalrow = $totalrow > 16 ? 16 : $totalrow; //只取16行数据返回 for ($rowindex = 2; $rowindex <= $totalrow; $rowindex++) { $rowdata = array(); for ($colindex = 0; $colindex < $totalcolumn; $colindex++) { $name = $objWorksheet->getCellByColumnAndRow($colindex, 1)->getValue(); $value = $objWorksheet->getCellByColumnAndRow($colindex, $rowindex)->getValue(); $rowdata[$name] = empty($value) ? "" : $value; } array_push($data, $rowdata); } return array("data" => $data, "recordcount" => $total_row); }
public function post_parse_payments() { $config = array('path' => DOCROOT . 'uploads/csv', 'randomize' => true, 'ext_whitelist' => array('csv')); Upload::process($config); if (Upload::is_valid()) { //Upload::save(); $file = Upload::get_files(); $uploaded_file = $file[0]['file']; Package::load("excel"); $excel = PHPExcel_IOFactory::createReader('CSV')->setDelimiter(',')->setEnclosure('"')->setLineEnding("\n")->setSheetIndex(0)->load($uploaded_file); $objWorksheet = $excel->setActiveSheetIndex(0); $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //read from file for ($row = 1; $row <= $highestRow; ++$row) { $file_data = array(); for ($col = 0; $col <= $highestColumnIndex; ++$col) { $value = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); $file_data[$col] = trim($value); } $result[] = $file_data; } print_r($result); } else { print "Invalid uploads"; } }
/** * (non-PHPdoc) * @see library/Oara/Network/Oara_Network_Publisher_Interface#getMerchantList() */ public function getMerchantList() { $merchants = array(); $params = array(new Oara_Curl_Parameter('cmdDownload', 'Download All Active Merchants'), new Oara_Curl_Parameter('strRelationStatus', 'active')); $urls = array(); $urls[] = new Oara_Curl_Request($this->_domain . '/affiliate/merchants.php', $params); $result = $this->_client->post($urls); $folder = realpath(dirname(__FILE__)) . '/../../data/pdf/'; $my_file = $folder . mt_rand() . '.xls'; $handle = fopen($my_file, 'w') or die('Cannot open file: ' . $my_file); $data = $result[0]; fwrite($handle, $data); fclose($handle); $objReader = PHPExcel_IOFactory::createReader('Excel5'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($my_file); $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); for ($row = 2; $row <= $highestRow; ++$row) { $obj = array(); $obj['cid'] = $objWorksheet->getCellByColumnAndRow(0, $row)->getValue(); $obj['name'] = $objWorksheet->getCellByColumnAndRow(1, $row)->getValue(); $merchants[] = $obj; } unlink($my_file); return $merchants; }
/** * Create a new row iterator * * @param PHPExcel_Worksheet $subject The worksheet to iterate over * @param string $columnIndex The column that we want to iterate * @param integer $startRow The row number at which to start iterating * @param integer $endRow Optionally, the row number at which to stop iterating */ public function __construct(PHPExcel_Worksheet $subject = null, $columnIndex = 'A', $startRow = 1, $endRow = null) { // Set subject $this->subject = $subject; $this->columnIndex = PHPExcel_Cell::columnIndexFromString($columnIndex) - 1; $this->resetEnd($endRow); $this->resetStart($startRow); }
public function coord_E2L($coord) { $Xcoord=PHPExcel_Cell::coordinateFromString($coord); return array( PHPExcel_Cell::columnIndexFromString($Xcoord[0])-1, $Xcoord[1]-1 ); }
/** * 导入代理商信息 * @method import */ public function import() { $file_info = R('Public/UploadExcel'); //上传excel并返回上传路径 if (!file_exists($file_info)) { //上传失败则输出 $error_info['status'] = 0; $error_info['info'] = $file_info; die(json_encode($error_info)); } import('Common.Tools.PHPExcel.PHPExcel'); $PHPExcel = new \PHPExcel(); $PHPReader = new \PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($file_info)) { $PHPReader = new PHPExcel_Reader_Excel5(); } if (!$PHPReader->canRead($file_info)) { $error_info['status'] = 0; $error_info['info'] = '无法读取文件'; die(json_encode($error_info)); } $PHPExcel = $PHPReader->load($file_info); $currentSheet = $PHPExcel->getSheet(0); $rowTotal = $currentSheet->getHighestRow(); //总行数 $columnTotal = $currentSheet->getHighestColumn(); //总列数 $columnTotal = \PHPExcel_Cell::columnIndexFromString($columnTotal); //字母列转换为数字列 $_list = array(); for ($i = 1; $i <= $rowTotal; $i++) { for ($j = 0; $j <= $columnTotal; $j++) { $val = $currentSheet->getCellByColumnAndRow($j, $i)->getValue(); if (!empty($val)) { $_list[$i - 1][] = $val; } } } foreach ($_list as $_k => $_v) { $list[$_k]['name'] = $_v[0]; $list[$_k]['mobile'] = $_v[1]; $list[$_k]['id_card'] = $_v[2]; $list[$_k]['wechat_number'] = $_v[3]; $list[$_k]['class'] = $_v[4]; $list[$_k]['authorize_code'] = $_v[5]; $list[$_k]['ctime'] = now(); $list[$_k]['mtime'] = now(); } $result = D('Agent')->addAll($list); if ($result) { $error_info['status'] = 1; $error_info['info'] = '导入成功'; } else { $error_info['status'] = 0; $error_info['info'] = '导入数据失败'; } die(json_encode($error_info)); }
/** * Returns the index value of the last used column from a sheet * <br>The index value represents a number from 0 to N-1, where * N represent the number of columns used on the sheet * @param object $sheet The PHPExcel Sheet object * @return integer The N-1 value */ public function getMaxColumnSheet($sheet) { try { $result = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); } catch (Exception $ex) { $result = 0; } return $result; }
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); }
/** * Obtiene el numero de indice de la ultima columna efectivamente utilizada * de una hoja leida.<br> El valor devuelto corresponde a un numero de posicion * de columna que comenzara en 0 y terminara en N-1, siendo N la cantidad de * columnas utilizadas en esa hoja. * @param obj $hoja <p>Objeto WorkingSheet instanciado con la hoja leida</p> * @return int <p>El numero con la posicion N-1</p> */ function obtenerMaxColumnaHojaExcel($hoja) { try { $retorno = PHPExcel_Cell::columnIndexFromString($hoja->getHighestColumn()); } catch (Exception $ex) { //echo $ex->getMessage()."<br>"; $retorno = 0; } return $retorno; }
public function getAllRowsCurrentSheet() { $data = array(); $worksheet = $this->PHPExcel->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); $highestColumn = $worksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); for ($row = 1; $row <= $highestRow; ++$row) { for ($col = 0; $col < $highestColumnIndex; ++$col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); $data[$row - 1][$col] = $cell->getValue(); } } return $data; }
function excelToArray($spreadsheetPath, $workSheetNumber = 0, $rowwOffset = 0) { // Include the PHPExcel library set_include_path(get_include_path() . PATH_SEPARATOR . 'sites/all/libraries/PHPExcel/'); include_once 'PHPExcel.php'; // load the spreadsheet $objPHPExcel = PHPExcel_IOFactory::load($spreadsheetPath); $excelHeaders = array(); $excelArray = array(); $currentWkSheetNumb = -1; foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $currentWkSheetNumb++; if ($workSheetNumber === $currentWkSheetNumb) { $highestRow = $worksheet->getHighestRow(); // e.g. 10 $highestColumn = $worksheet->getHighestColumn(); // e.g 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $nrColumns = ord($highestColumn) - 64; // Get the headers - assume the cells on the first row are headers for ($col = 0; $col < $highestColumnIndex; ++$col) { $excelHeaders[$col] = $worksheet->getCellByColumnAndRow($col, 1 + $rowwOffset)->getValue(); } for ($row = 2 + $rowwOffset; $row <= $highestRow; ++$row) { $excelArray['row-' . $row] = array('assoc' => array(), 'cols' => array()); for ($col = 0; $col < $highestColumnIndex; ++$col) { $thisColHeader = $excelHeaders[$col]; $thisCellValue = $worksheet->getCellByColumnAndRow($col, $row)->getValue(); $excelArray['row-' . $row]['assoc'][$thisColHeader] = $thisCellValue; $excelArray['row-' . $row]['cols'][$col] = $thisCellValue; } } } } // Array cleanup - Check the last rows of the array, if all of the cells (coumns) for this row are null, remove the row $rowsInArray_ReverseOrder = array_reverse(array_keys($excelArray)); foreach ($rowsInArray_ReverseOrder as $rowKey) { if (strval(@implode('', $excelArray[$rowKey]['cols'])) === '') { // If there is no information (if all the elements in this array are null)... unset($excelArray[$rowKey]); // Remove this row from the array } else { break; // Do not check any more rows in the array } } return $excelArray; }
public function getHeader($currentSheetNum, $startRow, $startColumn) { $this->isFilepathReadable(); $headerArray = array(); $startColumn = PHPExcel_Cell::columnIndexFromString($startColumn) - 1; $currentSheet = $this->PHPExcel->getSheet($currentSheetNum); $lastColumn = $currentSheet->getHighestColumn(); $lastColumn = PHPExcel_Cell::columnIndexFromString($lastColumn) - 1; for ($column = $startColumn; $column <= $lastColumn; $column++) { $cell = $currentSheet->getCellByColumnAndRow($column, $startRow)->getCalculatedValue(); if (!is_null($cell)) { $headerArray[PHPExcel_Cell::stringFromColumnIndex($column)] = $cell; } } return $headerArray; }
/** * 读取excel $filename 路径文件名 $encode 返回数据的编码 默认为utf8 */ public function read($filename, $encode = 'utf-8') { $objReader = PHPExcel_IOFactory::createReader('Excel5'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($filename); $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $excelData = array(); for ($row = 1; $row <= $highestRow; $row++) { for ($col = 0; $col < $highestColumnIndex; $col++) { $excelData[$row][] = (string) $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); } } return $excelData; }
protected function loadColumns() { $highestColumn = $this->sheet->getHighestColumn(); // here 'E' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; // here 5 $this->columns = array(); //read columns from 1st row for ($col = 0; $col <= $highestColumnIndex; $col++) { $value = $this->getCellValue($col, 0); //echo "\n$col, 0: " . $value . "\n"; if (!$value || $value == '') { continue; } $this->columns[$value] = $col; } }
public function getdata() { $type = strtolower(pathinfo($this->casefile, PATHINFO_EXTENSION)); if (!file_exists($this->casefile)) { return false; } //根据不同类型分别操作 if ($type == 'xlsx' || $type == 'xls') { $objPHPExcel = PHPExcel_IOFactory::load($this->casefile); } else { if ($type == 'csv') { $objReader = PHPExcel_IOFactory::createReader('CSV')->setDelimiter(',')->setInputEncoding('GBK')->setEnclosure('"')->setLineEnding("\r\n")->setSheetIndex(0); $objPHPExcel = $objReader->load($this->casefile); } else { return false; } } //选择标签页 $sheet = $objPHPExcel->getSheet(0); //获取行数与列数,注意列数需要转换 $highestRowNum = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); $highestColumnNum = PHPExcel_Cell::columnIndexFromString($highestColumn); //取得字段,这里测试表格中的第一行为数据的字段,因此先取出用来作后面数组的键名 $filed = array(); for ($i = 0; $i < $highestColumnNum; $i++) { $cellName = PHPExcel_Cell::stringFromColumnIndex($i) . '1'; $cellVal = $sheet->getCell($cellName)->getValue(); //取得列内容 $filed[] = $cellVal; } //开始取出数据并存入数组 $data = array(); for ($i = 2; $i <= $highestRowNum; $i++) { //ignore row 1 $row = array(); for ($j = 0; $j < $highestColumnNum; $j++) { $cellName = PHPExcel_Cell::stringFromColumnIndex($j) . $i; $cellVal = $sheet->getCell($cellName)->getValue(); $row[$filed[$j]] = $cellVal; } $data[] = $row; } return $data; }
public function importInit($patch_to_file) { require_once __DIR__ . '/ExcelService/PHPExcel/IOFactory.php'; $objPHPExcel = PHPExcel_IOFactory::load($patch_to_file); $arrayData = array(); foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $highestRow = $worksheet->getHighestRow(); $highestColumn = $worksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); for ($row = 1; $row <= $highestRow; ++$row) { for ($i = 0; $i <= $highestColumnIndex; ++$i) { $arrayData[$row][$i] = trim($worksheet->getCellByColumnAndRow($i, $row)->getValue()); //$arrayData[$row][$i] = iconv ('utf-8', $code_tyle, $arrayData[$row][$i]); } } } return $arrayData; }
function displayAwards($year, $race) { // Create new PHPExcel object // echo date('H:i:s') . " Create new PHPExcel object\n"; $inputFileType = 'Excel5'; $inputFileName = '../Results.xls'; /** Create a new Reader of the type defined in $inputFileType **/ $objReader = PHPExcel_IOFactory::createReader($inputFileType); /** Advise the Reader that we only want to load cell data **/ $objReader->setReadDataOnly(true); /** Load $inputFileName to a PHPExcel Object **/ $objPHPExcel = $objReader->load($inputFileName); $objWorksheet = $objPHPExcel->getActiveSheet(); foreach ($objWorksheet->getRowIterator() as $rowNum => $row) { if ($objWorksheet->getCellByColumnAndRow(0, $rowNum)->getValue() == $year) { if ($objWorksheet->getCellByColumnAndRow(1, $rowNum)->getValue() == $race) { $cellIterator = $row->getCellIterator(); $i = 0; foreach ($cellIterator as $cell) { if ($i >= 2) { $colIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; echo '<li class="award">' . $cell->getValue(); switch ($cell->getValue()) { case 1: echo 'st: '; break; case 2: echo 'nd: '; break; case 3: echo 'rd: '; break; default: echo 'th: '; break; } echo $objWorksheet->getCellByColumnAndRow($colIndex, 1)->getValue() . '</li>'; } $i++; } } } } }
function parseExcel() { global $log; $log->debug("Entering into function parseExcel()"); $objReader = PHPExcel_IOFactory::createReader('Excel5'); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($this->accountfile); $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); // e.g. 10 $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $accountrel = $this->excelAccountRel(); for ($row = 2; $row <= $highestRow; ++$row) { $salesorderinf = array(); foreach ($accountrel as $accountprop => $pos) { if ($pos < $highestColumnIndex) { $accountval = $objWorksheet->getCellByColumnAndRow($pos, $row)->getValue(); $accountval = $this->transferExcelProps(1, $accountprop, $accountval); //$this->accountInf[$accountprop]=$accountval; $salesorderinf[$accountprop] = $accountval; } } // parse Contact part if ($this->salesorderIsExist($salesorderinf) == 0) { //invalid row $this->skip_record += 1; $this->skip_rows[] = $salesorderinf; $this->total_reocrd += 1; } else { $excelrows = $this->accountInf[$salesorderinf['subject']]; if (!isset($excelrows)) { $excelrows = array(); } $excelrows[] = $salesorderinf; $this->accountInf[$salesorderinf['subject']] = $excelrows; $this->total_reocrd += 1; } } //save entries $this->saveEntries(); $log->debug("Exit function parseExcel()"); }
/** * 读取 EXCEL */ public static function read($file) { self::checkFile($file); $ext = self::getFileExt($file); try { if ($ext == self::EXCEL_TYPE_CSV) { // CSV 格式要特别处理 $PHPReader = \PHPExcel_IOFactory::createReader(self::$fileConf[$ext])->setDelimiter(',')->setEnclosure('"')->setSheetIndex(0); } else { $PHPReader = \PHPExcel_IOFactory::createReader(self::$fileConf[$ext]); } $PHPReader->setReadDataOnly(true); $PHPexcel = $PHPReader->load($file); $workSheet = $PHPexcel->getActiveSheet(); // 获取行数 $highestRow = $workSheet->getHighestRow(); // 获取列数 $highestCol = $workSheet->getHighestColumn(); $highestColIndex = \PHPExcel_Cell::columnIndexFromString($highestCol); $excelData = array(); for ($row = 1; $row <= $highestRow; $row++) { for ($col = 0; $col < $highestColIndex; $col++) { $excelData[$row][] = strval($workSheet->getCellByColumnAndRow($col, $row)->getValue()); } } return $excelData; } catch (\Exception $e) { return self::error($e->getMessage()); } }
public function read($filename, $encode, $file_type) { if (strtolower($file_type) == 'xls') { vendor("Excel.PHPExcel.Reader.Excel5", LIB_PATH . '../Extend/Vendor'); $objReader = PHPExcel_IOFactory::createReader('Excel5'); } else { if (strtolower($file_type) == 'xlsx') { vendor("Excel.PHPExcel.Reader.Excel2007", LIB_PATH . '../Extend/Vendor'); $objReader = PHPExcel_IOFactory::createReader('Excel2007'); } } $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($filename); $objWorksheet = $objPHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $excelData = array(); for ($row = 1; $row <= $highestRow; $row++) { for ($col = 0; $col < $highestColumnIndex; $col++) { $excelData[$row][] = (string) $objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); } } return $excelData; }