PHP PHPExcel_Cell类代码示例

 public static function exportXlsx($data, $keys)
 {
     // Create new PHPExcel object
     $objPHPExcel = new \PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("Roadiz CMS")->setLastModifiedBy("Roadiz CMS")->setCategory("");
     $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     $cacheSettings = ['memoryCacheSize' => '8MB'];
     \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
     $objPHPExcel->setActiveSheetIndex(0);
     foreach ($keys as $key => $value) {
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value);
     }
     foreach ($data as $key => $answer) {
         foreach ($answer as $k => $value) {
             $columnAlpha = \PHPExcel_Cell::stringFromColumnIndex($k);
             if ($value instanceof \DateTime) {
                 $value = \PHPExcel_Shared_Date::PHPToExcel($value);
                 $objPHPExcel->getActiveSheet()->getStyle($columnAlpha . (2 + $key))->getNumberFormat()->setFormatCode('dd.mm.yyyy hh:MM:ss');
             }
             $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, 2 + $key, $value);
         }
     }
     // Set active sheet index to the first sheet, so Excel opens this as the first sheet
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     ob_start();
     $objWriter->save('php://output');
     $return = ob_get_clean();
     return $return;
 }

 public function output()
 {
     // Create new PHPExcel object
     $objPHPExcel = new \PHPExcel();
     $objSheet = $objPHPExcel->setActiveSheetIndex(0);
     $col = 0;
     $row = 1;
     if (isset($this->header)) {
         foreach ($this->header as $v) {
             $cell = \PHPExcel_Cell::stringFromColumnIndex($col) . $row;
             $objSheet->setCellValue($cell, $v);
             $col++;
         }
         $row++;
         $col = 0;
     }
     foreach ($this->content as $rowValue) {
         foreach ($rowValue as $_v) {
             $cell = \PHPExcel_Cell::stringFromColumnIndex($col) . $row;
             $objSheet->setCellValue($cell, $_v);
             $col++;
         }
         $row++;
         $col = 0;
     }
     // Rename worksheet
     $objPHPExcel->getActiveSheet()->setTitle($this->title);
     // Set active sheet index to the first sheet, so Excel opens this as the first sheet
     $objPHPExcel->setActiveSheetIndex(0);
     $this->browserExport($this->type, $this->filename);
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $this->type);
     $objWriter->save('php://output');
 }

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

 public static function write(PHPExcel_Shared_XMLWriter $objWriter, PHPExcel_Cell $cell)
 {
     $comments = $cell->getWorksheet()->getComments();
     if (! isset($comments[$cell->getCoordinate()])) {
         return;
     }
     $comment = $comments[$cell->getCoordinate()];
     
     $objWriter->startElement('office:annotation');
     // $objWriter->writeAttribute('draw:style-name', 'gr1');
     // $objWriter->writeAttribute('draw:text-style-name', 'P1');
     $objWriter->writeAttribute('svg:width', $comment->getWidth());
     $objWriter->writeAttribute('svg:height', $comment->getHeight());
     $objWriter->writeAttribute('svg:x', $comment->getMarginLeft());
     $objWriter->writeAttribute('svg:y', $comment->getMarginTop());
     // $objWriter->writeAttribute('draw:caption-point-x', $comment->getMarginLeft());
     // $objWriter->writeAttribute('draw:caption-point-y', $comment->getMarginTop());
     $objWriter->writeElement('dc:creator', $comment->getAuthor());
     // TODO: Not realized in PHPExcel_Comment yet.
     // $objWriter->writeElement('dc:date', $comment->getDate());
     $objWriter->writeElement('text:p', $comment->getText()
         ->getPlainText());
     // $objWriter->writeAttribute('draw:text-style-name', 'P1');
     $objWriter->endElement();
 }

 public static function convertCellData(\PHPExcel_Cell $cell)
 {
     $ret = array();
     $datatype = $cell->getDataType();
     if ($datatype === \excel2sql\Type::EXCEL_NUMERIC) {
         $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
         if (array_key_exists($format, \excel2sql\Type::$numeric_convert_map)) {
             $format = \excel2sql\Type::$numeric_convert_map[$format];
         }
         $ret['type'] = \excel2sql\Type::$numeric_to_sql_map[$format];
         $ret['value'] = \PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), $format);
         if ($format === \excel2sql\Type::EXCEL_DATE || $format === \excel2sql\Type::EXCEL_DATETIME || $format === \excel2sql\Type::EXCEL_TIME) {
             $ret['value'] = "'{$ret['value']}'";
         }
     } else {
         $ret['type'] = \excel2sql\Type::$excel_to_sql_map[$datatype];
         if ($ret['value'] = $cell->getFormattedValue()) {
             if ($datatype === \excel2sql\Type::EXCEL_STRING || $datatype === \excel2sql\Type::EXCEL_STRING2) {
                 $ret['value'] = "\"" . preg_replace("[\"]", "''", $ret['value']) . "\"";
             }
         } else {
             $ret['value'] = "null";
         }
     }
     return $ret;
 }

 /**
  * Bind value to a cell
  *
  * @param PHPExcel_Cell $cell	Cell to bind value to
  * @param mixed $value			Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // Set value explicit
     $cell->setValueExplicit($value, PHPExcel_Cell_DataType::dataTypeForValue($value));
     // Done!
     return true;
 }

 /**
  * Bind value to a cell
  *
  * @param PHPExcel_Cell $cell	Cell to bind value to
  * @param mixed $value			Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // sanitize UTF-8 strings
     if (is_string($value)) {
         $value = PHPExcel_Shared_String::SanitizeUTF8($value);
     }
     // Find out data type
     $dataType = parent::dataTypeForValue($value);
     // Style logic - strings
     if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
         // Check for percentage
         if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
             // Convert value to number
             $cell->setValueExplicit((double) str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
             return true;
         }
         // Check for time e.g. '9:45', '09:45'
         if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) {
             list($h, $m) = explode(':', $value);
             $days = $h / 24 + $m / 1440;
             // Convert value to number
             $cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
             return true;
         }
         // Check for date
         if (strtotime($value) !== false) {
             // make sure we have UTC for the sake of strtotime
             $saveTimeZone = date_default_timezone_get();
             date_default_timezone_set('UTC');
             // Convert value to Excel date
             $cell->setValueExplicit(PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
             // restore original value for timezone
             date_default_timezone_set($saveTimeZone);
             return true;
         }
     }
     // Style logic - Numbers
     if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
         // Leading zeroes?
         if (preg_match('/^\\-?[0]+[0-9]*\\.?[0-9]*$/', $value)) {
             // Convert value to string
             $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
             // Set style
             $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
             return true;
         }
     }
     // Not bound yet? Use parent...
     return parent::bindValue($cell, $value);
 }

 /**
  * Format cell data type
  * @param  \PHPExcel_Cell $cell 
  * @return string               
  */
 protected function reformatCellDataType(\PHPExcel_Cell $cell)
 {
     $value = $cell->getValue();
     //datetime
     if (\PHPExcel_Shared_Date::isDateTime($cell)) {
         $format = $this->cellFormat['dateTime'];
         return date($format, \PHPExcel_Shared_Date::ExcelToPHP($value));
     }
     return $value;
 }

 /**
  * Bind value to a cell
  *
  * @param  PHPExcel_Cell  $cell   Cell to bind value to
  * @param  mixed          $value  Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // sanitize UTF-8 strings
     if (is_string($value)) {
         $value = PHPExcel_Shared_String::SanitizeUTF8($value);
     }
     // Set value explicit
     $cell->setValueExplicit($value, self::dataTypeForValue($value));
     // Done!
     return TRUE;
 }

 /**
  * Bind value to a cell, preserving possible leading zeros
  * See http://stackoverflow.com/questions/12457610/reading-numbers-as-text-format-with-phpexcel
  *
  * @param  PHPExcel_Cell  $cell   Cell to bind value to
  * @param  mixed          $value  Value to bind in cell
  * @return boolean
  */
 public function bindValue(PHPExcel_Cell $cell, $value = null)
 {
     // sanitize UTF-8 strings
     if (is_string($value)) {
         $value = PHPExcel_Shared_String::SanitizeUTF8($value);
     }
     // Preserve numeric string, including leading zeros, if it is a text format
     $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
     if ($format == PHPExcel_Style_NumberFormat::FORMAT_TEXT) {
         $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
         return true;
     }
     // Not bound yet? Use default value parent...
     return parent::bindValue($cell, $value);
 }

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

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

 /**
  * Format a recordset
  *
  * @param Garp_Model $model
  * @param array $rowset
  * @return string
  */
 public function format(Garp_Model $model, array $rowset)
 {
     $phpexcel = new PHPExcel();
     PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
     // set metadata
     $props = $phpexcel->getProperties();
     if (Garp_Auth::getInstance()->isLoggedIn()) {
         $userData = Garp_Auth::getInstance()->getUserData();
         $bootstrap = Zend_Controller_Front::getInstance()->getParam('bootstrap');
         if ($bootstrap) {
             $view = $bootstrap->getResource('view');
             $userName = $view->fullName($userData);
             $props->setCreator($userName)->setLastModifiedBy($userName);
         }
     }
     $props->setTitle('Garp content export – ' . $model->getName());
     if (count($rowset)) {
         $this->_addContent($phpexcel, $model, $rowset);
     }
     /**
      * Hm, PHPExcel seems to only be able to write to a file (instead of returning
      * an XLS binary string). Therefore, we save a temporary file, read its contents
      * and return those, after which we unlink the temp file.
      */
     $tmpFileName = APPLICATION_PATH . '/data/logs/tmp.xls';
     $writer = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
     $writer->save($tmpFileName);
     $contents = file_get_contents($tmpFileName);
     unlink($tmpFileName);
     return $contents;
 }

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