Java POI excel设置单元格格式，自定义设置

太过爱你忘了你带给我的痛 2023-10-14 21:57 15阅读 0赞

#### 文章目录 ####

*  1、设置单元格格式：来源\_formats
 *  2、设置单元格格式：自定义格式
 *   *  2.1、自定义格式分析&源码分析
     *  2.2、自定义单元格格式，代码示例
 *  参考链接：

## 1、设置单元格格式：来源\_formats ##

更多数据类型从**formats**里面发现

private static final String[] _formats = new String[]{
        "General", "0", "0.00", "#,##0", "#,##0.00", "\"$\"#,##0_);(\"$\"#,##0)", "\"$\"#,##0_);[Red](\"$\"#,##0)", "\"$\"#,##0.00_);(\"$\"#,##0.00)", "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)", "0%", "0.00%", "0.00E+00", "# ?/?", "# ??/??", "m/d/yy", "d-mmm-yy", "d-mmm", "mmm-yy", "h:mm AM/PM", "h:mm:ss AM/PM", "h:mm", "h:mm:ss", "m/d/yy h:mm", "reserved-0x17", "reserved-0x18", "reserved-0x19", "reserved-0x1A", "reserved-0x1B", "reserved-0x1C", "reserved-0x1D", "reserved-0x1E", "reserved-0x1F", "reserved-0x20", "reserved-0x21", "reserved-0x22", "reserved-0x23", "reserved-0x24", "#,##0_);(#,##0)", "#,##0_);[Red](#,##0)", "#,##0.00_);(#,##0.00)", "#,##0.00_);[Red](#,##0.00)", "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)", "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)", "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)", "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)", "mm:ss", "[h]:mm:ss", "mm:ss.0", "##0.0E+0", "@"};

/**
         * 设置单元格数据类型
         */
        public static void setExcelCellDataFormat() throws IOException {
        
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("单元格数据类型");
    
            XSSFRow row1 = sheet.createRow(0);
            // 单元格样式
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            // 单元格数据格式
            XSSFDataFormat cellDataFormat = workbook.createDataFormat();
            // 来源：org.apache.poi.ss.usermodel.BuiltinFormats
            // private static final String[] _formats = new String[]{"General", "0", "0.00", "#,##0", "#,##0.00", "\"$\"#,##0_);(\"$\"#,##0)", "\"$\"#,##0_);[Red](\"$\"#,##0)", "\"$\"#,##0.00_);(\"$\"#,##0.00)", "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)", "0%", "0.00%", "0.00E+00", "# ?/?", "# ??/??", "m/d/yy", "d-mmm-yy", "d-mmm", "mmm-yy", "h:mm AM/PM", "h:mm:ss AM/PM", "h:mm", "h:mm:ss", "m/d/yy h:mm", "reserved-0x17", "reserved-0x18", "reserved-0x19", "reserved-0x1A", "reserved-0x1B", "reserved-0x1C", "reserved-0x1D", "reserved-0x1E", "reserved-0x1F", "reserved-0x20", "reserved-0x21", "reserved-0x22", "reserved-0x23", "reserved-0x24", "#,##0_);(#,##0)", "#,##0_);[Red](#,##0)", "#,##0.00_);(#,##0.00)", "#,##0.00_);[Red](#,##0.00)", "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)", "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)", "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)", "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)", "mm:ss", "[h]:mm:ss", "mm:ss.0", "##0.0E+0", "@"};
    
            // 单元格百分比：从_formats里面选择一个包含的单元格数据类型
            cellStyle.setDataFormat(cellDataFormat.getFormat("0.00%"));
            XSSFCell cell1 = row1.createCell(0);
            cell1.setCellStyle(cellStyle);
            // 需要注意一下，poi已经不建议使用setCellType设置数据类型了，可以直接通过重载的setCellValue判断当前单元格是什么数据类型
            // cell1.setCellType(CellType.BOOLEAN);
            cell1.setCellValue(0.06);
    
            FileOutputStream outputStream = new FileOutputStream("D:\\temp\\Excel单元格数据类型.xlsx");
            workbook.write(outputStream);
            outputStream.flush();
    
            workbook.close();
            outputStream.close();
        }

