Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· openaiHugging Face Suzhen/CodeChat

java使用阿里巴巴的easyexcel如何导出含有水印的excel

  1. User

    java使用阿里巴巴的easyexcel如何导出含有水印的excel

  2. Assistant

    使用EasyExcel导出含有水印的Excel文件,可以按照以下步骤进行:

    1. 导入EasyExcel和poi-ooxml-schemas依赖
    <!--Excel导入导出工具-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.6</version>
    </dependency>
    <!--poi-ooxml-schemas依赖-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>4.1.2</version>
    </dependency>
    
    1. 创建自定义的水印样式类
    public class WaterMarkStyle {
    
        private static XSSFFont FONT = null;
        private static XSSFCellStyle STYLE = null;
    
        /**
         * 初始化水印样式
         * @param wb
         * @param text 水印文字
         * @return
         */
        public static XSSFCellStyle initStyle(XSSFWorkbook wb, String text) {
            // 1. 创建字体对象
            if (FONT == null) {
                FONT = wb.createFont();
                FONT.setFontName("黑体"); // 字体
                FONT.setFontHeightInPoints((short) 18); // 字号
                FONT.setBold(true); // 加粗
            }
    
            // 2. 创建水印单元格样式对象
            if (STYLE == null) {
                STYLE = wb.createCellStyle();
                STYLE.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
                STYLE.setAlignment(HorizontalAlignment.CENTER); // 水平居中
            }
    
            // 3. 创建水印文字
            XSSFRichTextString textString = new XSSFRichTextString(text);
            textString.applyFont(FONT); // 添加字体
            // 4. 创建水印
            Drawing drawing = sheet.createDrawingPatriarch();
            int width = sheet.getColumnWidth(0);
            int height = sheet.getColumnWidth(sheet.getLastRowNum());
            // 定义位置,以及旋转角度,宽度和高度
            ClientAnchor anchor = new XSSFClientAnchor(0, 0, width, height, (short) 4, 6, (short) 6, 12);
            // 创建注释
            Comment comment = drawing.createCellComment(anchor);
            comment.setString(textString);
            comment.setVisible(false); // 不可见
            // 注释加入单元格样式
            STYLE.setComment(comment);
    
            return STYLE;
        }
    }
    
    1. 在Excel写入数据之前,先设置水印样式
    public class ExportExcelService {
    
        public void exportExcel(String fileName, List<Object> dataList, String sheetName, String waterMarkText) throws IOException {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet(sheetName);
    
            // 设置水印样式
            WaterMarkStyle.initStyle(workbook, waterMarkText);
    
            // 写入数据
            ExcelWriter writer = EasyExcel.write(fileName).withTemplate(templatePath).build();
            Sheet sheet1 = writer.getSheet();
            writer.write(dataList, sheet1);
            writer.finish();
    
            // 导出Excel
            FileOutputStream fileOutputStream = new FileOutputStream(fileName);
            workbook.write(fileOutputStream);
            fileOutputStream.close();
        }
    }
    
    1. 调用导出Excel的方法
    public static void main(String[] args) {
        List<Object> dataList = new ArrayList<Object>();
        String fileName = "导出的Excel.xlsx";
        String sheetName = "工作表1";
        String waterMarkText = "测试水印";
        try {
            new ExportExcelService().exportExcel(fileName, dataList, sheetName, waterMarkText);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    

    这样,就可以在导出的Excel文件中加入自定义的水印了。

  3. User

    java使用阿里巴巴的easyexcel如何导出含有水印的excel

  4. Assistant

    使用EasyExcel导出含有水印的Excel文件,可以按照以下步骤进行:

    1. 导入EasyExcel和poi-ooxml-schemas依赖
    <!--Excel导入导出工具-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.6</version>
    </dependency>
    <!--poi-ooxml-schemas依赖-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>1.3.0</version>
    </dependency>
    
    1. 创建自定义的水印样式类
    public class WaterMarkStyle {
    
        private static XSSFFont FONT = null;
        private static XSSFCellStyle STYLE = null;
    
        /**
         * 初始化水印样式
         *
         * @param wb   工作簿
         * @param text 水印文字
         * @return
         */
        public static XSSFCellStyle initStyle(XSSFWorkbook wb, String text) {
            // 1. 创建字体对象
            if (FONT == null) {
                FONT = wb.createFont();
                FONT.setFontName("宋体");     // 字体
                FONT.setFontHeightInPoints((short) 14);
                FONT.setColor(IndexedColors.GREY_50_PERCENT.getIndex());
            }
    
            // 2. 创建水印单元格样式对象
            if (STYLE == null) {
                STYLE = wb.createCellStyle();
                STYLE.setVerticalAlignment(VerticalAlignment.CENTER);
                STYLE.setAlignment(HorizontalAlignment.CENTER);
            }
    
            // 3. 创建水印文字
            XSSFRichTextString textString = new XSSFRichTextString(text);
            textString.applyFont(FONT);
    
            // 4. 创建水印
            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 2, 2, (short) 8, 8); // 绘图锚定位置
            Comment comment = drawing.createCellComment(anchor);
            comment.setString(textString);
            comment.setVisible(false);
    
            // 注释加入单元格样式
            STYLE.setComment(comment);
    
            return STYLE;
        }
    }
    
    1. 在写数据之前,先设置水印样式
    public class ExcelWriterDemo {
    
        public static void main(String[] args) throws IOException {
            String fileName = "含水印的Excel文件" + System.currentTimeMillis() + ".xlsx";
            OutputStream out = new FileOutputStream(fileName);
            EasyExcel.write(out)
                    .registerWriteHandler(new CustomCellWriteHandler(WaterMarkStyle.initStyle(new XSSFWorkbook(), "测试水印")))
                    .head(head())
                    .sheet("模板")
                    .doWrite(list());
            out.close();
        }
    
        private static List<List<String>> head() {
            List<List<String>> list = new ArrayList<>();
            List<String> head0 = new ArrayList<>();
            head0.add("姓名");
            List<String> head1 = new ArrayList<>();
            head1.add("年龄");
            list.add(head0);
            list.add(head1);
            return list;
        }
    
        private static List<List<Object>> list() {
            List<List<Object>> list = new ArrayList<>();
            for (int i = 0; i < 10; i++) {
                List<Object> rowList = new ArrayList<>();
                rowList.add("张三" + i);
                rowList.add(20 + i);
                list.add(rowList);
            }
            return list;
        }
    }
    

    注:此处使用了自定义的CustomCellWriteHandler,其主要是因为EasyExcel没有提供直接设置单元格注释的方法,详见issue。

    /**
     * 自定义单元格写操作处理器,用于设置水印注释
     */
    public class CustomCellWriteHandler extends AbstractCellWriteHandler {
    
        /**
         * 自定义的单元格样式,包含水印注释
         */
        private XSSFCellStyle customCellStyle;
    
        /**
         * @param customCellStyle 自定义的单元格样式,包含水印注释
         */
        public CustomCellWriteHandler(XSSFCellStyle customCellStyle) {
            this.customCellStyle = customCellStyle;
        }
    
        @Override
        public void afterCellDispose(WriteHandlerContext context) {
            XSSFCell cell = (XSSFCell) context.getCell();
            cell.setCellStyle(customCellStyle);
        }
    }
    

    这样,就可以在导出的Excel文件中添加自定义的水印了。