在使用Apache的POI库读取Excel文档时，遇到了一个问题：读取到的科学计数法数值与Excel中显示的不一致。 网上常见的方法是使用NumberFormat对字符进行格式化，但这种方法存在一个问题，即读取到的科学计数法字符串已经与Excel中显示的字符串不同，因此即使进行格式化，读取结果也是错误的。
正确的做法是将单元格格式设置为字符串，然后以字符串方式读取数据。下面是一段示例代码：
// 设置单元格格式为字符串 cell.setCellType(CellType.STRING); // 以字符串方式读取数据 String data = cell.getStringCellValue();
Maven依赖：
<dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>3.1.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency>
Java读取代码：
package excelDemo;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Main {
private static final String filePath = "D:\\中软国际\\excel\\聚合收单系统操作员报名表\\";
private static final String newfilePath = "D:\\中软国际\\excel\\聚合收单系统操作员报名表2\\";
public static void main(String[] args) throws Exception {
// getSingleFile();
List<String> filePathArr = getFileNames(newfilePath);
List<List<Object>> dataArr = new ArrayList<>();
for(String str: filePathArr) {
XSSFWorkbook workbook =new XSSFWorkbook(new FileInputStream(newfilePath + str));
Sheet sheet = workbook.getSheetAt(0);
int lstRow = sheet.getLastRowNum();
for(int i=3;i<lstRow;i++) {
Row row = sheet.getRow(i);
List<Object> data = new ArrayList<>();
for(int j =0;j<row.getLastCellNum();j++) {
Cell cell = row.getCell(j);
try {
String objStr = cell.getStringCellValue();
if(objStr.contains("E")||objStr.contains("e")) {
System.out.println();
}
data.add(objStr);
}catch (IllegalStateException e) {
Double obj = cell.getNumericCellValue();
String s = obj.toString();
if (s.contains("E")) {
cell.setCellType(CellType.STRING);
s = cell.getStringCellValue();
} else {
s = s.contains(".0")?s.replace(".0", ""):s;
}
data.add(s);
}
}
dataArr.add(data);
}
}
XSSFWorkbook workBook = new XSSFWorkbook("D:\\中软国际\\excel\\newFile.xlsx");
Sheet sheet = workBook.createSheet();
for(int i=0; i<dataArr.size();i++) {
Row row = sheet.createRow(i);
List<Object> data = dataArr.get(i);
for(int j=0;j<data.size();j++) {
Cell cell = row.createCell(j);
cell.setCellValue(String.valueOf(data.get(j)));
}
}
workBook.write(new FileOutputStream("D:\\中软国际\\excel\\newFile2.xlsx"));
workBook.close();
}
public static void getSingleFile() throws Exception{
List<String> filePathArr = getFileNames(filePath);
for(String str: filePathArr) {
XSSFWorkbook workbook =new XSSFWorkbook(new FileInputStream(filePath + str));
Sheet sheet = workbook.getSheetAt(0);
removeMerged(sheet, 3, sheet.getLastRowNum(),2, 3);
String[] forData = new String[2];
int lstRow = sheet.getLastRowNum();
for(int i=3;i<lstRow;i++) {
Row row = sheet.getRow(i);
for(int j =2;j<4;j++) {
Cell cell = row.getCell(j);
String cellValue = null;
try {
cellValue = cell.getStringCellValue();
}catch (IllegalStateException e) {
cellValue = String.valueOf(cell.getNumericCellValue());
}
if(cellValue==null||cellValue=="") {
cell.setCellValue(forData[j-2]);
}else {
try {
forData[j-2] = cell.getStringCellValue();
}catch (IllegalStateException e) {
forData[j-2] = String.valueOf(cell.getNumericCellValue());
}
}
}
}
FileOutputStream out = new FileOutputStream(newfilePath + str);
workbook.write(out);
out.close();
workbook.close();
}
}
private static List<String> getFileNames(String path) {
File file = new File(path);
if (!file.exists()) {
return null;
}
List<String> fileNames = new ArrayList<>();
return getFileNames(file, fileNames);
}
private static List<String> getFileNames(File file, List<String> fileNames) {
File[] files = file.listFiles();
for (File f : files) {
if (f.isDirectory()) {
getFileNames(f, fileNames);
} else {
fileNames.add(f.getName());
}
}
return fileNames;
}
/**
* 取消多个合并单元格
*
* @param sheet
* @param startRow 开始行号
* @param endRow 结束行号
* @param startColumn 开始列号
* @param endColumn 结束列号
*/
public static void removeMerged(Sheet sheet, int startRow, int endRow, int startColumn, int endColumn) {
//获取所有的单元格
int sheetMergeCount = sheet.getNumMergedRegions();
//用于保存要移除的那个合并单元格序号
List<Integer> indexList = new ArrayList<>();
for (int i = 0; i < sheetMergeCount; i++) {
//获取第i个单元格
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (startRow <= firstRow && endRow >= lastRow && startColumn <= firstColumn && endColumn >= lastColumn) {
indexList.add(i);
}
}
sheet.removeMergedRegions(indexList);
}
}
随时随地看视频
