HSSFWorkbook And SXSSFWorkbook样式设置

本文详细介绍了如何使用HSSFWorkbook和SXSSFWorkbook在Excel中设置样式,包括背景色、边框、字体、居中对齐及自动换行等,并展示了如何合并单元格及应用常见公式。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

直插正题:

HSSFWorkbook  创建样式:

	HSSFCellStyle style = workbook.createCellStyle();
    一、设置背景色:
	cellStyle.setFillForegroundColor((short) 10);// 设置背景色,颜色的索引还必须是 0x08 ~ 0x40 (8 ~ 64) 的数字    
	自定义颜色,如果不使用POI提供的默认颜色,就需要自定颜色索引:
	      HSSFPalette palette = workbook.getCustomPalette(); 
	      palette.setColorAtIndex((short) 9, (byte) (color.getRed()), (byte) (color.getGreen()), (byte) (color.getBlue()));
              设置颜色的索引只能是 8 ~ 64,在此之外的索引无效,也不会报错。以下三种方式都可以设置成功
       	      palette.setColorAtIndex((short)9, (byte) (0xff & 251), (byte) (0xff & 161), (byte) (0xff & 161));
  palette.setColorAtIndex((short)10, (byte) (0x66), (byte) (0xcd), (byte) (0xaa));
  palette.setColorAtIndex((short)11, (byte) (255), (byte) (165), (byte) (0));
	      然后使用颜色,如上例,可以用新的颜色索引,替换原有的颜色:
  style.setFillForegroundColor((short) 9)
;
	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //设置前景填充样式,SOLID_FOREGROUND纯色使用前景颜色填充
	      cellStyle.setFillForegroundColor(HSSFColor.DARK_RED.index);//前景填充色
	      cellStyle.setFillBackgroundColor(HSSFColor.AQUA.index);//设置背景色      
	颜色类型是在HSSFColor里面定义的:
                执行结果
HSSFColor.ROYAL_BLUE 
HSSFColor.TEAL 
HSSFColor.LIME 
HSSFColor.PALE_BLUE 
HSSFColor.AQUA 
HSSFColor.GREEN 
HSSFColor.TURQUOISE 
HSSFColor.DARK_BLUE 
HSSFColor.CORNFLOWER_BLUE 
HSSFColor.OLIVE_GREEN 
HSSFColor.WHITE 
HSSFColor.LIGHT_TURQUOISE 
HSSFColor.LEMON_CHIFFON 
HSSFColor.LIGHT_GREEN 
HSSFColor.BLUE 
HSSFColor.DARK_RED 
HSSFColor.CORAL 
HSSFColor.RED 
HSSFColor.LIGHT_YELLOW 
HSSFColor.SKY_BLUE 
HSSFColor.BROWN 
HSSFColor.SEA_GREEN 
HSSFColor.INDIGO 
HSSFColor.MAROON 
HSSFColor.GREY_80_PERCENT 
HSSFColor.GREY_25_PERCENT 
HSSFColor.DARK_GREEN 
HSSFColor.YELLOW 
HSSFColor.GOLD 
HSSFColor.GREY_40_PERCENT 
HSSFColor.DARK_TEAL 
HSSFColor.PINK 
HSSFColor.ORCHID 
HSSFColor.LIGHT_BLUE 
HSSFColor.LIGHT_CORNFLOWER_BLUE 
HSSFColor.BLACK 
HSSFColor.DARK_YELLOW 
HSSFColor.VIOLET 
HSSFColor.LAVENDER 
HSSFColor.ROSE 
HSSFColor.BLUE_GREY 
HSSFColor.LIGHT_ORANGE 
HSSFColor.ORANGE 
HSSFColor.GREY_50_PERCENT 
   二、设置边框:
	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
   三、设置居中:
	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
	cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
   四、设置字体: 
	HSSFFont font = workbook.createFont(); 
	font.setFontName("宋体");//设置字体
	font.setFontHeightInPoints((short) 20);//设置字体大小
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
   五、设置自动换行:
	cellStyle.setWrapText(true);//设置自动换行   
   六、合并单元格:
	Region region1 = new Region(0, (short) 0, 0, (short) 6);//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号
	sheet.addMergedRegion(region1); 
   七、公式:	
