简单封装POI导出excel

清疚 2022-06-11 05:51 235阅读 0赞

# **简单封装POI导出excel** #

Apache POI是一套根据Office Open XML标准（OOXML）和Microsoft OLE 2复合文档格式（OLE2）来处理各种文件格式的Java API，在应用中经常用来和Microsoft Office系列产品对接，在和excel对接是，需要处理excel繁琐的单元格，下面提供一个在Spring环境下导出excel的简单框架，减少对单元格的繁琐处理，减少重复搬砖，提高开发效率。

## 1. **依赖** ##

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

## 2. **Excel导出配置** ##

1) 导出excel的样式、数据配置文件

导出excel时，主要逻辑无非是将数据填充到excel单元格中，再设置单元格样式，封装POI对excel单元格的操作，只需要定义预先约定的配置文件，封装类再解析配置文件，得到被导出excel每行、每个单元格的数据，操作单元格，最后生成excel文件，返回给用户。

下面是约定的简单配置

<?xml version="1.0" encoding="UTF-8"?>
    <excel name="export.xls">
    	<sheet data-list="data1" desc="" field="examName">
    		<title desc="" field="examName" border="thin" height="40" background-color="41" align="center" v-align="center" wrap="true" bold="bold" font-size="25" font-color="12" font-name="黑体"></title>
    		<headers border="thin" height="25" background-color="43" align="center" v-align="center" wrap="true" bold="bold" font-size="20" font-name="宋体" >
    			<column header="名称" field="examName" type="String" border="thin" width="30" height="20" background-color="" align="center" v-align="top" wrap="true" bold="" font-size="18" font-name="宋体"></column>
    			<column header="测试1" field="schoolYear" type="String" border="thin" width="20" height="20" background-color="" align="right" v-align="center" wrap="true" bold="" font-size="18" font-name="宋体"></column>
    			<column header="测试2" field="term" type="String" border="thin" width="40" height="20" background-color="" align="left" v-align="" wrap="bottom" bold="true" font-size="18" font-name="隶书"></column>
    		</headers>
    	</sheet>
    	<sheet data-list="data1" desc="11111" field="examName">
    		<title desc="but" field="examName" border="thin" height="40" background-color="41" align="center" v-align="center" wrap="true" bold="bold" font-size="25" font-color="12" font-name="黑体"></title>
    		<headers border="thin" height="25" background-color="43" align="center" v-align="center" wrap="true" bold="bold" font-size="20" font-name="宋体" >
    			<column header="名称" field="examName" type="String" border="thin" width="30" height="20" background-color="" align="center" v-align="top" wrap="true" bold="" font-size="18" font-name="宋体"></column>
    			<column header="测试1" field="schoolYear" type="String" border="thin" width="20" height="20" background-color="" align="right" v-align="center" wrap="true" bold="" font-size="18" font-name="宋体"></column>
    			<column header="测试2" field="term" type="String" border="thin" width="40" height="20" background-color="" align="left" v-align="" wrap="bottom" bold="true" font-size="18" font-name="隶书"></column>
    		</headers>
    	</sheet>
    </excel>

2) 配置解析器

配置解析器ExcelConfigHelper负责解析导出配置，生成程序可读的导出excel配置ExcelConfigVO对象

