一.JXL概述
在java中,使用jxl操作excel是一个很不错的选择,当然你也可以选择POI之类的,个人感觉POI比较重,jxl在的API在使用逻辑和易操作性上都很不错,但是jxl在功能上没有POI强大,对于图形和图表的识别能力有限
二.下面是一个使用JXL写的一个工具类(可用于简单的导出功能)
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.log4j.Logger;
@SuppressWarnings("unchecked")
public class ExcelExportor {
private static Logger log=Logger.getLogger(ExcelExportor.class);
// 创建excel的工作区域
private WritableWorkbook wwb;
// 创建excel工作区域中的页面
private WritableSheet ws;
// 设置单元格的字体样式
private WritableFont wf = new WritableFont(WritableFont.ARIAL, 14,
WritableFont.BOLD);
// 初始化格式化类
private WritableCellFormat wcf = new WritableCellFormat(wf);
private OutputStream os;
private ExcelExportor(OutputStream os){
this.os=os;
}
public static ExcelExportor getInstance(HttpServletResponse response,String fileName){
ServletOutputStream sos=null;
try{
response.reset();
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode(fileName,"UTF-8"));
sos=response.getOutputStream();
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}
return new ExcelExportor(sos);
}
public static ExcelExportor getInstance(OutputStream os){
return new ExcelExportor(os);
}
public void export(List data)throws Exception{
export(data,null,null);
}
public void export(List data,String[][]header)throws Exception{
export(data,header,null);
}
/**
*
* @param data 需要导出的数据,为List类型,可以为Bean也可以为Map类型
* @param header Map或Bean对应的字段名数据格式为
* {{"字段名1","中文名1"},{"字段名2","中文名2"},{"字段名3","中文名3"}}
* @param sheetName 导出excel时下面页签的名称
* @throws Exception
*/
public void export(List data,String[][]header,String sheetName)throws Exception{
// 设置生成excel后导出的流类
wwb = Workbook.createWorkbook(os);
Map datas=new HashMap();datas.put("sheet1",data);
Map headers=new HashMap();headers.put("sheet1",header);
Map steetNames=new HashMap();steetNames.put("sheet1",sheetName);
export(datas, headers, steetNames);
}
/**
* list中存放的对象可以是bean也可以是Map类型的对象 header为设置excel的标题头部
*
* @param list
* @param header
* @return
* @throws Exception
*/
public void export(Map> datas,Map headers,Map steetNames)
throws Exception {
Set keys=datas.keySet();
Iterator it=keys.iterator();
long start=System.currentTimeMillis();
while(it.hasNext()){
String key=it.next().toString();
//根据key获取sheetName
String sheetName=(String)steetNames.get(key);
//根据key获取需要导出的数据集合
List list=(List)datas.get(key);
//根据key获取需要导出的header名称
String[][] header=(String[][])headers.get(key);
if(sheetName==null||sheetName.equals("")){
log.warn(key+"所对应的sheetName未设定,默认使用"+key+"的名称!");
sheetName=key;
}
ws = wwb.createSheet(sheetName, 1);
if (list == null||list.size()==0){
log.warn(key+"所对应的数据集合为空!");
continue;
}
if(header==null){
log.warn(key+"所对应的header未设定,默认使用字段名做导出标题");
}
if (list.get(0) instanceof Map)
praseMap(list, header);
else
praseBean(list, header);
}
long end=System.currentTimeMillis();
log.info("导出数据总共耗时:"+(end-start)+"ms");
wwb.write();
wwb.close();
}
// 解析生成类型为Map类型的excel
// 如果不设置头部,默认使用键值,like:{{'key1','标题1'},{'key2','标题2'},....}
private void praseMap(List list, String[][] header) throws Exception {
for (int i = 0; i < list.size(); i++) {
Map map = (Map) list.get(i);
Set keyset = map.keySet();
Object[] keys = keyset.toArray();
//生成Excel的标题部分......................
if (i == 0) {
if(header==null)
for (int j = 0; j < keys.length; j++) {
Label cell = new Label(j, i, keys[j]==null?"":keys[j].toString(), wcf);
ws.addCell(cell);
}
// 用自定义的列名覆盖键值名称
else
for (int j = 0; j < header.length; j++) {
Label cell = new Label(j, i, header[j][1]==null?"":header[j][1], wcf);
ws.addCell(cell);
}
}
//生成Excel的数据部分....................
if(header==null)
for (int j = 0; j < keys.length; j++) {
Label cell = new Label(j, i + 1, map.get(keys[j])==null?"":map.get(keys[j]).toString());
ws.addCell(cell);
}
else
for(int j=0;jlength;j++){
Label cell = new Label(j, i + 1, map.get(header[j][0])==null?"":map.get(header[j][0]).toString());
ws.addCell(cell);
}
}
}
// 解析生成类型为bean类型的excel
// 头部不设置默认为bean字段的名称,like:{'标题a','标题b'}
private void praseBean(List list, String[][] header) throws Exception {
Method[] getMethods = null;
for (int i = 0; i < list.size(); i++) {
Object bj = list.get(i);
Class cls = obj.getClass();
Field[] fields = cls.getDeclaredFields();
if (i == 0) {
// 初始化方法数组,以供后面调用
if(header!=null){
getMethods = new Method[header.length];
// 用自定义的列名覆盖字段名称
for (int j = 0; j < header.length; j++) {
String getMethodName = "get"
+ header[j][0].substring(0, 1).toUpperCase()
+ header[j][0].substring(1);
try {
getMethods[j] = cls.getDeclaredMethod(getMethodName,
new Class[] {});
} catch (Exception ex) {
ex.printStackTrace();
log.error(ex.getMessage());
}
Label cell = new Label(j, i, header[j][1]==null?"":header[j][1], wcf);
ws.addCell(cell);
}
}
else{
getMethods = new Method[fields.length];
for (int j = 0; j < fields.length; j++) {
String getMethodName = "get"
+ fields[j].getName().substring(0, 1).toUpperCase()
+ fields[j].getName().substring(1);
try {
getMethods[j] = cls.getDeclaredMethod(getMethodName,
new Class[] {});
} catch (Exception ex) {
ex.printStackTrace();
log.error(ex.getMessage());
}
Label cell = new Label(j, i, fields[j].getName(), wcf);
ws.addCell(cell);
}
}
}
for (int j = 0; j < getMethods.length; j++) {
// fields[j].setAccessible(true);
//System.out.println(j);
Object value = getMethods[j].invoke(obj, new Object[]{});
Label cell = new Label(j, i + 1, value==null?"":value.toString());
ws.addCell(cell);
}
}
}
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
List list = new ArrayList();
Map map=new HashMap();
map.put("age",25);
map.put("password","123456");
map.put("username","xxxholic");
for(int i=0;i<10000;i++)
list.add(map);
map=new HashMap();
map.put("姓名", "xxxholic");
map.put("年龄", "25");
map.put("性别", "男");
map.put("地址", "设法的撒");
map.put("电话", "2313123313");
for(int i=0;i<10000;i++)
list.add(map);
ExcelExportor.getInstance(new FileOutputStream("E:\\xx.xls")).export(list);
}
}
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23071790/viewspace-702865/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23071790/viewspace-702865/