| import com.alibaba.fastjson.JSON; |
| import com.alibaba.fastjson.JSONArray; |
| import org.apache.poi.ss.usermodel.BorderStyle; |
| 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.FillPatternType; |
| import org.apache.poi.ss.usermodel.HorizontalAlignment; |
| import org.apache.poi.ss.usermodel.IndexedColors; |
| import org.apache.poi.ss.usermodel.Sheet; |
| import org.apache.poi.ss.usermodel.VerticalAlignment; |
| import org.apache.poi.ss.util.CellRangeAddress; |
| import org.apache.poi.ss.util.CellRangeAddressList; |
| import org.apache.poi.ss.util.RegionUtil; |
| import org.apache.poi.xssf.usermodel.XSSFCell; |
| import org.apache.poi.xssf.usermodel.XSSFCellStyle; |
| import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint; |
| import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper; |
| import org.apache.poi.xssf.usermodel.XSSFRow; |
| import org.apache.poi.xssf.usermodel.XSSFSheet; |
| import org.apache.poi.xssf.usermodel.XSSFWorkbook; |
| |
| import java.lang.reflect.Field; |
| import java.util.ArrayList; |
| import java.util.HashMap; |
| import java.util.List; |
| import java.util.Map; |
| import java.util.Random; |
| |
| |
| |
| |
| |
| |
| |
| public class GenerateExcelTemplateUtils { |
| |
| |
| |
| public static int VALIDATION_ROW = 1000; |
| |
| |
| |
| public static int WIDTH = 30; |
| |
| |
| |
| public static int HEIGHT = 30; |
| |
| |
| |
| |
| public static String SHEET_NAME = "sheet"; |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| public static XSSFWorkbook complexHeader(String json) { |
| XSSFWorkbook workbook = new XSSFWorkbook(); |
| List<String> parse = JSON.parseArray(json, String.class); |
| List<List<TemplateObj>> list = new ArrayList<>(); |
| for (String obj : parse) { |
| list.add(JSON.parseArray(obj, TemplateObj.class)); |
| } |
| XSSFSheet sheet = workbook.createSheet(SHEET_NAME); |
| XSSFCellStyle style = workbook.createCellStyle(); |
| style.setAlignment(HorizontalAlignment.CENTER); |
| style.setVerticalAlignment(VerticalAlignment.CENTER); |
| style.setBorderBottom(BorderStyle.THIN); |
| style.setBorderLeft(BorderStyle.THIN); |
| style.setBorderTop(BorderStyle.THIN); |
| style.setBorderRight(BorderStyle.THIN); |
| Integer colspan = 0; |
| Integer rowspan = 0; |
| Integer maxColspan = 0; |
| for (int y = 0; y < list.size(); y++) { |
| XSSFRow excelRow = sheet.createRow(rowspan); |
| colspan = 0; |
| for (int x = 0; x < maxColspan; x++) { |
| if (isMergedRegion(sheet, rowspan, colspan)) { |
| colspan++; |
| continue; |
| } |
| } |
| for (int x = 0; x < list.get(y).size(); x++) { |
| if (isMergedRegion(sheet, rowspan, colspan)) { |
| colspan++; |
| } |
| if (null != list.get(y).get(x).getHeight()) { |
| excelRow.setHeight((short) (list.get(y).get(x).getHeight() * HEIGHT)); |
| } |
| XSSFCell cell = excelRow.createCell(colspan); |
| cell.setCellStyle(style); |
| cell.setCellValue(list.get(y).get(x).getTitle()); |
| if (list.get(y).get(x).getRowspan() > 1 || list.get(y).get(x).getColspan() > 1) { |
| if (list.get(y).get(x).getRowspan() > 1) { |
| Integer lastRow = list.get(y).get(x).getRowspan() + rowspan; |
| CellRangeAddress region = new CellRangeAddress(rowspan, lastRow - 1, colspan, colspan); |
| if (null != list.get(y).get(x).getWidth()) { |
| sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH); |
| } |
| sheet.addMergedRegion(region); |
| if (list.get(y).get(x).getValidation()) { |
| setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList()); |
| } |
| colspan++; |
| } |
| if (list.get(y).get(x).getColspan() > 1) { |
| Integer lastCol = list.get(y).get(x).getColspan() + colspan; |
| CellRangeAddress region = new CellRangeAddress(rowspan, rowspan, colspan, lastCol - 1); |
| if (null != list.get(y).get(x).getWidth()) { |
| sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH); |
| } |
| sheet.addMergedRegion(region); |
| if (list.get(y).get(x).getValidation()) { |
| setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList()); |
| } |
| colspan = lastCol; |
| } |
| } else { |
| if (list.get(y).get(x).getValidation()) { |
| setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList()); |
| } |
| if (null != list.get(y).get(x).getWidth()) { |
| sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH); |
| } |
| colspan++; |
| } |
| } |
| rowspan++; |
| maxColspan = maxColspan > colspan ? maxColspan : colspan; |
| } |
| for (int y = list.size(); y < 20 - list.size(); y++) { |
| XSSFRow excelRow = sheet.createRow(y); |
| for (int x = 0; x < maxColspan; x++) { |
| XSSFCell cell = excelRow.createCell(x); |
| cell.setCellStyle(style); |
| } |
| } |
| return workbook; |
| } |
| |
| |
| |
| |
| |
| |
| private static Map<String,Object> complexHeaderCarryData(String json) { |
| Map<String,Object> map = new HashMap<>(); |
| Map<String,Integer> index = new HashMap<>(); |
| XSSFWorkbook workbook = new XSSFWorkbook(); |
| List<String> parse = JSON.parseArray(json, String.class); |
| List<List<TemplateObj>> list = new ArrayList<>(); |
| for (String obj : parse) { |
| list.add(JSON.parseArray(obj, TemplateObj.class)); |
| } |
| XSSFSheet sheet = workbook.createSheet(SHEET_NAME); |
| XSSFCellStyle style = workbook.createCellStyle(); |
| style.setAlignment(HorizontalAlignment.CENTER); |
| style.setVerticalAlignment(VerticalAlignment.CENTER); |
| style.setBorderBottom(BorderStyle.THIN); |
| style.setBorderLeft(BorderStyle.THIN); |
| style.setBorderTop(BorderStyle.THIN); |
| style.setBorderRight(BorderStyle.THIN); |
| style.setFillPattern(FillPatternType.SOLID_FOREGROUND); |
| style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); |
| Integer colspan = 0; |
| Integer rowspan = 0; |
| Integer maxColspan = 0; |
| for (int y = 0; y < list.size(); y++) { |
| XSSFRow excelRow = sheet.createRow(rowspan); |
| colspan = 0; |
| for (int x = 0; x < maxColspan; x++) { |
| if (isMergedRegion(sheet, rowspan, colspan)) { |
| colspan++; |
| continue; |
| } |
| } |
| for (int x = 0; x < list.get(y).size(); x++) { |
| if (isMergedRegion(sheet, rowspan, colspan)) { |
| colspan++; |
| } |
| if (null != list.get(y).get(x).getHeight()) { |
| excelRow.setHeight((short) (list.get(y).get(x).getHeight() * HEIGHT)); |
| } |
| XSSFCell cell = excelRow.createCell(colspan); |
| cell.setCellStyle(style); |
| cell.setCellValue(list.get(y).get(x).getTitle()); |
| if (null != list.get(y).get(x).getField() && !"".equals(list.get(y).get(x).getField())) { |
| index.put(list.get(y).get(x).getField(), colspan); |
| } |
| if (list.get(y).get(x).getRowspan() > 1 || list.get(y).get(x).getColspan() > 1) { |
| if (list.get(y).get(x).getRowspan() > 1) { |
| Integer lastRow = list.get(y).get(x).getRowspan() + rowspan; |
| CellRangeAddress region = new CellRangeAddress(rowspan, lastRow - 1, colspan, colspan); |
| if (null != list.get(y).get(x).getWidth()) { |
| sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH); |
| } |
| sheet.addMergedRegion(region); |
| if (list.get(y).get(x).getValidation()) { |
| setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList()); |
| } |
| colspan++; |
| } |
| if (list.get(y).get(x).getColspan() > 1) { |
| Integer lastCol = list.get(y).get(x).getColspan() + colspan; |
| CellRangeAddress region = new CellRangeAddress(rowspan, rowspan, colspan, lastCol - 1); |
| if (null != list.get(y).get(x).getWidth()) { |
| sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH); |
| } |
| sheet.addMergedRegion(region); |
| if (list.get(y).get(x).getValidation()) { |
| setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList()); |
| } |
| colspan = lastCol; |
| } |
| } else { |
| if (list.get(y).get(x).getValidation()) { |
| setExcelValidation(workbook, rowspan, VALIDATION_ROW, colspan, colspan, list.get(y).get(x).getValidationList()); |
| } |
| if (null != list.get(y).get(x).getWidth()) { |
| sheet.setColumnWidth(colspan, list.get(y).get(x).getWidth() * WIDTH); |
| } |
| colspan++; |
| } |
| } |
| rowspan++; |
| maxColspan = maxColspan > colspan ? maxColspan : colspan; |
| } |
| map.put("workbook",workbook); |
| map.put("index",index); |
| return map; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| private static boolean isMergedRegion(Sheet sheet, int row, int column) { |
| int sheetMergeCount = sheet.getNumMergedRegions(); |
| for (int i = 0; i < sheetMergeCount; i++) { |
| CellRangeAddress range = sheet.getMergedRegion(i); |
| int firstColumn = range.getFirstColumn(); |
| int lastColumn = range.getLastColumn(); |
| int firstRow = range.getFirstRow(); |
| int lastRow = range.getLastRow(); |
| if (row >= firstRow && row <= lastRow) { |
| if (column >= firstColumn && column <= lastColumn) { |
| return true; |
| } |
| } |
| } |
| return false; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| public static void setExcelValidation(XSSFWorkbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, List<String> date) { |
| XSSFSheet sheet = workbook.getSheet(SHEET_NAME); |
| String sheetName = getRandomString(8); |
| XSSFSheet validationSheet = workbook.createSheet(sheetName); |
| int sheetTotal = workbook.getNumberOfSheets(); |
| int index = 0; |
| String strFormula = sheetName + "!$A$1:$A$" + date.size(); |
| for (int i = 0; i < date.size(); i++) { |
| XSSFRow xssfRow = validationSheet.createRow(i); |
| XSSFCell cell = xssfRow.createCell(index); |
| cell.setCellValue(date.get(i)); |
| } |
| XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula); |
| CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); |
| DataValidationHelper help = new XSSFDataValidationHelper(sheet); |
| DataValidation validation = help.createValidation(constraint, regions); |
| sheet.addValidationData(validation); |
| workbook.setSheetHidden(sheetTotal - 1, true); |
| } |
| |
| |
| public static class TemplateObj { |
| |
| private String field; |
| |
| private String title; |
| |
| private Integer width; |
| |
| private Integer height; |
| |
| private Integer colspan = 1; |
| |
| private Integer rowspan = 1; |
| |
| private Integer align = 1; |
| |
| private Integer fontSize = 10; |
| |
| |
| private boolean validation = false; |
| |
| private List<String> validationList; |
| |
| public TemplateObj() { |
| |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| public TemplateObj(String field, String title, Integer width, Integer colspan, Integer rowspan) { |
| this.field = field; |
| this.title = title; |
| this.width = width; |
| this.colspan = colspan; |
| this.rowspan = rowspan; |
| } |
| |
| |
| |
| |
| |
| |
| |
| public TemplateObj(String field, String title, Integer colspan, Integer rowspan) { |
| this.field = field; |
| this.title = title; |
| this.colspan = colspan; |
| this.rowspan = rowspan; |
| } |
| |
| |
| |
| |
| |
| public TemplateObj(String field, String title) { |
| this.field = field; |
| this.title = title; |
| } |
| |
| |
| |
| |
| |
| |
| public TemplateObj(String field, String title, Integer width) { |
| this.field = field; |
| this.title = title; |
| this.width = width; |
| } |
| |
| |
| |
| |
| |
| |
| public TemplateObj(String title, Integer width, List<String> validationList) { |
| this.title = title; |
| this.width = width; |
| this.validation = true; |
| this.validationList = validationList; |
| } |
| |
| |
| |
| |
| |
| public TemplateObj(String title, List<String> validationList) { |
| this.title = title; |
| this.validation = true; |
| this.validationList = validationList; |
| } |
| |
| public boolean getValidation() { |
| return validation; |
| } |
| |
| public String getField() { |
| return field; |
| } |
| |
| public TemplateObj setField(String field) { |
| this.field = field; |
| return this; |
| } |
| |
| public String getTitle() { |
| return title; |
| } |
| |
| public TemplateObj setTitle(String title) { |
| this.title = title; |
| return this; |
| } |
| |
| public Integer getWidth() { |
| return width; |
| } |
| |
| public TemplateObj setWidth(Integer width) { |
| this.width = width; |
| return this; |
| } |
| |
| public Integer getColspan() { |
| return colspan; |
| } |
| |
| public TemplateObj setColspan(Integer colspan) { |
| this.colspan = colspan; |
| return this; |
| } |
| |
| public Integer getRowspan() { |
| return rowspan; |
| } |
| |
| public TemplateObj setRowspan(Integer rowspan) { |
| this.rowspan = rowspan; |
| return this; |
| } |
| |
| public boolean isValidation() { |
| return validation; |
| } |
| |
| public TemplateObj setValidation(boolean validation) { |
| this.validation = validation; |
| return this; |
| } |
| |
| public List<String> getValidationList() { |
| return validationList; |
| } |
| |
| public TemplateObj setValidationList(List<String> validationList) { |
| this.validationList = validationList; |
| return this; |
| } |
| |
| public Integer getHeight() { |
| return height; |
| } |
| |
| public TemplateObj setHeight(Integer height) { |
| this.height = height; |
| return this; |
| } |
| |
| public Integer getFontSize() { |
| return fontSize; |
| } |
| |
| public TemplateObj setFontSize(Integer fontSize) { |
| this.fontSize = fontSize; |
| return this; |
| } |
| |
| public Integer getAlign() { |
| return align; |
| } |
| |
| |
| |
| |
| |
| |
| |
| public TemplateObj setAlign(Integer align) { |
| this.align = align; |
| return this; |
| } |
| } |
| |
| |
| |
| |
| |
| |
| |
| public static String getRandomString(int length) { |
| String str = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"; |
| Random random = new Random(); |
| StringBuffer sb = new StringBuffer(); |
| for (int i = 0; i < length; i++) { |
| int number = random.nextInt(62); |
| sb.append(str.charAt(number)); |
| } |
| return sb.toString(); |
| } |
| |
| |
| |
| |
| |
| public static void setStyle(XSSFSheet sheet) { |
| List<CellRangeAddress> mergedRegions = sheet.getMergedRegions(); |
| for (CellRangeAddress cellRangeAddress : mergedRegions) { |
| RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet); |
| RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet); |
| RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet); |
| RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet); |
| } |
| } |
| |
| public static XSSFWorkbook complexHeaderCarryData(String tableJson,List<?> data) throws Exception { |
| Map<String, Object> map = complexHeaderCarryData(tableJson); |
| XSSFWorkbook workbook = (XSSFWorkbook) map.get("workbook"); |
| XSSFCellStyle style = workbook.createCellStyle(); |
| style.setAlignment(HorizontalAlignment.CENTER); |
| style.setVerticalAlignment(VerticalAlignment.CENTER); |
| style.setBorderBottom(BorderStyle.THIN); |
| style.setBorderLeft(BorderStyle.THIN); |
| style.setBorderTop(BorderStyle.THIN); |
| style.setBorderRight(BorderStyle.THIN); |
| Map<String, Integer> index = (Map<String, Integer>) map.get("index"); |
| XSSFSheet sheet = workbook.getSheet(SHEET_NAME); |
| int rowLength = sheet.getLastRowNum() + 1; |
| int styleRowLength = rowLength; |
| for (Object obj : data) { |
| XSSFRow row = sheet.createRow(rowLength); |
| Class<?> cls = obj.getClass(); |
| Field[] fields = cls.getDeclaredFields(); |
| for (Field field : fields) { |
| if (index.containsKey(field.getName())) { |
| field.setAccessible(true); |
| XSSFCell cell = row.createCell(index.get(field.getName())); |
| Object v = field.get(obj); |
| cell.setCellValue(null != v ? v.toString() : ""); |
| |
| } |
| } |
| rowLength++; |
| } |
| for (int y = 0; y < data.size(); y++) { |
| XSSFRow excelRow = sheet.getRow(styleRowLength); |
| if (null == excelRow) { |
| excelRow = sheet.createRow(styleRowLength); |
| } |
| excelRow.setHeight((short) (15 * HEIGHT)); |
| for (int x = 0; x < index.size(); x++) { |
| XSSFCell cell = excelRow.getCell(x); |
| if (null == cell) { |
| cell = excelRow.createCell(x); |
| } |
| cell.setCellStyle(style); |
| } |
| styleRowLength++; |
| } |
| setStyle(sheet); |
| return workbook; |
| } |
| } |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器