Accessing Sheets, Saving Excel files, Java, Java Com Bridge ( Jacob jacob-1.16)

Question

I looked at the included Excel example of the latest version of Java Com Bridge (jacob-1.16) and was a little disappointed because it creates and works on a brand new Excel file.

What I would like to do is having access to an existing Excel file from Java, in this case named 'JACOBWithExcel.xls', but any Excel file should be fine.

The SourceForge example for Excel works fine on my machine, and as I modified it for accessing an existing Excel file I ran into the following issues:

1., I could not get an existing sheet in the workbook, even though I am trying the same way I got the workbook:

Dispatch sheet = Dispatch.get(workbook, "Worksheets").toDispatch();  
Dispatch.call(sheet, "Select", new Object[]{"Sheet2"}).toDispatch();

This code will generate the following exception: com.jacob.com.ComFailException: Can't map name to dispid: Worksheets

2.; Could not save the workbook:

// Save the open workbook as "C:\jacob-1.16-M1\Test1.xls" file:
Dispatch.call(workbook, "SaveAs", new Variant("C:\\jacob-1.16-M1\\Test1.xls"),new   Variant("1"));

This code will generate the following exception: com.jacob.com.ComFailException: Can't map name to dispid: SaveAs

3.; I don't know how to get started with the following simple but very common Excel operations, as far as Java syntax for Java COM bridge:

(Included here the Excel VBA code that I am trying to implement in Java)

Selecting a single cell: Range("A4").Select

Copy selected range to clipboard:

Selection.Copy

Select multi-cell range to copy to:

Range("D9:D17").Select

Paste clipboard contents to selection:

ActiveSheet.Paste

Renaming a sheet:

Sheets("Sheet2").Select
Sheets("Sheet2").Name = "MySheet2"

Format cells, example for text:

Selection.NumberFormat = "@"

Delete Rows:

Rows(intI).Select
Selection.Delete Shift:=xlUp

And possibly...

Sort a selection:

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,  Orientation:=xlTopToBottom

Find the last cell in sheet:

ActiveSheet.Cells(65536, 1).End(xlUp).Select
intLastCellRow = Selection.Row

Thank you for your help.

P.S.:

The full code of the application:

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

public class TestJACOBWithExcel {
public static void main(String[] args) {

String strInputDoc = "C:\\jacob-1.16-M1\\JACOBWithExcel.xls";  // file to be opened.

ComThread.InitSTA();

ActiveXComponent xl = new ActiveXComponent("Excel.Application"); // Instance of application object created.

try {
// Get Excel application object properties in 2 ways:
System.out.println("version=" + xl.getProperty("Version"));
System.out.println("version=" + Dispatch.get(xl, "Version"));

// Make Excel instance visible:
Dispatch.put(xl, "Visible", new Variant(true));

// Open XLS file, get the workbooks object required for access:
Dispatch workbook = xl.getProperty("Workbooks").toDispatch();
Dispatch.call(workbook, "Open", new Variant(strInputDoc),new Variant("1"));

Dispatch sheet = Dispatch.get(workbook, "Worksheets").toDispatch();
Dispatch.call(sheet, "Select", new Object[]{"Sheet2"}).toDispatch();

// put in a value in cell A22 and place a a formula in cell A23:
Dispatch a22 = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { "A22" }, new int[1]).toDispatch();

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

Dispatch.put(a22, "Value", "123.456");
Dispatch.put(a23, "Formula", "=A22*2");

// Get values from cells A1 and A2
System.out.println("a22 from excel:" + Dispatch.get(a22, "Value"));
System.out.println("a23 from excel:" + Dispatch.get(a23, "Value"));

// Save the open workbook as "C:\jacob-1.16-M1\Test1.xls" file:
Dispatch.call(workbook, "SaveAs", new Variant("C:\\jacob-1.16-M1\\Test1.xls"),new Variant("1"));

// Close the Excel workbook without saving:
Variant saveYesNo = new Variant(false);
Dispatch.call(workbook, "Close", saveYesNo);

} catch (Exception e) {
e.printStackTrace();
} finally {

// Quit Excel:
// xl.invoke("Quit", new Variant[] {});
ComThread.Release();
}

}
}

Answer 1

Disclaimer: In this answer i will be reffering MS Excel object properties, methods and object types with double quotation to try to avoid some confusion. My answer to this question follows.