注意可能会出现不兼容的问题，但是不影响导出  
![QQ截图20230728152405.png][QQ_20230728152405.png]

## 2、设置单元格格式：自定义格式 ##

### 2.1、自定义格式分析&源码分析 ###

在**formats**数据格式不能完全支持的时候，如下图情况：  
![image.png][]  
可能会使用自定义的数据格式来在单元格展示数据。如下图，部分自定义数据格式示例：  
![image.png][image.png 1]  
![image.png][image.png 2]  
设置单元格格式源码分析：  
![image.png][image.png 3]  
下面开始源码部分  
![image.png][image.png 4]  
`BuiltinFormats.getBuiltinFormat(format)`执行如下图：  
![image.png][image.png 5]  
返回-1时会进行自定义数据格式设置：  
![image.png][image.png 6]  
`stylesSource.putNumberFormat(format)`执行如下图：  
![image.png][image.png 7]

### 2.2、自定义单元格格式，代码示例 ###

更多自定义数据格式在代码示例。

public static void setExcelCellDataFormat2() throws IOException {
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("单元格数据类型");
    
        XSSFRow row1 = sheet.createRow(0);
        // 单元格样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // 单元格数据格式
        XSSFDataFormat cellDataFormat = workbook.createDataFormat();
        // 人民币货币格式
        cellStyle.setDataFormat(cellDataFormat.getFormat("￥#,##0.00"));
        XSSFCell cell1 = row1.createCell(0);
        cell1.setCellStyle(cellStyle);
        cell1.setCellValue(0.06);
        sheet.setColumnWidth(0, 256 * 14 + 184);
    
        // 美元货币格式
        XSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setDataFormat(cellDataFormat.getFormat("$#,##0.00"));
        XSSFCell cell2 = row1.createCell(1);
        cell2.setCellStyle(cellStyle2);
        cell2.setCellValue(0.06);
        sheet.setColumnWidth(1, 256 * 14 + 184);
    
        // 添加文字描述的数据格式
        XSSFCellStyle cellStyle3 = workbook.createCellStyle();
        cellStyle3.setDataFormat(cellDataFormat.getFormat("占比#,##0.00%"));
        XSSFCell cell3 = row1.createCell(2);
        cell3.setCellStyle(cellStyle3);
        cell3.setCellValue(0.06);
        sheet.setColumnWidth(2, 256 * 14 + 184);
    
    
        // 带颜色的数据格式化，正数为绿色，负数为红色
        XSSFCellStyle cellStyle4 = workbook.createCellStyle();
        cellStyle4.setDataFormat(cellDataFormat.getFormat("提升[绿色]#,##0.00%;[红色]下降#,##0.00%"));
        XSSFCell cell4 = row1.createCell(3);
        cell4.setCellStyle(cellStyle4);
        cell4.setCellValue(-0.06);
        sheet.setColumnWidth(3, 256 * 14 + 184);
    
        // 带颜色的数据格式化，正数为绿色，负数为红色
        XSSFCellStyle cellStyle5 = workbook.createCellStyle();
        cellStyle5.setDataFormat(cellDataFormat.getFormat("提升[绿色]#,##0.00%;[红色]下降#,##0.00%"));
        XSSFCell cell5 = row1.createCell(4);
        cell5.setCellStyle(cellStyle5);
        cell5.setCellValue(0.06);
        sheet.setColumnWidth(4, 256 * 14 + 184);
    
    
        FileOutputStream outputStream = new FileOutputStream("D:\\temp\\Excel单元格数据类型2.xlsx");
        workbook.write(outputStream);
        outputStream.flush();
    
        workbook.close();
        outputStream.close();
    }

示例运行结果：  
![image.png][image.png 8]

## 参考链接： ##

[Java 中使用POI设置EXCEL单元格格式为文本、小数、百分比、货币、日期、科学计数法和中文大写、单元格边框等\_Julian.zhou的博客-CSDN博客][Java _POI_EXCEL_Julian.zhou_-CSDN]

