Jacob<\/a>. 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.<\/p>\nUsing 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.<\/p>\n
<\/p>\n
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\u00a0intuitive. See the below example.<\/p>\n
\r\nString com.jacob.com.SafeArray.getString(int sa_idx1, int sa_idx2) \/\/api\r\n<\/pre>\nIf you acquire one column of data and cast it into a SafeArray, I was expecting to access it such as.<\/p>\n
\r\nString cellData = safeArray.getString(1, i); \/\/where 1 is the first column and i is the ith item in the column\r\n<\/pre>\nHowever, it’s actually the reverse:<\/p>\n
\r\nString cellData = safeArray.getString(i, 1); \/\/where 1 is the first column and i is the ith item in the column\r\n<\/pre>\nThis is a “got you” that caused me a bit of frustration at first.<\/p>\n
I have written a utility class to do most of the heavy lifting, posted below.<\/p>\n
\r\npublic class ExcelUtility {\r\n\r\n\tprivate static final Log LOGGER = LogFactory.getLog(ExcelUtility.class);\r\n\r\n\t\/**\r\n\t * Gets the value of one particular cell in a specific sheet and workbook\r\n\t *\/\r\n\tpublic static Variant getCellValue(String workbookName, String sheetName, String cellLoc) {\r\n\t\ttry {\r\n\t\t\tActiveXComponent xl = ActiveXComponent.connectToActiveInstance("Excel.Application");\r\n\r\n\t\t\tDispatch xlo = xl.getObject();\r\n\t\t\tDispatch workbooks = xl.getProperty("Workbooks").getDispatch();\r\n\r\n\t\t\tDispatch workbook = Dispatch.invoke(workbooks, "Item", Dispatch.Get, new Object[] { workbookName }, new int[0]).getDispatch();\r\n\t\t\tLOGGER.debug(Dispatch.get(workbook, "Name").toString());\r\n\r\n\t\t\tDispatch sheets = Dispatch.get(workbook, "Sheets").toDispatch();\r\n\t\t\tDispatch sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get, new Object[] { sheetName }, new int[0]).getDispatch();\r\n\r\n\t\t\tDispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { cellLoc }, new int[1]).toDispatch();\r\n\r\n\t\t\tVariant value = Dispatch.get(cell, "Value");\r\n\r\n\t\t\txlo.safeRelease();\r\n\r\n\t\t\treturn value;\r\n\t\t}\r\n\t\tcatch (RuntimeException ex) {\r\n\t\t\tthrow new ExcelException("Unable to read from Excel Process, trye again later.", ex);\r\n\t\t}\r\n\t}\r\n\r\n\tpublic static List getSheets(String workbookName) throws ExcelException {\r\n\t\tList sheetNames = new ArrayList();\r\n\t\ttry {\r\n\t\t\tActiveXComponent xl = ActiveXComponent.connectToActiveInstance("Excel.Application");\r\n\r\n\t\t\tDispatch xlo = xl.getObject();\r\n\t\t\tDispatch workbooks = xl.getProperty("Workbooks").getDispatch();\r\n\r\n\t\t\tDispatch workbook = Dispatch.invoke(workbooks, "Item", Dispatch.Get, new Object[] { workbookName }, new int[0]).getDispatch();\r\n\t\t\tLOGGER.debug(Dispatch.get(workbook, "Name").toString());\r\n\r\n\t\t\tDispatch sheets = Dispatch.get(workbook, "Sheets").toDispatch();\r\n\r\n\t\t\tint sheetCount = Dispatch.get(sheets, "Count").getInt();\r\n\r\n\t\t\tLOGGER.debug("Total open sheets: " + sheetCount);\r\n\t\t\tfor (int i = 1; i \t\t\t\tDispatch onesheet = Dispatch.invoke(sheets, "Item", Dispatch.Get, new Object[] { i }, new int[0]).getDispatch();\r\n\t\t\t\tLOGGER.debug(Dispatch.get(onesheet, "Name").toString());\r\n\t\t\t\tsheetNames.add(Dispatch.get(onesheet, "Name").toString());\r\n\t\t\t}\r\n\r\n\t\t\txlo.safeRelease();\r\n\t\t}\r\n\t\tcatch (RuntimeException ex) {\r\n\t\t\tthrow new ExcelException("Unable to read from Excel Process, trye again later.", ex);\r\n\t\t}\r\n\t\treturn sheetNames;\r\n\t}\r\n\r\n\t\/**\r\n\t * Gets a list of all Workbook names from the live instance of Excel\r\n\t * @return\r\n\t * @throws Exception\r\n\t *\/\r\n\tpublic static List getOpenedWorkbooks() throws ExcelException {\r\n\t\tList workbookNames = new ArrayList();\r\n\t\ttry {\r\n\t\t\tActiveXComponent xl = ActiveXComponent.connectToActiveInstance("Excel.Application");\r\n\t\t\txl.setProperty("Visible", true);\r\n\t\t\tDispatch xlo = xl.getObject();\r\n\r\n\t\t\t\/\/xl.setProperty("Visible", new Variant(true));\r\n\t\t\tDispatch workbooks = xl.getProperty("Workbooks").getDispatch();\r\n\t\t\tint openWorkBookCount = Dispatch.get(workbooks, "Count").getInt();\r\n\t\t\tif (openWorkBookCount == 0) {\r\n\t\t\t\tthrow new ExcelException("No open Excel workbooks");\r\n\t\t\t}\r\n\t\t\tfor (int i = 1; i \t\t\t\tDispatch oneworkbook = Dispatch.invoke(workbooks, "Item", Dispatch.Get, new Object[] { i }, new int[0]).getDispatch();\r\n\t\t\t\tLOGGER.debug("Workbook name: " + Dispatch.get(oneworkbook, "Name").toString());\r\n\t\t\t\tworkbookNames.add(Dispatch.get(oneworkbook, "Name").toString());\r\n\t\t\t}\r\n\t\t\txlo.safeRelease();\r\n\t\t} catch (RuntimeException ex) {\r\n\t\t\tthrow new ExcelException("Unable to read from Excel Process, trye again later.", ex);\r\n\t\t}\r\n\r\n\t\treturn workbookNames;\r\n\t}\r\n\r\n}\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[23,22,21],"class_list":["post-78","post","type-post","status-publish","format-standard","hentry","category-problem","tag-excel","tag-jacob","tag-java-2"],"_links":{"self":[{"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/posts\/78","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/comments?post=78"}],"version-history":[{"count":9,"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/posts\/78\/revisions"}],"predecessor-version":[{"id":86,"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/posts\/78\/revisions\/86"}],"wp:attachment":[{"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/media?parent=78"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/categories?post=78"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.lazydungeon.com\/blog\/wp-json\/wp\/v2\/tags?post=78"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}