public class ExcelConfigHelper {
    	public static ExcelConfigVO GetExcelConfig(String configPath) {
    		ExcelConfigVO config = new ExcelConfigVO();
    		SAXReader reader = new SAXReader();
    		try {
    			Document doc = reader.read(new File(configPath));
    			Element root = doc.getRootElement();
    
    			config.setName(root.attributeValue("name"));
    
    			List<SheetConfigVO> sheetList = new ArrayList<SheetConfigVO>();
    			config.setSheetList(sheetList);
    
    			Iterator<Element> si = root.elementIterator();
    			while (si.hasNext()) {
    				Element se = si.next();
    
    				SheetConfigVO sc = new SheetConfigVO();
    				sheetList.add(sc);
    
    				sc.setDataList(se.attributeValue("data-list"));
    				sc.setDesc(se.attributeValue("desc"));
    				sc.setField(se.attributeValue("field"));
    
    				Element te = se.element("title");
    				if (null != te) {
    					sc.setTitleStyle(getStyleConfig(te));
    				}
    
    				Element he = se.element("headers");
    				if (null != he) {
    					sc.setHeaderStyle(getStyleConfig(he));
    				}
    				List<ColumnConfigVO> columnList = new ArrayList<ColumnConfigVO>();
    				sc.setColumnList(columnList);
    				Iterator<Element> ci = he.elementIterator();
    				while (ci.hasNext()) {
    
    					Element ce = ci.next();
    					ColumnConfigVO cc = getColumnConfig(ce);
    					columnList.add(cc);
    
    				}
    			}
    		} catch (DocumentException e) {
    
    			e.printStackTrace();
    		}
    		return config;
    	}
    
    	private static StyleConfigVO getStyleConfig(Element e) {
    		StyleConfigVO style = new StyleConfigVO();
    		style.setField(e.attributeValue("field"));
    		style.setDesc(e.attributeValue("desc"));
    		style.setBorder(e.attributeValue("border"));
    		style.setWidth(e.attributeValue("width"));
    		style.setHeight(e.attributeValue("height"));
    		style.setBgcolor(e.attributeValue("background-color"));
    		style.setAlign(e.attributeValue("align"));
    		style.setValign(e.attributeValue("v-align"));
    		style.setWrap(e.attributeValue("wrap"));
    		style.setBold(e.attributeValue("bold"));
    		style.setFontSize(e.attributeValue("font-size"));
    		style.setFontName(e.attributeValue("font-name"));
    		style.setFontColor(e.attributeValue("font-color"));
    		return style;
    	}
    
    	private static ColumnConfigVO getColumnConfig(Element e) {
    		ColumnConfigVO c = new ColumnConfigVO();
    		c.setField(e.attributeValue("field"));
    		c.setHeader(e.attributeValue("header"));
    
    		StyleConfigVO style = getStyleConfig(e);
    		c.setStyle(style);
    		return c;
    	}
    }

Excel配置类

ExcelConfigVO

public class ExcelConfigVO implements Serializable {
    
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = -6669292508345659358L;
    
    	private String name;
    
    	private List<SheetConfigVO> sheetList;

SheetConfigVO

public class SheetConfigVO implements Serializable {
    
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = -7809672890306591286L;
    
    	private String dataList;
    
    	private String desc;
    
    	private String field;
    
    	private StyleConfigVO titleStyle;
    
    	private StyleConfigVO headerStyle;
    
    	private List<ColumnConfigVO> columnList;

StyleConfigVO

public class StyleConfigVO implements Serializable {
    
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 2747357536186545711L;
    
    	private String desc;
    
    	private String field;
    
    	private String border;
    
    	private String width;
    
    	private String height;
    
    	private String bgcolor;
    
    	private String align;
    
    	private String valign;
    
    	private String wrap;
    
    	private String bold;
    
    	private String fontSize;
    
    	private String fontName;
    
    	private String fontColor;

ColumnConfigVO

public class ColumnConfigVO implements Serializable {
    
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 5303919948113915294L;
    
    	private String header;
    
    	private String field;
    
    	private StyleConfigVO style;

## 3. **根据配置和数据生成excel** ##

Excel生成器ExcelExportHelper根据配置和数据生成excel

public class ExcelExportHelper {
    
    	private static final Logger log = LoggerFactory.getLogger(ExcelExportHelper.class);
    
