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

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

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

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)?

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

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.

That is exactly right. Create excel sheet and save as a file.

That's ALL.

Why is this sooooooooo hard?

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;

Select allOpen 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.

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');

Select allOpen in new window

Chris,

How do I install composer?

Thanks

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.

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

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

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

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.

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.

Julian,

How do I open the created XML file?

In Excel ? or an html editor?

Thanks,

Richard

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.

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.

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">

Select allOpen 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;

Select allOpen 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

Julian, thanks, that looks real good, I will try today.

Richard

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

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

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

Richard,

Apologies - missed your last post.

Excel has two options for saving XML

You want to choose the option XML Spreadsheet 2003 (*.xml)

Julian,

Still CANNOT get it to work.

Here is my full code; of course (sorry) more complex than your simple example.

<?php
define('SAVE_LOCATION','urpts/');
function date_conv($x) {
	$d = explode("-", substr($x,0,10));
	$r = $d[1] . "/" . $d[2] . "/" . $d[0] . " " . substr($x,11,5);
	return $r;	
}
session_start();
include "db_connect_nb.php";
// get prior saturday
$psat = "2018-02-17 00:00:00";
$currfri = "2018-02-23 17:00:00";
$qry = "Select * from cvisitor where ruid <> 0 and last_time >= '" . $psat . "' and last_time <= '" . $currfri .  "' order by last_time";
$res = mysqli_query($link, $qry);
// get registered user info
$ncv = mysqli_num_rows($res);
//echo "cvqry = " . $qry . "<br>";
//echo "ncv = " . $ncv . "<br>";

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

 for ($i = 0; $i < $ncv; $i++) {
	$cv = mysqli_fetch_array($res,MYSQLI_ASSOC);
	$lakos = strpos($cv['email'], "@lakos.com");
	if (! $lakos) {
	// get options / addons str
	$oastr = "";
	if ($cv['pepump'] == "Y") {
		$oastr = "PE Pump";
	}	
	if ($cv['pumprepair'] == "Y") {
		if ($oastr == "") {
			$oastr = "Pump Repair";
		} else {	
			$oastr = $oastr . ", " . "Pump Repair";
		}	
	}	
	if ($cv['valve_kit'] != "") {
		if ($oastr == "") {
			$oastr = $cv['valve_kit'] . "valve kit";
		} else {	
			$oastr = $oastr . ", " . $cv['valve_kit'] . "valve kit";
		}	
	}
	if ($cv['dec_contact'] == "Y") {
		if ($oastr == "") {
			$oastr = "DEC Contact";
		} else {
			$oastr = $oastr . ", " . "DEC Contact";
		}	
	}	
	if ($cv['multi'] == "Y") {
		if ($oastr == "") {
			$oastr = "Multi Tower";
		} else {
			$oastr = $oastr . ", " . "Multi Tower";
		}	
	}	
	if ($cv['replbags'] != 0) {
		if ($oastr == "") {
			$oastr = $cv['replbags'] . " repl bags";
		} else {
			$oastr = $oastr . ", " . $cv['replbags'] . " repl bags";
		}	
	}
	if ($cv['strainer'] != "")  {
		if ($oastr == "") {
			$oastr = "Strainer";
		} else {
			$oastr = $oastr . ", " . "Strainer";
		}	
	}
		// full stream opts, if any
	if ($cv['cltype'] == "F") {
		$qryfso = "SELECT * from fs_opts where vid = " . $cv['vid'];
		$resfso = mysqli_query($link, $qryfso);
		$nfso = mysqli_num_rows($resfso);
		if ($nfso != 0) {
			$fso = mysqli_fetch_array($resfso,MYSQLI_ASSOC); 
			if ($fso['lowprosep'] == "Y") {
				if ($oastr == "") {
					$oastr = "Low Pro Separator";
				} else {
					$oastr = $oastr . ", " . "Low Pro Separator";
				}	
			}	
			if ($fso['SRI'] == "Y") {
				if ($oastr == "") {
					$oastr = "SRI";
				} else {
					$oastr = $oastr . ", " . "SRI";
				}	
			}	
			if ($fso['PGK'] == "Y") {
				if ($oastr == "") {
					$oastr = "Pressure Gauge Kit";
				} else {
					$oastr = $oastr . ", " . "Pressure Gauge Kit";
				}	
			}
			if ($fso['WOF'] == "Y") {
				if ($oastr == "") {
					$oastr = "Weld on Flanges";
				} else {
					$oastr = $oastr . ", " . "Weld on Flanges";
				}	
			}
			if ($fso['Fspool'] == "Y") {
				if ($oastr == "") {
					$oastr = "Flanged Spools";
				} else {
					$oastr = $oastr . ", " . "Flanged Spools";
				}	
			}
			if ($fso['GCS'] == "Y") {
				if ($oastr == "") {
					$oastr = "Grooved Coupling Spools";
				} else {
					$oastr = $oastr . ", " . "Grooved Coupling Spools";
				}	
			}
			if ($fso['GRCoup'] == "Y") {
				if ($oastr == "") {
					$oastr = "Grooved Couplings";
				} else {
					$oastr = $oastr . ", " . "Grooved Couplings";
				}	
			}
		}	
	}
// b_hydro & sand_opts	
if ($_SESSION['group'] == "sand") {
	$qryso = "SELECT * from sand_opts where vid = " . $_SESSION['vid'];
	$resso = mysqli_query($link, $qryso);
	$nso = mysqli_num_rows($resso);
	if ($nso != 0) {
		$so = mysqli_fetch_array($resso,MYSQLI_ASSOC);
		if ($so['bkwash'] != "") {
			if ($oastr == "") {
				$oastr = "Backwash: " . $so['bkwash'];
			} else {
				$oastr = $oastr . ", " . "Backwash: " . $so['bkwash'];
			}	
		}
		if ($so['material'] != "") {
			if ($so['material'] == "C") {
				$matl = "Carbon Steel";
			} else {
				$matl = "Stainless Steel";
			}	
			if ($oastr == "") {
				$oastr = "Material: " . $matl;
			} else {
				$oastr = $oastr . ", " . "Material: " . $matl;
			}	
		}
		if ($so['bkwhtank'] != "") {
			if ($oastr == "") {
				$oastr = $so['bkwhtank'];
			} else {
				$oastr = $oastr . ", " . $so['bkwhtank'];
			}	
		}	
		if ($so['hlctrls'] != "") {
			if ($oastr == "") {
				$oastr = $so['hlctrls'];
			} else {
				$oastr = $oastr . ", " . $so['hlctrls'];
			}	
		}
	}
}	
	$ruid = $cv['ruid'];
	$qryru = "SELECT * from registered_users where ix = " . $ruid;
	$resru = mysqli_query($link, $qryru); 
	$ru = mysqli_fetch_array($resru,MYSQLI_ASSOC); 
	if ($ru['state'] == "") {
		$stctry = $ru['country'];
	} else {
		$stctry = $ru['state'] . "/" . $ru['country'];
	}	
	$dttime = date_conv(substr($cv['last_time'],0,10)) . substr($cv['last_time'],11,8);
	$namestr = $ru['firstname'] . " " . $ru['lastname'];
	$affil = $ru['affilliation'];
	$projname = $cv['proj_name'];
	$model = $cv['model'];
	$price = "$" . $number_format($cv['net_price'],2);
	$purge_meth = $cv['purge_method']; 
	$voltage = $cv['voltage'];
  $output .= <<< ROW
   <Row>
    <Cell><Data ss:Type="String">$namestr</Data></Cell>
    <Cell><Data ss:Type="String">$affil</Data></Cell>
    <Cell><Data ss:Type="String">$stctry</Data></Cell>
	<Cell><Data ss:Type="String">$dttime</Data></Cell>
	<Cell><Data ss:Type="String">$projname</Data></Cell>
	<Cell><Data ss:Type="String">$model</Data></Cell>
	<Cell><Data ss:Type="String">$price</Data></Cell>
	<Cell><Data ss:Type="String">$purge_meth</Data></Cell>
	<Cell><Data ss:Type="String">$voltage</Data></Cell>
	<Cell><Data ss:Type="String">$oastr</Data></Cell>
   </Row>

ROW;
}
}
$output .= file_get_contents('urpts-bottom.xml');
$filename = 'sample-xml-spreadsheet' . date('Y-m-d') . '.xml';
$fullpath = SAVE_LOCATION . $filename;
echo "fullpath = " . $fullpath . "<br>";
file_put_contents($fullpath, $output);

