import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import javax.servlet.http.HttpServletResponse; import jxl.CellView; import jxl.Range; import jxl.Sheet; import jxl.SheetSettings; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Colour; import jxl.format.VerticalAlignment; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.Number; import jxl.write.WritableCell; import org.apache.commons.lang3.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.oadmin.superapp.model.App; public class JxlExcelUtils { private static final Logger logger = LoggerFactory .getLogger(JxlExcelUtils.class); /** * @author * @param objData * 导出内容数组 * @param sheetName * 导出工作表的名称 * @param columns * 导出Excel的表头数组 * @return */ public static int exportToExcel(HttpServletResponse response, List objData, String sheetName, Map columns) { int flag = 0; // 声明工作簿jxl.write.WritableWorkbook WritableWorkbook wwb; try { // 根据传进来的file对象创建可写入的Excel工作薄 OutputStream os = response.getOutputStream(); wwb = Workbook.createWorkbook(os); /* * 创建一个工作表、sheetName为工作表的名称、"0"为第一个工作表 * 打开Excel的时候会看到左下角默认有3个sheet、"sheet1、sheet2、sheet3"这样 * 代码中的"0"就是sheet1、其它的一一对应。 createSheet(sheetName, * 0)一个是工作表的名称，另一个是工作表在工作薄中的位置 */ WritableSheet ws = wwb.createSheet(sheetName, 0); CellView cellView = new CellView(); cellView.setAutosize(true); // 设置自动大小 SheetSettings ss = ws.getSettings(); ss.setVerticalFreeze(1);// 冻结表头 ss.setDefaultColumnWidth(14); WritableFont font1 = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD); WritableCellFormat wcf = new WritableCellFormat(font1); // wcf.setBorder(Border.ALL, BorderLineStyle.THIN); // 创建单元格样式 // WritableCellFormat wcf = new WritableCellFormat(); // 背景颜色 wcf.setBackground(jxl.format.Colour.YELLOW); wcf.setAlignment(Alignment.CENTRE); // 平行居中 wcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直居中 // 判断一下表头数组是否有数据 if (columns != null && columns.size() > 0) { // 循环写入表头 Set<String> key = columns.keySet(); int i = 0; for (Iterator it = key.iterator(); it.hasNext();) { String s = (String) it.next(); ws.addCell(new Label(i, 0, (String) columns.get(s), wcf)); i++; } // 判断表中是否有数据 if (objData != null && objData.size() > 0) { // 循环写入表中数据 for (int rows = 0; rows < objData.size(); rows++) { Object source = objData.get(rows); Class sourceClz = source.getClass(); int c = 0; for (Iterator it = key.iterator(); it.hasNext();) { String fieldName = (String) it.next(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); Method getMethod; try { getMethod = sourceClz.getDeclaredMethod(getMethodName, new Class[] {}); } catch (NoSuchMethodException e) { getMethod = sourceClz.getSuperclass().getDeclaredMethod(getMethodName, new Class[] {}); } ws.addCell(new Label(c, rows + 1, String.valueOf(getMethod.invoke(source, new Object[] {})))); c++; } } } else { flag = -1; } // 写入Exel工作表 wwb.write(); // 关闭Excel工作薄对象 wwb.close(); // 关闭流 os.flush(); os.close(); os = null; } } catch (IllegalStateException e) { System.err.println(e.getMessage()); } catch (Exception ex) { flag = 0; ex.printStackTrace(); } return flag; } /** * @author * @param objData * 导出内容数组 * @param sheetName * 导出工作表的名称 * @param columns * 导出Excel的表头数组 * @return */ public static int exportToExcel2(HttpServletResponse response, List objData, String sheetName, Map columns,List numbers) { int flag = 0; // 声明工作簿jxl.write.WritableWorkbook WritableWorkbook wwb = null; OutputStream os = null; try { // 根据传进来的file对象创建可写入的Excel工作薄 os = response.getOutputStream(); wwb = Workbook.createWorkbook(os); /* * 创建一个工作表、sheetName为工作表的名称、"0"为第一个工作表 * 打开Excel的时候会看到左下角默认有3个sheet、"sheet1、sheet2、sheet3"这样 * 代码中的"0"就是sheet1、其它的一一对应。 createSheet(sheetName, * 0)一个是工作表的名称，另一个是工作表在工作薄中的位置 */ WritableSheet ws = wwb.createSheet(sheetName, 0); CellView cellView = new CellView(); cellView.setAutosize(true); // 设置自动大小 SheetSettings ss = ws.getSettings(); ss.setVerticalFreeze(1);// 冻结表头 ss.setDefaultColumnWidth(14); WritableFont font1 = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD); WritableCellFormat wcf = new WritableCellFormat(font1); // wcf.setBorder(Border.ALL, BorderLineStyle.THIN); // 创建单元格样式 // WritableCellFormat wcf = new WritableCellFormat(); // 背景颜色 wcf.setBackground(jxl.format.Colour.YELLOW); wcf.setAlignment(Alignment.CENTRE); // 平行居中 wcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直居中 // 判断一下表头数组是否有数据 if (columns != null && columns.size() > 0) { // 循环写入表头 Set<String> key = columns.keySet(); int i = 0; for (Iterator it = key.iterator(); it.hasNext();) { String s = (String) it.next(); ws.addCell(new Label(i, 0, (String) columns.get(s), wcf)); i++; } // 判断表中是否有数据 if (objData != null && objData.size() > 0) { // 循环写入表中数据 for (int rows = 0; rows < objData.size(); rows++) { Object source = objData.get(rows); Class sourceClz = source.getClass(); int c = 0; for (Iterator it = key.iterator(); it.hasNext();) { String fieldName = (String) it.next(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); Method getMethod; try { getMethod = sourceClz.getDeclaredMethod(getMethodName, new Class[] {}); } catch (NoSuchMethodException e) { getMethod = sourceClz.getSuperclass().getDeclaredMethod(getMethodName, new Class[] {}); } Object valueObject = getMethod.invoke(source, new Object[] {}); String value = valueObject !=null ? String.valueOf(valueObject) : null; if (numbers!=null && numbers.contains(fieldName) && StringUtils.isNotBlank(value)){ try{ ws.addCell(new Number(c, rows + 1, Double.valueOf(value))); }catch(Exception ex){ if(logger.isDebugEnabled()){ logger.debug(ex.getMessage()); } ws.addCell(new Label(c, rows + 1, value)); } }else{ ws.addCell(new Label(c, rows + 1, value)); } c++; } } } else { flag = -1; } // 写入Exel工作表 wwb.write(); // // 关闭Excel工作薄对象 // wwb.close(); // 关闭流 // os.flush(); // os.close(); // // os = null; } } catch (Exception ex) { flag = 0; logger.error(ex.getMessage(), ex); } finally { if(wwb!=null){ try { wwb.close(); } catch (Exception e) { logger.error(e.getMessage(), e); } } if(os!=null){ try { os.flush(); os.close(); os = null; } catch (Exception e) { logger.error(e.getMessage(), e); } } } return flag; } /** * 下载excel * * @author * @param response * @param filename * 文件名 ,如:20110808.xls * @param listData * 数据源 * @param sheetName * 表头名称 * @param columns * 列名称集合,如：{物品名称，数量，单价} */ public static void exportexcle(HttpServletResponse response, String filename, List listData, String sheetName, List<String> columnsCode, List<String> columnsName) { // 调用上面的方法、生成Excel文件 response.setContentType("application/vnd.ms-excel"); // response.setHeader("Content-Disposition", // "attachment;filename="+filename); try { response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "ISO8859-1") + ".xls"); // exportToExcel(response, listData, sheetName, columnsCode, // columnsName); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } } public static void exportexcle(HttpServletResponse response, String filename, List listData, String sheetName, Map columens) { // 调用上面的方法、生成Excel文件 response.setContentType("application/vnd.ms-excel"); // response.setHeader("Content-Disposition", // "attachment;filename="+filename); try { response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "ISO8859-1") + ".xls"); exportToExcel(response, listData, sheetName, columens); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } } public static void exportexcle2(HttpServletResponse response, String filename, List listData, String sheetName, Map columns, List numbers) { // 调用上面的方法、生成Excel文件 response.setContentType("application/vnd.ms-excel"); // response.setHeader("Content-Disposition", // "attachment;filename="+filename); try { response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "ISO8859-1") + ".xls"); exportToExcel2(response, listData, sheetName, columns,numbers); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } } public static void setWs(WritableSheet ws, int row, Object o) throws NoSuchFieldException, SecurityException { App app = new App(); app.setGuserId("44578258-978f-416f-b6b4-5a74d2b9a378");; app.setTitle("还好还好");; Class targetClz = app.getClass(); Class sourceClz = app.getClass(); // 得到Class对象所表征的类的所有属性(包括私有属性) Field[] fields = sourceClz.getDeclaredFields(); if (fields.length == 0) { fields = sourceClz.getSuperclass().getDeclaredFields(); } for (int i = 0; i < fields.length; i++) { String fieldName = fields[i].getName(); Field targetField = null; // 得到targetClz对象所表征的类的名为fieldName的属性，不存在就进入下次循环 try { targetField = targetClz.getDeclaredField(fieldName); } catch (NoSuchFieldException e) { targetField = targetClz.getSuperclass().getDeclaredField(fieldName); } // 判断sourceClz字段类型和targetClz同名字段类型是否相同 if (fields[i].getType() == targetField.getType()) { // 由属性名字得到对应get和set方法的名字 String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); System.out.println("getMethodName=" + getMethodName); String setMethodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); System.out.println("setMethodName=" + setMethodName); Method getMethod; Method setMethod; try { try { getMethod = sourceClz.getDeclaredMethod(getMethodName, new Class[] {}); } catch (NoSuchMethodException e) { getMethod = sourceClz.getSuperclass().getDeclaredMethod(getMethodName, new Class[] {}); } try { setMethod = targetClz.getDeclaredMethod(setMethodName, fields[i].getType()); } catch (NoSuchMethodException e) { setMethod = targetClz.getSuperclass().getDeclaredMethod(setMethodName, fields[i].getType()); } // 调用source对象的getMethod方法 System.out.println(getMethod.invoke(app, new Object[] {})); ws.addCell(new Label(i, row, String.valueOf(getMethod.invoke(app, new Object[] {})))); } catch (Exception e) { e.printStackTrace(); } } } } public static void main(String[] args) throws Exception { } }
下面是真正的实例
@RequestMapping(value = "/exportOrderList") public Object exportOrderList(HttpServletResponse response, HttpSession session, String orders) { ReturnResult returnResult = new ReturnResult(); try { boolean isLogin = abstractManagerImpl.isLogin(session); if (isLogin) { String appId = (String) session.getAttribute(UserConstant.LOGIN_APP_ID); logger.info("appid=" + appId); String filename = "未发货订单列表" + DateUtil.formatDateTime(DateUtil.DATE_FORMAT_SHORT, new Date()); List<OrderVO> orderList = orderManager.getOrderListById(orders);//OrderVO是需要导出的对象 String sheetName = "ordercehsi"; Map<String, String> columens = new LinkedHashMap<String, String>(); columens.put("order", "物流编号"); columens.put("company", "物流公司名称"); columens.put("order_number", "订单编号"); columens.put("appId", "app编号"); columens.put("orderId", "订单id"); columens.put("guserId", "买家id"); columens.put("createTime", "创建时间"); columens.put("state", "订单状态"); columens.put("nickname", "收件人"); columens.put("address", "收货地址"); columens.put("title", "标题"); columens.put("phone", "联系电话"); columens.put("productId", "产品编号"); columens.put("label", "产品属性"); columens.put("sizelist", "尺码大小"); columens.put("modellist", "产品规格"); columens.put("price", "单价"); columens.put("total", "数量"); columens.put("totalPrice", "总金额"); JxlExcelUtils.exportexcle(response, filename, orderList, sheetName, columens); } else { returnResult.setCode(ReturnCodeType.NOT_AUTHORIZED).setMessage(CommonConstant.GO_TO_LOGIN); } } catch (Exception e) { logger.error(e.getMessage(), e); } return returnResult; }