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