Convert XLS to CSV stopped working now upgraded to PHP 7.X :(

PHP
1

I have been using the PHPExcel Class and it has worked fine for several months without any problems until I upgraded to PHP 7.X…

I tried downloading the updated version but there were still problems so decided to Google for alternatives.

Unsuccessfully tried:

  • PPExcel-develop
  • Php-excel-reader-2.21
  • PhpSpreadsheet-develop
  • faisalman-simple-excel-php-4a69805
  • faisalman-simple-excel-php-4a69805
  • portphp-master
  • spreadsheet-reader
  • php-excel-reader
  • spout-master

Anyone one know of a PHP class or library that works with PHP 7.X

2

There is a new version, PhpSpreadsheet.

1 Like
3

@SamA74, many thanks.

I did try that version and had problems using git clone … so downloaded the Zip file. The zipped contents worked for some XLS spreadsheets but not others. Fortunately my son sorted the download problem using Composer.

Downloaded:
8,094 items, totalling 197.3 MB

I have had enough for one day and hope to try again tomorrow and also report back.

4

I managed to install the library on the server using composer:

composer require phpoffice/phpspreadsheet

Library: 674 items, totalling 11.2 MB… seems like using a pile-driver to crack a peanut :frowning:

It is very quick and takes about three seconds to convert over 22,000 lines!

My son mentioned that Node.js may be better…

Online Demo

Source File

<?php 
  # declare(strict_types=1);
  # ini_set('display_errors', 'true');
  # error_reporting(-1);

# CONSTANTS  
  if(1):
    define('WWW_ROOT',   '/var/www/');
  else:  
    define('WWW_ROOT',   '/home/john/www/');
  endif;  
  define('ABOVE_ROOT', dirname($_SERVER['DOCUMENT_ROOT']) .'/' );

# https://github.com/PHPOffice/PhpSpreadsheet
  require WWW_ROOT .'ci2/vendor/autoload.php';
  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


  $title  = 'JB PhpOffice Create CSV';
  $forum  = 'https://www.sitepoint.com/community/t'
          . '/convert-xls-to-csv-stopped-working-now-upgraded-to-php-7-x'
          . '/290323/2';
  
  $xls  = ABOVE_ROOT .'above_root/dasa-latest.xls';
  if( isset($_GET['convert']) && 'xls_2'===$_GET['convert']):
    $xls  = ABOVE_ROOT .'above_root/dasa-arrivals.xls';
  endif;
  $csv    = str_replace('.xls', '.csv', $xls);

  # CREATE NEW CSV FILE
    if( file_exists($csv)):  
      unlink($csv);
    endif;  

  if( isset($_GET['convert']) ):
    define('START', microtime(TRUE) );
    $ok  =  convertXlsCsv($xls, $csv);
    if($ok):
      $tmp    = file_get_contents($csv);
      $tmp    = '<b>Time taken ==> ' .elapsed() .' seconds </b>'
              . '<hr>'
              . print_r($tmp, TRUE);
    endif;  
  else:
    # HIGHLIGHT FILE
      $tmp = highlight_file(__file__, TRUE);
  endif;  

$hdr = <<< ____TMP
  <!DOCTYPE HTML>
  <html lang="en-UK">
  <head>
  <title> $title </title>
  <style>
  body {background-color:#f0f0f0; color:#333;}
  pre  {background-color:snow;}
  .bdr {border:solid 1px #999;}
  .bgs {background-color;snow;}
  .flr {float:right;}
  .fss {font-size:small;}
  .mga {margin:0 auto;}
  .ooo {margin:0; padding:0;}
  .p42 {padding:0.42em;}
  .tac {text-align:center;}
  .w88 {width:88%; max-width:888px;}
  .wrp {white-space:wrap;}
  </style>
  </head>
  <body>
    <h5 class="ooo flr"> <a href="$forum"> Forum </a> </h5>  
    <h1> $title </h1>
    <hr>
    <dl class="bgs">
    <dt> \$xls ==> </dt>
      <dd> $xls </dd> 
    <dt> \$csv ==> </dt>
      <dd> $csv </dd>
      <dd> &nbsp; </dd>
    <dt class="tac"> 
      <a href="index.php?convert=xls_1">Convert xls_1</a> 
      &nbsp;&nbsp;|&nbsp;&nbsp; 
      <a href="index.php?convert=xls_2">Convert xls_2</a> 
      &nbsp;&nbsp;|&nbsp;&nbsp; 
      <a href="index.php?source">Source</a> 
    </dt>  
    <dd> &nbsp; </dd>
    <dt><pre class="w88 mga bdr p42 fss wrp"> $tmp </pre></dt>
  </dl>
____TMP;
echo trim($hdr);

echo '</body>';
echo '</html>';


// ONLY FUNCTION BELOW

//==============================================
function convertXlsCsv($xls, $csv)
:bool
{
  $result = FALSE; // DEFAULT

  $csv = str_replace('.xls', '.csv', $xls);
  try {
    # Load $inputFileName to a Spreadsheet Object
      $spreadsheet  = \PhpOffice\PhpSpreadsheet\IOFactory::load($xls);

    # WRITECSV
      $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
      $writer->setUseBOM(true);
      $writer->save($csv);
      $result = TRUE;
  }catch (Exception $e){
    fred( '$e->getMessage()', $e->getMessage() );
  }
    
  return $result;  
}#endfunc

//=============================================
function fred($val, $title=NULL)
:bool
{
  $style  = 'width:88%; margin:2em auto; padding:1em;' 
          . 'border:sold 1px #ddd; font-size:small;';
  echo '<div style="' .$style .'">';
    echo $title .' ==> ';
    echo '<hr>';
    echo '<pre>';
      print_r($val);
    echo '</pre>';
  echo '</div>';  

  return TRUE;
}

//=============================================
function elapsed($msg=NULL)
:string
{
  $result = microtime(true) - START;

  return (string) $result;
}
5

That doesn’t sound to bad to process 22K lines.

Server side JS is the hotness these days.

1 Like
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.