Excel读写技术方案两种:POI和JXL,除此之外Excel版本过高则需要改写用XSSF方式。
有没有一种效率高、兼容性强的方式,上代码
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.6</version>
</dependency>
</dependencies>
以下代码在https://blog.csdn.net/u013753310/article/details/103044309基础上做了改良,支持动态创建execl表头名称
package com.example.demo.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import com.alibaba.fastjson.JSON;
public class PoiExcelReadUtil {
private static org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(PoiExcelReadUtil.class);
//测试demo
public static void main(String[] args) throws IOException {
File xlsFile = new File("d:\\test.xlsx");
FileInputStream fileInputStream = new FileInputStream(xlsFile);
// 获得工作簿内容
List<Map<String, String>> readExcel = PoiExcelReadUtil.readExcel(fileInputStream);
//打印
System.out.println(JSON.toJSONString(readExcel));
}
/**
* 读取excel文件流
*
* @param in
* @return
*/
public static List<Map<String, String>> readExcel(FileInputStream in) {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Map<String, String> map = null;
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(in);
Sheet sheet = workbook.getSheetAt(0); //默认excel只有1页sheet
//统计行数、列数坐标
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 1; i < rows; i++) {//第0行是表头,从第1行开始
map = new HashMap<String, String>();
// 获取第i行数据
XSSFRow sheetRow = (XSSFRow) sheet.getRow(i);
// 获取第j列数据
int physicalNumberOfCells = sheetRow.getPhysicalNumberOfCells();
for(int j = 0; j<physicalNumberOfCells; j++){
// 组织数据
XSSFRow sheetRowHeader = (XSSFRow) sheet.getRow(0);//表头名称
map.put(sheetRowHeader.getCell(j).getStringCellValue(), getXssfCellValue(sheetRow.getCell(j)));
}
System.out.println("开始读取第" + i + "行数据:" + JSON.toJSONString(map));
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (in != null) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return list;
}
private static String getXssfCellValue(XSSFCell xssfCell) {
String cellvalue = "";
DataFormatter formatter = new DataFormatter();
if (null != xssfCell) {
switch (xssfCell.getCellType()) {
case XSSFCell.CELL_TYPE_NUMERIC: // 数字
double value = xssfCell.getNumericCellValue();
DecimalFormat df = new DecimalFormat("0");
cellvalue = df.format(value);
// int intValue = (int) value;
//cellvalue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
break;
case XSSFCell.CELL_TYPE_STRING: // 字符串
cellvalue = xssfCell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellvalue = String.valueOf(xssfCell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_FORMULA: // 公式
cellvalue = String.valueOf(xssfCell.getCellFormula());
break;
case XSSFCell.CELL_TYPE_BLANK: // 空值
cellvalue = "";
break;
case XSSFCell.CELL_TYPE_ERROR: // 故障
cellvalue = "";
break;
default:
cellvalue = "UNKNOWN TYPE";
break;
}
} else {
System.out.print("-");
}
return cellvalue.trim();
}
}