create excel file with colspan

I have no idea how to do it with jacob, but I know it can be done with Apache POI (http://poi.apache.org).

Have a look at: http://poi.apache.org/hssf/quick-guide.html#MergedCells

Not what you asked for, but if you don't have to use jacob, I'd say give POI a shot.

Hi schybert,
i prorgrammed a little project with jacob and word, where i manipulated a word file. Now i want to do the same for the excel file, and combine the results at the end.
 Thank you your Comment, but i have to use jacob.

According to the Jacob documentation, it is only an interface to using the Microsoft Office API through a Java-COM Bridge (hence the name).  You have to read the Microsoft Office API for Excel to discover the right set of calls for merged cells (which is what it's called in Excel, not colspan).  For example here:
http://msdn2.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.merge.aspx
is VB code for creating merged cells in a spreadsheet.

You can see the Microsoft newsgroup for programming Excel here:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.programming&cat=en_US_3a793e1f-4961-419d-9ec7-899d6e6086cd&lang=en&cr=US

See the Jacob FAQ for more explanation of how to use Jacob:
http://danadler.com/jacob/jacobfaq.html

Sorry, a2 above should be range (of couse).

schybert:
i tried the code above, but it didnt works. it gives the following error:
>com.jacob.com.ComFailException: Invoke of: MergeCells
>Source: Microsoft Office Excel
>Description: Die MergeCells-Eigenschaft des Range-Objektes kann nicht festgelegt werden.
>      at com.jacob.com.Dispatch.invokev(Native Method)
>      at com.jacob.com.Dispatch.invokev(Dispatch.java:945)
>      at com.jacob.com.Dispatch.invoke(Dispatch.java:604)
>      at com.jacob.com.Dispatch.put(Dispatch.java:903)
>      at oriented.openExcel.main(openExcel.java:51)
-------------------------------------------------------------------------------------------------------------------

mrcoffee365: I going to take a look at the websites you proposed me, am will give a feedback as soon as i found something in There.

Thanks all

Can you post your whole code? Or is it too much?

Oh and my german is a bit rusty... can you translate this "Die MergeCells-Eigenschaft des Range-Objektes kann nicht festgelegt werden."

First, Thank you schybert:

instead og MergeCells  i use only Merge and if half-works: it merges the cells but it didnt put the Value "Testing ... " in it. this time it gives the following error:
>com.jacob.com.ComFailException: A COM exception has been encountered:
>At Invoke of: Merge
>Description: Mitglied nicht gefunden. -> Member not found

The first error is in englisch: The MergeCells characteristic of the rank object cannot be specified.
But it doesnt matter now, because the Solution is Merge instead of MergeCells

The code on which am Working now is the same as in the Jacob-Project-website.
The Hole Project is too big.

OK, we must be using different versions of Excel, because it works flawlessly for me.

Are you sure you're using the code I posted? MergeCells is a property and Merge is a method, so I find it hard to see that calling Merge with Dispatch.put would work...

you think so?
I am usinh Excel (or office) 2003
programming with eclipse 3.2.0
java version "1.6.0_02"
Java(TM) SE Runtime Environment (build 1.6.0_02-b06)

something that maybe relevent:
i must cast some Objects to Class Dispatch so that it works, for example, the code you wrote here:

Object a2= Dispatch.invoke((Dispatch) sheet, "Range", Dispatch.Get,
                                        new Object[] {"A1", "B1"},
                                        new int[1]).toDispatch();
Dispatch.put((Dispatch) a2, "Merge", "True");
Dispatch.put((Dispatch) a2, "Value", "Testing merged cells");

We're using different jre versions, but it's the COM-API that's complaining... Can you just post the lines of your code that represent the lines I suggested earlier?

Huh? The invoke method takes an Object as its first argument, so I can't see why you would have to cast it...

ok, hier is the code:

package office;
 
import com.jacob.com.*;
import com.jacob.activeX.*;
 
public class openExcel 
{
  public static void main(String[] args)
  {
    ComThread.InitSTA();
 
    ActiveXComponent xl = new ActiveXComponent("Excel.Application");
    Object xlo = xl.getObject();
    try {
      System.out.println("version="+xl.getProperty("Version"));
      System.out.println("version="+Dispatch.get((Dispatch) xlo, "Version"));
      Dispatch.put((Dispatch) xlo, "Visible", new Variant(true));
      Object workbooks = xl.getProperty("Workbooks").toDispatch();
      Object workbook = Dispatch.get((Dispatch) workbooks,"Add").toDispatch();
      
      Object sheet = Dispatch.get((Dispatch) workbook,"ActiveSheet").toDispatch();
     
      
      Object aa = Dispatch.invoke((Dispatch) sheet, "Range", Dispatch.Get,
				new Object[] {"A1", "C1"},
				new int[1]).toDispatch();
		Dispatch.put((Dispatch)aa, "Merge","True");
		Dispatch.put((Dispatch)aa, "Value", "Testing merged cells");
    
      
      Variant f = new Variant(false);
      
      //Dispatch.call((Dispatch) workbook, "Close", f); // to close the excel-File
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      //xl.invoke("Quit", new Variant[] {}); // to quit the excel-programm
      ComThread.Release();
    }
  }
}

Select allOpen in new window

Well, that's just weird. If I take your code and run it in my environment and change the Merge to MergeCells ir works just fine :-/

The Dispatch.put((Dispatch) aa, "Merge", "True"); call seems to work "good enough", and the reason that the value isn't set is because it's never executed (due to the exception thrown). So a workaround (ugly) is to surround the Merge call in a try-catch block and just swallow the Exception... works, but it's pretty ugly.

The different behaviors you're seeing with Merge and MergeCells might have to do with different versions of Office, hence the dlls, you're using.  What versions do you have?

There's a very active forum for Jacob on SourceForge:
http://sourceforge.net/projects/jacob-project/

hi,
I thank you all for your Help, especially schybert.
here is the optimised Code, this time for merging cells and setting a width to a column.
I hope this discussion helps us all to to improve our knowledge.

an Other Question related to the same Topic (or should i open an other Question):
where can I find a list (or documentation) about The Keywords and their Signification used in the Jacob to manipulate Excel. Like Merge, MergeCells, EntireColumn, ColumnWidth

Thanx

			
//Get Range : define where the Table begins and where it ends (From A until K)
Dispatch theRange = Dispatch.invoke((Dispatch) sheet,
"Range",Dispatch.Get,new Object[]{"A1:K1"},new int[1]).toDispatch();
 
//Get Column A & set Width
Dispatch theColumn = Dispatch.invoke(theRange,
"EntireColumn",Dispatch.Get,new Object[]{"A"},new int[1]).toDispatch();
Dispatch.put(theColumn,"ColumnWidth","5");
 
//Merge cell E1:G1 (from E1 to G1)
theRange = Dispatch.invoke((Dispatch) sheet,
"Range",Dispatch.Get,new Object[]{"E1:G1"},new int[1]).toDispatch();
theRange.call(theRange,"Merge");//or "MergeCells" but without "True"
Dispatch.put(theRange,"Value","Testing merging cells");

Select allOpen in new window

Great -- thanks for posting back your working code.

I think you should award the points on this question and start another one if you still need pointers to documentation.

The links I've given you have the documentation for Microsoft's API for Excel, which is what you need to make calls to Excel.

Already run same code i got this exception like
com.jacob.com.ComFailException: A COM exception has been encountered:
At Invoke of: Workbooks
Description: An unknown COM error has occured.
      at com.jacob.com.Dispatch.invokev(Native Method)
      at com.jacob.activeX.ActiveXComponent.getProperty(ActiveXComponent.java)
      at allexp.DispatchTest.main(DispatchTest.java:18)
I am using eclipse5.0 and java 1.6
Can u suggest any thing for this exception