报错信息如下
java.lang.AbstractMethodError: org.apache.poi.xssf.usermodel.XSSFCell.setCellType(Lorg/apache/poi/ss/usermodel/CellType;
首先项目集成POI进行导入导出的pom文件
注意:最开始我使用的是3.15和3.14,但是会产生上面的报错信息,我这边一直以为是我代码存在问题, 反复修改之后还是存在问题,最后翻墙查了下stackoverflow,感觉出错的原因是poi的版本过低,于是将这边都修改成下面的4.0.0,最后解决问题
https://stackoverflow.com/questions/39993683/alternative-to-deprecated-getcelltype
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>准备工作,将本地文件夹中的excel,准备好,修改demo中的地址
写入Excele的demo
package com.excel.demo.util;
import com.excel.demo.model.User;
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.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class WriteExcel {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
public static void main(String[] args) {
// ArrayList<String> strings = new ArrayList<>();
// strings.add("1");
// strings.add("2");
// strings.add("3");
// strings.add("4");
// strings.add("5");
// strings.add("6");
// strings.add("7");
// writeExcelToRow(strings, 3, "E:/writeExcel.xlsx");
ArrayList<User> vos = new ArrayList<>();
User user = new User();
user.setAddress("地球村");
user.setLove("喜洋ccccc洋");
user.setAge(18);
user.setName("张三");
user.setSex(true);
vos.add(user);
vos.add(user);
vos.add(user);
vos.add(user);
writeExcelVo(vos, "E:/writeExcel.xlsx");
}
//向Excel里存储对象
public static void writeExcelVo(List<User> dataList, String finalXlsxPath) {
OutputStream out = null;
try {
// 获取总列数
int columnNumCount = dataList.size();
// 读取Excel文档
File finalXlsxFile = new File(finalXlsxPath);
Workbook workBook = getWorkbok(finalXlsxFile);
// sheet 对应一个工作页
Sheet sheet = workBook.getSheetAt(0);
/**
* 删除原有数据，除了属性列
*/
int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
System.out.println("原始数据总行数，除属性列：" + rowNumber);
for (int i = 1; i <= rowNumber; i++) {
Row row = sheet.getRow(i);
if(row==null){
continue;
}
sheet.removeRow(row);
}
// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
/**
* 往Excel中写新数据
*/
for (int j = 0; j < dataList.size(); j++) {
// 创建一行：从第二行开始，跳过属性列
Row row = sheet.createRow(j + 1);
// 得到要插入的每一条记录
//TODO 下面可以进行优化,使用反射获取字段数量,然后对字段进行遍历,在存储,这样可以减少手写代码的量
User user = dataList.get(j);
// 在一行内循环
Cell first = row.createCell(0);
first.setCellValue(user.getName());
Cell second = row.createCell(1);
second.setCellValue(user.getAge());
Cell third = row.createCell(2);
third.setCellValue(user.getLove());
Cell c4 = row.createCell(4);
c4.setCellValue(user.getAddress());
Cell c5 = row.createCell(5);
c5.setCellValue(user.isSex());
}
// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("数据导出成功");
}
public static void writeExcel(List<Map> dataList, int cloumnCount, String finalXlsxPath) {
OutputStream out = null;
try {
// 获取总列数
int columnNumCount = cloumnCount;
// 读取Excel文档
File finalXlsxFile = new File(finalXlsxPath);
Workbook workBook = getWorkbok(finalXlsxFile);
// sheet 对应一个工作页
Sheet sheet = workBook.getSheetAt(0);
/**
* 删除原有数据，除了属性列
*/
int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
System.out.println("原始数据总行数，除属性列：" + rowNumber);
for (int i = 1; i <= rowNumber; i++) {
Row row = sheet.getRow(i);
if(row==null){
continue;
}
sheet.removeRow(row);
}
// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
/**
* 往Excel中写新数据
*/
for (int j = 0; j < dataList.size(); j++) {
// 创建一行：从第二行开始，跳过属性列
Row row = sheet.createRow(j + 1);
// 得到要插入的每一条记录
Map dataMap = dataList.get(j);
String name = dataMap.get("BankName").toString();
String address = dataMap.get("Addr").toString();
String phone = dataMap.get("Phone").toString();
for (int k = 0; k <= columnNumCount; k++) {
// 在一行内循环
Cell first = row.createCell(0);
first.setCellValue(name);
Cell second = row.createCell(1);
second.setCellValue(address);
Cell third = row.createCell(2);
third.setCellValue(phone);
}
}
// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("数据导出成功");
}
/**
* 指定行里面添加数据
*
* @param dataList
* @param rowNum
* @param finalXlsxPath
*/
public static void writeExcelToRow(List<String> dataList, int rowNum, String finalXlsxPath) {
OutputStream out = null;
try {
// 获取总列数
int columnNumCount = rowNum;
// 读取Excel文档
File finalXlsxFile = new File(finalXlsxPath);
Workbook workBook = getWorkbok(finalXlsxFile);
// sheet 对应一个工作页
Sheet sheet = workBook.getSheetAt(0);
/**
* 删除原有数据，除了属性列
*/
int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
System.out.println("原始数据总行数，除属性列：" + rowNumber);
for (int i = 1; i <= rowNumber; i++) {
Row row = sheet.getRow(i);
if(row==null){
continue;
}
sheet.removeRow(row);
}
// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
/**
* 往Excel中写新数据
*/
Row row = sheet.createRow(columnNumCount);
for (int j = 0; j < dataList.size(); j++) {
// 创建一行：从第二行开始，跳过属性列
// 得到要插入的每一条记录
row.createCell(j).setCellValue(dataList.get(j));
}
// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
out = new FileOutputStream(finalXlsxPath);
workBook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("数据导出成功");
}
/**
* 判断Excel的版本,获取Workbook
*
* @param file
* @return
* @throws IOException
*/
public static Workbook getWorkbok(File file) throws IOException {
Workbook wb = null;
FileInputStream in = new FileInputStream(file);
if (file.getName().endsWith(EXCEL_XLS)) { //Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
}读取Excel的demo
package com.excel.demo.util;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ReadExcel {
private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";
public static void main(String[] args) {
ReadExcel obj = new ReadExcel();
File file = new File("E:/readExcel1.xls");
List excelList = obj.readExcel(file);
System.out.println("list中的数据打印出来");
for (int i = 0; i < excelList.size(); i++) {
List list = (List) excelList.get(i);
for (int j = 0; j < list.size(); j++) {
System.out.print(list.get(j));
}
System.out.println();
}
}
// 去读Excel的方法readExcel，该方法的入口参数为一个File对象
public List readExcel(File file) {
try {
// 创建输入流，读取Excel
Workbook wb = getWorkbok(file);
// Excel的页签数量
int sheetSize = wb.getNumberOfSheets();
for (int index = 0; index < sheetSize; index++) {
List<List> outerList = new ArrayList<List>();
// 每个页签创建一个Sheet对象
Sheet sheet = wb.getSheetAt(index);
// sheet.getLastRowNum()返回该页的总行数
for (int i = 0; i < sheet.getLastRowNum(); i++) {
List innerList = new ArrayList();
// row.getPhysicalNumberOfCells()返回该页的总列数
Row row = sheet.getRow(i);
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
Cell cell = row.getCell(j);
String value = getCellValue(cell);
if (value.isEmpty()) {
continue;
}
innerList.add(value);
System.out.print(value + " ");
}
outerList.add(i, innerList);
System.out.println();
}
return outerList;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 判断Excel的版本,获取Workbook
*
* @param file
* @return
* @throws IOException
*/
public static org.apache.poi.ss.usermodel.Workbook getWorkbok(File file) throws IOException {
org.apache.poi.ss.usermodel.Workbook wb = null;
FileInputStream in = new FileInputStream(file);
if (file.getName().endsWith(EXCEL_XLS)) { //Excel 2003
wb = new HSSFWorkbook(in);
} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
wb = new XSSFWorkbook(in);
}
return wb;
}
private static String getValue(Cell cell) {
String value = null;
switch (cell.getCellType()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
value = formater.format(date);
} else if (String.valueOf(cell.getNumericCellValue()).contains(".")) {
DecimalFormat df = new DecimalFormat("#");
value = df.format(cell.getNumericCellValue());
} else {
// value = (cell + "").trim();
value = String.valueOf(cell.getNumericCellValue());
}
break;
case STRING:
value = cell.getStringCellValue();
break;
case FORMULA:
break;
case BLANK:
value = "";
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
break;
default:
break;
}
return value;
}
/**
* 不同类型对应不同的取值范围
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
String value = "";
switch (cell.getCellType()) {
case STRING:
value = cell.getRichStringCellValue().getString();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = cell.getDateCellValue().toString();
} else {
value = String.valueOf(cell.getNumericCellValue());
}
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
value = cell.getCellFormula();
break;
case BLANK:
break;
default:
}
return value;
}
}上面两个Demo,第一个基本上摘自别人博客的,但是第二个,别人博客是试用jxl进行读取的,所以这边将读取的逻辑改成了新版的POI对Excel进行读取,新的读取主要是增加了getCellTypeEnum()来判断单元格数据类型,最后在根据枚举类型返回需要的结果,之前的方法是cell.getCellTypeEnum()我这边看了下代码是一个已经被废弃的,所以就使用getCellTypeEnum()来进行取代了.
运行结果如下