Select allOpen in new window

Does not save file; does not echo fullpath at the end.

Ran source code through http://phpcodechecker.com/. No issues.

Can you tell what's wrong?

If  you need to see urpts-top.xml & urpts-bottom.xml, they are attached.

Thanks
urpts-bottom.xml
urpts-top.xml

Richard,

I suspect it has something to do with your if logic failing. I stripped out the db code and the if statements and put in some dummy data for the loop (see code below) and it produced the attached file

<?php
define('SAVE_LOCATION','urpts/');
function date_conv($x) {
  $d = explode("-", substr($x,0,10));
  $r = $d[1] . "/" . $d[2] . "/" . $d[0] . " " . substr($x,11,5);
  return $r;  
}
session_start();
//include "db_connect_nb.php";
// get prior saturday
$psat = "2018-02-17 00:00:00";
$currfri = "2018-02-23 17:00:00";
//$qry = "Select * from cvisitor where ruid <> 0 and last_time >= '" . $psat . "' and last_time <= '" . $currfri .  "' order by last_time";
//$res = mysqli_query($link, $qry);
// get registered user info
//$ncv = mysqli_num_rows($res);
//echo "cvqry = " . $qry . "<br>";
//echo "ncv = " . $ncv . "<br>";

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

 for ($i = 0; $i < 20; $i++) {
   
//    $cv = mysqli_fetch_array($res,MYSQLI_ASSOC);
//    $lakos = strpos($cv['email'], "@lakos.com");
/*    if (! $lakos) {
    // get options / addons str
    $oastr = "";
    if ($cv['pepump'] == "Y") {
      $oastr = "PE Pump";
    }  
    if ($cv['pumprepair'] == "Y") {
      if ($oastr == "") {
        $oastr = "Pump Repair";
      } else {  
        $oastr = $oastr . ", " . "Pump Repair";
      }  
    }  
    if ($cv['valve_kit'] != "") {
      if ($oastr == "") {
        $oastr = $cv['valve_kit'] . "valve kit";
      } else {  
        $oastr = $oastr . ", " . $cv['valve_kit'] . "valve kit";
      }  
    }
    if ($cv['dec_contact'] == "Y") {
      if ($oastr == "") {
        $oastr = "DEC Contact";
      } else {
        $oastr = $oastr . ", " . "DEC Contact";
      }  
    }  
    if ($cv['multi'] == "Y") {
      if ($oastr == "") {
        $oastr = "Multi Tower";
      } else {
        $oastr = $oastr . ", " . "Multi Tower";
      }  
    }  
    if ($cv['replbags'] != 0) {
      if ($oastr == "") {
        $oastr = $cv['replbags'] . " repl bags";
      } else {
        $oastr = $oastr . ", " . $cv['replbags'] . " repl bags";
      }  
    }
    if ($cv['strainer'] != "")  {
      if ($oastr == "") {
        $oastr = "Strainer";
      } else {
        $oastr = $oastr . ", " . "Strainer";
      }  
    }
      // full stream opts, if any
    if ($cv['cltype'] == "F") {
      $qryfso = "SELECT * from fs_opts where vid = " . $cv['vid'];
      $resfso = mysqli_query($link, $qryfso);
      $nfso = mysqli_num_rows($resfso);
      if ($nfso != 0) {
        $fso = mysqli_fetch_array($resfso,MYSQLI_ASSOC); 
        if ($fso['lowprosep'] == "Y") {
          if ($oastr == "") {
            $oastr = "Low Pro Separator";
          } else {
            $oastr = $oastr . ", " . "Low Pro Separator";
          }  
        }  
        if ($fso['SRI'] == "Y") {
          if ($oastr == "") {
            $oastr = "SRI";
          } else {
            $oastr = $oastr . ", " . "SRI";
          }  
        }  
        if ($fso['PGK'] == "Y") {
          if ($oastr == "") {
            $oastr = "Pressure Gauge Kit";
          } else {
            $oastr = $oastr . ", " . "Pressure Gauge Kit";
          }  
        }
        if ($fso['WOF'] == "Y") {
          if ($oastr == "") {
            $oastr = "Weld on Flanges";
          } else {
            $oastr = $oastr . ", " . "Weld on Flanges";
          }  
        }
        if ($fso['Fspool'] == "Y") {
          if ($oastr == "") {
            $oastr = "Flanged Spools";
          } else {
            $oastr = $oastr . ", " . "Flanged Spools";
          }  
        }
        if ($fso['GCS'] == "Y") {
          if ($oastr == "") {
            $oastr = "Grooved Coupling Spools";
          } else {
            $oastr = $oastr . ", " . "Grooved Coupling Spools";
          }  
        }
        if ($fso['GRCoup'] == "Y") {
          if ($oastr == "") {
            $oastr = "Grooved Couplings";
          } else {
            $oastr = $oastr . ", " . "Grooved Couplings";
          }  
        }
      }  
    }
  // b_hydro & sand_opts  
  if ($_SESSION['group'] == "sand") {
    $qryso = "SELECT * from sand_opts where vid = " . $_SESSION['vid'];
    $resso = mysqli_query($link, $qryso);
    $nso = mysqli_num_rows($resso);
    if ($nso != 0) {
      $so = mysqli_fetch_array($resso,MYSQLI_ASSOC);
      if ($so['bkwash'] != "") {
        if ($oastr == "") {
          $oastr = "Backwash: " . $so['bkwash'];
        } else {
          $oastr = $oastr . ", " . "Backwash: " . $so['bkwash'];
        }  
      }
      if ($so['material'] != "") {
        if ($so['material'] == "C") {
          $matl = "Carbon Steel";
        } else {
          $matl = "Stainless Steel";
        }  
        if ($oastr == "") {
          $oastr = "Material: " . $matl;
        } else {
          $oastr = $oastr . ", " . "Material: " . $matl;
        }  
      }
      if ($so['bkwhtank'] != "") {
        if ($oastr == "") {
          $oastr = $so['bkwhtank'];
        } else {
          $oastr = $oastr . ", " . $so['bkwhtank'];
        }  
      }  
      if ($so['hlctrls'] != "") {
        if ($oastr == "") {
          $oastr = $so['hlctrls'];
        } else {
          $oastr = $oastr . ", " . $so['hlctrls'];
        }  
      }
    }
  }  
    $ruid = $cv['ruid'];
    $qryru = "SELECT * from registered_users where ix = " . $ruid;
    $resru = mysqli_query($link, $qryru); 
    $ru = mysqli_fetch_array($resru,MYSQLI_ASSOC); 
    if ($ru['state'] == "") {
      $stctry = $ru['country'];
    } else {
      $stctry = $ru['state'] . "/" . $ru['country'];
    }  
    $dttime = date_conv(substr($cv['last_time'],0,10)) . substr($cv['last_time'],11,8);
    $namestr = $ru['firstname'] . " " . $ru['lastname'];
    $affil = $ru['affilliation'];
    $projname = $cv['proj_name'];
    $model = $cv['model'];
    $price = "$" . $number_format($cv['net_price'],2);
    $purge_meth = $cv['purge_method']; 
    $voltage = $cv['voltage'];
*/    
    $namestr = "NAMESTR-{$i}";
    $affil = "AFFIL-{$i}";
    $stctry = "STCTRY-{$i}";
    $dttime = "DTTIME-{$i}";
    $projname = "PROJNAME-{$i}";
    $model = "MODEL-{$i}";
    $price = "PRICE-{$i}";
    $purge_meth = "PURGE_METH-{$i}";
    $voltage = "VOLTAGE-{$i}";
    $oastr = "OASTR-{$i}";
    $output .= <<< ROW
     <Row>
    <Cell><Data ss:Type="String">$namestr</Data></Cell>
    <Cell><Data ss:Type="String">$affil</Data></Cell>
    <Cell><Data ss:Type="String">$stctry</Data></Cell>
    <Cell><Data ss:Type="String">$dttime</Data></Cell>
    <Cell><Data ss:Type="String">$projname</Data></Cell>
    <Cell><Data ss:Type="String">$model</Data></Cell>
    <Cell><Data ss:Type="String">$price</Data></Cell>
    <Cell><Data ss:Type="String">$purge_meth</Data></Cell>
    <Cell><Data ss:Type="String">$voltage</Data></Cell>
    <Cell><Data ss:Type="String">$oastr</Data></Cell>
     </Row>

ROW;
//  }
}
$output .= file_get_contents('urpts-bottom.xml');
$filename = 'sample-xml-spreadsheet' . date('Y-m-d') . '.xml';
$fullpath = SAVE_LOCATION . $filename;
echo "fullpath = " . $fullpath . "<br>";
file_put_contents($fullpath, $output);

