poi操作excel之: 将NUMERIC转换成TEXT

左手的ㄟ右手 2022-06-09 01:15 279阅读 0赞

没办法，企业业务系统总是避免不了要使用excel进行导入和导出，本代码使用的poi版本是3.16：

#### 一、NUMERIC TO TEXT(生成excel) ####

代码生成一个excel文件：

public static void generateExcel() throws Exception {
            XSSFWorkbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("天下霸唱");
            sheet.setColumnWidth(0, 10000); // 设置excel一列的宽度
    
            // one style
            Font font = workbook.createFont();
            font.setFontName("微软雅黑");
            font.setBold(true);
            font.setFontHeightInPoints((short) 14);
    
            Font font1 = workbook.createFont();
            font1.setFontName("微软雅黑");
            font1.setFontHeightInPoints((short) 14);
    
            XSSFCellStyle centerAlignStyle = workbook.createCellStyle();
            centerAlignStyle.setAlignment(HorizontalAlignment.CENTER);
            centerAlignStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 上下垂直居中
            centerAlignStyle.setFont(font); //设置字体
            centerAlignStyle.setWrapText(true); // 换行
    
            XSSFCellStyle centerAlignStyle1 = workbook.createCellStyle();
            centerAlignStyle1.setAlignment(HorizontalAlignment.CENTER);
            centerAlignStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
            centerAlignStyle1.setFont(font1);
    
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("一个值");
            cell.setCellStyle(centerAlignStyle);
    
            Row row1 = sheet.createRow(1);
            Cell cell1 = row1.createCell(0);
            cell1.setCellValue(String.valueOf(1242532523632L));
            cell1.setCellStyle(centerAlignStyle1);
    
            Row row2 = sheet.createRow(2);
            Cell cell2 = row2.createCell(0);
            cell2.setCellValue("00000000000009");
            cell2.setCellStyle(centerAlignStyle1);
    
            FileOutputStream outputStream = new FileOutputStream("test.xlsx");
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        }
    
        public static void main(String[] args) throws Exception {
            generateExcel();
        }

生成的excel如下：  
![这里写图片描述][SouthEast]

但你用鼠标点进A2和A3，然后移出鼠标，变成了：  
![这里写图片描述][SouthEast 1]

在常规模式下，excel就将数值格式化成那样了，如果你将`常规`设置成`文本`那就是正常的字符串了。

要让数值不被excel格式化，只需加入下面这行代码：

centerAlignStyle1.setDataFormat(BuiltinFormats.getBuiltinFormat("text")); // 设置为文本

再次运行你的代码，你应该发现这个神奇的效果了。

#### 二、NUMERIC TO TEXT（读取excel） ####

要是excel已经是下面这样了：  
![这里写图片描述][SouthEast 2]

Java代码在读取的时候想要的是字符串：1242532523632，那怎么办？

public static String getCellStringValue(Cell cell) {
            if (cell == null) {
                return "";
            }
            CellType type = cell.getCellTypeEnum();
            String cellValue;
            switch (type) {
                case BLANK:
                    cellValue = "";
                    break;
                case _NONE:
                    cellValue = "";
                    break;
                case ERROR:
                    cellValue = "";
                    break;
                case BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case NUMERIC:
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case FORMULA:
                    cellValue = cell.getCellFormula();
                    break;
                default:
                    cellValue = "";
                    break;
            }
            return cellValue;
        }
    
        public static void readExcel() throws Exception {
            FileInputStream inputStream = new FileInputStream(new File("test.xlsx"));
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            Cell cell = sheet.getRow(1).getCell(0);
            String cellValue = getCellStringValue(cell);
            System.out.println(cellValue);
    
            workbook.close();
            inputStream.close();
        }
    
        public static void main(String[] args) throws Exception {
            readExcel();
        }

上面的执行的结果是：

1.242532523632E12    //不是我们想要的字符串呢

那就使用poi的另一个神器`NumberToTextConverter`，改写switch的代码：

case NUMERIC:
     // 改成这样
                    cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                    break;

执行后返回：

1242532523632      //我们想要的结果

That’s all, 哈哈！  
Google英文搜索关键字：

> excel poi get text value from a numeric cell  
> excel poi format a cell to text type

参考链接：  
[https://stackoverflow.com/questions/36018660/how-to-set-a-cell-format-to-text][https_stackoverflow.com_questions_36018660_how-to-set-a-cell-format-to-text]  
[https://www.programcreek.com/java-api-examples/index.php?api=org.apache.poi.ss.util.NumberToTextConverter][https_www.programcreek.com_java-api-examples_index.php_api_org.apache.poi.ss.util.NumberToTextConverter]

[SouthEast]: /images/20220609/16a49645358648bebbde8e7d68782e00.png
[SouthEast 1]: /images/20220609/b3e65338c9124be2ac30f9d9117f8f44.png
[SouthEast 2]: /images/20220609/9618990e7b9a4844a6bc1644c383670b.png
[https_stackoverflow.com_questions_36018660_how-to-set-a-cell-format-to-text]: https://stackoverflow.com/questions/36018660/how-to-set-a-cell-format-to-text
[https_www.programcreek.com_java-api-examples_index.php_api_org.apache.poi.ss.util.NumberToTextConverter]: https://www.programcreek.com/java-api-examples/index.php?api=org.apache.poi.ss.util.NumberToTextConverter