Freemarker整合poi导出带有图片的Excel教程

最新推荐文章于 2024-04-28 15:15:25 发布

大脑补丁

最新推荐文章于 2024-04-28 15:15:25 发布

阅读量4.9k

点赞数 15

分类专栏： Java 文章标签： freemarker freemarker导出图片 java导出Excel图片 poi导出Excel图片 Freemarker导出xls

本文链接：https://blog.csdn.net/x541211190/article/details/105675771

版权

Java 专栏收录该内容

25 篇文章 17 订阅

订阅专栏

序言

前一篇文章《Freemarker导出复杂Excel图文教程》，讲解了使用Freemarker导出复杂的Excel，本文将在前文的基础之上，讲解使用Freemarker导出Excel后，在整合poi插入图片到Excel，从而实现Freemarker导出带有图片的Excel工具。
在这里插入图片描述

为什么要做这个工具呢？

因为Freemarker是不支持导出带有图片的Excel的，不支持的原因是Freemarker导出的Excel为xml格式，此格式所有的富文本信息都会丢失，只留下文本内容，所以不要在妄想用Freemarker直接导出带有图片的Excel了，但是Freemarker是可以导出带有图片的Word。基于此种需求，做此工具导出带有图片的Excel。

本教程实现的功能

1.支持Freemarker导出Excel的所有功能（完美导出复杂的合并单元格、合并行和列、颜色、字体等）。
2.支持导出带有图片的Excel。
3.支持多Sheet页导出。
4.支持导出单元格注释。
5.支持导出文件格式支持.xls、.xlsx、.xml三种格式（绝非通过.xml重命名实现。.xml重命名为.xls，会有弹框报错，不友好）。

导出步骤

使用Freemarker将Excel中文本信息导出。参考前文：《Freemarker导出复杂Excel图文教程》
将xml格式的Excel转化为xls格式（最复杂步骤，绝非改个后缀名那么简单）。
通过poi将图片插入到Excel指定位置。

导出Excel效果图

在这里插入图片描述

下载全套源码及示例

项目仓库	下载地址
CSDN仓库	点击下载
Github仓库	点击下载

注：CSDN下载物价上涨太快了，所以我还是提供个免费下载地址吧，给大家省钱买鸡腿。

一.快速开始

从一个示例开始，演示下如何使用此工具。你只需要构建FreemakerInput对象，将做好的Freemarker模板放到template中，就可以调用工具类导出Excel了。用法是不是很简单。示例：

    /**
     * 导出带有图片的Excel示例
     * 
     */
    public void export() {
        String imagePath = "";
		List<ExcelImageInput> excelImageInputs = new ArrayList<>();
		try {
			Enumeration<URL> urlEnumeration = this.getClass().getClassLoader().getResources("templates/image.png");
			URL url = urlEnumeration.nextElement();
			imagePath = url.getPath();
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 若改变图片位置，修改后4个参数
		XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 16, 1, (short) 26, 27);
		ExcelImageInput excelImageInput = new ExcelImageInput(imagePath, 0, anchor);
		excelImageInputs.add(excelImageInput);
		FreemarkerInput freemarkerInput = new FreemarkerInput();
		freemarkerInput.setTemplateName("发票.ftl");
		freemarkerInput.setTemplateFilePath("");
		freemarkerInput.setDataMap(getExcelData());
		freemarkerInput.setXmlTempFile("export/temp/");
		// 若导出不带图片的Excel，此参数为空即可
		freemarkerInput.setExcelImageInputs(excelImageInputs);
		freemarkerInput.setFileName("导出带图片Excel缓存文件");
		// 导出到项目所在目录下，export文件夹中
		FreemarkerUtils.exportImageExcelNew("export/带图片(2007版).xlsx", freemarkerInput);
    }

    private Map<String, Object> getExcelData() {
          // 模拟Excel假数据，省略..
      }
}

二.导出Excel工具类讲解

