PHPExcel
PHPExcel copied to clipboard
Published
20 hours ago •
PHPOffice
PHPOffice
Removing empty columns and rows
Hi, guys, can you please tell me how to load XLS file and save it as static html page without empty rows/columns
My current listing is
require_once 'assets/libs/phpexcel/PHPExcel/IOFactory.php';
$xls=$_GET['xls']; //example
if (!file_exists($xls)) {
$out = "<p style=\"padding:1rem\">No table attached</p>";
return $out;
}else{
//new file name
$htm=str_replace('.xlsx','.htm',$xls);
//read
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($xls);
//saving to html
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace('.xlsx', '.htm', $xls));
//read from file
$table=file_get_contents($htm);
$output=substr($table,strpos($table,'<body>'),strpos($table,'</body>')-strpos($table,'<body>'));
$output=str_replace('border="0" cellpadding="0" cellspacing="0"','border="1" cellpadding="1" cellspacing="1"',$output);
echo $output;
}
Result produces large amount of empty columns and rows(cut)
how to avoid empty cells? Attached sample file 01.xlsx
Here is a snippet from my code, which should only load the data that is actually written. Maybe it will be of help to you:
/**
* Reads the data from the file
*
* @return array Excel data
*/
public function getData()
{
$loader = \PHPExcel_IOFactory::createReaderForFile($this->filename);
$loader->setReadDataOnly(true);
// Load only the first sheet
$worksheets = $loader->listWorkSheetNames($this->filename);
$loader->setLoadSheetsOnly($worksheets[0]);
$reader = $loader->load($this->filename);
$sheet = $reader->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highestCol = $sheet->getHighestColumn();
return $sheet->rangeToArray("A1:$highestCol$highestRow", null, true, false, false);
}
/**
* Reads the data from the file
*
* @return array Excel data
*/
public function getData()
{
$loader = \PHPExcel_IOFactory::createReaderForFile($this->filename);
$loader->setReadDataOnly(true);
$reader = $loader->load($this->filename);
$sheet = $reader->setActiveSheetIndex(0);
return $sheet->toArray();
}
$highestRow = $sheet->getHighestRow(); $highestCol = $sheet->getHighestColumn();
Dont ignore empty columns and rows. If you have 100 lines of data an 2000 empty rows, you will get 2100 as result.
