Excel 工具类 4.1.2 xlsx

image.png

1.poi版本

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
      </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>ooxml-schemas</artifactId>
            <version>1.4</version>
        </dependency>

2.水印工具类

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.AlphaComposite;
import java.awt.BasicStroke;
import java.awt.Color;
import java.awt.Font;
import java.awt.Graphics2D;
import java.awt.Transparency;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;

/**
 * 新增水印
 * 只支持XSSFWorkbook
 * .xlsx
 *
 * @author archie
 * @date 2024-02-02
 */
@Slf4j
public class ExcelWaterMark {

    public static ByteArrayOutputStream createWaterMark(String content) {
        int width = 200;
        int height = 150;
        // 获取bufferedImage对象
        BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
        String fontType = "微软雅黑";
        int fontStyle = Font.BOLD;
        int fontSize = 20;
        Font font = new Font(fontType, fontStyle, fontSize);
        // 获取Graphics2d对象
        Graphics2D g2d = image.createGraphics();
        image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
        g2d.dispose();
        g2d = image.createGraphics();
        //设置字体颜色和透明度，最后一个参数为透明度 设置字体
        g2d.setColor(new Color(0, 0, 0, 30));
        g2d.setStroke(new BasicStroke(1));
        g2d.setFont(font);
        // 设置字体类型  加粗 大小设置倾斜度
        g2d.rotate(-0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);
        FontRenderContext context = g2d.getFontRenderContext();
        Rectangle2D bounds = font.getStringBounds(content, context);
        double x = (width - bounds.getWidth()) / 2;
        double y = (height - bounds.getHeight()) / 2;
        double ascent = -bounds.getY();
        double baseY = y + ascent;
        // 写入水印文字原定高度过小，所以累计写水印，增加高度
        g2d.drawString(content, (int) x, (int) baseY);
        // 设置透明度
        g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
        // 释放对象
        g2d.dispose();
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            ImageIO.write(image, "png", os);
        } catch (IOException e) {
            log.error("写入水印图片失败", e);
            throw new RuntimeException(e);
        }
        return os;
    }


    /**
     * 为Excel打上水印工具函数
     *
     * @param sheet excel sheet
     * @param bytes 水印图片字节数组
     */
    public static void putWaterRemarkToExcel(XSSFSheet sheet, byte[] bytes) {
        //add relation from sheet to the picture data
        XSSFWorkbook workbook = sheet.getWorkbook();
        int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
        String rID = sheet.addRelation(null, XSSFRelation.IMAGES, workbook.getAllPictures().get(pictureIdx))
                .getRelationship().getId();
        //set background picture to sheet
        sheet.getCTWorksheet().addNewPicture().setId(rID);
    }
}

Excel导出工具类

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


/**
 * excel导出工具
 *
 * @author archie
 */
