BadBlue PHP ShareOffice - Convert and Read Access, Excel, Word, Office files from PHP (XLS, MDB, DOC files)

PHP ShareOffice

BadBlue Help Center >> PHP FAQ BadBlue Discussion Email support

Need to read Office files using PHP? ShareOffice lets you:

read an entire Excel spreadsheet in a single function call!

read an Access table or query in a single call!

read an entire Word document into a string in a single call!

update an Excel cell with a single call!

ShareOffice is a source-code library - written in PHP - that is included free with BadBlue Personal Edition (free download at left) and BadBlue Enterprise Edition. ShareOffice lets you instantly read Office spreadsheets and databases with a single function call... even if they're on a different computer. By installing BadBlue, you'll give your PHP scripts the instant ability to grab, interpret and analyze Office data either on your local PC or even remotely. *

To get started, make sure you're familiar with the Excel Sharing help page which describes how to set up an Excel spreadsheet for sharing. The following section describes how to call the ShareOffice functions from PHP and provides some examples that illustrate typical usage. The functions are:

SOInit - Initialize ShareOffice connection to local or remote PC

SOExcel - Read Excel spreadsheet data from local or remote PC into an array

SOAccess - Read Access table or query from local or remote PC into an array

SOExcelUpdate - Update Excel spreadsheet cell on local or remote PC

SOWord - Read Word file as HTML on local or remote PC into a string variable