    	public static HSSFWorkbook export(Map<String, Object> map, ExcelConfigVO config) {
    		try {
    
    			HSSFWorkbook hw = new HSSFWorkbook();
    
    			int sheetIndex = 1;
    			for (SheetConfigVO sc : config.getSheetList()) {
    
    				// dataList数据集合，desc是sheet名称，field是sheet名称在数据集合中的字段，后面二者取一个
    				String dataList = sc.getDataList();
    				String desc = sc.getDesc();
    				String field = sc.getField();
    				List<Object> datas = (List<Object>) map.get(dataList);
    
    				if (StringUtils.isEmpty(desc) && !CollectionUtils.isEmpty(datas) && !StringUtils.isEmpty(field)) {
    					Object obj = datas.get(0);
    					Field f = obj.getClass().getDeclaredField(field);
    					f.setAccessible(true);
    					desc = (String) f.get(obj);
    				}
    
    				if (StringUtils.isEmpty(desc)) {
    					desc = "sheet" + sheetIndex;
    				}
    
    				HSSFSheet sheet = hw.createSheet(desc);
    
    				if (null != sc.getTitleStyle()) {
    					StyleConfigVO ts = sc.getTitleStyle();
    					String tdesc = ts.getDesc();
    					String tfield = ts.getField();
    
    					if (StringUtils.isEmpty(tdesc) && !CollectionUtils.isEmpty(datas) && !StringUtils.isEmpty(tfield)) {
    						Object obj = datas.get(0);
    						Field f = obj.getClass().getDeclaredField(tfield);
    						f.setAccessible(true);
    						tdesc = (String) f.get(obj);
    					}
    
    					if (StringUtils.isEmpty(tdesc)) {
    						tdesc = "title" + sheetIndex;
    					}
    
    					HSSFCellStyle titleStyle = getCellStyle(hw, ts);
    					int rindex = 0;
    					HSSFRow row = sheet.createRow(rindex++);
    					if (!StringUtils.isEmpty(ts.getHeight())) {
    						row.setHeightInPoints(Short.valueOf(ts.getHeight()));
    					}
    					HSSFCell cell = row.createCell(0);
    					cell.setCellStyle(titleStyle);
    					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    					cell.setCellValue(desc);
    					if (!CollectionUtils.isEmpty(sc.getColumnList()) && sc.getColumnList().size() > 1) {
    						for (int i = 1; i < sc.getColumnList().size(); i++) {
    							cell = row.createCell(i);
    							cell.setCellStyle(titleStyle);
    							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    							cell.setCellValue("");
    						}
    						CellRangeAddress merge = new CellRangeAddress(rindex - 1, rindex - 1, 0,
    								sc.getColumnList().size() - 1);
    						sheet.addMergedRegion(merge);
    					}
    
    					StyleConfigVO hs = sc.getHeaderStyle();
    					HSSFCellStyle headerstyle = getCellStyle(hw, hs);
    					row = sheet.createRow(rindex++);
    					if (!StringUtils.isEmpty(hs.getHeight())) {
    						row.setHeightInPoints(Short.valueOf(hs.getHeight()));
    					}
    
    					Map<Integer, HSSFCellStyle> styleMap = new HashMap<Integer, HSSFCellStyle>();
    					if (!CollectionUtils.isEmpty(sc.getColumnList())) {
    						for (int i = 0; i < sc.getColumnList().size(); i++) {
    							ColumnConfigVO column = sc.getColumnList().get(i);
    							styleMap.put(i, getCellStyle(hw, column.getStyle()));
    
    							cell = row.createCell(i);
    							cell.setCellStyle(headerstyle);
    							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    							cell.setCellValue(column.getHeader());
    							if (null != column.getStyle() && !StringUtils.isEmpty(column.getStyle().getWidth())) {
    								sheet.setColumnWidth(i, Short.valueOf(column.getStyle().getWidth()) * 256);
    							}
    
    						}
    					}
    
    					for (Object obj : datas) {
    						row = sheet.createRow(rindex++);
    						for (int i = 0; i < sc.getColumnList().size(); i++) {
    
    							ColumnConfigVO column = sc.getColumnList().get(i);
    
    							if (i == 0 && !StringUtils.isEmpty(column.getStyle().getHeight())) {
    
    								row.setHeightInPoints(Short.valueOf(column.getStyle().getHeight()));
    							}
    							styleMap.put(i, getCellStyle(hw, column.getStyle()));
    
    							cell = row.createCell(i);
    							cell.setCellStyle(styleMap.get(i));
    							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    							String val = "";
    							Field f = obj.getClass().getDeclaredField(column.getField());
    							f.setAccessible(true);
    							val = (String) f.get(obj);
    							cell.setCellValue(null == val ? "" : val);
    						}
    					}
    				}
    
    				sheetIndex++;
    			}
    
    			return hw;
    		} catch (NoSuchFieldException e) {
    
    			log.error("", e);
    		} catch (SecurityException e) {
    
    			log.error("", e);
    		} catch (IllegalArgumentException e) {
    
    			log.error("", e);
    		} catch (IllegalAccessException e) {
    
    			log.error("", e);
    		}
    		return null;
    	}
    
