一款高效实用 IoTCard 管理 运营系统

package top.iotos.common.utils.poi; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.ParameterizedType; import java.math.BigDecimal; import java.text.DecimalFormat; import java.time.LocalDate; import java.time.LocalDateTime; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.Comparator; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import java.util.UUID; import java.util.stream.Collectors; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.ArrayUtils; import org.apache.commons.lang3.RegExUtils; import org.apache.commons.lang3.reflect.FieldUtils; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFPicture; import org.apache.poi.hssf.usermodel.HSSFPictureData; import org.apache.poi.hssf.usermodel.HSSFShape; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ooxml.POIXMLDocumentPart; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.ClientAnchor; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Drawing; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.usermodel.PictureData; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; 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; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFDataValidation; import org.apache.poi.xssf.usermodel.XSSFDrawing; import org.apache.poi.xssf.usermodel.XSSFPicture; import org.apache.poi.xssf.usermodel.XSSFShape; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import top.iotos.common.annotation.Excel; import top.iotos.common.annotation.Excel.ColumnType; import top.iotos.common.annotation.Excel.Type; import top.iotos.common.annotation.Excels; import top.iotos.common.config.RuoYiConfig; import top.iotos.common.core.domain.AjaxResult; import top.iotos.common.core.text.Convert; import top.iotos.common.exception.UtilException; import top.iotos.common.utils.DateUtils; import top.iotos.common.utils.DictUtils; import top.iotos.common.utils.StringUtils; import top.iotos.common.utils.file.FileTypeUtils; import top.iotos.common.utils.file.FileUtils; import top.iotos.common.utils.file.ImageUtils; import top.iotos.common.utils.reflect.ReflectUtils; /** * Excel相关处理 * @author iotos.top */ public class ExcelUtil<T> { private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class); public static final String FORMULA_REGEX_STR = "=|-|\\+|@"; public static final String[] FORMULA_STR = { "=", "-", "+", "@" }; /** * Excel sheet最大行数，默认65536 */ public static final int sheetSize = 65536; /** * 工作表名称 */ private String sheetName; /** * 导出类型（EXPORT:导出数据；IMPORT：导入模板） */ private Type type; /** * 工作薄对象 */ private Workbook wb; /** * 工作表对象 */ private Sheet sheet; /** * 样式列表 */ private Map<String, CellStyle> styles; /** * 导入导出数据列表 */ private List<T> list; /** * 注解列表 */ private List<Object[]> fields; /** * 当前行号 */ private int rownum; /** * 标题 */ private String title; /** * 最大高度 */ private short maxHeight; /** * 合并后最后行数 */ private int subMergedLastRowNum = 0; /** * 合并后开始行数 */ private int subMergedFirstRowNum = 1; /** * 对象的子列表方法 */ private Method subMethod; /** * 对象的子列表属性 */ private List<Field> subFields; /** * 统计列表 */ private Map<Integer, Double> statistics = new HashMap<Integer, Double>(); /** * 数字格式 */ private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00"); /** * 实体对象 */ public Class<T> clazz; /** * 需要排除列属性 */ public String[] excludeFields; public ExcelUtil(Class<T> clazz) { this.clazz = clazz; } /** * 隐藏Excel中列属性 * * @param fields 列属性名 示例[单个"name"/多个"id","name"] * @throws Exception */ public void hideColumn(String... fields) { this.excludeFields = fields; } public void init(List<T> list, String sheetName, String title, Type type) { if (list == null) { list = new ArrayList<T>(); } this.list = list; this.sheetName = sheetName; this.type = type; this.title = title; createExcelField(); createWorkbook(); createTitle(); createSubHead(); } /** * 创建excel第一行标题 */ public void createTitle() { if (StringUtils.isNotEmpty(title)) { subMergedFirstRowNum++; subMergedLastRowNum++; int titleLastCol = this.fields.size() - 1; if (isSubList()) { titleLastCol = titleLastCol + subFields.size() - 1; } 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(), titleLastCol)); } } /** * 创建对象的子列表名称 */ public void createSubHead() { if (isSubList()) { subMergedFirstRowNum++; subMergedLastRowNum++; Row subRow = sheet.createRow(rownum); int excelNum = 0; for (Object[] objects : fields) { Excel attr = (Excel) objects[1]; Cell headCell1 = subRow.createCell(excelNum); headCell1.setCellValue(attr.name()); headCell1.setCellStyle(styles.get(StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor()))); excelNum++; } int headFirstRow = excelNum - 1; int headLastRow = headFirstRow + subFields.size() - 1; if (headLastRow > headFirstRow) { sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, headFirstRow, headLastRow)); } rownum++; } }