Skip to content

Commit 66397b4

Browse files
author
quding
committed
增加excel解析工具
1 parent fc7108b commit 66397b4

File tree

97 files changed

+368
-166
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

97 files changed

+368
-166
lines changed

‎excel-Demo/pom.xml renamed to ‎Excel-Demo/pom.xml

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
<groupId>cn.mrdear.poi</groupId>
88
<artifactId>poi</artifactId>
99
<version>1.0-SNAPSHOT</version>
10-
10+
<name>Excel-Demo</name>
1111
<dependencies>
1212
<!--poi start-->
1313
<dependency>
@@ -25,6 +25,28 @@
2525
<artifactId>junit</artifactId>
2626
<version>4.10</version>
2727
</dependency>
28+
29+
<!--logger依赖-->
30+
<dependency>
31+
<groupId>org.slf4j</groupId>
32+
<artifactId>slf4j-api</artifactId>
33+
<version>1.7.2</version>
34+
</dependency>
35+
<dependency>
36+
<groupId>org.slf4j</groupId>
37+
<artifactId>jcl-over-slf4j</artifactId>
38+
<version>1.7.2</version>
39+
</dependency>
40+
<dependency>
41+
<groupId>ch.qos.logback</groupId>
42+
<artifactId>logback-core</artifactId>
43+
<version>1.1.2</version>
44+
</dependency>
45+
<dependency>
46+
<groupId>ch.qos.logback</groupId>
47+
<artifactId>logback-classic</artifactId>
48+
<version>1.1.2</version>
49+
</dependency>
2850
</dependencies>
2951

3052

‎Excel-Demo/src/main/java/cn/mrdear/excel/constant/ExcelType.java

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
package cn.mrdear.excel.constant;
2+
3+
/**
4+
* 定义excel格式
5+
* @author Niu Li
6+
* @since 2017/3/17
7+
*/
8+
public enum ExcelType {
9+
XLS, XLSX;
10+
}

‎Excel-Demo/src/main/java/cn/mrdear/excel/core/ExcelExtractor.java

Lines changed: 108 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,108 @@
1+
package cn.mrdear.excel.core;
2+
3+
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
4+
import org.apache.poi.ss.usermodel.Cell;
5+
import org.apache.poi.ss.usermodel.Row;
6+
import org.apache.poi.ss.usermodel.Sheet;
7+
import org.apache.poi.ss.usermodel.Workbook;
8+
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
9+
import org.slf4j.Logger;
10+
import org.slf4j.LoggerFactory;
11+
12+
import java.io.IOException;
13+
import java.io.InputStream;
14+
import java.util.ArrayList;
15+
import java.util.HashMap;
16+
import java.util.HashSet;
17+
import java.util.Iterator;
18+
import java.util.List;
19+
import java.util.Map;
20+
import java.util.Set;
21+
22+
import cn.mrdear.excel.constant.ExcelType;
23+
import cn.mrdear.excel.util.BeanUtils;
24+
25+
/**
26+
* excel解析
27+
*
28+
* @author Niu Li
29+
* @since 2017/3/17
30+
*/
31+
public class ExcelExtractor {
32+
33+
private static Logger logger = LoggerFactory.getLogger(ExcelFormat.class);
34+
35+
/**
36+
* 解析excel,默认第一行为表头
37+
*
38+
* @param type excel类型
39+
* @param excel excel输入流
40+
* @param tClass 要生成的实体类
41+
* @param headerMapper 对应解析表格
42+
* @return 解析后集合
43+
* @throws IllegalArgumentException 解析错误
44+
*/
45+
public static <T> List<T> extractFrom(ExcelType type, InputStream excel, Class<T> tClass,
46+
Map<String, String> headerMapper, Integer sheetIndex) throws IllegalArgumentException, IOException {
47+
Workbook workbook;
48+
try {
49+
workbook = type.equals(ExcelType.XLS) ? new HSSFWorkbook(excel) : new XSSFWorkbook(excel);
50+
} catch (IOException e) {
51+
throw new IllegalArgumentException("未知的表格类型");
52+
}
53+
//解析表头.默认第一行为表头
54+
Sheet sheet = workbook.getSheetAt(sheetIndex);
55+
Iterator<Row> rowIterator = sheet.rowIterator();
56+
//第一行
57+
Row firstRow = rowIterator.next();
58+
Iterator<Cell> firstCell = firstRow.cellIterator();
59+
60+
Set<String> existHeaderSet = new HashSet<>();
61+
List<String> existHeader = new ArrayList<>(headerMapper.size());
62+
firstCell.forEachRemaining(cell -> {
63+
if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
64+
throw new IllegalArgumentException("第一行必须全部为字符串,第" + cell.getColumnIndex() + 1 + "有问题");
65+
}
66+
existHeaderSet.add(cell.getStringCellValue());
67+
existHeader.add(cell.getStringCellValue());
68+
});
69+
//查询出不存在的表头
70+
for (String s : headerMapper.keySet()) {
71+
if (!existHeaderSet.contains(s)) {
72+
throw new IllegalArgumentException("不存在的表头:" + s);
73+
}
74+
}
75+
//开始解析表单
76+
List<T> result = new ArrayList<T>();
77+
rowIterator.forEachRemaining(x -> {
78+
Map<String, Object> tempMap = new HashMap<>();
79+
Iterator<Cell> cellIterator = x.cellIterator();
80+
cellIterator.forEachRemaining(y -> {
81+
Object value = null;
82+
switch (y.getCellType()) {
83+
case Cell.CELL_TYPE_STRING:
84+
value = y.getStringCellValue();
85+
break;
86+
case Cell.CELL_TYPE_BOOLEAN:
87+
value = y.getBooleanCellValue();
88+
break;
89+
case Cell.CELL_TYPE_NUMERIC:
90+
value = y.getNumericCellValue();
91+
break;
92+
case Cell.CELL_TYPE_ERROR:
93+
value = "";
94+
break;
95+
case Cell.CELL_TYPE_BLANK:
96+
value = "";
97+
break;
98+
default:
99+
value = "";
100+
}
101+
String key = headerMapper.get(existHeader.get(y.getColumnIndex()));
102+
tempMap.put(key, value);
103+
});
104+
result.add(BeanUtils.map2Bean(tempMap, tClass));
105+
});
106+
return result;
107+
}
108+
}