    	private static HSSFCellStyle getCellStyle(HSSFWorkbook hw, StyleConfigVO ts) {
    		HSSFCellStyle titlestyle = hw.createCellStyle();
    		HSSFFont titlefont = hw.createFont();
    
    		if (!StringUtils.isEmpty(ts.getBold()) && "BOLD".equalsIgnoreCase(ts.getBold().trim())) {
    			titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    		}
    
    		if (!StringUtils.isEmpty(ts.getFontName())) {
    			titlefont.setFontName(ts.getFontName().trim());
    		}
    
    		if (!StringUtils.isEmpty(ts.getFontSize())) {
    			titlefont.setFontHeightInPoints(Short.valueOf(ts.getFontSize()));
    		}
    
    		if (!StringUtils.isEmpty(ts.getFontColor())) {
    			titlefont.setColor(Short.valueOf(ts.getFontColor()));
    		}
    		titlestyle.setFont(titlefont);
    
    		if (!StringUtils.isEmpty(ts.getBorder())) {
    			if ("THIN".equalsIgnoreCase(ts.getBorder().trim())) {
    				titlestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    				titlestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    				titlestyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    				titlestyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    			} else if ("NONE".equalsIgnoreCase(ts.getBorder().trim())) {
    				titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
    				titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
    				titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
    				titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
    			} else if ("DOUBLE".equalsIgnoreCase(ts.getBorder().trim())) {
    				titlestyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
    				titlestyle.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);
    				titlestyle.setBorderRight(HSSFCellStyle.BORDER_DOUBLE);
    				titlestyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
    			} else if ("DOTTED".equalsIgnoreCase(ts.getBorder().trim())) {
    				titlestyle.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);
    				titlestyle.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);
    				titlestyle.setBorderRight(HSSFCellStyle.BORDER_DOTTED);
    				titlestyle.setBorderTop(HSSFCellStyle.BORDER_DOTTED);
    			} else if ("DASHED".equalsIgnoreCase(ts.getBorder().trim())) {
    				titlestyle.setBorderBottom(HSSFCellStyle.BORDER_DASHED);
    				titlestyle.setBorderLeft(HSSFCellStyle.BORDER_DASHED);
    				titlestyle.setBorderRight(HSSFCellStyle.BORDER_DASHED);
    				titlestyle.setBorderTop(HSSFCellStyle.BORDER_DASHED);
    			} else if ("MEDIUM".equalsIgnoreCase(ts.getBorder().trim())) {
    				titlestyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    				titlestyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    				titlestyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    				titlestyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    			}
    		}
    
    		if (!StringUtils.isEmpty(ts.getAlign())) {
    			String align = ts.getAlign().toUpperCase().trim();
    			switch (align) {
    			case "CENTER":
    				titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    			case "LEFT":
    				titlestyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    			case "RIGHT":
    				titlestyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    			case "FILL":
    				titlestyle.setAlignment(HSSFCellStyle.ALIGN_FILL);
    			case "GENERAL":
    				titlestyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL);
    			default:
    				titlestyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
    			}
    
    		}
    