Hi,

it is important to understand the hierarchy, that excel API have. And also search what types of methods, properties or events are available on each level of hierarchy. Im going to answer your quetions now.

In question 1 you say you cannot open correct worksheet and thats because you are getting property "Worksheets" on wrong type of object. In your code snippet

Dispatch sheet = Dispatch.get(workbook, "Worksheets").toDispatch();
Dispatch.call(sheet, "Select", new Object[]{"Sheet2"}).toDispatch();

you get property "Worksheets" on object of type "Workbooks", which is incorrect one. This doc https://msdn.microsoft.com/EN-US/library/office/ff841074.aspx shows, that "Workbooks" doesnt have property "Worksheets". Problem is in how you open concrete "Workbook" object.

Dispatch workbook = xl.getProperty("Workbooks").toDispatch();
Dispatch.call(workbook, "Open", new Variant(strInputDoc),new Variant("1"));

You call method "Open" on "Workbooks" object, which according doc have this method and it opens MS Excel for you. Problem is variable "Dispatch workbook" is still object of type "Workbooks" and not "Workbook", which you wrongly assumed. Correct way is to save return value of your "Open" method call, which gives you concrete "Workbook" object and after that use methods available for that type of object. Exactly following MS docu here https://msdn.microsoft.com/en-us/library/office/ff194819.aspx .

So correct way to open concrete worksheet would be like this:

//get "Workbooks" property from "Application" object
Dispatch workbooks = xl.getProperty("Workbooks").toDispatch();
//call method "Open" with filepath param on "Workbooks" object and save "Workbook" object
Dispatch workbook = Dispatch.call(workbooks, "Open", new Variant(strInputDoc)).toDispatch();
//get "Worksheets" property from "Workbook" object
Dispatch sheets = Dispatch.get(workbook, "Worksheets").toDispatch();
//Call method "Select" on "Worksheets" object with "Sheet2" param   
Dispatch.call(sheets, "Select", new Object[]{"Sheet2"}).toDispatch();
//probably again save "Worksheet" object and continue same way

In question 2 is the same problem as in question 1.

//calling method "SaveAs" on "Workbooks" object instead of "Workbook" type
Dispatch.call(workbook, "SaveAs", new Variant("C:\\jacob-1.16-M1\\Test1.xls"),new   Variant("1"));

All other questions are just same principle as two before. Get property or call method on correct MS object. Also cannot stress enough, how important MS docu will be for anyone with java-excel comunication.

Will just mention a example, how to get actual values from some MS objects. Lets say you want values inside "Range" object, because getting properties of objects, that require parameters is done a bit differently then normal value property. To do this you need to get to "Worksheet" object first and get "Range" property from that.

//lets say we have already some "Worksheet" object
Dispatch worksheet = ...;
//we want a property "Range" of "Worksheet" object with some macro
Variant range = Dispatch.invoke(worksheet, "Range", Dispatch.Get, new Object[]{"A1:L10"}, new int[1]);
//according to docu, you can specify format in which the value is returned
//im using 12 which is one of XML formats available
//in case no such param is provided default "textTable" format is returned
Variant value = Dispatch.invoke(range.getDispatch(), "Value", Dispatch.Get, new Object[]{12}, new int[1]);
//and get the value from it and you are done with MS Excel-Java communication
String xmlFormat = value.toString();



Worksheet.Range property docu:
msdn.microsoft.com/en-us/library/office/ff836512.aspx
Range.Value docu:
msdn.microsoft.com/en-us/library/office/ff195193.aspx

Im not allowed to post more than 2 links, so im sorry if these links arent working properly.

Answer 2

Hy,

actually i can only answer to your first question right now. I use this code and it's working for *.xltx Template files properly:

File file = new File("pathToOurTemplate");
ActiveXComponent excel = null;
  ComThread.InitSTA();
  try {
    excel = new ActiveXComponent("Excel.Application");
    Dispatch excelObject = excel.getObject();
    Dispatch workbooks = excel.getProperty("Workbooks").toDispatch();
    Dispatch workbook = Dispatch.call(workbooks, "Add", file.getAbsolutePath()).toDispatch();
} catch (...) 
//...

Works at least with Office 2010, Java6_u23, Jacob 1.15_M4

Hope this helps for a first shot / this is more what you need than shown in the examples (guess you took a look at those).