Excel表格中文件格式包括.xls和.xlsx文件两类，我们在操作或读写Excel表格时要注意并区别对待，简单介绍一下这两类文件的区别：
Excel中 .xls和.xlsx文件的区别 文件格式版本文件结构优缺点.xlsExcel 2003及前版本文件格式特有二级制格式&复合文档类型结构 .xlsxExcel 2007及后版本文件格式XML类型结构更节约空间&运算速度更快 POI方法 -- Excel文件读/写操作1.需引入poi相关jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
2.读操作demo -- 结果为String/数组/集合
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());
HSSFWorkbook hwb = null;
XSSFWorkbook xwb = null;
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++) {
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 -- 参数为封装的实体对象
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)未知类型-1NUMERIC(0)数值型0STRING(1)字符型1FORMULA(2)公式型2BLANK(3)空值3BOOLEAN(4)布尔4ERROR(5)错误5 JXL方法 -- Excel文件读/写操作备注：JXl方法暂不支持.xlsx文件格式
1.需引入的jxl相关jar包
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
2.读操作demo -- 结果为封装的实体对象
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.写操作 -- 参数为封装的实体对象
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 label1 = new Label(0, i, student.getId()+"");
Label label2 = new Label(1, i, student.getName());
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日期