‎excel-Demo/src/main/java/cn/mrdear/excel/core/Excel.java renamed to ‎Excel-Demo/src/main/java/cn/mrdear/excel/core/ExcelFormat.java

Lines changed: 27 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -1,24 +1,35 @@
11
package cn.mrdear.excel.core;
22

3-
import cn.mrdear.excel.util.BeanUtils;
4-
import com.sun.tools.javac.comp.Todo;
53
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
64
import org.apache.poi.ss.usermodel.Cell;
75
import org.apache.poi.ss.usermodel.Row;
86
import org.apache.poi.ss.usermodel.Sheet;
97
import org.apache.poi.ss.usermodel.Workbook;
108
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
11-
12-
import java.io.*;
13-
import java.util.*;
9+
import org.slf4j.Logger;
10+
import org.slf4j.LoggerFactory;
11+
12+
import java.io.File;
13+
import java.io.FileOutputStream;
14+
import java.io.IOException;
15+
import java.io.OutputStream;
16+
import java.util.ArrayList;
17+
import java.util.Date;
18+
import java.util.LinkedHashMap;
19+
import java.util.List;
20+
import java.util.Map;
21+
22+
import cn.mrdear.excel.constant.ExcelType;
23+
import cn.mrdear.excel.util.BeanUtils;
1424

