通用Excel导出实现（JAVA）
参考sf.json的实现机制，实现了一个比较简单通用的Excel导出类。
用到的外部包是jxl(jxl-2.6.10.jar)
主要有Excel导出工具类以及几个辅助的配置类。
代码
1 package gvitech.util;
2
"color: #008080;"> 3 /**
4 * 导出Excel的一个工具类
5 * @author huihui
6 *
7 */
8 import java.io.IOException;
9 import java.io.OutputStream;
10 import java.io.UnsupportedEncodingException;
11 import java.lang.reflect.Field;
12 import java.text.SimpleDateFormat;
13 import java.util.Date;
14 import java.util.Iterator;
15 import java.util.LinkedHashMap;
16 import java.util.List;
17 import java.util.Map;
18 import java.util.Set;
19 import javax.servlet.http.HttpServletResponse;
20 import gvitech.util.PropertyFilter;
21 import jxl.write.WritableSheet;
22 import jxl.write.WritableWorkbook;
23 import jxl.write.WriteException;
24 import jxl.write.biff.RowsExceededException;
25
26 public class ExcelUtils {
27
28 private HttpServletResponse response;
29 private String fileName;
30 private PropertyFilter filter;
31 private Map<String,Map<String,String>> propertyMap;
32
33 public ExcelUtils(HttpServletResponse response){
34 this.response = response;
35 this.fileName = getFileName();
36 }
37
38 public ExcelUtils(HttpServletResponse response,PropertyFilter filter){
39 this.response = response;
40 this.fileName = getFileName();
41lt;/span> thisgt;.filter = filter;
42 }
43
44 /**
45 *
46 * @param response
47 * @param propertyMap 这是一个属性对照表，在这种情景下有用：
48 * 领域模型中比如1，2，3，4，需要显示出实际代表的真实意义。
49 */
50 public ExcelUtils(HttpServletResponse response,Map<String,Map<String,String>> propertyMap){
51 this.response = response;
52 this.fileName = getFileName();
53 this.propertyMap = propertyMap;
54 }
55
56 public ExcelUtils( HttpServletResponse response,PropertyFilter filter,
57 Map<String,Map<String,String>> propertyMap){
58 this.response = response;
59 this.fileName = getFileName();
60 this.filter = filter;
61 this.propertyMap = propertyMap;
62 }
63
64
65 public ExcelUtils(HttpServletResponse response,String fileName){
66 this.response = response;
67 this.fileName = fileName;
68 }
69
70 public ExcelUtils(HttpServletResponse response,String fileName,PropertyFilter filter){
71 this.response = response;
72 this.fileName = fileName;
73 this.filter = filter;
74 }
75
76 public ExcelUtils(HttpServletResponse response,String fileName,
77 Map<String,Map<String,String>> propertyMap){
78 this.response = response;
79 this.fileName = fileName;
80 this.propertyMap = propertyMap;
81 }
82
83 public ExcelUtils(HttpServletResponse response, String fileName,
84 PropertyFilter filter, Map<String, Map<String, String>> propertyMap) {
85 this.response = response;
86 this.fileName = fileName;
87 this.propertyMap = propertyMap;
88 this.filter = filter;
89 }
90
91 /**
92 * 把集合对象写入到excel
93 * @param list 要写入的集合
94 */
95 @SuppressWarnings("unchecked")
96 public void write(List list) throws Exception {
97 String format = "yyyy-MM-dd HH:mm:ss";
98 write(list,format);
99 }
100
101 /**
102 * 把集合对象写入到excel
103 * @param list 要写入的集合
104 * @param dateFormat 时间格式
105 */
106 @SuppressWarnings("unchecked")
107 public void write(List list, String dateFormat) throws Exception {
108 setExcelContentType();
109 WritableWorkbook wwbook = null;
110 OutputStream os = null;
111 try {
112 os = response.getOutputStream();
113 wwbook = jxl.Workbook.createWorkbook(os);
114 WritableSheet wsheet = wwbook.createSheet("Sheet1", 0);
115 Map<Field, String> map = getFields(list);
116 Set<Field> fields = map.keySet();
117 int columnIndex = 0;
118 Map<Field, Map<String, String>> writeField =
119 new LinkedHashMap<Field, Map<String, String>>();//准备输出的列
120
121 for (Iterator<Field> field = fields.iterator(); field.hasNext();) {// 添加表头
122 Field f = field.next();
123 if(filter !=null){//进行字段过滤处理
124 if(!filter.apply(f))
125 continue;
126 }
127 Map<String, String> proMap = null;
128 if(propertyMap != null && propertyMap.containsKey(f.getName())){
129 proMap = propertyMap.get(f.getName());
130 }
131 writeField.put(f, proMap);//把需要输出的列添加到Map中
132 jxl.write.Label titleCell = new jxl.write.Label(columnIndex,
133 0, map.get(f));
134 wsheet.addCell(titleCell);
135 wsheet.setColumnView(columnIndex, 25);
136 columnIndex++;
137 }
138
139 if (list != null) {
140 String methodName;
141 int rowIndex = 0;
142 for (Object o : list) {
143 rowIndex++;
144 columnIndex = 0;
145 for (Field f : writeField.keySet()) {// 添加行记录
146 methodName = "get"+ toFirstLetterUpperCase(f.getName());
147 Object value = o.getClass().getMethod(methodName).invoke(o);
148 String cellValue = (value != null) ? value.toString(): "";
149 Map<String, String> proMap = writeField.get(f);
150 if(proMap != null){//如果有属性对照表，设置属性对照表
151 if(proMap.containsKey(cellValue)){
152 cellValue = proMap.get(cellValue);
153 }
154 }
155 if (value instanceof Date) {//对时间格式做格式化输出
156 SimpleDateFormat sf = new SimpleDateFormat(dateFormat);
157 Date date = (Date) value;
158 cellValue = sf.format(date);
159 }
160 jxl.write.Label valueCell = new jxl.write.Label(
161 columnIndex++, rowIndex, cellValue);
162 wsheet.addCell(valueCell);//创建、添加Excel单元格
163 }
164 }
165 }
166 wwbook.write();
167 } catch (IOException e) {
168 e.printStackTrace();
169 } catch (RowsExceededException e) {
170 e.printStackTrace();
171 } catch (WriteException e) {
172 e.printStackTrace();
173 } catch (IllegalArgumentException e) {
174 e.printStackTrace();
175 } finally {
176 try {
177 wwbook.close();
178 os.close();
179 } catch (IOException ie) {
180 ie.printStackTrace();
181 } catch (WriteException e) {
182 e.printStackTrace();
183 }
184 }
185 }
186
187 /**获取集合中对象的属性字段。认为list中的所有对象和list中的第一个对象是同一类对象*/
188 @SuppressWarnings("unchecked")
189 private Map<Field, String> getFields(List list) {
190 Map<Field, String> map = new LinkedHashMap<Field, String>();
191 if(list == null){//如果list为null，则输出一个空白的excel
192 return map;
193 }
194 Class c = list.get(0).getClass();
195 Field[] fields = c.getDeclaredFields();
196 if (fields.length == 0)
197 return null;
198 for (Field field : fields) {
199 if (field.isAnnotationPresent(ExcelAnno.class)) {
200 ExcelAnno anno = field.getAnnotation(ExcelAnno.class);
201 map.put(field, anno.value());
202 } else {
203 map.put(field, field.getName());
204 }
205 }
206 return map;
207 }
208
209 /**
210 * 设置头信息
211 *
212 * @param response
213 * HttpServletResponse
214 * @param fileName
215 * 默认的文件名称
216 */
217 private void setExcelContentType() {
218 try {
219 fileName = new String(fileName.getBytes("utf-8"), "ISO-8859-1");
220 } catch (UnsupportedEncodingException e) {
221 }
222 response.reset();
223 response.setContentType("application/msexcel;charset=utf-8");
224 response.setHeader("Content-disposition",
225 "attachment;filename= "+ fileName);
226 }
227
228
229 /**产生一个以当前服务器时间的文件名称*/
230 private String getFileName() {
231 SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss");
232 StringBuilder sb = new StringBuilder();
233 sb.append(sf.format(System.currentTimeMillis()));
234 sb.append(".xls");
235 return sb.toString();
236 }
237
238 /** 首字母大写 */
239 private String toFirstLetterUpperCase(String str) {
240 if (str == null || str.length() < 2) {
241 return str;
242 }
243 String firstLetter = str.substring(0, 1).toUpperCase();
244 return firstLetter + str.substring(1, str.length());
245 }
246
247
248 }
249
1 package gvitech.util;
2
3 import java.lang.reflect.Field;
4
5 public interface PropertyFilter {
6 /**
7 *
8 * @param filed 字段
9 * @return
10 */
11 boolean apply(Field filed);
12 }
代码
package gvitech.util;
import gvitech.util.PropertyFilter;
import java.lang.reflect.Field;
import java.util.Collection;
import java.util.Set;
import antlr.collections.List;
public class IgnoreFieldImpl implements PropertyFilter {
/**
* 忽略的属性名称
*/
private String[] fields;
/**
* 是否忽略集合
*/
private boolean ignoreColl = false;
/**
* 空参构造方法<br/>
* 默认不忽略集合
*/
public IgnoreFieldImpl() {
// empty
}
/**
* 构造方法
*
* @param fields
* 忽略属性名称数组
*/
public IgnoreFieldImpl(String[] fields) {
this.fields = fields;
}
/**
* 构造方法
*
* @param ignoreColl
* 是否忽略集合
* @param fields
* 忽略属性名称数组
*/
public IgnoreFieldImpl(boolean ignoreColl, String[] fields) {
this.fields = fields;
this.ignoreColl = ignoreColl;
}
/**
* 构造方法
*
* @param ignoreColl
* 是否忽略集合
*/
public IgnoreFieldImpl(boolean ignoreColl) {
this.ignoreColl = ignoreColl;
}
@SuppressWarnings("unchecked")
public boolean apply(Field filed) {
if (ignoreColl) {
Class c = filed.getType();
if(c.isInterface()){
if (Set.class.isAssignableFrom( c ) || List.class.isAssignableFrom(c)){
return false;
}
}
if (c.isArray() || Collection.class.isAssignableFrom( c )){
return false;
}
}
if (fields == null || fields.length == 0)
return true;
for (String s : fields) {
if (s.equals(filed.getName())) {
return false;
}
}
return true;
}
public String[] getFields() {
return fields;
}
/**
* 设置忽略的属性
*
* @param fields
*/
public void setFields(String[] fields) {
this.fields = fields;
}
public boolean isIgnoreColl() {
return ignoreColl;
}
/**
* 设置是否忽略集合类
*
* @param ignoreColl
*/
public void setIgnoreColl(boolean ignoreColl) {
this.ignoreColl = ignoreColl;
}
}
代码
package gvitech.util;
import java.lang.reflect.Field;
import java.util.Collection;
import java.util.Set;
import antlr.collections.List;
import gvitech.util.PropertyFilter;
public class AttendFieldImpl implements PropertyFilter {
/**
* 需要输出的属性名称
*/
private String[] fields;
/**
* 是否忽略集合
*/
private boolean ignoreColl = false;
/**
* 空参构造方法<br/>
* 默认不忽略集合
*/
public AttendFieldImpl() {
// empty
}
/**
* 构造方法
*
* @param fields
* 需要输出的属性名称数组
*/
public AttendFieldImpl(String[] fields) {
this.fields = fields;
}
/**
* 构造方法
*
* @param ignoreColl
* 是否忽略集合
* @param fields
* 需要输出的属性名称数组
*/
public AttendFieldImpl(boolean ignoreColl, String[] fields) {
this.fields = fields;
this.ignoreColl = ignoreColl;
}
/**
* 构造方法
*
* @param ignoreColl
* 是否忽略集合
*/
public AttendFieldImpl(boolean ignoreColl) {
this.ignoreColl = ignoreColl;
}
@SuppressWarnings("unchecked")
public boolean apply(Field filed) {
if (ignoreColl) {
Class c = filed.getType();
if(c.isInterface()){
if (Set.class.isAssignableFrom( c ) || List.class.isAssignableFrom(c)){
return false;
}
}
if (c.isArray() || Collection.class.isAssignableFrom( c )){
return false;
}
}
if (fields == null || fields.length == 0)//如果没有设置需要输出的字段项，则全部输出。
return true;
boolean isPass = false;
for (String s : fields) {
if (s.equals(filed.getName())) {
isPass = true;
break;
}
}
return isPass;
}
public String[] getFields() {
return fields;
}
/**
* 设置需要输出的属性
*
* @param fields
*/
public void setFields(String[] fields) {
this.fields = fields;
}
public boolean isIgnoreColl() {
return ignoreColl;
}
/**
* 设置是否忽略集合类
*
* @param ignoreColl
*/
public void setIgnoreColl(boolean ignoreColl) {
this.ignoreColl = ignoreColl;
}
}
使用：
代码
1 @SuppressWarnings("unchecked")
2 public String exportExcel(){
3 HttpServletResponse response = ServletActionContext.getResponse();
4 List<Role> list = DSA.roleService.getAllRoles();
5 Map<String,Map<String,String>> propertyMap = new HashMap<String,Map<String,String>> ();
6 Map<String,String> proMap = new HashMap<String,String>();
7 proMap.put("1", "huihui");
8 proMap.put("15", "aaaa");
9 proMap.put("16", "bbbb");
10 proMap.put("17", "cccc");
11 propertyMap.put("id", proMap);
12 PropertyFilter filter = new AttendFieldImpl(true,new String[]{"id","name"});
13 ExcelUtils utils = new ExcelUtils(response,filter,propertyMap);
14 try{
15 utils.write(list);
16 }catch(Exception e){
17
18 }
19 return null;
20 }
以上是 通用Excel导出实现（JAVA） 的全部内容， 来源链接： utcz.com/z/392977.html
