BadBlue Java ShareOffice

Java ShareOffice

BadBlue Help Center >> PHP FAQ BadBlue Discussion Email support

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

read an entire Excel spreadsheet in a single method 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 Java - 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. Simply install BadBlue on a PC running Excel, Access or Word. Then use the simple Java ShareOffice classes to give your programs the ability to read and analyze Office data from any of the machines running BadBlue. *

How Java ShareOffice works

Java ShareOffice is a package designed to communicate with one or more BadBlue servers. Java ShareOffice can be installed on any Java development or application server. Its classes can be used by Java programs to gather data from BadBlue servers that have been configured to share Excel, Word and Access data.

Java Requirements

Java ShareOffice can be installed on any machine capable of running the Java 2 Platform Standard Edition (J2SE), the Micro (or Mobile) Edition (J2ME) or Enterprise Edition (J2EE). For Java development, BadBlue recommends an up-to-date JDK (e.g., JDK 1.4 and above).

Initial location of the ShareOffice files

After installation of BadBlue, all ShareOffice files are installed to BadBlue's main folder, usually \program files\badblue\pe (Personal Edition) or \program files\badblue\ee (Enterprise Edition). Important: the Java ShareOffice source files must be copied from the BadBlue installation folder to the Java development environment (either on the same machine or, more typically, on a separate machine that hosts the Java application server).

Where to copy the ShareOffice files

The ShareOffice library is designed to be built into a Java package and should be copied to the top level of the class path folders. For instance, if you've installed the JDK at c:\j2sdk1.4.1, create a folder called c:\j2sdk1.4.1\ShareOffice with the correct capitalization (capital 'S' and capital 'O', with the remaining characters of the folder name in lower-case).

How to build Java ShareOffice

To manually build the ShareOffice package, open up a command window and switch to the c:\j2sdkx.x\ShareOffice folder. Then use the following commands (or create a batch file with the commands) to construct the package:

javac -classpath c:\j2sdk1.4.1 HTTPGet.java

javac -classpath c:\j2sdk1.4.1 SOexcel.java

javac -classpath c:\j2sdk1.4.1 SOword.java

javac -classpath c:\j2sdk1.4.1 SOaccess.java

javac -classpath c:\j2sdk1.4.1 SOinit.java

If Java and its classpath are set correctly, the new class files should appear in the ShareOffice subfolder.

How to test Java ShareOffice

Java ShareOffice includes a small test application called SOtest.java. Create a test folder directly underneath the main J2SDK folder (e.g., c:\j2sdkx.x\demo). Copy the SOtest.java file from the main BadBlue folder to the test folder.

Important: make sure you set up BadBlue (see below for details) to share the Office documents you will need to access. You must also modify the test program for your particular configuration (which Office files to read, the paths on which they are shared, etc.).

Once the test application has been tailored to your particular environment, you will need to build the test application. To build it, open up a command-window, switch to the test folder and build the tester:

cd \j2sdk1.4.1\demo

javac -classpath c:\j2sdk1.4.1 SOtest.java

After building the tester, use the following command to run it:

java -cp c:\j2sdk1.4.1\demo;c:\j2sdk1.4.1 SOtest | more

Setting up BadBlue to share Office files

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 (you can also use Access or Word sharing).

The following sections describe how to call the ShareOffice functions from Java and provides some examples that illustrate typical usage. The major classes provided are:

SOinit - Initialize ShareOffice connection to local or remote PC

SOexcel - Read or write Excel spreadsheet data

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

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

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

Complete Java ShareOffice example

SOinit - Initialize ShareOffice

Method: Initialize sets up communications with a BadBlue server so that subsequent ShareOffice calls can be made.

Input: String sAddr

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

Output: String sErrmsg

Empty if no error occurred, otherwise error message

Example:

	//	Initialize.
	//
	SOinit init = new SOinit();
	String sErr = init.Initialize("12.39.27.42:7777");
	if (sErr.length() > 0) {
		System.out.println("Initialization failed: " + sErr);
	}

SOExcel - Read an Excel spreadsheet

Method: GetExcelData 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: String sAddr

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

Input: String sPath

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

Input: String sFile

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

Input: int nSheet

sheet number (e.g., 1)

Input: String sCellStart

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

Input: String sCellEnd

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

Input: String sUser

(optional) user-name to get access to file

Input: String sPassword

(optional) password to get access to file

Output: String[][] aasSheet

Cell [0][0] is an error message if error occurs, otherwise array is populated with specified contents of spreadsheet cells

Example:


	//	Get Excel data.
	//
	SOexcel xls = new SOexcel();
	String[][] aasSheet = xls.GetExcelData(
		"10.10.22.204:7777", "path7", "resource7.xls", 1,
		"A1", "D20", "percy", "percy"
	);
	if (aasSheet[0][0].length() > 6  &&
	    aasSheet[0][0].substring(0, 6) == "Error:") {
		System.out.println("SOexcel error: " + aasSheet[0][0] + "\r\n");
	} else {
		int x, y;
		for (y = 0; y < 20; y++) {
			for (x = 0; x < 4; x++) {
				System.out.print(aasSheet[y][x]+",");
			}
			System.out.println("...");
		}
		System.out.println(".........");
	}

SOAccess - Read an Access table or query

Method: GetAccessData 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 column number (e.g., aasTableData[12][9] ).

Input: String sAddr

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

Input: String sPath

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

Input: String sFile

name of Access file to examine (e.g., "invoices.mdb")

Input: String sTable

name of table (or query) to retrieve

Input: int nRowStart

numeric zero-based row to start retrieving

Input: int nRows

number of rows to retrieve

Input: String sUser

(optional) user-name to get access to file

Input: String sPassword

(optional) password to get access to file

