使用poi读取xls、xlsx文件
程序员文章站
2022-05-17 09:27:58
...
首先导入jar包:
commons-collections 4-4.1jar
poi 3.9.jar --核心jar包;
poi-ooxml.3.9.jar --支持xlsx读取
poi-ooxml-schemas 3.9.jar
xmlbeans-2.6.0.jar
maven配置:
读取excel工具类:
package com.feosa.common.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Header;
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;
public class ExcelPoiReaderUtils {
/**
* 读取excel 第1张sheet (xls和xlsx)
*
* @param filePath excel路径
* @param columns 列名(表头)
* @author lizixiang ,2018-05-08
* @return
*/
public List<Map<String, String>> readExcel(String filePath,String columns[]) {
Sheet sheet = null;
Row row = null;
Row rowHeader = null;
List<Map<String, String>> list = null;
String cellData = null;
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
wb = new XSSFWorkbook(is);
} else {
wb = null;
}
if (wb != null) {
// 用来存放表中数据
list = new ArrayList<Map<String, String>>();
// 获取第一个sheet
sheet = wb.getSheetAt(0);
// 获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
// 获取第一行
rowHeader = sheet.getRow(0);
row = sheet.getRow(0);
// 获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = 1; i < rownum; i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < colnum; j++) {
if(columns[j].equals(getCellFormatValue(rowHeader.getCell(j)))){
cellData = (String) getCellFormatValue(row
.getCell(j));
map.put(columns[j], cellData);
}
}
} else {
break;
}
list.add(map);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/** 获取单个单元格数据
* @param cell
* @return
* @author lizixiang ,2018-05-08
*/
public Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
// 判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
// 判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
// 转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {
// 数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
}
测试类:
public static void main(String[] args) {
String filePath = "D:/tools/托管文件.xls";
String columns[] = {"账户名称","开户时间","证券帐号","开户机构"};
List<Map<String, String>> list = new ExcelPoiReaderUtils().readExcel(filePath, columns);
//遍历解析出来的list
for (Map<String,String> map : list) {
for (Entry<String,String> entry : map.entrySet()) {
System.out.print(entry.getKey()+":"+entry.getValue()+",");
}
System.out.println();
}
}
excel文件:
上一篇: 生活规律顺四季而变