Execel 导出

IT民工
186 阅读2分钟

自己写的一个excel导出功能，上代码。 /**

  • @author 我是你的小乖乖

  • @version 1.0

  • @date 2020-07-26 12:02

  • @descible: 资金付款导出接口 */ @RestController @RequestMapping("/capital") public class CapitalExportController {

    @Autowired private IPaymentInfoImplService iPaymentInfoImplService;

    @Autowired private IFundPaymentInfoService iFundPaymentInfoService;

    @Autowired private IFundPaymentInfoService fundPaymentInfoService;

    @Autowired private CapitalExportService capitalExportService;

    @Autowired private IFundExecutorDAO iFundExecutorDAO;

    private static final Logger logger = LoggerFactory.getLogger(CapitalExportController.class);

    @RequestMapping("/export") public void capitalExport(@RequestParam("param") String param, HttpServletRequest request, HttpServletResponse response) throws IOException { ExportVO exportVO = JsonUtils.fromJSON(param, ExportVO.class); List datas = new ArrayList<>(); HashMap<String, String> map = new HashMap<>(); List fundPaymentInfoVOS1 = exportVO.getFundPaymentInfoVOS(); for (FundPaymentInfoVO fundPaymentInfoVO : fundPaymentInfoVOS1) { String paymentInfoId = fundPaymentInfoVO.getPaymentInfoId(); String version = String.valueOf(fundPaymentInfoVO.getVersion()); datas.add(paymentInfoId); map.put(paymentInfoId, version); } request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); OutputStream out = response.getOutputStream(); try { List paymentInfoVOList = iPaymentInfoImplService.selectAllList(datas); logger.info("===========【{}】", paymentInfoVOList); String batchNo = SerialNumberUtil.getBatchNo(new Date(), "ECS"); List list1 = new ArrayList<>(); for (int i = 0; i < paymentInfoVOList.size(); i++) { FundPaymentInfoVO fundPaymentInfoVO = paymentInfoVOList.get(i); String paymentInfoId = fundPaymentInfoVO.getPaymentInfoId(); fundPaymentInfoVO.setStatus(FundPaymentStatusEnum.PAYMENT); fundPaymentInfoVO.setItem01(batchNo); fundPaymentInfoVO.setItem03("报盘"); String version = map.get(paymentInfoId); fundPaymentInfoVO.setVersion(Integer.parseInt(version)); //修改状态xx iFundPaymentInfoService.updateFundPaymentInfo(fundPaymentInfoVO); //网银-对 //更新T_FUND_EXECUTOR表中状态 FundExecutorDO fundExecutorDO =new FundExecutorDO(); fundExecutorDO.setPaymentInfoId(paymentInfoId); fundExecutorDO.setPaymentStatus(FundPaymentStatusEnum.PAYMENT); iFundExecutorDAO.updateFundExecutorInfo(fundExecutorDO); FundPaymentInfoQueryVO queryVO = new FundPaymentInfoQueryVO(); queryVO.setPaymentInfoId(fundPaymentInfoVO.getPaymentInfoId()); queryVO.setBillCode(fundPaymentInfoVO.getBillCode()); queryVO.setPageNum(1); queryVO.setPageSize(1000); PageInfo data = this.fundPaymentInfoService.getFundPaymentInfo(queryVO); List fundPaymentInfoVOS = data.getList(); FundPaymentInfoVO fundPaymentInfoVO1 = fundPaymentInfoVOS.get(0); logger.info("返回的封装实体类【{}】", fundPaymentInfoVO1); //对私 if ("SYS_USER".equals(fundPaymentInfoVO1.getPayeeType())) { list1.add(fundPaymentInfoVO1); }

         }
     capitalExportService.exportDuiShi(request,response, list1);
 } catch (Exception e) {
     logger.error("对私导出失败【{}】", e.getMessage());
     e.printStackTrace();
 } finally {
     out.close();
 }
 return;

    } }

