xlsx to text with php
0
I have following function to convert xlsx to text..but it does not give space for new cell.. means for different cell it directly appends to previous word..
function xlsx_to_text($input_file){
$xml_filename = "xl/sharedStrings.xml"; //content file name
$zip_handle = new ZipArchive;
$output_text = "";
if(true === $zip_handle->open($input_file)){
if(($xml_index = $zip_handle->locateName($xml_filename)) !== false){
$xml_datas = $zip_handle->getFromIndex($xml_index);
$xml_handle = new DOMDocument();
$xml_handle->loadXML($xml_datas, LIBXML_NOENT | LIBXML_XINCLUDE | LIBXML_NOERROR | LIBXML_NOWARNING);
$output_text = strip_tags($xml_handle->saveXML());
}else{
$output_text .="";
}
$zip_handle->close();
}else{
$output_text .="";
}
return $output_text;
}
I have output like.. Ihaveanapple.. but it should be like I have an apple
please help me to get out of this..
php xlsx
asked Feb 27 '16 at 4:23
Himanshu UpadhyayHimanshu Upadhyay
418321
add a comment |
0
I have following function to convert xlsx to text..but it does not give space for new cell.. means for different cell it directly appends to previous word..
function xlsx_to_text($input_file){
$xml_filename = "xl/sharedStrings.xml"; //content file name
$zip_handle = new ZipArchive;
$output_text = "";
if(true === $zip_handle->open($input_file)){
if(($xml_index = $zip_handle->locateName($xml_filename)) !== false){
$xml_datas = $zip_handle->getFromIndex($xml_index);
$xml_handle = new DOMDocument();
$xml_handle->loadXML($xml_datas, LIBXML_NOENT | LIBXML_XINCLUDE | LIBXML_NOERROR | LIBXML_NOWARNING);
$output_text = strip_tags($xml_handle->saveXML());
}else{
$output_text .="";
}
$zip_handle->close();
}else{
$output_text .="";
}
return $output_text;
}
I have output like.. Ihaveanapple.. but it should be like I have an apple
please help me to get out of this..
php xlsx
asked Feb 27 '16 at 4:23
Himanshu UpadhyayHimanshu Upadhyay
418321
replace one of the tags with a space
– user557846
Feb 27 '16 at 4:25
but actually with same file, i got desired result from reference site..
– Himanshu Upadhyay
Feb 27 '16 at 4:57
not sure what that means but i imagine they have no spaces between tags so you have to had them
– user557846
Feb 27 '16 at 5:14
add a comment |
0
0
0
I have following function to convert xlsx to text..but it does not give space for new cell.. means for different cell it directly appends to previous word..
function xlsx_to_text($input_file){
$xml_filename = "xl/sharedStrings.xml"; //content file name
$zip_handle = new ZipArchive;
$output_text = "";
if(true === $zip_handle->open($input_file)){
if(($xml_index = $zip_handle->locateName($xml_filename)) !== false){
$xml_datas = $zip_handle->getFromIndex($xml_index);
$xml_handle = new DOMDocument();
$xml_handle->loadXML($xml_datas, LIBXML_NOENT | LIBXML_XINCLUDE | LIBXML_NOERROR | LIBXML_NOWARNING);
$output_text = strip_tags($xml_handle->saveXML());
}else{
$output_text .="";
}
$zip_handle->close();
}else{
$output_text .="";
}
return $output_text;
}
I have output like.. Ihaveanapple.. but it should be like I have an apple
please help me to get out of this..
php xlsx
asked Feb 27 '16 at 4:23
Himanshu UpadhyayHimanshu Upadhyay
418321
I have following function to convert xlsx to text..but it does not give space for new cell.. means for different cell it directly appends to previous word..
function xlsx_to_text($input_file){
$xml_filename = "xl/sharedStrings.xml"; //content file name
$zip_handle = new ZipArchive;
$output_text = "";
if(true === $zip_handle->open($input_file)){
if(($xml_index = $zip_handle->locateName($xml_filename)) !== false){
$xml_datas = $zip_handle->getFromIndex($xml_index);
$xml_handle = new DOMDocument();
$xml_handle->loadXML($xml_datas, LIBXML_NOENT | LIBXML_XINCLUDE | LIBXML_NOERROR | LIBXML_NOWARNING);
$output_text = strip_tags($xml_handle->saveXML());
}else{
$output_text .="";
}
$zip_handle->close();
}else{
$output_text .="";
}
return $output_text;
}
I have output like.. Ihaveanapple.. but it should be like I have an apple
please help me to get out of this..
php xlsx
php xlsx
asked Feb 27 '16 at 4:23
Himanshu UpadhyayHimanshu Upadhyay
418321
asked Feb 27 '16 at 4:23
Himanshu UpadhyayHimanshu Upadhyay
418321
asked Feb 27 '16 at 4:23
Himanshu UpadhyayHimanshu Upadhyay
418321
asked Feb 27 '16 at 4:23
Himanshu UpadhyayHimanshu Upadhyay
418321
asked Feb 27 '16 at 4:23
Himanshu UpadhyayHimanshu Upadhyay
418321
418321
replace one of the tags with a space
– user557846
Feb 27 '16 at 4:25
but actually with same file, i got desired result from reference site..
– Himanshu Upadhyay
Feb 27 '16 at 4:57
not sure what that means but i imagine they have no spaces between tags so you have to had them
– user557846
Feb 27 '16 at 5:14
add a comment |
replace one of the tags with a space
– user557846
Feb 27 '16 at 4:25
but actually with same file, i got desired result from reference site..
– Himanshu Upadhyay
Feb 27 '16 at 4:57
not sure what that means but i imagine they have no spaces between tags so you have to had them
– user557846
Feb 27 '16 at 5:14
replace one of the tags with a space
– user557846
Feb 27 '16 at 4:25
replace one of the tags with a space
– user557846
Feb 27 '16 at 4:25
but actually with same file, i got desired result from reference site..
– Himanshu Upadhyay
Feb 27 '16 at 4:57
but actually with same file, i got desired result from reference site..
– Himanshu Upadhyay
Feb 27 '16 at 4:57
not sure what that means but i imagine they have no spaces between tags so you have to had them
– user557846
Feb 27 '16 at 5:14
not sure what that means but i imagine they have no spaces between tags so you have to had them
– user557846
Feb 27 '16 at 5:14
add a comment |
3 Answers
3
active
oldest
votes
3
I got an answer.
function xlsx_to_text($input_file){
require('SpreadsheetReader.php');
$Reader = new SpreadsheetReader($input_file);
$totalCount = 0;
$test=0;
foreach ($Reader as $Row)
{
$count_rows = $Row;
foreach($count_rows as $countRow){
if($countRow != ''){
$taa = explode(' ',$countRow);
$tCount = count($taa);
$result=$countRow." ";
}
}
}
return implode("",$result);
}
write below code in SpreadsheetReader.php
<?php
class SpreadsheetReader implements SeekableIterator, Countable
{
const TYPE_XLSX = 'XLSX';
const TYPE_XLS = 'XLS';
const TYPE_CSV = 'CSV';
const TYPE_ODS = 'ODS';
private $Options = array(
'Delimiter' => '',
'Enclosure' => '"'
);
/**
* @var int Current row in the file
*/
private $Index = 0;
/**
* @var SpreadsheetReader_* Handle for the reader object
*/
private $Handle = array();
/**
* @var TYPE_* Type of the contained spreadsheet
*/
private $Type = false;
/**
* @param string Path to file
* @param string Original filename (in case of an uploaded file), used to determine file type, optional
* @param string MIME type from an upload, used to determine file type, optional
*/
public function __construct($Filepath, $OriginalFilename = false, $MimeType = false)
{
if (!is_readable($Filepath))
{
throw new Exception('SpreadsheetReader: File ('.$Filepath.') not readable');
}
// To avoid timezone warnings and exceptions for formatting dates retrieved from files
$DefaultTZ = @date_default_timezone_get();
if ($DefaultTZ)
{
date_default_timezone_set($DefaultTZ);
}
// Checking the other parameters for correctness
// This should be a check for string but we're lenient
if (!empty($OriginalFilename) && !is_scalar($OriginalFilename))
{
throw new Exception('SpreadsheetReader: Original file (2nd parameter) path is not a string or a scalar value.');
}
if (!empty($MimeType) && !is_scalar($MimeType))
{
throw new Exception('SpreadsheetReader: Mime type (3nd parameter) path is not a string or a scalar value.');
}
// 1. Determine type
if (!$OriginalFilename)
{
$OriginalFilename = $Filepath;
}
$Extension = strtolower(pathinfo($OriginalFilename, PATHINFO_EXTENSION));
switch ($MimeType)
{
case 'text/csv':
case 'text/comma-separated-values':
case 'text/plain':
$this -> Type = self::TYPE_CSV;
break;
case 'application/vnd.ms-excel':
case 'application/msexcel':
case 'application/x-msexcel':
case 'application/x-ms-excel':
case 'application/vnd.ms-excel':
case 'application/x-excel':
case 'application/x-dos_ms_excel':
case 'application/xls':
case 'application/xlt':
case 'application/x-xls':
// Excel does weird stuff
if (in_array($Extension, array('csv', 'tsv', 'txt')))
{
$this -> Type = self::TYPE_CSV;
}
else
{
$this -> Type = self::TYPE_XLS;
}
break;
case 'application/vnd.oasis.opendocument.spreadsheet':
case 'application/vnd.oasis.opendocument.spreadsheet-template':
$this -> Type = self::TYPE_ODS;
break;
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.template':
case 'application/xlsx':
case 'application/xltx':
$this -> Type = self::TYPE_XLSX;
break;
case 'application/xml':
// Excel 2004 xml format uses this
break;
}
if (!$this -> Type)
{
switch ($Extension)
{
case 'xlsx':
case 'xltx': // XLSX template
case 'xlsm': // Macro-enabled XLSX
case 'xltm': // Macro-enabled XLSX template
$this -> Type = self::TYPE_XLSX;
break;
case 'xls':
case 'xlt':
$this -> Type = self::TYPE_XLS;
break;
case 'ods':
case 'odt':
$this -> Type = self::TYPE_ODS;
break;
default:
$this -> Type = self::TYPE_CSV;
break;
}
}
// Pre-checking XLS files, in case they are renamed CSV or XLSX files
if ($this -> Type == self::TYPE_XLS)
{
self::Load(self::TYPE_XLS);
$this -> Handle = new SpreadsheetReader_XLS($Filepath);
if ($this -> Handle -> Error)
{
$this -> Handle -> __destruct();
if (is_resource($ZipHandle = zip_open($Filepath)))
{
$this -> Type = self::TYPE_XLSX;
zip_close($ZipHandle);
}
else
{
$this -> Type = self::TYPE_CSV;
}
}
}
// 2. Create handle
switch ($this -> Type)
{
case self::TYPE_XLSX:
self::Load(self::TYPE_XLSX);
$this -> Handle = new SpreadsheetReader_XLSX($Filepath);
break;
case self::TYPE_CSV:
self::Load(self::TYPE_CSV);
$this -> Handle = new SpreadsheetReader_CSV($Filepath, $this -> Options);
break;
case self::TYPE_XLS:
// Everything already happens above
break;
case self::TYPE_ODS:
self::Load(self::TYPE_ODS);
$this -> Handle = new SpreadsheetReader_ODS($Filepath, $this -> Options);
break;
}
}
/**
* Gets information about separate sheets in the given file
*
* @return array Associative array where key is sheet index and value is sheet name
*/
public function Sheets()
{
return $this -> Handle -> Sheets();
}
/**
* Changes the current sheet to another from the file.
* Note that changing the sheet will rewind the file to the beginning, even if
* the current sheet index is provided.
*
* @param int Sheet index
*
* @return bool True if sheet could be changed to the specified one,
* false if not (for example, if incorrect index was provided.
*/
public function ChangeSheet($Index)
{
return $this -> Handle -> ChangeSheet($Index);
}
/**
* Autoloads the required class for the particular spreadsheet type
*
* @param TYPE_* Spreadsheet type, one of TYPE_* constants of this class
*/
private static function Load($Type)
{
if (!in_array($Type, array(self::TYPE_XLSX, self::TYPE_XLS, self::TYPE_CSV, self::TYPE_ODS)))
{
throw new Exception('SpreadsheetReader: Invalid type ('.$Type.')');
}
// 2nd parameter is to prevent autoloading for the class.
// If autoload works, the require line is unnecessary, if it doesn't, it ends badly.
if (!class_exists('SpreadsheetReader_'.$Type, false))
{
require(dirname(__FILE__).DIRECTORY_SEPARATOR.'SpreadsheetReader_'.$Type.'.php');
}
}
// !Iterator interface methods
/**
* Rewind the Iterator to the first element.
* Similar to the reset() function for arrays in PHP
*/
public function rewind()
{
$this -> Index = 0;
if ($this -> Handle)
{
$this -> Handle -> rewind();
}
}
/**
* Return the current element.
* Similar to the current() function for arrays in PHP
*
* @return mixed current element from the collection
*/
public function current()
{
if ($this -> Handle)
{
return $this -> Handle -> current();
}
return null;
}
/**
* Move forward to next element.
* Similar to the next() function for arrays in PHP
*/
public function next()
{
if ($this -> Handle)
{
$this -> Index++;
return $this -> Handle -> next();
}
return null;
}
/**
* Return the identifying key of the current element.
* Similar to the key() function for arrays in PHP
*
* @return mixed either an integer or a string
*/
public function key()
{
if ($this -> Handle)
{
return $this -> Handle -> key();
}
return null;
}
/**
* Check if there is a current element after calls to rewind() or next().
* Used to check if we've iterated to the end of the collection
*
* @return boolean FALSE if there's nothing more to iterate over
*/
public function valid()
{
if ($this -> Handle)
{
return $this -> Handle -> valid();
}
return false;
}
// !Countable interface method
public function count()
{
if ($this -> Handle)
{
return $this -> Handle -> count();
}
return 0;
}
/**
* Method for SeekableIterator interface. Takes a posiiton and traverses the file to that position
* The value can be retrieved with a `current()` call afterwards.
*
* @param int Position in file
*/
public function seek($Position)
{
if (!$this -> Handle)
{
throw new OutOfBoundsException('SpreadsheetReader: No file opened');
}
$CurrentIndex = $this -> Handle -> key();
if ($CurrentIndex != $Position)
{
if ($Position < $CurrentIndex || is_null($CurrentIndex) || $Position == 0)
{
$this -> rewind();
}
while ($this -> Handle -> valid() && ($Position > $this -> Handle -> key()))
{
$this -> Handle -> next();
}
if (!$this -> Handle -> valid())
{
throw new OutOfBoundsException('SpreadsheetError: Position '.$Position.' not found');
}
}
return null;
}
}
?>
answered Apr 7 '16 at 11:14
Himanshu UpadhyayHimanshu Upadhyay
418321
add a comment |
1
Why don't you use some existing libraries like Spout or PHPExcel to read data from your XLSX files? There are a lot of edge cases that these libraries have already solved. Some examples: security with the XML file, inline data, metadata stored in sharedStrings.xml, large XML file... Doing it yourself means you will have to find all these edge cases again and handle them in your code.
Parsing the sharedStrings.xml file is unfortunately not as easy as calling strip_tags($xml_handle->saveXML());.
Now your code should kind of work if the string "I have an apple" is in one cell. It seems like each word is in a different cell so your XML looks like this:
<si><t>I</t></si>
<si><t>have</t></si>
<si><t>an</t></si>
<si><t>apple</t></si>
So when you remove all the tags, you end up with "Ihaveanapple", as no spaces get inserted.
answered Feb 29 '16 at 22:55
AdrienAdrien
1,3301715
PHPExcel is a superb one for this purpose.
– Himel Nag Rana
Apr 7 '16 at 11:22
add a comment |
0
Why not use PhpOffice/PhpSpreadsheet.
<?php
// Create a new Reader of the type defined in $inputFileType
// If file type is Xlsx / Csv this package will read.
$reader = PhpOfficePhpSpreadsheetIOFactory::createReader("Xlsx");
// Load $inputFileName to a PhpSpreadsheet Object
$spreadsheet = $reader->load($xlsxFileName);
$worksheet = $spreadsheet->getActiveSheet();
// TODO: Add a const for Xlsx and Csv
$dataEndRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$headersArrayList = $worksheet->rangeToArray("A1:{$highestColumn}1", null, true, true, true);
if (!isset($headersArrayList[1])) {
return;
}
$headersArray = $headersArrayList[1];
$data = $worksheet->rangeToArray("A2:{$highestColumn}{$dataEndRow}", null, true, true, true);
?>
The data will contain two dimensional array. Rows keys will be integer and Columns keys will be string.
Accessing a cell, $data[2][A] will give you value in 2nd row, A column of your spreadsheet.
Note: This package requires xmlreader, xml, dom, xmlwriter php extensions
answered Dec 28 '18 at 9:28
theBuzzyCodertheBuzzyCoder
9421216
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f35665625%2fxlsx-to-text-with-php%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
I got an answer.
function xlsx_to_text($input_file){
require('SpreadsheetReader.php');
$Reader = new SpreadsheetReader($input_file);
$totalCount = 0;
$test=0;
foreach ($Reader as $Row)
{
$count_rows = $Row;
foreach($count_rows as $countRow){
if($countRow != ''){
$taa = explode(' ',$countRow);
$tCount = count($taa);
$result=$countRow." ";
}
}
}
return implode("",$result);
}
write below code in SpreadsheetReader.php
<?php
class SpreadsheetReader implements SeekableIterator, Countable
{
const TYPE_XLSX = 'XLSX';
const TYPE_XLS = 'XLS';
const TYPE_CSV = 'CSV';
const TYPE_ODS = 'ODS';
private $Options = array(
'Delimiter' => '',
'Enclosure' => '"'
);
/**
* @var int Current row in the file
*/
private $Index = 0;
/**
* @var SpreadsheetReader_* Handle for the reader object
*/
private $Handle = array();
/**
* @var TYPE_* Type of the contained spreadsheet
*/
private $Type = false;
/**
* @param string Path to file
* @param string Original filename (in case of an uploaded file), used to determine file type, optional
* @param string MIME type from an upload, used to determine file type, optional
*/
public function __construct($Filepath, $OriginalFilename = false, $MimeType = false)
{
if (!is_readable($Filepath))
{
throw new Exception('SpreadsheetReader: File ('.$Filepath.') not readable');
}
// To avoid timezone warnings and exceptions for formatting dates retrieved from files
$DefaultTZ = @date_default_timezone_get();
if ($DefaultTZ)
{
date_default_timezone_set($DefaultTZ);
}
// Checking the other parameters for correctness
// This should be a check for string but we're lenient
if (!empty($OriginalFilename) && !is_scalar($OriginalFilename))
{
throw new Exception('SpreadsheetReader: Original file (2nd parameter) path is not a string or a scalar value.');
}
if (!empty($MimeType) && !is_scalar($MimeType))
{
throw new Exception('SpreadsheetReader: Mime type (3nd parameter) path is not a string or a scalar value.');
}
// 1. Determine type
if (!$OriginalFilename)
{
$OriginalFilename = $Filepath;
}
$Extension = strtolower(pathinfo($OriginalFilename, PATHINFO_EXTENSION));
switch ($MimeType)
{
case 'text/csv':
case 'text/comma-separated-values':
case 'text/plain':
$this -> Type = self::TYPE_CSV;
break;
case 'application/vnd.ms-excel':
case 'application/msexcel':
case 'application/x-msexcel':
case 'application/x-ms-excel':
case 'application/vnd.ms-excel':
case 'application/x-excel':
case 'application/x-dos_ms_excel':
case 'application/xls':
case 'application/xlt':
case 'application/x-xls':
// Excel does weird stuff
if (in_array($Extension, array('csv', 'tsv', 'txt')))
{
$this -> Type = self::TYPE_CSV;
}
else
{
$this -> Type = self::TYPE_XLS;
}
break;
case 'application/vnd.oasis.opendocument.spreadsheet':
case 'application/vnd.oasis.opendocument.spreadsheet-template':
$this -> Type = self::TYPE_ODS;
break;
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.template':
case 'application/xlsx':
case 'application/xltx':
$this -> Type = self::TYPE_XLSX;
break;
case 'application/xml':
// Excel 2004 xml format uses this
break;
}
if (!$this -> Type)
{
switch ($Extension)
{
case 'xlsx':
case 'xltx': // XLSX template
case 'xlsm': // Macro-enabled XLSX
case 'xltm': // Macro-enabled XLSX template
$this -> Type = self::TYPE_XLSX;
break;
case 'xls':
case 'xlt':
$this -> Type = self::TYPE_XLS;
break;
case 'ods':
case 'odt':
$this -> Type = self::TYPE_ODS;
break;
default:
$this -> Type = self::TYPE_CSV;
break;
}
}
// Pre-checking XLS files, in case they are renamed CSV or XLSX files
if ($this -> Type == self::TYPE_XLS)
{
self::Load(self::TYPE_XLS);
$this -> Handle = new SpreadsheetReader_XLS($Filepath);
if ($this -> Handle -> Error)
{
$this -> Handle -> __destruct();
if (is_resource($ZipHandle = zip_open($Filepath)))
{
$this -> Type = self::TYPE_XLSX;
zip_close($ZipHandle);
}
else
{
$this -> Type = self::TYPE_CSV;
}
}
}
// 2. Create handle
switch ($this -> Type)
{
case self::TYPE_XLSX:
self::Load(self::TYPE_XLSX);
$this -> Handle = new SpreadsheetReader_XLSX($Filepath);
break;
case self::TYPE_CSV:
self::Load(self::TYPE_CSV);
$this -> Handle = new SpreadsheetReader_CSV($Filepath, $this -> Options);
break;
case self::TYPE_XLS:
// Everything already happens above
break;
case self::TYPE_ODS:
self::Load(self::TYPE_ODS);
$this -> Handle = new SpreadsheetReader_ODS($Filepath, $this -> Options);
break;
}
}
/**
* Gets information about separate sheets in the given file
*
* @return array Associative array where key is sheet index and value is sheet name
*/
public function Sheets()
{
return $this -> Handle -> Sheets();
}
/**
* Changes the current sheet to another from the file.
* Note that changing the sheet will rewind the file to the beginning, even if
* the current sheet index is provided.
*
* @param int Sheet index
*
* @return bool True if sheet could be changed to the specified one,
* false if not (for example, if incorrect index was provided.
*/
public function ChangeSheet($Index)
{
return $this -> Handle -> ChangeSheet($Index);
}
/**
* Autoloads the required class for the particular spreadsheet type
*
* @param TYPE_* Spreadsheet type, one of TYPE_* constants of this class
*/
private static function Load($Type)
{
if (!in_array($Type, array(self::TYPE_XLSX, self::TYPE_XLS, self::TYPE_CSV, self::TYPE_ODS)))
{
throw new Exception('SpreadsheetReader: Invalid type ('.$Type.')');
}
// 2nd parameter is to prevent autoloading for the class.
// If autoload works, the require line is unnecessary, if it doesn't, it ends badly.
if (!class_exists('SpreadsheetReader_'.$Type, false))
{
require(dirname(__FILE__).DIRECTORY_SEPARATOR.'SpreadsheetReader_'.$Type.'.php');
}
}
// !Iterator interface methods
/**
* Rewind the Iterator to the first element.
* Similar to the reset() function for arrays in PHP
*/
public function rewind()
{
$this -> Index = 0;
if ($this -> Handle)
{
$this -> Handle -> rewind();
}
}
/**
* Return the current element.
* Similar to the current() function for arrays in PHP
*
* @return mixed current element from the collection
*/
public function current()
{
if ($this -> Handle)
{
return $this -> Handle -> current();
}
return null;
}
/**
* Move forward to next element.
* Similar to the next() function for arrays in PHP
*/
public function next()
{
if ($this -> Handle)
{
$this -> Index++;
return $this -> Handle -> next();
}
return null;
}
/**
* Return the identifying key of the current element.
* Similar to the key() function for arrays in PHP
*
* @return mixed either an integer or a string
*/
public function key()
{
if ($this -> Handle)
{
return $this -> Handle -> key();
}
return null;
}
/**
* Check if there is a current element after calls to rewind() or next().
* Used to check if we've iterated to the end of the collection
*
* @return boolean FALSE if there's nothing more to iterate over
*/
public function valid()
{
if ($this -> Handle)
{
return $this -> Handle -> valid();
}
return false;
}
// !Countable interface method
public function count()
{
if ($this -> Handle)
{
return $this -> Handle -> count();
}
return 0;
}
/**
* Method for SeekableIterator interface. Takes a posiiton and traverses the file to that position
* The value can be retrieved with a `current()` call afterwards.
*
* @param int Position in file
*/
public function seek($Position)
{
if (!$this -> Handle)
{
throw new OutOfBoundsException('SpreadsheetReader: No file opened');
}
$CurrentIndex = $this -> Handle -> key();
if ($CurrentIndex != $Position)
{
if ($Position < $CurrentIndex || is_null($CurrentIndex) || $Position == 0)
{
$this -> rewind();
}
while ($this -> Handle -> valid() && ($Position > $this -> Handle -> key()))
{
$this -> Handle -> next();
}
if (!$this -> Handle -> valid())
{
throw new OutOfBoundsException('SpreadsheetError: Position '.$Position.' not found');
}
}
return null;
}
}
?>
answered Apr 7 '16 at 11:14
Himanshu UpadhyayHimanshu Upadhyay
418321
add a comment |
3
I got an answer.
function xlsx_to_text($input_file){
require('SpreadsheetReader.php');
$Reader = new SpreadsheetReader($input_file);
$totalCount = 0;
$test=0;
foreach ($Reader as $Row)
{
$count_rows = $Row;
foreach($count_rows as $countRow){
if($countRow != ''){
$taa = explode(' ',$countRow);
$tCount = count($taa);
$result=$countRow." ";
}
}
}
return implode("",$result);
}
write below code in SpreadsheetReader.php
<?php
class SpreadsheetReader implements SeekableIterator, Countable
{
const TYPE_XLSX = 'XLSX';
const TYPE_XLS = 'XLS';
const TYPE_CSV = 'CSV';
const TYPE_ODS = 'ODS';
private $Options = array(
'Delimiter' => '',
'Enclosure' => '"'
);
/**
* @var int Current row in the file
*/
private $Index = 0;
/**
* @var SpreadsheetReader_* Handle for the reader object
*/
private $Handle = array();
/**
* @var TYPE_* Type of the contained spreadsheet
*/
private $Type = false;
/**
* @param string Path to file
* @param string Original filename (in case of an uploaded file), used to determine file type, optional
* @param string MIME type from an upload, used to determine file type, optional
*/
public function __construct($Filepath, $OriginalFilename = false, $MimeType = false)
{
if (!is_readable($Filepath))
{
throw new Exception('SpreadsheetReader: File ('.$Filepath.') not readable');
}
// To avoid timezone warnings and exceptions for formatting dates retrieved from files
$DefaultTZ = @date_default_timezone_get();
if ($DefaultTZ)
{
date_default_timezone_set($DefaultTZ);
}
// Checking the other parameters for correctness
// This should be a check for string but we're lenient
if (!empty($OriginalFilename) && !is_scalar($OriginalFilename))
{
throw new Exception('SpreadsheetReader: Original file (2nd parameter) path is not a string or a scalar value.');
}
if (!empty($MimeType) && !is_scalar($MimeType))
{
throw new Exception('SpreadsheetReader: Mime type (3nd parameter) path is not a string or a scalar value.');
}
// 1. Determine type
if (!$OriginalFilename)
{
$OriginalFilename = $Filepath;
}
$Extension = strtolower(pathinfo($OriginalFilename, PATHINFO_EXTENSION));
switch ($MimeType)
{
case 'text/csv':
case 'text/comma-separated-values':
case 'text/plain':
$this -> Type = self::TYPE_CSV;
break;
case 'application/vnd.ms-excel':
case 'application/msexcel':
case 'application/x-msexcel':
case 'application/x-ms-excel':
case 'application/vnd.ms-excel':
case 'application/x-excel':
case 'application/x-dos_ms_excel':
case 'application/xls':
case 'application/xlt':
case 'application/x-xls':
// Excel does weird stuff
if (in_array($Extension, array('csv', 'tsv', 'txt')))
{
$this -> Type = self::TYPE_CSV;
}
else
{
$this -> Type = self::TYPE_XLS;
}
break;
case 'application/vnd.oasis.opendocument.spreadsheet':
case 'application/vnd.oasis.opendocument.spreadsheet-template':
$this -> Type = self::TYPE_ODS;
break;
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.template':
case 'application/xlsx':
case 'application/xltx':
$this -> Type = self::TYPE_XLSX;
break;
case 'application/xml':
// Excel 2004 xml format uses this
break;
}
if (!$this -> Type)
{
switch ($Extension)
{
case 'xlsx':
case 'xltx': // XLSX template
case 'xlsm': // Macro-enabled XLSX
case 'xltm': // Macro-enabled XLSX template
$this -> Type = self::TYPE_XLSX;
break;
case 'xls':
case 'xlt':
$this -> Type = self::TYPE_XLS;
break;
case 'ods':
case 'odt':
$this -> Type = self::TYPE_ODS;
break;
default:
$this -> Type = self::TYPE_CSV;
break;
}
}
// Pre-checking XLS files, in case they are renamed CSV or XLSX files
if ($this -> Type == self::TYPE_XLS)
{
self::Load(self::TYPE_XLS);
$this -> Handle = new SpreadsheetReader_XLS($Filepath);
if ($this -> Handle -> Error)
{
$this -> Handle -> __destruct();
if (is_resource($ZipHandle = zip_open($Filepath)))
{
$this -> Type = self::TYPE_XLSX;
zip_close($ZipHandle);
}
else
{
$this -> Type = self::TYPE_CSV;
}
}
}
// 2. Create handle
switch ($this -> Type)
{
case self::TYPE_XLSX:
self::Load(self::TYPE_XLSX);
$this -> Handle = new SpreadsheetReader_XLSX($Filepath);
break;
case self::TYPE_CSV:
self::Load(self::TYPE_CSV);
$this -> Handle = new SpreadsheetReader_CSV($Filepath, $this -> Options);
break;
case self::TYPE_XLS:
// Everything already happens above
break;
case self::TYPE_ODS:
self::Load(self::TYPE_ODS);
$this -> Handle = new SpreadsheetReader_ODS($Filepath, $this -> Options);
break;
}
}
/**
* Gets information about separate sheets in the given file
*
* @return array Associative array where key is sheet index and value is sheet name
*/
public function Sheets()
{
return $this -> Handle -> Sheets();
}
/**
* Changes the current sheet to another from the file.
* Note that changing the sheet will rewind the file to the beginning, even if
* the current sheet index is provided.
*
* @param int Sheet index
*
* @return bool True if sheet could be changed to the specified one,
* false if not (for example, if incorrect index was provided.
*/
public function ChangeSheet($Index)
{
return $this -> Handle -> ChangeSheet($Index);
}
/**
* Autoloads the required class for the particular spreadsheet type
*
* @param TYPE_* Spreadsheet type, one of TYPE_* constants of this class
*/
private static function Load($Type)
{
if (!in_array($Type, array(self::TYPE_XLSX, self::TYPE_XLS, self::TYPE_CSV, self::TYPE_ODS)))
{
throw new Exception('SpreadsheetReader: Invalid type ('.$Type.')');
}
// 2nd parameter is to prevent autoloading for the class.
// If autoload works, the require line is unnecessary, if it doesn't, it ends badly.
if (!class_exists('SpreadsheetReader_'.$Type, false))
{
require(dirname(__FILE__).DIRECTORY_SEPARATOR.'SpreadsheetReader_'.$Type.'.php');
}
}
// !Iterator interface methods
/**
* Rewind the Iterator to the first element.
* Similar to the reset() function for arrays in PHP
*/
public function rewind()
{
$this -> Index = 0;
if ($this -> Handle)
{
$this -> Handle -> rewind();
}
}
/**
* Return the current element.
* Similar to the current() function for arrays in PHP
*
* @return mixed current element from the collection
*/
public function current()
{
if ($this -> Handle)
{
return $this -> Handle -> current();
}
return null;
}
/**
* Move forward to next element.
* Similar to the next() function for arrays in PHP
*/
public function next()
{
if ($this -> Handle)
{
$this -> Index++;
return $this -> Handle -> next();
}
return null;
}
/**
* Return the identifying key of the current element.
* Similar to the key() function for arrays in PHP
*
* @return mixed either an integer or a string
*/
public function key()
{
if ($this -> Handle)
{
return $this -> Handle -> key();
}
return null;
}
/**
* Check if there is a current element after calls to rewind() or next().
* Used to check if we've iterated to the end of the collection
*
* @return boolean FALSE if there's nothing more to iterate over
*/
public function valid()
{
if ($this -> Handle)
{
return $this -> Handle -> valid();
}
return false;
}
// !Countable interface method
public function count()
{
if ($this -> Handle)
{
return $this -> Handle -> count();
}
return 0;
}
/**
* Method for SeekableIterator interface. Takes a posiiton and traverses the file to that position
* The value can be retrieved with a `current()` call afterwards.
*
* @param int Position in file
*/
public function seek($Position)
{
if (!$this -> Handle)
{
throw new OutOfBoundsException('SpreadsheetReader: No file opened');
}
$CurrentIndex = $this -> Handle -> key();
if ($CurrentIndex != $Position)
{
if ($Position < $CurrentIndex || is_null($CurrentIndex) || $Position == 0)
{
$this -> rewind();
}
while ($this -> Handle -> valid() && ($Position > $this -> Handle -> key()))
{
$this -> Handle -> next();
}
if (!$this -> Handle -> valid())
{
throw new OutOfBoundsException('SpreadsheetError: Position '.$Position.' not found');
}
}
return null;
}
}
?>
answered Apr 7 '16 at 11:14
Himanshu UpadhyayHimanshu Upadhyay
418321
add a comment |
3
3
3
I got an answer.
function xlsx_to_text($input_file){
require('SpreadsheetReader.php');
$Reader = new SpreadsheetReader($input_file);
$totalCount = 0;
$test=0;
foreach ($Reader as $Row)
{
$count_rows = $Row;
foreach($count_rows as $countRow){
if($countRow != ''){
$taa = explode(' ',$countRow);
$tCount = count($taa);
$result=$countRow." ";
}
}
}
return implode("",$result);
}
write below code in SpreadsheetReader.php
<?php
class SpreadsheetReader implements SeekableIterator, Countable
{
const TYPE_XLSX = 'XLSX';
const TYPE_XLS = 'XLS';
const TYPE_CSV = 'CSV';
const TYPE_ODS = 'ODS';
private $Options = array(
'Delimiter' => '',
'Enclosure' => '"'
);
/**
* @var int Current row in the file
*/
private $Index = 0;
/**
* @var SpreadsheetReader_* Handle for the reader object
*/
private $Handle = array();
/**
* @var TYPE_* Type of the contained spreadsheet
*/
private $Type = false;
/**
* @param string Path to file
* @param string Original filename (in case of an uploaded file), used to determine file type, optional
* @param string MIME type from an upload, used to determine file type, optional
*/
public function __construct($Filepath, $OriginalFilename = false, $MimeType = false)
{
if (!is_readable($Filepath))
{
throw new Exception('SpreadsheetReader: File ('.$Filepath.') not readable');
}
// To avoid timezone warnings and exceptions for formatting dates retrieved from files
$DefaultTZ = @date_default_timezone_get();
if ($DefaultTZ)
{
date_default_timezone_set($DefaultTZ);
}
// Checking the other parameters for correctness
// This should be a check for string but we're lenient
if (!empty($OriginalFilename) && !is_scalar($OriginalFilename))
{
throw new Exception('SpreadsheetReader: Original file (2nd parameter) path is not a string or a scalar value.');
}
if (!empty($MimeType) && !is_scalar($MimeType))
{
throw new Exception('SpreadsheetReader: Mime type (3nd parameter) path is not a string or a scalar value.');
}
// 1. Determine type
if (!$OriginalFilename)
{
$OriginalFilename = $Filepath;
}
$Extension = strtolower(pathinfo($OriginalFilename, PATHINFO_EXTENSION));
switch ($MimeType)
{
case 'text/csv':
case 'text/comma-separated-values':
case 'text/plain':
$this -> Type = self::TYPE_CSV;
break;
case 'application/vnd.ms-excel':
case 'application/msexcel':
case 'application/x-msexcel':
case 'application/x-ms-excel':
case 'application/vnd.ms-excel':
case 'application/x-excel':
case 'application/x-dos_ms_excel':
case 'application/xls':
case 'application/xlt':
case 'application/x-xls':
// Excel does weird stuff
if (in_array($Extension, array('csv', 'tsv', 'txt')))
{
$this -> Type = self::TYPE_CSV;
}
else
{
$this -> Type = self::TYPE_XLS;
}
break;
case 'application/vnd.oasis.opendocument.spreadsheet':
case 'application/vnd.oasis.opendocument.spreadsheet-template':
$this -> Type = self::TYPE_ODS;
break;
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.template':
case 'application/xlsx':
case 'application/xltx':
$this -> Type = self::TYPE_XLSX;
break;
case 'application/xml':
// Excel 2004 xml format uses this
break;
}
if (!$this -> Type)
{
switch ($Extension)
{
case 'xlsx':
case 'xltx': // XLSX template
case 'xlsm': // Macro-enabled XLSX
case 'xltm': // Macro-enabled XLSX template
$this -> Type = self::TYPE_XLSX;
break;
case 'xls':
case 'xlt':
$this -> Type = self::TYPE_XLS;
break;
case 'ods':
case 'odt':
$this -> Type = self::TYPE_ODS;
break;
default:
$this -> Type = self::TYPE_CSV;
break;
}
}
// Pre-checking XLS files, in case they are renamed CSV or XLSX files
if ($this -> Type == self::TYPE_XLS)
{
self::Load(self::TYPE_XLS);
$this -> Handle = new SpreadsheetReader_XLS($Filepath);
if ($this -> Handle -> Error)
{
$this -> Handle -> __destruct();
if (is_resource($ZipHandle = zip_open($Filepath)))
{
$this -> Type = self::TYPE_XLSX;
zip_close($ZipHandle);
}
else
{
$this -> Type = self::TYPE_CSV;
}
}
}
// 2. Create handle
switch ($this -> Type)
{
case self::TYPE_XLSX:
self::Load(self::TYPE_XLSX);
$this -> Handle = new SpreadsheetReader_XLSX($Filepath);
break;
case self::TYPE_CSV:
self::Load(self::TYPE_CSV);
$this -> Handle = new SpreadsheetReader_CSV($Filepath, $this -> Options);
break;
case self::TYPE_XLS:
// Everything already happens above
break;
case self::TYPE_ODS:
self::Load(self::TYPE_ODS);
$this -> Handle = new SpreadsheetReader_ODS($Filepath, $this -> Options);
break;
}
}
/**
* Gets information about separate sheets in the given file
*
* @return array Associative array where key is sheet index and value is sheet name
*/
public function Sheets()
{
return $this -> Handle -> Sheets();
}
/**
* Changes the current sheet to another from the file.
* Note that changing the sheet will rewind the file to the beginning, even if
* the current sheet index is provided.
*
* @param int Sheet index
*
* @return bool True if sheet could be changed to the specified one,
* false if not (for example, if incorrect index was provided.
*/
public function ChangeSheet($Index)
{
return $this -> Handle -> ChangeSheet($Index);
}
/**
* Autoloads the required class for the particular spreadsheet type
*
* @param TYPE_* Spreadsheet type, one of TYPE_* constants of this class
*/
private static function Load($Type)
{
if (!in_array($Type, array(self::TYPE_XLSX, self::TYPE_XLS, self::TYPE_CSV, self::TYPE_ODS)))
{
throw new Exception('SpreadsheetReader: Invalid type ('.$Type.')');
}
// 2nd parameter is to prevent autoloading for the class.
// If autoload works, the require line is unnecessary, if it doesn't, it ends badly.
if (!class_exists('SpreadsheetReader_'.$Type, false))
{
require(dirname(__FILE__).DIRECTORY_SEPARATOR.'SpreadsheetReader_'.$Type.'.php');
}
}
// !Iterator interface methods
/**
* Rewind the Iterator to the first element.
* Similar to the reset() function for arrays in PHP
*/
public function rewind()
{
$this -> Index = 0;
if ($this -> Handle)
{
$this -> Handle -> rewind();
}
}
/**
* Return the current element.
* Similar to the current() function for arrays in PHP
*
* @return mixed current element from the collection
*/
public function current()
{
if ($this -> Handle)
{
return $this -> Handle -> current();
}
return null;
}
/**
* Move forward to next element.
* Similar to the next() function for arrays in PHP
*/
public function next()
{
if ($this -> Handle)
{
$this -> Index++;
return $this -> Handle -> next();
}
return null;
}
/**
* Return the identifying key of the current element.
* Similar to the key() function for arrays in PHP
*
* @return mixed either an integer or a string
*/
public function key()
{
if ($this -> Handle)
{
return $this -> Handle -> key();
}
return null;
}
/**
* Check if there is a current element after calls to rewind() or next().
* Used to check if we've iterated to the end of the collection
*
* @return boolean FALSE if there's nothing more to iterate over
*/
public function valid()
{
if ($this -> Handle)
{
return $this -> Handle -> valid();
}
return false;
}
// !Countable interface method
public function count()
{
if ($this -> Handle)
{
return $this -> Handle -> count();
}
return 0;
}
/**
* Method for SeekableIterator interface. Takes a posiiton and traverses the file to that position
* The value can be retrieved with a `current()` call afterwards.
*
* @param int Position in file
*/
public function seek($Position)
{
if (!$this -> Handle)
{
throw new OutOfBoundsException('SpreadsheetReader: No file opened');
}
$CurrentIndex = $this -> Handle -> key();
if ($CurrentIndex != $Position)
{
if ($Position < $CurrentIndex || is_null($CurrentIndex) || $Position == 0)
{
$this -> rewind();
}
while ($this -> Handle -> valid() && ($Position > $this -> Handle -> key()))
{
$this -> Handle -> next();
}
if (!$this -> Handle -> valid())
{
throw new OutOfBoundsException('SpreadsheetError: Position '.$Position.' not found');
}
}
return null;
}
}
?>
answered Apr 7 '16 at 11:14
Himanshu UpadhyayHimanshu Upadhyay
418321
I got an answer.
function xlsx_to_text($input_file){
require('SpreadsheetReader.php');
$Reader = new SpreadsheetReader($input_file);
$totalCount = 0;
$test=0;
foreach ($Reader as $Row)
{
$count_rows = $Row;
foreach($count_rows as $countRow){
if($countRow != ''){
$taa = explode(' ',$countRow);
$tCount = count($taa);
$result=$countRow." ";
}
}
}
return implode("",$result);
}
write below code in SpreadsheetReader.php
<?php
class SpreadsheetReader implements SeekableIterator, Countable
{
const TYPE_XLSX = 'XLSX';
const TYPE_XLS = 'XLS';
const TYPE_CSV = 'CSV';
const TYPE_ODS = 'ODS';
private $Options = array(
'Delimiter' => '',
'Enclosure' => '"'
);
/**
* @var int Current row in the file
*/
private $Index = 0;
/**
* @var SpreadsheetReader_* Handle for the reader object
*/
private $Handle = array();
/**
* @var TYPE_* Type of the contained spreadsheet
*/
private $Type = false;
/**
* @param string Path to file
* @param string Original filename (in case of an uploaded file), used to determine file type, optional
* @param string MIME type from an upload, used to determine file type, optional
*/
public function __construct($Filepath, $OriginalFilename = false, $MimeType = false)
{
if (!is_readable($Filepath))
{
throw new Exception('SpreadsheetReader: File ('.$Filepath.') not readable');
}
// To avoid timezone warnings and exceptions for formatting dates retrieved from files
$DefaultTZ = @date_default_timezone_get();
if ($DefaultTZ)
{
date_default_timezone_set($DefaultTZ);
}
// Checking the other parameters for correctness
// This should be a check for string but we're lenient
if (!empty($OriginalFilename) && !is_scalar($OriginalFilename))
{
throw new Exception('SpreadsheetReader: Original file (2nd parameter) path is not a string or a scalar value.');
}
if (!empty($MimeType) && !is_scalar($MimeType))
{
throw new Exception('SpreadsheetReader: Mime type (3nd parameter) path is not a string or a scalar value.');
}
// 1. Determine type
if (!$OriginalFilename)
{
$OriginalFilename = $Filepath;
}
$Extension = strtolower(pathinfo($OriginalFilename, PATHINFO_EXTENSION));
switch ($MimeType)
{
case 'text/csv':
case 'text/comma-separated-values':
case 'text/plain':
$this -> Type = self::TYPE_CSV;
break;
case 'application/vnd.ms-excel':
case 'application/msexcel':
case 'application/x-msexcel':
case 'application/x-ms-excel':
case 'application/vnd.ms-excel':
case 'application/x-excel':
case 'application/x-dos_ms_excel':
case 'application/xls':
case 'application/xlt':
case 'application/x-xls':
// Excel does weird stuff
if (in_array($Extension, array('csv', 'tsv', 'txt')))
{
$this -> Type = self::TYPE_CSV;
}
else
{
$this -> Type = self::TYPE_XLS;
}
break;
case 'application/vnd.oasis.opendocument.spreadsheet':
case 'application/vnd.oasis.opendocument.spreadsheet-template':
$this -> Type = self::TYPE_ODS;
break;
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
case 'application/vnd.openxmlformats-officedocument.spreadsheetml.template':
case 'application/xlsx':
case 'application/xltx':
$this -> Type = self::TYPE_XLSX;
break;
case 'application/xml':
// Excel 2004 xml format uses this
break;
}
if (!$this -> Type)
{
switch ($Extension)
{
case 'xlsx':
case 'xltx': // XLSX template
case 'xlsm': // Macro-enabled XLSX
case 'xltm': // Macro-enabled XLSX template
$this -> Type = self::TYPE_XLSX;
break;
case 'xls':
case 'xlt':
$this -> Type = self::TYPE_XLS;
break;
case 'ods':
case 'odt':
$this -> Type = self::TYPE_ODS;
break;
default:
$this -> Type = self::TYPE_CSV;
break;
}
}
// Pre-checking XLS files, in case they are renamed CSV or XLSX files
if ($this -> Type == self::TYPE_XLS)
{
self::Load(self::TYPE_XLS);
$this -> Handle = new SpreadsheetReader_XLS($Filepath);
if ($this -> Handle -> Error)
{
$this -> Handle -> __destruct();
if (is_resource($ZipHandle = zip_open($Filepath)))
{
$this -> Type = self::TYPE_XLSX;
zip_close($ZipHandle);
}
else
{
$this -> Type = self::TYPE_CSV;
}
}
}
// 2. Create handle
switch ($this -> Type)
{
case self::TYPE_XLSX:
self::Load(self::TYPE_XLSX);
$this -> Handle = new SpreadsheetReader_XLSX($Filepath);
break;
case self::TYPE_CSV:
self::Load(self::TYPE_CSV);
$this -> Handle = new SpreadsheetReader_CSV($Filepath, $this -> Options);
break;
case self::TYPE_XLS:
// Everything already happens above
break;
case self::TYPE_ODS:
self::Load(self::TYPE_ODS);
$this -> Handle = new SpreadsheetReader_ODS($Filepath, $this -> Options);
break;
}
}
/**
* Gets information about separate sheets in the given file
*
* @return array Associative array where key is sheet index and value is sheet name
*/
public function Sheets()
{
return $this -> Handle -> Sheets();
}
/**
* Changes the current sheet to another from the file.
* Note that changing the sheet will rewind the file to the beginning, even if
* the current sheet index is provided.
*
* @param int Sheet index
*
* @return bool True if sheet could be changed to the specified one,
* false if not (for example, if incorrect index was provided.
*/
public function ChangeSheet($Index)
{
return $this -> Handle -> ChangeSheet($Index);
}
/**
* Autoloads the required class for the particular spreadsheet type
*
* @param TYPE_* Spreadsheet type, one of TYPE_* constants of this class
*/
private static function Load($Type)
{
if (!in_array($Type, array(self::TYPE_XLSX, self::TYPE_XLS, self::TYPE_CSV, self::TYPE_ODS)))
{
throw new Exception('SpreadsheetReader: Invalid type ('.$Type.')');
}
// 2nd parameter is to prevent autoloading for the class.
// If autoload works, the require line is unnecessary, if it doesn't, it ends badly.
if (!class_exists('SpreadsheetReader_'.$Type, false))
{
require(dirname(__FILE__).DIRECTORY_SEPARATOR.'SpreadsheetReader_'.$Type.'.php');
}
}
// !Iterator interface methods
/**
* Rewind the Iterator to the first element.
* Similar to the reset() function for arrays in PHP
*/
public function rewind()
{
$this -> Index = 0;
if ($this -> Handle)
{
$this -> Handle -> rewind();
}
}
/**
* Return the current element.
* Similar to the current() function for arrays in PHP
*
* @return mixed current element from the collection
*/
public function current()
{
if ($this -> Handle)
{
return $this -> Handle -> current();
}
return null;
}
/**
* Move forward to next element.
* Similar to the next() function for arrays in PHP
*/
public function next()
{
if ($this -> Handle)
{
$this -> Index++;
return $this -> Handle -> next();
}
return null;
}
/**
* Return the identifying key of the current element.
* Similar to the key() function for arrays in PHP
*
* @return mixed either an integer or a string
*/
public function key()
{
if ($this -> Handle)
{
return $this -> Handle -> key();
}
return null;
}
/**
* Check if there is a current element after calls to rewind() or next().
* Used to check if we've iterated to the end of the collection
*
* @return boolean FALSE if there's nothing more to iterate over
*/
public function valid()
{
if ($this -> Handle)
{
return $this -> Handle -> valid();
}
return false;
}
// !Countable interface method
public function count()
{
if ($this -> Handle)
{
return $this -> Handle -> count();
}
return 0;
}
/**
* Method for SeekableIterator interface. Takes a posiiton and traverses the file to that position
* The value can be retrieved with a `current()` call afterwards.
*
* @param int Position in file
*/
public function seek($Position)
{
if (!$this -> Handle)
{
throw new OutOfBoundsException('SpreadsheetReader: No file opened');
}
$CurrentIndex = $this -> Handle -> key();
if ($CurrentIndex != $Position)
{
if ($Position < $CurrentIndex || is_null($CurrentIndex) || $Position == 0)
{
$this -> rewind();
}
while ($this -> Handle -> valid() && ($Position > $this -> Handle -> key()))
{
$this -> Handle -> next();
}
if (!$this -> Handle -> valid())
{
throw new OutOfBoundsException('SpreadsheetError: Position '.$Position.' not found');
}
}
return null;
}
}
?>
answered Apr 7 '16 at 11:14
Himanshu UpadhyayHimanshu Upadhyay
418321
answered Apr 7 '16 at 11:14
Himanshu UpadhyayHimanshu Upadhyay
418321
answered Apr 7 '16 at 11:14
Himanshu UpadhyayHimanshu Upadhyay
418321
answered Apr 7 '16 at 11:14
Himanshu UpadhyayHimanshu Upadhyay
418321
418321
add a comment |
add a comment |
1
Why don't you use some existing libraries like Spout or PHPExcel to read data from your XLSX files? There are a lot of edge cases that these libraries have already solved. Some examples: security with the XML file, inline data, metadata stored in sharedStrings.xml, large XML file... Doing it yourself means you will have to find all these edge cases again and handle them in your code.
Parsing the sharedStrings.xml file is unfortunately not as easy as calling strip_tags($xml_handle->saveXML());.
Now your code should kind of work if the string "I have an apple" is in one cell. It seems like each word is in a different cell so your XML looks like this:
<si><t>I</t></si>
<si><t>have</t></si>
<si><t>an</t></si>
<si><t>apple</t></si>
So when you remove all the tags, you end up with "Ihaveanapple", as no spaces get inserted.
answered Feb 29 '16 at 22:55
AdrienAdrien
1,3301715
PHPExcel is a superb one for this purpose.
– Himel Nag Rana
Apr 7 '16 at 11:22
add a comment |
1
Why don't you use some existing libraries like Spout or PHPExcel to read data from your XLSX files? There are a lot of edge cases that these libraries have already solved. Some examples: security with the XML file, inline data, metadata stored in sharedStrings.xml, large XML file... Doing it yourself means you will have to find all these edge cases again and handle them in your code.
Parsing the sharedStrings.xml file is unfortunately not as easy as calling strip_tags($xml_handle->saveXML());.
Now your code should kind of work if the string "I have an apple" is in one cell. It seems like each word is in a different cell so your XML looks like this:
<si><t>I</t></si>
<si><t>have</t></si>
<si><t>an</t></si>
<si><t>apple</t></si>
So when you remove all the tags, you end up with "Ihaveanapple", as no spaces get inserted.
answered Feb 29 '16 at 22:55
AdrienAdrien
1,3301715
PHPExcel is a superb one for this purpose.
– Himel Nag Rana
Apr 7 '16 at 11:22
add a comment |
1
1
1
Why don't you use some existing libraries like Spout or PHPExcel to read data from your XLSX files? There are a lot of edge cases that these libraries have already solved. Some examples: security with the XML file, inline data, metadata stored in sharedStrings.xml, large XML file... Doing it yourself means you will have to find all these edge cases again and handle them in your code.
Parsing the sharedStrings.xml file is unfortunately not as easy as calling strip_tags($xml_handle->saveXML());.
Now your code should kind of work if the string "I have an apple" is in one cell. It seems like each word is in a different cell so your XML looks like this:
<si><t>I</t></si>
<si><t>have</t></si>
<si><t>an</t></si>
<si><t>apple</t></si>
So when you remove all the tags, you end up with "Ihaveanapple", as no spaces get inserted.
answered Feb 29 '16 at 22:55
AdrienAdrien
1,3301715
Why don't you use some existing libraries like Spout or PHPExcel to read data from your XLSX files? There are a lot of edge cases that these libraries have already solved. Some examples: security with the XML file, inline data, metadata stored in sharedStrings.xml, large XML file... Doing it yourself means you will have to find all these edge cases again and handle them in your code.
Parsing the sharedStrings.xml file is unfortunately not as easy as calling strip_tags($xml_handle->saveXML());.
Now your code should kind of work if the string "I have an apple" is in one cell. It seems like each word is in a different cell so your XML looks like this:
<si><t>I</t></si>
<si><t>have</t></si>
<si><t>an</t></si>
<si><t>apple</t></si>
So when you remove all the tags, you end up with "Ihaveanapple", as no spaces get inserted.
answered Feb 29 '16 at 22:55
AdrienAdrien
1,3301715
answered Feb 29 '16 at 22:55
AdrienAdrien
1,3301715
answered Feb 29 '16 at 22:55
AdrienAdrien
1,3301715
answered Feb 29 '16 at 22:55
AdrienAdrien
1,3301715
1,3301715
PHPExcel is a superb one for this purpose.
– Himel Nag Rana
Apr 7 '16 at 11:22
add a comment |
PHPExcel is a superb one for this purpose.
– Himel Nag Rana
Apr 7 '16 at 11:22
PHPExcel is a superb one for this purpose.
– Himel Nag Rana
Apr 7 '16 at 11:22
PHPExcel is a superb one for this purpose.
– Himel Nag Rana
Apr 7 '16 at 11:22
add a comment |
0
Why not use PhpOffice/PhpSpreadsheet.
<?php
// Create a new Reader of the type defined in $inputFileType
// If file type is Xlsx / Csv this package will read.
$reader = PhpOfficePhpSpreadsheetIOFactory::createReader("Xlsx");
// Load $inputFileName to a PhpSpreadsheet Object
$spreadsheet = $reader->load($xlsxFileName);
$worksheet = $spreadsheet->getActiveSheet();
// TODO: Add a const for Xlsx and Csv
$dataEndRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$headersArrayList = $worksheet->rangeToArray("A1:{$highestColumn}1", null, true, true, true);
if (!isset($headersArrayList[1])) {
return;
}
$headersArray = $headersArrayList[1];
$data = $worksheet->rangeToArray("A2:{$highestColumn}{$dataEndRow}", null, true, true, true);
?>
The data will contain two dimensional array. Rows keys will be integer and Columns keys will be string.
Accessing a cell, $data[2][A] will give you value in 2nd row, A column of your spreadsheet.
Note: This package requires xmlreader, xml, dom, xmlwriter php extensions
answered Dec 28 '18 at 9:28
theBuzzyCodertheBuzzyCoder
9421216
add a comment |
0
Why not use PhpOffice/PhpSpreadsheet.
<?php
// Create a new Reader of the type defined in $inputFileType
// If file type is Xlsx / Csv this package will read.
$reader = PhpOfficePhpSpreadsheetIOFactory::createReader("Xlsx");
// Load $inputFileName to a PhpSpreadsheet Object
$spreadsheet = $reader->load($xlsxFileName);
$worksheet = $spreadsheet->getActiveSheet();
// TODO: Add a const for Xlsx and Csv
$dataEndRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$headersArrayList = $worksheet->rangeToArray("A1:{$highestColumn}1", null, true, true, true);
if (!isset($headersArrayList[1])) {
return;
}
$headersArray = $headersArrayList[1];
$data = $worksheet->rangeToArray("A2:{$highestColumn}{$dataEndRow}", null, true, true, true);
?>
The data will contain two dimensional array. Rows keys will be integer and Columns keys will be string.
Accessing a cell, $data[2][A] will give you value in 2nd row, A column of your spreadsheet.
Note: This package requires xmlreader, xml, dom, xmlwriter php extensions
answered Dec 28 '18 at 9:28
theBuzzyCodertheBuzzyCoder
9421216
add a comment |
0
0
0
Why not use PhpOffice/PhpSpreadsheet.
<?php
// Create a new Reader of the type defined in $inputFileType
// If file type is Xlsx / Csv this package will read.
$reader = PhpOfficePhpSpreadsheetIOFactory::createReader("Xlsx");
// Load $inputFileName to a PhpSpreadsheet Object
$spreadsheet = $reader->load($xlsxFileName);
$worksheet = $spreadsheet->getActiveSheet();
// TODO: Add a const for Xlsx and Csv
$dataEndRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$headersArrayList = $worksheet->rangeToArray("A1:{$highestColumn}1", null, true, true, true);
if (!isset($headersArrayList[1])) {
return;
}
$headersArray = $headersArrayList[1];
$data = $worksheet->rangeToArray("A2:{$highestColumn}{$dataEndRow}", null, true, true, true);
?>
The data will contain two dimensional array. Rows keys will be integer and Columns keys will be string.
Accessing a cell, $data[2][A] will give you value in 2nd row, A column of your spreadsheet.
Note: This package requires xmlreader, xml, dom, xmlwriter php extensions
answered Dec 28 '18 at 9:28
theBuzzyCodertheBuzzyCoder
9421216
Why not use PhpOffice/PhpSpreadsheet.
<?php
// Create a new Reader of the type defined in $inputFileType
// If file type is Xlsx / Csv this package will read.
$reader = PhpOfficePhpSpreadsheetIOFactory::createReader("Xlsx");
// Load $inputFileName to a PhpSpreadsheet Object
$spreadsheet = $reader->load($xlsxFileName);
$worksheet = $spreadsheet->getActiveSheet();
// TODO: Add a const for Xlsx and Csv
$dataEndRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$headersArrayList = $worksheet->rangeToArray("A1:{$highestColumn}1", null, true, true, true);
if (!isset($headersArrayList[1])) {
return;
}
$headersArray = $headersArrayList[1];
$data = $worksheet->rangeToArray("A2:{$highestColumn}{$dataEndRow}", null, true, true, true);
?>
The data will contain two dimensional array. Rows keys will be integer and Columns keys will be string.
Accessing a cell, $data[2][A] will give you value in 2nd row, A column of your spreadsheet.
Note: This package requires xmlreader, xml, dom, xmlwriter php extensions
answered Dec 28 '18 at 9:28
theBuzzyCodertheBuzzyCoder
9421216
answered Dec 28 '18 at 9:28
theBuzzyCodertheBuzzyCoder
9421216
answered Dec 28 '18 at 9:28
theBuzzyCodertheBuzzyCoder
9421216
answered Dec 28 '18 at 9:28
theBuzzyCodertheBuzzyCoder
9421216
9421216
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f35665625%2fxlsx-to-text-with-php%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
replace one of the tags with a space
– user557846
Feb 27 '16 at 4:25
but actually with same file, i got desired result from reference site..
– Himanshu Upadhyay
Feb 27 '16 at 4:57
not sure what that means but i imagine they have no spaces between tags so you have to had them
– user557846
Feb 27 '16 at 5:14