package com.simuwang.base.common.util; import cn.hutool.core.collection.CollUtil; import cn.hutool.core.date.DateUtil; import cn.hutool.core.exceptions.ExceptionUtil; import cn.hutool.core.lang.Pair; import cn.hutool.core.util.StrUtil; import com.github.junrar.Archive; import com.github.junrar.exception.RarException; import com.github.junrar.rarfile.FileHeader; import com.simuwang.base.common.conts.DateConst; import com.simuwang.base.common.conts.EmailDataDirectionConst; import com.simuwang.base.pojo.vo.*; import org.apache.commons.io.FileUtils; import org.apache.pdfbox.Loader; import org.apache.pdfbox.pdmodel.PDDocument; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.*; import org.jsoup.Jsoup; import org.jsoup.nodes.Document; import org.jsoup.nodes.Element; import org.jsoup.select.Elements; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.*; import java.nio.file.Files; import java.nio.file.Paths; import java.text.NumberFormat; import java.time.LocalDate; import java.time.format.DateTimeFormatter; import java.util.*; import org.apache.commons.compress.archivers.ArchiveEntry; import org.apache.commons.compress.archivers.ArchiveInputStream; import org.apache.commons.compress.archivers.ArchiveStreamFactory; import org.apache.commons.compress.utils.IOUtils; import technology.tabula.ObjectExtractor; import technology.tabula.Page; import technology.tabula.PageIterator; import technology.tabula.RectangularTextContainer; import technology.tabula.extractors.SpreadsheetExtractionAlgorithm; public class ExcelUtil { private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class); public static boolean isExcel(String fileName) { return StrUtil.isNotBlank(fileName) && (fileName.endsWith("xls") || fileName.endsWith("xlsx") || fileName.endsWith("XLS") || fileName.endsWith("XLSX")); } public static boolean isPdf(String fileName) { return StrUtil.isNotBlank(fileName) && (fileName.endsWith("pdf") || fileName.endsWith("PDF")); } public static boolean isZip(String fileName) { return StrUtil.isNotBlank(fileName) && (fileName.endsWith("zip") || fileName.endsWith("ZIP")); } public static boolean isHTML(String fileName) { return StrUtil.isNotBlank(fileName) && fileName.endsWith("html"); } public static boolean isRAR(String fileName) { return StrUtil.isNotBlank(fileName) && (fileName.endsWith("rar") || fileName.endsWith("RAR")); } public static Sheet getSheet(File file, int sheetIndex) { if (file == null || !file.exists()) { return null; } InputStream is = file2InStream(file); String[] arr = file.getName().split("\\."); String ext = arr[arr.length - 1]; Sheet sheet = null; try { sheet = getSheet(is, ext, sheetIndex); } catch (Exception e) { logger.error(e.getMessage()); if (e.getMessage().contains("XSSF instead of HSSF")) { is = file2InStream(file); try { sheet = getSheet(is, "xlsx", sheetIndex); } catch (IOException e1) { e1.printStackTrace(); } } else if (e.getMessage().contains("HSSF instead of XSSF")) { is = file2InStream(file); try { sheet = getSheet(is, "xls", sheetIndex); } catch (IOException e1) { e1.printStackTrace(); } } else if ("xls".equals(ext) && e.getMessage().contains("left 4 bytes remaining still to be read")) { file = changeXls(file); is = file2InStream(file); try { sheet = getSheet(is, "xls", sheetIndex); logger.info("文件转换成功！"); } catch (IOException e1) { e1.printStackTrace(); } } else { sheet = null; } } return sheet; } public static Sheet getSheet(InputStream is, String ext, int sheetIndex) throws IOException { if (ext == null) { ext = "xls"; } ext = ext.toLowerCase(); Sheet sheet = null; if ("xls".equals(ext)) { Workbook wb = new HSSFWorkbook(is); sheet = getSheet(wb, sheetIndex, null); } else if ("xlsx".equals(ext)) { Workbook wb = new XSSFWorkbook(is); sheet = getSheet(wb, sheetIndex, null); } return sheet; } private static Sheet getSheet(Workbook wb, int sheetIndex, String sheetName) throws IOException { if (wb != null) { Sheet sheet = null; if (sheetIndex > -1) { sheet = wb.getSheetAt(sheetIndex); } else if (StrUtil.isNotBlank(sheetName) && !"null".equals(sheetName.toLowerCase())) { sheet = wb.getSheet(sheetName); } wb.close(); // 同时会把文件输入流关闭 return sheet; } return null; } public static InputStream file2InStream(File file) { if (!file.exists()) { return null; } else { FileInputStream is = null; try { is = new FileInputStream(file); } catch (FileNotFoundException var3) { var3.printStackTrace(); } return is; } } public static File changeXls(File file) { try { jxl.Workbook workbook = jxl.Workbook.getWorkbook(file); String fileName = "copy-" + file.getName(); File file2 = new File(file.getParent() + "/" + fileName); if (file2.exists()) { return file2; } jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(file2, workbook); wwb.write(); wwb.close(); workbook.close(); return file2; } catch (Exception e) { e.printStackTrace(); return null; } } public static String getCellValue(Cell cell) { if (cell == null) { return null; } String cellValue = ""; switch (cell.getCellTypeEnum()) { case STRING: cellValue = cell.getStringCellValue(); break; case NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { // 如果是日期格式的数字 Date dateCellValue = cell.getDateCellValue(); cellValue = DateUtil.format(dateCellValue, DateConst.YYYY_MM_DD); } else { // 否则是纯数字 NumberFormat numberFormat = NumberFormat.getNumberInstance(); numberFormat.setMaximumFractionDigits(15); double formulaResult = cell.getNumericCellValue(); cellValue = numberFormat.format(formulaResult).replaceAll(",", ""); } break; case BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case FORMULA: // 处理公式结果 try { cellValue = String.valueOf(cell.getNumericCellValue()); } catch (IllegalStateException e) { cellValue = cell.getStringCellValue(); } break; case BLANK: break; case ERROR: cellValue = "ERROR: " + cell.getErrorCellValue(); break; default: cellValue = ""; break; } // 去掉换行符号 cellValue = StrUtil.isNotBlank(cellValue) ? cellValue.replaceAll("[\\r\\n]+", "").replaceAll(",","").trim() : ""; return cellValue; } public static List extractCompressedFiles(String filePath, String destFilePath) { List filePathList = CollUtil.newArrayList(); try { if(ExcelUtil.isZip(filePath)){ File destFile = new File(destFilePath); if (!destFile.exists()) { destFile.mkdirs(); } logger.info("开始解压zip==================="+filePath); BufferedInputStream fis = new BufferedInputStream(new FileInputStream(filePath)); ArchiveInputStream ais = new ArchiveStreamFactory().createArchiveInputStream(fis); ArchiveEntry entry; while ((entry = ais.getNextEntry()) != null) { String uuid = UUID.randomUUID().toString().replaceAll("-",""); String entryName = entry.getName(); String fileName = null; if(entryName.contains(".")){ fileName = uuid + entryName.substring(entryName.lastIndexOf("."),entryName.length()); }else{ fileName = uuid; } File entryFile = new File(destFilePath, fileName); if (entry.isDirectory()) { entryFile.mkdirs(); logger.info("解压子文件：{}",entryFile.getName()); } else { try { FileOutputStream fos = new FileOutputStream(entryFile); IOUtils.copy(ais, fos); filePathList.add(entryFile.getPath()); logger.info("解压子文件：{}",entryFile.getPath()); fos.close(); }catch (Exception e){ logger.error(e.getMessage(),e); } } } ais.close(); fis.close(); } if(ExcelUtil.isRAR(filePath)){ logger.info("开始解压RAR==================="+filePath); String destPath = filePath.replaceAll(".rar", "").replaceAll(".RAR", ""); File destFile = new File(destPath); if (!destFile.exists()) { destFile.mkdirs(); } List rarDir = ExcelUtil.extractRar(filePath, destPath); for (String subFile : rarDir) { if(!subFile.contains(destPath)){ subFile = destPath+"/"+subFile; } logger.info("解压之后的文件："+subFile); } filePathList.addAll(rarDir); } }catch (Exception e) { logger.error(filePath+"======="+e.getMessage(),e); } logger.info("解压结束================"); return filePathList; } public static List extractRar(String inputFilePath, String outputDirPath){ List fileList = new ArrayList<>(); // 创建Archive对象，用于读取rar压缩文件格式 try{ Archive archive = new Archive(new FileInputStream(inputFilePath)); // 读取压缩文件中的所有子目录或子文件（FileHeader对象） List fileHeaderList = archive.getFileHeaders(); // 遍历子目录和子文件 for (FileHeader fd:fileHeaderList) { System.out.println(fd.getFileName()); File f = new File(outputDirPath+"/"+fd.getFileName()); if(fd.isDirectory()){ // 创建新子目录 f.mkdirs(); }else{ // 创建新子文件 f.createNewFile(); // 获取压缩包中的子文件输出流 InputStream in = archive.getInputStream(fd); // 复制文件输入流至新子文件 FileUtils.copyInputStreamToFile(in,f); fileList.add(f.getAbsolutePath()); } } } catch (Exception e) { logger.error(e.getMessage(),e); //调用unrar命令解压 try{ unrar(inputFilePath,outputDirPath); File dir = new File(outputDirPath); for (File file : dir.listFiles()) { logger.info(file.getAbsolutePath()); fileList.addAll(fileList(file)); } }catch (Exception e1){ logger.error(e.getMessage(),e1); } } return fileList; } private static List fileList(File file){ List fileList = new ArrayList<>(); if(file.isDirectory()){ File[] files = file.listFiles(); for (File file1 : files) { System.out.println(file1.getAbsolutePath()); if(file1.isDirectory()){ List dirFileList = fileList(file1); fileList.addAll(dirFileList); }else{ fileList.add(file1.getAbsolutePath()); } } }else{ fileList.add(file.getAbsolutePath()); System.out.println(file.getAbsolutePath()); } return fileList; } /** * 解压RAR文件 * @param rarFilePath RAR文件路径 * @param destDir 目标解压目录 * @throws IOException 如果IO错误或解压失败 * @throws InterruptedException 如果进程被中断 */ public static void unrar(String rarFilePath, String destDir) throws IOException, InterruptedException { // 确保目标目录存在 File destDirFile = new File(destDir); if(!destDirFile.exists()){ destDirFile.mkdirs(); } // 构建解压命令 ProcessBuilder processBuilder = new ProcessBuilder( "unrar", "x", "-o+", rarFilePath, destDir ); processBuilder.redirectErrorStream(true); // 合并标准错误流和标准输出流 // 执行命令 Process process = processBuilder.start(); // 读取命令输出（避免阻塞） try (BufferedReader reader = new BufferedReader( new InputStreamReader(process.getInputStream()))) { String line; while ((line = reader.readLine()) != null) { // 可选：处理输出信息（例如记录日志） System.out.println(line); } } // 等待命令执行完成 int exitCode = process.waitFor(); if (exitCode != 0) { throw new IOException("解压失败，退出码: " + exitCode); } } public static void writeDataToSheet(Sheet sheet, Elements rows) { int rowSize = rows.size(); for (int rowNum = 0; rowNum < rowSize; rowNum++) { Row sheetRow = sheet.createRow(rowNum); Element elementRow = rows.get(rowNum); Elements cells = elementRow.select("td"); if (cells.size() == 0) { cells = elementRow.select("th"); } int cellSize = cells.size(); for (int cellNum = 0; cellNum < cellSize; cellNum++) { Cell sheetRowCell = sheetRow.createCell(cellNum); sheetRowCell.setCellValue(cells.get(cellNum).text()); } } } /** * 获取优先级高的字段值 * * @param sheetRow 行 * @param priorityPosition 字段位置 * @param basePosition 优先级搞的字段位置 * @return 优先级高的字段值 */ public static String getPriorityFieldValue(Row sheetRow, Integer priorityPosition, Integer basePosition) { boolean hasPriorityValue = priorityPosition != null && sheetRow.getCell(priorityPosition) != null && StrUtil.isNotBlank(ExcelUtil.getCellValue(sheetRow.getCell(priorityPosition))); if (hasPriorityValue) { return ExcelUtil.getCellValue(sheetRow.getCell(priorityPosition)); } return basePosition != null && sheetRow.getCell(basePosition) != null ? ExcelUtil.getCellValue(sheetRow.getCell(basePosition)) : null; } /** * 去掉逗号 * * @param numberData 数字型字符串 * @return 无逗号的数字型字符串 */ public static String numberDataStripCommas(String numberData) { if (StrUtil.isBlank(numberData)) { return null; } // pdf解析到的值带有",",比如:"10,656,097.37" String data = numberData.replaceAll(",", ""); if (!StringUtil.isNumeric(data)) { return null; } return data; } public static XSSFWorkbook getHSSFWorkbook(String sheetName, List title, Map>> valueMap, XSSFWorkbook wb) { // 第一步，创建一个HSSFWorkbook，对应一个Excel文件 if (wb == null) { wb = new XSSFWorkbook(); } try { // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet XSSFSheet sheet = wb.createSheet(sheetName); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 XSSFRow row = sheet.createRow(0); // 第四步，创建单元格，并设置值表头 设置表头居中 XSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setWrapText(true); sheet.setColumnWidth(0, 10000); sheet.setColumnWidth(1, 10000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 8000); sheet.setColumnWidth(5, 5000); //声明列对象 XSSFCell cell = null; //创建标题 for (int i = 0; i < title.size(); i++) { cell = row.createCell(i); cell.setCellValue(title.get(i)); cell.setCellStyle(style); } int count = 65500; List> values = valueMap.get(sheetName); //创建内容 for (int i = 0; i < values.size(); i++) { row = sheet.createRow(i + 1); for (int j = 0; j < values.get(i).size(); j++) { //将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(values.get(i).get(j)); } if(i > count){ break; } } } catch (Exception e) { logger.error(e.getMessage(), e); } return wb; } public static Sheet getFirstSheet(String filePath) { if (StrUtil.isBlank(filePath)) { return null; } try { File file = new File(filePath); return ExcelUtil.getSheet(file, 0); } catch (Exception e) { logger.error("获取文件的sheet错误 -> 文件路径:{},堆栈信息为：{}", filePath, ExceptionUtil.stacktraceToString(e)); } return null; } public static String pdfConvertToExcel(String filePath, String excelFilePath) { File savefile = new File(excelFilePath); if (!savefile.exists()) { if (!savefile.getParentFile().exists()) { savefile.getParentFile().mkdirs(); savefile.getParentFile().setExecutable(true); } } try (OutputStream outputStream = Files.newOutputStream(Paths.get(excelFilePath))) { PDDocument document = Loader.loadPDF(new File(filePath)); PageIterator extract = new ObjectExtractor(document).extract(); Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("Sheet1"); int preEndRowIndex =0; while (extract.hasNext()) { Page next = extract.next(); List tableList = new SpreadsheetExtractionAlgorithm().extract(next); for (technology.tabula.Table table : tableList) { List> rows = table.getRows(); int rowLength = rows.size(); for (int rowNum = 0; rowNum < rowLength; rowNum++) { Row sheetRow = sheet.createRow(rowNum + preEndRowIndex); List textContainerList = rows.get(rowNum); for (int cellNum = 0; cellNum < textContainerList.size(); cellNum++) { Cell cell = sheetRow.createCell(cellNum); RectangularTextContainer textContainer = textContainerList.get(cellNum); if (textContainer != null) { cell.setCellValue(textContainer.getText()); } } } preEndRowIndex = preEndRowIndex + rowLength - 1 < 0 ? 0 : preEndRowIndex + rowLength - 1; } } // 将Excel工作簿写入输出流 workbook.write(outputStream); } catch (Exception e) { logger.error("解析邮件pdf附件报错 -> 堆栈信息:{}", ExceptionUtil.stacktraceToString(e)); return null; } return excelFilePath; } public static String contentConvertToExcel(String emailContent, String excelFilePath) { Elements rows; try { Document doc = Jsoup.parse(emailContent); Element table = doc.select("table").first(); rows = table.select("tr"); } catch (Exception e) { return null; } File saveFile = new File(excelFilePath); if (!saveFile.exists()) { if (!saveFile.getParentFile().exists()) { saveFile.getParentFile().mkdirs(); saveFile.getParentFile().setExecutable(true); } } try (OutputStream outputStream = new FileOutputStream(saveFile)) { // 创建一个新的Excel工作簿 Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("Sheet1"); ExcelUtil.writeDataToSheet(sheet, rows); // 将Excel工作簿写入输出流 workbook.write(outputStream); } catch (Exception e) { logger.error("邮件正文转换成excel报错 -> 堆栈信息:{}", ExceptionUtil.stacktraceToString(e)); return null; } return excelFilePath; } /** * 通过表头所在位置判断是行数据还是列数据 * * @param fieldPositionMap excel中表头所在的位置 * @return 行方向-1,,列方向-2 */ public static Integer detectDataDirection(Map> fieldPositionMap) { long count = fieldPositionMap.values().stream().map(Pair::getValue).distinct().count(); return count == 1 ? EmailDataDirectionConst.COLUMN_DIRECTION_TYPE : EmailDataDirectionConst.ROW_DIRECTION_TYPE; } // Excel 的起始日期是 1900 年 1 月 1 日 private static final LocalDate EXCEL_EPOCH_DATE = LocalDate.of(1900, 1, 1); public static String convertExcelDateToString(String dateNum) { LocalDate startDate = LocalDate.of(1900, 1, 1); LocalDate localDate = startDate.plusDays(Math.round(Double.valueOf(dateNum) - 2)); DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); return localDate.format(formatter); } public static XSSFWorkbook getProductXSSFWorkbook(List productDataVOList) { // 第一步，创建一个HSSFWorkbook，对应一个Excel文件 XSSFWorkbook wb = new XSSFWorkbook(); try { createProductInfoSheet(wb,productDataVOList); createProductContractSheet(wb,productDataVOList); createProductDetailSheet(wb,productDataVOList); createInvestmentManagerSheet(wb,productDataVOList); }catch (Exception e) { logger.error(e.getMessage(),e); } return wb; } private static void createInvestmentManagerSheet(XSSFWorkbook wb, List productDataVOList) { try{ XSSFSheet sheet = wb.createSheet("投资经理信息"); List title = new ArrayList<>(); title.add("编号"); title.add("中基协基金编号"); title.add("投资经理任职起始时间"); title.add("投资经理任职结束时间"); title.add("投资经理"); //创建第一行 XSSFRow row1 = sheet.createRow(0); XSSFRow row2 = sheet.createRow(1); for (int i = 0; i < title.size(); i++) { row1.createCell(i); row2.createCell(i); } XSSFCell firstCcell = row1.createCell(0); firstCcell.setCellValue("私募管理人数据推送信息填写（投资经理情况）"); XSSFCellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setFontName("微软");//设置字体 font.setBold(true);//加粗 font.setFontHeightInPoints((short) 12);//设置字体大小 font.setBold(true); style.setFont(font);//设置单元格字体 style.setAlignment(HorizontalAlignment.LEFT); style.setWrapText(true); firstCcell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(0,1,0,title.size())); XSSFRow row = sheet.createRow(2); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 5000); sheet.setColumnWidth(2, 8000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 10000); //声明列对象 XSSFCell cell = null; //创建标题 for (int i = 0; i < title.size(); i++) { cell = row.createCell(i); cell.setCellValue(title.get(i)); cell.setCellStyle(style); } int count = 65500; List> values = new ArrayList<>(); for (ProductDataVO productDataVO : productDataVOList) { List investmentManagerVOList = productDataVO.getInvestmentManagerVOList(); for (InvestmentManagerVO investmentManagerVO : investmentManagerVOList) { List valueList = new ArrayList<>(); valueList.add(String.valueOf(investmentManagerVO.getId())); valueList.add(investmentManagerVO.getRegisterNumber()); valueList.add(investmentManagerVO.getStartDate()); valueList.add(investmentManagerVO.getEndDate()); valueList.add(investmentManagerVO.getManagerName()); values.add(valueList); } } //创建内容 for (int i = 0; i < values.size(); i++) { row = sheet.createRow(i + 3); for (int j = 0; j < values.get(i).size(); j++) { //将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(values.get(i).get(j)); } if(i > count){ break; } } } catch (Exception e) { logger.error(e.getMessage(), e); } } private static void createProductContractSheet(XSSFWorkbook wb, List productDataVOList) { try{ XSSFSheet sheet = wb.createSheet("产品合同信息"); List title = new ArrayList<>(); title.add("编号"); title.add("中基协基金编号"); title.add("基金产品全名"); title.add("基金合同"); title.add("投资范围"); title.add("投资限制"); title.add("投资策略"); title.add("投资方式"); title.add("业绩比较基准"); title.add("业绩报酬计提方式"); title.add("备注"); //创建第一行 XSSFRow row1 = sheet.createRow(0); XSSFRow row2 = sheet.createRow(1); for (int i = 0; i < title.size(); i++) { row1.createCell(i); row2.createCell(i); } XSSFCell firstCcell = row1.createCell(0); firstCcell.setCellValue("私募管理人数据推送信息填写（其他信息）"); XSSFCellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setFontName("微软");//设置字体 font.setBold(true);//加粗 font.setFontHeightInPoints((short) 12);//设置字体大小 font.setBold(true); style.setFont(font);//设置单元格字体 style.setAlignment(HorizontalAlignment.LEFT); style.setWrapText(true); firstCcell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(0,1,0,title.size())); XSSFRow row = sheet.createRow(2); // 第四步，创建单元格，并设置值表头 设置表头居中 sheet.setColumnWidth(0, 2000); sheet.setColumnWidth(1, 5000); sheet.setColumnWidth(2, 10000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 8000); sheet.setColumnWidth(5, 5000); sheet.setColumnWidth(6, 5000); sheet.setColumnWidth(7, 5000); sheet.setColumnWidth(8, 5000); sheet.setColumnWidth(9, 8000); sheet.setColumnWidth(10, 5000); //声明列对象 XSSFCell cell = null; //创建标题 for (int i = 0; i < title.size(); i++) { cell = row.createCell(i); cell.setCellValue(title.get(i)); cell.setCellStyle(style); } int count = 65500; List> values = new ArrayList<>(); for (ProductDataVO productDataVO : productDataVOList) { ProductContractVO productContractVO = productDataVO.getProductContractVO(); List valueList = new ArrayList<>(); valueList.add(String.valueOf(productContractVO.getId())); valueList.add(productContractVO.getRegisterNumber()); valueList.add(productContractVO.getProductName()); valueList.add(productContractVO.getProductContract()); valueList.add(productContractVO.getInvestmentScope()); valueList.add(productContractVO.getInvestmentLimit()); valueList.add(productContractVO.getInvestmentStrategy()); valueList.add(productContractVO.getInvestmentMethod()); valueList.add(productContractVO.getPerformanceBasic()); valueList.add(productContractVO.getAccruedMethod()); valueList.add(productContractVO.getRemark()); values.add(valueList); } //创建内容 for (int i = 0; i < values.size(); i++) { row = sheet.createRow(i + 3); for (int j = 0; j < values.get(i).size(); j++) { //将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(values.get(i).get(j)); } if(i > count){ break; } } } catch (Exception e) { logger.error(e.getMessage(), e); } } private static void createProductDetailSheet(XSSFWorkbook wb, List productDataVOList) { try{ XSSFSheet sheet = wb.createSheet("产品其他信息"); List title = new ArrayList<>(); title.add("编号"); title.add("中基协基金编号"); title.add("基金产品全名"); title.add("成立分红拆分清算公告"); title.add("复权净值"); title.add("产品份额"); title.add("产品总资产"); title.add("估值表"); title.add("投资经理投资年限起始日（即最早对外募集资金并投资之日）"); title.add("投资经理从业经历"); title.add("投资经理在管数量"); title.add("公司总管理规模"); title.add("申购/赎回费率"); title.add("管理人费率"); title.add("托管费率"); title.add("外包费率"); //创建第一行 XSSFRow row1 = sheet.createRow(0); XSSFRow row2 = sheet.createRow(1); for (int i = 0; i < title.size(); i++) { row1.createCell(i); row2.createCell(i); } XSSFCell firstCcell = row1.createCell(0); firstCcell.setCellValue("私募管理人数据推送信息填写（其他信息）"); XSSFCellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setFontName("微软");//设置字体 font.setBold(true);//加粗 font.setFontHeightInPoints((short) 12);//设置字体大小 font.setBold(true); style.setFont(font);//设置单元格字体 style.setAlignment(HorizontalAlignment.LEFT); style.setWrapText(true); firstCcell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(0,1,0,title.size())); XSSFRow row = sheet.createRow(2); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 5000); sheet.setColumnWidth(2, 10000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 8000); sheet.setColumnWidth(5, 5000); sheet.setColumnWidth(6, 5000); sheet.setColumnWidth(7, 5000); sheet.setColumnWidth(8, 17000); sheet.setColumnWidth(9, 8000); sheet.setColumnWidth(10, 8000); sheet.setColumnWidth(11, 5000); sheet.setColumnWidth(12, 5000); sheet.setColumnWidth(13, 5000); sheet.setColumnWidth(14, 5000); sheet.setColumnWidth(15, 5000); //声明列对象 XSSFCell cell = null; //创建标题 for (int i = 0; i < title.size(); i++) { cell = row.createCell(i); cell.setCellValue(title.get(i)); cell.setCellStyle(style); } int count = 65500; List> values = new ArrayList<>(); for (ProductDataVO productDataVO : productDataVOList) { ProductDerivativeVO productDerivativeVO = productDataVO.getProductDerivativeVO(); List valueList = new ArrayList<>(); valueList.add(String.valueOf(productDerivativeVO.getId())); valueList.add(productDerivativeVO.getRegisterNumber()); valueList.add(productDerivativeVO.getProductName()); valueList.add(productDerivativeVO.getDistributeReport()); valueList.add(productDerivativeVO.getCumulativeNav()); valueList.add(productDerivativeVO.getProductShare()); valueList.add(productDerivativeVO.getProductAsset()); valueList.add(productDerivativeVO.getProductValuation()); valueList.add(productDerivativeVO.getStartDate()); valueList.add(productDerivativeVO.getInvestmentManagerDesc()); valueList.add(productDerivativeVO.getProductCount()); valueList.add(productDerivativeVO.getManageAsset()); valueList.add(productDerivativeVO.getFeeNote()); valueList.add(productDerivativeVO.getManagementfeeTrust()); valueList.add(productDerivativeVO.getManagementfeeBank()); valueList.add(productDerivativeVO.getOutsourceFee()); values.add(valueList); } //创建内容 for (int i = 0; i < values.size(); i++) { row = sheet.createRow(i + 3); for (int j = 0; j < values.get(i).size(); j++) { //将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(values.get(i).get(j)); } if(i > count){ break; } } } catch (Exception e) { logger.error(e.getMessage(), e); } } private static void createProductInfoSheet(XSSFWorkbook wb, List productDataVOList) { try{ XSSFSheet sheet = wb.createSheet("产品基本信息"); List title = new ArrayList<>(); title.add("编号"); title.add("中基协基金编号"); title.add("基金产品全名"); title.add("成立日期"); title.add("清盘日期"); title.add("母子基金标签"); title.add("是否结构化产品"); title.add("基金一级分类"); title.add("基金二级分类"); title.add("基金三级分类"); title.add("投资策略变更情况说明"); title.add("现任投资经理"); title.add("任职起始日期"); title.add("净值数据提供频率"); title.add("基金资产净值提供频率"); title.add("定期报告"); title.add("备注"); //创建第一行 XSSFRow row1 = sheet.createRow(0); XSSFRow row2 = sheet.createRow(1); for (int i = 0; i < title.size(); i++) { row1.createCell(i); row2.createCell(i); } XSSFCell firstCcell = row1.createCell(0); firstCcell.setCellValue("私募管理人数据推送信息填写（产品基本信息）"); XSSFCellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setFontName("微软");//设置字体 font.setBold(true);//加粗 font.setFontHeightInPoints((short) 12);//设置字体大小 font.setBold(true); style.setFont(font);//设置单元格字体 style.setAlignment(HorizontalAlignment.LEFT); style.setWrapText(true); firstCcell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(0,1,0,title.size())); XSSFRow row = sheet.createRow(2); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 10000); sheet.setColumnWidth(2, 10000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 8000); sheet.setColumnWidth(5, 5000); sheet.setColumnWidth(6, 10000); sheet.setColumnWidth(7, 5000); sheet.setColumnWidth(8, 5000); sheet.setColumnWidth(9, 5000); sheet.setColumnWidth(10, 15000); sheet.setColumnWidth(11, 5000); sheet.setColumnWidth(12, 5000); sheet.setColumnWidth(13, 10000); sheet.setColumnWidth(14, 10000); sheet.setColumnWidth(15, 5000); sheet.setColumnWidth(16, 5000); //声明列对象 XSSFCell cell = null; //创建标题 for (int i = 0; i < title.size(); i++) { cell = row.createCell(i); cell.setCellValue(title.get(i)); cell.setCellStyle(style); } int count = 65500; List> values = new ArrayList<>(); for (ProductDataVO productDataVO : productDataVOList) { ProductInformationVO productInformationVO = productDataVO.getProductInformationVO(); List valueList = new ArrayList<>(); valueList.add(String.valueOf(productInformationVO.getId())); valueList.add(productInformationVO.getRegisterNumber()); valueList.add(productInformationVO.getProductName()); valueList.add(productInformationVO.getInceptionDate()); valueList.add(productInformationVO.getLiquidateDate()); valueList.add(productInformationVO.getMsLabel()); valueList.add(productInformationVO.getIsStruct()); valueList.add(productInformationVO.getFirstStrategy()); valueList.add(productInformationVO.getSecondStrategy()); valueList.add(productInformationVO.getThirdStrategy()); valueList.add(productInformationVO.getInvestmentStrategyDesc()); valueList.add(productInformationVO.getInvestmentManager()); valueList.add(productInformationVO.getStartDate()); valueList.add(productInformationVO.getNavFrequency()); valueList.add(productInformationVO.getAssetFrequency()); valueList.add(productInformationVO.getReportFrequency()); valueList.add(productInformationVO.getRemark()); values.add(valueList); } //创建内容 for (int i = 0; i < values.size(); i++) { row = sheet.createRow(i + 3); for (int j = 0; j < values.get(i).size(); j++) { //将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(values.get(i).get(j)); } if(i > count){ break; } } } catch (Exception e) { logger.error(e.getMessage(), e); } } public static boolean isImage(String fileName) { return StrUtil.isNotBlank(fileName) && (fileName.endsWith("jpg") || fileName.endsWith("JPG") || fileName.endsWith("PNG") || fileName.endsWith("png")); } }