首页 > 代码库 > jxl创建excel加水印

jxl创建excel加水印

最近做个excel加水印的,在网上找了很多,都是使用jxl添加,但是本地测试一直没有通过,主要原因是因为背景图片不符合要求,后来找了image4j来做成图片,完成了背景图片的添加,需要用到的jxl.jar包image4j.jar,具体代码如下:

package com.file;

import java.awt.AlphaComposite;
import java.awt.BasicStroke;
import java.awt.Color;
import java.awt.Font;
import java.awt.Graphics2D;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.regex.Pattern;

import jxl.Range;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import net.sf.image4j.codec.bmp.BMPEncoder;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;

/**
 * 
 * 
 * @date 2014-5-16
 */
@Component
@SuppressWarnings({ "rawtypes", "unchecked" })
public class ExcelWriter {

	private WritableWorkbook book;

	private static final Logger LOG = Logger.getLogger(ExcelWriter.class);
	private FileInputStream fis = null;
	private File watermarkFileName = null;
	private int width = 480; // 水印图片的宽度
	private int height = 1020; // 水印图片的高度 因为设置其他的高度会有黑线,所以拉高高度

	public void createBook(String excelName, String path) {
		if (book == null) {
			try {
				book = Workbook
						.createWorkbook(new File(path + "\\" + excelName));
				watermarkFileName = new File(System.getProperty("user.dir")
						+ "/" + System.currentTimeMillis() + ".bmp");
			} catch (IOException e) {
				e.printStackTrace();
				LOG.error(e.getMessage(), e);
			}
		}
	}

	public void createBook(File file) {
		if (book == null) {
			try {
				if (file.exists()) {
					Workbook book1 = Workbook.getWorkbook(file);
					book = Workbook.createWorkbook(file, book1);
				} else {
					book = Workbook.createWorkbook(file);
					watermarkFileName = new File(System.getProperty("user.dir")
							+ "/" + System.currentTimeMillis() + ".bmp");
				}
			} catch (IOException e) {
				e.printStackTrace();
				LOG.error(e.getMessage(), e);
			} catch (BiffException e) {
				e.printStackTrace();
			}
		}
	}

	public void createBook(OutputStream os) {
		if (book == null) {
			try {
				book = Workbook.createWorkbook(os);
				watermarkFileName = new File(System.getProperty("user.dir")
						+ "/" + System.currentTimeMillis() + ".bmp");
			} catch (IOException e) {
				e.printStackTrace();
				LOG.error(e.getMessage(), e);
			}
		}
	}

	public void closeBook() {
		try {
			book.write();
			book.close();
			if (fis != null) {
				fis.close();
			}
			// 删除临时的水印文件
			if (watermarkFileName != null && watermarkFileName.exists()) {
				watermarkFileName.delete();
			}
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error(e.getMessage(), e);
		}
	}

