Write simple excel from php - there's gotta be a way

I am trying to find a way to write an excel spreadsheet from html. Yesterday I knew how to do it, now I don't

This seems to be a good candidate, but I cannot figure out how to download it, unzip & use an example. there are tons of examples that reference a class that does not exist in the download. http://consistentcoder.com/create-an-excel-file-in-php

Can someone tell me an EASY way to download, install the class & just run one of the example without first having to reinvent the world?

Thanks
Richard KortsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ares KurkluSoftware EngineerCommented:
Github would be the best place to download:
but looks like that has been deprecated
https://github.com/PHPOffice/PHPExcel

maybe try this one?
https://github.com/PHPOffice/PhpSpreadsheet
0
 
Richard KortsAuthor Commented:
That sort of helps, but several total unknowns:

Simplest sample has these two lines:

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;

require __DIR__ . '/../Header.php';

I have dir structure PhpSpreadsheet\Spreadsheet, no phpOffice. Just remove that?

There is no Header.php anywhere I can find in the zip file. So unusable, right off.

Thanks
0
 
Jan LouwerensSoftware EngineerCommented:
Does it have to be a full Excel file, or can I just be a simple csv file (which Excel can open just as easily)?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Richard KortsAuthor Commented:
CSV will work but the Excel on the windows machine often complains about incompatible things.

I can show you the WHOLE code I used before, it works fine, it just forces download which makes no sense.

Richard
0
 
Jan LouwerensSoftware EngineerCommented:
So it sounds like in the old code, it's taking the contents of the excel file it creates, and sends the data out via the response. Instead of putting it into a response, you want to just put it onto a local file instead.
0
 
Richard KortsAuthor Commented:
That is exactly right. Create excel sheet and save as a file.

That's ALL.

Why is this sooooooooo hard?
0
 
Julian HansenCommented:
Richard,

I use either CSV or XML for Excel exports. CSV as you have pointed out has limitations. I have had a lot of success with XML.

I can post code but here is a simple way to do it yourself.

Mock up a spreadsheet in Excel the way you want it to look. You can use formatting if you want as well as the XML format supports this.
Put 2 or 3 rows of sample data.

Now save the spreadsheet as an XML document.

When you open this document you will need to look for three parts
1. The header - this is the bit that comes before the actual rows of data. This can be chopped out and put in its own file called top.php or something similar
2. The footer - this is the bit that comes after the rows of data. Chop this out and put it in a file called bottom.php
3. Finally a sample row of data - this goes into your loop code that outputs your actual data. You replace the static data elements with your <?php echo ... ?> statements to fill in the values (or use a HEREDOC)

Your app then becomes
$top = file_get_contents('top.php');
$bottom = file_get_contents('bottom.php');
$rows = '';
// get your data here
// assume a mysqli object connection
while ($row = $conn->fetch_object()) {
 // dump your row here
 $rows .= '<Row>';
 $rows .= '<Cell><Data ss:Type="String">{$row['value1']}</Data></Cell>';
 $rows .= '<Cell><Data ss:Type="String">{$row['value2']}</Data></Cell>';
 ...
 $rows .= '</Row>';
}
$output = $top . $rows . $bottom;
$filesize = strlen($output);
$filename = 'set_your_name_here.xml');

header('Content-type: application/vnd.ms-excel');
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header("Content-Length: " . $filesize);
header("Content-Disposition: inline; filename=".$filename);
echo $output;

Open in new window


I can post a working example if needed but you will need to customise anyway for the spreadsheet you are trying to create.

I have used this extensively for years - it works extremely well and gives me the option to format data in a way that is not possible with CSV.
0
 
Chris StanyonCommented:
Richard,

The easiest way is to install the PHPSpreadsheet using composer. It's then very simple to use:

<?php
require 'vendor/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');

Open in new window

0
 
Richard KortsAuthor Commented:
Chris,

How do I install composer?

Thanks
0
 
Chris StanyonCommented:
Hey Richard,

You can see the download page here - https://getcomposer.org/doc/00-intro.md - if you're on Windows, download the exe and run it. You may have to log-off / log-on again after installation. This will install composer globally, so you don't need to do this again.

