create watermark in excel using java

[Ramya28]

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.. ..

[SammyB]

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

[SammyB]

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?

[SammyB]

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!

import java.io.*;
import org.apache.poi.hssf.usermodel.*;
public class xlWatermark {
    public static void main(String[] args) {
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream("C:\\TestWM.xls");
            HSSFSheet ws = wb.createSheet("MySheet");
            HSSFPatriarch dp = ws.createDrawingPatriarch();
            HSSFClientAnchor anchor = new HSSFClientAnchor
                (0, 0, 1023, 255, (short) 2, 4, (short) 13, 26);
            HSSFTextbox txtbox = dp.createTextbox(anchor);
            HSSFRichTextString rtxt = new HSSFRichTextString("Draft");
            HSSFFont font = wb.createFont();
            font.setColor((short) 27);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            font.setFontHeightInPoints((short) 192);
            font.setFontName("Verdana");
            rtxt.applyFont(font);
            txtbox.setString(rtxt);
            txtbox.setLineStyle(HSSFShape.LINESTYLE_NONE);
            txtbox.setNoFill(true);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } 
    }
}

[Ramya28]

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..

[Ramya28]

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??

FileOutputStream fileOut = new FileOutputStream("c:/Book11.xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("Worksheet");
 
FileInputStream fis=new FileInputStream("draft.jpg");
ByteArrayOutputStream img_bytes=new ByteArrayOutputStream();
int b;
while((b=fis.read())!=-1)
img_bytes.write(b);
fis.close();
HSSFClientAnchor anchor = new
    HSSFClientAnchor(50,100,100,100,(short)col,row,(short)++col,++row);
int index=workbook.addPicture(img_bytes.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);
HSSFSheet sheet=workbook.getSheet("Worksheet");
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
patriarch.createPicture(anchor,index).resize();
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?

[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

[Ramya28]

Thanks for the tip :).. Thanks again