	/**
	 * data <string[]>
	 * 
	 * @param data
	 * @param sheetName
	 */
	public void writerSheet(List data, String sheetName, String watermark) {
		WritableSheet sheet = book.createSheet(sheetName,
				book.getNumberOfSheets() + 1);

		Pattern p = Pattern
				.compile("(\\-|\\+)?[0-9]+\\.?[0-9]*((E|e)\\+[0-9]+)?");

		Pattern p1 = Pattern.compile("[0-9]{15,18}");// 身份证

		for (int i = 0; i < data.size(); i++) {
			String[] lines = (String[]) data.get(i);

			for (int j = 0; j < lines.length; j++) {
				try {
					if ("#rspan".equalsIgnoreCase(lines[j])) {

						// 判断是否是最后一个#rspan不是最后一个就直接pass
						if (i + 1 < data.size()) {
							String[] lines1 = (String[]) data.get(i + 1);

							if ("#rspan".equalsIgnoreCase(lines1[j])) {
								continue;
							}
						}
						// 再判断第一个rowspan的位置
						int rowOffset = 1;
						while (i - rowOffset > 0) {

							String[] lines1 = (String[]) data
									.get(i - rowOffset);
							if (!"#rspan".equalsIgnoreCase(lines1[j])) {
								break;
							}
							rowOffset++;
						}

						Range range = sheet.mergeCells(j, i - rowOffset, j, i);

						if (range.getTopLeft() instanceof Label) {
							Label cell = (Label) range.getTopLeft();
							WritableCellFormat wcf = new WritableCellFormat();
							wcf.setAlignment(Alignment.CENTRE);
							wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
							cell.setCellFormat(wcf);
						}
					} else if ("#cspan".equalsIgnoreCase(lines[j])) { // 判断是否是合并列
						// 判断是是合并Range的最后一个
						if (j + 1 < lines.length
								&& "#cspan".equalsIgnoreCase(lines[j + 1])) {
							continue;
						}

						int colOffset = 1;
						while (j - colOffset > 0) {
							if (!"#cspan"
									.equalsIgnoreCase(lines[j - colOffset])) {
								break;
							}
							colOffset++;
						}
						Range range = sheet.mergeCells(j - colOffset, i, j, i);
						if (range.getTopLeft() instanceof Label) {
							Label cell = (Label) range.getTopLeft();
							WritableCellFormat wcf = new WritableCellFormat();
							wcf.setAlignment(Alignment.CENTRE);
							cell.setCellFormat(wcf);
						}
					} else {
						WritableCell cell = null;
						String val = lines[j];
						if (val != null && p1.matcher(val).matches()) {
							cell = new Label(j, i, val);
						} else if (val != null && p.matcher(val).matches()) {
							cell = new jxl.write.Number(j, i,
									Double.parseDouble(val));
						} else {
							cell = new Label(j, i, val);
						}

						sheet.addCell(cell);
					}
				} catch (RowsExceededException e) {
					e.printStackTrace();
				} catch (WriteException e) {
					e.printStackTrace();
				}
			}

		}
		// 添加水印
		if (watermark != null && !"".equals(watermark)
				&& watermark.length() < 20) {
			try {
				File file = // new
							// File("C:/Users/Administrator/Downloads/test/kkkk.bmp");
				createWaterMark(watermark);
				byte[] imageByte = new byte[(int) file.length()];
				fis = new FileInputStream(file);
				fis.read(imageByte);
				sheet.setWaterMarkImage(imageByte, width, height);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * 生成水印图片
	 * 
	 * @param watermark
	 * @return
	 * @throws IOException
	 */
	public File createWaterMark(String watermark) throws IOException {

		// 获取bufferedImage对象
		BufferedImage bi = new BufferedImage(width, height,
				BufferedImage.TYPE_INT_RGB);
		// 处理背景色,设置为 白色
		int minx = bi.getMinX();
		int miny = bi.getMinY();
		for (int i = minx; i < width; i++) {
			for (int j = miny; j < height; j++) {
				bi.setRGB(i, j, 0xffffff);
			}
		}

		// 获取Graphics2d对象
		Graphics2D g2d = bi.createGraphics();
		// 设置字体颜色为灰色
		g2d.setColor(Color.LIGHT_GRAY);
		// 设置图片的属性
		g2d.setStroke(new BasicStroke(1));
		// 设置字体
		g2d.setFont(new Font("华文细黑", Font.ITALIC, 20));
		// 设置字体倾斜度
		g2d.rotate(Math.toRadians(-10));

		// 写入水印文字 原定高度过小,所以累计写水印,增加高度
		for (int i = 1; i < 26; i++) {
			g2d.drawString(watermark, 0, 40 * i);
		}
		// 设置透明度
		g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
		// 释放对象
		g2d.dispose();
		// 通过bmp写入文件
		BMPEncoder.write(bi, watermarkFileName);

		return watermarkFileName;
	}

	public static void main(String[] args) {
		ExcelWriter book = new ExcelWriter();
		// try {
		// book.createWaterMark("test");
		// } catch (IOException e) {
		// e.printStackTrace();
		// }
		List list = new java.util.ArrayList();

		String[] lines = { "105621", "2", "3", "excel带水印" };
		list.add(lines);
		book.createBook("out.xls", "d:\\");

		book.writerSheet(list, "excel带水印", "测试");

		// book.writerSheet(list, "我是傻逼2");

		book.closeBook();

		System.out.println("1.8e+04"
				.matches("(\\-|\\+)?[0-9]+\\.?[0-9]*((E|e)\\+[0-9]{2})?"));
	}
}