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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 StanyonWebDevCommented:
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 StanyonWebDevCommented:
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 StanyonWebDevCommented:
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 StanyonWebDevCommented:
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 StanyonWebDevCommented:
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 StanyonWebDevCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Richard KortsAuthor Commented:
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);

Open 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
0
Julian HansenCommented:
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);

Open in new window

sample-xml-spreadsheet2018-02-20.xml
0
Chris StanyonWebDevCommented:
RIchard,

Turn on error reporting when you're developing and it'll show any errors you have in your script. Currently your script fails silently. If you decide to setup a DevEnv, then just turn them on in your php.ini. If you prefer not to have a DevEnv, then add the following to the start of your script (just make sure you remove it before it goes live)

error_reporting(E_ALL);
ini_set('display_errors', 1);

Open in new window

Hint: You have at least one problem at line 187
0
Richard KortsAuthor Commented:
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.
0
Chris StanyonWebDevCommented:
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 ...

:)
0
Richard KortsAuthor Commented:
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
0
Chris StanyonWebDevCommented:
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 !
0
Richard KortsAuthor Commented:
What is the difference between $yourFile & $fileName? Why two?

Thanks
0
Chris StanyonWebDevCommented:
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
0
Richard KortsAuthor Commented:
Got it, I'll try that.

Thanks
0
Julian HansenCommented:
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.
1
Chris StanyonWebDevCommented:
@Julian - didn't realise you could simply chagne the extension. Makes sense if I'd thought it through. Cool :)
0
Richard KortsAuthor Commented:
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">

Open 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
0
Julian HansenCommented:
Change the line in the top file to
<Worksheet ss:Name="urpts_urpt<?php echo date('Y_m_d_H_i_s');?>">

Open 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();

Open 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.