SOHTTPGet - Retrieve a web page (even if it's password-protected)

Complete ShareOffice example

SOInit - Initialize ShareOffice

Description: initializes communications with a BadBlue server so that subsequent ShareOffice calls can be made.

Input: $sAddr

Address of BadBlue server (e.g., "127.0.0.1:8080").

Output: $errmsg

Empty if no error occurred, otherwise error message

Example:

	//	Initialize.
	//
	$errmsg = SOInit("127.0.0.1:8081");
	if (strlen($errmsg)) {
		echo("<b>Initialization Error</b>: ".$errmsg."<br>");
	}

SOExcel - Read an Excel spreadsheet

Description: reads Excel spreadsheet data from a local or remote BadBlue server; data is read into an associative array so that cell data can be easily referenced by location (e.g., cell "D2").

Input: $sAddr

address of BadBlue server (e.g., "127.0.0.1:8080")

Input: $sPath

path of shared file in EXT.INI file (e.g., "path3")

Input: $sFile

name of Excel file to examine (e.g., "invoice.xls")

Input: $nSheet

sheet number (e.g., 1)

Input: $aData

associative array returned with data (passed by reference)

Input: $sCellStart

starting cell (top left cell) of area to retrieve (e.g., "A1")

Input: $sCellEnd

ending cell (bottom right cell) of area to retrieve (e.g., "G99")

Input: $sUser

(optional) user-name to get access to file

Input: $sPassword

(optional) password to get access to file

Output: $errmsg

Empty if no error occurred, otherwise error message

Example:

	//	Grab the test Excel data.
	//
	$errmsg = SOExcel(
		"127.0.0.1:8081", "path9", "CompanyTotalRevenue_2002.xls", 1,
		$arrayExcelData, "A1", "K35", "percy", "percy"
	);
	if (strlen($errmsg)) {
		echo("<b>SOExcel Error</b>: ".$errmsg."<br>");
	} else {
		echo("Cell  A3: ".$arrayExcelData["A3"]."<BR>");
		echo("Cell D17: ".$arrayExcelData["D17"]."<BR>");
	}

SOAccess - Read an Access table or query

Description: reads an Access table or query from a local or remote BadBlue server; data is read into an associative array so that cell data can be easily referenced by row and field (e.g., $tabledata[12]["lastname"] ).

Input: $sAddr

address of BadBlue server (e.g., "127.0.0.1:8080")

Input: $sPath

path of shared file in EXT.INI file (e.g., "path3")

Input: $sFile

name of Excel file to examine (e.g., "invoice.xls")

Input: $sTable

name of table (or query) to retrieve

Input: $aData

array returned with data (passed by reference)

Input: $nRowStart

numeric zero-based row to start retrieving

Input: $nRows

number of rows to retrieve

Input: $sUser

(optional) user-name to get access to file

Input: $sPassword

(optional) password to get access to file

Output: $errmsg

Empty if no error occurred, otherwise error message

Example:

	//	Grab the Access data.
	//
	$errmsg = SOAccess("127.0.0.1:8081", "path11", "mlb2000.mdb", 
		"Batters 200 Hits", $arrayAccessData, 0, 100,
		"pattabler", "bluejay"
	);
	if (strlen($errmsg)) {
		echo("<b>SOAccess Error</b>: ".$errmsg."<br>");
	} else {
		echo("Row 3 Firstname: ".$arrayAccessData[3]["firstname"]."<BR>");
		echo("Row 9 Year: ".$arrayAccessData[9]["year"]."<BR>");
		echo("Row 21 Expr1: ".$arrayAccessData[21]["expr1"]."<BR>");
		echo("Row 99 lastname: ".$arrayAccessData[99]["lastname"]."<BR>");
		echo("Row 99 hr: ".$arrayAccessData[99]["hr"]."<BR>");
		echo("Row 99 rbi: ".$arrayAccessData[99]["rbi"]."<BR>");
	}

SOExcelUpdate - Update a cell in an Excel shared workbook

Description: updates a cell value in an Excel shared workbook; all formula settings and recalculations will automatically occur. Can be used in conjunction with the SOExcel function to set, then read recalculated Excel data. The spreadsheet should be an Excel shared workbook and the authenticated user should have "update" permission on the spreadsheet.

Input: $sAddr

address of BadBlue server (e.g., "127.0.0.1:8080")

Input: $sPath

path of shared file in EXT.INI file (e.g., "path3")

Input: $sFile

name of Excel file to examine (e.g., "invoice.xls")

Input: $nSheet

sheet number (e.g., 1)

Input: $sUpdateCell

cell of area to retrieve (e.g., "A1")

Input: $sUpdateType

data type of cell being updated (default is "S")

I2 = short-int	I4 = long int	R4 = real
R8 = float	C = currency	D = date
B = boolean	E = empty	S = string

Input: $sUser

(optional) user-name to get access to file

Input: $sPassword

(optional) password to get access to file

Output: $errmsg

Empty if no error occurred, otherwise error message

Example:

	//	Update cell F3 with new date value.
	//
	$errmsg = SOExcelUpdate("127.0.0.1:8081", "path9",
		"CompanyTotalRevenue.xls", 1,
		"F3", "1-14-2002", "D", "percy", "percy");
	if (strlen($errmsg)) {
		echo("<b>SOExcelUpdate Error</b>: ".$errmsg."<br>");
	} else {
		echo("No error reported updating Excel<br>");
	}

SOWord - Read a Word document as HTML

Description: reads a Word document from a local or remote BadBlue server; data is read into a string variable and contains the HTML equivalent of the document text.

Input: $sAddr

address of BadBlue server (e.g., "127.0.0.1:8080")

Input: $sPath

path of shared file in EXT.INI file (e.g., "path3")

Input: $sFile

name of Word document to examine (e.g., "memo.doc")

Input: $sPage

string variable returned with data (passed by reference)

Input: $sUser

(optional) user-name to get access to file

Input: $sPassword

(optional) password to get access to file

Output: $errmsg

Empty if no error occurred, otherwise error message

Example:

	//	Grab the test Word data.
	//
	$sPage = "";
	$errmsg = SOWord(
		"127.0.0.1:7777", "path3", "momentum-fischer-pumps.doc",
		$sPage, "percy", "percy"
	);
	if (strlen($errmsg)) {
		echo("<b>SOWord Error</b>: ".$errmsg."<br>");
	} else {
		echo("No error reported reading Word data<br>");
	}

SOHTTPGet - Retrieve the contents of a web page

Description: retrieves a web page and supports optional specification of a user-name and password if the page is password-protected.

Input: $sURL

full URL of page to retrieve

Input: $sPage

returned page if successful

Input: $sUser

(optional) user-name to get access to file

Input: $sPassword

(optional) password to get access to file

Output: $errmsg

Empty if no error occurred, otherwise error message

Example:

	// Retrieve page...
	//
	$sURL = "http://badblue.com/helpphp.htm";
	$errmsg = SOHTTPGet($sURL, &$sPage);
	if (strlen($errmsg)) {
		echo("Unable to retrieve ".$sURL.", reason: ".$errmsg."<BR>");
		break;
	}
	echo("Contents of page... ".$sPage);

Example


<html><body>
<?php

include("SOaccess.php");
include("SOexcel.php");
include("SOexcelu.php");
include("SOinit.php");

$aAccessData = array();
$aExcelData  = array();
do {

	// Initialize.
	//
	$errmsg = SOInit("127.0.0.1:8081");
	if (strlen($errmsg)) {
		echo("<b>Initialization Error</b>: ".$errmsg."<br>");
	}

	//	Fetch Excel data.
	//
	$errmsg = SOExcel(
		"127.0.0.1:8081", "path9", "CompanyTotalRevenue.xls", 1,
		$aExcelData, "A1", "K35", "percy", "percy"
	);
	if (strlen($errmsg)) {
		echo("<b>SOExcel Error</b>: ".$errmsg."<br>");
	} else {
		echo("Cell  A3: ".$aExcelData["A3"]."<BR>");
		echo("Cell D17: ".$aExcelData["D17"]."<BR>");
	}

	//	Update Excel data.
	//
	$errmsg = SOExcelUpdate("127.0.0.1:8081", "path9",
		"CompanyTotalRevenue.xls", 1,
		"F3", "1-14-2002", "D", "percy", "percy");
	if (strlen($errmsg)) {
		echo("<b>SOExcelUpdate Error</b>: ".$errmsg."<br>");
	} else {
		echo("No error reported updating Excel<br>");
	}

	//	Fetch Access data.
	//
	$errmsg = SOAccess("127.0.0.1:8081", "path11", "mlb2000.mdb", 
		"Batters 200 Hits", $aAccessData, 0, 100,
		"pattabler", "bluejay"
	);
	if (strlen($errmsg)) {
		echo("<b>SOAccess Error</b>: ".$errmsg."<br>");
	} else {
		echo("Row 3 Firstname: ".$aAccessData[3]["firstname"]."<BR>");
		echo("Row 9 Year: ".$aAccessData[9]["year"]."<BR>");
		echo("Row 21 Expr1: ".$aAccessData[21]["expr1"]."<BR>");
		echo("Row 99 lastname: ".$aAccessData[99]["lastname"]."<BR>");
		echo("Row 99 hr: ".$aAccessData[99]["hr"]."<BR>");
		echo("Row 99 rbi: ".$aAccessData[99]["rbi"]."<BR>");
	}

	//	Grab the test Word data.
	//
	$sPage = "";
	$errmsg = SOWord(
		"127.0.0.1:8081", "path3", "momentum-fischer-pumps.doc",
		$sPage, "percy", "percy"
	);
	if (strlen($errmsg)) {
		echo("<b>SOWord Error</b>: ".$errmsg."<br>");
	} else {
		echo("Word data: ".$sPage."<BR>");
	}

} while (0);

?>
</body></html>

Notes: The EXT.INI file is a configuration file used by BadBlue. It is located in the BadBlue installation folder, usually \program files\badblue\pe (Personal Edition) or \program files\badblue\ee (Enterprise Edition). When you share a file using the menus in BadBlue, the shared path is numbered and saved in the EXT.INI file. Open the EXT.INI file (e.g., with Notepad) to determine the path number (e.g, path2) of your file. The shared paths are saved in the shared section. For example:


[shared]
path1=C:\My Pictures\*.*
path2=C:\My Documents\Spreadsheets\*.*
path3=C:\My Documents\Database Invoices\*.*

* Some ShareOffice features require BadBlue Enterprise Edition. The ShareOffice library can run on any PHP-capable web server (not just BadBlue). The PC that contains the Excel or Access data must be running a copy of BadBlue.

BadBlue Help Center >> PHP FAQ BadBlue Discussion Email support