一、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如下:
但你用鼠标点进A2和A3,然后移出鼠标,变成了:
在常规模式下,excel就将数值格式化成那样了,如果你将常规设置成文本那就是正常的字符串了。
要让数值不被excel格式化,只需加入下面这行代码:
centerAlignStyle1.setDataFormat(BuiltinFormats.getBuiltinFormat("text")); // 设置为文本
再次运行你的代码,你应该发现这个神奇的效果了。
二、NUMERIC TO TEXT(读取excel)
要是excel已经是下面这样了:
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://www.programcreek.com/java-api-examples/index.php?api=org.apache.poi.ss.util.NumberToTextConverter