官方文档:https://www.yuque.com/easyexcel/doc/read
EasyExcel 实例中 读取了本地的文件 读取了hdfs中的文件
pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>2.7.3</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-hdfs</artifactId>
<version>2.7.1</version>
</dependency>
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import org.apache.commons.lang3.StringUtils;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.FSDataInputStream;
import org.apache.hadoop.fs.FileSystem;
import org.apache.hadoop.fs.Path;
import org.springframework.util.ResourceUtils;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelRead {
public static void main(String[] args) {
String hdfsfile = "hdfs://172.0.0.1:8020/test/1640333127093.xlsx";
String localfile = "C:\\Users\\87690\\Desktop\\1111.xlsx";
String excelSheetName = "Sheet1";
String excelStartCell = "A1";
String excelEndCell = "";
String excelHeader = "true";
String col = getBigExcelColumns(localfile,excelSheetName,excelHeader,excelStartCell,excelEndCell);
System.out.println(col);
}
/**
*
* @param filePath excel文件路径
* @param excelSheetName sheet页名称 sheet1
* @param excelHeader 是否包含列头 true、false
* @param excelStartCell excel 开始标记为 A1
* @param excelEndCell excel结束标记为 B12
* @return
*/
public static String getBigExcelColumns(String filePath,String excelSheetName,String excelHeader,String excelStartCell,String excelEndCell) {
String cols = "";
if(StringUtils.isNotEmpty(filePath)){
//如果起始单元格为空或者为null 默认从A1开始
if(StringUtils.isEmpty(excelStartCell)){
excelStartCell = "A1";
}
int startCellNum = 0; //起始单元格数字格式
int endCellNum = 0; //终止单元格数字格式
String regex = "^[a-zA-Z]*[1-9][0-9]*$"; //判断是否为有效的单元格标记
if(excelStartCell.matches(regex) && excelEndCell!=null && excelEndCell.matches(regex)){
startCellNum = excelColStrToNum(excelStartCell);
endCellNum = excelColStrToNum(excelEndCell);
}else if(excelStartCell.matches(regex) && StringUtils.isEmpty(excelEndCell)){
startCellNum = excelColStrToNum(excelStartCell);
}
if(startCellNum>endCellNum && endCellNum!=0){
return cols;
}
Map<String, List<String>> resMap = readBigExcel2(filePath,excelSheetName);
List<String> headList = resMap.get("head");
List<String> valList = resMap.get("val");
System.out.println("------------------------------------------------------");
System.out.println(headList);
System.out.println(valList);
StringBuilder sbu = new StringBuilder();
int startFor = startCellNum;
int endFor = endCellNum==0?headList.size():endCellNum;
endFor = endFor>headList.size()?headList.size():endFor;
if(headList!=null && headList.size()>0){
for (int i = startFor-1; i < endFor; i++) {
if("true".equalsIgnoreCase(excelHeader)){
sbu.append(headList.get(i));
sbu.append(getColType(valList.get(i))+",");
}else {
sbu.append("_c"+i+getColType(headList.get(i))+",");
}
}
}
if(sbu!=null && sbu.length()>0){
cols = sbu.substring(0,sbu.length()-1);
}
}
return cols;
}
private static int excelColStrToNum(String colStr) {
int num = 0;
int result = 0;
if(StringUtils.isNotEmpty(colStr)){
int length = colStr.length();
String regex = "^[A-Za-z]*$";
int j = 0;
for(int i = 0; i < length; i++) {
char ch = colStr.charAt(length - i - 1);
if(String.valueOf(ch).matches(regex)){
num = (int)(ch - 'A' + 1) ;
num *= Math.pow(26, j);
j++;
result += num;
}
}
}
return result;
}
/**
* "string"; //"字符型";
* "date";//"日期型date";
* "timestamp";//"日期型timestamp";
* "integer";//"整型";
* "double";//"浮点型";
* @param column
* @return
*/
private static String getColType(String column){
String res = "string";
if(StringUtils.isNotEmpty(column)){
String cell[] = column.split("|");
if(cell!=null && cell.length>0){
//数字格式校验
String numReg = "^[\\d]";
Boolean numFlag = false;
for (String str : cell) {
numFlag = str.matches(numReg);
if(!numFlag){
if(".".equals(str) && column.indexOf(".")==column.lastIndexOf(".")
&& !column.startsWith(".") && !column.endsWith(".")){
return "double";
}else if ("-".equals(str) || "/".equals(str)){
String pattern = null;
String pattern_mm = null;
String patternTime = null;
String patternTime_mm = null;
if("-".equals(str)){
pattern = "yyyy-MM-dd";
pattern_mm = "yyyy-M-dd";
}else{
pattern = "yyyy/MM/dd";
pattern_mm = "yyyy/M/dd";
}
if(column.contains(" ") && column.contains(":")){
patternTime = pattern+" "+"HH:mm:ss";
patternTime_mm = pattern_mm+" "+"HH:mm:ss";
boolean dateTimeFlag = dateStrIsValid(column,patternTime);
boolean dateTimeFlag_mm = dateStrIsValid(column,patternTime_mm);
if(dateTimeFlag || dateTimeFlag_mm){
return "timestamp";
}else{
return "string";
}
}else{
boolean dateFlag = dateStrIsValid(column,pattern);
boolean dateFlag_mm = dateStrIsValid(column,pattern_mm);
if(dateFlag || dateFlag_mm){
return "date";
}else{
return "string";
}
}
}else{
return "string";
}
}
}
if(numFlag){
res = "integer";
}
}
}
return res;
}
private static boolean dateStrIsValid(String rawDateStr, String pattern) {
SimpleDateFormat dateFormat = new SimpleDateFormat(pattern);
Date date = null;
try {
// 转化为 Date类型测试判断
date = dateFormat.parse(rawDateStr);
return rawDateStr.equals(dateFormat.format(date));
} catch (Exception e) {
return false;
}
}
//EasyExcel.read(InputStream,listen)
private static Map<String,List<String>> readBigExcel(String filePath,String sheetName){
Map<String,List<String>> resMap = new HashMap<>();
FSDataInputStream fsDataInputStream = getFSDataInputStream(filePath);
ExcelListen listen = new ExcelListen();
ExcelReaderBuilder read = EasyExcel.read(fsDataInputStream,listen);
read.sheet(sheetName).doRead();
resMap.put("head",listen.getHeadList());
resMap.put("val",listen.getValList());
return resMap;
}
//EasyExcel.read(file,listen)
private static Map<String,List<String>> readBigExcel2(String filePath,String sheetName){
Map<String,List<String>> resMap = new HashMap<>();
File file = new File(filePath);
ExcelListen listen = new ExcelListen();
ExcelReaderBuilder read = EasyExcel.read(file,listen);
ExcelReader reader = read.build();
int sheetSize = reader.excelExecutor().sheetList().size();
for (int i = 0; i < sheetSize; i++) {
read.sheet(i).doRead();
}
reader.finish();
resMap.put("head",listen.getHeadList());
resMap.put("val",listen.getValList());
return resMap;
}
public static FSDataInputStream getFSDataInputStream(String filePath){
if(StringUtils.isEmpty(filePath)){
return null;
}
FSDataInputStream fsDataInputStream = null;
Path path = new Path(filePath);
Configuration conf = new Configuration();
FileSystem fileSystem = null;
try {
fileSystem = path.getFileSystem(conf);
fsDataInputStream = fileSystem.open(path);
} catch (IOException e) {
e.printStackTrace();
}
return fsDataInputStream;
}
public static Configuration getConfiguration(){
Configuration conf = new Configuration();
String dirPath = "";
try {
dirPath = new File(ResourceUtils.getURL("classpath:").getPath()).getParentFile().getParentFile().getParent();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
conf.addResource(new Path(dirPath+"\\hdfs-site.xml"));
conf.addResource(new Path(dirPath+"\\core-site.xml"));
return conf;
}
}
监听类:
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.data.ReadCellData;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class ExcelListen extends AnalysisEventListener<Map<Integer, String>> {
private List<String> headList = new ArrayList<>();
private List<String> valList = new ArrayList<>();
@Override
public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
//记录excel中的数据
for (Integer integer : integerStringMap.keySet()) {
valList.add(integerStringMap.get(integer));
}
valList.add("\n");
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
Set<Map.Entry<Integer, ReadCellData<?>>> entries = headMap.entrySet();
for (Map.Entry<Integer, ReadCellData<?>> entry : entries) {
headList.add(entry.getValue().getStringValue());
}
}
public List<String> getHeadList() {
return headList;
}
public void setHeadList(List<String> headList) {
this.headList = headList;
}
public List<String> getValList() {
return valList;
}
public void setValList(List<String> valList) {
this.valList = valList;
}
}
数据截图
运行结果