实现类： /**

  • @author 欧阳

  • @version 1.0

  • @date 2020-07-26 13:26

  • @descible: */ @Service public class CapitalExportServiceImpl implements CapitalExportService {

    private static final Logger logger = LoggerFactory.getLogger(CapitalExportServiceImpl.class);

    @Override public void exportDuiShi(HttpServletRequest request,HttpServletResponse response, List list1) throws Exception { OutputStream out = response.getOutputStream(); try { String agent = request.getHeader("USER-AGENT").toLowerCase(); response.setContentType("application/vnd.ms-excel"); String fname = "对私批量转账"; String codedFileName = java.net.URLEncoder.encode(fname, "UTF-8"); if (agent.contains("firefox")) { response.setCharacterEncoding("utf-8"); response.setHeader("content-disposition", "attachment;fname=" + new String(fname.getBytes(), "ISO8859-1") + ".xls"); } else { response.setHeader("content-disposition", "attachment;fname=" + codedFileName + ".xls"); } List sheetTitle = new ArrayList<>(); String[] rowsName = new String[]{"账号 A/C No.", "账号金额 A/C Amount ", "姓名 Name", "摘要 Remark","证件号码 ID No."}; List<Object[]> dataList = new ArrayList<>(); sheetTitle.add(fname); Object[] objs = null; if (null != list1 && list1.size() > 0) { for (int i = 0; i < list1.size(); i++) { objs = new Object[rowsName.length]; FundPaymentInfoVO fundPaymentInfoVO = list1.get(i); objs[0] = fundPaymentInfoVO.getPayeeAccountNo(); objs[1] = fundPaymentInfoVO.getPaymentAmount(); objs[2] = fundPaymentInfoVO.getPayeeName(); objs[3] = fundPaymentInfoVO.getSummary(); objs[4]=fundPaymentInfoVO.getItem10(); dataList.add(objs); } ExcelUtils ex = new ExcelUtils(sheetTitle, rowsName, dataList, fname); ex.export(response); } } catch (Exception e) { logger.error("代发工资批量【{}】", e.getMessage()); e.printStackTrace(); } finally { out.close(); } }

}