public class ExcelsXlsxUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelsXlsxUtil.class);


    /**
     * 时间格式
     */
    private static final String TIME_PATTERN = "yyyy-MM-dd HH:mm:ss";
    /**
     * 默认列的宽度
     */
    private static final int COLUMN_WIDTH = 24;
    /**
     * 表头字体大小
     */
    private static final int TITLE_FONT_SIZE = 11;
    /**
     * 表头单元格
     **/
    private static final int TITLE_CELL = 0;
    /**
     * 内容单元格
     **/
    private static final int CONTENT_CELL = 1;

    /**
     * 每个sheeet的最大数据数量
     */
    private static final int MAX_LENGTH = 60000;


    /**
     * 导出
     * 2003版本的xls
     *
     * @param title         表格标题名,文件名
     * @param headers       表格头部标题中文集合
     * @param headerZhWords 表格头部标题的字段名
     * @param dataSet       需要显示的数据集合
     */
    public static <T> void exportExcel(String title, String[] headers, String[] headerZhWords, Collection<T> dataSet,
                                       String waterMaker,
                                       HttpServletResponse response) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", String.format("attachment;filename=%s.xlsx",
                URLEncoder.encode(title, "UTF-8")));
        //水印文字
        byte[] wYBytes = new byte[0];
        if (!StringUtils.isEmpty(waterMaker)) {
            wYBytes = ExcelWaterMark.createWaterMark(waterMaker).toByteArray();
        }
        try (XSSFWorkbook workbook = new XSSFWorkbook();
             ServletOutputStream out = response.getOutputStream()) {
            int pageNum = 1;
            int dataLength = dataSet.size();
            List<T> dataAll = new ArrayList<>(dataSet);
            List<T> dataPage;
            //分sheet处理 每个sheet数据条数为MAX_LENGTH
            while (dataLength >= 0) {
                if (dataLength > MAX_LENGTH) {
                    dataPage = dataAll.subList((pageNum - 1) * MAX_LENGTH, pageNum * MAX_LENGTH);
                } else {
                    dataPage = dataAll.subList((pageNum - 1) * MAX_LENGTH, dataAll.size());
                }
                // 生成一个sheet
                XSSFSheet sheet = workbook.createSheet(String.format("%s -%s-", title, pageNum));
                dataLength -= MAX_LENGTH;
                pageNum++;
                // 设置表格默认列宽度
                sheet.setDefaultColumnWidth(COLUMN_WIDTH);
                // 生成标题样式
                CellStyle titleStyle = setCellStyle(workbook, TITLE_CELL);
                // 产生表格标题行
                XSSFRow row = sheet.createRow(0);
                XSSFCell cellHeader;
                for (int i = 0; i < headers.length; i++) {
                    cellHeader = row.createCell(i);
                    cellHeader.setCellStyle(titleStyle);
                    cellHeader.setCellValue(new XSSFRichTextString(headers[i]));
                }
                //填充内容
                fillContent(headerZhWords, dataPage, workbook, sheet, 0);
                if (wYBytes.length > 0) {
                    ExcelWaterMark.putWaterRemarkToExcel(sheet, wYBytes);
                }
            }
            workbook.write(out);
        } catch (IOException e) {
            logger.error("excel生成错误", e);
        }
    }

    /**
     * 设置样式
     **/
    private static CellStyle setCellStyle(XSSFWorkbook workbook, int cellType) {
        CellStyle style = null;
        //边框
        BorderStyle borderStyle = BorderStyle.THIN;
        short borderColor = HSSFColor.HSSFColorPredefined.BLACK.getIndex();
        if (cellType == TITLE_CELL) {
            style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setBorderBottom(borderStyle);
            style.setBorderLeft(borderStyle);
            style.setBorderRight(borderStyle);
            style.setBorderTop(borderStyle);
            style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            style.setTopBorderColor(borderColor);
            style.setLeftBorderColor(borderColor);
            style.setRightBorderColor(borderColor);
            style.setBottomBorderColor(borderColor);
            //垂直居中
            style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            //自动换行
            style.setWrapText(false);
            // 生成标题字体
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontName("宋体");
            font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
            font.setFontHeightInPoints((short) TITLE_FONT_SIZE);
            // 把字体应用到当前的样式
            style.setFont(font);
        } else if (cellType == CONTENT_CELL) {
            style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AUTOMATIC.getIndex());
            style.setFillPattern(FillPatternType.NO_FILL);
            style.setBorderBottom(borderStyle);
            style.setBorderLeft(borderStyle);
            style.setBorderRight(borderStyle);
            style.setBorderTop(borderStyle);
            style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置自动换行
            style.setWrapText(false);
            // 生成内容字体
            Font font = workbook.createFont();
            font.setBold(false);
            style.setFont(font);
        }
        return style;
    }

    /**
     * 填充sheet内容
     *
     * @param headerWords       填充数据的字段
     * @param dataSet           数据集
     * @param workbook          工作上下文
     * @param sheet             sheet
     * @param contentStartIndex 从第contentStartIndex+1行开始填充数据
     */
    private static <T> void fillContent(String[] headerWords, Collection<T> dataSet, XSSFWorkbook workbook, XSSFSheet sheet,
                                        Integer contentStartIndex) {
        //内容样式
        CellStyle contentStyle = setCellStyle(workbook, CONTENT_CELL);
        SimpleDateFormat sdf = new SimpleDateFormat(TIME_PATTERN);
        //字段名
        String fieldName;
        //get方法名称
        String getMethodName;
        //单元格
        XSSFCell cell;
        Method getMethod;
        Object value = "";
        XSSFRow row;
        contentStartIndex = contentStartIndex == null ? 0 : contentStartIndex;
        T t;
        int index = 0;
        Iterator<T> it = dataSet.iterator();
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index + contentStartIndex);
            t = it.next();
            for (int i = 0; i < headerWords.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(contentStyle);
                fieldName = headerWords[i];
                if (t instanceof Map) {
                    //Map类型
                    value = ((Map<?, ?>) t).get(fieldName);
                } else {
                    getMethodName = getBeanMethodName(fieldName);
                    //Bean类
                    try {
                        getMethod = t.getClass().getMethod(getMethodName);
                        value = getMethod.invoke(t);
                    } catch (SecurityException | NoSuchMethodException | IllegalAccessException |
                             InvocationTargetException e) {
                        logger.error("excel生成错误", e);
                    }
                }
                if (null == value) {
                    cell.setCellValue("");
                } else if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof Float || value instanceof Double) {
                    cell.setCellValue(String.valueOf(value));
                } else if (value instanceof Long) {
                    cell.setCellValue((Long) value);
                } else if (value instanceof Date) {
                    cell.setCellValue(sdf.format((Date) value));
                } else {
                    cell.setCellValue(String.valueOf(value));
                }
            }
        }
    }

    /**
     * 获取bean的获取值的方法
     *
     * @param fieldName
     * @return
     */
    private static String getBeanMethodName(String fieldName) {
        String cacheName = CommonConstant.CachePre.METHOD + fieldName;
        String methodName = String.valueOf(CommonConstant.BaseCache.COMMON_CACHE.get(cacheName));
        if (CommonConstant.BaseConstant.NULL.equals(methodName)) {
            methodName = "get" + fieldName.substring(0, 1).toUpperCase()
                    + fieldName.substring(1);
            CommonConstant.BaseCache.COMMON_CACHE.put(cacheName, methodName);
        }
        return methodName;
    }
}