Excle数据导出

 Excle实体类：

package com.nio.portal.persistence.entity; import lombok.Data; /** * Title: ExcleEntity * Description: Excle导出实体类 * @author lulu.wang.o * @version V1.0 * @date 2019-09-24 */ @Data public class ExcleEntity { private String titleName; private String alias; public ExcleEntity(String titleName, String alias) { this.titleName = titleName; this.alias = alias; } }

Controller:

/** * 导出BOP物料信息 * @param bizData * BOP示例名称 * @return 导出Excle文件 */ @PostMapping("/exportPartListFromBop") public Result exportPartListFromBop(@RequestParam String bizData, HttpServletRequest request, HttpServletResponse response) { if(StringUtil.isBlank(bizData)){ return Result.result(ResultType.ILLEGAL_ARGUMENTS.getCode(),"参数非法，请检查参数是否为空!"); } try { Map<String,String> map = JsonHelper.parseToMap(bizData); List<Map> result = mpdMbomCheckService.queryBopDataList(map); List<ExcleEntity> titleList = new ArrayList<>(); titleList.add(new ExcleEntity("Part No","partNo")); titleList.add(new ExcleEntity("Revision","revision")); titleList.add(new ExcleEntity("Part Name EN","partNameEN")); titleList.add(new ExcleEntity("Part Name CN","partNameCN")); titleList.add(new ExcleEntity("Partition code","SVPartitionCode")); titleList.add(new ExcleEntity("FNA","fna")); titleList.add(new ExcleEntity("Part Effectivity","partEffectivity")); titleList.add(new ExcleEntity("Quantity","quantity")); titleList.add(new ExcleEntity("Unit","unit")); titleList.add(new ExcleEntity("BroadCast Code","broadCastCode")); titleList.add(new ExcleEntity("动态扭矩","dynamicTorque")); titleList.add(new ExcleEntity("扭矩备注","torqueRemark")); titleList.add(new ExcleEntity("静态扭矩","jingtaiTorque")); titleList.add(new ExcleEntity("追溯性","traceability")); titleList.add(new ExcleEntity("备注","mark")); String fileName = "BOP物料信息.xls"; excelService.exportExcel(result,request,response,titleList,fileName); return Result.result(ResultType.SUCCESS.getCode(),"BOP物料信息导出Excle成功！"); }catch (Exception e){ log.error("BOP物料信息导出Excle失败，" + e.getMessage(), e); return Result.result(ResultType.SYSTEM_ERROR.getCode(),e.getMessage()); } }

 service:  

package com.nio.portal.service.impl.mpd; import com.alibaba.fastjson.util.IOUtils; import com.mysql.jdbc.StringUtils; import com.nio.portal.common.util.StringUtil; import com.nio.portal.persistence.dao.mpd.MpdMappingRuleDao; import com.nio.portal.persistence.entity.ExcleEntity; import com.nio.portal.service.mpd.ExcelService; import lombok.extern.slf4j.Slf4j; import org.apache.commons.collections.map.HashedMap; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import javax.activation.MimetypesFileTypeMap; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.util.*; import java.util.stream.Collectors; /** * Title: 数据导出公共方法 * @author lulu.wang.o * @version V1.0 * @date 2019-09-24 */ @Service @Slf4j @Transactional public class ExcelServiceImpl implements ExcelService { @Autowired private MpdMappingRuleDao mpdMappingRuleDao; /** * 创建标题 */ private List<HSSFCell> createCell(HSSFRow row2,List<ExcleEntity> header) { List<HSSFCell> list = new ArrayList<>(); for(int i = 0; i < header.size();i ++){ HSSFCell cell = row2.createCell(i); cell.setCellValue(header.get(i).getTitleName()); list.add(cell); } return list; } /** * 解决中文乱码 */ @Override public void setExcelFileName(String fileName, HttpServletRequest request, HttpServletResponse response) throws Exception { try { //获取文件类型 String mineType = new MimetypesFileTypeMap().getContentType(fileName); //将mineType放入响赢头 response.setContentType(mineType+";charset=utf-8"); //获取浏览器类型 String userAgent = request.getHeader("user-agent"); System.out.println(userAgent); if (userAgent != null && (userAgent.indexOf("Firefox") >= 0 || userAgent.indexOf("Chrome") >= 0 || userAgent.indexOf("Safari") >= 0)) { fileName = new String(fileName.getBytes(), "ISO8859-1"); } else { fileName = URLEncoder.encode(fileName, "UTF8"); // 其他浏览器 } //将附件名称和下载方式放入响应头中 response.setHeader("Content-Disposition", "attachment;filename=\""+ fileName+"\""); } catch (Exception e) { throw new Exception("获取导出文件名称失败", e); } } @Override public void exportExcel(List<Map> datas, HttpServletRequest request, HttpServletResponse response, List<ExcleEntity> header, String fileName) throws Exception { if (!StringUtils.isNullOrEmpty(String.valueOf(datas))) { log.info("从数据库查询到的数据为：" + datas.toString()); } HSSFWorkbook workBook = null; OutputStream outputStream = null; //创建excel workBook = new HSSFWorkbook(); //创建sheet HSSFSheet sheet = workBook.createSheet("Process code属性"); //创建样式 HSSFCellStyle cellStyle = workBook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); cellStyle.setFillBackgroundColor(HSSFColor.PALE_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建字体 HSSFFont font = workBook.createFont(); font.setFontHeight((short) ((short) 256 * 2)); cellStyle.setFont(font); //创建第一行 HSSFRow row = sheet.createRow(0); row.setHeight((short) ((short) 256 * 3)); //设置单元格合并 CellRangeAddress address = new CellRangeAddress(0, 0, 0, 14); sheet.addMergedRegion(address); HSSFCell cell = row.createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue("基本信息"); //创建第二行 HSSFRow row2 = sheet.createRow(1); //获取列的集合 List<HSSFCell> list = createCell(row2,header); //准备Key值 List<String> lis = header.stream().map(r -> r.getAlias()).collect(Collectors.toList()); //封装数据 for (int j = 0; j < datas.size(); j++) {//行 //获取行---从第三行开始封装数据 HSSFRow Row = sheet.createRow(j + 2); for (int i = 0; i < list.size(); i++) {//列 sheet.autoSizeColumn(i, true); if (!StringUtils.isNullOrEmpty((String) datas.get(j).get(lis.get(i)))) { Row.createCell(i).setCellValue(datas.get(j).get(lis.get(i)).toString()); } else { Row.createCell(i); } } } setExcelFileName(fileName, request, response); try { outputStream = response.getOutputStream(); workBook.write(outputStream); } catch (IOException e) { e.printStackTrace(); }finally { IOUtils.close(outputStream); } } }