    		if (!StringUtils.isEmpty(ts.getValign())) {
    			String valign = ts.getValign().toUpperCase().trim();
    			switch (valign) {
    			case "CENTER":
    				titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    			case "BOTTOM":
    				titlestyle.setAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
    			case "TOP":
    				titlestyle.setAlignment(HSSFCellStyle.VERTICAL_TOP);
    			default:
    				titlestyle.setAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);
    			}
    
    		}
    
    		if (!StringUtils.isEmpty(ts.getBgcolor())) {
    			titlestyle.setFillForegroundColor(Short.valueOf(ts.getBgcolor()));
    			titlestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    		}
    
    		if (!StringUtils.isEmpty(ts.getWrap())) {
    			titlestyle.setWrapText(Boolean.valueOf(ts.getWrap()));
    		}
    
    		return titlestyle;
    	}
    
    	public static String getFileName(String fileName, ExcelConfigVO config) {
    		if (StringUtils.isEmpty(fileName)) {
    			fileName = config.getName();
    		}
    
    		if (StringUtils.isEmpty(fileName)) {
    			fileName = "export.xls";
    		}
    		return fileName;
    	}
    }

## 4. **导出逻辑** ##

1) 获取导出任务

导出任务接口，导出特定数据时需要实现接口，给配置解析器提供特定的配置，给**excel生成器提供**特定的数据。

public interface IExcelExportProcessor {
    
    	/**
    	 * 配置文件路径
    	 * 
    	 * @return
    	 */
    	public String getConfigPath();
    
    	/**
    	 * 导出文件名
    	 * 
    	 * @return
    	 */
    	public String getFilename();
    
    	/**
    	 * 查询数据
    	 * 
    	 * @param queryMap
    	 *            查询参数
    	 * @return 导出数据
    	 */
    	public Map<String, Object> getDatas(Map<String, Object> queryMap);
    }

导出任务实现样例

@Component("excelExport.seat")
    public class ExportSeatProcessor implements IExcelExportProcessor {
    
    	private static final Logger log = LoggerFactory.getLogger(ExportSeatProcessor.class);
    
    	@Override
    	public String getConfigPath() {
    
    		return "";
    	}
    
    	@Override
    	public String getFilename() {
    
    		return null;
    	}
    
    	@Override
    	public Map<String, Object> getDatas(Map<String, Object> queryMap) {
    
    		return null;
    	}
    
    }

2) 导出接口

导出接口根据请求参数得到导出任务Bean和数据请求参数，通过导出任务Bean获取导出配置，数据，导出文件名，再调用配置解析器，excel生成器，生成excel并返回。

RequestMapping(value = "/export", method = RequestMethod.GET)
    	public void test(HttpServletRequest req, HttpServletResponse res) throws IOException {
    		String processorName = req.getParameter("processor");
    
    		if (StringUtils.isEmpty(processorName)) {
    			res.getWriter().println("parameter error!");
    			return;
    		}
    
    		IExcelExportProcessor processor = (IExcelExportProcessor) ExcelExportContext.getApplicationContext()
    				.getBean(processorName);
    		if (null == processor) {
    			res.getWriter().println("parameter error!");
    			return;
    		}
    
    		Map<String, Object> queryMap = getParameterMap(req);
    		String fileName = processor.getFilename();
    		String configPath = processor.getConfigPath();
    		Map<String, Object> map = processor.getDatas(queryMap);
    
    		ExcelConfigVO config = ExcelConfigHelper.GetExcelConfig(configPath);
    		fileName = ExcelExportHelper.getFileName(fileName, config);
    
    		HSSFWorkbook hw = ExcelExportHelper.export(map, config);
    		res.setContentType("application/octet-stream");
    		res.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO-8859-1"));
    		OutputStream out = null;
    		try {
    
    			out = res.getOutputStream();
    			hw.write(out);
    		} catch (IOException e) {
    
    			log.error("export excel error", e);
    		} finally {
    			try {
    				if (null != out) {
    					out.close();
    				}
    			} catch (IOException e) {
    				log.error("export excel error", e);
    			}
    
    		}
    	}

http GET请求 url?processorName=xxx&name=xxx$yyy=xxx，增加导出数据查询参数，即可导出excel。

**（完）**