[QQ_20230728152405.png]: https://img-blog.csdnimg.cn/img_convert/e66fb9d241e68fb813444855178b5605.png#averageHue=#2f2e2d&clientId=u01df8c18-6d9e-4&from=paste&height=479&id=ue52cf1e3&originHeight=646&originWidth=1626&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=205827&status=done&style=none&taskId=uc1336145-be02-42d3-b50a-45eeb87d073&title=&width=1204.4445295294677
[image.png]: https://img-blog.csdnimg.cn/img_convert/437fda7ebcef70272b6d6061cb79c4b6.png#averageHue=#f6f4f3&clientId=u39b0e149-6066-4&from=paste&height=406&id=uf2a3e0d4&originHeight=609&originWidth=564&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=103892&status=done&style=none&taskId=uff1d23a2-ba20-4183-a8c3-c3c1438ab36&title=&width=376
[image.png 1]: https://img-blog.csdnimg.cn/img_convert/b1e7fb664ee90bedd77f7710d90c2569.png#averageHue=#f7f1f0&clientId=u39b0e149-6066-4&from=paste&height=233&id=ue64bb591&originHeight=350&originWidth=382&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=57581&status=done&style=none&taskId=u03ff7b20-e072-4a76-8f49-65fa71d070e&title=&width=254.66666666666666
[image.png 2]: https://img-blog.csdnimg.cn/img_convert/605365f0c79f426c17b82c6ef102c7ff.png#averageHue=#f8f8f7&clientId=u39b0e149-6066-4&from=paste&height=221&id=uc9bd494e&originHeight=331&originWidth=372&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=24904&status=done&style=none&taskId=u7a743636-f1d6-4893-8e46-a0523be4ada&title=&width=248
[image.png 3]: https://img-blog.csdnimg.cn/img_convert/396ea4280851a9f3cd81c6efde49d057.png#averageHue=#3a2f2e&clientId=u39b0e149-6066-4&from=paste&height=77&id=kxogu&originHeight=116&originWidth=760&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=19705&status=done&style=none&taskId=u3c7115cb-5dcd-48e4-8fd8-a1bd4c5f627&title=&width=506.6666666666667
[image.png 4]: https://img-blog.csdnimg.cn/img_convert/1fb7f5ceeb3254b1f565cd63e22ea15e.png#averageHue=#322c2b&clientId=u39b0e149-6066-4&from=paste&height=121&id=u3a576260&originHeight=182&originWidth=678&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=24464&status=done&style=none&taskId=u75bd9d3e-b2af-43b1-a79e-27f9cf49a47&title=&width=452
[image.png 5]: https://img-blog.csdnimg.cn/img_convert/cb18b9de01fb01a9b60524c34d394fc0.png#averageHue=#302a29&clientId=u39b0e149-6066-4&from=paste&height=322&id=u2ce5862c&originHeight=483&originWidth=686&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=43135&status=done&style=none&taskId=ue4c43fcd-3461-4121-a579-cac3861e042&title=&width=457.3333333333333
[image.png 6]: https://img-blog.csdnimg.cn/img_convert/5f34fc9861fb1f390975b7f14b603e4b.png#averageHue=#332c2b&clientId=u39b0e149-6066-4&from=paste&height=115&id=u7af98b1b&originHeight=172&originWidth=674&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=24088&status=done&style=none&taskId=ua72ede6d-a0f5-47e5-89eb-8371eefc99e&title=&width=449.3333333333333
[image.png 7]: https://img-blog.csdnimg.cn/img_convert/54f3ce4964d246b15a50f82124c870f8.png#averageHue=#2e2b2b&clientId=u39b0e149-6066-4&from=paste&height=555&id=u7761cc7c&originHeight=832&originWidth=1004&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=116138&status=done&style=none&taskId=ua8391a4b-c9ff-4cc3-b907-e97ec835509&title=&width=669.3333333333334
[image.png 8]: https://img-blog.csdnimg.cn/img_convert/f058d057fb269292509c7fa1ecb6ee2d.png#averageHue=#f8f7f7&clientId=u39b0e149-6066-4&from=paste&height=121&id=ueefb5187&originHeight=182&originWidth=853&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=11861&status=done&style=none&taskId=u0e4084e5-d6b8-4b13-914d-849c1226bdf&title=&width=568.6666666666666
[Java _POI_EXCEL_Julian.zhou_-CSDN]: https://blog.csdn.net/z_play_du/article/details/34831953