archiveManager/src/main/java/com/ruoyi/domain/DocumentMaterials.java
@@ -28,6 +28,12 @@ @Excel(name = "文件材料序号") private Long fileNumber; @Excel(name = "文件编号") private String documentNumber; public Long getFileNumber() { return fileNumber; } @@ -279,6 +285,14 @@ return securityLevel; } public String getDocumentNumber() { return documentNumber; } public void setDocumentNumber(String documentNumber) { this.documentNumber = documentNumber; } public void setIsSensitive(String isSensitive) { this.isSensitive = isSensitive; archiveManager/src/main/java/com/ruoyi/domain/vo/DocumentMaterialsVo.java
@@ -8,6 +8,12 @@ @Data public class DocumentMaterialsVo { @Excel(name="序号") private Long num; @Excel(name = "文件编号") private String documentNumber; @Excel(name = "编任者") private String creator; @Excel(name = "文件题名") @@ -23,6 +29,15 @@ private Long pageNumber; @Excel(name = "备注") private String remarks; @Excel(name = "档号") private String recordId; @Excel(name = "公开属性") private String publicity; // @Excel(name = "图片", cellType = Excel.ColumnType.IMAGE) // private String url; } archiveManager/src/main/java/com/ruoyi/domain/vo/DocumentMaterialsVoSmall.java
New file @@ -0,0 +1,59 @@ package com.ruoyi.domain.vo; import com.fasterxml.jackson.annotation.JsonFormat; import com.ruoyi.common.annotation.Excel; import lombok.Data; import java.util.Date; @Data public class DocumentMaterialsVoSmall { @Excel(name="序号") private Long num; @Excel(name = "文件编号") private String documentNumber; @Excel(name = "编任者") private String creator; @Excel(name = "文件题名") private String title; @JsonFormat(pattern = "yyyy-MM-dd") @Excel(name = "日期", width = 30, dateFormat = "yyyy-MM-dd") private Date date; @Excel(name = "页号") private Long pageNumber; @Excel(name = "备注") private String remarks; public DocumentMaterialsVoSmall(Long num, String documentNumber, String creator, String title, Date date, Long pageNumber, String remarks) { this.num = num; this.documentNumber = documentNumber; this.creator = creator; this.title = title; this.date = date; this.pageNumber = pageNumber; this.remarks = remarks; } // public DocumentMaterialsVoSmall(Long num, String documentNumber, String creator, String title, Date date, Long pageNumber, String remarks) { // this.num = num; // this.documentNumber = documentNumber; // this.creator = creator; // // } // @Excel(name = "档号") // private String recordId; // @Excel(name = "公开属性") // private String publicity; // @Excel(name = "图片", cellType = Excel.ColumnType.IMAGE) // private String url; } archiveManager/src/main/java/com/ruoyi/mapper/DocumentMaterialsMapper.java
@@ -12,8 +12,8 @@ @Mapper public interface DocumentMaterialsMapper extends BaseMapper<DocumentMaterials> { @Select("update document_materials SET url = #{url},file_number=#{fileNumber},page_order=#{pageOrder},width=#{width},height=#{height},file_size=#{fileSize}, format=#{format} where page_number = #{pageNumber}") Long updateInfoByPageNumber(@Param("pageNumber") Long pageNumber,@Param("fileNumber") Long fileNumber,@Param("pageOrder") Long pageOrder,@Param("width") int width, @Param("height") int height, @Param("fileSize") Double fileSize,@Param("url") String url,@Param("format") String format); @Select("update document_materials SET url = #{url},file_number=#{fileNumber},page_order=#{pageOrder},width=#{width},height=#{height},horizontal_resolution=#{horizontalResolution},vertical_resolution=#{verticalResolution},file_size=#{fileSize}, format=#{format} where page_number = #{pageNumber}") Long updateInfoByPageNumber(@Param("pageNumber") Long pageNumber,@Param("fileNumber") Long fileNumber,@Param("pageOrder") Long pageOrder,@Param("width") int width, @Param("height") int height, @Param("horizontalResolution") int horizontalResolution, @Param("verticalResolution") int verticalResolution, @Param("fileSize") Double fileSize,@Param("url") String url,@Param("format") String format); //根据题名拿到最大的pageOrder @@ -25,11 +25,11 @@ Long getFileNumber(@Param("pageNumber") Long pageNumber); //查询卷内目录 @Select("select creator,title, date, page_number,remarks from document_materials\n" + "WHERE page_number IN (\n" + " SELECT MIN(page_number)\n" + " FROM document_materials where record_id=#{recordId}\n" + " GROUP BY stage, title\n" + ") order by page_number ;") List<DocumentMaterialsVo> getArchiveMatInfo(@Param("recordId") String recordId); @Select( "select ROW_NUMBER() OVER (ORDER BY page_number) AS num, document_number, creator,title, date, page_number,dm.remarks, ar.record_id, publicity from document_materials as dm, archive_records as ar \n" + " WHERE ar.id = dm.record_id and page_number IN (\n" + " SELECT MIN(page_number)\n" + " FROM document_materials where record_id=#{recordId}\n" + " GROUP BY stage, title) and ar.id =#{recordId} order by page_number ;") List<DocumentMaterialsVo> getArchiveMatInfo(@Param("recordId") int recordId); } archiveManager/src/main/java/com/ruoyi/service/IDocumentMaterialsService.java
@@ -59,6 +59,6 @@ */ public int deleteDocumentMaterialsByMaterialId(String materialId); public Long getFiNum(Long pageNumber); public int updateByPageNumber(Long pageNumber,Long fileNumber, int wid, int hei, double sz,String url,String format); public int updateByPageNumber(Long pageNumber,Long fileNumber, int wid, int hei, int wdpi, int hdpi, double sz,String url,String format); public List<DocumentMaterialsVo> findArchMInfo(String recordId); } archiveManager/src/main/java/com/ruoyi/service/impl/DocumentMaterialsServiceImpl.java
@@ -159,7 +159,7 @@ } @Override public int updateByPageNumber(Long pageNumber, Long fileNumber, int width, int height, double fileSize, String url, String format) { public int updateByPageNumber(Long pageNumber, Long fileNumber, int width, int height, int horizontalResolution, int verticalResolution, double fileSize, String url, String format) { //根据pageNumber拿到title，然后计算pageOrder LambdaQueryWrapper<DocumentMaterials> lqw = new LambdaQueryWrapper<>(); lqw.eq(pageNumber!=null, DocumentMaterials::getPageNumber, pageNumber); @@ -174,13 +174,13 @@ pageOrder = maxPOrder + 1; } this.baseMapper.updateInfoByPageNumber(pageNumber, fileNumber,pageOrder, width, height, fileSize, url,format); this.baseMapper.updateInfoByPageNumber(pageNumber, fileNumber,pageOrder, width, height, horizontalResolution, verticalResolution, fileSize, url,format); return 0; } @Override public List<DocumentMaterialsVo> findArchMInfo(String recordId) { return this.baseMapper.getArchiveMatInfo(recordId); return this.baseMapper.getArchiveMatInfo(Integer.parseInt(recordId)); } } ruoyi-admin/pom.xml
@@ -54,6 +54,22 @@ <version>5.2.0</version> </dependency> <!-- https://mvnrepository.com/artifact/com.drewnoakes/metadata-extractor --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-imaging</artifactId> <version>1.0.0-alpha6</version> </dependency> <dependency> <groupId>com.drewnoakes</groupId> <artifactId>metadata-extractor</artifactId> <version>2.19.0</version> </dependency> <!-- 防止进入swagger页面报类型转换错误，排除3.0.0中的引用，手动增加1.6.2版本 --> <dependency> <groupId>io.swagger</groupId> ruoyi-admin/src/main/java/com/ruoyi/web/controller/archive/DocumentMaterialsController.java
@@ -3,10 +3,14 @@ import java.awt.image.BufferedImage; import java.io.ByteArrayInputStream; import java.util.List; import javax.imageio.ImageIO; import javax.servlet.http.HttpServletResponse; import com.drew.imaging.ImageMetadataReader; import com.drew.metadata.Metadata; import com.drew.metadata.exif.ExifIFD0Directory; import com.ruoyi.common.config.RuoYiConfig; import com.ruoyi.common.core.text.Convert; import com.ruoyi.common.utils.ServletUtils; @@ -15,6 +19,9 @@ import com.ruoyi.domain.DocumentMaterials; import com.ruoyi.framework.config.ServerConfig; import com.ruoyi.service.IDocumentMaterialsService; import com.sun.xml.internal.ws.api.addressing.WSEndpointReference; import org.apache.commons.imaging.ImageInfo; import org.apache.commons.imaging.Imaging; import org.springframework.security.access.prepost.PreAuthorize; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; @@ -156,10 +163,18 @@ int hei = bufferedImage.getHeight(); double sz = Double.parseDouble(String.format("%.2f", file.getSize()*1.0/1024)); System.out.println(wid+":"+hei+":"+sz); //拿到图像的dpi信息 ImageInfo info = Imaging.getImageInfo(file.getBytes()); int wdpi = info.getPhysicalWidthDpi() ; int hdpi = info.getPhysicalHeightDpi(); System.out.println("DPI: " + info.getPhysicalWidthDpi()); //计算fileNumber Long fileNumber = documentMaterialsService.getFiNum(nam); //插入数据库对应的url当中 documentMaterialsService.updateByPageNumber(nam, fileNumber,wid, hei, sz, fileName, pname); documentMaterialsService.updateByPageNumber(nam, fileNumber,wid, hei,wdpi, hdpi, sz, fileName, pname); // System.out.println(url); // System.out.println(fileName); ajax.put("fileName", fileName); ruoyi-admin/src/main/java/com/ruoyi/web/controller/archive/archiveAllExportController.java
@@ -8,10 +8,14 @@ import com.itextpdf.text.pdf.PdfWriter; import com.ruoyi.common.config.RuoYiConfig; import com.ruoyi.common.utils.file.FileUtils; import com.ruoyi.common.utils.poi.ExcelExp; import com.ruoyi.common.utils.poi.ExcelUtil; import com.ruoyi.common.utils.poi.ExcelUtilManySheet; import com.ruoyi.common.utils.poi.ExcelUtilManySheetSecond; import com.ruoyi.domain.DocumentMaterials; import com.ruoyi.domain.vo.ArchiveInfoVo; import com.ruoyi.domain.vo.DocumentMaterialsVo; import com.ruoyi.domain.vo.DocumentMaterialsVoSmall; import com.ruoyi.framework.web.domain.server.Sys; import com.ruoyi.service.IArchiveRecordsService; import com.ruoyi.service.IDocumentMaterialsService; @@ -36,9 +40,11 @@ import java.net.URLEncoder; import java.nio.file.Files; import java.nio.file.Path; import java.nio.file.Paths; import java.text.SimpleDateFormat; import java.util.*; import java.util.List; import java.util.stream.Collectors; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipInputStream; @@ -59,6 +65,64 @@ @Autowired private IArchiveRecordsService iArchiveRecordsService; @PostMapping("/importTemplate") public void importTemplate(HttpServletResponse response) throws IOException { //导出卷面封面代码 ArchiveInfoVo aIV = iArchiveRecordsService.selectByRecordId(55L); List<ArchiveInfoVo> arsi = new ArrayList<>(); arsi.add(aIV); ExcelExp e1 = new ExcelExp("案卷封面数据",arsi, ArchiveInfoVo.class); ExcelExp e2 = new ExcelExp("提取方法…", arsi, ArchiveInfoVo.class); List<ExcelExp> mysheet = new ArrayList<ExcelExp>(); mysheet.add(e1); mysheet.add(e2); ByteOutputStream bos1 = new ByteOutputStream(); ExcelUtilManySheetSecond<List<ExcelExp>> util2 = new ExcelUtilManySheetSecond<List<ExcelExp>>(mysheet); util2.exportExcelManySheet(response, mysheet); //导出卷面目录代码 // DocumentMaterials documentMaterials = new DocumentMaterials(); // documentMaterials.setRecordId(55L); // // List<DocumentMaterials> docs = documentMaterialsService.selectDocumentMaterialsList(documentMaterials); // List<DocumentMaterialsVo> dsvs = documentMaterialsService.findArchMInfo("55"); // // dsvs.get(0).setUrl("/profile/upload/2025/08/14/30_20250814212128A031.jpg"); // // // // dsvs.stream().map() // List<DocumentMaterialsVoSmall> list2 = dsvs.stream().map(res -> new DocumentMaterialsVoSmall(res.getNum(), res.getDocumentNumber(),res.getCreator(), // res.getTitle(), res.getDate(), res.getPageNumber(), res.getRemarks())).collect(Collectors.toList()); // // String recordId = dsvs.get(0).getRecordId(); // byte[] imgr = barcodeService.generateBarcodeImage(recordId); // ExcelExp e1 = new ExcelExp("卷内目录数据",dsvs, DocumentMaterialsVo.class); // ExcelExp e2 = new ExcelExp("卷内数据", list2, recordId, imgr, DocumentMaterialsVoSmall.class); // List<ExcelExp> mysheet = new ArrayList<ExcelExp>(); // mysheet.add(e1); // mysheet.add(e2); // ExcelUtilManySheet<List<ExcelExp>> util2 = new ExcelUtilManySheet<List<ExcelExp>>(mysheet); // util2.exportExcelManySheet(response, mysheet); } /** * 打包下载 * @param response @@ -303,16 +367,35 @@ zos.putNextEntry(entry); ByteOutputStream bos = new ByteOutputStream(); util.byteOutputStreamExcel(bos, docs,"Date List", ""); util.byteOutputStreamExcel(bos, docs,"电子文件目录", ""); bos.writeTo(zos); //拿到卷内目录的excel List<DocumentMaterialsVoSmall> list2 = dsvs.stream().map(res -> new DocumentMaterialsVoSmall(res.getNum(), res.getDocumentNumber(),res.getCreator(), res.getTitle(), res.getDate(), res.getPageNumber(), res.getRemarks())).collect(Collectors.toList()); String recordId = dsvs.get(0).getRecordId(); byte[] imgr = barcodeService.generateBarcodeImage(recordId); ExcelExp e1 = new ExcelExp("卷内目录数据",dsvs, DocumentMaterialsVo.class); ExcelExp e2 = new ExcelExp("卷内数据", list2, recordId, imgr, DocumentMaterialsVoSmall.class); List<ExcelExp> mysheet = new ArrayList<ExcelExp>(); mysheet.add(e1); mysheet.add(e2); ExcelUtilManySheet<List<ExcelExp>> util2 = new ExcelUtilManySheet<List<ExcelExp>>(mysheet); ZipEntry entr = new ZipEntry("卷内目录" + ".xls"); ExcelUtil<DocumentMaterialsVo> util1 = new ExcelUtil<DocumentMaterialsVo>(DocumentMaterialsVo.class); System.out.println(dsvs); zos.putNextEntry(entr); ByteOutputStream bos1 = new ByteOutputStream(); util1.byteOutputStreamExcel(bos1, dsvs,"Date List", ""); util2.exportExcelManySheet(bos1, mysheet); // util1.byteOutputStreamExcel(bos1, dsvs,"Date List", ""); bos1.writeTo(zos); //把excel转为pdf
New file @@ -0,0 +1,79 @@ package com.ruoyi.common.utils.poi; import java.util.List; public class ExcelExp { private String fileName;// sheet的名称 private String[] handers;// sheet里的标题 private List dataset;// sheet里的数据集 private Class clazz; private String recordId; //档案号 private byte[] imgr; //条形码 public ExcelExp(String fileName, List dataset, Class clazz) { this.fileName = fileName; this.dataset = dataset; this.clazz = clazz; } public ExcelExp(String fileName, List dataset, String recordId, byte[] imgr,Class clazz) { this.fileName = fileName; this.dataset = dataset; this.clazz = clazz; this.recordId = recordId; this.imgr = imgr; } public String getFileName() { return fileName; } public void setFileName(String fileName) { this.fileName = fileName; } public String[] getHanders() { return handers; } public void setHanders(String[] handers) { this.handers = handers; } public List getDataset() { return dataset; } public void setDataset(List dataset) { this.dataset = dataset; } public String getRecordId() { return recordId; } public void setRecordId(String recordId) { this.recordId = recordId; } public byte[] getImgr() { return imgr; } public void setImgr(byte[] imgr) { this.imgr = imgr; } public Class getClazz() { return clazz; } public void setClazz(Class clazz) { this.clazz = clazz; } }
New file @@ -0,0 +1,980 @@ package com.ruoyi.common.utils.poi; import com.ruoyi.common.annotation.Excel; import com.ruoyi.common.annotation.Excels; import com.ruoyi.common.config.RuoYiConfig; import com.ruoyi.common.core.text.Convert; import com.ruoyi.common.utils.DateUtils; import com.ruoyi.common.utils.StringUtils; import com.ruoyi.common.utils.file.FileTypeUtils; import com.ruoyi.common.utils.file.ImageUtils; import com.ruoyi.common.utils.reflect.ReflectUtils; import com.sun.xml.internal.messaging.saaj.util.ByteOutputStream; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; 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 javax.servlet.http.HttpServletResponse; import java.io.ByteArrayInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.math.BigDecimal; import java.nio.file.Files; import java.nio.file.Paths; import java.text.DecimalFormat; import java.util.*; import java.util.stream.Collectors; /** * Excel相关处理 * * @author ruoyi */ public class ExcelUtilManySheet<T> { private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class); /** * Excel sheet最大行数，默认65536 */ public static final int sheetSize = 65536; /** * 工作表名称 */ private String sheetName; /** * 导出类型（EXPORT:导出数据；IMPORT：导入模板） */ private Excel.Type type; /** * 工作薄对象 */ private Workbook wb; /** * 工作表对象 */ private Sheet sheet; /** * 样式列表 */ private Map<String, CellStyle> styles; /** * 导入导出数据列表 */ private List<T> list; /** * 注解列表 */ 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 List<ExcelExp> clazzlist; public ExcelUtilManySheet(List<ExcelExp> clazzlist) { this.clazzlist = clazzlist; } public void init(List<T> list, String sheetName, Excel.Type type) { if (list == null) { list = new ArrayList<T>(); } this.list = list; this.sheetName = sheetName; this.type = type; createExcelField(); } /** * 对excel表单默认第一个索引名转换成list * * @param is 输入流 * @return 转换后集合 */ public List<T> importExcel(InputStream is) throws Exception { return importExcel(StringUtils.EMPTY, is); } /** * 对excel表单指定表格索引名转换成list * * @param sheetName 表格索引名 * @param is 输入流 * @return 转换后集合 */ public List<T> importExcel(String sheetName, InputStream is) throws Exception { this.type = Excel.Type.IMPORT; this.wb = WorkbookFactory.create(is); List<T> list = new ArrayList<T>(); Sheet sheet = null; if (StringUtils.isNotEmpty(sheetName)) { // 如果指定sheet名,则取指定sheet中的内容. sheet = wb.getSheet(sheetName); } else { // 如果传入的sheet名不存在则默认指向第1个sheet. sheet = wb.getSheetAt(0); } if (sheet == null) { throw new IOException("文件sheet不存在"); } int rows = sheet.getPhysicalNumberOfRows(); if (rows > 0) { // 定义一个map用于存放excel列的序号和field. 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 (StringUtils.isNotNull(cell)) { String value = this.getCellValue(heard, i).toString(); cellMap.put(value, i); } else { cellMap.put(null, i); } } // 有数据时才处理 得到类的所有field. Field[] allFields = clazz.getDeclaredFields(); // 定义一个map用于存放列的序号和field. 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() == Excel.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); T entity = null; for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet()) { Object val = this.getCellValue(row, entry.getKey()); // 如果不存在实例则新建. entity = (entity == null ? clazz.newInstance() : entity); // 从map中得到对应列的field. Field field = fieldsMap.get(entry.getKey()); // 取得类型,并根据对象类型设置值. Class<?> fieldType = field.getType(); if (String.class == fieldType) { String s = Convert.toStr(val); if (StringUtils.endsWith(s, ".0")) { val = StringUtils.substringBefore(s, ".0"); } else { String dateFormat = field.getAnnotation(Excel.class).dateFormat(); if (StringUtils.isNotEmpty(dateFormat)) { val = DateUtils.parseDateToStr(dateFormat, (Date) val); } else { val = Convert.toStr(val); } } } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(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 (StringUtils.isNotNull(fieldType)) { Excel attr = field.getAnnotation(Excel.class); String propertyName = field.getName(); if (StringUtils.isNotEmpty(attr.targetAttr())) { propertyName = field.getName() + "." + attr.targetAttr(); } else if (StringUtils.isNotEmpty(attr.readConverterExp())) { val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator()); } ReflectUtils.invokeSetter(entity, propertyName, val); } } list.add(entity); } } return list; } /** * 对list数据源将其里面的数据导入到excel表单 * * @param response 返回数据 * @param list 导出数据集合 * @param sheetName 工作表的名称 * @return 结果 * @throws IOException */ public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); this.init(list, sheetName, Excel.Type.EXPORT); exportExcel(response.getOutputStream()); } /** * 对list数据源将其里面的数据导入到excel表单 * * @param * @param list 导出数据集合 * @return 结果 * @throws IOException */ public void exportExcelManySheet(ByteOutputStream byteOutputStream, List<ExcelExp> list) throws IOException { // response.setContentType("application/vnd.ms-excel"); // response.setCharacterEncoding("utf-8"); try { createWorkbook(); for (int index = 0; index <list.size(); index++) { this.clazz = list.get(index).getClazz(); this.init(list.get(index).getDataset(), list.get(index).getFileName(), Excel.Type.EXPORT); // 取出一共有多少个sheet. // double sheetNo = Math.ceil(list.size() / sheetSize); createSheetManySheet(index); Row row; int column = 0; if(index == 1) { // 合并第一行的前5个单元格 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); // 创建行并设置高度 row = sheet.createRow(0); row.setHeight((short)(40 * 40)); // 创建单元格并添加图片 Cell cell = row.createCell(0); // try { // String imagePath = "/profile/upload/2025/08/14/30_20250814212128A031.jpg"; // if (Files.exists(Paths.get(imagePath))) { // byte[] imageBytes = Files.readAllBytes(Paths.get(imagePath)); // 将byte数组转换成inputstream，以便插入到Excel中 ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 5), cell.getRow().getRowNum() + 1); // 计算居中位置 int col1 = 1; // 中间列 int col2 = col1 + 5; anchor.setCol1(col1); anchor.setCol2(col2); anchor.setRow1(0); anchor.setRow2(1); // anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE); // anchor.set // anchor.setHorizontallyCenter(true); // byte[] data = ImageUtils.getImage(imagePath); //拿到图片 byte[] data = list.get(index).getImgr(); //Files.readAllBytes(Paths.get(RuoYiConfig.getProfile() + "/upload/2025/08/14/30_20250814212128A031.jpg")); System.out.println(data.length); getDrawingPatriarch(cell.getSheet()).createPicture(anchor, cell.getSheet().getWorkbook().addPicture(data, getImageType(data))); //} else { // log.warn("图片文件不存在: {}", imagePath); // } //} catch (IOException e) { // log.error("添加图片失败: {}", e.getMessage()); // } row = sheet.createRow(1); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6)); Cell titleCell = row.createCell(0); titleCell.setCellValue("卷内目录"); // 设置加粗和居中样式 CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setBold(true); font.setFontName("宋体"); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER); titleCell.setCellStyle(style); row = sheet.createRow(2); // row.createCell(4).setCellValue("档号:"); // row.createCell(4).setCellValue("档号:"); //拿到档号 String recordId = list.get(index).getRecordId(); row.createCell(5).setCellValue(recordId); row = sheet.createRow(3); column = 0; } else { // 普通sheet处理 // recordId = (DocumentMaterialsVo)list.get(0) row = sheet.createRow(0); column = 0; } // // 产生一行 // 写入各个字段的列头名称 for (Object[] os : fields) { Excel excel = (Excel) os[1]; this.createCell(excel, row, column++); } if (Excel.Type.EXPORT.equals(type)) { fillExcelData(index, row); addStatisticsRow(); } } wb.write(byteOutputStream); } catch (IOException e) { log.error("导出Excel异常{}", e.getMessage()); } finally { if (wb != null) { try { wb.close(); } catch (IOException e1) { e1.printStackTrace(); } } // if (response.getOutputStream() != null) { // try { // response.getOutputStream().close(); // } catch (IOException e1) { // e1.printStackTrace(); // } // } } // exportExcel(response.getOutputStream()); } /** * 对list数据源将其里面的数据导入到excel表单 * * @param sheetName 工作表的名称 * @return 结果 */ public void importTemplateExcel(HttpServletResponse response, String sheetName) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); this.init(null, sheetName, Excel.Type.IMPORT); exportExcel(response.getOutputStream()); } /** * 对list数据源将其里面的数据导入到excel表单 * * @return 结果 */ public void exportExcel(OutputStream outputStream) { try { // 取出一共有多少个sheet. 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 (Excel.Type.EXPORT.equals(type)) { fillExcelData(index, row); addStatisticsRow(); } } wb.write(outputStream); } catch (Exception e) { log.error("导出Excel异常{}", e.getMessage()); } finally { if (wb != null) { try { wb.close(); } catch (IOException e1) { e1.printStackTrace(); } } if (outputStream != null) { try { outputStream.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 = 0; i < list.size(); i++) { if(index==1) row = sheet.createRow(i + 4 ); else row = sheet.createRow(i + 1 ); // 得到导出对象. 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 (Excel.ColumnType.STRING == attr.cellType()) { cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix()); } else if (Excel.ColumnType.NUMERIC == attr.cellType()) { cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value)); } else if (Excel.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 (StringUtils.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 (StringUtils.isNotEmpty(attr.prompt())) { // 这里默认设了2-101列提示. setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column); } // 如果设置了combo属性则本列只能选择不能输入 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); // attr.align(); // 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(); if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) { cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value)); } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) { cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, 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 (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); } /** * 反向解析值 男=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 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(); } } /** * 获取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 (StringUtils.isNotEmpty(excel.targetAttr())) { String target = excel.targetAttr(); if (target.indexOf(".") > -1) { 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 (StringUtils.isNotNull(o) && StringUtils.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() == Excel.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 index 序号 */ public void createSheetManySheet( int index) { this.sheet = wb.createSheet(); this.styles = createStyles(wb); wb.setSheetName(index, sheetName); } /** * 获取单元格值 * * @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 (StringUtils.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; } } ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtilManySheetSecond.java
New file @@ -0,0 +1,904 @@ package com.ruoyi.common.utils.poi; import com.ruoyi.common.annotation.Excel; import com.ruoyi.common.annotation.Excels; import com.ruoyi.common.core.text.Convert; import com.ruoyi.common.utils.DateUtils; import com.ruoyi.common.utils.StringUtils; import com.ruoyi.common.utils.file.FileTypeUtils; import com.ruoyi.common.utils.file.ImageUtils; import com.ruoyi.common.utils.reflect.ReflectUtils; import com.sun.xml.internal.messaging.saaj.util.ByteOutputStream; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; 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 javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.math.BigDecimal; import java.text.DecimalFormat; import java.util.*; import java.util.stream.Collectors; /** * Excel相关处理 * * @author ruoyi */ public class ExcelUtilManySheetSecond<T> { private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class); /** * Excel sheet最大行数，默认65536 */ public static final int sheetSize = 65536; /** * 工作表名称 */ private String sheetName; /** * 导出类型（EXPORT:导出数据；IMPORT：导入模板） */ private Excel.Type type; /** * 工作薄对象 */ private Workbook wb; /** * 工作表对象 */ private Sheet sheet; /** * 样式列表 */ private Map<String, CellStyle> styles; /** * 导入导出数据列表 */ private List<T> list; /** * 注解列表 */ 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 List<ExcelExp> clazzlist; public ExcelUtilManySheetSecond(List<ExcelExp> clazzlist) { this.clazzlist = clazzlist; } public void init(List<T> list, String sheetName, Excel.Type type) { if (list == null) { list = new ArrayList<T>(); } this.list = list; this.sheetName = sheetName; this.type = type; createExcelField(); } /** * 对excel表单默认第一个索引名转换成list * * @param is 输入流 * @return 转换后集合 */ public List<T> importExcel(InputStream is) throws Exception { return importExcel(StringUtils.EMPTY, is); } /** * 对excel表单指定表格索引名转换成list * * @param sheetName 表格索引名 * @param is 输入流 * @return 转换后集合 */ public List<T> importExcel(String sheetName, InputStream is) throws Exception { this.type = Excel.Type.IMPORT; this.wb = WorkbookFactory.create(is); List<T> list = new ArrayList<T>(); Sheet sheet = null; if (StringUtils.isNotEmpty(sheetName)) { // 如果指定sheet名,则取指定sheet中的内容. sheet = wb.getSheet(sheetName); } else { // 如果传入的sheet名不存在则默认指向第1个sheet. sheet = wb.getSheetAt(0); } if (sheet == null) { throw new IOException("文件sheet不存在"); } int rows = sheet.getPhysicalNumberOfRows(); if (rows > 0) { // 定义一个map用于存放excel列的序号和field. 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 (StringUtils.isNotNull(cell)) { String value = this.getCellValue(heard, i).toString(); cellMap.put(value, i); } else { cellMap.put(null, i); } } // 有数据时才处理 得到类的所有field. Field[] allFields = clazz.getDeclaredFields(); // 定义一个map用于存放列的序号和field. 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() == Excel.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); T entity = null; for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet()) { Object val = this.getCellValue(row, entry.getKey()); // 如果不存在实例则新建. entity = (entity == null ? clazz.newInstance() : entity); // 从map中得到对应列的field. Field field = fieldsMap.get(entry.getKey()); // 取得类型,并根据对象类型设置值. Class<?> fieldType = field.getType(); if (String.class == fieldType) { String s = Convert.toStr(val); if (StringUtils.endsWith(s, ".0")) { val = StringUtils.substringBefore(s, ".0"); } else { String dateFormat = field.getAnnotation(Excel.class).dateFormat(); if (StringUtils.isNotEmpty(dateFormat)) { val = DateUtils.parseDateToStr(dateFormat, (Date) val); } else { val = Convert.toStr(val); } } } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(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 (StringUtils.isNotNull(fieldType)) { Excel attr = field.getAnnotation(Excel.class); String propertyName = field.getName(); if (StringUtils.isNotEmpty(attr.targetAttr())) { propertyName = field.getName() + "." + attr.targetAttr(); } else if (StringUtils.isNotEmpty(attr.readConverterExp())) { val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator()); } ReflectUtils.invokeSetter(entity, propertyName, val); } } list.add(entity); } } return list; } /** * 对list数据源将其里面的数据导入到excel表单 * * @param response 返回数据 * @param list 导出数据集合 * @param sheetName 工作表的名称 * @return 结果 * @throws IOException */ public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); this.init(list, sheetName, Excel.Type.EXPORT); exportExcel(response.getOutputStream()); } /** * 对list数据源将其里面的数据导入到excel表单 * * @param * @param list 导出数据集合 * @return 结果 * @throws IOException */ public void exportExcelManySheet(HttpServletResponse response, List<ExcelExp> list) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); try { createWorkbook(); for (int index = 0; index <list.size(); index++) { this.clazz = list.get(index).getClazz(); this.init(list.get(index).getDataset(), list.get(index).getFileName(), Excel.Type.EXPORT); // 取出一共有多少个sheet. // double sheetNo = Math.ceil(list.size() / sheetSize); createSheetManySheet(index); // 产生一行 Row row = sheet.createRow(0); int column = 0; // 写入各个字段的列头名称 for (Object[] os : fields) { Excel excel = (Excel) os[1]; this.createCell(excel, row, column++); } if (Excel.Type.EXPORT.equals(type)) { fillExcelData(index, row); addStatisticsRow(); } } wb.write(response.getOutputStream()); } catch (IOException e) { log.error("导出Excel异常{}", e.getMessage()); } finally { if (wb != null) { try { wb.close(); } catch (IOException e1) { e1.printStackTrace(); } } if (response.getOutputStream() != null) { try { response.getOutputStream().close(); } catch (IOException e1) { e1.printStackTrace(); } } } // exportExcel(response.getOutputStream()); } /** * 对list数据源将其里面的数据导入到excel表单 * * @param sheetName 工作表的名称 * @return 结果 */ public void importTemplateExcel(HttpServletResponse response, String sheetName) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); this.init(null, sheetName, Excel.Type.IMPORT); exportExcel(response.getOutputStream()); } /** * 对list数据源将其里面的数据导入到excel表单 * * @return 结果 */ public void exportExcel(OutputStream outputStream) { try { // 取出一共有多少个sheet. 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 (Excel.Type.EXPORT.equals(type)) { fillExcelData(index, row); addStatisticsRow(); } } wb.write(outputStream); } catch (Exception e) { log.error("导出Excel异常{}", e.getMessage()); } finally { if (wb != null) { try { wb.close(); } catch (IOException e1) { e1.printStackTrace(); } } if (outputStream != null) { try { outputStream.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 = 0; i < list.size(); i++) { if(index==1) row = sheet.createRow(i + 4 ); else row = sheet.createRow(i + 1 ); // 得到导出对象. 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 (Excel.ColumnType.STRING == attr.cellType()) { cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix()); } else if (Excel.ColumnType.NUMERIC == attr.cellType()) { cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value)); } else if (Excel.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 (StringUtils.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 (StringUtils.isNotEmpty(attr.prompt())) { // 这里默认设了2-101列提示. setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column); } // 如果设置了combo属性则本列只能选择不能输入 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); // attr.align(); // 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(); if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) { cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value)); } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) { cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, 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 (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); } /** * 反向解析值 男=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 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(); } } /** * 获取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 (StringUtils.isNotEmpty(excel.targetAttr())) { String target = excel.targetAttr(); if (target.indexOf(".") > -1) { 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 (StringUtils.isNotNull(o) && StringUtils.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() == Excel.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 index 序号 */ public void createSheetManySheet( int index) { this.sheet = wb.createSheet(); this.styles = createStyles(wb); wb.setSheetName(index, sheetName); } /** * 获取单元格值 * * @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 (StringUtils.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; } }
