Java给Excel设置单元格格式

港控/mmm° 2024-03-22 23:37 106阅读 0赞

## maven 依赖 ##

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
    
    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.11.0</version>
    </dependency>
    
    
    
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-api</artifactId>
        <version>2.15.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-core</artifactId>
        <version>2.15.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-slf4j-impl</artifactId>
        <version>2.15.0</version>
    </dependency>

--------------------

## 字体样式 ##

public static Font demoFont(Workbook workbook) {
        
        Font font = workbook.createFont();
        font.setFontName("等线"); // 字体
        font.setFontHeightInPoints((short) 14);// 字号
        font.setBold(true);// 加粗
        font.setColor(IndexedColors.BLACK.getIndex());// 颜色
        font.setItalic(true);// 斜体
        font.setStrikeout(true);// 使用划线
        return font;
    }

--------------------

## 单元格样式 ##

public static CellStyle demoStyle(Workbook workbook) {
        
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(demoFont(workbook)); // 字体
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        cellStyle.setWrapText(true); // 自动换行
        cellStyle.setBorderTop(BorderStyle.THIN); // 单元格边框
        cellStyle.setBorderBottom(BorderStyle.THIN); // 单元格边框
        cellStyle.setBorderLeft(BorderStyle.THIN); // 单元格边框
        cellStyle.setBorderRight(BorderStyle.THIN); // 单元格边框
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//高度居中
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 实心填充
        cellStyle.setFillForegroundColor(new XSSFColor(new Color(181, 230, 181),new DefaultIndexedColorMap()));
        return cellStyle;
    }

--------------------

## 设置列宽 ##

sheet.autoSizeColumn(c); // 自适应
    sheet.setColumnWidth(c, 50 * 256);

--------------------

## 给Sheet页设置Style通俗处理 ##

public static void buildStyle(Workbook wb) {
        
        Sheet sheet;
        Row row;
        for (int s = 0; s < wb.getNumberOfSheets(); s++) {
        
            sheet = wb.getSheetAt(s);
            for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        
                row = sheet.getRow(r);
                for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
        
                    if (r == 0) {
         // 表头
                        row.getCell(c).setCellStyle(headerStyle(wb));
                        sheet.autoSizeColumn(c);
                    } else {
         // 数据
                        row.getCell(c).setCellStyle(cellsStyle(wb));
                        if (sheet.getColumnWidth(c) > 50 * 256) {
        
                            sheet.setColumnWidth(c, 50 * 256);
                        }
                    }
                }
            }
        }
    }

--------------------

### 参考 ###

**[POI 导出 Excel 并且根据内容设置列宽自适应][POI _ Excel]**

[POI _ Excel]: https://my.oschina.net/u/2331760/blog/3050616