/**
* 导出excel
* @param list :结果集;
* @param filePath:指定路径名;
* @param out:输出流,response.getOutputStream();
* @param mapFields:导出字段对应的key(类字段实体), value:对应表显示的列名称;
* @param sheetName:当前工作表名称;
*/
public static void createExcel(List list, String filePath, OutputStream out, Map<String, String> mapFields,
String sheetName){
if(null==sheetName || "".equals(sheetName))// 为空则默认设置:sheet1
sheetName = "sheet1" ;
WritableWorkbook wook = null;
try{
if(null!=filePath && !"".equals(filePath))
wook = Workbook.createWorkbook(new File(filePath));
else
wook = Workbook.createWorkbook(out);
// 设置头部字体格式
WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
font.setColour(Colour.RED);
// 应用字体
WritableCellFormat wcfh = new WritableCellFormat(font);
// 设置其它样式
wcfh.setAlignment(Alignment.CENTRE);//水平对齐
wcfh.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直居中
wcfh.setBorder(Border.ALL, BorderLineStyle.THIN);//边框
wcfh.setBackground(Colour.YELLOW); // 背景色
wcfh.setWrap(false);//自动换行
WritableFont font_text = new WritableFont(WritableFont.TIMES, 11, WritableFont.NO_BOLD);
WritableCellFormat wcfc = new WritableCellFormat(font_text);
// 设置其它样式
wcfc.setAlignment(Alignment.CENTRE);//水平对齐
wcfc.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直居中
wcfc.setBorder(Border.ALL, BorderLineStyle.THIN);//边框
wcfc.setWrap(false);//自动换行
// 创建工作表
WritableSheet sheet = wook.createSheet(sheetName, 0);
SheetSettings setting = sheet.getSettings();// 冻结窗口头部
setting.setVerticalFreeze(1);
Integer columnIndex = 0 ;
List<String> methodNameList = new ArrayList<String>();
// 添加列标题
if(null!=mapFields){
String key = "" ;
//开始导出表格头部
for(Iterator<String> i=mapFields.keySet().iterator(); i.hasNext();){
key = i.next();
// 应用wcfh样式创建单元格
sheet.addCell(new Label(columnIndex, 0, mapFields.get(key), wcfh));
//记录字段的顺序,以便于导出的内容与字段不出现偏移
methodNameList.add(key);
columnIndex++;
}
//导出表格内容
if(null!=list && list.size()>0){
Object objClass = null;
Map<String,Method> getMap = null;
Method method = null;
for(int i=0, len=list.size(); i<len; i++){
objClass = list.get(i);
//获得对象所有的get方法
getMap = getAllMethod(objClass);
//按保存的字段顺序导出内容
for(int k=0; k<methodNameList.size(); k++){
// 根据key获取对应的方法
method = getMap.get("GET" + methodNameList.get(k).toString().toUpperCase());
if(null!=method){
// 从对应的get方法得到返回值
String value = method.invoke(objClass, null).toString();
//应用wcfc样式创建单元格
sheet.addCell(new Label(k, i+1, value, wcfc));
}else{
//如果没有对应的get方法,则默认将内容设为""
sheet.addCell(new Label(k, i+1, "", wcfc));
}
}
}
}
wook.write();
}
System.out.println("生成Excel表格完成;");
}catch(Exception e){
e.printStackTrace();
}finally{
try {
if(null!=wook)
wook.close();
if(null!=out)
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 获取类的所有get方法
* @param args
*/
public static HashMap<String, Method> getAllMethod(Object obj){
HashMap<String,Method> map = new HashMap<String,Method>();
Method[] methods = obj.getClass().getMethods(); // 得到所有的方法
String methodName = "" ;
for(int i=0; i<methods.length; i++){
methodName = methods[i].getName().toUpperCase();// 方法名
if(methodName.startsWith("GET")){
map.put(methodName, methods[i]);
}
}
return map;
}
public static void main(String[] args){
User user = new User();
user.setAge(24);
user.setAlipayAccount("1311111111");
user.setName("test001");
user.setTotal(360.00);
User user2 = new User();
user2.setAge(36);
user2.setAlipayAccount("131111111111@139.com");
user2.setName("test002");
user2.setTotal(721.50);
List list = new ArrayList();
list.add(user);
list.add(user2);
String filePath = "E:\\20141015.xls";
OutputStream out = null ;
Map mapFields = new HashMap();
mapFields.put("total", "总额");
mapFields.put("age", "年龄");
mapFields.put("alipayAccount", "帐号");
mapFields.put("name", "名称");
String sheetName = "test" ;
createExcel(list, filePath, out, mapFields, sheetName);
}
java jxl
最新推荐文章于 2024-07-20 23:59:39 发布