Export File Data to Excel (PHP)

Description: This WebSmart PHP example exports a DB2 file to a Microsoft Excel file when the user clicks the 'Export to Excel' button.
Note: Microsoft Excel 2003 or later will be required to view the generated spreadsheet.

The column spacing and header text in the exported file can be specified in the HTML segment 'xlsHeader'. The Excel file includes an additional column, a formula field based on the unit price.

Order Number	Inventory Item Code	Order Quantity	Unit Price
100026	119	8	77.55
100026	120	5	7.56
100026	121	8	32.77
100026	411	1	1450.00
100026	118	3	30.78
100026	404	5	115.00
100026	405	51	49.50
100026	100	20	10.61
100026	97	20	2.04
100026	102	20	10.36
100026	211	2	109.50
100026	212	2	106.92
100026	21	24	2000.00

Create a new program using the PHP iSeries SQL 'Record Listing' template.

On the Additional Design Options window accept the defaults.

Add the file XL_WEBDEMO/MU_ORDDF and choose the fields Order Number, Inventory Item Code, Order Quantity and Unit Price.

Add the following code under '<div id="divider">' in the HTML segment 'ListHeader':

     <form action="phptoexcel.php" method="get">
      <input type="hidden" name="task" value="toexcel">
      <table>
        <tr>
          <td>File Name: </td>
          <td> </td>
          <td><input type="text" name="xlsName" size="20" value="filename"></td>
        </tr>
        <tr>
          <td>Worksheet Title: </td>
          <td> </td>
          <td><input type="text" name="xlsTitle" size="20" value="myworksheet"></td>
        </tr>
        <tr>
          <td colspan="3"><input type="submit" value="Export to Excel"></td>
        </tr>
      </table> 
    </form>

This will submit the form to make the Excel file.

Look for the comment that reads 'Global variables should be defined here' at the top of the PHP code, and add the following global variables declarations:
```
// Global variables should be defined here
global $title;
global $authName;
global $fileName;
global $rowCount;
	
```
Add the following PHP code below the default task condition:
```
if ($pf_task == 'toexcel')
  toExcel();
	
```

Add the following function at the bottom of your PHP:

function toExcel()
{
	
	// Make all global variables available here
	foreach($GLOBALS as $arraykey=>$arrayvalue) 
	{
		global $$arraykey;
	}
	
	$query = 'select ODORD, ODITEM, ODQTY, ODPRIC from XL_WEBDEMO/MU_ORDDF';
	
	// Fetch rows for page: relative to initial cursor 
	if (!($stmt = db2_exec($db2conn, $query))) 
	{
		echo "Error ".db2_stmt_error() .":".db2_stmt_errormsg(). ""; 
		die;
	}
	
	// Output header
	wrtHeader();
	
	// Output Header XML	
	wrtseg("xlsHeader");

	
	$rowCount = 0;
	while ($row = db2_fetch_assoc($stmt))
	{
		$ODORD = $row['ODORD'];
		$ODITEM = $row['ODITEM'];
		$ODQTY = $row['ODQTY'];
		$ODPRIC = $row['ODPRIC'];
		
		$rowCount++;
		
		// Output details
		wrtseg("xlsDetails");
		
	}
	
	// closet the database connection
	db2_close($db2conn);   
	
	// Output footer
	wrtseg("xlsFooter");
	
}

Create a helper function to output the header. Do this at the bottom of the PHP code:

function wrtHeader()
{
	// Make all global variables available here
	foreach($GLOBALS as $arraykey=>$arrayvalue) 
	{
		if($arraykey[0]!='_' && $arraykey != 'GLOBALS')
			global $$arraykey;
	}
	
	// Set worksheet title
	if(isset($_REQUEST['xlsTitle']))
	{
		$title = htmlspecialchars($_REQUEST['xlsTitle']);	
	}
	else
	{
		$title = "Worksheet 1";
	}
	
	// strip out special chars first
	$title = preg_replace ("/[\\|:|\/|\?|\*|\[|\]]/", "", $title);
	
	// Substring it to the allowed length
	$title = substr ($title, 0, 31);
	
	// Set author name
	$authName = "Author's Name";	
	
	// Strip out special characters
	$authName = preg_replace ("/[\\|:|\/|\?|\*|\[|\]]/", "", $authName);
	
	// Set XLS file name
	if(isset($_REQUEST['xlsName']))
	{
		$fileName = htmlspecialchars($_REQUEST['xlsName']);	
	}
	else
	{
		$fileName = "Excel1";
	}
		
	// Set content headers
	header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
	header("Content-Disposition: inline; filename=\"" . $fileName . ".xls\"");
	header("Cache-Control: private");
	
}

Create the HTML segment 'xlsHeader' with the following XML:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
	<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
	xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns:x="urn:schemas-microsoft-com:office:excel"
	xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
	xmlns:html="http://www.w3.org/TR/REC-html40">
	<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
	<Author></Author>
	<LastAuthor></LastAuthor>
	</DocumentProperties>
	<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
	<WindowHeight>7560</WindowHeight>
	<WindowWidth>12300</WindowWidth>
	<WindowTopX>360</WindowTopX>
	<WindowTopY>135</WindowTopY>
	<ProtectStructure>False</ProtectStructure>
	<ProtectWindows>False</ProtectWindows>
	</ExcelWorkbook>
	<Styles>
	<Style ss:ID="Default" ss:Name="Normal">
	<Alignment ss:Vertical="Bottom"/>
	<Borders/>
	<Font/>
	<Interior/>
	<NumberFormat/>
	<Protection/>
	</Style>
	
	<Style ss:ID="s21">
	<NumberFormat ss:Format="Short Date"/>
	</Style>
	</Styles>
	<Worksheet ss:Name="$title">
	<Table x:FullColumns="1" x:FullRows="1">
	<Column ss:AutoFitWidth="0" ss:Width="87"/>
	<Column ss:AutoFitWidth="0" ss:Width="207"/>
	<Row>
		<Cell><Data ss:Type="String">Order #</Data></Cell>
		<Cell><Data ss:Type="String">Item #</Data></Cell>
		<Cell><Data ss:Type="String">Order Quantity</Data></Cell>
		<Cell><Data ss:Type="String">Price</Data></Cell>
		<Cell><Data ss:Type="String">Price With Tax</Data></Cell>
	</Row>

Create the HTML segment 'xlsDetails' with the following XML:

    <Row>
        <Cell><Data ss:Type="Number">$ODORD</Data></Cell>
        <Cell><Data ss:Type="String">$ODITEM</Data></Cell>
        <Cell ss:StyleID="s21"><Data ss:Type="Number">$ODQTY</Data></Cell>
        <Cell><Data ss:Type="Number">$ODPRIC</Data></Cell>
        <Cell ss:Formula="=RC[-1] *1.15"><Data ss:Type="Number"></Data></Cell>
    </Row>

Create the HTML segment 'xlsFooter' with the following XML:

    <Row>
	 <Cell ss:Index="5" ss:Formula="=SUM(R[-$rowCount]C:R[-1]C)">
	 <Data ss:Type="Number"></Data></Cell>
    </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <Selected/>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    </Workbook>

Program Definition: phptoexcel.phw

Rate This Example

Did this knowledge base article help you to achieve your goal?	Yes	No	Don't Know

Enter additional comments below. If you want to hear back from us, include your contact information.