Select allOpen in new window

sample-xml-spreadsheet2018-02-20.xml

Chris, thanks, the error detection found it all. Thanks to Julian for the concept.

Don't know why I have such a mental block on using the error display, it solves almost EVERY time.

No worries Richard. Glad you got there in the end.

When you have the time and you're feeling adventurous, I really would suggest you take a few minutes to set up a very simple local development environment. The time taken to set it up will be repaid within a day or two developing - I guarantee it !!

You'll have full control over your environment, error reporting will always be on, xDebug will give you a whole load of useful information, you can easily install any of the thousands of PHP Libraries will a single command, changes to your codebase will be effective immediately ...

:)

One more thing.

I am sending the file as an attachment to an email using the class phpmailer. I store the file & path in a $_SESSION var.

It correctly sends the email, but the file is xml & it does not open (or allow it to open) as an Excel spreadsheet in Windows.

If I manually (ftp) download the xml file & open in Excel, it is perfect.

How can I make this work right using phpmailer? I don't think I can just call it xls or xlsx, can I?

Thanks

Hey Richard,

Pretty sure you can't just rename the extension. The file you're creating is an XML file so there's no getting around that. If you save the attachment from the email to your PC, can you then open it using Excel?

It's unlikely you'll be able to open the file by just clicking it in the email or double clicking it on the filesystem unless your users have associated the XML extension with Excel (think it's associated with MS Internet Explorer or Edge by default).

You could try passing in the Mime Type when you add the attachment using PHPMailer ... something like:

$mailer->AddAttachment($yourFile, $fileName, "base64", "application/vnd.ms-excel") ;

I don't know the correct Mime Type of an XML / Excel Spreadsheet, so you may have to experiment. I would have thought that it was just a simple text-based XML file, but who knows !

What is the difference between $yourFile & $fileName? Why two?

Thanks

The first argument is the path and name of the file you want to send. The second argument, if provided, is the name of the file as you want it to appear to the recipient, so:

AddAttachment("/path/to/some/file.xml", "yourData.xml", ...

This would send file.xml but when the recipient opened the email they would see the an attachment called yourData.xml

Got it, I'll try that.

Thanks

Richard,

If you name your file .xls extension Excel should open it. I did not pickup that you wanted to attach the file - the download method solves this with the content-type.

@Julian - didn't realise you could simply chagne the extension. Makes sense if I'd thought it through. Cool :)

Julian & Chris,

One more minor thing. All working great now, except the sheet name. In the "top" XML (per Julian) it says:

<Worksheet ss:Name="urpts_urpt2018-02-16_22_26">

Select allOpen in new window

Is there a way I can dynamically change the sheet name to use the current date / time; eg, like urpts_urptYYYY-mm-dd_HH_mm?

I can't put php in there. Or can I?

Thanks,

Richard

Change the line in the top file to

<Worksheet ss:Name="urpts_urpt<?php echo date('Y_m_d_H_i_s');?>">

Select allOpen in new window

Then instead of using file_get_contents(...) on the file do this

ob_start();
require_once('urpts-top.xml');
$output = ob_get_clean();

Select allOpen in new window

You can now put any PHP code you want in the header.

If you want to do the same in the footer then follow the same process.