基于Java、Vue、JavaScript、HTML的电力考试通设计源码

package com.turing.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.text.ParseException; import java.text.SimpleDateFormat; import java.time.LocalDate; import java.time.LocalDateTime; import java.util.*; import java.util.stream.Collectors; import javax.servlet.http.HttpServletResponse; import com.turing.common.annotation.ExcelsByWx; 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.*; import org.apache.poi.ooxml.POIXMLDocumentPart; import org.apache.poi.ss.usermodel.*; 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.*; import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.turing.common.annotation.Excel; import com.turing.common.annotation.Excel.ColumnType; import com.turing.common.annotation.Excel.Type; import com.turing.common.annotation.Excels; import com.turing.common.config.ElectricConfig; import com.turing.common.core.domain.AjaxResult; import com.turing.common.core.text.Convert; import com.turing.common.exception.UtilException; import com.turing.common.utils.DateUtils; import com.turing.common.utils.DictUtils; import com.turing.common.utils.StringUtils; import com.turing.common.utils.file.FileTypeUtils; import com.turing.common.utils.file.FileUtils; import com.turing.common.utils.file.ImageUtils; import com.turing.common.utils.reflect.ReflectUtils; /** * Excel相关处理 * * @author turing */ 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 = { "=", "-", "+", "@" }; /** * 用于dictType属性数据存储，避免重复查缓存 */ public Map<String, String> sysDictMap = new HashMap<String, String>(); /** * 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++; } } /** * 对excel表单默认第一个索引名转换成list * * @param is 输入流 * @return 转换后集合 */ public List<T> importExcel(InputStream is) throws Exception { return importExcel(is, 0); } /** * 对excel表单默认第一个索引名转换成list * * @param is 输入流 * @param titleNum 标题占用行数 * @return 转换后集合 */ public List<T> importExcel(InputStream is, int titleNum) throws Exception { return importExcel(StringUtils.EMPTY, is, titleNum); } /** * 对excel表单指定表格索引名转换成list * * @param sheetName 表格索引名 * @param titleNum 标题占用行数 * @param is 输入流 * @return 转换后集合 */ public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception { this.type = Type.IMPORT; this.wb = WorkbookFactory.create(is); List<T> list = new ArrayList<T>(); // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0); if (sheet == null) { throw new IOException("文件sheet不存在"); } boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook); Map<String, PictureData> pictures; if (isXSSFWorkbook) { pic