I have a blank excel sheet having some macro and command button embadded in that. I have written a program using HSSF API to fill some data in this sheet and create cell comment. Now the problem is when execute this program the command button getting removed from the excel sheet. Please find the code i am using : HSSFPatriarch patr = sheet.createDrawingPatriarch();//This method is responsible for removing command button HSSFComment comment1 = patr.createComment(new HSSFClientAnchor(2, 2, 2, 2, (short)4, 2, (short) 6, 5)); comment1.setString(new HSSFRichTextString("We can set comments in POI")); comment1.setAuthor("Apache Software Foundation"); comment1.setRow(2); comment1.setColumn((short)2); Please help me if you have any solutions or is there any way i can create a command button and assign a macro fucntion to it, in excel sheet using java program
It's not a bug. The 'patriarch' object owns all the drawing elements in the workbook. MS Office drawings, comments, and buttons are all drawing elements. When you call sheet.createDrawingPatriarch(); you are creating a new patriarch object (and therefore removing the one that contained your button). Maybe you want to call sheet.getDrawingPatriarch(); But that method is not completely implemented, because all the types of Escher records that could be components of the patriarch are not completely understood. In particular, I don't think POI yet understands how to associate form elements with macros.
Hi Michael, Thanks for the comment. So does it means as of now i can not achieve this by using POI. can anybody suggest some other option to achieve the same. Thanks, Sohel (In reply to comment #1) > It's not a bug. > > The 'patriarch' object owns all the drawing elements in the workbook. MS Office > drawings, comments, and buttons are all drawing elements. > > When you call > > sheet.createDrawingPatriarch(); > > you are creating a new patriarch object (and therefore removing the one that > contained your button). > > Maybe you want to call > > sheet.getDrawingPatriarch(); > > But that method is not completely implemented, because all the types of Escher > records that could be components of the patriarch are not completely > understood. In particular, I don't think POI yet understands how to associate > form elements with macros. >
At this time, it's not possible to get at the existing patriach, so you can't add a comment without ditching the old one For now, you should create as many comments as you want at the same time as adding the command. Then, in POI, move those comments to where you need them, and add the text you need.
Hi Nick, I am using a excel template which already has some command button embaded in it. these command buttons are used to execute some macro functions. anyway i can not assign a macro function to a command button. so i can not create the above mentioned template using POI. One udpate I have achieved the similar functionality using SmartXL APIs.