Excel表格中文件格式包括.xls和.xlsx文件两类,我们在操作或读写Excel表格时要注意并区别对待,简单介绍一下这两类文件的区别:
文件格式 | 版本 | 文件结构 | 优缺点 |
.xls | Excel 2003及前版本文件格式 | 特有二级制格式&复合文档类型结构 | |
.xlsx | Excel 2007及后版本文件格式 | XML类型结构 | 更节约空间&运算速度更快 |
POI方法 -- Excel文件读/写操作
1.需引入poi相关jar包
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency>
2.读操作demo -- 结果为String/数组/集合
/**
* 读操作 -- 结果为String/数组/集合
* @param pathName 文件路径
* @return
* @throws Exception
*/
public static List<String> readExcel(String pathName) throws Exception {
List<String> result = new ArrayList<String>();
File file = new File(pathName);
FileInputStream fs = new FileInputStream(file.getAbsolutePath());
//2003.xls文件
HSSFWorkbook hwb = null;
//2007.xlsx文件
XSSFWorkbook xwb = null;
//表格(第一个 index=0)
Sheet sheet = null;
if(pathName.indexOf(".xlsx") >= 0){
xwb = new XSSFWorkbook(fs);
sheet = xwb.getSheetAt(0);
}else{
hwb = new HSSFWorkbook(fs);
sheet = hwb.getSheetAt(0);
}
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
String first = "";
String second = "";
for (int i = firstRowNum; i <= lastRowNum; i++) {
//取得第i行 (第一行i=0是表头)
Row row = sheet.getRow(i);
if (row != null) {
if (row.getCell(0) != null) {
row.getCell(0).setCellType(CellType.STRING);
first = row.getCell(0).getStringCellValue();
}
if (row.getCell(1) != null) {
row.getCell(1).setCellType(CellType.STRING);
second = row.getCell(1).getStringCellValue();
}
}
System.out.println("第" + i + "行信息为:" + first + " | " + second);
result.add(first+second);
}
return result;
}
3.写操作demo -- 参数为封装的实体对象
/**
* 写操作 -- 参数为封装的实体对象
* @param pathName 文件路径
* @param sheetName 表格名称
* @param style .xls/.xlsx文件类别
* @param titles 表头信息
* @param datas 表格信息
* @return
*/
public static boolean writeExcel(
String pathName, String sheetName, String style, List<String> titles, List<Student> datas
) throws Exception {
Workbook workbook;
if (".XLS".equals(style.toUpperCase())) {
workbook = new HSSFWorkbook();
} else {
workbook = new XSSFWorkbook();
}
// 生成一个表格
Sheet sheet = workbook.createSheet(sheetName);
Row row = sheet.createRow(0);
/**
* 创建表头信息
*/
for (int i = 0; i < titles.size(); i++) {
Cell cell = row.createCell(i);
cell.setCellValue(titles.get(i));
}
/**
* 创建表格信息
*/
Iterator<Student> iterator = datas.iterator();
int index = 0;
while (iterator.hasNext()) {
index++;
row = sheet.createRow(index);
Student student = iterator.next();
//实体对象属性个数
int length = student.getClass().getDeclaredFields().length;
System.out.println("Student类属性数量为:"+length);
for (int i = 0; i < length ; i++) {
Cell cell = row.createCell(i);
//依次对应实体对象的属性
switch (i){
case 0 :
cell.setCellValue(student.getId());
break;
case 1 :
cell.setCellValue(student.getName());
break;
default:
System.out.println("【异常】Student类属性数量为:"+length+" | i="+i);
break;
}
}
}
/**
* 写入到文件中
*/
boolean isCorrect = false;
File file = new File(pathName);
OutputStream outputStream = null;
try {
outputStream = new FileOutputStream(file);
workbook.write(outputStream);
isCorrect = true;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != outputStream) {
outputStream.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return isCorrect;
}
备注:POI中CellType类型及code对应关系表
CellType | 类型 | code |
_NONE(-1) | 未知类型 | -1 |
NUMERIC(0) | 数值型 | 0 |
STRING(1) | 字符型 | 1 |
FORMULA(2) | 公式型 | 2 |
BLANK(3) | 空值 | 3 |
BOOLEAN(4) | 布尔 | 4 |
ERROR(5) | 错误 | 5 |
JXL方法 -- Excel文件读/写操作
备注:JXl方法暂不支持.xlsx文件格式
1.需引入的jxl相关jar包
<!--https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl--> <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.12</version> </dependency>
2.读操作demo -- 结果为封装的实体对象
/**
* 读操作 -- 结果为封装的实体对象
* @param pathName
* @return
* @throws Exception
*/
public static List<Student> readExcelBak(String pathName) throws Exception {
List<Student> result = new ArrayList<>();
if(pathName.indexOf(".xlsx") < 0){
File file = new File(pathName);
FileInputStream fs = new FileInputStream(file.getAbsolutePath());
Workbook wb = Workbook.getWorkbook(fs);
Sheet sheet = wb.getSheet(0);
int rows = sheet.getRows();
for (int i=0 ; i<rows ; i++){
Student student = new Student();
Cell[] cells = sheet.getRow(i);
if(cells[0] != null){
if(CellType.LABEL.equals(cells[0].getType())){
student.setName(cells[0].getContents().trim());
}else {
System.out.println("注意:("+i+"行, "+0+"列)数据格式(LABEL)有误请核对 | "+cells[0].getType());
}
}
if (cells[1] != null) {
if (CellType.NUMBER.equals(cells[1].getType())) {
student.setId(Integer.parseInt(cells[1].getContents().trim()));
}else{
System.out.println("注意:("+i+"行, "+1+"列)数据格式(NUMBER)有误请核对 | "+cells[1].getType());
}
}
System.out.println("第" + i + "行信息为:" + student.getName() + " | " + student.getId());
result.add(student);
}
}else {
System.out.println("JXL方法暂不支持.xlsx文件格式,请先处理Excel文件");
}
return result;
}
3.写操作 -- 参数为封装的实体对象
/**
* 写操作 -- 参数为封装的实体对象
* @param pathName
* @throws Exception
*/
public static boolean writeExcel(String pathName, List<Student> students) throws Exception{
File file = new File(pathName);
OutputStream outputStream = new FileOutputStream(file);
WritableWorkbook writableWorkbook = Workbook.createWorkbook(outputStream);
WritableSheet writableSheet = writableWorkbook.createSheet("测试", 0);
for(int i=0; i<students.size(); i++){
Student student = students.get(i);
//创建一个Label,第一个参数是x轴,第二个参数是y轴,第三个参数是内容,第四个参数可选,指定类型
Label label1 = new Label(0, i, student.getId()+"");
Label label2 = new Label(1, i, student.getName());
//把label加入sheet对象中
writableSheet.addCell(label1);
writableSheet.addCell(label2);
}
boolean result = false;
try {
writableWorkbook.write();
result = true;
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != writableWorkbook){
writableWorkbook.close();
}
if(null != outputStream){
outputStream.close();
}
}
return result;
}
备注:JXL中CellType类型对应关系表
Celltype | 类型 |
Empty | 空值 |
Label | 常规 |
Number | 数值 |
Boolean | 布尔 |
Error | 错误 |
Numerical Formula | 数值型公式 |
Date Formula | 日期型公式 |
String Formula | 字符型公式 |
Boolean Formula | 布尔型公式 |
Formula Error | 公式误差 |
Date | 日期 |