spring boot实现导出数据到excel

1.导入依赖;

<!--数据导出依赖 excel--> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17</version> </dependency>

2.设置excel文件的表头

import java.util.ArrayList; import java.util.HashMap; import java.util.Map; /** * @desc:数据导出，生成excel文件时的列名称集合 * @author: chao * @time: 2018.6.11 */ public class ColumnTitleMap { private Map<String, String> columnTitleMap = new HashMap<String, String>(); private ArrayList<String> titleKeyList = new ArrayList<String> (); public ColumnTitleMap(String datatype) { switch (datatype) { case "passenger": initPassengerColu(); initPassengerTitleKeyList(); break; default: break; } } /** * mysql用户表需要导出字段--显示名称对应集合 */ private void initPassengerColu() { columnTitleMap.put("idCard", "身份证号"); columnTitleMap.put("passName", "乘客姓名"); columnTitleMap.put("sex", "性别"); columnTitleMap.put("birdate", "出生日期"); columnTitleMap.put("phone", "手机号"); columnTitleMap.put("birthplace", "出生地户籍"); columnTitleMap.put("residence", "现住址"); columnTitleMap.put("source", "注册来源"); } /** * mysql用户表需要导出字段集 */ private void initPassengerTitleKeyList() { titleKeyList.add("idCard"); titleKeyList.add("passName"); titleKeyList.add("sex"); titleKeyList.add("birdate"); titleKeyList.add("phone"); titleKeyList.add("birthplace"); titleKeyList.add("residence"); titleKeyList.add("source"); } public Map<String, String> getColumnTitleMap() { return columnTitleMap; } public ArrayList<String> getTitleKeyList() { return titleKeyList; } }

3.导出工具类

把对象转MAp

import java.lang.reflect.Field; import java.util.HashMap; import java.util.Map; public class ObjToMapUtil { /** * 对象转map * @param obj * @return */ public Map<String, Object> objToMap(Object obj) { Map<String, Object> map = new HashMap<String, Object>(); Field[] fields = obj.getClass().getDeclaredFields(); // 获取f对象对应类中的所有属性域 for (int i = 0, len = fields.length; i < len; i++) { String varName = fields[i].getName(); varName = varName.toLowerCase(); // 将key置为小写，默认为对象的属性 try { boolean accessFlag = fields[i].isAccessible(); // 获取原来的访问控制权限 fields[i].setAccessible(true); // 修改访问控制权限 Object o = fields[i].get(obj); // 获取在对象f中属性fields[i]对应的对象中的变量 if (o != null){ map.put(varName, o.toString()); } fields[i].setAccessible(accessFlag); // 恢复访问控制权限 } catch (IllegalArgumentException ex) { ex.printStackTrace(); } catch (IllegalAccessException ex) { ex.printStackTrace(); } } return map; } }

把查询到的数据读入excel

import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import java.util.Map; /** * @desc:数据导出，生成excel文件 * @author: chao * @time: 2018.6.12 */ @Service public class ExportExcelUtil { public OutputStream expoerDataExcel(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String,Object>> src_list) throws IOException { String xlsFile_name = System.currentTimeMillis() + ".xlsx"; //输出xls文件名称 //内存中只创建100个对象 Workbook wb = new SXSSFWorkbook(100); //关键语句 Sheet sheet = null; //工作表对象 Row nRow = null; //行对象 Cell nCell = null; //列对象 int rowNo = 0; //总行号 int pageRowNo = 0; //页行号 for (int k=0;k<src_list.size();k++) { Map<String,Object> srcMap = src_list.get(k); //写入300000条后切换到下个工作表 if(rowNo%300000==0){ wb.createSheet("工作簿"+(rowNo/300000));//创建新的sheet对象 sheet = wb.getSheetAt(rowNo/300000); //动态指定当前的工作表 pageRowNo = 0; //新建了工作表,重置工作表的行号为0 // -----------定义表头----------- nRow = sheet.createRow(pageRowNo++); // 列数 titleKeyList.size() for(int i=0;i<titleKeyList.size();i++){ Cell cell_tem = nRow.createCell(i); cell_tem.setCellValue(titleMap.get(titleKeyList.get(i))); } rowNo++; // --------------------------- } rowNo++; nRow = sheet.createRow(pageRowNo++); //新建行对象 // 行，获取cell值 for(int j=0;j<titleKeyList.size();j++){ nCell = nRow.createCell(j); if (srcMap.get(titleKeyList.get(j)) != null) { if("taxidriver".equals(titleKeyList.get(j))){ if("0".equals(srcMap.get(titleKeyList.get(j)).toString())){ nCell.setCellValue("否"); }else if("1".equals(srcMap.get(titleKeyList.get(j)).toString())){ nCell.setCellValue("是"); } }else if("drivergender".equals(titleKeyList.get(j))){ if("1".equals(srcMap.get(titleKeyList.get(j)).toString())){ nCell.setCellValue("男"); }else if("2".equals(srcMap.get(titleKeyList.get(j)).toString())){ nCell.setCellValue("女"); }else if("9".equals(srcMap.get(titleKeyList.get(j)).toString())){ nCell.setCellValue("未知"); } }else { nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString()); } } else { nCell.setCellValue(""); } } } response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name); response.flushBuffer(); OutputStream outputStream = response.getOutputStream(); wb.write(response.getOutputStream()); wb.close(); outputStream.flush(); outputStream.close(); return outputStream; } }

4.导出数据到excel的controller

@PostMapping("/passenger/excel/download") public Result exportPassengerData(String phone,String start,String end,String source,String state,HttpServletResponse response){ try { Map<String,Object> map = new HashMap<>(); //查询需要导出的数据 List<Passenger> passengerList = passengerService.getDao().getPassengerByCondition(phone,start,end,source,state); List<Map<String,Object>> list = new ArrayList<>(); //把对象转map passengerList.forEach(passenger -> { Map<String, Object> item = new ObjToMapUtil().objToMap(passenger); list.add(item); }); ArrayList<String> titleKeyList= new ColumnTitleMap("passenger").getTitleKeyList(); Map<String, String> titleMap = new ColumnTitleMap("passenger").getColumnTitleMap(); //导出数据到excel OutputStream outputStream = new ExportExcelUtil().expoerDataExcel(response, titleKeyList, titleMap, list); map.put("data",outputStream); return Result.ok(map); }catch (Exception e){ e.printStackTrace(); return Result.fail(901,"导出乘客信息失败！"); } }