Uploading and Reading an XLSX file.

Scriptcase 8 Discussion
#1

I am trying to upload an XLSX file with no luck. If I convert the file to XLS it works great. I am using PHP 5.6 and I have the zip extension enabled. I have verified that the file is being uploaded to the correct location and it has the same permissions as the XLS file. Any ideas would be greatly appreciated.

I am getting the following error messages.

XMLReader::open(): Unable to open source data

Script: /var/www/html/scriptcase/prod/third/phpexcel/PHPExcel/Reader/Excel2007.php (212)
XMLReader::setParserProperty(): Invalid parser property

Script: /var/www/html/scriptcase/prod/third/phpexcel/PHPExcel/Reader/Excel2007.php (213)
XMLReader::read(): Load Data before trying to read

Script: /var/www/html/scriptcase/prod/third/phpexcel/PHPExcel/Reader/Excel2007.php (216)

I am using the example code posted on the the scriptcase website.

require_once(’…/…/…/…’.$this->Ini->path_prod.’/third/phpexcel/PHPExcel.php’);
require_once(’…/…/…/…’.$this->Ini->path_prod.’/third/phpexcel/PHPExcel/IOFactory.php’);

$array_filename = explode(’.’,$this->xlsinput_ul_name);
[table_name] = $array_filename[0];

$fileXlsX = explode(’.’,$this->xlsinput_ul_name);

if($fileXlsX[1]==‘xlsx’){

//xlsx
$inputFileName = “…/…/…/tmp/”.$this->xlsinput_ul_name;
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
$worksheetData = $objReader->listWorksheetInfo("…/…/…/tmp/".$this->xlsinput_ul_name);
$totalRows = $worksheetData[0][‘totalRows’];
$totalColumns = $worksheetData[0][‘totalColumns’];
$lastColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
$sheet = $objPHPExcel->getSheet(0);
$columnNames="";
$rowDate ="";

for($colum=0;$colum<$totalColumns;$colum++){
if($colum==$totalColumns-1){
$columnNames.= utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($colum, 1)->getValue());
}else{
$columnNames.= utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($colum, 1)->getValue()).",";
}

}
for ($row = 2; $row <= $totalRows; $row++){
for($colum=0;$colum<$totalColumns;$colum++){

if($colum==$totalColumns-1){
$rowDate .= “’”.utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($colum, $row)->getValue())."’";
}else{
$rowDate .= “’”.utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($colum, $row)->getValue())."’,";
}
}
echo “INSERT INTO TABLEA (”.$columnNames.") VALUES (".$rowDate.")";
echo “<br>”;
$rowDate="";
}

}else{
//xls
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(false);
$objPHPExcel = $objReader->load("…/…/…/tmp/".$this->xlsinput_ul_name);

$worksheetData = $objReader->listWorksheetInfo("…/…/…/tmp/".$this->xlsinput_ul_name);
$totalRows = $worksheetData[0][‘totalRows’];
$totalColumns = $worksheetData[0][‘totalColumns’];
$lastColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
$sheet = $objPHPExcel->getSheet(0);
$columnNames="";
$rowDate ="";

for($colum=0;$colum<$totalColumns;$colum++){
if($colum==$totalColumns-1){
$columnNames.= utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($colum, 1)->getValue());
}else{
$columnNames.= utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($colum, 1)->getValue()).",";
}

}
for ($row = 2; $row <= $totalRows; $row++){
for($colum=0;$colum<$totalColumns;$colum++){

if($colum==$totalColumns-1){
$rowDate .= “’”.utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($colum, $row)->getFormattedValue())."’";
}else{
$rowDate .= “’”.utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($colum, $row)->getFormattedValue())."’,";
}
}
echo “INSERT INTO TABLEA (”.$columnNames.") VALUES (".$rowDate.")";
echo “<br>”;}

}

#2

In Excel, there is an .xlsx format called Strict Open XML Spreadsheet. Try saving your MS Office default .xlsx spreadsheet in this format and importing. The PHPExcel documentation seems to suggest that only the Open XML flavor of .xlsx is supported.

#3

I have verified that I have saved the file in the MS Office default .xlsx spreadsheet format. I still get the same errors. AN

#4

MS Office default format is not what I suggested. Strict Open XML is the format I would try. Both have an .xlsx extension. You need to select Strict Open XML explicitly.
is the wrong format.

is the format I am suggesting.

Just want to make sure I was explaining properly. There may still be a problem, but at least I would try this first.

#5

Hi Beckjoh,

Recently we also encounter this problem in an application that had always worked. The solution was to replace the library that brings Scriptcase phpexcel in the trird directory by this other https://github.com/PHPOffice/PHPExcel