在一次excle导入中通过java poi包导入数值过长时出现数值失真的问题。
100283710028672000000 在通过java导入时变成了100283710028672010000
现在通过goland 、java 、python三种语言对导入过程进行展示
java代码如下
public class Hello {
public static void main(String[] args) throws IOException {
NumberFormat numberFormat = NumberFormat.getInstance( ); numberFormat.setGroupingUsed(false); String filePath = "D:\test.xlsx";
InputStream is = new FileInputStream(filePath);
XSSFWorkbook wb = new XSSFWorkbook(is);
XSSFSheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(1);
Cell cell = row.getCell(1); String val = String.valueOf(numberFormat.format(cell.getNumericCellValue()));
// String val = cell.getStringCellValue();
BigDecimal b = new BigDecimal(val);
System._out_.println(b);
}
}
网上主要的解决方法都提高了这行代码 String val = String.valueOf(numberFormat.format(cell.getNumericCellValue())); 通过修改之后没有起到作用。
朋友在网上发帖某位大佬回复提示以下两个链接 Apache POI not returning the proper value for large numbers coming from Excel - Stack Overflow
DataFormatter (POI API Documentation)
修改代码如下，主要修改为红色部分。
public class Hello {
public static void main(String[] args) throws IOException {
NumberFormat numberFormat = NumberFormat.getInstance( ); numberFormat.setGroupingUsed(false);
String filePath = "D:\test.xlsx";
InputStream is = new FileInputStream(filePath);
XSSFWorkbook wb = new XSSFWorkbook(is);
XSSFSheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(1);
Cell cell = row.getCell(1);
String val = String.valueOf(numberFormat.format(cell.getNumericCellValue()));
// String val = cell.getStringCellValue();
BigDecimal b = new BigDecimal(val);
System.out.println(b);
String v = b.round(new MathContext(15)).toPlainString(); //解决问题的关键代码 System.out.println(v);
}
}
再次运行输出正确结果 Connected to the target VM, address: '127.0.0.1:50228', transport: 'socket' ERROR StatusLogger Log4j2 could not find a logging implementation. Please add log4j-core to the classpath. Using SimpleLogger to log to the console... 100283710028672010000 100283710028672000000 Disconnected from the target VM, address: '127.0.0.1:50228', transport: 'socket'
goland代码
突发奇想通过go语言对此excle进行处理，这里使用的为excelize这个工具包。 具体的操作方法可以这位大佬的文章 快速开始 · Excelize 简体字文档 内容解释的相当丰富。
go get github.com/xuri/excelize/v2
安装依赖包 通过运行测试输出结果正常
API server listening at: [::]:65052 100283710028672000000 出口时间 最终轴组信息 总重（kg） 2/1/16 00:00 100283710028672000000 1400
goland代码如下
package main
import ( "fmt"
"github.com/xuri/excelize/v2"
)
func main() {
f, err := excelize.OpenFile("D:\test.xlsx")
if err != nil { fmt.Println(err) return } // 获取工作表中指定单元格的值 cell, err := f.GetCellValue("Sheet1", "B2") if err != nil { fmt.Println(err) return } fmt.Println(cell)
// 获取 Sheet1 上所有单元格
rows, err := f.GetRows("Sheet1") i
f err != nil { fmt.Println(err) return }
for _, row := range rows { for _, colCell := range row { fmt.Print(colCell, "\t") } fmt.Println() }
// 关闭工作簿
if err = f.Close(); err != nil { fmt.Println(err) } }
python代码
python代码如下 使用xlrd
from decimal import Decimal
import xlrd import xlwt
import xlutils import os
workbook = xlrd.open_workbook(r'D:\test.xls')
print(workbook.sheet_names()) _# ----读取sheet---- _
table = workbook.sheets()[0]
print(table.row(1)[1].value)
print(Decimal(table.row(1)[1].value))
打印结果依然有问题
D:\python_WorkSpace\venv\Scripts\python.exe "D:\Program Files\JetBrains\PyCharm 2021.2.1\plugins\python\helpers\pydev\pydevd.py" --multiproc --qt-support=auto --client 127.0.0.1 --port 60151 --file D:/python_WorkSpace/excletest/excleimport.py Connected to pydev debugger (build 212.5080.64) ['Sheet1'] 1.00283710028672e+20 100283710028672008192
切换python依赖插件openpyxl
from decimal import Decimal
import numpy as np
import openpyxl
workbook = openpyxl.load_workbook("D:/test.xlsx") sheet1 = workbook.active print(Decimal(sheet1['B2'].value)) np.set_printoptions(suppress=True) print(str(Decimal(sheet1['B2'].value)))
for row in sheet1.rows: for item in row: print(item.value)
输出日志问题尚未解决
D:\python_WorkSpace\venv\Scripts\python.exe "D:\Program Files\JetBrains\PyCharm 2021.2.1\plugins\python\helpers\pydev\pydevd.py" --multiproc --qt-support=auto --client 127.0.0.1 --port 52534 --file D:/python_WorkSpace/excletest/excleimportopen.py Connected to pydev debugger (build 212.5080.64) 100283710028672008192 100283710028672008192 出口时间 最终轴组信息 总重（kg） 2016-02-01 00:00:00 1.00283710028672e+20 1400 Process finished with exit code 0
python 处理依然存在问题。。。。。。。。。。。。。。。。。 解决方法尚未提出。。。。。。。。。。。。。。。。。。。。。 后续会继续进行更新。。。。。。。。。。。。。。。。。。。。
