POI操作Excel项目实例demo

直接看代码吧，注解十分详细，可直接复制粘贴使用在工作中。

demo结构图：

base.xlsx为模板文件

excellist.xml为excel标题栏的配置文件

实体类Student

package com.poi.entity;public class Student {private Integer id;private String name;private String age;private String phone;public Student() {super();}public Student(Integer id, String name, String age, String phone) {super();this.id = id;this.name = name;this.age = age;this.phone = phone;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + ", age=" + age+ ", phone=" + phone + "]";}}

配置文件excellist.xml

<?xml version="1.0" encoding="UTF-8"?><root><!-- 一个列表页 --><!-- name:显示的标题 --><!-- key:对应集合中的字段名 --><!-- value:对应excel列名 --><!-- width：设置excel数据每列的宽度 --><list id="student" name="学生信息明细表" var="e"><property key="id"value="编号"  width="4500"/><property key="name"value="姓名"  width="4500"/><property key="age" value="年龄"  width="3500"/><property key="phone" value="手机号 "  width="6000"/></list></root>

base.xlsx模板内容

工具类ExcelExportTool

package com.poi.util;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.jexl2.Expression;import org.apache.commons.jexl2.JexlContext;import org.apache.commons.jexl2.JexlEngine;import org.apache.commons.jexl2.MapContext;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import com.poi.util.ExcelListExportTool.ListExportCfg;/** *  * ExcelExportTool *  * Excel导出工具 *  */public class ExcelExportTool {private static final JexlEngine jexlEngine = new JexlEngine();private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");private static final int startCell = 0;@SuppressWarnings({ "unchecked", "rawtypes" })public static void exportToXls(Map beanParams,String type,SXSSFWorkbook workbook) {List<Object> lists = (List<Object>) beanParams.get("list");ListExportCfg cfg = (ListExportCfg) beanParams.get("cfg");/* *  SXSSF在把内存数据刷新到硬盘时，是把每个SHEET生成临时文件 *  这个临时文件可能会很大，有可以会达到G级别，如果文件的过大对你来说是一个问题， *  你可以使用下面的方法让SXSSF来进行压缩，当然性能也会有一定的影响 */workbook.setCompressTempFiles(true);//获取名为"output"的sheet，若无则创建名为"cfg.getName()"的sheetSheet sheet = workbook.getSheet("Output");if (sheet == null) {sheet = workbook.createSheet("工作表");}//设置创建的sheet的名字（getSheetIndex(sheet)：得到创建的sheet的坐标）workbook.setSheetName(workbook.getSheetIndex(sheet), "工作表");int r = 0, c = startCell;//设置每列的宽List<String> widthList = cfg.getWidthList();for(int i=0;i<widthList.size();i++){sheet.setColumnWidth((short)i, (short)Integer.parseInt(widthList.get(i)));}//创建标题行Row row = sheet.createRow(r++);//设置标题栏样式row.setHeight((short) 500);//高Cell cell = row.createCell(c);//创建标题单元格cell.setCellValue(cfg.getName());//设置标题名cell.setCellStyle(ExcelListExportTool.titleStyle);//设置标题单元格样式//设置生成时间row = sheet.createRow(r++);row.setHeight((short) 400);c = startCell;Cell timeCell = row.createCell(c + cfg.getKeyList().size() - 3);timeCell.setCellValue("生成时间:"+ sdf.format(new Date()));//设置时间靠右显示CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);    timeCell.setCellStyle(cellStyle);//创建列名行row = sheet.createRow(r++);row.setHeight((short) 400);c = startCell;//循环获取生成列名for (String str : cfg.getValueList()) {cell = row.createCell(c++);cell.setCellValue(str);//列名赋值cell.setCellStyle(ExcelListExportTool.headerStyle);//列单元格样式}//字符串表达式集合List<Expression> exprList = new ArrayList<Expression>(cfg.getKeyList().size());try {for (String key : cfg.getKeyList()) {//jexl解析字符串表达式（循环获取字符串变量名）Expression jexlExpresssion = null;jexlExpresssion = jexlEngine.createExpression(key.substring(2, key.length()-1));exprList.add(jexlExpresssion);}} catch (Exception e) {e.printStackTrace();}if(lists!=null){try {//循环写入集合中所有的实体类数据到excel中for (Object obj : lists) {Map<String, Object> map = new HashMap<String, Object>();//存储和jexl字符串表达式名相似的key及对应的整条实体类记录objmap.put("e", obj);c = startCell;row = sheet.createRow(r++);//循环写每行for (Expression expr : exprList) {if (expr != null) {//把包含数据的map放入jexl的环境中JexlContext context = new MapContext(map);cell = row.createCell(c++);//取出对应的(expr.evaluate(context))cell.setCellValue(String.valueOf(expr.evaluate(context)));if (r % 2 == 0) {cell.setCellStyle(ExcelListExportTool.line1Style);} else {cell.setCellStyle(ExcelListExportTool.line2Style);}context = null;//赋值null，让GC回收}}map = null;//赋值null，让GC回收}} catch (Exception e) {e.printStackTrace();}}//合并单元格int totalLenght = cfg.getKeyList().size()-1;sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, totalLenght));//合并标题sheet.addMergedRegion(new CellRangeAddress(1, 1, totalLenght-2, totalLenght));//合并生成时间}}

初始化工具类ExcelListExportTool

package com.poi.util;import java.io.File;import java.io.UnsupportedEncodingException;import java.net.URLDecoder;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.dom4j.Document;import org.dom4j.DocumentException;import org.dom4j.Element;import org.dom4j.io.SAXReader;import org.springframework.util.StringUtils;public class ExcelListExportTool {private static Map<String, ListExportCfg> cfgMap;public static CellStyle titleStyle;public static String baseFile = null;public static CellStyle headerStyle;public static CellStyle line1Style;public static CellStyle line2Style;public static Workbook baseWorkbook;public static <E> void exportListToPer(String type, List<E> list, SXSSFWorkbook workbook) {ListExportCfg cfg = cfgMap.get(type);Map<String, Object> beanParams = new HashMap<>();beanParams.put("list", list);beanParams.put("cfg", cfg);if (cfg != null) {ExcelExportTool.exportToXls(beanParams,type,workbook);}}//初始化excellist中的值到cfgMap�?@SuppressWarnings("unchecked")private static void initData() {String path = ExcelListExportTool.class.getResource("/base.xlsx").getPath();String basePath = null;try {basePath = URLDecoder.decode(path,"utf-8");} catch (UnsupportedEncodingException e1) {e1.printStackTrace();} cfgMap = new HashMap<>();SAXReader reader = new SAXReader();Document document = null;try {document = reader.read(ExcelListExportTool.class.getResourceAsStream("/excellist.xml"));} catch (DocumentException e) {e.printStackTrace();}baseFile = basePath;try {//获取已存在的base.xlsx的单元格样式(只需得到第一列的前面四行的cell样式即可)baseWorkbook = WorkbookFactory.create(new File(basePath));Sheet st = baseWorkbook.getSheetAt(0);titleStyle = st.getRow(0).getCell(0).getCellStyle();headerStyle = st.getRow(1).getCell(0).getCellStyle();line1Style = st.getRow(2).getCell(0).getCellStyle();line2Style = st.getRow(3).getCell(0).getCellStyle();} catch (Exception e) {e.printStackTrace();}List<Element> listEles = document.getRootElement().elements("list");Pattern charPattern = Pattern.compile("^(\\w+)$");for (Element list : listEles) {ListExportCfg cfg = new ListExportCfg();cfg.setId(list.attributeValue("id"));cfg.setName(list.attributeValue("name"));cfg.setVarName(list.attributeValue("var"));List<Element> propEles = list.elements("property");String key = "", value = "", width = "";Matcher m;//遍历获取excellist中的值，使之成为报表的目录栏for (Element prop : propEles) {if (!StringUtils.isEmpty(prop.attribute("key"))) {key = prop.attributeValue("key");}if (!StringUtils.isEmpty(prop.attribute("value"))) {value = prop.attributeValue("value");}if (!StringUtils.isEmpty(prop.attribute("width"))) {width = prop.attributeValue("width");}m = charPattern.matcher(key);if (m.find()) {//matcher.group(n);  匹配pattern中第n个括号中的表达式key = "${" + cfg.getVarName() + "." + m.group(1) + "}";}cfg.addWidth(width);cfg.addKey(key);cfg.addValue(value);}cfgMap.put(cfg.getId(), cfg);}}static {initData();}public static class ListExportCfg {private String id;private String name;private String varName;private String width;private List<String> keyList = new ArrayList<>();private List<String> widthList = new ArrayList<>();public String getId() {return id;}public String getVarName() {return varName;}public void setVarName(String varName) {this.varName = varName;}public void setId(String id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public void addKey(String key) {keyList.add(key);}public void addWidth(String width) {widthList.add(width);}public void addValue(String value) {valueList.add(value);}public List<String> getKeyList() {return keyList;}public void setKeyList(List<String> keyList) {this.keyList = keyList;}public List<String> getValueList() {return valueList;}public void setValueList(List<String> valueList) {this.valueList = valueList;}public String getWidth() {return width;}public void setWidth(String width) {this.width = width;}public List<String> getWidthList() {return widthList;}public void setWidthList(List<String> widthList) {this.widthList = widthList;}private List<String> valueList = new ArrayList<>();}}

工具类：FileUtil

package com.poi.util;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import org.apache.commons.io.FileUtils;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.WorkbookFactory;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class FileUtil {public static File getCopyFile(File file){String destFilePath = "D:\\temp.xlsx";File copyFile = new File(destFilePath);try {FileUtils.copyFile(file, copyFile);copyFile.createNewFile();//创建copyFile的实例} catch (IOException e1) {e1.printStackTrace();}return copyFile;}public static SXSSFWorkbook getWorkBook(File file){SXSSFWorkbook workbook = null;try {workbook = new SXSSFWorkbook((XSSFWorkbook) WorkbookFactory.create(new FileInputStream(file)));} catch (InvalidFormatException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return workbook;}}

测试类：MainTest

package com.poi.test;import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.UnsupportedEncodingException;import java.net.URLDecoder;import java.util.ArrayList;import java.util.List;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import com.poi.entity.Student;import com.poi.util.ExcelListExportTool;import com.poi.util.FileUtil;public class MainTest {@SuppressWarnings("unchecked")public static <E> void main(String[] args) {//获得集合，项目中可根据实际需要从数据库中获取List<Student> list = new ArrayList<Student>();Student s1 = new Student(1, "张三", "11", "11111111111");Student s2 = new Student(2, "李四", "12", "22222222222");Student s3 = new Student(3, "王五", "13", "33333333333");list.add(s1);list.add(s2);list.add(s3);List<E> copyList = (List<E>)list;//集合泛型//获取base.xlsx模板文件String filePath = ExcelListExportTool.class.getClassLoader().getResource("base.xlsx").getPath();String basePath = null;try {//但是在中文编码环境下，空格会变成"%20"从而使得路径错误basePath = URLDecoder.decode(filePath,"utf-8");} catch (UnsupportedEncodingException e1) {e1.printStackTrace();} File file = new File(basePath);//新建一个workbookSXSSFWorkbook workbook = FileUtil.getWorkBook(file);//创建一个copy文件去存取sheet数据File copyFile = FileUtil.getCopyFile(file);if(workbook!=null){FileOutputStream fos = null;try {fos = new FileOutputStream(copyFile);ExcelListExportTool.exportListToPer("student", copyList, workbook);} catch (FileNotFoundException e) {e.printStackTrace();}finally{try {workbook.write(fos);workbook.dispose();if(fos!=null){fos.close();}} catch (IOException e) {e.printStackTrace();}}}}}

直接运行main方法即可在D盘中生成所得文件temp.xlsx。

注：若需要在前端页面中下载此Excel文件，可使用返回ResponseEntity<byte[]>来进行下载操作，就不具体演示了。。