ExcelUtil.java
4.49 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
package com.taover.bazhuayun.analysis.util;
import java.io.File;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import com.taover.easyexcel.EasyExcel;
import cn.hutool.core.text.csv.CsvRow;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.util.CharsetUtil;
public class ExcelUtil {
private final static String CSV =".csv"; //csv
private static boolean endWithCsv(String fileName) {
int dotIndex = fileName.lastIndexOf(".");
if(dotIndex < 0) {
return false;
}
String filteTypeLower = fileName.substring(dotIndex).trim().toLowerCase();
if(CSV.equals(filteTypeLower)){
return true;
}
return false;
}
private static void checkFile(File dataFile) throws Exception{
if(dataFile == null) {
throw new Exception("未传入文件引用");
}
if(!dataFile.exists()) {
throw new Exception("文件不存在["+dataFile.getAbsolutePath()+"]");
}
}
private static List<List<Object>> transCsvRowToList(List<CsvRow> rowData){
List<List<Object>> data = new ArrayList<List<Object>>();
if(rowData == null) {
return data;
}
for(CsvRow item: rowData) {
List<Object> itemData = new ArrayList<Object>();
for(String cell: item.getRawList()) {
itemData.add(cell);
}
data.add(itemData);
}
return data;
}
public static List<List<Object>> readExcelSheetAllForList(File dataFile, boolean readHiddenRow) throws Exception{
checkFile(dataFile);
if(endWithCsv(dataFile.getName())) {
return transCsvRowToList(CsvUtil.getReader().read(dataFile, CharsetUtil.CHARSET_GBK).getRows());
}else {
return transListMapTo2List(EasyExcel.read(dataFile).readHiddenRow(readHiddenRow).headRowNumber(0).doReadAllSync());
}
}
public static Map<String, List<List<Object>>> readExcelSheetAllForMap(File dataFile, boolean readHiddenRow) throws Exception{
checkFile(dataFile);
if(endWithCsv(dataFile.getName())) {
return Collections.singletonMap("0", transCsvRowToList(CsvUtil.getReader().read(dataFile, CharsetUtil.CHARSET_GBK).getRows()));
}else {
return transMapListMapToMap2List(EasyExcel.read(dataFile).readHiddenRow(readHiddenRow).headRowNumber(0).doReadAllSyncForMap());
}
}
private static Map<String, List<List<Object>>> transMapListMapToMap2List(Map<Integer, List<Map<Integer, Object>>> doReadAllSyncForMap) {
Map<String, List<List<Object>>> data = new HashMap<String, List<List<Object>>>();
if(doReadAllSyncForMap == null) {
return data;
}
for(Entry<Integer, List<Map<Integer, Object>>> item: doReadAllSyncForMap.entrySet()) {
List<List<Object>> itemData = new ArrayList<List<Object>>();
for(Map<Integer, Object> cellData: item.getValue()) {
itemData.add(transMapToList(cellData));
}
data.put(item.getKey().toString(), itemData);
}
return data;
}
private static List<List<Object>> transListMapTo2List(List<Object> listMapData) {
List<List<Object>> data = new ArrayList<List<Object>>();
if(listMapData == null) {
return data;
}
for(Object item: listMapData) {
data.add(transMapToList((Map<Integer, Object>) item));
}
return data;
}
private static List<Object> transMapToList(Map<Integer, Object> cellData) {
List<Object> data = new ArrayList<Object>((int)(cellData.size()*1.5));
if(cellData == null || cellData.isEmpty()) {
return data;
}
//获取最大索引
int maxIndex = 0;
for(Entry<Integer, Object> item: cellData.entrySet()) {
if(item.getKey() > maxIndex && item.getValue()!=null && !"".equals(item.getValue())) {
maxIndex = item.getKey();
}
}
//设置元素并填入空串
fillBlankToList(data, maxIndex+1);
//置入元素
int dataSize = data.size();
for(Entry<Integer, Object> item: cellData.entrySet()) {
if(item.getKey() >= dataSize) {
continue;
}
if(item.getValue() == null) {
data.set(item.getKey(), "");
}else {
data.set(item.getKey(), item.getValue());
}
}
return data;
}
private static void fillBlankToList(List<Object> data, int blankNumber) {
for(int i=0; i<blankNumber; ++i) {
data.add("");
}
}
public static List<List<Object>> readExcelSheet(File dataFile, boolean readHiddenRow) throws Exception{
checkFile(dataFile);
if(endWithCsv(dataFile.getName())) {
return transCsvRowToList(CsvUtil.getReader().read(dataFile, CharsetUtil.CHARSET_GBK).getRows());
}else {
return transListMapTo2List(EasyExcel.read(dataFile).readHiddenRow(readHiddenRow).headRowNumber(0).doReadSelectedSync());
}
}
}