springboot中使用poi导出excel文件
1.POI简介
Jakarta POI 是一套用于访问微软格式文档的Java API.
组件HWPF用于操作Word的;
组件HSSF用于操作Excel格式文件.
2.常用组件
HSSFWorkbook -- excel的文档对象
HSSFSheet -- excel的表单
HSSFRow -- excel的行
HSSFCell -- excel的格子单元
HSSFHeader -- sheet头
HSSFFooter -- sheet尾（只有打印的时候才能看到效果）
HSSFDataFormat -- 日期格式
HSSFCellStyle -- cell样式
HSSFFont -- excel字体
HSSFColor -- 颜色
HSSFDateUtil -- 日期
HSSFPrintSetup -- 打印
HSSFErrorConstants -- 错误信息表
合并单元格,构造参数依次表示起始行,截止行,起始列,截止列
eg:sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
设置单元格样式时,先创建样式,再指定到单元格。
样式可指定对齐方式、背景填充方式及颜色、上下左右边框样式及颜色
设置单元格的填充方式，以及前景颜色和背景颜色时注意：
a.如果需要前景颜色或背景颜色，一定要指定填充方式，两者顺序无所谓；
b.如果同时存在前景颜色和背景颜色，前景颜色的设置要写在前面；
c.前景颜色不是字体颜色。
3.结构说明
Excel <-- 一 工作空间(workbook)
workbook <-- 多 工作表(sheet)
sheet <-- 多 行(row) + 多列(cell)
4.操作步骤
a、用HSSFWorkbook打开或者创建Excel文件对象
b、用HSSFWorkbook对象返回或者创建Sheet对象
c、用Sheet对象返回行对象，用行对象得到Cell对象
d、对Cell对象读写
5.实例
第一种方法 固定导出字段导出excel;
第二种方法 用配置的方式将导出字段存储数库中导出excel,可重用;
pom.xml
<!-- json转换工具 --> <dependency> <groupId>net.sf.json-lib</groupId> <artifactId>json-lib</artifactId> <version>2.4</version> <classifier>jdk15</classifier> </dependency> <!-- easypoi 导入导出插件--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.0.3</version> </dependency> <!-- POI，excel导入需要的 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.1</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency>
IExportExcleService.java 接口
package com.wulss.jakartapoi.hssf; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public interface IExportExcelService { /** * 第一种 直接导出excle * @param req * @param resp * @param list 要导出的数据 */ public void exportExcelWithSimple(HttpServletRequest req,HttpServletResponse resp,List<UserConsumeDetailRecord> list); /** * 第二种 根据exportKey查询出要导出的字段,并匹配list每个类中字段来导出excel,只需维护数据库,即可实现该方法的重用 * @param exportKey 数据库中存储的导出英文名 * @param fileName 文件名 * @param list 要导出的数据 * @param req * @param resp */ public void exportExcelWithDispose(String exportKey,String fileName,List<?> list,HttpServletRequest req,HttpServletResponse resp); }
ExportExcleServiceImpl.java 实现类
package com.wulss.jakartapoi.hssf; import java.text.SimpleDateFormat; import java.util.List; import java.util.UUID; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import net.sf.json.JSONArray; import net.sf.json.JSONObject; @Service public class ExportExcelServiceImpl extends ExportExcelBaseService implements IExportExcelService{ @Autowired private ExportMapper exportMapper; @Override public void exportExcelWithSimple(HttpServletRequest req,HttpServletResponse resp,List<UserConsumeDetailRecord> list){ String fileName = "个人消费明细表" + UUID.randomUUID().toString(); try { //工作空间 HSSFWorkbook workbook = new HSSFWorkbook(); //第1张工作表 HSSFSheet sheet1 = workbook.createSheet("个人消费明细"); sheet1.setDefaultRowHeightInPoints(20);//行高 sheet1.setDefaultColumnWidth(20);//列宽 //行标题 HSSFRow titleRow = sheet1.createRow(0); titleRow.createCell(0).setCellValue("个人消费明细表"); sheet1.addMergedRegion(new CellRangeAddress(0,0,0,4));////合并单元格 //行表头 HSSFRow headRow = sheet1.createRow(sheet1.getLastRowNum() + 1); headRow.createCell(0).setCellValue("序号"); headRow.createCell(headRow.getLastCellNum()).setCellValue("用户姓名"); headRow.createCell(headRow.getLastCellNum()).setCellValue("消费金额"); headRow.createCell(headRow.getLastCellNum()).setCellValue("消费时间"); headRow.createCell(headRow.getLastCellNum()).setCellValue("消费项目"); //行表头单元格设置样式 for(int h = 0; h < headRow.getLastCellNum() ; h ++) { headRow.getCell(h).setCellStyle(super.getCellStyle(workbook)); } //行数据体 int index = 1; HSSFRow bodyRow = null; for(UserConsumeDetailRecord bean:list) { bodyRow = sheet1.createRow(sheet1.getLastRowNum() + 1); bodyRow.createCell(0).setCellValue(index ++ ); bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(bean.getUserName()); bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(bean.getConsumeAmount()); bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue( new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(bean.getConsumeDate()));//.split("\\.")[0] bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(bean.getConsumeTitle()); } //输出 super.outExcelStream(resp, workbook, fileName); //将生成的excel文件写到磁盘 // FileOutputStream fos = new FileOutputStream(fileName + ".xls"); // workbook.write(fos); // fos.close(); //从磁盘删除删除文件 // super.deleteFileDir(fileName + ".xls"); }catch(Exception e){ e.printStackTrace(); } } @Override public void exportExcelWithDispose(String exportKey,String fileName,List<?> list,HttpServletRequest req,HttpServletResponse resp){ //查询一表 级联 获取多表集合 List<ExportFieldBean> fieldBeanList = exportMapper.getExportByExportKey(exportKey).getFieldBeanList(); try { //工作空间 HSSFWorkbook workbook = new HSSFWorkbook(); //第1张工作表 HSSFSheet sheet1 = workbook.createSheet("个人消费明细"); sheet1.setDefaultRowHeightInPoints(20);//行高 sheet1.setDefaultColumnWidth(20);//列宽 //行表头 HSSFRow headRow = sheet1.createRow(0); headRow.createCell(0).setCellValue("序号"); headRow.getCell(0).setCellStyle(super.getCellStyle(workbook)); //创建行表头单元格并设置样式 for(ExportFieldBean fieldBean:fieldBeanList) { headRow.createCell(headRow.getLastCellNum()).setCellValue(fieldBean.getExportName());//赋值 headRow.getCell(headRow.getLastCellNum()).setCellStyle(super.getCellStyle(workbook));//样式 } //创建行数据体 int index = 1; HSSFRow bodyRow = null; JSONArray jsonArray = JSONArray.fromObject(list);// --赋值(先转json, 再赋值, 通用性高) for(Object obj:jsonArray) { bodyRow = sheet1.createRow(sheet1.getLastRowNum() + 1); bodyRow.createCell(0).setCellValue(index ++ ); for(ExportFieldBean fieldBean:fieldBeanList) { bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(((JSONObject)obj).get(fieldBean.getExportCode()) + "");//赋值 // bodyRow.getCell(bodyRow.getLastCellNum()).setCellStyle(super.getCellStyle(workbook));//样式 } } //输出 super.outExcelStream(resp, workbook, fileName); }catch(Exception e){ e.printStackTrace(); } } }
ExportExcelBaseService.java 基础类
package com.wulss.jakartapoi.hssf; import java.io.File; import java.io.IOException; import java.io.OutputStream; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.stereotype.Component; @Component public class ExportExcelBaseService { /** * 获取设置好的样式 * @param workbook 工作空间 * @return */ public HSSFCellStyle getCellStyle(HSSFWorkbook workbook) { HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//单元格-垂直居中 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//单元格-水平居中 cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);//背景色-方块填充 cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);//前背景色-天蓝 cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);//后背景色-浅黄 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//底边框样式-倾斜断点 cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);//底边框颜色-暗红 cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//日期显示格式 // headRowCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@")); cellStyle.setFont(this.getFont(workbook));//设置字体 return cellStyle; } /** * 获取设置好的字体 * @param workbook 工作空间 * @return */ public HSSFFont getFont(HSSFWorkbook workbook) { HSSFFont fontStyle = workbook.createFont(); fontStyle.setFontName("宋体");//名称-宋体 fontStyle.setFontHeightInPoints((short)13);//高度-13 fontStyle.setColor(HSSFColor.WHITE.index);//颜色-白色 fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗 fontStyle.setItalic(true);//斜体 fontStyle.setUnderline(HSSFFont.U_SINGLE);//下划线 return fontStyle; } /** * 通过流的方式输出excle到页面 * @param response 响应 * @param workbook 工作空间 * @param fileName 文件名 */ public void outExcelStream(HttpServletResponse response, Workbook workbook, String fileName){ OutputStream os = null; try { os = response.getOutputStream(); response.setContentType("application/x-download"); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls"); workbook.write(os); os.flush(); }catch (Exception e) { e.printStackTrace(); }finally { if(os!=null){ try { os.close(); } catch (IOException e) { e.printStackTrace(); } } } } //删除单个文件夹 public void deleteFileDir(String fileName) { File file = new File(fileName); DeleteAll(file); } public void DeleteAll(File dir) { if (dir.isFile()) { dir.delete(); return; } else { File[] files = dir.listFiles(); for (File file : files) { DeleteAll(file); } } dir.delete(); } }
ExportExcelController.java
package com.wulss.jakartapoi.hssf; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.UUID; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RestController; @RestController @RequestMapping("/exportExlce") public class ExportExcelController { @Autowired IExportExcelService iExportExcelService; @RequestMapping(value="/withSimple",method=RequestMethod.GET) public String withSimple(HttpServletRequest req,HttpServletResponse resp) { List<UserConsumeDetailRecord> list = new ArrayList<>(); UserConsumeDetailRecord record = null; for(int i=0;i<10;i++) { record = new UserConsumeDetailRecord(); record.setUserName("奥雷里亚诺"); record.setConsumeAmount(6.66); record.setConsumeDate(new Date()); record.setConsumeTitle("喝酒吃肉"); list.add(record); } iExportExcelService.exportExcelWithSimple(req, resp, list); return "success"; } @RequestMapping(value="/withDispose",method=RequestMethod.GET) public String WithDispose(HttpServletRequest req,HttpServletResponse resp) { List<UserConsumeDetailRecord> list = new ArrayList<>(); UserConsumeDetailRecord record = null; for(int i=0;i<10;i++) { record = new UserConsumeDetailRecord(); record.setUserName("奥雷里亚诺"); record.setConsumeAmount(6.66); record.setConsumeDate(new Date()); record.setConsumeTitle("喝酒吃肉"); list.add(record); } iExportExcelService.exportExcelWithDispose("consume_detail", "个人消费明细表" + UUID.randomUUID().toString(), list, req, resp); return "success"; } }
第二种方法涉及到的建表语句
CREATE TABLE `export` ( `id` int(32) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `export_code` varchar(255) DEFAULT NULL COMMENT '导出主题英文名', `export_name` varchar(255) DEFAULT NULL COMMENT '导出主题中文名' PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='导出主题表'; CREATE TABLE `export_field` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `export_id` int(11) unsigned DEFAULT NULL COMMENT '导出主表ID', `field_code` varchar(55) DEFAULT NULL COMMENT '字段英文名', `field_name` varchar(64) DEFAULT NULL COMMENT '字段中文名', `sort` int(11) unsigned DEFAULT '1' COMMENT '排序字段' PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='导出字段表';
数据截图
表一
表二
涉及到的 UserConsumeDetailRecord.java 、ExportBean.java、ExportFieldBean.java、ExportMapper.java等，mybatis映射文件就不贴出来了
/** * * @Descript TODO (用户消费实体) * @author Administrator * @date 2019年5月24日 * */ public class UserConsumeDetailRecord { private int id; private int userId; private String userName; private String userTel; private String consumeTitle; private Date consumeDate; private Double consumeAmount; public int getId() { return id; } public void setId(int id) { this.id = id; } public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getUserTel() { return userTel; } public void setUserTel(String userTel) { this.userTel = userTel; } public String getConsumeTitle() { return consumeTitle; } public void setConsumeTitle(String consumeTitle) { this.consumeTitle = consumeTitle; } public Date getConsumeDate() { return consumeDate; } public void setConsumeDate(Date consumeDate) { this.consumeDate = consumeDate; } public Double getConsumeAmount() { return consumeAmount; } public void setConsumeAmount(Double consumeAmount) { this.consumeAmount = consumeAmount; } }
/** * * @Descript TODO (导出主题表) * @author Administrator * @date 2019年5月27日 * */ public class ExportBean { private Integer id; private String exportCode; private String exportName; private List<ExportFieldBean> fieldBeanList; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getExportCode() { return exportCode; } public void setExportCode(String exportCode) { this.exportCode = exportCode; } public String getExportName() { return exportName; } public void setExportName(String exportName) { this.exportName = exportName; } public List<ExportFieldBean> getFieldBeanList() { return fieldBeanList; } public void setFieldBeanList(List<ExportFieldBean> fieldBeanList) { this.fieldBeanList = fieldBeanList; } }
/** * * @Descript TODO (导出字段表) * @author Administrator * @date 2019年5月27日 * */ public class ExportFieldBean { private Integer id; private Integer exportId; private String exportCode; private String exportName; private Integer sort; private ExportBean exportBean; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getExportId() { return exportId; } public void setExportId(Integer exportId) { this.exportId = exportId; } public String getExportCode() { return exportCode; } public void setExportCode(String exportCode) { this.exportCode = exportCode; } public String getExportName() { return exportName; } public void setExportName(String exportName) { this.exportName = exportName; } public Integer getSort() { return sort; } public void setSort(Integer sort) { this.sort = sort; } public ExportBean getExportBean() { return exportBean; } public void setExportBean(ExportBean exportBean) { this.exportBean = exportBean; } }
@Mapper public interface ExportMapper { ExportBean getExportByExportKey(String exportKey); }
其他更详细的可以参考此文章https://www.cnblogs.com/dawnheaven/p/4462572.html
