Skip to content

Commit 229f5fe

Browse files
author
quding
committed
add excelDemo
1 parent 22c8c69 commit 229f5fe

File tree

7 files changed

+375
-0
lines changed

7 files changed

+375
-0
lines changed

‎excel-Demo/pom.xml

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<project xmlns="http://maven.apache.org/POM/4.0.0"
3+
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4+
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
5+
<modelVersion>4.0.0</modelVersion>
6+
7+
<groupId>cn.mrdear.poi</groupId>
8+
<artifactId>poi</artifactId>
9+
<version>1.0-SNAPSHOT</version>
10+
11+
<dependencies>
12+
<!--poi start-->
13+
<dependency>
14+
<groupId>org.apache.poi</groupId>
15+
<artifactId>poi</artifactId>
16+
<version>3.15</version>
17+
</dependency>
18+
<dependency>
19+
<groupId>org.apache.poi</groupId>
20+
<artifactId>poi-ooxml</artifactId>
21+
<version>3.15</version>
22+
</dependency>
23+
<dependency>
24+
<groupId>junit</groupId>
25+
<artifactId>junit</artifactId>
26+
<version>4.10</version>
27+
</dependency>
28+
</dependencies>
29+
30+
31+
<build>
32+
<plugins>
33+
<!--编译版本-->
34+
<plugin>
35+
<groupId>org.apache.maven.plugins</groupId>
36+
<artifactId>maven-compiler-plugin</artifactId>
37+
<configuration>
38+
<source>1.8</source>
39+
<target>1.8</target>
40+
</configuration>
41+
</plugin>
42+
</plugins>
43+
</build>
44+
</project>

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

