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 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 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); }
/** * @inheritDoc */ public function writeHeaderRow($headerRow) { $this->writeRow($headerRow); $lastColumnLetter = \PHPExcel_Cell::stringFromColumnIndex(count($headerRow) - 1); $headerRange = "A{$this->currentRowIndex}:{$lastColumnLetter}{$this->currentRowIndex}"; $this->phpExcel->getActiveSheet()->getStyle($headerRange)->getFont()->setBold(true); }
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 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"; } }
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 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 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; } }
/** * exportCollectionSheet * * default sheet export for collection * * @param string $object * @param string $fields * @return void * @author Brent Shaffer */ public function exportCollectionSheet($collection, $fields, $title = null) { if ($this->_sheets > 0) { $workSheet = $this->_xls->createSheet(); } else { $workSheet = $this->_xls->getActiveSheet(); } $this->_sheets++; $workSheet->setTitle($title ? $title : $this->getExportTitle()); // Initialize coordinate counters $row = 1; $col = 0; foreach ($fields as $field => $label) { $workSheet->setCellValueByColumnAndRow($col, $row, $label); $workSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($col))->setAutoSize(true); $col++; } $row++; foreach ($collection as $record) { $col = 0; foreach ($fields as $field => $label) { $workSheet->setCellValueByColumnAndRow($col, $row, $this->exportField($record, $field)); $col++; } $row++; } }
/** * (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; }
/** * Clone the cell collection * * @return void */ public function copyCellCollection(PHPExcel_Worksheet $parent) { $this->_parent = $parent; if (!is_null($this->_currentObject) && is_object($this->_currentObject)) { $this->_currentObject->attach($parent); } }
/** * Set parent * * @param PHPExcel_Cell $value */ public function setParent(PHPExcel_Cell $value) { // Set parent $this->_parent = $value; // Set parent value $this->_parent->setValue($this); }
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 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); } }
/** * 行を完全コピーする * * http://blog.kotemaru.org/old/2012/04/06.html より * @param PHPExcel_Worksheet $sheet * @param int $srcRow * @param int $dstRow * @param int $height * @param int $width * @throws PHPExcel_Exception */ function copyRows(PHPExcel_Worksheet $sheet, $srcRow, $dstRow, $height, $width) { for ($row = 0; $row < $height; $row++) { // セルの書式と値の複製 for ($col = 0; $col < $width; $col++) { $cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row); $style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row); $dstCell = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row); $sheet->setCellValue($dstCell, $cell->getValue()); $sheet->duplicateStyle($style, $dstCell); } // 行の高さ複製。 $h = $sheet->getRowDimension($srcRow + $row)->getRowHeight(); $sheet->getRowDimension($dstRow + $row)->setRowHeight($h); } // セル結合の複製 // - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。 // - $merge="AB16:AC19" foreach ($sheet->getMergeCells() as $mergeCell) { $mc = explode(":", $mergeCell); $col_s = preg_replace("/[0-9]*/", "", $mc[0]); $col_e = preg_replace("/[0-9]*/", "", $mc[1]); $row_s = (int) preg_replace("/[A-Z]*/", "", $mc[0]) - $srcRow; $row_e = (int) preg_replace("/[A-Z]*/", "", $mc[1]) - $srcRow; // 複製先の行範囲なら。 if (0 <= $row_s && $row_s < $height) { $merge = $col_s . (string) ($dstRow + $row_s) . ":" . $col_e . (string) ($dstRow + $row_e); $sheet->mergeCells($merge); } } }
/** * 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 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; }