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