Lines changed: 171 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,171 @@
1+
package cn.mrdear.excel.core;
2+
3+
import cn.mrdear.excel.util.BeanUtils;
4+
import com.sun.tools.javac.comp.Todo;
5+
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
6+
import org.apache.poi.ss.usermodel.Cell;
7+
import org.apache.poi.ss.usermodel.Row;
8+
import org.apache.poi.ss.usermodel.Sheet;
9+
import org.apache.poi.ss.usermodel.Workbook;
10+
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
11+
12+
import java.io.*;
13+
import java.util.*;
14+
15+
/**
16+
* 工具类入口
17+
* @author Niu Li
18+
* @since 2017/2/23
19+
*/
20+
public class Excel {
21+
22+
//该表格的工作本
23+
private Workbook workbook;
24+
25+
/**
26+
* 控制表头,其中键为对应DTO的字段,值为表头显示内容
27+
*/
28+
private LinkedHashMap<String,String> headers;
29+
/**
30+
* 具体表内容,只接受DTO
31+
*/
32+
private List<?> contents;
33+
34+
/**
35+
* 入口函数
36+
* @param headers 表单头部
37+
* @param content 表单内容DTO
38+
* @return this表单对象
39+
*/
40+
public static Excel from(LinkedHashMap<String,String> headers,List<?> content){
41+
return new Excel(headers,content);
42+
}
43+
44+
/**
45+
* 在此workbook中增加另一个sheet
46+
* @param headers 新sheet的表头
47+
* @param content 新sheet的内容
48+
* @return this
49+
*/
50+
public Excel andForm(LinkedHashMap<String,String> headers,List<?> content){
51+
this.headers = headers;
52+
this.contents = content;
53+
return this;
54+
}
55+
56+
57+
/**
58+
* 端点方法,生成最终的表单
59+
* @return this
60+
*/
61+
public Excel build(String sheetName){
62+
//创建字表
63+
Sheet sheet = sheetName == null ? workbook.createSheet() : workbook.createSheet(sheetName);
64+
//创建表头
65+
int rowNum = 0;
66+
Row headerRow = sheet.createRow(rowNum++);
67+
List<String> headers = new ArrayList<>(this.headers.keySet());//表头
68+
List<String> values = new ArrayList<>(this.headers.values());//对应值
69+
for (int i = 0; i < headers.size(); i++) {
70+
Cell cell = headerRow.createCell(i);
71+
cell.setCellValue(values.get(i)==null?headers.get(i):values.get(i));
72+
}
73+
//构造表单内容
74+
try {
75+
for (Object content : contents) {
76+
Map<String,Object> contentMap = BeanUtils.bean2Map(content);
77+
Row current = sheet.createRow(rowNum++);
78+
for (int i = 0; i < headers.size(); i++) {
79+
Cell cell = current.createCell(i);
80+
Object obj = contentMap.get(headers.get(i));
81+
if (obj == null) {
82+
obj = "";
83+
}
84+
if (obj instanceof String) {
85+
cell.setCellValue((String) obj);
86+
} else if (obj instanceof Integer) {
87+
cell.setCellValue((Integer) obj);
88+
} else if (obj instanceof Long) {
89+
cell.setCellValue((Long) obj);
90+
} else if (obj instanceof Double) {
91+
cell.setCellValue((Double) obj);
92+
} else if (obj instanceof Date) {
93+
cell.setCellValue((Date) obj);
94+
} else if (obj instanceof Boolean) {
95+
cell.setCellValue((Boolean) obj);
96+
} else {
97+
throw new IllegalArgumentException("unsupported cell type");
98+
}
99+
}
100+
}
101+
} catch (IllegalAccessException e) {
102+
// todo 替换为自己项目的日志
103+
e.printStackTrace();
104+
}
105+
//设置样式
106+
107+
return this;
108+
}
109+
/**
110+
* 私有化构造函数
111+
*/
112+
private Excel(LinkedHashMap<String, String> headers, List<?> contents) {
113+
this.headers = headers;
114+
this.contents = contents;
115+
}
116+
117+
/**
118+
* 调用该方法后,此workbook则写入关闭
119+
* @param dirIncludedFileName 最终生成文件名称
120+
*/
121+
public void write(String dirIncludedFileName){
122+
try {
123+
File file = new File(dirIncludedFileName);
124+
FileOutputStream os = new FileOutputStream(file);
125+
workbook.write(os);
126+
os.close();
127+
} catch (IOException e) {
128+
e.printStackTrace();
129+
}finally {
130+
try {
131+
if (workbook != null) workbook.close();
132+
} catch (IOException e) {
133+
e.printStackTrace();
134+
}
135+
}
136+
}
137+
138+
// todo 测试在写入后关闭对其的影响
139+
/**
140+
* 结果写到一个输出流中
141+
* @param os 目标流
142+
*/
143+
public void write(OutputStream os) {
144+
try {
145+
workbook.write(os);
146+
workbook.close();
147+
} catch (IOException e) {
148+
e.printStackTrace();
149+
}
150+
}
151+
152+
153+
/**
154+
* 链式调用设置生成文档格式
155+
* @param type 指定格式
156+
* @return this
157+
*/
158+
public Excel excelType(ExcelType type){
159+
workbook = type == ExcelType.XLS?new HSSFWorkbook():new XSSFWorkbook();
160+
return this;
161+
}
162+
163+
/**
164+
* 定义excel格式
165+
*/
166+
public static enum ExcelType{
167+
XLS,
168+
XLSX
169+
}
170+
171+
}

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

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
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+
* @author Niu Li
10+
* @since 2017/2/23
11+
*/
12+
public class BeanUtils {
13+
14+
/**
15+
* 转换bean为map
16+
* @param source 要转换的bean
17+
* @param <T> bean类型
18+
* @return 转换结果
19+
*/
20+
public static <T> Map<String,Object> bean2Map(T source) throws IllegalAccessException {
21+
Map<String,Object> result = new HashMap<>();
22+
23+
Class<?> sourceClass = source.getClass();
24+
//拿到所有的字段,不包括继承的字段
25+
Field[] sourceFiled = sourceClass.getDeclaredFields();
26+
for (Field field : sourceFiled) {
27+
field.setAccessible(true);//设置可访问,不然拿不到private
28+
//配置了注解的话则使用注解名称,作为header字段
29+
FiledName filedName = field.getAnnotation(FiledName.class);
30+
if (filedName == null){
31+
result.put(field.getName(),field.get(source));
32+
}else {
33+
if (filedName.Ignore()) continue;
34+
result.put(filedName.value(),field.get(source));
35+
}
36+
}
37+
return result;
38+
}
39+
40+
}
41+

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

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
package cn.mrdear.excel.util;
2+
3+
import java.lang.annotation.ElementType;
4+
import java.lang.annotation.Retention;
5+
import java.lang.annotation.RetentionPolicy;
6+
import java.lang.annotation.Target;
7+
8+
/**
9+
* 自定义字段名
10+
* @author Niu Li
11+
* @since 2017/2/23
12+
*/
13+
@Retention(RetentionPolicy.RUNTIME)
14+
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.PARAMETER})
15+
public @interface FiledName {
16+
/**
17+
* 字段名
18+
*/
19+
String value() default "";
20+
/**
21+
* 是否忽略
22+
*/
23+
boolean Ignore() default false;
24+
}