工具类： /**

  • @author 刘磊

  • @version 1.0

  • @date 2020-07-26 17:58

  • @descible: 文件下载工具 */ public class ExcelUtils {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    // 显示的导出表的标题 private List sheetTitle; // 导出表的列名 private String[] rowName; private String fname;

    private List<Object[]> dataList = new ArrayList<Object[]>(); HttpServletResponse response;

    // 构造方法，传入要导出的数据 public ExcelUtils(List sheetTitle, String[] rowName, List<Object[]> dataList, String fname) { this.dataList = dataList; this.rowName = rowName; this.sheetTitle = sheetTitle; this.fname = fname; }

    public ExcelUtils(List sheetTitle, String[] rowName) { this.rowName = rowName; this.sheetTitle = sheetTitle; }

    /*

    • 导出数据 */ public void export(HttpServletResponse response) throws Exception { try { HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象 for (int i = 0; i < sheetTitle.size(); i++) { HSSFSheet sheet = workbook.createSheet(sheetTitle.get(i)); // 创建工作表 // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】 HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);// 获取列头样式对象 HSSFCellStyle style = this.getStyle(workbook); // 单元格样式对象 // 定义所需列数 int columnNum = rowName.length; HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行) // 将列头设置到sheet的单元格中 for (int n = 0; n < columnNum; n++) { HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型 HSSFRichTextString text = new HSSFRichTextString(rowName[n]); cellRowName.setCellValue(text); // 设置列头单元格的值 cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式 } // 将查询出的数据设置到sheet对应的单元格中 if (dataList.size() > 0) { for (int k = 0; k < dataList.size(); k++) { Object[] obj = dataList.get(k);// 遍历每个对象 HSSFRow row = sheet.createRow(k + 1);// 创建所需的行数 for (int j = 0; j < obj.length; j++) { HSSFCell cell = null; // 设置单元格的数据类型 cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING); if (!"".equals(obj[j]) && obj[j] != null) { cell.setCellValue(obj[j].toString()); // 设置单元格的值 }else { cell.setCellValue(""); } cell.setCellStyle(style); // 设置单元格样式 } } } // 让列宽随着导出的列长自动适应 for (int colNum = 0; colNum < columnNum; colNum++) { int columnWidth = sheet.getColumnWidth(colNum) / 256; for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { HSSFRow currentRow; // 当前行未被使用过 if (sheet.getRow(rowNum) == null) { currentRow = sheet.createRow(rowNum); } else { currentRow = sheet.getRow(rowNum); } if (currentRow.getCell(colNum) != null) { HSSFCell currentCell = currentRow.getCell(colNum); if (currentCell!=null&&currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { if(currentCell.getStringCellValue()!=null&&!"".equals(currentCell.getStringCellValue())) { int length = currentCell.getStringCellValue().getBytes().length; if (columnWidth < length) { columnWidth = length; } }

       				}
 			}
 		}
 		if (colNum == 0) {
 			sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
 		} else {
 			sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
 		}
 	}
 }

 if (workbook != null) {
 	try {
 		SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyyMMddHHmm");
 		String currentDate = simpleDateFormat.format(new Date());
 		String filename = fname +currentDate+ ".xls";
 		response.reset();
 		HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder
 				.getRequestAttributes()).getRequest();
 		if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
 			response.setHeader("Content-Disposition",
 					"attachment;" + "filename=" + new String(filename.getBytes("GBK"), "ISO8859-1"));
 		} else {
 			response.setHeader("Content-Disposition",
 					"attachment;" + "filename=" + new String(filename.getBytes("UTF8"), "ISO8859-1"));
 		}
 		response.setContentType("APPLICATION/OCTET-STREAM");
 		OutputStream out = response.getOutputStream();
 		workbook.write(out);
 	} catch (IOException e) {
 		e.printStackTrace();
 	}
 }

      } catch (Exception e) { e.printStackTrace(); } } public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short)11); //font.setBoldweight((short)700); font.setFontName("Courier New"); HSSFCellStyle style = workbook.createCellStyle(); style.setBorderBottom(BorderStyle.valueOf((short)1)); style.setBottomBorderColor((short)8); style.setBorderLeft(BorderStyle.valueOf((short)1)); style.setLeftBorderColor((short)8); style.setBorderRight(BorderStyle.valueOf((short)1)); style.setFillPattern(FillPatternType.forInt((short)1)); style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); style.setRightBorderColor((short)8); style.setBorderTop(BorderStyle.valueOf((short)1)); style.setTopBorderColor((short)8); style.setFont(font); style.setWrapText(false); style.setAlignment(HorizontalAlignment.forInt((short)2)); style.setVerticalAlignment(VerticalAlignment.forInt((short)1)); return style; }

    public HSSFCellStyle getStyle(HSSFWorkbook workbook) { HSSFFont font = workbook.createFont(); font.setFontName("Courier New"); HSSFCellStyle style = workbook.createCellStyle(); style.setBorderBottom(BorderStyle.valueOf((short)1)); style.setBottomBorderColor((short)8); style.setBorderLeft(BorderStyle.valueOf((short)1)); style.setLeftBorderColor((short)8); style.setBorderRight(BorderStyle.valueOf((short)1)); style.setRightBorderColor((short)8); style.setBorderTop(BorderStyle.valueOf((short)1)); style.setTopBorderColor((short)8); style.setFont(font); style.setWrapText(false); style.setAlignment(HorizontalAlignment.forInt((short)2)); style.setVerticalAlignment(VerticalAlignment.forInt((short)1)); return style; } }

这个是自己做的一个导出功能，希望能帮到你!

avatar
文章
阅读
粉丝