PHP PHPExcel_Cell::columnIndexFromString方法代码示例

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