-
Controller
@PostMapping("/exportInfo") @ResponseBody public Rest exportInfo(HttpServletRequest request, HttpServletResponse response, String queryRequest) { QueryCondition query = JSON.parseObject(queryRequest, QueryCondition.class); List<Info> pageResponse = infoService.findPageToReport(query); ReportHandle handle = new ReportHandle(); TwoTuple<List<ExportColBean>, List<List<ExportCellBean>>> report = handle.getReport(pageResponse); HSSFWorkbook hssfWorkbook = PoiUtils.createWorkBook(report.getLeft(), report.getRight()); try { PoiUtils.exportExcel(response,hssfWorkbook,"EXCEL详情"); } catch (Exception e) { e.printStackTrace(); } return Rest.ok(); }
-
JAS库存导出列的配置
public class reportHandle { private List<ExportColBean> getReportTitle() { List<ExportColBean> title = new ArrayList<>(); title.add(new ExportColBean("expiryDate","到期日","expiryDate", "到期日",false,"",false,ExportColBean.STRING)); title.add(new ExportColBean("itemType","类别","itemType", "itemType",false,"",false,ExportColBean.STRING)); title.add(new ExportColBean("memberPrice","会员价","memberPrice", "memberPrice",false,"",false,ExportColBean.DATA_FORMAT,"#,##0")); return title; } public TwoTuple<List<ExportColBean>,List<List<ExportCellBean>>> getReport(List<Info> jas) { List<ExportColBean> jasTitle = getJasReportTitle(); List<List<ExportCellBean>> values = new ArrayList<>(); jas.forEach(o ->{ List<ExportCellBean> value = jasTitle.stream().map(title -> { ExportCellBean bean = new ExportCellBean(); bean.setIsDelicate(title.getIsDelicate()); bean.setType(title.getType()); bean.setFormula(title.getFormula()); bean.setDataFormat(title.getDataFormat()); bean.setValue(String.valueOf(ReflectionUtils.getFieldValue(o, title.getFieldName()))); return bean; }).collect(Collectors.toList()); values.add(value); }); return new TwoTuple(jasTitle,values); } /** * 直接读取对象属性值, 无视private/protected修饰符, 不经过getter函数. */ public static Object getFieldValue(final Object obj, final String fieldName) { Field field = getAccessibleField(obj, fieldName); if (field == null) { //throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + obj + "]"); return null; } Object result = null; try { result = field.get(obj); } catch (IllegalAccessException e) { logger.error("不可能抛出的异常{}", e.getMessage()); } return result; }
-
表格和文档的操作
public class PoiUtils { public static HSSFWorkbook createWorkBook(List<ExportColBean> title,List<List<ExportCellBean>> values) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet1"); HSSFRow row = sheet.createRow((int) 0); for (int i = 0; i < title.size(); i++) { row.createCell(i).setCellValue(title.get(i).getTitle2()); } HSSFCellStyle numStyle = ExcelUtil.getCellNumStyle(wb); HSSFCellStyle numRed = ExcelUtil.getCellRedStyle(wb, ExcelUtil.getCellNumStyle(wb)); HSSFCellStyle eurStyle = ExcelUtil.getCellEurStyle(wb); HSSFCellStyle numStyle1 = ExcelUtil.getCell100NumStyle(wb); HSSFCellStyle red = ExcelUtil.getCellRedStyle(wb, null); for (int i = 0; i < values.size(); i++) { Integer rowIndex = 1 + i; setCellValueMethod(wb,sheet,values,rowIndex,i,red,numRed,eurStyle,numStyle1); } wb.setForceFormulaRecalculation(true); return wb; } /** * 导出Excel * @param response * @param workbook * @param wbName * @throws Exception */ public static void exportExcel(HttpServletResponse response,HSSFWorkbook workbook,String wbName) throws Exception { response.setContentType("application/msexcel; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=\"" + new String((wbName + ".xls").getBytes("GBK"),"ISO-8859-1") +"\""); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } }
-
实体类
@Data public class TwoTuple<A,B> { private A left; private B right; } /** * 用来接收需要导出的列属性的对象 */ @Data public class ExportColBean { public static String NUM = "num"; public static String DATE = "date"; public static String STRING = "String"; public static String NUM100 = "num100"; public static String FORMULA = "Formula"; public static String DATA_FORMAT = "dataFormat"; public static String EUR_FORMAT = "eurFormat"; public static String NUM100_FORMAT = "num100Format"; /** * 标题1 */ private String title1; /** * 标题2 */ private String title2; /** * 字段名称 */ private String fieldName; /** * 表名 */ private String tableName; /** * 排序字段 */ private Boolean sortable; /** * 排序字段 */ private String order; /** * 类型 日期,字符串,公式 */ private String type; /** * 是否敏感 */ private Boolean isDelicate; /** * 假如是公式列的话启用的公式 */ private AbstractFormula formula; /** * 数据格式化 */ private String dataFormat; public ExportColBean(String title1, String title2, String fieldName, String tableName, Boolean sortable, String order, Boolean isDelicate,String type) { this.title1 = title1; this.title2 = title2; this.fieldName = fieldName; this.tableName = tableName; this.sortable = sortable; this.order = order; this.isDelicate = isDelicate; this.type = type; } public ExportColBean(String title1, String title2, String fieldName, String tableName, Boolean sortable, String order, Boolean isDelicate,String type,String dataFormat) { this.title1 = title1; this.title2 = title2; this.fieldName = fieldName; this.tableName = tableName; this.sortable = sortable; this.order = order; this.isDelicate = isDelicate; this.type = type; this.dataFormat = dataFormat; } public ExportColBean() { } public ExportColBean setFormula(AbstractFormula formula) { this.formula = formula; return this; } @Data public class ExportCellBean { /** * 类别 */ private String type; /** * 值 */ private String value; /** * 是否敏感 */ private Boolean isDelicate; /** * 假如是公式列的话启用的公式 */ private AbstractFormula formula; /** * 数据格式化 */ private String dataFormat; }
-
依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>fr.opensagres.xdocreport</groupId> <artifactId>org.apache.poi.xwpf.converter.core</artifactId> <version>1.0.5</version> </dependency> <dependency> <groupId>fr.opensagres.xdocreport</groupId> <artifactId>org.apache.poi.xwpf.converter.pdf</artifactId> <version>1.0.5</version> </dependency>
java导出EXCEL表格
最新推荐文章于 2023-01-12 10:41:29 发布