1525
/**
1626
* 工具类入口
1727
* @author Niu Li
1828
* @since 2017/2/23
1929
*/
20-
public class Excel {
30+
public class ExcelFormat {
2131

32+
private static Logger logger = LoggerFactory.getLogger(ExcelFormat.class);
2233
//该表格的工作本
2334
private Workbook workbook;
2435

@@ -37,8 +48,8 @@ public class Excel {
3748
* @param content 表单内容DTO
3849
* @return this表单对象
3950
*/
40-
public static Excel from(LinkedHashMap<String,String> headers,List<?> content){
41-
return new Excel(headers,content);
51+
public static ExcelFormat from(LinkedHashMap<String,String> headers,List<?> content){
52+
return new ExcelFormat(headers,content);
4253
}
4354

4455
/**
@@ -47,7 +58,7 @@ public static Excel from(LinkedHashMap<String,String> headers,List<?> content){
4758
* @param content 新sheet的内容
4859
* @return this
4960
*/
50-
public Excel andForm(LinkedHashMap<String,String> headers,List<?> content){
61+
public ExcelFormat andForm(LinkedHashMap<String,String> headers,List<?> content){
5162
this.headers = headers;
5263
this.contents = content;
5364
return this;
@@ -58,7 +69,7 @@ public Excel andForm(LinkedHashMap<String,String> headers,List<?> content){
5869
* 端点方法,生成最终的表单
5970
* @return this
6071
*/
61-
public Excel build(String sheetName){
72+
public ExcelFormat build(String sheetName){
6273
//创建字表
6374
Sheet sheet = sheetName == null ? workbook.createSheet() : workbook.createSheet(sheetName);
6475
//创建表头
@@ -99,8 +110,7 @@ public Excel build(String sheetName){
99110
}
100111
}
101112
} catch (IllegalAccessException e) {
102-
// todo 替换为自己项目的日志
103-
e.printStackTrace();
113+
logger.error("parse excel fail ",e);
104114
}
105115
//设置样式
106116

@@ -109,7 +119,7 @@ public Excel build(String sheetName){
109119
/**
110120
* 私有化构造函数
111121
*/
112-
private Excel(LinkedHashMap<String, String> headers, List<?> contents) {
122+
private ExcelFormat(LinkedHashMap<String, String> headers, List<?> contents) {
113123
this.headers = headers;
114124
this.contents = contents;
115125
}
@@ -125,17 +135,16 @@ public void write(String dirIncludedFileName){
125135
workbook.write(os);
126136
os.close();
127137
} catch (IOException e) {
128-
e.printStackTrace();
138+
logger.error("write excel fail ",e);
129139
}finally {
130140
try {
131141
if (workbook != null) workbook.close();
132142
} catch (IOException e) {
133-
e.printStackTrace();
143+
logger.error("write excel fail ",e);
134144
}
135145
}
136146
}
137147

138-
// todo 测试在写入后关闭对其的影响
139148
/**
140149
* 结果写到一个输出流中
141150
* @param os 目标流
@@ -145,7 +154,7 @@ public void write(OutputStream os) {
145154
workbook.write(os);
146155
workbook.close();
147156
} catch (IOException e) {
148-
e.printStackTrace();
157+
logger.error("write excel fail ",e);
149158
}
150159
}
151160

@@ -155,17 +164,9 @@ public void write(OutputStream os) {
155164
* @param type 指定格式
156165
* @return this
157166
*/
158-
public Excel excelType(ExcelType type){
167+
public ExcelFormat excelType(ExcelType type){
159168
workbook = type == ExcelType.XLS?new HSSFWorkbook():new XSSFWorkbook();
160169
return this;
161170
}
162171

163-
/**
164-
* 定义excel格式
165-
*/
166-
public static enum ExcelType{
167-
XLS,
168-
XLSX
169-
}
170-
171172
}

‎Excel-Demo/src/main/java/cn/mrdear/excel/util/BeanUtils.java

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
package cn.mrdear.excel.util;
2+
3+
import java.lang.reflect.Field;
4+
import java.util.HashMap;
5+
import java.util.Map;
6+
7+
/**
8+
* 对bean一些转换方法
9+
*
10+
* @author Niu Li
11+
* @since 2017/2/23
12+
*/
13+
public class BeanUtils {
14+
15+
/**
16+
* 转换bean为map
17+
*
18+
* @param source 要转换的bean
19+
* @param <T> bean类型
20+
* @return 转换结果
21+
*/
22+
public static <T> Map<String, Object> bean2Map(T source) throws IllegalAccessException {
23+
Map<String, Object> result = new HashMap<>();
24+
25+
Class<?> sourceClass = source.getClass();
26+
//拿到所有的字段,不包括继承的字段
27+
Field[] sourceFiled = sourceClass.getDeclaredFields();
28+
for (Field field : sourceFiled) {
29+
field.setAccessible(true);//设置可访问,不然拿不到private
30+
//配置了注解的话则使用注解名称,作为header字段
31+
FieldName fieldName = field.getAnnotation(FieldName.class);
32+
if (fieldName == null) {
33+
result.put(field.getName(), field.get(source));
34+
} else {
35+
if (fieldName.Ignore()) continue;
36+
result.put(fieldName.value(), field.get(source));
37+
}
38+
}
39+
return result;
40+
}
41+
42+
/**
43+
* map转bean
44+
* @param source map属性
45+
* @param instance 要转换成的备案
46+
* @return 该bean
47+
*/
48+
public static <T> T map2Bean(Map<String, Object> source, Class<T> instance) {
49+
try {
50+
T object = instance.newInstance();
51+
Field[] fields = object.getClass().getDeclaredFields();
52+
for (Field field : fields) {
53+
field.setAccessible(true);
54+
FieldName fieldName = field.getAnnotation(FieldName.class);
55+
if (fieldName != null){
56+
field.set(object,source.get(fieldName.value()));
57+
}else {
58+
field.set(object,source.get(field.getName()));
59+
}
60+
}
61+
return object;
62+
} catch (InstantiationException | IllegalAccessException e) {
63+
e.printStackTrace();
64+
}
65+
return null;
66+
}
67+
}
68+

‎excel-Demo/src/main/java/cn/mrdear/excel/util/FiledName.java renamed to ‎Excel-Demo/src/main/java/cn/mrdear/excel/util/FieldName.java

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
*/
1313
@Retention(RetentionPolicy.RUNTIME)
1414
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.PARAMETER})
15-
public @interface FiledName {
15+
public @interface FieldName {
1616
/**
1717
* 字段名
1818
*/

0 commit comments

Comments
 (0)