From 5a54ab14ce8161a8bff0a94dcc524a41a7d39863 Mon Sep 17 00:00:00 2001 From: RuoYi <yzz_ivy@163.com> Date: 星期日, 26 九月 2021 09:02:20 +0800 Subject: [PATCH] Excel注解支持导入导出标题信息 --- src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 179 ++++++++++++++++++++++++++++++++++++++++++++++++++++------- 1 files changed, 157 insertions(+), 22 deletions(-) diff --git a/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java b/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java index 025a1cd..2784244 100644 --- a/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java +++ b/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java @@ -6,6 +6,7 @@ import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; +import java.lang.reflect.Method; import java.math.BigDecimal; import java.text.DecimalFormat; import java.util.ArrayList; @@ -46,6 +47,7 @@ import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.streaming.SXSSFWorkbook; @@ -125,6 +127,16 @@ private List<Object[]> fields; /** + * 褰撳墠琛屽彿 + */ + private int rownum; + + /** + * 鏍囬 + */ + private String title; + + /** * 鏈�澶ч珮搴� */ private short maxHeight; @@ -149,7 +161,7 @@ this.clazz = clazz; } - public void init(List<T> list, String sheetName, Type type) + public void init(List<T> list, String sheetName, String title, Type type) { if (list == null) { @@ -158,8 +170,27 @@ this.list = list; this.sheetName = sheetName; this.type = type; + this.title = title; createExcelField(); createWorkbook(); + createTitle(); + } + + /** + * 鍒涘缓excel绗竴琛屾爣棰� + */ + public void createTitle() + { + if (StringUtils.isNotEmpty(title)) + { + Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0); + titleRow.setHeightInPoints(30); + Cell titleCell = titleRow.createCell(0); + titleCell.setCellStyle(styles.get("title")); + titleCell.setCellValue(title); + sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), + this.fields.size() - 1)); + } } /** @@ -170,17 +201,30 @@ */ public List<T> importExcel(InputStream is) throws Exception { - return importExcel(StringUtils.EMPTY, is); + return importExcel(is, 0); + } + + /** + * 瀵筫xcel琛ㄥ崟榛樿绗竴涓储寮曞悕杞崲鎴恖ist + * + * @param is 杈撳叆娴� + * @param titleNum 鏍囬鍗犵敤琛屾暟 + * @return 杞崲鍚庨泦鍚� + */ + public List<T> importExcel(InputStream is, int titleNum) throws Exception + { + return importExcel(StringUtils.EMPTY, is, titleNum); } /** * 瀵筫xcel琛ㄥ崟鎸囧畾琛ㄦ牸绱㈠紩鍚嶈浆鎹㈡垚list * * @param sheetName 琛ㄦ牸绱㈠紩鍚� + * @param titleNum 鏍囬鍗犵敤琛屾暟 * @param is 杈撳叆娴� * @return 杞崲鍚庨泦鍚� */ - public List<T> importExcel(String sheetName, InputStream is) throws Exception + public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception { this.type = Type.IMPORT; this.wb = WorkbookFactory.create(is); @@ -209,7 +253,7 @@ // 瀹氫箟涓�涓猰ap鐢ㄤ簬瀛樻斁excel鍒楃殑搴忓彿鍜宖ield. Map<String, Integer> cellMap = new HashMap<String, Integer>(); // 鑾峰彇琛ㄥご - Row heard = sheet.getRow(0); + Row heard = sheet.getRow(titleNum); for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) { Cell cell = heard.getCell(i); @@ -242,7 +286,7 @@ } } } - for (int i = 1; i <= rows; i++) + for (int i = titleNum + 1; i <= rows; i++) { // 浠庣2琛屽紑濮嬪彇鏁版嵁,榛樿绗竴琛屾槸琛ㄥご. Row row = sheet.getRow(i); @@ -333,6 +377,10 @@ { val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator()); } + else if (!attr.handler().equals(ExcelHandlerAdapter.class)) + { + val = dataFormatHandlerAdapter(val, attr); + } else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures)) { PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey()); @@ -364,7 +412,20 @@ */ public AjaxResult exportExcel(List<T> list, String sheetName) { - this.init(list, sheetName, Type.EXPORT); + return exportExcel(list, sheetName, StringUtils.EMPTY); + } + + /** + * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟 + * + * @param list 瀵煎嚭鏁版嵁闆嗗悎 + * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О + * @param title 鏍囬 + * @return 缁撴灉 + */ + public AjaxResult exportExcel(List<T> list, String sheetName, String title) + { + this.init(list, sheetName, title, Type.EXPORT); return exportExcel(); } @@ -377,11 +438,26 @@ * @return 缁撴灉 * @throws IOException */ - public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) throws IOException + public void exportExcel(HttpServletResponse response, List<T> list, String sheetName)throws IOException + { + exportExcel(response, list, sheetName, StringUtils.EMPTY); + } + + /** + * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟 + * + * @param response 杩斿洖鏁版嵁 + * @param list 瀵煎嚭鏁版嵁闆嗗悎 + * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О + * @param title 鏍囬 + * @return 缁撴灉 + * @throws IOException + */ + public void exportExcel(HttpServletResponse response, List<T> list, String sheetName, String title) throws IOException { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); - this.init(list, sheetName, Type.EXPORT); + this.init(list, sheetName, title, Type.EXPORT); exportExcel(response.getOutputStream()); } @@ -393,7 +469,19 @@ */ public AjaxResult importTemplateExcel(String sheetName) { - this.init(null, sheetName, Type.IMPORT); + return importTemplateExcel(sheetName, StringUtils.EMPTY); + } + + /** + * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟 + * + * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О + * @param title 鏍囬 + * @return 缁撴灉 + */ + public AjaxResult importTemplateExcel(String sheetName, String title) + { + this.init(null, sheetName, title, Type.IMPORT); return exportExcel(); } @@ -405,9 +493,21 @@ */ public void importTemplateExcel(HttpServletResponse response, String sheetName) throws IOException { + importTemplateExcel(response, sheetName); + } + + /** + * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟 + * + * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О + * @param title 鏍囬 + * @return 缁撴灉 + */ + public void importTemplateExcel(HttpServletResponse response, String sheetName, String title) throws IOException + { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); - this.init(null, sheetName, Type.IMPORT); + this.init(null, sheetName, title, Type.IMPORT); exportExcel(response.getOutputStream()); } @@ -468,13 +568,13 @@ public void writeSheet() { // 鍙栧嚭涓�鍏辨湁澶氬皯涓猻heet. - double sheetNo = Math.ceil(list.size() / sheetSize); - for (int index = 0; index <= sheetNo; index++) + int sheetNo = Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize)); + for (int index = 0; index < sheetNo; index++) { createSheet(sheetNo, index); // 浜х敓涓�琛� - Row row = sheet.createRow(0); + Row row = sheet.createRow(rownum); int column = 0; // 鍐欏叆鍚勪釜瀛楁鐨勫垪澶村悕绉� for (Object[] os : fields) @@ -502,7 +602,7 @@ int endNo = Math.min(startNo + sheetSize, list.size()); for (int i = startNo; i < endNo; i++) { - row = sheet.createRow(i + 1 - startNo); + row = sheet.createRow(i + 1 + rownum - startNo); // 寰楀埌瀵煎嚭瀵硅薄. T vo = (T) list.get(i); int column = 0; @@ -528,6 +628,16 @@ // 鍐欏叆鍚勬潯璁板綍,姣忔潯璁板綍瀵瑰簲excel琛ㄤ腑鐨勪竴琛� Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); + style.setAlignment(HorizontalAlignment.CENTER); + style.setVerticalAlignment(VerticalAlignment.CENTER); + Font titleFont = wb.createFont(); + titleFont.setFontName("Arial"); + titleFont.setFontHeightInPoints((short) 16); + titleFont.setBold(true); + style.setFont(titleFont); + styles.put("title", style); + + style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setBorderRight(BorderStyle.THIN); @@ -729,6 +839,10 @@ { cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString()); } + else if (!attr.handler().equals(ExcelHandlerAdapter.class)) + { + cell.setCellValue(dataFormatHandlerAdapter(value, attr)); + } else { // 璁剧疆鍒楃被鍨� @@ -899,6 +1013,28 @@ public static String reverseDictByExp(String dictLabel, String dictType, String separator) { return DictUtils.getDictValue(dictType, dictLabel, separator); + } + + /** + * 鏁版嵁澶勭悊鍣� + * + * @param value 鏁版嵁鍊� + * @param excel 鏁版嵁娉ㄨВ + * @return + */ + public String dataFormatHandlerAdapter(Object value, Excel excel) + { + try + { + Object instance = excel.handler().newInstance(); + Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class }); + value = formatMethod.invoke(instance, value, excel.args()); + } + catch (Exception e) + { + log.error("涓嶈兘鏍煎紡鍖栨暟鎹� " + excel.handler(), e.getMessage()); + } + return Convert.toStr(value); } /** @@ -1086,6 +1222,9 @@ public void createWorkbook() { this.wb = new SXSSFWorkbook(500); + this.sheet = wb.createSheet(); + wb.setSheetName(0, sheetName); + this.styles = createStyles(wb); } /** @@ -1094,17 +1233,13 @@ * @param sheetNo sheet鏁伴噺 * @param index 搴忓彿 */ - public void createSheet(double sheetNo, int index) + public void createSheet(int sheetNo, int index) { - this.sheet = wb.createSheet(); - this.styles = createStyles(wb); // 璁剧疆宸ヤ綔琛ㄧ殑鍚嶇О. - if (sheetNo == 0) + if (sheetNo > 1 && index > 0) { - wb.setSheetName(index, sheetName); - } - else - { + this.sheet = wb.createSheet(); + this.createTitle(); wb.setSheetName(index, sheetName + index); } } -- Gitblit v1.9.3