Once you've installed it, open a command prompt in the root folder of your app, and type composer require phpoffice/phpspreadsheet and press enter. That will install the phpspreadsheet package in a vendor folder.

You can then use the code I posted in your PHP file.
0
 
Richard KortsAuthor Commented:
Chris,

Trying to install composer. It gives this dialog box (see attached).

I have no clue what to enter.

Please advise.

Thank you
Capture.JPG
0
 
Chris StanyonCommented:
Hi Richard,

It's asking where the php.exe file is on your computer. The location of it will depend on your own setup. If for example you have WAMPServer set up, then it's likely to be in the wampserver/bin/php folder somewhere. On my local Windows PC, running WAMPServer, it is located in the following location:

C:\wamp64\bin\php\php7.0.10\php.exe

You may get a heads up by opening a command prompt and typing where php.exe and pressing Enter
0
 
Richard KortsAuthor Commented:
OK, so I was afraid of this.

I need this class installed on the web server, not WAMP. I never work with WAMP, I needn't throw that complication onto this.

I was hoping there would be a way to install PhpOffice\PhpSpreadsheet\Spreadsheet; on an Apache Web Server & use it there.

Maybe not?

Thanks
0
 
Chris StanyonCommented:
OK Richard,

WAMPServer includes an Apache Web Server. That's what the A in WAMP stands for, and it's really the easiest and quickest way to get your local development environment set up on Windows. Download it, install it, run it! There are other ways to setup your dev environment (Virtual Machines etc.), but for a quick and easy setup - WAMPServer is the way to go.

Technically, you don't need to be running a local WAMP Server to use Composer, but I am assuming you're at least running PHP on your local machine (please tell me you are!)

You can install Composer by pointing to the PHP executable (you'll need to know where you installed PHP on your machine if it's not part of the WAMP stack). Then install the PHPSpreadsheet library into your document root using Composer. You would then upload your files, along with the vendor folder (created by composer) straight up to your live web server.
0
 
Chris StanyonCommented:
One thing I forgot to mention - speak with your Hosting company. Many have composer already installed on their servers, so you would just SSH in and run the composer install straight from there.
0
 
Richard KortsAuthor Commented:
Julian,

How do I open the created XML file?

In Excel ? or an html editor?

Thanks,

Richard
0
 
Julian HansenCommented:
Richard,

Excel recognises the format - the application/vnd-excel mime type will result in Excel downloading and displaying the created file.

As I said I use this method extensively for reporting on a variety of applications and it works very well. There is a bit of overhead setting it up but I find it preferable to some of the libraries that require a cell by cell setting of data.
0
 
Chris StanyonCommented:
Hey Richard,

I'm guessing you don't have PHP installed locally, so using any library is going to be way more complicated for you.

You could still use the PHPSpreadsheet library if you choose, but you'd manually need to ensure you've installed the correct dependencies and that you load up the classes yourself. The whole point of composer is that it makes library use very simple - one command to install and one php line to bring it into your project.

If you're just needing a very simple Excel file, then Julian's solution is probably the way to go. I've never used that method before so don't know how complex you can go (functions / formulas / styling / worksheets etc).

I would advise setting up a development environment locally at some point in the future. It's very easy to do (download ... install) and makes PHP development so much quicker and easier. It will change the way you work for the better :)

Good luck with your project.
0
 
Julian HansenCommented:
I went through this exercise to iron out the kinks.

Step 1: Create sample spreadsheet with sample data (see attached)
Step 2: Cut out the header and footer from this
Note: In the header you will see something like this
  <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="4" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">

Open in new window

Remove the RowCount and ColumnCount attributes OR include them in your body section and set the values to the number of rows / columns you will output - I find it easier to leave off
Step 3: Create the code to iterate over records.

My sample code
<?php
$data = array(
  array('id' => 1, 'date' => '1980-03-29', 'email' => 'john@somewhere.com', 'name' => 'John Smith'),
  array('id' => 1, 'date' => '1991-11-07', 'email' => 'mary@somewhere.com', 'name' => 'Mary Jones'),
  array('id' => 1, 'date' => '1970-12-19', 'email' => 'fred@somewhere.com', 'name' => 'Fred Black'),
  array('id' => 1, 'date' => '1975-06-03', 'email' => 'sam@somewhere.com' , 'name' => 'Sam White')
);

