Coding Sanctuary

I’ve recently have to do some Excel integration with Java.

It was a very interesting challenge, I had to play around with COM and Dispatch stuff, things I have never touched before from a pure Java world.

After some investigation with the different libraries available, I’ve decided to try it Jacob. One of my requirements was to be able to read the data from Excel’s spreadsheet in memory, these data could have been altered constantly from external sources without been saved to to file system.

Using Jacob I was able to read all the workbook’s title, worksheets within the workbook, and the cells and their data all in memory. Basically anything functions from the Visual Basic editor within Excel was available from the Jacob’s API.

The data returned from Excel are casted to a SafeArray object, which can be 2 dimensional. One minor complain is the method to access the data seems a bit counter intuitive. See the below example.

String com.jacob.com.SafeArray.getString(int sa_idx1, int sa_idx2) //api

If you acquire one column of data and cast it into a SafeArray, I was expecting to access it such as.

String cellData = safeArray.getString(1, i); //where 1 is the first column and i is the ith item in the column

However, it’s actually the reverse:

String cellData = safeArray.getString(i, 1); //where 1 is the first column and i is the ith item in the column

This is a “got you” that caused me a bit of frustration at first.

I have written a utility class to do most of the heavy lifting, posted below.

public class ExcelUtility {

	private static final Log LOGGER = LogFactory.getLog(ExcelUtility.class);

	/**
	 * Gets the value of one particular cell in a specific sheet and workbook
	 */
	public static Variant getCellValue(String workbookName, String sheetName, String cellLoc) {
		try {
			ActiveXComponent xl = ActiveXComponent.connectToActiveInstance("Excel.Application");

			Dispatch xlo = xl.getObject();
			Dispatch workbooks = xl.getProperty("Workbooks").getDispatch();

			Dispatch workbook = Dispatch.invoke(workbooks, "Item", Dispatch.Get, new Object[] { workbookName }, new int[0]).getDispatch();
			LOGGER.debug(Dispatch.get(workbook, "Name").toString());

			Dispatch sheets = Dispatch.get(workbook, "Sheets").toDispatch();
			Dispatch sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get, new Object[] { sheetName }, new int[0]).getDispatch();

			Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { cellLoc }, new int[1]).toDispatch();

			Variant value = Dispatch.get(cell, "Value");

			xlo.safeRelease();

			return value;
		}
		catch (RuntimeException ex) {
			throw new ExcelException("Unable to read from Excel Process, trye again later.", ex);
		}
	}

	public static List getSheets(String workbookName) throws ExcelException {
		List sheetNames = new ArrayList();
		try {
			ActiveXComponent xl = ActiveXComponent.connectToActiveInstance("Excel.Application");

			Dispatch xlo = xl.getObject();
			Dispatch workbooks = xl.getProperty("Workbooks").getDispatch();

			Dispatch workbook = Dispatch.invoke(workbooks, "Item", Dispatch.Get, new Object[] { workbookName }, new int[0]).getDispatch();
			LOGGER.debug(Dispatch.get(workbook, "Name").toString());

			Dispatch sheets = Dispatch.get(workbook, "Sheets").toDispatch();

			int sheetCount = Dispatch.get(sheets, "Count").getInt();

			LOGGER.debug("Total open sheets: " + sheetCount);
			for (int i = 1; i 				Dispatch onesheet = Dispatch.invoke(sheets, "Item", Dispatch.Get, new Object[] { i }, new int[0]).getDispatch();
				LOGGER.debug(Dispatch.get(onesheet, "Name").toString());
				sheetNames.add(Dispatch.get(onesheet, "Name").toString());
			}

			xlo.safeRelease();
		}
		catch (RuntimeException ex) {
			throw new ExcelException("Unable to read from Excel Process, trye again later.", ex);
		}
		return sheetNames;
	}

	/**
	 * Gets a list of all Workbook names from the live instance of Excel
	 * @return
	 * @throws Exception
	 */
	public static List getOpenedWorkbooks() throws ExcelException {
		List workbookNames = new ArrayList();
		try {
			ActiveXComponent xl = ActiveXComponent.connectToActiveInstance("Excel.Application");
			xl.setProperty("Visible", true);
			Dispatch xlo = xl.getObject();

			//xl.setProperty("Visible", new Variant(true));
			Dispatch workbooks = xl.getProperty("Workbooks").getDispatch();
			int openWorkBookCount = Dispatch.get(workbooks, "Count").getInt();
			if (openWorkBookCount == 0) {
				throw new ExcelException("No open Excel workbooks");
			}
			for (int i = 1; i 				Dispatch oneworkbook = Dispatch.invoke(workbooks, "Item", Dispatch.Get, new Object[] { i }, new int[0]).getDispatch();
				LOGGER.debug("Workbook name: " + Dispatch.get(oneworkbook, "Name").toString());
				workbookNames.add(Dispatch.get(oneworkbook, "Name").toString());
			}
			xlo.safeRelease();
		} catch (RuntimeException ex) {
			throw new ExcelException("Unable to read from Excel Process, trye again later.", ex);
		}

		return workbookNames;
	}

}