gpt-3.5-turbo-0301· openai
Suzhen/CodeChat
java使用阿里巴巴的easyexcel如何导出含有水印的excel
- User
java使用阿里巴巴的easyexcel如何导出含有水印的excel
- Assistant
使用EasyExcel导出含有水印的Excel文件,可以按照以下步骤进行:
- 导入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>- 创建自定义的水印样式类
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; } }- 在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(); } }- 调用导出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文件中加入自定义的水印了。
- User
java使用阿里巴巴的easyexcel如何导出含有水印的excel
- Assistant
使用EasyExcel导出含有水印的Excel文件,可以按照以下步骤进行:
- 导入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>- 创建自定义的水印样式类
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; } }- 在写数据之前,先设置水印样式
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文件中添加自定义的水印了。