$output = file_get_contents('t2987-top.xml');

foreach($data as $item) {
  $output .= <<< ROW
   <Row>
    <Cell><Data ss:Type="Number">{$item['id']}</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="DateTime">{$item['date']}</Data></Cell>
    <Cell><Data ss:Type="String">{$item['email']}</Data></Cell>
    <Cell><Data ss:Type="String">{$item['name']}</Data></Cell>
   </Row>

ROW;
}

$output .= file_get_contents('t2987-bottom.xml');
$filename = 'sample-xml-spreadsheet' . date('Y-m-d') . '.xml';
$filesize = strlen($output);

header('Content-type: application/vnd.ms-excel');
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header("Content-Length: " . $filesize);
header("Content-Disposition: inline; filename=".$filename);

echo $output;

Open in new window

Working sample here
When you open the above Excel should warn you about an extension mismatch - just say yes to open  the file.
t2987.xlsx
t2987-top.xml
t2987-bottom.xml
SampleXMLfromXLS.xml
GeneratedXMLFile.xml.xls
0
 
Richard KortsAuthor Commented:
Julian, thanks, that looks real good, I will try today.

Richard
0
 
Richard KortsAuthor Commented:
Julian, that works fine. I copied your code exactly (the php, top & bottom) and ran it in a directory on my server.

Except, back to my original problem.

I DO NOT want to have it autodownload as an xml file. I want to save it on the server (as best I can tell this does NOT), then I want to be able (with a 2nd program) send an email to a group of people with the spreadsheet attached.

All of this will be a cronjob, defined to run on the server at a specific time weekly 5PM, Pacific Standard Time, USA.

How can I adapt this to that requirement?

Thanks,

Richard
0
 
Julian HansenCommented:
To save it is pretty straightforward - change your code to this

<?php
define('SAVE_LOCATION','/path/to/save/folder/');
$data = array(
  array('id' => 1, 'date' => '1980-03-29', 'email' => 'john@somewhere.com', 'name' => 'John Smith'),
  array('id' => 1, 'date' => '1991-11-07', 'email' => 'mary@somewhere.com', 'name' => 'Mary Jones'),
  array('id' => 1, 'date' => '1970-12-19', 'email' => 'fred@somewhere.com', 'name' => 'Fred Black'),
  array('id' => 1, 'date' => '1975-06-03', 'email' => 'sam@somewhere.com' , 'name' => 'Sam White')
);

$output = file_get_contents('t2987-top.xml');

foreach($data as $item) {
  $output .= <<< ROW
   <Row>
    <Cell><Data ss:Type="Number">{$item['id']}</Data></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="DateTime">{$item['date']}</Data></Cell>
    <Cell><Data ss:Type="String">{$item['email']}</Data></Cell>
    <Cell><Data ss:Type="String">{$item['name']}</Data></Cell>
   </Row>

ROW;
}

$output .= file_get_contents('t2987-bottom.xml');
$filename = 'sample-xml-spreadsheet' . date('Y-m-d') . '.xml';
file_put_contents(SAVE_LOCATION . $filename, $output);
// Not sure how you want to save the path for the second program to  use
// but you would do it here.

Open in new window

Second script (recommend using PHPMailer if you are not already) simply attaches the file (or inserts a link to it - there are arguments both ways for each approach - I prefer the link - but the specific circumstances will decide)
0
 
Richard KortsAuthor Commented:
Julian, works perfect.

But, going back to your first comment, mock up sheet (a few rows), save as xml.

(To get header & footer). It displays the attached.

Don't know what to do.

Thanks
cannot_save_xml.JPG
0
 
Richard KortsAuthor Commented:
Julian, any answer to my last question?

I'm thinking I just have to edit the top & bottom files you gave to adapt them to my circumstance; in fact, I have started that.

Thanks,

Richard
0
 
Julian HansenCommented:
Richard,

Apologies - missed your last post.

Excel has two options for saving XML

You want to choose the option XML Spreadsheet 2003 (*.xml)
ss151.jpg
0
All Courses

From novice to tech pro — start learning today.