Jump to content

Write to Excel


ShadowMage

Recommended Posts

My company has started asking for more and more Excel-based data.  We used to just print the data to a table and then copy/paste from the browser to Excel, but they're wanting auto emailers and stuff like that now.  I did a little research and it seems like PHPSpreadsheet is the way to go.  It seems to be the most up to date, and still actively supported.  Problem is, I'm not sure how to set it up and use it.  Their documentation (https://phpspreadsheet.readthedocs.io/en/latest/) is a little vague on that part.  It reads like this is supposed to be installed into a project (similar to Visual Studio's projects) but I didn't even know that was a thing with PHP.  I've only ever used PHP in a webserver environment (maybe that's the project?).  I've never used Composer so I have no clue where to even start.

So, I guess my question is, can somebody offer some assistance in setting this up and/or using it in a webserver environment?

Or, offer some alternatives to PHPSpreadsheet that are easier to use?

Thanks in advance.

Link to comment
Share on other sites

You just need to copy their files onto a folder your server. Then the code example they provide will work.
The line require 'vendor/autoload.php'; will need to have the path of the autoload.php file in your server's filesystem.

 

If you don't care about formatting or anything, you can just export your data as .csv files. It's very simple and Excel can open them. A CSV file looks like this:

Name,Age,Occupation
John,35,Carpenter
Smith,28,Doctor

A CSV file is just a file where each line is a row and commas are used to separate columns. You can build a PHP program to generate one easily. The most difficult part is escaping data. If your data has a comma, line break or quotation mark in it, it needs to be escaped. Data is escaped by wrapping it in quotes and quotation marks themselves need to be escaped by duplicating them. Here's a simple function to escape data for use in a csv file:

function csv_escape($str) {
  // Line breaks, commas and quotation marks must be wrapped in quotation marks
  if(
    stripos($str, "\r") !== false ||
    stripos($str, "\n") !== false ||
    stripos($str, '"') !== false ||
    stripos($str, ',') !== false
  ) {
    // Replace " with ""
    $str = str_replace('"', '""', $str);
    // Wrap string in quotation marks
    $str = '"' . $str . '"';
  }
  return $str;
}

 

Link to comment
Share on other sites

Actually, they're not providing the autoload file. Composer generates it. Your autoload file just needs to look something like this with the right path for the folder:

spl_autoload_register(function ($class_name) {
    include 'src/PHPSpreadsheet/' . $class_name . '.php';
});

The files you need to download would be these ones: https://github.com/PHPOffice/PhpSpreadsheet/tree/master/src/PhpSpreadsheet

Put them somewhere on your server and make sure autoload has the right path for them.

Link to comment
Share on other sites

So do I need to change the $class_name variable?  Or just make sure the "src/PHPSpreadsheet" part is pointing to the right place?

 

EDIT: Nvmind, answered my own question with a bit of research.  😁 Found a StackOverflow article that explains autoloading.  Just need to change the "src/PHPSpreadsheet" part.  Let's see if I can get this thing working now...  I might be back...

Thanks, Ingolme!!

Edited by ShadowMage
Link to comment
Share on other sites

So I've got this code:

require('PhpSpreadsheet\autoload.php');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

Which was copied directly from their documentation as a test.  However, I get this error:

PHP Warning:  include(PhpSpreadsheet/src/PhpOffice\PhpSpreadsheet\Spreadsheet.php): failed to open stream: No such file or directory

The autoload.php file looks like this:

spl_autoload_register(function ($class_name) {
    // include 'PhpSpreadsheet/src/PHPSpreadsheet/'.$class_name.'.php';
    include 'PhpSpreadsheet/src/'.$class_name.'.php';
});

I copied the files from their original folder (PHPSpreadsheet) up a level into the src folder just to eliminate the extra file path length.  However, I did try it with the original file structure when it failed.  Both ways produce the same error.  It looks to me like maybe the use lines aren't functioning correctly?  Maybe?  It looks like the autoload function is pulling in the full namespace of the class instead of just the class name.  Any ideas what's going on?

Edited by ShadowMage
Link to comment
Share on other sites

It looks like $class_name has all the namespaces attached. You'll have to put all the files in a folder named PhpOffice. To clean things up, you can use PhpOffice as the root of this system and not use src at all.

The file structure will need to be something like this:

+-PhpOffice/
  +-autoload.php
  +-PhpSpreadsheet/
    

Then, to make sure paths are always correct, you should use the __DIR__ constant in the autoload function. If this is a linux server it would also be good to replace backslashes with forward slashes.

<?pph
spl_autoload_register(function ($class_name) {
    $class_name = str_replace('\\', '/', $class_name);
    include __DIR__ . '/../' . $class_name.'.php';
});

Then your code can include PhpOffice from anywhere.

require('PhpOffice/autoload.php');
Link to comment
Share on other sites

That seems to have done the trick on getting the PHPSpreadsheet stuff to load.  But it looks like I've got some prerequisite stuff to take care of now as it's giving me an error saying it can't load psr/simple-cache which is not part of the PHPSpreadsheet library.

Thanks for all the help, Ingolme!! I'll be back if I run into more snags. 😄

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...