469,086 Members | 1,247 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,086 developers. It's quick & easy.

create watermark in excel using java

Hi...
I need to read and write to an excel sheet with watermark in it by using java..
i can perform the rest of operations i need to on the excel except for watermark image in the output excel..

could anyone tel me.. how to create watermark in excel using java??
any ideas.. ..
Mar 30 '10 #1

✓ answered by SammyB

In order to get the picture in the background, you must insert the picture into the worksheet header, set the brightness to .85, the contrast to .15, and the ColorType to msoPictureWatermark. The picture actually goes into the PageSetup object. Easy to do in Excel, but there does not seem to be any HSSF method that support this in HSSFHeader or HSSFPageSetup. I also thought about writing an AutoOpen macro to move a worksheet picture to the header, but again HSSF does not seem to have any interface for writing macros into a workbook.

So, the only way that I can think of is to create a template with the watermark already on board and open it instead of a new workbook. For good instruction for manually creating this template, see http://www.educationworld.com/a_tech...htorial089.pdf

HTH -- Sam

7 12510
SammyB
807 Expert 512MB
Well, I've forgotten more Java than I've learned, so if you can post a snippet to create a workbook and worksheet, I can get you a watermark. What version of Excel are you using? Do you want text or a picture?

The problem is that Excel doesn't have a Watermark object: you just create a shape and change it's transparency so that it looks like a watermark. For text, after you have a WorkSheet object, then you use the AddTextEffect to the Shapes property of the sheet. The AddTextEffect actually adds text as a shape, then you change the properties of that shape object: .ScaleHeight, .ScaleWidth, .Fill.Visible, .Fill.Transparency, .Line.Weight, .Line.DashStyle, .Line.Style, .Line.Transparency, .Line.Visible, .Line.ForeColor, .Line.BackColor, .Height, .Width, etc

Get it working as an Excel macro and then translate it into Java. Or, answer the questions above and I'll dust off my Java skills. HTH --Sam
Mar 30 '10 #2
SammyB
807 Expert 512MB
Agggggg, Java and Excel do not play nicely together, but I'm having fun, LOL.

So, what API are you using for Excel?

I'm assuming org.apache.poi.ss.usermodel. Do you have
import org.apache.poi.ss.usermodel.*;
at the top?

Looking at some other posts here on Bytes, it looks like the apache poi is limited. You would be better off using a Mocrosoft environment like C# or VB. Is this an option?
Mar 30 '10 #3
SammyB
807 Expert 512MB
OK, here is the best that I could do. Had to switch back to HSSF because I couldn't understand the SS documentation. What a mess!
Expand|Select|Wrap|Line Numbers
  1. import java.io.*;
  2. import org.apache.poi.hssf.usermodel.*;
  3. public class xlWatermark {
  4.     public static void main(String[] args) {
  5.         HSSFWorkbook wb = new HSSFWorkbook();
  6.         FileOutputStream fileOut = null;
  7.         try {
  8.             fileOut = new FileOutputStream("C:\\TestWM.xls");
  9.             HSSFSheet ws = wb.createSheet("MySheet");
  10.             HSSFPatriarch dp = ws.createDrawingPatriarch();
  11.             HSSFClientAnchor anchor = new HSSFClientAnchor
  12.                 (0, 0, 1023, 255, (short) 2, 4, (short) 13, 26);
  13.             HSSFTextbox txtbox = dp.createTextbox(anchor);
  14.             HSSFRichTextString rtxt = new HSSFRichTextString("Draft");
  15.             HSSFFont font = wb.createFont();
  16.             font.setColor((short) 27);
  17.             font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
  18.             font.setFontHeightInPoints((short) 192);
  19.             font.setFontName("Verdana");
  20.             rtxt.applyFont(font);
  21.             txtbox.setString(rtxt);
  22.             txtbox.setLineStyle(HSSFShape.LINESTYLE_NONE);
  23.             txtbox.setNoFill(true);
  24.             wb.write(fileOut);
  25.             fileOut.close();
  26.         } catch (FileNotFoundException e) {
  27.             e.printStackTrace();
  28.         } catch (IOException e) {
  29.             e.printStackTrace();
  30.         } 
  31.     }
  32. }
Mar 30 '10 #4
HI,

THanks a ton... .. I have import org.apache.poi.hssf.usermodel.*;
at the top...
And..nope I cant do it on Vb or C#...
And thanks for the tips n code part... I am happy I ve this option of textbox..
But still I face a problem...
The same problem I face till now is... Let the watermark be text(according to the code u had helped) or be it an image (text written and saved as jpg or png as I had tried).. I need the watermark below /behind the rest of the text.. but it appears on/over the other text . That is it overlaps text.. instead of appearing under it..
Mar 31 '10 #5
Hi...

I loaded picture for watermarking... an jpg file containing the text(ex:"draft")..
Here too.. I cant make the image transparent,it overlaps the text...does HSSF and image transparent go together??

Expand|Select|Wrap|Line Numbers
  1. FileOutputStream fileOut = new FileOutputStream("c:/Book11.xls");
  2. HSSFWorkbook workbook = new HSSFWorkbook();
  3. HSSFSheet worksheet = workbook.createSheet("Worksheet");
  4.  
  5. FileInputStream fis=new FileInputStream("draft.jpg");
  6. ByteArrayOutputStream img_bytes=new ByteArrayOutputStream();
  7. int b;
  8. while((b=fis.read())!=-1)
  9. img_bytes.write(b);
  10. fis.close();
  11. HSSFClientAnchor anchor = new
  12.     HSSFClientAnchor(50,100,100,100,(short)col,row,(short)++col,++row);
  13. int index=workbook.addPicture(img_bytes.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);
  14. HSSFSheet sheet=workbook.getSheet("Worksheet");
  15. HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
  16. patriarch.createPicture(anchor,index).resize();
  17. anchor.setAnchorType(2);
I came across TYPE_INT_ARGB (in watermarking image over image),on using it, may be the transparency of the image cud be reduced. But I am unable to use it with workbook.Could anyone suggest something more?
Mar 31 '10 #6
SammyB
807 Expert 512MB
In order to get the picture in the background, you must insert the picture into the worksheet header, set the brightness to .85, the contrast to .15, and the ColorType to msoPictureWatermark. The picture actually goes into the PageSetup object. Easy to do in Excel, but there does not seem to be any HSSF method that support this in HSSFHeader or HSSFPageSetup. I also thought about writing an AutoOpen macro to move a worksheet picture to the header, but again HSSF does not seem to have any interface for writing macros into a workbook.

So, the only way that I can think of is to create a template with the watermark already on board and open it instead of a new workbook. For good instruction for manually creating this template, see http://www.educationworld.com/a_tech...htorial089.pdf

HTH -- Sam
Mar 31 '10 #7
Thanks for the tip :).. Thanks again
Apr 5 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Dan | last post: by
2 posts views Thread by export | last post: by
9 posts views Thread by Chuck Anderson | last post: by
reply views Thread by =?Utf-8?B?S2Vycnk=?= | last post: by
2 posts views Thread by cssExp | last post: by
1 post views Thread by SagarDoke | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.