常用的POI格式设置以及使用POI生成EXCEL文件并转为MultipartFile类型上传到服务器

// 根据数据设置文档大小 Integer dataCount = purGoodsModelDtoMap.size() + 12; //创建一个Excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个工作表 HSSFSheet sheet = workbook.createSheet("表格"); // 设置列宽 Integer width = 1820; sheet.setColumnWidth(0, 600); sheet.setColumnWidth(1, width); PrintSetup ps = sheet.getPrintSetup(); //设置用纸 ps.setPaperSize(PrintSetup.A4_PAPERSIZE); // 调整为一页 sheet.setAutobreaks(true); // 创建表格大小 POIUtil.createAllRow(sheet, 0, dataCount, false); int rowIndex = 0; int colIndex = 0; // 创建行 HSSFRow row = sheet.getRow(rowIndex); row.setHeight((short) 700); // 创建单元格 HSSFCell cell = row.createCell(colIndex + 1); // 单元格样式 HSSFCellStyle cellStyle = workbook.createCellStyle(); Font baseFont = workbook.createFont(); baseFont.setFontName("华文中宋"); baseFont.setFontHeightInPoints((short)20); baseFont.setBold(true); // 设置字体 cellStyle.setFont(baseFont); // 水平方向居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 允许换行 cellStyle.setWrapText(true); cell.setCellStyle(cellStyle); // 设置文件标题单元格 POIUtil.setCellValue("aa\r\nbb", cell); // 单元格合并 sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 2, colIndex + 1, colIndex + 6)); // 左对齐\右对齐 华文宋体 11号 HSSFCellStyle cellStyleCommon = workbook.createCellStyle(); HSSFCellStyle cellStyleCommonRight = workbook.createCellStyle(); Font baseFontCommon = workbook.createFont(); baseFontCommon.setFontName("华文宋体"); baseFontCommon.setFontHeightInPoints((short)11); baseFontCommon.setBold(false); cellStyleCommon.setFont(baseFontCommon); cellStyleCommon.setAlignment(HorizontalAlignment.LEFT); cellStyleCommonRight.setFont(baseFontCommon); cellStyleCommonRight.setAlignment(HorizontalAlignment.RIGHT); // 华文宋体 12号，设置表格边框 HSSFCellStyle cellStyleTable = workbook.createCellStyle(); Font tableFontCommon = workbook.createFont(); tableFontCommon.setFontName("华文宋体"); tableFontCommon.setFontHeightInPoints((short)12); tableFontCommon.setBold(false); cellStyleTable.setFont(tableFontCommon); cellStyleTable.setAlignment(HorizontalAlignment.LEFT); cellStyleTable.setBorderBottom(BorderStyle.MEDIUM); cellStyleTable.setBorderLeft(BorderStyle.MEDIUM); cellStyleTable.setBorderTop(BorderStyle.MEDIUM); cellStyleTable.setBorderRight(BorderStyle.MEDIUM); // 文件名 String fileName = storeOut.getStoreId().toString() + ".xls"; // 生成文件 try { uploadFile(fileName, workbook, storeOut); } catch (IOException e) { log.info("上传附件失败 ->{}",e); }