SpringBoot+POI+JXL实现excel导出并添加水印功能

1.Pom引入

   <dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.9</version>
	</dependency>
	<dependency>
		<groupId>net.sourceforge.jexcelapi</groupId>
		<artifactId>jxl</artifactId>
		<version>2.6</version>
	</dependency>
注意：这里的jxl的jar包是基于2.6.12改进后的包，
下载地址：https://download.csdn.net/download/redsnower_1/3529559

实现过程，部分代码如下

public Void exportBillReport(XXXXReq request, HttpServletResponse response) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(“xxxx”);
//设置是否受保护
sheet.protectSheet(UUID.randomUUID().toString());
PoiUtil.createTitle(workbook, sheet, request.getFields(), urlsProperties.getPictureTitle());

    List<String> columns = request.getColumns();
    //resultSets 为你要导出的信息，自行获取
    List<JSONObject> resultSets = XXXX.getResult();
    //设置日期格式
    int rowNum = 2;
    for (JSONObject jsonObject : resultSets) {
        HSSFRow row = sheet.createRow(rowNum);
        for (int i = 0; i < columns.size(); i++) {
            row.createCell(i).setCellValue(jsonObject.get(columns.get(i)).toString());
        }
        rowNum++;
    }


    String fileName = FINE_NAME;
    Model model = new Model("XXX", new Date());

    try {
        JSONObject jo = WaterMarkUtil.modelToJSON(model);
        //将poi生成的表转成输入流写入水印
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        workbook.write(os);
        ByteArrayInputStream is = new ByteArrayInputStream(os.toByteArray());
        InputStream in = ExcelUtil.addWatermark(is, urlsProperties.getOutFile(), jo, urlsProperties.getWaterMark());

        Workbook wb = WorkbookFactory.create(in);
        wb.write(generateResponseExcel(fileName, response));

    } catch (Exception e) {
        log.error("error while operating database, exception is {}", e);
        throw new DataParseException(CommonResult.DATAEXCEPTION.getKey(),
                CommonResult.DATAEXCEPTION.getValue());
    }
}

poiutil类:
package com.ai.bssquery.util;

import org.apache.poi.hssf.usermodel.;
import org.apache.poi.ss.usermodel.;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;

import java.io.*;
import java.util.List;

public class PoiUtil {

private static String SHEET_TITLE = "政企CRM_ABB个性化账单";

//创建表头
public static void createTitle(HSSFWorkbook workbook, HSSFSheet sheet, List<String> fields, String bgUrl) {
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    HSSFRow row1 = sheet.createRow(0);
    String url = bgUrl;

    HSSFCellStyle titleStyle = workbook.createCellStyle();
    HSSFFont titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 22);
    titleFont.setFontName("黑体");
    titleStyle.setFont(titleFont);
    titleStyle.setAlignment(CellStyle.ALIGN_CENTER);

    HSSFCell cell1;
    cell1 = row1.createCell(0, HSSFCell.CELL_TYPE_STRING);
    row1.setHeightInPoints(40);
    cell1.setCellValue(" ");
    CellRangeAddress pic = new CellRangeAddress(0, 0, 0, 5);
    sheet.addMergedRegion(pic);
    drawPictureInfoExcel(workbook, patriarch, url, 0);

    cell1 = row1.createCell(6);
    cell1.setCellStyle(titleStyle);
    cell1.setCellValue(SHEET_TITLE);
    CellRangeAddress region = new CellRangeAddress(0, 0, 6, 36);
    sheet.addMergedRegion(region);


    HSSFRow row = sheet.createRow(1);
    sheet.setColumnWidth(1, 12 * 256);
    sheet.setColumnWidth(3, 17 * 256);


    //设置格式
    HSSFCellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 12);
    font.setFontName("黑体");
    style.setFont(font);
    style.setAlignment(CellStyle.ALIGN_CENTER);

    HSSFCell cell;
    for (int i = 0; i < fields.size(); i++) {

        cell = row.createCell(i);
        cell.setCellValue(fields.get(i));
        cell.setCellStyle(style);
    }

}

private static void drawPictureInfoExcel(HSSFWorkbook wb, HSSFPatriarch patriarch, String pictureUrl, int rowIndex) {
    //rowIndex代表当前行
    try {
        if (pictureUrl != null) {
            BufferedImage bufferImg = null;
            bufferImg = ImageIO.read(new File(pictureUrl));
            //得到图片的二进制数据，以二进制封装得到数据，具有通用性
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ImageIO.write(bufferImg, "png", os);
            InputStream input = new ByteArrayInputStream(os.toByteArray());
            byte[] data = readInputStream(input);
            //anchor主要用于设置图片的属性
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 250, (short) 0, rowIndex, (short) 5,
                    rowIndex);
            //Sets the anchor type （图片在单元格的位置）
            //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.
            anchor.setAnchorType(0);
            patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
        }
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

private static byte[] readInputStream(InputStream inStream) throws Exception {
    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    //创建一个Buffer字符串
    byte[] buffer = new byte[1024];
    //每次读取的字符串长度，如果为-1，代表全部读取完毕
    int len = 0;
    //使用一个输入流从buffer里把数据读取出来
    while ((len = inStream.read(buffer)) != -1) {
        //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
        outStream.write(buffer, 0, len);
    }
    //关闭输入流
    inStream.close();
    //把outStream里的数据写入内存
    return outStream.toByteArray();
}

}
excelUtil类：
package com.ai.bssquery.util;

import java.io.*;
import java.util.Date;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;

import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelUtil {

/**
 *
 * @param watermarkText
 * @throws Exception
 */
@SuppressWarnings("rawtypes")
public static InputStream addWatermark(InputStream is, String xls_filePath_out, JSONObject watermarkText, String filName) throws Exception{

    Workbook wb = Workbook.getWorkbook(is); // 获得原始文档
    WritableWorkbook wwb = Workbook.createWorkbook(new File(xls_filePath_out),wb);
    WritableSheet ws1 = wwb.getSheet(0);// 得到工作薄中的第一个工作表
    //String watermarkMessage = jsonObject.toString();
    Map watermarkMessage = WaterMarkUtil.toMap(watermarkText.toString());
    Map map = WaterMarkUtil.createWaterMark1(watermarkMessage, filName);
    File fileImg = (File) map.get("file");

    byte imageData[] = new byte[(int) fileImg.length()];
    FileInputStream fis = new FileInputStream(fileImg);
    fis.read(imageData);

    ws1.setWaterMarkImage(imageData, (int)map.get("width"), (int)map.get("height"));
    wwb.write();
    wwb.close();
    fis.close();

    InputStream in = new FileInputStream(new File(xls_filePath_out));
    return in;
}

}