Output: String[][] aasData

Cell [0][0] is an error message if error occurs, otherwise array is populated with specified contents of table or query

Example:


	//	Get Access data.
	//
	SOaccess mdb = new SOaccess();
	String[][] aasData = mdb.GetAccessData(
		"10.10.22.204:7777", "path10", "nflbet.mdb", "nflbase", 0, 50,
		"", ""
	);
	if (aasData[0][0].length() > 6  &&  aasData[0][0].substring(0, 6) == "Error:") {
		System.out.println("SOaccess Error: " + aasData[0][0] + "\r\n");
	} else {
		int x, y;
		for (y = 0; y < 50; y++) {
			for (x = 0; x < 12; x++) {
				System.out.print(aasData[y][x]+",");
			}
			System.out.println("...");
		}
		System.out.println(".........");
	}

SOExcel - Update a cell in an Excel shared workbook

Method: UpdateExcelData updates a cell value in an Excel shared workbook; all formula settings and recalculations will automatically occur. Can be used in conjunction with the GetExcelData method 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: String sAddr

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

Input: String sPath

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

Input: String sFile

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

Input: String nSheet

sheet number (e.g., 1)

Input: String sUpdateCell

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

Input: String 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: String sUser

(optional) user-name to get access to file

Input: String sPassword

(optional) password to get access to file

Output: String errmsg

Empty if no error occurred, otherwise error message

Example:

	//	Update cell F3 with new date value.
	//      Update Excel data.
	//
	SOexcel xls = new SOexcel();
	String sUpdate = xls.UpdateExcelData(
		"10.10.22.204:7777", "path7", "revenue7.xls", 1,
		"A1", "9-18-2002", "D", "percy", "percy"
	);
	System.out.println("...\r\n"+sUpdate+"\r\n...\r\n");

SOWord - Read a Word document as HTML

Method: GetWordDocument 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: String sAddr

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

Input: String sPath

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

Input: String sFile

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

Input: String sUser

(optional) user-name to get access to file

Input: String sPassword

(optional) password to get access to file

Output: String sDoc

Error message or complete contents (in HTML format) of Word document

Example:


	//	Get Word data.
	//
	SOword doc = new SOword();
	String sDoc = doc.GetWordDocument(
		"10.10.22.204:7777", "path9", "creatin1.doc", "percy", "percy"
	);
	System.out.println(sDoc);

SOHTTPGet - Retrieve the contents of a web page

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

Input: String sURL

full URL of page to retrieve

Input: String sUser

(optional) user-name to get access to file

Input: String sPassword

(optional) password to get access to file

Output: String sPage

Error message if the page could not be retrieved or the complete response (including header) from the server

Example:

	// Retrieve page...
	//
	String sURL = "http://badblue.com/helpjavo.htm";
	HTTPGet h = new HTTPGet();
	String sPage = h.Read(sURL);
	System.out.println(sPage);

Example


//	Import ShareOffice.
//
import ShareOffice.*;

//	ShareOffice tester.
//
public class SOtest {
    public static void main(String[] args) {

        //    Get Word data.
        //
        SOword doc = new SOword();
        String sErr = doc.Initialize("10.10.22.204:7777");
        if (sErr.length() > 0) {
            System.out.println("Initialization failed: " + sErr);
        }
        String sDoc = doc.GetWordDocument(
            "10.10.22.204:7777", "path9", "creatin1.doc", "percy", "percy"
        );
        System.out.println(sDoc);

        //    Get a password-protected web page.
        //
        HTTPGet h = new HTTPGet();
        String sPage = h.Read("http://10.10.22.204:7777/get/path3/dotsconn.txt",
            "percy", "percy");
        System.out.println(sPage);

        //    Update Excel data.
        //
        SOexcel xls = new SOexcel();
        String sUpdate = xls.UpdateExcelData(
            "10.10.22.204:7777", "path7", "bug7.xls", 1,
            "A1", "9-18-2002", "D", "percy", "percy"
        );
        System.out.println("...\r\n"+sUpdate+"\r\n...\r\n");

        //    Get Excel data.
        //
        String[][] aasSheet = xls.GetExcelData(
            "10.10.22.204:7777", "path7", "bug7.xls", 1,
            "A1", "D20", "percy", "percy"
        );
        if (aasSheet[0][0].length() > 6  &&  aasSheet[0][0].substring(0, 6) == "Error:") {
            System.out.println("SOexcel Error: " + aasSheet[0][0] + "\r\n");
        } else {
            int x, y;
            for (y = 0; y < 20; y++) {
                for (x = 0; x < 4; x++) {
                    System.out.print(aasSheet[y][x]+",");
                }
                System.out.println("...");
            }
            System.out.println(".........");
        }

        //    Get Access data.
        //
        SOaccess mdb = new SOaccess();
        String[][] aasData = mdb.GetAccessData(
            "10.10.22.204:7777", "path10", "nflbet.mdb", "nflbase", 0, 50,
            "", ""
        );
        if (aasData[0][0].length() > 6  &&  aasData[0][0].substring(0, 6) == "Error:") {
            System.out.println("SOexcel Error: " + aasData[0][0] + "\r\n");
        } else {
            int x, y;
            for (y = 0; y < 50; y++) {
                for (x = 0; x < 12; x++) {
                    System.out.print(aasData[y][x]+",");
                }
                System.out.println("...");
            }
            System.out.println(".........");
        }

    //    Done.
    //
    }
}

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 Java ShareOffice library can run on any up-to-date Java platform (see above). The PC that contains the Excel, Access or Word data must be running a copy of BadBlue to support the web service calls that the Java ShareOffice classes implement.

BadBlue Help Center >> PHP FAQ BadBlue Discussion Email support