Jxl导出Excel表

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;
    }