From 0375fd319c9f3b08d255c814cb0f8521d8ec641b Mon Sep 17 00:00:00 2001
From: 疯狂的狮子Li <15040126243@163.com>
Date: 星期三, 18 八月 2021 11:11:24 +0800
Subject: [PATCH] !78 同步dev分支 Merge pull request !78 from 疯狂的狮子Li/dev
---
ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 1184 ++++++----------------------------------------------------
1 files changed, 131 insertions(+), 1,053 deletions(-)
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
index 96843d1..f242d8c 100644
--- a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
@@ -1,1072 +1,150 @@
package com.ruoyi.common.utils.poi;
-import cn.hutool.core.convert.Convert;
-import cn.hutool.core.lang.Validator;
-import cn.hutool.core.util.StrUtil;
-import com.ruoyi.common.annotation.Excel;
-import com.ruoyi.common.annotation.Excel.ColumnType;
-import com.ruoyi.common.annotation.Excel.Type;
-import com.ruoyi.common.annotation.Excels;
-import com.ruoyi.common.config.RuoYiConfig;
-import com.ruoyi.common.core.domain.AjaxResult;
-import com.ruoyi.common.exception.CustomException;
-import com.ruoyi.common.utils.DateUtils;
+import cn.hutool.core.util.IdUtil;
+import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.ruoyi.common.utils.DictUtils;
-import com.ruoyi.common.utils.file.FileTypeUtils;
-import com.ruoyi.common.utils.file.ImageUtils;
-import com.ruoyi.common.utils.reflect.ReflectUtils;
-import org.apache.poi.ss.usermodel.*;
-import org.apache.poi.ss.util.CellRangeAddressList;
-import org.apache.poi.xssf.streaming.SXSSFWorkbook;
-import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
-import org.apache.poi.xssf.usermodel.XSSFDataValidation;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
+import com.ruoyi.common.utils.StringUtils;
+import com.ruoyi.common.utils.file.FileUtils;
-import java.io.*;
-import java.lang.reflect.Field;
-import java.math.BigDecimal;
-import java.text.DecimalFormat;
-import java.util.*;
-import java.util.stream.Collectors;
+import javax.servlet.ServletOutputStream;
+import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.io.InputStream;
+import java.net.URLEncoder;
+import java.nio.charset.StandardCharsets;
+import java.util.List;
/**
* Excel鐩稿叧澶勭悊
*
* @author ruoyi
*/
-public class ExcelUtil<T>
-{
- private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
+public class ExcelUtil {
- /**
- * Excel sheet鏈�澶ц鏁帮紝榛樿65536
- */
- public static final int sheetSize = 65536;
+ /**
+ * 瀵筫xcel琛ㄥ崟榛樿绗竴涓储寮曞悕杞崲鎴恖ist锛圗asyExcel锛�
+ *
+ * @param is 杈撳叆娴�
+ * @return 杞崲鍚庨泦鍚�
+ */
+ public static <T> List<T> importExcel(InputStream is, Class<T> clazz) {
+ return EasyExcel.read(is).autoCloseStream(false).sheet().doReadSync();
+ }
- /**
- * 宸ヤ綔琛ㄥ悕绉�
- */
- private String sheetName;
+ /**
+ * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟锛圗asyExcel锛�
+ *
+ * @param list 瀵煎嚭鏁版嵁闆嗗悎
+ * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
+ * @return 缁撴灉
+ */
+ public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, HttpServletResponse response) {
+ try {
+ String filename = encodingFilename(sheetName);
+ response.reset();
+ response.addHeader("Access-Control-Allow-Origin", "*");
+ response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
+ FileUtils.setAttachmentResponseHeader(response, URLEncoder.encode(filename, StandardCharsets.UTF_8.toString()));
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
+ ServletOutputStream os = response.getOutputStream();
+ EasyExcel.write(os, clazz)
+ .autoCloseStream(false)
+ // 鑷姩閫傞厤
+ .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
+ .sheet(sheetName).doWrite(list);
+ } catch (IOException e) {
+ throw new RuntimeException("瀵煎嚭Excel寮傚父");
+ }
+ }
- /**
- * 瀵煎嚭绫诲瀷锛圗XPORT:瀵煎嚭鏁版嵁锛汭MPORT锛氬鍏ユā鏉匡級
- */
- private Type type;
+ /**
+ * 瑙ｆ瀽瀵煎嚭鍊� 0=鐢�,1=濂�,2=鏈煡
+ *
+ * @param propertyValue 鍙傛暟鍊�
+ * @param converterExp 缈昏瘧娉ㄨВ
+ * @param separator 鍒嗛殧绗�
+ * @return 瑙ｆ瀽鍚庡��
+ */
+ public static String convertByExp(String propertyValue, String converterExp, String separator) {
+ StringBuilder propertyString = new StringBuilder();
+ String[] convertSource = converterExp.split(",");
+ for (String item : convertSource) {
+ String[] itemArray = item.split("=");
+ if (StringUtils.containsAny(separator, propertyValue)) {
+ for (String value : propertyValue.split(separator)) {
+ if (itemArray[0].equals(value)) {
+ propertyString.append(itemArray[1] + separator);
+ break;
+ }
+ }
+ } else {
+ if (itemArray[0].equals(propertyValue)) {
+ return itemArray[1];
+ }
+ }
+ }
+ return StringUtils.stripEnd(propertyString.toString(), separator);
+ }
- /**
- * 宸ヤ綔钖勫璞�
- */
- private Workbook wb;
+ /**
+ * 鍙嶅悜瑙ｆ瀽鍊� 鐢�=0,濂�=1,鏈煡=2
+ *
+ * @param propertyValue 鍙傛暟鍊�
+ * @param converterExp 缈昏瘧娉ㄨВ
+ * @param separator 鍒嗛殧绗�
+ * @return 瑙ｆ瀽鍚庡��
+ */
+ public static String reverseByExp(String propertyValue, String converterExp, String separator) {
+ StringBuilder propertyString = new StringBuilder();
+ String[] convertSource = converterExp.split(",");
+ for (String item : convertSource) {
+ String[] itemArray = item.split("=");
+ if (StringUtils.containsAny(separator, propertyValue)) {
+ for (String value : propertyValue.split(separator)) {
+ if (itemArray[1].equals(value)) {
+ propertyString.append(itemArray[0] + separator);
+ break;
+ }
+ }
+ } else {
+ if (itemArray[1].equals(propertyValue)) {
+ return itemArray[0];
+ }
+ }
+ }
+ return StringUtils.stripEnd(propertyString.toString(), separator);
+ }
- /**
- * 宸ヤ綔琛ㄥ璞�
- */
- private Sheet sheet;
+ /**
+ * 瑙ｆ瀽瀛楀吀鍊�
+ *
+ * @param dictValue 瀛楀吀鍊�
+ * @param dictType 瀛楀吀绫诲瀷
+ * @param separator 鍒嗛殧绗�
+ * @return 瀛楀吀鏍囩
+ */
+ public static String convertDictByExp(String dictValue, String dictType, String separator) {
+ return DictUtils.getDictLabel(dictType, dictValue, separator);
+ }
- /**
- * 鏍峰紡鍒楄〃
- */
- private Map<String, CellStyle> styles;
+ /**
+ * 鍙嶅悜瑙ｆ瀽鍊煎瓧鍏稿��
+ *
+ * @param dictLabel 瀛楀吀鏍囩
+ * @param dictType 瀛楀吀绫诲瀷
+ * @param separator 鍒嗛殧绗�
+ * @return 瀛楀吀鍊�
+ */
+ public static String reverseDictByExp(String dictLabel, String dictType, String separator) {
+ return DictUtils.getDictValue(dictType, dictLabel, separator);
+ }
- /**
- * 瀵煎叆瀵煎嚭鏁版嵁鍒楄〃
- */
- private List<T> list;
+ /**
+ * 缂栫爜鏂囦欢鍚�
+ */
+ public static String encodingFilename(String filename) {
+ return IdUtil.fastSimpleUUID() + "_" + filename + ".xlsx";
+ }
- /**
- * 娉ㄨВ鍒楄〃
- */
- private List<Object[]> fields;
-
- /**
- * 鏈�澶ч珮搴�
- */
- private short maxHeight;
-
- /**
- * 缁熻鍒楄〃
- */
- private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
-
- /**
- * 鏁板瓧鏍煎紡
- */
- private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
-
- /**
- * 瀹炰綋瀵硅薄
- */
- public Class<T> clazz;
-
- public ExcelUtil(Class<T> clazz)
- {
- this.clazz = clazz;
- }
-
- public void init(List<T> list, String sheetName, Type type)
- {
- if (list == null)
- {
- list = new ArrayList<T>();
- }
- this.list = list;
- this.sheetName = sheetName;
- this.type = type;
- createExcelField();
- createWorkbook();
- }
-
- /**
- * 瀵筫xcel琛ㄥ崟榛樿绗竴涓储寮曞悕杞崲鎴恖ist
- *
- * @param is 杈撳叆娴�
- * @return 杞崲鍚庨泦鍚�
- */
- public List<T> importExcel(InputStream is) throws Exception
- {
- return importExcel(StrUtil.EMPTY, is);
- }
-
- /**
- * 瀵筫xcel琛ㄥ崟鎸囧畾琛ㄦ牸绱㈠紩鍚嶈浆鎹㈡垚list
- *
- * @param sheetName 琛ㄦ牸绱㈠紩鍚�
- * @param is 杈撳叆娴�
- * @return 杞崲鍚庨泦鍚�
- */
- public List<T> importExcel(String sheetName, InputStream is) throws Exception
- {
- this.type = Type.IMPORT;
- this.wb = WorkbookFactory.create(is);
- List<T> list = new ArrayList<T>();
- Sheet sheet = null;
- if (Validator.isNotEmpty(sheetName))
- {
- // 濡傛灉鎸囧畾sheet鍚�,鍒欏彇鎸囧畾sheet涓殑鍐呭.
- sheet = wb.getSheet(sheetName);
- }
- else
- {
- // 濡傛灉浼犲叆鐨剆heet鍚嶄笉瀛樺湪鍒欓粯璁ゆ寚鍚戠1涓猻heet.
- sheet = wb.getSheetAt(0);
- }
-
- if (sheet == null)
- {
- throw new IOException("鏂囦欢sheet涓嶅瓨鍦�");
- }
-
- int rows = sheet.getPhysicalNumberOfRows();
-
- if (rows > 0)
- {
- // 瀹氫箟涓�涓猰ap鐢ㄤ簬瀛樻斁excel鍒楃殑搴忓彿鍜宖ield.
- Map<String, Integer> cellMap = new HashMap<String, Integer>();
- // 鑾峰彇琛ㄥご
- Row heard = sheet.getRow(0);
- for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
- {
- Cell cell = heard.getCell(i);
- if (Validator.isNotNull(cell))
- {
- String value = this.getCellValue(heard, i).toString();
- cellMap.put(value, i);
- }
- else
- {
- cellMap.put(null, i);
- }
- }
- // 鏈夋暟鎹椂鎵嶅鐞� 寰楀埌绫荤殑鎵�鏈塮ield.
- Field[] allFields = clazz.getDeclaredFields();
- // 瀹氫箟涓�涓猰ap鐢ㄤ簬瀛樻斁鍒楃殑搴忓彿鍜宖ield.
- Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
- for (int col = 0; col < allFields.length; col++)
- {
- Field field = allFields[col];
- Excel attr = field.getAnnotation(Excel.class);
- if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
- {
- // 璁剧疆绫荤殑绉佹湁瀛楁灞炴�у彲璁块棶.
- field.setAccessible(true);
- Integer column = cellMap.get(attr.name());
- if (column != null)
- {
- fieldsMap.put(column, field);
- }
- }
- }
- for (int i = 1; i < rows; i++)
- {
- // 浠庣2琛屽紑濮嬪彇鏁版嵁,榛樿绗竴琛屾槸琛ㄥご.
- Row row = sheet.getRow(i);
- if(row == null)
- {
- continue;
- }
- T entity = null;
- for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet())
- {
- Object val = this.getCellValue(row, entry.getKey());
-
- // 濡傛灉涓嶅瓨鍦ㄥ疄渚嬪垯鏂板缓.
- entity = (entity == null ? clazz.newInstance() : entity);
- // 浠巑ap涓緱鍒板搴斿垪鐨刦ield.
- Field field = fieldsMap.get(entry.getKey());
- // 鍙栧緱绫诲瀷,骞舵牴鎹璞＄被鍨嬭缃��.
- Class<?> fieldType = field.getType();
- if (String.class == fieldType)
- {
- String s = Convert.toStr(val);
- if (StrUtil.endWith(s, ".0"))
- {
- val = StrUtil.subBefore(s, ".0",false);
- }
- else
- {
- String dateFormat = field.getAnnotation(Excel.class).dateFormat();
- if (Validator.isNotEmpty(dateFormat))
- {
- val = DateUtils.parseDateToStr(dateFormat, (Date) val);
- }
- else
- {
- val = Convert.toStr(val);
- }
- }
- }
- else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && Validator.isNumber(Convert.toStr(val)))
- {
- val = Convert.toInt(val);
- }
- else if (Long.TYPE == fieldType || Long.class == fieldType)
- {
- val = Convert.toLong(val);
- }
- else if (Double.TYPE == fieldType || Double.class == fieldType)
- {
- val = Convert.toDouble(val);
- }
- else if (Float.TYPE == fieldType || Float.class == fieldType)
- {
- val = Convert.toFloat(val);
- }
- else if (BigDecimal.class == fieldType)
- {
- val = Convert.toBigDecimal(val);
- }
- else if (Date.class == fieldType)
- {
- if (val instanceof String)
- {
- val = DateUtils.parseDate(val);
- }
- else if (val instanceof Double)
- {
- val = DateUtil.getJavaDate((Double) val);
- }
- }
- else if (Boolean.TYPE == fieldType || Boolean.class == fieldType)
- {
- val = Convert.toBool(val, false);
- }
- if (Validator.isNotNull(fieldType))
- {
- Excel attr = field.getAnnotation(Excel.class);
- String propertyName = field.getName();
- if (Validator.isNotEmpty(attr.targetAttr()))
- {
- propertyName = field.getName() + "." + attr.targetAttr();
- }
- else if (Validator.isNotEmpty(attr.readConverterExp()))
- {
- val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
- }
- else if (Validator.isNotEmpty(attr.dictType()))
- {
- val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
- }
- ReflectUtils.invokeSetter(entity, propertyName, val);
- }
- }
- list.add(entity);
- }
- }
- return list;
- }
-
- /**
- * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟
- *
- * @param list 瀵煎嚭鏁版嵁闆嗗悎
- * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
- * @return 缁撴灉
- */
- public AjaxResult exportExcel(List<T> list, String sheetName)
- {
- this.init(list, sheetName, Type.EXPORT);
- return exportExcel();
- }
-
- /**
- * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟
- *
- * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
- * @return 缁撴灉
- */
- public AjaxResult importTemplateExcel(String sheetName)
- {
- this.init(null, sheetName, Type.IMPORT);
- return exportExcel();
- }
-
- /**
- * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟
- *
- * @return 缁撴灉
- */
- public AjaxResult exportExcel()
- {
- OutputStream out = null;
- try
- {
- // 鍙栧嚭涓�鍏辨湁澶氬皯涓猻heet.
- double sheetNo = Math.ceil(list.size() / sheetSize);
- for (int index = 0; index <= sheetNo; index++)
- {
- createSheet(sheetNo, index);
-
- // 浜х敓涓�琛�
- Row row = sheet.createRow(0);
- int column = 0;
- // 鍐欏叆鍚勪釜瀛楁鐨勫垪澶村悕绉�
- for (Object[] os : fields)
- {
- Excel excel = (Excel) os[1];
- this.createCell(excel, row, column++);
- }
- if (Type.EXPORT.equals(type))
- {
- fillExcelData(index, row);
- addStatisticsRow();
- }
- }
- String filename = encodingFilename(sheetName);
- out = new FileOutputStream(getAbsoluteFile(filename));
- wb.write(out);
- return AjaxResult.success(filename);
- }
- catch (Exception e)
- {
- log.error("瀵煎嚭Excel寮傚父{}", e.getMessage());
- throw new CustomException("瀵煎嚭Excel澶辫触锛岃鑱旂郴缃戠珯绠＄悊鍛橈紒");
- }
- finally
- {
- if (wb != null)
- {
- try
- {
- wb.close();
- }
- catch (IOException e1)
- {
- e1.printStackTrace();
- }
- }
- if (out != null)
- {
- try
- {
- out.close();
- }
- catch (IOException e1)
- {
- e1.printStackTrace();
- }
- }
- }
- }
-
- /**
- * 濉厖excel鏁版嵁
- *
- * @param index 搴忓彿
- * @param row 鍗曞厓鏍艰
- */
- public void fillExcelData(int index, Row row)
- {
- int startNo = index * sheetSize;
- int endNo = Math.min(startNo + sheetSize, list.size());
- for (int i = startNo; i < endNo; i++)
- {
- row = sheet.createRow(i + 1 - startNo);
- // 寰楀埌瀵煎嚭瀵硅薄.
- T vo = (T) list.get(i);
- int column = 0;
- for (Object[] os : fields)
- {
- Field field = (Field) os[0];
- Excel excel = (Excel) os[1];
- // 璁剧疆瀹炰綋绫荤鏈夊睘鎬у彲璁块棶
- field.setAccessible(true);
- this.addCell(excel, row, vo, field, column++);
- }
- }
- }
-
- /**
- * 鍒涘缓琛ㄦ牸鏍峰紡
- *
- * @param wb 宸ヤ綔钖勫璞�
- * @return 鏍峰紡鍒楄〃
- */
- private Map<String, CellStyle> createStyles(Workbook wb)
- {
- // 鍐欏叆鍚勬潯璁板綍,姣忔潯璁板綍瀵瑰簲excel琛ㄤ腑鐨勪竴琛�
- Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
- CellStyle style = wb.createCellStyle();
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setBorderRight(BorderStyle.THIN);
- style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderLeft(BorderStyle.THIN);
- style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderTop(BorderStyle.THIN);
- style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderBottom(BorderStyle.THIN);
- style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- Font dataFont = wb.createFont();
- dataFont.setFontName("Arial");
- dataFont.setFontHeightInPoints((short) 10);
- style.setFont(dataFont);
- styles.put("data", style);
-
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- Font headerFont = wb.createFont();
- headerFont.setFontName("Arial");
- headerFont.setFontHeightInPoints((short) 10);
- headerFont.setBold(true);
- headerFont.setColor(IndexedColors.WHITE.getIndex());
- style.setFont(headerFont);
- styles.put("header", style);
-
- style = wb.createCellStyle();
- style.setAlignment(HorizontalAlignment.CENTER);
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- Font totalFont = wb.createFont();
- totalFont.setFontName("Arial");
- totalFont.setFontHeightInPoints((short) 10);
- style.setFont(totalFont);
- styles.put("total", style);
-
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.LEFT);
- styles.put("data1", style);
-
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.CENTER);
- styles.put("data2", style);
-
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(HorizontalAlignment.RIGHT);
- styles.put("data3", style);
-
- return styles;
- }
-
- /**
- * 鍒涘缓鍗曞厓鏍�
- */
- public Cell createCell(Excel attr, Row row, int column)
- {
- // 鍒涘缓鍒�
- Cell cell = row.createCell(column);
- // 鍐欏叆鍒椾俊鎭�
- cell.setCellValue(attr.name());
- setDataValidation(attr, row, column);
- cell.setCellStyle(styles.get("header"));
- return cell;
- }
-
- /**
- * 璁剧疆鍗曞厓鏍间俊鎭�
- *
- * @param value 鍗曞厓鏍煎��
- * @param attr 娉ㄨВ鐩稿叧
- * @param cell 鍗曞厓鏍间俊鎭�
- */
- public void setCellVo(Object value, Excel attr, Cell cell)
- {
- if (ColumnType.STRING == attr.cellType())
- {
- cell.setCellValue(Validator.isNull(value) ? attr.defaultValue() : value + attr.suffix());
- }
- else if (ColumnType.NUMERIC == attr.cellType())
- {
- if (Validator.isNotNull(value))
- {
- cell.setCellValue(StrUtil.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
- }
- }
- else if (ColumnType.IMAGE == attr.cellType())
- {
- ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1),
- cell.getRow().getRowNum() + 1);
- String imagePath = Convert.toStr(value);
- if (Validator.isNotEmpty(imagePath))
- {
- byte[] data = ImageUtils.getImage(imagePath);
- getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
- cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
- }
- }
- }
-
- /**
- * 鑾峰彇鐢诲竷
- */
- public static Drawing<?> getDrawingPatriarch(Sheet sheet)
- {
- if (sheet.getDrawingPatriarch() == null)
- {
- sheet.createDrawingPatriarch();
- }
- return sheet.getDrawingPatriarch();
- }
-
- /**
- * 鑾峰彇鍥剧墖绫诲瀷,璁剧疆鍥剧墖鎻掑叆绫诲瀷
- */
- public int getImageType(byte[] value)
- {
- String type = FileTypeUtils.getFileExtendName(value);
- if ("JPG".equalsIgnoreCase(type))
- {
- return Workbook.PICTURE_TYPE_JPEG;
- }
- else if ("PNG".equalsIgnoreCase(type))
- {
- return Workbook.PICTURE_TYPE_PNG;
- }
- return Workbook.PICTURE_TYPE_JPEG;
- }
-
- /**
- * 鍒涘缓琛ㄦ牸鏍峰紡
- */
- public void setDataValidation(Excel attr, Row row, int column)
- {
- if (attr.name().indexOf("娉細") >= 0)
- {
- sheet.setColumnWidth(column, 6000);
- }
- else
- {
- // 璁剧疆鍒楀
- sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
- }
- // 濡傛灉璁剧疆浜嗘彁绀轰俊鎭垯榧犳爣鏀句笂鍘绘彁绀�.
- if (Validator.isNotEmpty(attr.prompt()))
- {
- // 杩欓噷榛樿璁句簡2-101鍒楁彁绀�.
- setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
- }
- // 濡傛灉璁剧疆浜哻ombo灞炴�у垯鏈垪鍙兘閫夋嫨涓嶈兘杈撳叆
- if (attr.combo().length > 0)
- {
- // 杩欓噷榛樿璁句簡2-101鍒楀彧鑳介�夋嫨涓嶈兘杈撳叆.
- setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
- }
- }
-
- /**
- * 娣诲姞鍗曞厓鏍�
- */
- public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
- {
- Cell cell = null;
- try
- {
- // 璁剧疆琛岄珮
- row.setHeight(maxHeight);
- // 鏍规嵁Excel涓缃儏鍐靛喅瀹氭槸鍚﹀鍑�,鏈変簺鎯呭喌闇�瑕佷繚鎸佷负绌�,甯屾湜鐢ㄦ埛濉啓杩欎竴鍒�.
- if (attr.isExport())
- {
- // 鍒涘缓cell
- cell = row.createCell(column);
- int align = attr.align().value();
- cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
-
- // 鐢ㄤ簬璇诲彇瀵硅薄涓殑灞炴��
- Object value = getTargetValue(vo, field, attr);
- String dateFormat = attr.dateFormat();
- String readConverterExp = attr.readConverterExp();
- String separator = attr.separator();
- String dictType = attr.dictType();
- if (Validator.isNotEmpty(dateFormat) && Validator.isNotNull(value))
- {
- cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
- }
- else if (Validator.isNotEmpty(readConverterExp) && Validator.isNotNull(value))
- {
- cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
- }
- else if (Validator.isNotEmpty(dictType) && Validator.isNotNull(value))
- {
- cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));
- }
- else if (value instanceof BigDecimal && -1 != attr.scale())
- {
- cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
- }
- else
- {
- // 璁剧疆鍒楃被鍨�
- setCellVo(value, attr, cell);
- }
- addStatisticsData(column, Convert.toStr(value), attr);
- }
- }
- catch (Exception e)
- {
- log.error("瀵煎嚭Excel澶辫触{}", e);
- }
- return cell;
- }
-
- /**
- * 璁剧疆 POI XSSFSheet 鍗曞厓鏍兼彁绀�
- *
- * @param sheet 琛ㄥ崟
- * @param promptTitle 鎻愮ず鏍囬
- * @param promptContent 鎻愮ず鍐呭
- * @param firstRow 寮�濮嬭
- * @param endRow 缁撴潫琛�
- * @param firstCol 寮�濮嬪垪
- * @param endCol 缁撴潫鍒�
- */
- public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
- int firstCol, int endCol)
- {
- DataValidationHelper helper = sheet.getDataValidationHelper();
- DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
- CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
- DataValidation dataValidation = helper.createValidation(constraint, regions);
- dataValidation.createPromptBox(promptTitle, promptContent);
- dataValidation.setShowPromptBox(true);
- sheet.addValidationData(dataValidation);
- }
-
- /**
- * 璁剧疆鏌愪簺鍒楃殑鍊煎彧鑳借緭鍏ラ鍒剁殑鏁版嵁,鏄剧ず涓嬫媺妗�.
- *
- * @param sheet 瑕佽缃殑sheet.
- * @param textlist 涓嬫媺妗嗘樉绀虹殑鍐呭
- * @param firstRow 寮�濮嬭
- * @param endRow 缁撴潫琛�
- * @param firstCol 寮�濮嬪垪
- * @param endCol 缁撴潫鍒�
- * @return 璁剧疆濂界殑sheet.
- */
- public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
- {
- DataValidationHelper helper = sheet.getDataValidationHelper();
- // 鍔犺浇涓嬫媺鍒楄〃鍐呭
- DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
- // 璁剧疆鏁版嵁鏈夋晥鎬у姞杞藉湪鍝釜鍗曞厓鏍间笂,鍥涗釜鍙傛暟鍒嗗埆鏄細璧峰琛屻�佺粓姝㈣銆佽捣濮嬪垪銆佺粓姝㈠垪
- CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
- // 鏁版嵁鏈夋晥鎬у璞�
- DataValidation dataValidation = helper.createValidation(constraint, regions);
- // 澶勭悊Excel鍏煎鎬ч棶棰�
- if (dataValidation instanceof XSSFDataValidation)
- {
- dataValidation.setSuppressDropDownArrow(true);
- dataValidation.setShowErrorBox(true);
- }
- else
- {
- dataValidation.setSuppressDropDownArrow(false);
- }
-
- sheet.addValidationData(dataValidation);
- }
-
- /**
- * 瑙ｆ瀽瀵煎嚭鍊� 0=鐢�,1=濂�,2=鏈煡
- *
- * @param propertyValue 鍙傛暟鍊�
- * @param converterExp 缈昏瘧娉ㄨВ
- * @param separator 鍒嗛殧绗�
- * @return 瑙ｆ瀽鍚庡��
- */
- public static String convertByExp(String propertyValue, String converterExp, String separator)
- {
- StringBuilder propertyString = new StringBuilder();
- String[] convertSource = converterExp.split(",");
- for (String item : convertSource)
- {
- String[] itemArray = item.split("=");
- if (StrUtil.containsAny(propertyValue, separator))
- {
- for (String value : propertyValue.split(separator))
- {
- if (itemArray[0].equals(value))
- {
- propertyString.append(itemArray[1] + separator);
- break;
- }
- }
- }
- else
- {
- if (itemArray[0].equals(propertyValue))
- {
- return itemArray[1];
- }
- }
- }
- return StrUtil.strip(propertyString.toString(), null,separator);
- }
-
- /**
- * 鍙嶅悜瑙ｆ瀽鍊� 鐢�=0,濂�=1,鏈煡=2
- *
- * @param propertyValue 鍙傛暟鍊�
- * @param converterExp 缈昏瘧娉ㄨВ
- * @param separator 鍒嗛殧绗�
- * @return 瑙ｆ瀽鍚庡��
- */
- public static String reverseByExp(String propertyValue, String converterExp, String separator)
- {
- StringBuilder propertyString = new StringBuilder();
- String[] convertSource = converterExp.split(",");
- for (String item : convertSource)
- {
- String[] itemArray = item.split("=");
- if (StrUtil.containsAny(propertyValue, separator))
- {
- for (String value : propertyValue.split(separator))
- {
- if (itemArray[1].equals(value))
- {
- propertyString.append(itemArray[0] + separator);
- break;
- }
- }
- }
- else
- {
- if (itemArray[1].equals(propertyValue))
- {
- return itemArray[0];
- }
- }
- }
- return StrUtil.strip(propertyString.toString(), null,separator);
- }
-
- /**
- * 瑙ｆ瀽瀛楀吀鍊�
- *
- * @param dictValue 瀛楀吀鍊�
- * @param dictType 瀛楀吀绫诲瀷
- * @param separator 鍒嗛殧绗�
- * @return 瀛楀吀鏍囩
- */
- public static String convertDictByExp(String dictValue, String dictType, String separator)
- {
- return DictUtils.getDictLabel(dictType, dictValue, separator);
- }
-
- /**
- * 鍙嶅悜瑙ｆ瀽鍊煎瓧鍏稿��
- *
- * @param dictLabel 瀛楀吀鏍囩
- * @param dictType 瀛楀吀绫诲瀷
- * @param separator 鍒嗛殧绗�
- * @return 瀛楀吀鍊�
- */
- public static String reverseDictByExp(String dictLabel, String dictType, String separator)
- {
- return DictUtils.getDictValue(dictType, dictLabel, separator);
- }
-
- /**
- * 鍚堣缁熻淇℃伅
- */
- private void addStatisticsData(Integer index, String text, Excel entity)
- {
- if (entity != null && entity.isStatistics())
- {
- Double temp = 0D;
- if (!statistics.containsKey(index))
- {
- statistics.put(index, temp);
- }
- try
- {
- temp = Double.valueOf(text);
- }
- catch (NumberFormatException e)
- {
- }
- statistics.put(index, statistics.get(index) + temp);
- }
- }
-
- /**
- * 鍒涘缓缁熻琛�
- */
- public void addStatisticsRow()
- {
- if (statistics.size() > 0)
- {
- Cell cell = null;
- Row row = sheet.createRow(sheet.getLastRowNum() + 1);
- Set<Integer> keys = statistics.keySet();
- cell = row.createCell(0);
- cell.setCellStyle(styles.get("total"));
- cell.setCellValue("鍚堣");
-
- for (Integer key : keys)
- {
- cell = row.createCell(key);
- cell.setCellStyle(styles.get("total"));
- cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
- }
- statistics.clear();
- }
- }
-
- /**
- * 缂栫爜鏂囦欢鍚�
- */
- public String encodingFilename(String filename)
- {
- filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
- return filename;
- }
-
- /**
- * 鑾峰彇涓嬭浇璺緞
- *
- * @param filename 鏂囦欢鍚嶇О
- */
- public String getAbsoluteFile(String filename)
- {
- String downloadPath = RuoYiConfig.getDownloadPath() + filename;
- File desc = new File(downloadPath);
- if (!desc.getParentFile().exists())
- {
- desc.getParentFile().mkdirs();
- }
- return downloadPath;
- }
-
- /**
- * 鑾峰彇bean涓殑灞炴�у��
- *
- * @param vo 瀹炰綋瀵硅薄
- * @param field 瀛楁
- * @param excel 娉ㄨВ
- * @return 鏈�缁堢殑灞炴�у��
- * @throws Exception
- */
- private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
- {
- Object o = field.get(vo);
- if (Validator.isNotEmpty(excel.targetAttr()))
- {
- String target = excel.targetAttr();
- if (target.contains("."))
- {
- String[] targets = target.split("[.]");
- for (String name : targets)
- {
- o = getValue(o, name);
- }
- }
- else
- {
- o = getValue(o, target);
- }
- }
- return o;
- }
-
- /**
- * 浠ョ被鐨勫睘鎬х殑get鏂规硶鏂规硶褰㈠紡鑾峰彇鍊�
- *
- * @param o
- * @param name
- * @return value
- * @throws Exception
- */
- private Object getValue(Object o, String name) throws Exception
- {
- if (Validator.isNotNull(o) && Validator.isNotEmpty(name))
- {
- Class<?> clazz = o.getClass();
- Field field = clazz.getDeclaredField(name);
- field.setAccessible(true);
- o = field.get(o);
- }
- return o;
- }
-
- /**
- * 寰楀埌鎵�鏈夊畾涔夊瓧娈�
- */
- private void createExcelField()
- {
- this.fields = new ArrayList<Object[]>();
- List<Field> tempFields = new ArrayList<>();
- tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
- tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
- for (Field field : tempFields)
- {
- // 鍗曟敞瑙�
- if (field.isAnnotationPresent(Excel.class))
- {
- putToField(field, field.getAnnotation(Excel.class));
- }
-
- // 澶氭敞瑙�
- if (field.isAnnotationPresent(Excels.class))
- {
- Excels attrs = field.getAnnotation(Excels.class);
- Excel[] excels = attrs.value();
- for (Excel excel : excels)
- {
- putToField(field, excel);
- }
- }
- }
- this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
- this.maxHeight = getRowHeight();
- }
-
- /**
- * 鏍规嵁娉ㄨВ鑾峰彇鏈�澶ц楂�
- */
- public short getRowHeight()
- {
- double maxHeight = 0;
- for (Object[] os : this.fields)
- {
- Excel excel = (Excel) os[1];
- maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
- }
- return (short) (maxHeight * 20);
- }
-
- /**
- * 鏀惧埌瀛楁闆嗗悎涓�
- */
- private void putToField(Field field, Excel attr)
- {
- if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
- {
- this.fields.add(new Object[] { field, attr });
- }
- }
-
- /**
- * 鍒涘缓涓�涓伐浣滅翱
- */
- public void createWorkbook()
- {
- this.wb = new SXSSFWorkbook(500);
- }
-
- /**
- * 鍒涘缓宸ヤ綔琛�
- *
- * @param sheetNo sheet鏁伴噺
- * @param index 搴忓彿
- */
- public void createSheet(double sheetNo, int index)
- {
- this.sheet = wb.createSheet();
- this.styles = createStyles(wb);
- // 璁剧疆宸ヤ綔琛ㄧ殑鍚嶇О.
- if (sheetNo == 0)
- {
- wb.setSheetName(index, sheetName);
- }
- else
- {
- wb.setSheetName(index, sheetName + index);
- }
- }
-
- /**
- * 鑾峰彇鍗曞厓鏍煎��
- *
- * @param row 鑾峰彇鐨勮
- * @param column 鑾峰彇鍗曞厓鏍煎垪鍙�
- * @return 鍗曞厓鏍煎��
- */
- public Object getCellValue(Row row, int column)
- {
- if (row == null)
- {
- return row;
- }
- Object val = "";
- try
- {
- Cell cell = row.getCell(column);
- if (Validator.isNotNull(cell))
- {
- if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA)
- {
- val = cell.getNumericCellValue();
- if (DateUtil.isCellDateFormatted(cell))
- {
- val = DateUtil.getJavaDate((Double) val); // POI Excel 鏃ユ湡鏍煎紡杞崲
- }
- else
- {
- if ((Double) val % 1 != 0)
- {
- val = new BigDecimal(val.toString());
- }
- else
- {
- val = new DecimalFormat("0").format(val);
- }
- }
- }
- else if (cell.getCellType() == CellType.STRING)
- {
- val = cell.getStringCellValue();
- }
- else if (cell.getCellType() == CellType.BOOLEAN)
- {
- val = cell.getBooleanCellValue();
- }
- else if (cell.getCellType() == CellType.ERROR)
- {
- val = cell.getErrorCellValue();
- }
-
- }
- }
- catch (Exception e)
- {
- return val;
- }
- return val;
- }
}
--
Gitblit v1.9.3