操作语法
添加多个数值= SUM(Loc1:Locn) or = SUM(n1,n2,)
计数= COUNT(Loc1:Locn) or = COUNT(n1,n2,)
两个数的幂= POWER(Loc1,Loc2) or = POWER(number, power)
多个数的最大值= MAX(Loc1:Locn) or = MAX(n1,n2,)
乘积= PRODUCT(Loc1:Locn) or = PRODUCT(n1,n2,)
阶乘= FACT(Locn) or = FACT(number)
绝对数字= ABS(Locn) or = ABS(number)
今天的日期=TODAY()
转换成小写= LOWER(Locn) or = LOWER(text)
平方根= SQRT(locn) or = SQRT(number)

SXSSFWorkbook的设置与HSSFWorkbook设置一样,只是改HSSFCellStyle为CellStyle和HSSFPalette 为Palatte。


最后需要查看API的道友,可查看此链接:http://www.yiibai.com/apache_poi/apache_poi_cells.html
java package com.sinosoft.system.util; import com.spire.doc.DocumentProperties; import io.swagger.annotations.ApiModelProperty; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.http.converter.HttpMessageNotWritableException; import javax.imageio.ImageIO; import javax.imageio.ImageReader; import javax.imageio.stream.ImageInputStream; import javax.servlet.http.HttpServletResponse; import java.awt.image.BufferedImage; import java.io.*; import java.lang.reflect.Field; import java.net.URLEncoder; import java.util.*; public class ExcelExporterUtil<T> { @ApiModelProperty(value = "数据") private final List<T> obj; @ApiModelProperty(value = "数据类") private final Class<T> clazz; @ApiModelProperty(value = "查询字段") private final List<String> selectFields; @ApiModelProperty(value = "文件名称") private final String fileName; @ApiModelProperty(value = "表格页名称") private final String sheetName; @ApiModelProperty(value = "Excel 工作簿") private Workbook workbook; @ApiModelProperty(value = "表格页") private Sheet sheet; @ApiModelProperty(value = "表头样式") private CellStyle headerStyle; @ApiModelProperty(value = "数据样式") private CellStyle dataStyle; @ApiModelProperty(value = "图片样式(居中对齐)") private CellStyle imageStyle; // 初始化构造方法 public ExcelExporterUtil(List<T> obj, Class<T> clazz, List<String> selectFields, String fileName) { this(obj, clazz, selectFields, fileName, "Sheet1"); } public ExcelExporterUtil(List<T> obj, Class<T> clazz, List<String> selectFields, String fileName, String sheetName) { this.obj = obj; this.clazz = clazz; // 处理 selectFields 为 null 的情况 this.selectFields = selectFields != null ? selectFields : new ArrayList<>(); // 处理 fileName 为 null 的情况,随机生成文件名 this.fileName = fileName != null ? fileName : "export_" + System.currentTimeMillis(); this.sheetName = sheetName; // 使用 SXSSFWorkbook 处理大量数据 this.workbook = new SXSSFWorkbook(100); this.sheet = workbook.createSheet(sheetName); initStyles(); } // 初始化样式 private void initStyles() { // 表头样式 headerStyle = workbook.createCellStyle(); Font headerFont = workbook.createFont(); headerFont.setBold(true); headerFont.setFontHeightInPoints((short) 12); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); // 数据样式 dataStyle = workbook.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.LEFT); dataStyle.setBorderBottom(BorderStyle.THIN); dataStyle.setBorderLeft(BorderStyle.THIN); dataStyle.setBorderRight(BorderStyle.THIN); dataStyle.setBorderTop(BorderStyle.THIN); // 图片样式(居中对齐) imageStyle = workbook.createCellStyle(); imageStyle.setAlignment(HorizontalAlignment.CENTER); imageStyle.setVerticalAlignment(VerticalAlignment.CENTER); imageStyle.setBorderBottom(BorderStyle.THIN); imageStyle.setBorderLeft(BorderStyle.THIN); imageStyle.setBorderRight(BorderStyle.THIN); imageStyle.setBorderTop(BorderStyle.THIN); } public void export(HttpServletResponse response) throws IOException { // 移除文件名中的扩展名(如果有) String fileNameWithoutExt = removeExtension(this.fileName); // 清理文件名,移除前后的非法字符(包括下划线) String cleanedFileName = cleanFileName(fileNameWithoutExt); // 添加正确的.xlsx扩展名 String fileNameWithExt = cleanedFileName + ".xlsx"; // 设置完整的响应头 response.reset(); response.setCharacterEncoding("UTF-8"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(fileNameWithExt, "UTF-8") + "\""); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Pragma", "no-cache"); response.setDateHeader("Expires", 0); try (OutputStream outputStream = response.getOutputStream()) { // 生成Excel内容 createHeader(); fillData(); autoSizeColumns(); // 写入前强制刷新区 sheet.getWorkbook().getCreationHelper().createFormulaEvaluator().evaluateAll(); // 设置安全属性 workbook.setForceFormulaRecalculation(false); // 防止外部链接 // 写入Excel数据 workbook.write(outputStream); outputStream.flush(); } catch (Exception e) { // 记录错误日志 System.err.println("导出Excel失败: " + e.getMessage()); e.printStackTrace(); // 如果发生错误,尝试重置响应 try { response.reset(); response.setContentType("text/plain; charset=UTF-8"); response.getWriter().write("导出失败: " + e.getMessage()); } catch (IOException ex) { ex.printStackTrace(); } } finally { if (workbook instanceof SXSSFWorkbook) { ((SXSSFWorkbook) workbook).dispose(); } try { workbook.close(); } catch (IOException e) { e.printStackTrace(); } } } // 清理文件名,移除前后的非法字符(包括下划线、空格等) private String cleanFileName(String originalFileName) { if (originalFileName == null) { return "export_" + System.currentTimeMillis(); } // 移除前后的非法字符(正则表达式匹配所有非法字符,包括下划线) String cleaned = originalFileName.trim(); cleaned = cleaned.replaceAll("^[^a-zA-Z0-9\\u4e00-\\u9fa5]+", ""); // 移除开头的非法字符 cleaned = cleaned.replaceAll("[^a-zA-Z0-9\\u4e00-\\u9fa5]+$", ""); // 移除结尾的非法字符 // 替换中间的多个连续下划线为单个下划线 cleaned = cleaned.replaceAll("_+", "_"); // 确保文件名不以下划线开头或结尾 cleaned = cleaned.replaceAll("^_+|_+$", ""); // 如果清理后文件名为空,生成一个默认文件名 if (cleaned.isEmpty()) { return "export_" + System.currentTimeMillis(); } return cleaned; } // 移除文件名中的扩展名 private String removeExtension(String fileName) { if (fileName == null) { return null; } int dotIndex = fileName.lastIndexOf('.'); if (dotIndex > 0) { return fileName.substring(0, dotIndex); } return fileName; } // 获取需要导出的字段 private List<Field> getExportFields() { List<Field> fields = new ArrayList<>(); // 获取类及其父类的所有字段 Class<?> currentClass = clazz; while (currentClass != null) { fields.addAll(Arrays.asList(currentClass.getDeclaredFields())); currentClass = currentClass.getSuperclass(); } // 过滤不需要导出的字段(可根据需要添加更多过滤条件) fields.removeIf(field -> // 过滤静态字段 java.lang.reflect.Modifier.isStatic(field.getModifiers()) || // 过滤 transient 字段 java.lang.reflect.Modifier.isTransient(field.getModifiers()) ); return fields; } private List<T> getData() { if (obj == null || obj.isEmpty()) { throw new HttpMessageNotWritableException("列表为空"); } return obj; } // 创建表头 private void createHeader() { Row headerRow = sheet.createRow(0); List<Field> fields = getExportFields(); for (int i = 0; i < selectFields.size(); i++) { for (Field field : fields) { // 获取字段名 String columnName = field.getName(); if (Objects.equals(columnName.trim(), selectFields.get(i).trim())) { // 如果有 ApiModelProperty 注解,使用注解中的 value 作为列名 if (field.isAnnotationPresent(io.swagger.annotations.ApiModelProperty.class)) { columnName = field.getAnnotation(io.swagger.annotations.ApiModelProperty.class).value(); } Cell cell = headerRow.createCell(i); cell.setCellValue(columnName); cell.setCellStyle(headerStyle); } } } } // 填充数据 private void fillData() { List<Field> fields = getExportFields(); List<T> data = getData(); for (int i = 0; i < data.size(); i++) { Row dataRow = sheet.createRow(i + 1); T item = data.get(i); for (int k = 0; k < selectFields.size(); k++) { for (Field field : fields) { if (Objects.equals(field.getName().trim(), selectFields.get(k).trim())) { field.setAccessible(true); Cell cell = dataRow.createCell(k); try { Object value = field.get(item); if (value != null) { if (value instanceof byte[]) { // 调用修复的图片处理方法 handleImageCell(cell, (byte[]) value, i + 1, k); } else if (value instanceof String) { cell.setCellValue((String) value); cell.setCellStyle(dataStyle); } else if (value instanceof Integer) { cell.setCellValue((Integer) value); cell.setCellStyle(dataStyle); } else if (value instanceof Long) { cell.setCellValue((Long) value); cell.setCellStyle(dataStyle); } else if (value instanceof Double) { cell.setCellValue((Double) value); cell.setCellStyle(dataStyle); } else if (value instanceof Boolean) { cell.setCellValue((Boolean) value); cell.setCellStyle(dataStyle); } else if (value instanceof Date) { // 日期格式处理 cell.setCellValue((Date) value); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.cloneStyleFrom(dataStyle); CreationHelper createHelper = workbook.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd")); cell.setCellStyle(dateStyle); } else { cell.setCellValue(value.toString()); cell.setCellStyle(dataStyle); } } } catch (Exception e) { // 添加日志记录 System.err.println("数据填充失败: " + e.getMessage()); e.printStackTrace(); } } } } } } // 修复的图片处理方法 private void handleImageCell(Cell cell, byte[] imageBytes, int rowIndex, int colIndex) { try { // 1. 明确图片格式检测逻辑 int imageType = Workbook.PICTURE_TYPE_PNG; // 默认为PNG try (ByteArrayInputStream bis = new ByteArrayInputStream(imageBytes)) { ImageInputStream iis = ImageIO.createImageInputStream(bis); Iterator<ImageReader> readers = ImageIO.getImageReaders(iis); if (readers.hasNext()) { ImageReader reader = readers.next(); reader.setInput(iis); String formatName = reader.getFormatName().toLowerCase(); if (formatName.equals("jpeg") || formatName.equals("jpg")) { imageType = Workbook.PICTURE_TYPE_JPEG; } else if (formatName.equals("png")) { imageType = Workbook.PICTURE_TYPE_PNG; } } } // 2. 安全属性设置 CreationHelper helper = workbook.getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE); // 关键安全设置 // 2. 共享DrawingPatriarch对象(关键修复) Drawing<?> drawing = sheet.getDrawingPatriarch(); if (drawing == null) { drawing = sheet.createDrawingPatriarch(); } // 3. 添加图片 int pictureIdx = workbook.addPicture(imageBytes, imageType); // 4. 设置图片位置和大小 anchor.setCol1(colIndex); anchor.setRow1(rowIndex); anchor.setCol2(colIndex + 1); anchor.setRow2(rowIndex + 1); // 5. 创建图片并设置单元格样式 Picture pict = drawing.createPicture(anchor, pictureIdx); cell.setCellStyle(imageStyle); // 6. 调整行高适应图片 Row row = sheet.getRow(rowIndex); short rowHeight = row.getHeight(); // 图片高度大约等于20个POI单位/像素 if (rowHeight < 20 * 20) { row.setHeight((short) (20 * 20)); } } catch (Exception e) { // 添加日志记录 System.err.println("图片处理失败: " + e.getMessage()); e.printStackTrace(); cell.setCellValue("[Image Error]"); } } // 自适应列宽 private void autoSizeColumns() { for (int i = 0; i < selectFields.size(); i++) { sheet.autoSizeColumn(i); // 适当增加列宽,避免内容显示不全 sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 1000); } } } 导出文件名assets.xlsx ,实际导出后名称前后都带下划线,并且不能打开
06-24
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值