‎excel-Demo/src/test/java/cn/mrdear/excel/BeanUtilTest.java

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
package cn.mrdear.excel;
2+
3+
import cn.mrdear.excel.util.BeanUtils;
4+
5+
/**
6+
* @author Niu Li
7+
* @since 2017/2/23
8+
*/
9+
public class BeanUtilTest {
10+
public static void main(String[] args) throws IllegalAccessException {
11+
Demo demo = new Demo();
12+
demo.setUserName("111");
13+
demo.setPassWord("222");
14+
System.out.println(BeanUtils.bean2Map(demo));
15+
}
16+
}

‎excel-Demo/src/test/java/cn/mrdear/excel/Demo.java

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
package cn.mrdear.excel;
2+
3+
import cn.mrdear.excel.util.FiledName;
4+
5+
/**
6+
* @author Niu Li
7+
* @since 2017/2/23
8+
*/
9+
class Demo {
10+
11+
public Demo(String username, String password) {
12+
this.userName = username;
13+
this.passWord = password;
14+
}
15+
public Demo() {
16+
}
17+
//fildName字段需要和header对应
18+
@FiledName(value = "username")
19+
private String userName;
20+
private String passWord;
21+
22+
public String getUserName() {
23+
return userName;
24+
}
25+
26+
public void setUserName(String userName) {
27+
this.userName = userName;
28+
}
29+
30+
public String getPassWord() {
31+
return passWord;
32+
}
33+
34+
public void setPassWord(String passWord) {
35+
this.passWord = passWord;
36+
}
37+
}

‎excel-Demo/src/test/java/cn/mrdear/excel/ExcelTest.java

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
package cn.mrdear.excel;
2+
3+
import cn.mrdear.excel.core.Excel;
4+
import org.junit.Before;
5+
import org.junit.Test;
6+
7+
import java.util.ArrayList;
8+
import java.util.LinkedHashMap;
9+
import java.util.List;
10+
11+
/**
12+
* @author Niu Li
13+
* @since 2017/2/23
14+
*/
15+
public class ExcelTest {
16+
LinkedHashMap<String,String> headers;
17+
18+
@Before
19+
public void init() {
20+
headers = new LinkedHashMap<>();
21+
headers.put("username","用户名");
22+
headers.put("passWord", "密码");
23+
}
24+
25+
@Test
26+
public void testMake() {
27+
List<Demo> content = new ArrayList<>();
28+
Demo demo1 = new Demo("1","1");
29+
Demo demo2 = new Demo("2","2");
30+
Demo demo3 = new Demo("3","3");
31+
Demo demo4 = new Demo("4","4");
32+
content.add(demo1);
33+
content.add(demo2);
34+
content.add(demo3);
35+
content.add(demo4);
36+
37+
Excel.from(headers,content)
38+
.excelType(Excel.ExcelType.XLS)
39+
.build("niuli")
40+
.write("/tmp/niuli.xls");
41+
}
42+
}

0 commit comments

Comments
 (0)