提供了两种导出图片的方式，一种导出到本地硬盘；另一种导出到HttpServletResponse中，即在接口中使用。推荐使用以下两个方法，导出.xlsx方式，兼容性最好，性能最佳

 /**
	 * 导出到文件中（导出到硬盘，xlsx格式）
	 *
	 * @param excelFilePath
	 * @param freemakerEntity
	 * @author 大脑补丁 on 2020-04-14 15:34
	 */
	public static void exportImageExcelNew(String excelFilePath, FreemarkerInput freemakerEntity) {
		try {
			File file = new File(excelFilePath);
			FileUtils.forceMkdirParent(file);
			FileOutputStream outputStream = new FileOutputStream(file);
			createExcelToStream(freemakerEntity, outputStream);
			// 删除xml缓存文件
			FileUtils.forceDelete(new File(freemakerEntity.getXmlTempFile() + freemakerEntity.getFileName() + ".xml"));
			log.info("导出成功,导出到目录：" + file.getCanonicalPath());
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

   /**
	 * 导出到response输出流中(用于浏览器调用接口,支持Excel2007版，xlsx格式)
	 *
	 * @param excelFilePath
	 * @param freemakerInput
	 * @author 大脑补丁 on 2020-04-14 15:34
	 */
	public static void exportImageExcelNew(HttpServletResponse response, FreemarkerInput freemakerInput) {
		try {
			OutputStream outputStream = response.getOutputStream();
			// 写入excel文件
			response.reset();
			response.setContentType("application/msexcel;charset=UTF-8");
			response.setHeader("Content-Disposition",
					"attachment;filename=\"" + new String((freemakerInput.getFileName() + ".xls").getBytes("GBK"),
							"ISO8859-1") + "\"");
			response.setHeader("Response-Type", "Download");
			createExcelToStream(freemakerInput, outputStream);
			// 删除xml缓存文件
			FileUtils.forceDelete(new File(freemakerInput.getXmlTempFile() + freemakerInput.getFileName() + ".xml"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

工具类为FreemarkerUtils.java，完整的工具类代码：

package com.study.commons.utils;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
import com.study.dto.freemarker.input.ExcelImageInput;
import com.study.dto.freemarker.input.FreemarkerInput;
import com.study.entity.excel.Cell;
import com.study.entity.excel.Row;
import com.study.entity.excel.Table;
import com.study.entity.excel.*;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateExceptionHandler;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.dom4j.Document;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Map;

/**
 * @author 大脑补丁
 * @project freemarker-excel
 * @description: freemarker工具类
 * @create 2020-04-14 09:43
 */
public class FreemarkerUtils {

	private static final Logger log = LoggerFactory.getLogger(FreemarkerUtils.class);

	/**
	 * 导出Excel到指定文件中
	 *
	 * @param dataMap          数据源
	 * @param templateName     模板名称（包含文件后缀名.ftl）
	 * @param templateFilePath 模板所在路径（不能为空，当前路径传空字符：""）
	 * @param fileFullPath     文件完整路径（如：usr/local/fileName.xls）
	 * @author 大脑补丁 on 2020-04-05 11:51
	 */
	@SuppressWarnings("rawtypes")
	public static void exportToFile(Map dataMap, String templateName, String templateFilePath, String fileFullPath) {
		try {
			File file = new File(fileFullPath);
			FileUtils.forceMkdirParent(file);
			FileOutputStream outputStream = new FileOutputStream(file);
			exportToStream(dataMap, templateName, templateFilePath, outputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 导出Excel到输出流
	 *
	 * @param dataMap          数据源
	 * @param templateName     模板名称（包含文件后缀名.ftl）
	 * @param templateFilePath 模板所在路径（不能为空，当前路径传空字符：""）
	 * @param outputStream     输出流
	 * @author 大脑补丁 on 2020-04-05 11:52
	 */
	@SuppressWarnings("rawtypes")
	public static void exportToStream(Map dataMap, String templateName, String templateFilePath,
			FileOutputStream outputStream) {
		try {
			Template template = getTemplate(templateName, templateFilePath);
			OutputStreamWriter outputWriter = new OutputStreamWriter(outputStream, "UTF-8");
			Writer writer = new BufferedWriter(outputWriter);
			template.process(dataMap, writer);
			writer.flush();
			writer.close();
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 导出到文件中（导出到硬盘，xls格式）
	 *
	 * @param excelFilePath
	 * @param freemakerEntity
	 * @author 大脑补丁 on 2020-04-14 15:34
	 */
	public static void exportImageExcel(String excelFilePath, FreemarkerInput freemakerEntity) {
		try {
			File file = new File(excelFilePath);
			FileUtils.forceMkdirParent(file);
			FileOutputStream outputStream = new FileOutputStream(file);
			createImageExcleToStream(freemakerEntity, outputStream);
			// 删除xml缓存文件
			FileUtils.forceDelete(new File(freemakerEntity.getXmlTempFile() + freemakerEntity.getFileName() + ".xml"));
			log.info("导出成功,导出到目录：" + file.getCanonicalPath());
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 导出到文件中（导出到硬盘，xlsx格式）
	 *
	 * @param excelFilePath
	 * @param freemakerEntity
	 * @author 大脑补丁 on 2020-04-14 15:34
	 */
	public static void exportImageExcelNew(String excelFilePath, FreemarkerInput freemakerEntity) {
		try {
			File file = new File(excelFilePath);
			FileUtils.forceMkdirParent(file);
			FileOutputStream outputStream = new FileOutputStream(file);
			createExcelToStream(freemakerEntity, outputStream);
			// 删除xml缓存文件
			FileUtils.forceDelete(new File(freemakerEntity.getXmlTempFile() + freemakerEntity.getFileName() + ".xml"));
			log.info("导出成功,导出到目录：" + file.getCanonicalPath());
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 导出到response输出流中(用于浏览器调用接口,支持Excel2007版，xlsx格式)
	 *
	 * @param excelFilePath
	 * @param freemakerInput
	 * @author 大脑补丁 on 2020-04-14 15:34
	 */
	public static void exportImageExcelNew(HttpServletResponse response, FreemarkerInput freemakerInput) {
		try {
			OutputStream outputStream = response.getOutputStream();
			// 写入excel文件
			response.reset();
			response.setContentType("application/msexcel;charset=UTF-8");
			response.setHeader("Content-Disposition",
					"attachment;filename=\"" + new String((freemakerInput.getFileName() + ".xls").getBytes("GBK"),
							"ISO8859-1") + "\"");
			response.setHeader("Response-Type", "Download");
			createExcelToStream(freemakerInput, outputStream);
			// 删除xml缓存文件
			FileUtils.forceDelete(new File(freemakerInput.getXmlTempFile() + freemakerInput.getFileName() + ".xml"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 导出到response输出流中(用于浏览器调用接口,支持Excel2003版，xls格式)
	 *
	 * @param excelFilePath
	 * @param freemakerInput
	 * @author 大脑补丁 on 2020-04-14 15:34
	 */
	public static void exportImageExcel(HttpServletResponse response, FreemarkerInput freemakerInput) {
		try {
			OutputStream outputStream = response.getOutputStream();
			// 写入excel文件
			response.reset();
			response.setContentType("application/msexcel;charset=UTF-8");
			response.setHeader("Content-Disposition",
					"attachment;filename=\"" + new String((freemakerInput.getFileName() + ".xls").getBytes("GBK"),
							"ISO8859-1") + "\"");
			response.setHeader("Response-Type", "Download");
			createImageExcleToStream(freemakerInput, outputStream);
			// 删除xml缓存文件
			FileUtils.forceDelete(new File(freemakerInput.getXmlTempFile() + freemakerInput.getFileName() + ".xml"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 获取项目templates文件夹下的模板
	private static Template getTemplate(String templateName, String filePath) throws IOException {
		Configuration configuration = new Configuration(Configuration.VERSION_2_3_28);
		configuration.setDefaultEncoding("UTF-8");
		configuration.setTemplateUpdateDelayMilliseconds(0);
		configuration.setEncoding(Locale.CHINA, "UTF-8");
		configuration.setTemplateExceptionHandler(TemplateExceptionHandler.RETHROW_HANDLER);
		configuration.setClassForTemplateLoading(FreemarkerUtils.class, "/templates" + filePath);
		configuration.setOutputEncoding("UTF-8");
		return configuration.getTemplate(templateName, "UTF-8");
	}

	/**
	 * 导出Excel到输出流（支持Excel2003版，xls格式）
	 *
	 * @param freemakerEntity
	 * @param outputStream
	 */
	private static void createImageExcleToStream(FreemarkerInput freemakerEntity, OutputStream outputStream) {
		Writer out = null;
		try {
			// 创建xml文件
			Template template = getTemplate(freemakerEntity.getTemplateName(), freemakerEntity.getTemplateFilePath());
			File tempXMLFile = new File(freemakerEntity.getXmlTempFile() + freemakerEntity.getFileName() + ".xml");
			FileUtils.forceMkdirParent(tempXMLFile);
			out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tempXMLFile), "UTF-8"));
			template.process(freemakerEntity.getDataMap(), out);
			if (log.isDebugEnabled()) {
				log.debug("1.完成将文本数据导入到XML文件中");
			}
			SAXReader reader = new SAXReader();
			Document document = reader.read(tempXMLFile);
			Map<String, Style> styleMap = readXmlStyle(document);
			log.debug("2.完成解析XML中样式信息");
			List<Worksheet> worksheets = readXmlWorksheet(document);
			if (log.isDebugEnabled()) {
				log.debug("3.开始将XML信息写入Excel，数据为：" + worksheets.toString());
			}
			HSSFWorkbook wb = new HSSFWorkbook();
			for (Worksheet worksheet : worksheets) {
				HSSFSheet sheet = wb.createSheet(worksheet.getName());
				Table table = worksheet.getTable();
				List<Row> rows = table.getRows();
				List<Column> columns = table.getColumns();
				// 填充列宽
				int columnIndex = 0;
				for (int i = 0; i < columns.size(); i++) {
					Column column = columns.get(i);
					columnIndex = getCellWidthIndex(columnIndex, i, column.getIndex());
					sheet.setColumnWidth(columnIndex, (int) column.getWidth() * 50);
				}
				int createRowIndex = 0;
				List<CellRangeAddressEntity> cellRangeAddresses = new ArrayList<>();
				for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
					Row rowInfo = rows.get(rowIndex);
					if (rowInfo == null) {
						continue;
					}
					createRowIndex = getIndex(createRowIndex, rowIndex, rowInfo.getIndex());
					HSSFRow row = sheet.createRow(createRowIndex);
					if (rowInfo.getHeight() != null) {
						Integer height = rowInfo.getHeight() * 20;
						row.setHeight(height.shortValue());
					}
					List<Cell> cells = rowInfo.getCells();
					if (CollectionUtils.isEmpty(cells)) {
						continue;
					}
					int startIndex = 0;
					for (int cellIndex = 0; cellIndex < cells.size(); cellIndex++) {
						Cell cellInfo = cells.get(cellIndex);
						if (cellInfo == null) {
							continue;
						}
						// 获取起始列
						startIndex = getIndex(startIndex, cellIndex, cellInfo.getIndex());
						HSSFCell cell = row.createCell(startIndex);
						String styleID = cellInfo.getStyleID();
						Style style = styleMap.get(styleID);
						/*设置数据单元格格式*/
						CellStyle dataStyle = wb.createCellStyle();
						// 设置边框样式
						setBorder(style, dataStyle);
						// 设置对齐方式
						setAlignment(style, dataStyle);
						// 填充文本
						setValue(wb, cellInfo, cell, style, dataStyle);
						// 填充颜色
						setCellColor(style, dataStyle);
						cell.setCellStyle(dataStyle);
						//单元格注释
						if (cellInfo.getComment() != null) {
							Data data = cellInfo.getComment().getData();
							Comment comment = sheet.createDrawingPatriarch()
									.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
							comment.setString(new HSSFRichTextString(data.getText()));
							cell.setCellComment(comment);
						}
						// 合并单元格
						startIndex = getCellRanges(createRowIndex, cellRangeAddresses, startIndex, cellInfo, style);
					}
				}
				// 添加合并单元格
				addCellRange(sheet, cellRangeAddresses);
			}
			// 加载图片到excel
			log.debug("4.开始写入图片：" + freemakerEntity.getExcelImageInputs());
			if (!CollectionUtils.isEmpty(freemakerEntity.getExcelImageInputs())) {
				writeImageToExcel(freemakerEntity.getExcelImageInputs(), wb);
			}
			log.debug("5.完成写入图片：" + freemakerEntity.getExcelImageInputs());
			// 写入excel文件,response字符流转换成字节流，template需要字节流作为输出
			wb.write(outputStream);
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("导出excel异常：" + e.getMessage());
		} finally {
			try {
				out.close();
			} catch (Exception e) {

			}
		}
	}

	/**
	 * 导出Excel到输出流（支持Excel2007版，xlsx格式）
	 *
	 * @param freemakerEntity
	 * @param outputStream
	 */
	private static void createExcelToStream(FreemarkerInput freemakerEntity, OutputStream outputStream) {
		Writer out = null;
		try {
			// 创建xml文件
			Template template = getTemplate(freemakerEntity.getTemplateName(), freemakerEntity.getTemplateFilePath());
			File tempXMLFile = new File(freemakerEntity.getXmlTempFile() + freemakerEntity.getFileName() + ".xml");
			FileUtils.forceMkdirParent(tempXMLFile);
			out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tempXMLFile), "UTF-8"));
			template.process(freemakerEntity.getDataMap(), out);
			if (log.isDebugEnabled()) {
				log.debug("1.完成将文本数据导入到XML文件中");
			}
			SAXReader reader = new SAXReader();
			Document document = reader.read(tempXMLFile);
			Map<String, Style> styleMap = readXmlStyle(document);
			log.debug("2.完成解析XML中样式信息");
			List<Worksheet> worksheets = readXmlWorksheet(document);
			if (log.isDebugEnabled()) {
				log.debug("3.开始将XML信息写入Excel，数据为：" + worksheets.toString());
			}
			XSSFWorkbook wb = new XSSFWorkbook();
			for (Worksheet worksheet : worksheets) {
				XSSFSheet sheet = wb.createSheet(worksheet.getName());
				Table table = worksheet.getTable();
				List<Row> rows = table.getRows();
				List<Column> columns = table.getColumns();
				// 填充列宽
				int columnIndex = 0;
				for (int i = 0; i < columns.size(); i++) {
					Column column = columns.get(i);
					columnIndex = getCellWidthIndex(columnIndex, i, column.getIndex());
					sheet.setColumnWidth(columnIndex, (int) column.getWidth() * 50);
				}
				int createRowIndex = 0;
				List<CellRangeAddressEntity> cellRangeAddresses = new ArrayList<>();
				for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
					Row rowInfo = rows.get(rowIndex);
					if (rowInfo == null) {
						continue;
					}
					createRowIndex = getIndex(createRowIndex, rowIndex, rowInfo.getIndex());
					XSSFRow row = sheet.createRow(createRowIndex);
					if (rowInfo.getHeight() != null) {
						Integer height = rowInfo.getHeight() * 20;
						row.setHeight(height.shortValue());
					}
					List<Cell> cells = rowInfo.getCells();
					if (CollectionUtils.isEmpty(cells)) {
						continue;
					}
					int startIndex = 0;
					for (int cellIndex = 0; cellIndex < cells.size(); cellIndex++) {
						Cell cellInfo = cells.get(cellIndex);
						if (cellInfo == null) {
							continue;
						}
						// 获取起始列
						startIndex = getIndex(startIndex, cellIndex, cellInfo.getIndex());
						XSSFCell cell = row.createCell(startIndex);
						String styleID = cellInfo.getStyleID();
						Style style = styleMap.get(styleID);
						/*设置数据单元格格式*/
						CellStyle dataStyle = wb.createCellStyle();
						// 设置边框样式
						setBorder(style, dataStyle);
						// 设置对齐方式
						setAlignment(style, dataStyle);
						// 填充文本
						setValue(wb, cellInfo, cell, style, dataStyle);
						// 填充颜色
						setCellColor(style, dataStyle);
						cell.setCellStyle(dataStyle);
						//单元格注释
						if (cellInfo.getComment() != null) {
							Data data = cellInfo.getComment().getData();
							Comment comment = sheet.createDrawingPatriarch()
									.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
							comment.setString(new XSSFRichTextString(data.getText()));
							cell.setCellComment(comment);
						}
						// 合并单元格
						startIndex = getCellRanges(createRowIndex, cellRangeAddresses, startIndex, cellInfo, style);
					}
				}
				// 添加合并单元格
				addCellRange(sheet, cellRangeAddresses);
			}
			// 加载图片到excel
			log.debug("4.开始写入图片：" + freemakerEntity.getExcelImageInputs());
			if (!CollectionUtils.isEmpty(freemakerEntity.getExcelImageInputs())) {
				writeImageToExcel(freemakerEntity.getExcelImageInputs(), wb);
			}
			log.debug("5.完成写入图片：" + freemakerEntity.getExcelImageInputs());
			// 写入excel文件,response字符流转换成字节流，template需要字节流作为输出
			wb.write(outputStream);
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("导出excel异常：" + e.getMessage());
		} finally {
			try {
				out.close();
			} catch (Exception e) {
			}
		}
	}

	public static Map<String, Style> readXmlStyle(Document document) {
		Map<String, Style> styleMap = XmlReader.getStyle(document);
		return styleMap;
	}

	public static List<Worksheet> readXmlWorksheet(Document document) {
		List<Worksheet> worksheets = XmlReader.getWorksheet(document);
		return worksheets;
	}

	private static int getIndex(int columnIndex, int i, Integer index) {
		if (index != null) {
			columnIndex = index - 1;
		}
		if (index == null && columnIndex != 0) {
			columnIndex = columnIndex + 1;
		}
		if (index == null && columnIndex == 0) {
			columnIndex = i;
		}
		return columnIndex;
	}

	private static int getCellWidthIndex(int columnIndex, int i, Integer index) {
		if (index != null) {
			columnIndex = index;
		}
		if (index == null && columnIndex != 0) {
			columnIndex = columnIndex + 1;
		}
		if (index == null && columnIndex == 0) {
			columnIndex = i;
		}
		return columnIndex;
	}

	/**
	 * 设置边框
	 *
	 * @param style:
	 * @param dataStyle:
	 * @return void
	 */
	private static void setBorder(Style style, CellStyle dataStyle) {
		if (style != null && style.getBorders() != null) {
			for (int k = 0; k < style.getBorders().size(); k++) {
				Style.Border border = style.getBorders().get(k);
				if (border != null) {
					if ("Bottom".equals(border.getPosition())) {
						dataStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
						dataStyle.setBorderBottom(BorderStyle.THIN);
					}
					if ("Left".equals(border.getPosition())) {
						dataStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
						dataStyle.setBorderLeft(BorderStyle.THIN);
					}
					if ("Right".equals(border.getPosition())) {
						dataStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
						dataStyle.setBorderRight(BorderStyle.THIN);
					}
					if ("Top".equals(border.getPosition())) {
						dataStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
						dataStyle.setBorderTop(BorderStyle.THIN);
					}
				}

			}
		}
	}

	/**
	 * 将图片写入Excel(XLS版)
	 *
	 * @param excelImageInputs
	 * @param wb
	 * @throws IOException
	 */
	@SuppressWarnings("rawtypes")
	private static void writeImageToExcel(List<ExcelImageInput> excelImageInputs, HSSFWorkbook wb) throws IOException {
		BufferedImage bufferImg = null;
		if (!CollectionUtils.isEmpty(excelImageInputs)) {
			for (ExcelImageInput excelImageInput : excelImageInputs) {
				Sheet sheet = wb.getSheetAt(excelImageInput.getSheetIndex());
				if (sheet == null) {
					continue;
				}
				// 画图的顶级管理器，一个sheet只能获取一个
				Drawing patriarch = sheet.createDrawingPatriarch();
				// anchor存储图片的属性，包括在Excel中的位置、大小等信息
				HSSFClientAnchor anchor = excelImageInput.getAnchorXls();
				anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
				// 插入图片
				String imagePath = excelImageInput.getImgPath();
				// 将图片写入到byteArray中
				ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
				bufferImg = ImageIO.read(new File(imagePath));
				// 图片扩展名
				String imageType = imagePath.substring(imagePath.lastIndexOf(".") + 1, imagePath.length());
				ImageIO.write(bufferImg, imageType, byteArrayOut);
				// 通过poi将图片写入到Excel中
				patriarch.createPicture(anchor,
						wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
			}
		}
	}

	/**
	 * 将图片写入Excel(XLSX版)
	 *
	 * @param excelImageInputs
	 * @param wb
	 * @throws IOException
	 */
	@SuppressWarnings("rawtypes")
	private static void writeImageToExcel(List<ExcelImageInput> excelImageInputs, XSSFWorkbook wb) throws IOException {
		BufferedImage bufferImg = null;
		if (!CollectionUtils.isEmpty(excelImageInputs)) {
			for (ExcelImageInput excelImageInput : excelImageInputs) {
				Sheet sheet = wb.getSheetAt(excelImageInput.getSheetIndex());
				if (sheet == null) {
					continue;
				}
				// 画图的顶级管理器，一个sheet只能获取一个
				Drawing patriarch = sheet.createDrawingPatriarch();
				// anchor存储图片的属性，包括在Excel中的位置、大小等信息
				XSSFClientAnchor anchor = excelImageInput.getAnchorXlsx();
				anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
				// 插入图片
				String imagePath = excelImageInput.getImgPath();
				// 将图片写入到byteArray中
				ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
				bufferImg = ImageIO.read(new File(imagePath));
				// 图片扩展名
				String imageType = imagePath.substring(imagePath.lastIndexOf(".") + 1, imagePath.length());
				ImageIO.write(bufferImg, imageType, byteArrayOut);
				// 通过poi将图片写入到Excel中
				patriarch.createPicture(anchor,
						wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
			}
		}
	}

	/**
	 * 添加合并单元格（XLS格式）
	 *
	 * @param sheet:
	 * @param cellRangeAddresses:
	 * @return void
	 */
	private static void addCellRange(HSSFSheet sheet, List<CellRangeAddressEntity> cellRangeAddresses) {
		if (!CollectionUtils.isEmpty(cellRangeAddresses)) {
			for (CellRangeAddressEntity cellRangeAddressEntity : cellRangeAddresses) {
				CellRangeAddress cellRangeAddress = cellRangeAddressEntity.getCellRangeAddress();
				sheet.addMergedRegion(cellRangeAddress);
				if (CollectionUtils.isEmpty(cellRangeAddressEntity.getBorders())) {
					continue;
				}
				for (int k = 0; k < cellRangeAddressEntity.getBorders().size(); k++) {
					Style.Border border = cellRangeAddressEntity.getBorders().get(k);
					if (border == null) {
						continue;
					}
					if ("Bottom".equals(border.getPosition())) {
						RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
					}
					if ("Left".equals(border.getPosition())) {
						RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
					}
					if ("Right".equals(border.getPosition())) {
						RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
					}
					if ("Top".equals(border.getPosition())) {
						RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
					}
				}
			}
		}
	}

	/**
	 * 添加合并单元格（XLSX格式）
	 *
	 * @param sheet:
	 * @param cellRangeAddresses:
	 * @return void
	 */
	private static void addCellRange(XSSFSheet sheet, List<CellRangeAddressEntity> cellRangeAddresses) {
		if (!CollectionUtils.isEmpty(cellRangeAddresses)) {
			for (CellRangeAddressEntity cellRangeAddressEntity : cellRangeAddresses) {
				CellRangeAddress cellRangeAddress = cellRangeAddressEntity.getCellRangeAddress();
				sheet.addMergedRegion(cellRangeAddress);
				if (CollectionUtils.isEmpty(cellRangeAddressEntity.getBorders())) {
					continue;
				}
				for (int k = 0; k < cellRangeAddressEntity.getBorders().size(); k++) {
					Style.Border border = cellRangeAddressEntity.getBorders().get(k);
					if (border == null) {
						continue;
					}
					if ("Bottom".equals(border.getPosition())) {
						RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
					}
					if ("Left".equals(border.getPosition())) {
						RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
					}
					if ("Right".equals(border.getPosition())) {
						RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
					}
					if ("Top".equals(border.getPosition())) {
						RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
					}
				}
			}
		}
	}

	/**
	 * 设置对齐方式
	 *
	 * @param style:
	 * @param dataStyle:
	 * @return void
	 */
	private static void setAlignment(Style style, CellStyle dataStyle) {
		if (style != null && style.getAlignment() != null) {
			// 设置水平对齐方式
			String horizontal = style.getAlignment().getHorizontal();
			if (!ObjectUtils.isEmpty(horizontal)) {
				if ("Left".equals(horizontal)) {
					dataStyle.setAlignment(HorizontalAlignment.LEFT);
				} else if ("Center".equals(horizontal)) {
					dataStyle.setAlignment(HorizontalAlignment.CENTER);
				} else {
					dataStyle.setAlignment(HorizontalAlignment.RIGHT);
				}
			}

			// 设置垂直对齐方式
			String vertical = style.getAlignment().getVertical();
			if (!ObjectUtils.isEmpty(vertical)) {
				if ("Top".equals(vertical)) {
					dataStyle.setVerticalAlignment(VerticalAlignment.TOP);
				} else if ("Center".equals(vertical)) {
					dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
				} else if ("Bottom".equals(vertical)) {
					dataStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
				} else if ("JUSTIFY".equals(vertical)) {
					dataStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);
				} else {
					dataStyle.setVerticalAlignment(VerticalAlignment.DISTRIBUTED);
				}
			}
			// 设置换行
			String wrapText = style.getAlignment().getWrapText();
			if (!ObjectUtils.isEmpty(wrapText)) {
				dataStyle.setWrapText(true);
			}
		}
	}

	/**
	 * 设置单元格背景填充色
	 *
	 * @param style:
	 * @param dataStyle:
	 * @return void
	 */
	private static void setCellColor(Style style, CellStyle dataStyle) {
		if (style != null && style.getInterior() != null) {
			if ("#FF0000".equals(style.getInterior().getColor())) {
				// 填充单元格
				dataStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
				dataStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
			} else if ("#92D050".equals(style.getInterior().getColor())) {
				// 填充单元格
				dataStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
			}
			if ("Solid".equals(style.getInterior().getPattern())) {
				dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			}
		}
	}

	/**
	 * 构造合并单元格集合
	 *
	 * @param createRowIndex:
	 * @param cellRangeAddresses:
	 * @param startIndex:
	 * @param cellInfo:
	 * @param style:
	 * @return int
	 */
	private static int getCellRanges(int createRowIndex, List<CellRangeAddressEntity> cellRangeAddresses,
			int startIndex, Cell cellInfo, Style style) {
		if (cellInfo.getMergeAcross() != null || cellInfo.getMergeDown() != null) {
			CellRangeAddress cellRangeAddress = null;
			if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() != null) {
				int mergeAcross = startIndex;
				if (cellInfo.getMergeAcross() != 0) {
					// 获取该单元格结束列数
					mergeAcross += cellInfo.getMergeAcross();
				}
				int mergeDown = createRowIndex;
				if (cellInfo.getMergeDown() != 0) {
					// 获取该单元格结束列数
					mergeDown += cellInfo.getMergeDown();
				}
				cellRangeAddress = new CellRangeAddress(createRowIndex, mergeDown, (short) startIndex,
						(short) mergeAcross);
			} else if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() == null) {
				int mergeAcross = startIndex;
				if (cellInfo.getMergeAcross() != 0) {
					// 获取该单元格结束列数
					mergeAcross += cellInfo.getMergeAcross();
					// 合并单元格
					cellRangeAddress = new CellRangeAddress(createRowIndex, createRowIndex, (short) startIndex,
							(short) mergeAcross);
				}

			} else if (cellInfo.getMergeDown() != null && cellInfo.getMergeAcross() == null) {
				int mergeDown = createRowIndex;
				if (cellInfo.getMergeDown() != 0) {
					// 获取该单元格结束列数
					mergeDown += cellInfo.getMergeDown();
					// 合并单元格
					cellRangeAddress = new CellRangeAddress(createRowIndex, mergeDown, (short) startIndex,
							(short) startIndex);
				}
			}

			if (cellInfo.getMergeAcross() != null) {
				int length = cellInfo.getMergeAcross().intValue();
				//此处注释掉，否则绘制合并的单元格有问题
				//for (int i = 0; i < length; i++) {
					startIndex += cellInfo.getMergeAcross();
				//}
			}
			CellRangeAddressEntity cellRangeAddressEntity = new CellRangeAddressEntity();
			cellRangeAddressEntity.setCellRangeAddress(cellRangeAddress);
			if (style != null && style.getBorders() != null) {
				cellRangeAddressEntity.setBorders(style.getBorders());
			}
			cellRangeAddresses.add(cellRangeAddressEntity);
		}
		return startIndex;
	}

	/**
	 * 设置文本值内容（XLSX格式）
	 *
	 * @param wb:
	 * @param cellInfo:
	 * @param cell:
	 * @param style:
	 * @param dataStyle:
	 * @return void
	 */
	private static void setValue(XSSFWorkbook wb, Cell cellInfo, XSSFCell cell, Style style, CellStyle dataStyle) {
		if (cellInfo.getData() != null) {
			XSSFFont font = wb.createFont();
			if (style != null && style.getFont() != null) {
				String color = style.getFont().getColor();
				if ("#FF0000".equals(color)) {
					font.setColor(IndexedColors.RED.getIndex());
				} else if ("#000000".equals(color)) {
					font.setColor(IndexedColors.BLACK.getIndex());
				}
			}
			if (!ObjectUtils.isEmpty(cellInfo.getData().getType()) && "Number".equals(cellInfo.getData().getType())) {
				cell.setCellType(CellType.NUMERIC);
			}
			if (style != null && style.getFont().getBold() > 0) {
				font.setBold(true);
			}
			if (style != null && !ObjectUtils.isEmpty(style.getFont().getFontName())) {
				font.setFontName(style.getFont().getFontName());
			}
			if (style != null && style.getFont().getSize() > 0) {
				// 设置字体大小道
				font.setFontHeightInPoints((short) style.getFont().getSize());
			}

			if (cellInfo.getData().getFont() != null) {
				if (cellInfo.getData().getFont().getBold() > 0) {
					font.setBold(true);
				}
				if ("Number".equals(cellInfo.getData().getType())) {
					cell.setCellValue(Float.parseFloat(cellInfo.getData().getFont().getText()));
				} else {
					cell.setCellValue(cellInfo.getData().getFont().getText());
				}
				if (!ObjectUtils.isEmpty(cellInfo.getData().getFont().getCharSet())) {
					font.setCharSet(Integer.valueOf(cellInfo.getData().getFont().getCharSet()));
				}
			} else {
				if ("Number".equals(cellInfo.getData().getType())) {
					if (!ObjectUtils.isEmpty(cellInfo.getData().getText())) {
						// cell.setCellValue(Float.parseFloat(cellInfo.getData().getText()));
						cell.setCellValue(Float.parseFloat(cellInfo.getData().getText().replaceAll(",", "")));
					}
				} else {
					cell.setCellValue(cellInfo.getData().getText());

				}
			}

			if (style != null) {
				if (style.getNumberFormat() != null) {
					String color = style.getFont().getColor();
					if ("#FF0000".equals(color)) {
						font.setColor(IndexedColors.RED.getIndex());
					} else if ("#000000".equals(color)) {
						font.setColor(IndexedColors.BLACK.getIndex());
					}
					if ("0%".equals(style.getNumberFormat().getFormat())) {
						XSSFDataFormat format = wb.createDataFormat();
						dataStyle.setDataFormat(format.getFormat(style.getNumberFormat().getFormat()));
					} else {
						dataStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
					}
					// XSSFDataFormat format = wb.createDataFormat();
					// dataStyle.setDataFormat(format.getFormat(style.getNumberFormat().getFormat()));
				}
			}
			dataStyle.setFont(font);
		}
	}

	/**
	 * 设置文本值内容（XLS格式）
	 *
	 * @param wb:
	 * @param cellInfo:
	 * @param cell:
	 * @param style:
	 * @param dataStyle:
	 * @return void
	 */
	private static void setValue(HSSFWorkbook wb, Cell cellInfo, HSSFCell cell, Style style, CellStyle dataStyle) {
		if (cellInfo.getData() != null) {
			HSSFFont font = wb.createFont();
			if (style != null && style.getFont() != null) {
				String color = style.getFont().getColor();
				if ("#FF0000".equals(color)) {
					font.setColor(IndexedColors.RED.getIndex());
				} else if ("#000000".equals(color)) {
					font.setColor(IndexedColors.BLACK.getIndex());
				}
			}
			if (!ObjectUtils.isEmpty(cellInfo.getData().getType()) && "Number".equals(cellInfo.getData().getType())) {
				cell.setCellType(CellType.NUMERIC);
			}
			if (style != null && style.getFont().getBold() > 0) {
				font.setBold(true);
			}
			if (style != null && !ObjectUtils.isEmpty(style.getFont().getFontName())) {
				font.setFontName(style.getFont().getFontName());
			}
			if (style != null && style.getFont().getSize() > 0) {
				// 设置字体大小道
				font.setFontHeightInPoints((short) style.getFont().getSize());
			}

			if (cellInfo.getData().getFont() != null) {
				if (cellInfo.getData().getFont().getBold() > 0) {
					font.setBold(true);
				}
				if ("Number".equals(cellInfo.getData().getType())) {
					cell.setCellValue(Float.parseFloat(cellInfo.getData().getFont().getText()));
				} else {
					cell.setCellValue(cellInfo.getData().getFont().getText());
				}
				if (!ObjectUtils.isEmpty(cellInfo.getData().getFont().getCharSet())) {
					font.setCharSet(Integer.valueOf(cellInfo.getData().getFont().getCharSet()));
				}
			} else {
				if ("Number".equals(cellInfo.getData().getType())) {
					if (!ObjectUtils.isEmpty(cellInfo.getData().getText())) {
						// cell.setCellValue(Float.parseFloat(cellInfo.getData().getText()));
						cell.setCellValue(Float.parseFloat(cellInfo.getData().getText().replaceAll(",", "")));
					}
				} else {
					cell.setCellValue(cellInfo.getData().getText());

				}
			}

			if (style != null) {
				if (style.getNumberFormat() != null) {
					String color = style.getFont().getColor();
					if ("#FF0000".equals(color)) {
						font.setColor(IndexedColors.RED.getIndex());
					} else if ("#000000".equals(color)) {
						font.setColor(IndexedColors.BLACK.getIndex());
					}
					if ("0%".equals(style.getNumberFormat().getFormat())) {
						HSSFDataFormat format = wb.createDataFormat();
						dataStyle.setDataFormat(format.getFormat(style.getNumberFormat().getFormat()));
					} else {
						dataStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
					}
					// HSSFDataFormat format = wb.createDataFormat();
					// dataStyle.setDataFormat(format.getFormat(style.getNumberFormat().getFormat()));
				}
			}
			dataStyle.setFont(font);
		}
	}


}

三.项目依赖

1.Maven依赖

以下这几个依赖，为本文工具类中，必须使用的，请根据自己项目Spring的版本，确定兼容版本。

<!-- 模板引擎 -->
<dependency>
	<groupId>org.freemarker</groupId>
	<artifactId>freemarker</artifactId>
</dependency>

<!-- java处理Excel文件 -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.0</version>
</dependency>

<!--支持excel2007以上，性能特别好-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
    <exclusions>
        <exclusion>
            <artifactId>poi</artifactId>
                <groupId>org.apache.poi</groupId>
            </exclusion>
        <exclusion>
            <artifactId>xmlbeans</artifactId>
            <groupId>org.apache.xmlbeans</groupId>
        </exclusion>
    </exclusions>
</dependency>

<!--自定义xmlbeans版本引用-->
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>3.1.0</version>
</dependency>


<!-- java解析XML文件 -->
<dependency>
	<groupId>dom4j</groupId>
	<artifactId>dom4j</artifactId>
</dependency>

<!-- 使用其中的FileUtils工具类 -->
<dependency>
	<groupId>commons-io</groupId>
	<artifactId>commons-io</artifactId>
	<version>2.6</version>
</dependency>

<!-- 使用注解，简化代码 -->
<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<scope>provided</scope>
 </dependency>

注意：如果项目中引入了spring-boot-starter-web这个依赖，将不需要再引入logback,lombok,Freemarker,dom4j这几个依赖，因为其中已经包含了，为了演示，所以在pom.xml显式的引入了这几个依赖。

<!-- spring-boot-starter-web 已经包含了logback、lombok、Freemarker、dom4j依赖，这里只显式的引用下 -->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>

2.工具类中两个入参对象

FreemakerInput.java入参主对象：

package com.study.dto.freemarker.input;

import lombok.Data;

import java.util.List;
import java.util.Map;

/**
 * @author 大脑补丁
 * @project freemarker-excel
 * @description: FreeMarker导出带图片的Excel需要的参数对象
 * @create 2020-04-14 14:21
 */
@Data
@SuppressWarnings("rawtypes")
public class FreemarkerInput {
	/**
	 * 加载数据
	 */
	private Map dataMap;
	/**
	 * 模版名称
	 */
	private String templateName;
	/**
	 * 模版路径
	 */
	private String templateFilePath;
	/**
	 * 生成文件名称
	 */
	private String fileName;

	/**
	 * xml缓存文件路径
	 */
	private String xmlTempFile;

	/**
	 * 插入图片信息
	 */
	List<ExcelImageInput> excelImageInputs;

}

ExcelImageInput .java插入Excel图片信息对象：

package com.study.dto.freemarker.input;

import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

import java.io.Serializable;

@Data
public class ExcelImageInput implements Serializable {

	/**
	 * 图片地址
	 */
	private String imgPath;

	/**
	 * sheet索引
	 */
	private Integer sheetIndex;

	/**
	 * 图片所在位置坐标（xls格式版，HSSFClientAnchor与XSSFClientAnchor只能二选一）
	 */
	private HSSFClientAnchor anchorXls;

	/**
	 * 图片所在位置坐标（xlsx格式版，XSSFClientAnchor与HSSFClientAnchor只能二选一）
	 */
	private XSSFClientAnchor anchorXlsx;

	private ExcelImageInput() {

	}

	/**
	 * Excel图片参数对象(xlsx版)
	 *
	 * @param imgPath
	 * @param sheetIndex
	 * @param anchorXlsx
	 */
	public ExcelImageInput(String imgPath, Integer sheetIndex, XSSFClientAnchor anchorXlsx) {
		this.imgPath = imgPath;
		this.sheetIndex = sheetIndex;
		this.anchorXlsx = anchorXlsx;
	}

	/**
	 * Excel图片参数对象(xls版)
	 *
	 * @param imgPath
	 * @param sheetIndex
	 * @param anchorXls
	 */
	public ExcelImageInput(String imgPath, Integer sheetIndex, HSSFClientAnchor anchorXls) {
		this.imgPath = imgPath;
		this.sheetIndex = sheetIndex;
		this.anchorXls = anchorXls;
	}
}


}

3.工具类依赖的XML解析器

这个类是用来读取Freemarker导出的xml格式的Excel。将xml格式的Excel转化为xls格式的以后，才能向Excel中插入图片。

package com.study.commons.utils;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.Element;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import com.study.entity.excel.Cell;
import com.study.entity.excel.Column;
import com.study.entity.excel.Data;
import com.study.entity.excel.Font;
import com.study.entity.excel.Row;
import com.study.entity.excel.Style;
import com.study.entity.excel.Table;
import com.study.entity.excel.Worksheet;

/**
 * 
 * @project freemarker-excel
 * @description: 读取XML文件工具
 * @author 大脑补丁
 * @create 2020-04-21 08:58
 */
public class XmlReader {

    // 获取样式
    @SuppressWarnings("rawtypes")
    public static Map<String, Style> getStyle(Document document) {
        // 创建一个LinkedHashMap用于存放style，按照id查找
        Map<String, Style> styleMap = new LinkedHashMap<String, Style>();
        // 新建一个Style类用于存放节点数据
        Style style = null;
        // 获取根节点
        Element root = document.getRootElement();
        // 获取根节点下的Styles节点
        Element styles = root.element("Styles");
        // 获取Styles下的Style节点
        List styleList = styles.elements("Style");
        Iterator<?> it = styleList.iterator();
        while (it.hasNext()) {
            // 新建一个Style类用于存放节点数据
            style = new Style();
            Element e = (Element)it.next();
            String id = e.attributeValue("ID").toString();
            // 设置style的id
            style.setId(id);
            if (e.attributeValue("Name") != null) {
                String name = e.attributeValue("Name").toString();
                // 设置style的name
                style.setName(name);
            }
            // 获取Style下的NumberFormat节点
            Element enumberFormat = e.element("NumberFormat");
            if (enumberFormat != null) {
                Style.NumberFormat numberFormat = new Style.NumberFormat();
                numberFormat.setFormat(enumberFormat.attributeValue("Format"));
                style.setNumberFormat(numberFormat);
            }
            Style.Alignment alignment = new Style.Alignment();
            // 获取Style下的Alignment节点
            Element ealignment = e.element("Alignment");
            if (ealignment != null) {
                // 设置aligment的相关属性，并且设置style的aliment属性
                alignment.setHorizontal(ealignment.attributeValue("Horizontal"));
                alignment.setVertical(ealignment.attributeValue("Vertical"));
                alignment.setWrapText(ealignment.attributeValue("WrapText"));
                style.setAlignment(alignment);
            }
            // 获取Style下的Borders节点
            Element Borders = e.element("Borders");
            if (Borders != null) {
                // 获取Borders下的Border节点
                List Border = Borders.elements("Border");
                // 用迭代器遍历Border节点
                Iterator<?> borderIterator = Border.iterator();
                List<Style.Border> lborders = new ArrayList<Style.Border>();
                while (borderIterator.hasNext()) {
                    Element bd = (Element)borderIterator.next();
                    Style.Border border = new Style.Border();
                    border.setPosition(bd.attributeValue("Position"));
                    if (bd.attribute("LineStyle") != null) {
                        border.setLinestyle(bd.attributeValue("LineStyle"));
                        int weight = Integer.parseInt(bd.attributeValue("Weight"));
                        border.setWeight(weight);
                        border.setColor(bd.attributeValue("Color"));
                    }
                    lborders.add(border);
                }
                style.setBorders(lborders);
            }

            // 设置font的相关属性，并且设置style的font属性
            Style.Font font = new Style.Font();
            Element efont = e.element("Font");
            font.setFontName(efont.attributeValue("FontName"));
            if (efont.attributeValue("Size") != null) {
                double size = Double.parseDouble(efont.attributeValue("Size"));
                font.setSize(size);
            }
            if (efont.attribute("Bold") != null) {
                int bold = Integer.parseInt(efont.attributeValue("Bold"));
                font.setBold(bold);
            }
            font.setColor(efont.attributeValue("Color"));
            style.setFont(font);
            // 设置Interior的相关属性，并且设置style的interior属性
            Style.Interior interior = new Style.Interior();
            if (e.element("Interior") != null) {
                Element einterior = e.element("Interior");
                interior.setColor(einterior.attributeValue("Color"));
                interior.setPattern(einterior.attributeValue("Pattern"));
            }
            style.setInterior(interior);
            if (e.element("Protection") != null) {
                Element protectione = e.element("Protection");
                Style.Protection protection = new Style.Protection();
                protection.setModifier(protectione.attributeValue("Protected"));
                style.setProtection(protection);
            }
            styleMap.put(id, style);

        }
        return styleMap;
    }

    @SuppressWarnings("unchecked")
    public static List<Worksheet> getWorksheet(Document document) {
        List<Worksheet> worksheets = new ArrayList<>();
        Element root = document.getRootElement();
        // 读取根节点下的Worksheet节点
        List<Element> sheets = root.elements("Worksheet");
        if (CollectionUtils.isEmpty(sheets)) {
            return worksheets;
        }

        for (Element sheet : sheets) {
            Worksheet worksheet = new Worksheet();
            String name = sheet.attributeValue("Name");
            worksheet.setName(name);
            Table table = getTable(sheet);
            worksheet.setTable(table);
            worksheets.add(worksheet);
        }
        return worksheets;
    }

    private static Table getTable(Element sheet) {
        Element tableElement = sheet.element("Table");
        if (tableElement == null) {
            return null;
        }
        Table table = new Table();
        String expandedColumnCount = tableElement.attributeValue("ExpandedColumnCount");
        if (expandedColumnCount != null) {
            table.setExpandedColumnCount(Integer.parseInt(expandedColumnCount));
        }
        String expandedRowCount = tableElement.attributeValue("ExpandedRowCount");
        if (expandedRowCount != null) {
            table.setExpandedRowCount(Integer.parseInt(expandedRowCount));
        }
        String fullColumns = tableElement.attributeValue("FullColumns");
        if (fullColumns != null) {
            table.setFullColumns(Integer.parseInt(fullColumns));
        }

        String fullRows = tableElement.attributeValue("FullRows");
        if (fullRows != null) {
            table.setFullRows(Integer.parseInt(fullRows));
        }
        String defaultColumnWidth = tableElement.attributeValue("DefaultColumnWidth");
        if (defaultColumnWidth != null) {
            table.setDefaultColumnWidth(Double.valueOf(defaultColumnWidth).intValue());
        }

        String defaultRowHeight = tableElement.attributeValue("DefaultRowHeight");
        if (defaultRowHeight != null) {
            table.setDefaultRowHeight(Double.valueOf(defaultRowHeight).intValue());
        }
        // 读取列
        List<Column> columns = getColumns(tableElement, expandedColumnCount, defaultColumnWidth);
        table.setColumns(columns);

        // 读取行
        List<Row> rows = getRows(tableElement);
        table.setRows(rows);
        return table;
    }

    @SuppressWarnings("unchecked")
    private static List<Row> getRows(Element tableElement) {
        List<Element> rowElements = tableElement.elements("Row");
        if (CollectionUtils.isEmpty(rowElements)) {
            return null;
        }
        List<Row> rows = new ArrayList<>();
        for (Element rowElement : rowElements) {
            Row row = new Row();
            String height = rowElement.attributeValue("Height");
            if (height != null) {
                row.setHeight(Double.valueOf(height).intValue());
            }

            String index = rowElement.attributeValue("Index");
            if (index != null) {
                row.setIndex(Integer.valueOf(index));
            }
            List<Cell> cells = getCells(rowElement);
            row.setCells(cells);
            rows.add(row);
        }
        return rows;
    }

    @SuppressWarnings("unchecked")
    private static List<Cell> getCells(Element rowElement) {
        List<Element> cellElements = rowElement.elements("Cell");
        if (CollectionUtils.isEmpty(cellElements)) {
            return null;
        }
        List<Cell> cells = new ArrayList<>();
        for (Element cellElement : cellElements) {
            Cell cell = new Cell();
            String styleID = cellElement.attributeValue("StyleID");
            if (styleID != null) {
                cell.setStyleID(styleID);
            }
            String mergeAcross = cellElement.attributeValue("MergeAcross");
            if (mergeAcross != null) {
                cell.setMergeAcross(Double.valueOf(mergeAcross).intValue());
            }

            String mergeDown = cellElement.attributeValue("MergeDown");
            if (mergeDown != null) {
                cell.setMergeDown(Double.valueOf(mergeDown).intValue());
            }

            String index = cellElement.attributeValue("Index");
            if (index != null) {
                cell.setIndex(Integer.valueOf(index));
            }
            Element dataElement = cellElement.element("Data");
            if (dataElement != null) {
                Data data = new Data();
                String type = dataElement.attributeValue("Type");
                String xmlns = dataElement.attributeValue("xmlns");
                data.setType(type);
                data.setXmlns(xmlns);
                data.setText(dataElement.getText());
                Element bElement = dataElement.element("B");
                Integer bold = null;
                Element fontElement = null;
                if (bElement != null) {
                    fontElement = bElement.element("Font");
                    bold = 1;
                }
                Element uElement = dataElement.element("U");
                if (uElement != null) {
                    fontElement = uElement.element("Font");
                }
                if (fontElement == null) {
                    fontElement = dataElement.element("Font");
                }
                if (fontElement != null) {
                    Font font = new Font();
                    String face = fontElement.attributeValue("Face");
                    if (face != null) {
                        font.setFace(face);
                    }
                    String charSet = fontElement.attributeValue("CharSet");
                    if (charSet != null) {
                        font.setCharSet(charSet);
                    }
                    String color = fontElement.attributeValue("Color");
                    if (color != null) {
                        font.setColor(color);
                    }
                    if (bold != null) {
                        font.setBold(bold);
                    }
                    font.setText(fontElement.getText());
                    data.setFont(font);
                }

                cell.setData(data);
            }
            cells.add(cell);
        }
        return cells;
    }

    @SuppressWarnings("unchecked")
    private static List<Column> getColumns(Element tableElement, String expandedRowCount, String defaultColumnWidth) {
        List<Element> columnElements = tableElement.elements("Column");
        if (CollectionUtils.isEmpty(columnElements)) {
            return null;
        }
        if (ObjectUtils.isEmpty(expandedRowCount)) {
            return null;
        }
        int defaultWidth = 60;
        if (!ObjectUtils.isEmpty(defaultColumnWidth)) {
            defaultWidth = Double.valueOf(defaultColumnWidth).intValue();
        }
        List<Column> columns = new ArrayList<>();
        int indexNum = 0;
        for (int i = 0; i < columnElements.size(); i++) {
            Column column = new Column();
            Element columnElement = columnElements.get(i);
            String index = columnElement.attributeValue("Index");
            if (index != null) {
                if (indexNum < Integer.valueOf(index) - 1) {
                    for (int j = indexNum; j < Integer.valueOf(index) - 1; j++) {
                        column = new Column();
                        column.setIndex(indexNum);
                        column.setWidth(defaultWidth);package com.study.commons.utils;

import com.study.entity.excel.*;
import org.dom4j.Document;
import org.dom4j.Element;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import java.util.*;

/**
 * @author 大脑补丁
 * @project freemarker-excel
 * @description: 读取XML文件工具
 * @create 2020-04-21 08:58
 */
public class XmlReader {

	// 获取样式
	@SuppressWarnings("rawtypes")
	public static Map<String, Style> getStyle(Document document) {
		// 创建一个LinkedHashMap用于存放style，按照id查找
		Map<String, Style> styleMap = new LinkedHashMap<String, Style>();
		// 新建一个Style类用于存放节点数据
		Style style = null;
		// 获取根节点
		Element root = document.getRootElement();
		// 获取根节点下的Styles节点
		Element styles = root.element("Styles");
		// 获取Styles下的Style节点
		List styleList = styles.elements("Style");
		Iterator<?> it = styleList.iterator();
		while (it.hasNext()) {
			// 新建一个Style类用于存放节点数据
			style = new Style();
			Element e = (Element) it.next();
			String id = e.attributeValue("ID").toString();
			// 设置style的id
			style.setId(id);
			if (e.attributeValue("Name") != null) {
				String name = e.attributeValue("Name").toString();
				// 设置style的name
				style.setName(name);
			}
			// 获取Style下的NumberFormat节点
			Element enumberFormat = e.element("NumberFormat");
			if (enumberFormat != null) {
				Style.NumberFormat numberFormat = new Style.NumberFormat();
				numberFormat.setFormat(enumberFormat.attributeValue("Format"));
				style.setNumberFormat(numberFormat);
			}
			Style.Alignment alignment = new Style.Alignment();
			// 获取Style下的Alignment节点
			Element ealignment = e.element("Alignment");
			if (ealignment != null) {
				// 设置aligment的相关属性，并且设置style的aliment属性
				alignment.setHorizontal(ealignment.attributeValue("Horizontal"));
				alignment.setVertical(ealignment.attributeValue("Vertical"));
				alignment.setWrapText(ealignment.attributeValue("WrapText"));
				style.setAlignment(alignment);
			}
			// 获取Style下的Borders节点
			Element Borders = e.element("Borders");
			if (Borders != null) {
				// 获取Borders下的Border节点
				List Border = Borders.elements("Border");
				// 用迭代器遍历Border节点
				Iterator<?> borderIterator = Border.iterator();
				List<Style.Border> lborders = new ArrayList<Style.Border>();
				while (borderIterator.hasNext()) {
					Element bd = (Element) borderIterator.next();
					Style.Border border = new Style.Border();
					border.setPosition(bd.attributeValue("Position"));
					if (bd.attribute("LineStyle") != null) {
						border.setLinestyle(bd.attributeValue("LineStyle"));
						int weight = Integer.parseInt(bd.attributeValue("Weight"));
						border.setWeight(weight);
						border.setColor(bd.attributeValue("Color"));
					}
					lborders.add(border);
				}
				style.setBorders(lborders);
			}

			// 设置font的相关属性，并且设置style的font属性
			Style.Font font = new Style.Font();
			Element efont = e.element("Font");
			font.setFontName(efont.attributeValue("FontName"));
			if (efont.attributeValue("Size") != null) {
				double size = Double.parseDouble(efont.attributeValue("Size"));
				font.setSize(size);
			}
			if (efont.attribute("Bold") != null) {
				int bold = Integer.parseInt(efont.attributeValue("Bold"));
				font.setBold(bold);
			}
			font.setColor(efont.attributeValue("Color"));
			style.setFont(font);
			// 设置Interior的相关属性，并且设置style的interior属性
			Style.Interior interior = new Style.Interior();
			if (e.element("Interior") != null) {
				Element einterior = e.element("Interior");
				interior.setColor(einterior.attributeValue("Color"));
				interior.setPattern(einterior.attributeValue("Pattern"));
			}
			style.setInterior(interior);
			if (e.element("Protection") != null) {
				Element protectione = e.element("Protection");
				Style.Protection protection = new Style.Protection();
				protection.setModifier(protectione.attributeValue("Protected"));
				style.setProtection(protection);
			}
			styleMap.put(id, style);

		}
		return styleMap;
	}

	@SuppressWarnings("unchecked")
	public static List<Worksheet> getWorksheet(Document document) {
		List<Worksheet> worksheets = new ArrayList<>();
		Element root = document.getRootElement();
		// 读取根节点下的Worksheet节点
		List<Element> sheets = root.elements("Worksheet");
		if (CollectionUtils.isEmpty(sheets)) {
			return worksheets;
		}

		for (Element sheet : sheets) {
			Worksheet worksheet = new Worksheet();
			String name = sheet.attributeValue("Name");
			worksheet.setName(name);
			Table table = getTable(sheet);
			worksheet.setTable(table);
			worksheets.add(worksheet);
		}
		return worksheets;
	}

	private static Table getTable(Element sheet) {
		Element tableElement = sheet.element("Table");
		if (tableElement == null) {
			return null;
		}
		Table table = new Table();
		String expandedColumnCount = tableElement.attributeValue("ExpandedColumnCount");
		if (expandedColumnCount != null) {
			table.setExpandedColumnCount(Integer.parseInt(expandedColumnCount));
		}
		String expandedRowCount = tableElement.attributeValue("ExpandedRowCount");
		if (expandedRowCount != null) {
			table.setExpandedRowCount(Integer.parseInt(expandedRowCount));
		}
		String fullColumns = tableElement.attributeValue("FullColumns");
		if (fullColumns != null) {
			table.setFullColumns(Integer.parseInt(fullColumns));
		}

		String fullRows = tableElement.attributeValue("FullRows");
		if (fullRows != null) {
			table.setFullRows(Integer.parseInt(fullRows));
		}
		String defaultColumnWidth = tableElement.attributeValue("DefaultColumnWidth");
		if (defaultColumnWidth != null) {
			table.setDefaultColumnWidth(Double.valueOf(defaultColumnWidth).intValue());
		}

		String defaultRowHeight = tableElement.attributeValue("DefaultRowHeight");
		if (defaultRowHeight != null) {
			table.setDefaultRowHeight(Double.valueOf(defaultRowHeight).intValue());
		}
		// 读取列
		List<Column> columns = getColumns(tableElement, expandedColumnCount, defaultColumnWidth);
		table.setColumns(columns);

		// 读取行
		List<Row> rows = getRows(tableElement);
		table.setRows(rows);
		return table;
	}

	@SuppressWarnings("unchecked")
	private static List<Row> getRows(Element tableElement) {
		List<Element> rowElements = tableElement.elements("Row");
		if (CollectionUtils.isEmpty(rowElements)) {
			return null;
		}
		List<Row> rows = new ArrayList<>();
		for (Element rowElement : rowElements) {
			Row row = new Row();
			String height = rowElement.attributeValue("Height");
			if (height != null) {
				row.setHeight(Double.valueOf(height).intValue());
			}

			String index = rowElement.attributeValue("Index");
			if (index != null) {
				row.setIndex(Integer.valueOf(index));
			}
			List<Cell> cells = getCells(rowElement);
			row.setCells(cells);
			rows.add(row);
		}
		return rows;
	}

	@SuppressWarnings("unchecked")
	private static List<Cell> getCells(Element rowElement) {
		List<Element> cellElements = rowElement.elements("Cell");
		if (CollectionUtils.isEmpty(cellElements)) {
			return null;
		}
		List<Cell> cells = new ArrayList<>();
		for (Element cellElement : cellElements) {
			Cell cell = new Cell();
			String styleID = cellElement.attributeValue("StyleID");
			if (styleID != null) {
				cell.setStyleID(styleID);
			}
			String mergeAcross = cellElement.attributeValue("MergeAcross");
			if (mergeAcross != null) {
				cell.setMergeAcross(Integer.valueOf(mergeAcross));
			}

			String mergeDown = cellElement.attributeValue("MergeDown");
			if (mergeDown != null) {
				cell.setMergeDown(Integer.valueOf(mergeDown));
			}

			String index = cellElement.attributeValue("Index");
			if (index != null) {
				cell.setIndex(Integer.valueOf(index));
			}
			Element commentElement = cellElement.element("Comment");
			if (commentElement != null) {
				Comment comment = new Comment();
				String author = commentElement.attributeValue("Author");
				Element fontElement = commentElement.element("Font");
				Element dataElement = commentElement.element("Data");
				if (dataElement != null) {
					Data data = new Data();
					data.setText(dataElement.getStringValue());
					comment.setData(data);
				}
				if (fontElement != null) {
					Font font = new Font();
					font.setText(fontElement.getText());
					font.setBold(1);
					String color = fontElement.attributeValue("Color");
					if (color != null) {
						font.setColor(color);
					}
					comment.setFont(font);
				}
				comment.setAuthor(author);
				cell.setComment(comment);
			}

			Element dataElement = cellElement.element("Data");
			if (dataElement != null) {
				Data data = new Data();
				String type = dataElement.attributeValue("Type");
				String xmlns = dataElement.attributeValue("xmlns");
				data.setType(type);
				data.setXmlns(xmlns);
				data.setText(dataElement.getText());
				Element bElement = dataElement.element("B");
				Integer bold = null;
				Element fontElement = null;
				if (bElement != null) {
					fontElement = bElement.element("Font");
					bold = 1;
				}
				Element uElement = dataElement.element("U");
				if (uElement != null) {
					fontElement = uElement.element("Font");
				}
				if (fontElement == null) {
					fontElement = dataElement.element("Font");
				}
				if (fontElement != null) {
					Font font = new Font();
					String face = fontElement.attributeValue("Face");
					if (face != null) {
						font.setFace(face);
					}
					String charSet = fontElement.attributeValue("CharSet");
					if (charSet != null) {
						font.setCharSet(charSet);
					}
					String color = fontElement.attributeValue("Color");
					if (color != null) {
						font.setColor(color);
					}
					if (bold != null) {
						font.setBold(bold);
					}
					font.setText(fontElement.getText());
					data.setFont(font);
				}

				cell.setData(data);
			}
			cells.add(cell);
		}
		return cells;
	}

	@SuppressWarnings("unchecked")
	private static List<Column> getColumns(Element tableElement, String expandedRowCount, String defaultColumnWidth) {
		List<Element> columnElements = tableElement.elements("Column");
		if (CollectionUtils.isEmpty(columnElements)) {
			return null;
		}
		if (ObjectUtils.isEmpty(expandedRowCount)) {
			return null;
		}
		int defaultWidth = 60;
		if (!ObjectUtils.isEmpty(defaultColumnWidth)) {
			defaultWidth = Double.valueOf(defaultColumnWidth).intValue();
		}
		List<Column> columns = new ArrayList<>();
		int indexNum = 0;
		for (int i = 0; i < columnElements.size(); i++) {
			Column column = new Column();
			Element columnElement = columnElements.get(i);
			String index = columnElement.attributeValue("Index");
			if (index != null) {
				if (indexNum < Integer.valueOf(index) - 1) {
					for (int j = indexNum; j < Integer.valueOf(index) - 1; j++) {
						column = new Column();
						column.setIndex(indexNum);
						column.setWidth(defaultWidth);
						columns.add(column);
						indexNum += 1;
					}
				}
				column = new Column();
			}
			column.setIndex(indexNum);
			String autoFitWidth = columnElement.attributeValue("AutoFitWidth");
			if (autoFitWidth != null) {
				column.setAutofitwidth(Double.valueOf(autoFitWidth).intValue());
			}
			String width = columnElement.attributeValue("Width");
			if (width != null) {
				column.setWidth(Double.valueOf(width).intValue());
			}
			columns.add(column);
			indexNum += 1;
		}
		if (columns.size() < Integer.valueOf(expandedRowCount)) {
			for (int i = columns.size() + 1; i <= Integer.valueOf(expandedRowCount); i++) {
				Column column = new Column();
				column.setIndex(i);
				column.setWidth(defaultWidth);
				columns.add(column);
			}
		}
		return columns;
	}
}

4.工具类和解析器依赖的自定义Excel对象

以下9个对象，是自定义解析Excel相关对象，用来将xml格式的Excel转化为xls格式。

①：表格对象

package com.study.entity.excel;

import java.util.List;

import lombok.Data;

@Data
public class Table {
    private Integer expandedColumnCount;

    private Integer expandedRowCount;

    private Integer fullColumns;

    private Integer fullRows;

    private Integer defaultColumnWidth;

    private Integer defaultRowHeight;

    private List<Column> columns;

    private List<Row> rows;

}

②Sheet页对象

package com.study.entity.excel;

import lombok.Data;

@Data
public class Worksheet {

    private String Name;

    private Table table;

}

③列对象

package com.study.entity.excel;

import lombok.Data;

@Data
public class Column {

    private Integer index;
    private double width;
    private int autofitwidth;
}

④行对象

package com.study.entity.excel;

import java.util.List;

import lombok.Data;

@Data
public class Row {

    private Integer height;

    private List<Cell> cells;

    private Integer index;

}

⑤单元格对象

package com.study.entity.excel;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class Cell {

    private String styleID;

    private Integer mergeAcross;

    private Integer MergeDown;

    private Data data;

    private Integer index;

    private Comment comment;

}

⑥合并单元格信息对象：

package com.study.entity.excel;

import lombok.Data;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

@Data
public class CellRangeAddressEntity {

    private CellRangeAddress cellRangeAddress;

    private List<Style.Border> borders;

}

⑦数据对象

package com.study.entity.excel;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class Data {

    private String type;

    private String xmlns;

    private Font font;

    private String text;

}

⑧字体对象

package com.study.entity.excel;

import lombok.Data;

@Data
public class Font {

    private String face;

    private String charSet;

    private String color;

    private String text;

    private int bold;

}

⑨样式对象

package com.study.entity.excel;

import java.util.List;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
public class Style {
    private String id;
    private String parent;
    private String name;
    private Alignment alignment;
    private List<Border> borders;
    private Font font;
    private Interior interior;
    private NumberFormat numberFormat;
    private Protection protection;

    public Style(String id, Alignment alignment, List<Border> borders, Font font, Interior interior) {
        this.id = id;
        this.alignment = alignment;
        this.borders = borders;
        this.font = font;
        this.interior = interior;
    }

    public Style(String id, NumberFormat numberFormat) {
        this.id = id;
        this.numberFormat = numberFormat;
    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Alignment {
        private String horizontal;
        private String vertical;
        private String wrapText;
    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Border {
        private String position;
        private String linestyle;
        private int weight;
        private String color;

    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Font {
        private String fontName;
        private double size;
        private int bold;
        private String color;
        private Integer CharSet;
    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Interior {
        private String color;
        private String pattern;

    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class NumberFormat {
        private String format;

    }

    // 权限修饰
    @Data
    @NoArgsConstructor
    public static class Protection {
        private String modifier;

    }

}

⑩单元格注释对象（实现鼠标移动到单元格上，有hover弹框注释）

package com.study.entity.excel;

import lombok.Getter;
import lombok.Setter;

/**
 * @project cne-power-operation-web
 * @description: 单元格注释
 * @create: 2020-08-11 17:34
 */
@Getter
@Setter
public class Comment {

	private String author;

	private Data data;

	private Font font;

}

总结

至此，使用Freemarker导出复杂的Excel、导出带有图片的Excel教程已经完结，导出此类Excel工作，还是较为繁琐的，确实是个需要耐心慢慢做的工作。使用本文提供的工具，可以减轻不少的工作负担，节省至少几天的工期，最起码以后在遇到类似工作，不会在担心了。写教程、写示例项目也很费时，如果帮到了你，希望大家收藏、点赞。

Excel实用教程集锦

以下是我写的关于Java操作Excel的所有教程，基本包含了所有场景。

1.如果简单导出推荐使用工具类的方式，这种配置最简单。

2.如果对导出样式要求极高的还原度，推荐使用Freemarker方式，FreeMarker模板引擎可以通吃所有Excel的导出，属于一劳永逸的方式，项目经常导出推荐使用这种方式。

3.Freemarker导出的Excel为xml格式，是通过重命名为xls后，每次会打开弹框问题，我在《Freemarker整合poi导出带有图片的Excel教程》也已经完美解决，本教程将直接导出真正的xls格式，完美适配新版office和wps。Freemarker是无法导出带有图片的Excel，通过其他技术手段，也在本教程中完美导出带有图片的Excel。

4.下列教程中的代码都经本人和网友多次验证，真实有效！

↓↓↓↓一揽子Excel解决方案，赶快收藏吧↓↓↓↓

《Java导入Excel工具类使用教程》

《Java之Excel导出工具类使用教程》

《Freemarker导出复杂Excel图文教程》

《Freemarker整合poi导出带有图片的Excel教程》

大脑补丁

关注

15
点赞
踩
27

收藏

觉得还不错? 一键收藏
29
评论
Freemarker整合poi导出带有图片的Excel教程

序言前一篇文章《Freemarker导出复杂Excel图文教程》，讲解了使用Freemarker导出复杂的Excel，本文将在前文的基础之上，讲解使用Freemarker导出Excel后，在整合poi插入图片到Excel，从而实现Freemarker导出带有图片的Excel工具。为什么要做这个工具呢？因为Freemarker是不支持导出带有图片的Excel的，不支持的原因是Freemarke...
复制链接

扫一扫