微信小程序旅游平台微信小程序旅游平台

package com.tourism.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.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.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.RegExUtils; 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.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 com.tourism.common.annotation.Excel; import com.tourism.common.annotation.Excel.ColumnType; import com.tourism.common.annotation.Excel.Type; import com.tourism.common.annotation.Excels; import com.tourism.common.config.TourismConfig; import com.tourism.common.core.domain.AjaxResult; import com.tourism.common.core.text.Convert; import com.tourism.common.exception.UtilException; import com.tourism.common.utils.DateUtils; import com.tourism.common.utils.DictUtils; import com.tourism.common.utils.StringUtils; import com.tourism.common.utils.file.FileTypeUtils; import com.tourism.common.utils.file.FileUtils; import com.tourism.common.utils.file.ImageUtils; import com.tourism.common.utils.reflect.ReflectUtils; /** * Excel相关处理 * * @author tourism */ 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 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, 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(); } /** * 创建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)); } } /** * 对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) { pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb); } else { pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb); } // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1 int rows = sheet.getLastRowNum(); if (rows > 0) { // 定义一个map用于存放excel列的序号和field. Map<String, Integer> cellMap = new HashMap<String, Integer>(); // 获取表头 Row heard = sheet.getRow(titleNum); for (int i