最近项目需要 写了一个读取Excel的工具 可以获取到行列的信息 其后就任意处理数据了
而且还可以生成Excel
例子:
// 垃圾桶分组
@Test
public void group(){
ExcelReadResult excelReadResult = RWexcel.Instance().readExcel("D:\\奥园位置统计.xls", true);
System.out.println(excelReadResult.tableBody);
}
下面输出
[[1, 7, , 95, , 39.996305, 116.386228], [2, 24, , 35, , 39.996214, 116.38624], [3, 13, , 20, , 39.996172, 116.386099], [4, 6, , 14, , 39.996101, 116.385975], [5, 8, , 15, , 39.996094, 116.385756], [6, 16, , 76, , 39.996321, 116.385793], [7, 19, , 78, , 39.995936, 116.385757], [8, 33, , 69, , 39.995713, 116.385795], [9, 23, , 2, , 39.995744, 116.386048], [10, 31, , 25, , 39.99575, 116.386459], [11, 12, , 81, , 39.995423, 116.386573], [12, 88, , 50, , 39.995396, 116.386084], [13, 93, , 11, , 39.995415, 116.385801], [14, 56, , 75, , 39.994635, 116.385896], [15, 96, , 5, , 39.994647, 116.386249], [16, 10, , 27, , 39.994652, 116.386576], [17, 61, , 3, , 39.99445, 116.386635], [18, 4, , 72, , 39.99434, 116.386669], [19, 1, , 73, , 39.994168, 116.386701], [20, 46, , 22, , 39.994095, 116.386201], [21, 51, , 53, , 39.99412, 116.385904], [22, 68, , 91, , 39.99445, 116.386234], [23, 29, , 9, , 39.994438, 116.385924], [24, 36, , 80, , 39.993986, 116.385876], [25, 37, , 45, , 39.993729, 116.38588], [26, 34, , 49, , 39.993692, 116.386129], [27, 28, , 62, , 39.993687, 116.386312], [28, 17, , 47, , 39.993504, 116.386363], [29, 54, , 59, , 39.993473, 116.385903], [30, 74, , 65, , 39.992882, 116.386213], [31, 52, , 77, , 39.992896, 116.386584], [32, 18, , 79, , 39.992659, 116.3866], [33, 48, , 58, , 39.992612, 116.386402], [34, 85, , 89, , 39.992493, 116.386431], [35, 39, , 44, , 39.992539, 116.386225], [36, 82, , 94, , 39.992651, 116.386285], [37, 26, , 64, , 39.992648, 116.385904], [38, 57, , 83, , 39.992517, 116.385839], [39, 92, , 97, , 39.992296, 116.385955], [40, 40, , 71, , 39.992302, 116.38638], [41, 66, , 86, , 39.992317, 116.386655], [42, 42, , 63, , 39.992043, 116.386723]]
代码
/**
* @author 李鑫
* @version 1.0
* @date 2019/7/5 13:31
* @desc
*/
public class RWexcel {
/**
* @param filePath 文件路径
* @param hasHead 是否有表头
* @return
*/
public static RWexcel rWexcel;
// 单例模式
public static RWexcel Instance(){
if (rWexcel == null){
return new RWexcel();
}else {
return rWexcel;
}
}
// 读取excel 返回
public ExcelReadResult readExcel(String filePath,boolean hasHead){
ExcelReadResult readResult = new ExcelReadResult();
InputStream inputStream ;
ExcelReader reader;
try {
inputStream =new BufferedInputStream(new FileInputStream(filePath));
} catch (FileNotFoundException e) {
readResult.isSuccess=true;
readResult.fialReason = "未找到文件";
return readResult;
}
char c = filePath.charAt(filePath.length()-1);
// 根据不同的文件后缀来判断是03版之前还是之后
if (c=='s'){
reader = EasyExcelFactory.getReader(inputStream, new ExcelListener(readResult, hasHead));
}else if(c=='x'){
reader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, new ExcelListener(readResult, hasHead));
}else {
readResult.isSuccess=true;
readResult.fialReason = "文件格式不匹配";
return readResult;
}
reader.read();
return readResult;
}
// 通过输入流来解析
public ExcelReadResult readExcelByStream(InputStream is,String suffix, boolean hasHead){
InputStream stream = new BufferedInputStream(is);
ExcelReadResult readResult = new ExcelReadResult();
ExcelReader reader;
char c = suffix.charAt(suffix.length()-1);
// 根据不同的文件后缀来判断是03版之前还是之后
if (c=='s'){
reader = EasyExcelFactory.getReader(stream, new ExcelListener(readResult, hasHead));
}else if(c=='x'){
reader = new ExcelReader(stream, ExcelTypeEnum.XLSX, new ExcelListener(readResult, hasHead));
}else {
readResult.isSuccess=true;
readResult.fialReason = "文件格式不匹配";
return readResult;
}
reader.read();
return readResult;
}
// 生成Excel表格
/**
* @param head 要创建的excel的头信息
* @param body 要创建的excel的内部信息
* @param name 要创建的excel的名字
* @param parentFilePath 创建的文件放在那个文件加下
* @param env 选择工作的环境 Linux 是 ’L‘ windows 是 ’W‘
* @return excel的完整路径 Windows 默认在C盘excel文件夹下
*/
public String writeExcel(ArrayList<String> head , List<List<String>> body, String name , String parentFilePath, char env){
FileOutputStream stream ;
String filepath;
if (env=='L'){
filepath = parentFilePath+name+".xlsx";
}else {
filepath = "F:\\"+name+".xlsx";
}
try {
File file = new File(filepath);
file.setReadable(true,false);
file.setWritable(true,false);
file.setExecutable(true,false);
if (file.exists()){
file.delete();
}else {
//
}
//C:\AppCode\graduateDesign-master
stream = new FileOutputStream(filepath);
} catch (FileNotFoundException e) {
return null;
}
ExcelWriter writer = new ExcelWriter(stream, ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1);
sheet.setSheetName(name);
List<List<String>> headdata = new ArrayList<List<String>>();
for (String string : head){
List<String> item = new ArrayList<String>();
item.add(string);
headdata.add(item);
}
sheet.setHead(headdata);
writer.write0(body,sheet);
writer.finish();
String prefUrl = "/excel/"+name+".xlsx";
//http://39.105.219.190:8082/%E6%B7%BB%E5%8A%A0%E6%83%85%E5%86%B5.xlsx
//http://39.105.219.190:8082/%E6%B7%BB%E5%8A%A0%E6%83%85%E5%86%B5.xlsx
return prefUrl;
}
static class ExcelListener extends AnalysisEventListener {
List<List<String>> excelBody = new ArrayList<>();
ArrayList<String> excelHead = new ArrayList<>();
private boolean hasHead;
private int RowNumber = 0;
int headSize = 0;
ExcelReadResult excelReadResult = null;
public ExcelListener(ExcelReadResult readResult ,boolean hashead) {
this.hasHead = hashead;
excelReadResult = readResult;
}
@Override
// 开始读取
public void invoke(Object o, AnalysisContext analysisContext) {
// 如果有表头 那么第一行读的是表头的信息
if (hasHead&&analysisContext.getCurrentRowNum()==0){
excelHead = (ArrayList<String>) o;
headSize = excelHead.size();
}else {
ArrayList<String> rowBody = (ArrayList<String>) o;
int j = headSize - rowBody.size();
for (int i = 0; i < j ; i++) {
rowBody.add("");
}
excelBody.add(rowBody);
}
}
// 读取完毕回调
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
excelReadResult.isSuccess = true;
excelReadResult.tableBody=excelBody;
excelReadResult.tableHead = excelHead;
}
}
}
/**
* @author 李鑫
* @version 1.0
* @date 2019/7/5 11:11
* @desc
*/
public class ExcelReadResult {
public boolean isSuccess;
public String fialReason;
public ArrayList<String> tableHead;
public List<List<String>> tableBody;
public ExcelReadResult() {
this.tableHead = new ArrayList<>();
this.tableBody = new ArrayList<>();
}
}