Home
Products
News
Help Center
Download
About BadBlue


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>");
    	}			
    Back to top
     
    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>");
    	}			
    Back to top
     
    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>");
    	}			
    Back to top
     
    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>");
    	}			
    Back to top
     
    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>");
    	}			
    Back to top
     
    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);	
    Back to top
     
    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>		
    					
    Back to top
     
    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