这篇博客介绍了如何使用Apache POI处理Excel中的长整数和日期格式问题，避免数字转为科学计数法和日期转换为数值。作者提供了一个完整的Java代码示例，包括读取和写入Excel文件，以及处理数字和日期的细节。通过引入POI和Joda-Time库，实现了对数字格式和日期的正确处理。
POI操作EXCEl表格（包括对长整型数字和日期的处理）
需求原因
很久没有输出了，深感惭愧！！！
由于需要将xlsx格式的excel表格的数据写入到.sql文件，再直接执行.sql文件后将数据插入到表格里。
表格里包含数字和文本，其中比较难处理的是较长的数值和日期，POI会将较长的数字转成科学计数法，日期也会转成数值，而我想要的结果都是维持原来的模样。
最后实现了自己的需求，参考了下面两篇博客：
1、POI导入数字变为科学计数和后面自动加.0解决方法
2、java poi 获取单元格值时间
我的实现方式直接双手献上，也有助于自己以后再用到可以很快找到
1、pom文件引入依赖
<!--xlsx(07) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!-- joda日期处理-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.9.5</version>
</dependency>
3、读实现
package com.fanhf;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicReference;
public class ExcelReadTest {
private static String readPath = "D:\\data\\";
public static final String NORM_DATETIME_PATTERN = "yyyy-MM-dd HH:mm:ss";
private static ArrayList<String> PoiDateList = new ArrayList<String>() {
{
add("yy");
add("d");
add("m");
}
};
public static void main(String[] args) {
long start = System.currentTimeMillis();
read03();
long end = System.currentTimeMillis();
System.out.println("花费时间:" + (end - start) / 1000 + "s");
}
/**
* <pre>
* @desc 读取excel文件并写入到sql文件中
* @author fanhf
* @date 2021/6/24 17:57
* </pre>
**/
public static void read03() {
try {
FileInputStream fileInputStream = new FileInputStream(readPath + "followup.xlsx");
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet sheetAt = workbook.getSheetAt(0);
String insertddl = insertColumnNameHandle(sheetAt);
List<List<String>> cellList = columnValueHandle(sheetAt);
int insertLine = readAndWriteSqlFile(insertddl, cellList);
System.out.println("insert语句总行数:" + insertLine);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* <pre>
* @desc 读取excle表格的第一行的列名
* @author fanhf
* @date 2021/6/24 17:52
* @param sheetAt sheet页
* @return insert字段
* </pre>
**/
public static String insertColumnNameHandle(Sheet sheetAt) {
String insertddl = "INSERT INTO `c_renew_achievements_record`(@{}) VALUES (";
String ddlcname = "";
List<String> tableColumnName = Lists.newArrayList();
System.out.println("表中总行数:" + sheetAt.getPhysicalNumberOfRows());
for (int i = 0; i < 1; i++) {
Row row = sheetAt.getRow(i);
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
Cell cell = row.getCell(j);
if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
tableColumnName.add(cell.getStringCellValue());
}
}
}
StringBuilder cnameStr = new StringBuilder();
for (String cname : tableColumnName) {
cnameStr.append("`").append(cname).append("`").append(",");
}
ddlcname = cnameStr.toString();
ddlcname = ddlcname.substring(0, ddlcname.length() - 1);
insertddl = insertddl.replace("@{}", ddlcname);
System.out.println("insert ddl:" + insertddl);
return insertddl;
}
/**
* <pre>
* @desc 从sheet页读取里面的数据放入集合中
* @author fanhf
* @date 2021/6/24 17:53
* @param sheetAt sheet页
* @return 除第一行外其他所有行的数据集合
* </pre>
**/
public static List<List<String>> columnValueHandle(Sheet sheetAt) {
List<List<String>> cellList = Lists.newArrayList();
for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
Row row = sheetAt.getRow(i);
List<String> rowList = Lists.newArrayList();
int physicalNumbersOfCells = row.getPhysicalNumberOfCells();
//为了防止有些单元格数据为空，就少一次循环，导致后面一个单元格数据被忽略掉
if (physicalNumbersOfCells < 4) {
physicalNumbersOfCells = 4;
}
for (int j = 0; j < physicalNumbersOfCells; j++) {
Cell cell = row.getCell(j);
String value = "";
if (Objects.isNull(cell)) {
rowList.add(value);
} else {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
String str = Objects.nonNull(cell.getStringCellValue()) ? cell.getStringCellValue() : "";
rowList.add(str);
break;
case Cell.CELL_TYPE_NUMERIC:
// 获取单元格值的格式化信息
String dataFormat = cell.getCellStyle().getDataFormatString();
// 判断格式化信息中是否存在：年月日
AtomicReference<Boolean> isDate = new AtomicReference<>(false);
if (StringUtils.isNotBlank(dataFormat)) {
PoiDateList.forEach(x -> isDate.set(isDate.get() || dataFormat.contains(x)));
}
if (!isDate.get()) {
//处理整数被转为小数的问题
NumberFormat nf = NumberFormat.getInstance();
value = nf.format(cell.getNumericCellValue());
//处理科学计数法的问题
if (value.indexOf(",") >= 0) {
value = value.replace(",", "");
}
}
if (DateUtil.isCellDateFormatted(cell)) {
value = new SimpleDateFormat(NORM_DATETIME_PATTERN).format(cell.getDateCellValue());
}
rowList.add(value);
break;
}
}
}
cellList.add(rowList);
}
System.out.println("=======================已经读取完成，开始写入文件" + readPath + "followext.sql");
return cellList;
}
/**
* <pre>
* @desc 根据insert字段和对应的数据写入到sql文件中
* @author fanhf
* @date 2021/6/24 17:54
* @param insertddl insert字段
* @param cellList insert字段对应的数据
* @return 真实插入的行数
* </pre>
**/
public static int readAndWriteSqlFile(String insertddl, List<List<String>> cellList) {
FileWriter fw = null;
int insertLine = 0;
try {
fw = new FileWriter(readPath + "followext.sql", true);
for (List<String> list : cellList) {
StringBuilder stringBuilder1 = new StringBuilder();
for (String s : list) {
stringBuilder1.append("'").append(s).append("'").append(",");
}
String rowss = insertddl + stringBuilder1.toString();
rowss = rowss.substring(0, rowss.length() - 1) + ");";
fw.write(rowss);
fw.write("\r\n");
insertLine++;
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return insertLine;
}
另外再附上自己之前写着完的往excel写文件的测试
4、写实现
package com.fanhf;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteTest {
private static String outPath = "D:\\tools\\idea\\workspaces\\poi-excel\\";
public void write03xls() {
writeExcel(1,"time-address.xls");
// long spendTime = writeExcelBigData(1, 65536, "bigData03.xls");
// System.out.println(65536 + "行 花费时间："+(double) spendTime/1000 +" ms");
}
public void write07xlsx() {
writeExcel(2,"time-address.xlsx");
// long spendTime = writeExcelBigData(2, 100000, "bigData07.xlsx");
// System.out.println(100000 + "行 花费时间："+(double)spendTime/1000 +" ms");
}
public void write07xlsxPro() {
// writeExcel(3,"time-address.xlsx");
long spendTime = writeExcelBigData(3, 100000, "bigData07.xlsx");
System.out.println(100000 + "行 花费时间："+(double)spendTime/1000 +" ms");
}
public void writeExcel(int type,String excelExtenceName){
FileOutputStream fileOutputStream = null;
Workbook workbook = null;
try {
if (1 == type) {
workbook = new HSSFWorkbook();
}else{
workbook = new XSSFWorkbook();
}
Sheet sheet = workbook.createSheet();
//(1)
Row row1 = sheet.createRow(0);
//(1,1)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("时间");
//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("地点");
//(1,3)
Cell cell13 = row1.createCell(2);
cell13.setCellValue("人数");
//(2)
Row row2 = sheet.createRow(1);
//(2,1)
Cell cell21 = row2.createCell(0);
cell21.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
//(2,2)
Cell cell22 = row2.createCell(1);
cell22.setCellValue("北京市海淀区");
//(2,3)
Cell cell23 = row2.createCell(2);
cell23.setCellValue(100000);
//(3)
Row row3 = sheet.createRow(2);
//(3,1)
Cell cell31 = row3.createCell(0);
cell31.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
//(3,2)
Cell cell32 = row3.createCell(1);
cell32.setCellValue("北京市朝阳区");
//(3,3)
Cell cell33 = row3.createCell(2);
cell33.setCellValue(200000);
fileOutputStream = new FileOutputStream(outPath + excelExtenceName);
workbook.write(fileOutputStream);
System.out.println("file " + excelExtenceName + " generated successfully");
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public long writeExcelBigData(int type,int rowsNum,String excelExtenceName){
long start = System.currentTimeMillis();
FileOutputStream fileOutputStream = null;
Workbook workbook = null;
try {
if (1 == type) {
workbook = new HSSFWorkbook();
}else if(2 == type){
workbook = new XSSFWorkbook();
}else if(3 == type){
workbook = new SXSSFWorkbook();
}
Sheet sheet = workbook.createSheet();
for (int i = 0; i < rowsNum; i++) {
//创建65535行
Row row1 = sheet.createRow(i);
//每行写10列数据,
for (int j = 0; j < 10; j++) {
//每行单元格的数据是0到9
row1.createCell(j).setCellValue(j);
}
}
fileOutputStream = new FileOutputStream(outPath + excelExtenceName);
workbook.write(fileOutputStream);
if(3 == type){
//清除零时文件
((SXSSFWorkbook) workbook).dispose();
}
System.out.println("file " + excelExtenceName + " generated successfully");
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
long end = System.currentTimeMillis();
return end - start;
}
}
需要的自取，有好的建议万望不吝赐教！！！
--------------你知道的越多，不知道的越多-------------
扫一扫
1796
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
