纯JSP+工具类实现的jxl导出Excel

需求：根据查找到的数据集将其导出到excel中，前提：不用strtus框架、不能用servlet。
看到这个需求，没办法，只能弄一个超链接将所有查找数据集的参数传递到一个jsp页面去，在jsp页面再对参数进行封装，并查找数据库得到数据集list，将其传递到工具类中，对其进行处理，工具类使用jxl，并且使用到反射技术对其进行循环遍历，逐个写到excel表中。

使用超链接并携带多个参数

<a href="export.jsp?startTime=<%=start%>&endTime=<%=end%>&useTime=<%=useTime%>&numbers=<%=numbers%>">导出Excel</a>

获取参数并组装

WebContext context = Web.getContext();



    int useTime = context.getIntParam("useTime",0);
    int numbers = context.getIntParam("numbers",0);


    String start = context.getStrParam("startTime", DateUtilsEx
            .FORMAT_ISO_DATE_String());
    String end = context.getStrParam("endTime", DateUtilsEx
            .FORMAT_ISO_DATE_String());


    ParamCharge params = new ParamCharge();
    params.setEndDate(end.length() ==0? null : end);
    params.setStartDate(start.length() ==0? null : start);

    params.setUseTime(useTime);
    params.setNumbers(numbers);

WebContext工具类

package express.web;

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletContext;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.lang.StringUtils;

import app.App;

import common.util.DataTypeUtils;

public class WebContext {

    private HttpServletRequest request;

    private HttpServletResponse response;

    private Map<String, String> params;

    private Map<String, String> cookies;

    private Map<String, byte[]> uploadedFile;

    private List<String> files;

    private long createTime;

    public long getCreateTime() {
        return createTime;
    }

    private boolean uploadForm = false;

    public WebContext() {
        createTime = System.currentTimeMillis();
    }

    private boolean post = false;

    public static final String REQUEST_QUERY_CHARSET_FROM = "ISO-8859-1";

    public static final String REQUEST_QUERY_CHARSET_TO = "UTF-8";

    public static final String REQUEST_POST_CHARSET_FROM = "GBK";

    public static final String REQUEST_POST_CHARSET_TO = "GBK";

    public static final String USER_UNIQUE_ID_KEY = "GBK";

    public static final int AUTO_LOGIN_MAX_AGE = 365 * 24 * 60 * 60;

    public static final int MAX_UPLOAD_FILE_SIZE = 1024 * 1024 * 100;

    public static final String ADMIN_ID = "adminid";

    public static final String ADMIN_NAME = "adminname";

    public static final String SITE_ID = "siteid";

    public static final String SITE_NAME = "sitename";

    public static final String USER_ID = "userid";

    public static final String USER_NAME = "username";

    public static final String ACTIVITY_NAME = "activityname";

    public static final String ACTIVITY_ID = "activityid";

    public WebContext(HttpServletRequest request, HttpServletResponse response) {
        this.request = request;
        this.response = response;
        post = request.getMethod().equalsIgnoreCase("post");

        try {
            request.setCharacterEncoding("UTF-8");
            initCookies();
            if (getUserUniqueId() == null) {
                writeCookie(USER_UNIQUE_ID_KEY,
                        String.valueOf(System.currentTimeMillis()),
                        AUTO_LOGIN_MAX_AGE);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void forword(String url) {
        try {
            request.getRequestDispatcher(url).forward(request, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getQueryAccess() {
        return request.getRequestURI()
                + (request.getQueryString() == null ? "" : "?"
                        + request.getQueryString());
    }

    @SuppressWarnings("unchecked")
    public void parseUploadedContent() {

        if (!ServletFileUpload.isMultipartContent(request)) {
            return;
        }
        params = new HashMap<String, String>();
        uploadedFile = new HashMap<String, byte[]>();
        files = new ArrayList<String>();
        uploadForm = true;
        DiskFileItemFactory factory = new DiskFileItemFactory();
        factory.setSizeThreshold(4000);

        factory.setRepository(App.getUploadPath());
        ServletFileUpload upload = new ServletFileUpload(factory);
        upload.setSizeMax(MAX_UPLOAD_FILE_SIZE);

        try {
            for (FileItem item : (List<FileItem>) upload.parseRequest(request)) {
                uploadForm = true;
                if (!item.isFormField()) {
                    byte[] data = item.get();
                    if (item.getString() != null
                            && item.getString().length() > 0 && data != null
                            && data.length > 0) {
                        uploadedFile.put(item.getFieldName(), data);
                        params.put(item.getFieldName(), item.getName());
                        files.add(item.getFieldName());
                    }
                } else {
                    params.put(item.getFieldName(), item.getString());
                }
            }
        } catch (FileUploadException e) {
            e.printStackTrace();
        }
    }

    public byte[] getUploadedFile(String param) {
        if (uploadedFile == null) {
            return null;
        }
        return uploadedFile.get(param);
    }

    public OutputStream getOutputStream() {
        try {
            return response.getOutputStream();
        } catch (IOException e) {
        }
        return null;
    }

    public InputStream getIntputStream() {
        try {
            return request.getInputStream();
        } catch (IOException e) {
        }
        return null;
    }

    public HttpServletRequest getRequest() {
        return request;
    }

    public HttpServletResponse getResponse() {
        return response;
    }

    public void redirect(String url) {
        try {
            response.sendRedirect(url);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public int getIntParam(String paramName) {
        return getIntParam(paramName, -1);
    }

    public int getIntParam(String paramName, int def) {
        return DataTypeUtils.toInt(
                !uploadForm ? request.getParameter(paramName) : params
                        .get(paramName), def);
    }

    public long getLongParam(String paramName) {
        return getLongParam(paramName, -1);
    }

    public long getLongParam(String paramName, long def) {
        return DataTypeUtils.toLong(
                !uploadForm ? request.getParameter(paramName) : params
                        .get(paramName), def);
    }

    public long getLongParam(String paramName, int def) {
        return DataTypeUtils.toLong(
                !uploadForm ? request.getParameter(paramName) : params
                        .get(paramName), def);
    }

    public String getStrParam(String paramName) {
        return getStrParam(paramName, "");
    }

    public String getStrParam(String paramName, String def) {
        String param = DataTypeUtils.toString(
                !uploadForm ? request.getParameter(paramName) : params
                        .get(paramName), def);
        if (param == null) {
            return null;
        }
        try {
            if (post) {
                return new String(param.trim().getBytes(
                        REQUEST_POST_CHARSET_FROM), REQUEST_POST_CHARSET_TO);
            } else {
                return new String(param.trim().getBytes(
                        REQUEST_QUERY_CHARSET_FROM), REQUEST_QUERY_CHARSET_TO);
            }
        } catch (UnsupportedEncodingException e) {

        }
        return param.trim();
    }

    public List<String> getFiles() {
        return files;
    }

    private void initCookies() {
        if (cookies == null) {
            cookies = new HashMap<String, String>();
            if (request.getCookies() == null) {
                return;
            }
            for (Cookie cookie : request.getCookies()) {
                try {
                    cookies.put(cookie.getName(),
                            URLDecoder.decode(cookie.getValue(), "UTF-8"));
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public void writeCookie(String name, String value) {
        writeCookie(name, value, -1);
    }

    public void writeCookie(String name, String value, int maxAge) {
        initCookies();

        cookies.put(name, value);
        try {
            Cookie cookie = new Cookie(name, URLEncoder.encode(value, "UTF-8"));
            if (maxAge != -1) {
                cookie.setMaxAge(maxAge);
            }
            getResponse().addCookie(cookie);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }

    public void outFile(String fileName, byte[] binaryData) {
        try {
            OutputStream stream = response.getOutputStream();
            response.reset();
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", (new StringBuilder(
                    "attachment;filename=")).append(fileName).append(";")
                    .toString());
            response.setHeader("Content-Length",
                    String.valueOf(binaryData.length));
            BufferedInputStream fif = new BufferedInputStream(
                    new ByteArrayInputStream(binaryData));
            int data = 0;
            byte buf[] = new byte[800];
            while ((data = fif.read(buf)) != -1) {
                stream.write(buf, 0, data);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getStrParamInCookie(String paramName, String def) {
        initCookies();
        return DataTypeUtils.toString(cookies.get(paramName), def);
    }

    public int getIntParamInCookie(String paramName, int def) {
        initCookies();
        return DataTypeUtils.toInt(cookies.get(paramName), def);
    }

    public long getLongParamInCookie(String paramName, long def) {
        initCookies();
        return DataTypeUtils.toLong(cookies.get(paramName), def);
    }

    public String getStrInAttribute(String paramName, String def) {
        return DataTypeUtils.toString(request.getAttribute(paramName), def);
    }

    public String getStrInAttribute(String paramName) {
        return getStrInAttribute(paramName, "");
    }

    public int getIntInAttribute(String paramName, int def) {
        return DataTypeUtils.toInt(request.getAttribute(paramName), def);
    }

    public long getLongInAttribute(String paramName, long def) {
        return DataTypeUtils.toLong(request.getAttribute(paramName), def);
    }

    public String getUserUniqueId() {
        initCookies();
        return DataTypeUtils.toString(cookies.get(USER_UNIQUE_ID_KEY), null);
    }

    public String getIp() {
        return request.getRemoteAddr();
    }

    public String getUrl() {
        return request.getRequestURI();
    }

    public void outText(String txt) {
        try {
            response.getWriter().print(txt);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public boolean isPostBack() {
        return isPostBack("PostBack");
    }

    public boolean isPostBack(String paramName) {
        return request.getParameter(paramName) != null;
    }

    public String[] getArrayParam(String paramName) {
        return request.getParameterValues(paramName);
    }

    public String getArrayParamAsString(String paramName) {
        return getArrayParamAsString(paramName, "");
    }

    public String getArrayParamAsString(String paramName, String def) {
        String[] array = getArrayParam(paramName);
        if (array != null) {
            return StringUtils.join(getArrayParam(paramName), ",");
        }
        return def;
    }

    public String getArrayParamAsString(String paramName, String def, String dev) {
        String[] array = getArrayParam(paramName);
        if (array != null) {
            return StringUtils.join(getArrayParam(paramName), dev);
        }
        return def;
    }

    public HttpSession getSession() {
        return request.getSession();
    }

    public ServletContext getApplication() {
        return getSession().getServletContext();
    }

    public boolean isAdminLogined() {
        return getAdminId() != null;
    }

    public boolean isUserLogined() {
        return getUserId() != null;
    }

    public boolean isActivityLogined() {
        return getActivityId() != null;
    }

    public boolean isSiteLogined() {
        return getSiteId() != null;
    }

    public String getAdminId() {
        return getSession().getAttribute(ADMIN_ID) == null ? null : String
                .valueOf(getSession().getAttribute(ADMIN_ID));
    }

    public String getAdminName() {
        return getSession().getAttribute(ADMIN_NAME) == null ? null
                : (String) getSession().getAttribute(ADMIN_NAME);
    }

    public String getSiteId() {
        return getSession().getAttribute(SITE_ID) == null ? null : String
                .valueOf(getSession().getAttribute(SITE_ID));
    }

    public String getSiteName() {
        return getSession().getAttribute(SITE_NAME) == null ? null
                : (String) getSession().getAttribute(SITE_NAME);
    }

    public String getUserId() {
        return getSession().getAttribute(USER_ID) == null ? null : String
                .valueOf(getSession().getAttribute(USER_ID));
    }

    public String getUserName() {
        return getSession().getAttribute(USER_NAME) == null ? null
                : (String) getSession().getAttribute(USER_NAME);
    }

    public String getActivityId() {
        return getSession().getAttribute(ACTIVITY_ID) == null ? null : String
                .valueOf(getSession().getAttribute(ACTIVITY_ID));
    }

    public void logout() {
        getSession().invalidate();
    }

    public String parsePostContentAsString() {
        return parsePostContentAsString("UTF-8");
    }

    public String parsePostContentAsString(String encode) {
        try {
            return new String(parsePostContent(), encode);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return "";
    }

    public byte[] parsePostContent() {
        InputStream in = null;
        try {
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            in = request.getInputStream();
            int ch;
            while ((ch = in.read()) != -1) {
                baos.write(ch);
            }
            return baos.toByteArray();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (in != null) {
                    in.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return new byte[0];
    }
}

对其进行查找，得到list集合

List<ActivationCode> list = App.getActivationCode().stat(params);

导出Excel

jsp页面代码,调用工具类

OutputStream os = response.getOutputStream();
    String[] title = new String[]{"ID","渠道ID","类型","金币","铜币","体力","道具","将领","生成数量","开始时间","结束时间","激活码","使用次数","编号"};
    String fileName = "激活码编号为"+numbers+".xls";
    response.reset();
    response.setHeader("Content-disposition", "attachment; filename="
            + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
    response.setContentType("application/msexcel;charset= utf-8");
    ExportExcel.exportExcel(os,fileName,title,list);

工具类:ExportExcel.java

package common.util;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
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 app.model.ActivationCode;

/***
 * @author lsf
 */
public class ExportExcel {
    /***************************************************************************
     * @param fileName
     *            EXCEL文件名称
     * @param listTitle
     *            EXCEL文件第一行列标题集合
     * @param listContent
     *            EXCEL文件正文数据集合
     * @return
     */
    public final static String exportExcel(OutputStream os,
            String fileName, String[] Title, List<ActivationCode> listContent) {
        String result = "系统提示：Excel文件导出成功！";
        // 以下开始输出到EXCEL
        try {
            // 定义输出流，以便打开保存对话框______________________begin
            // HttpServletResponse response = HttpServletResponse.class.get;
//          OutputStream os = response.getOutputStream();// 取得输出流
//          response.reset();// 清空输出流
//          response.setHeader("Content-disposition", "attachment; filename="
//                  + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
//          // 设定输出文件头
//          response.setContentType("application/msexcel");// 定义输出类型
            // 定义输出流，以便打开保存对话框_______________________end

            /** **********创建工作簿************ */
            WritableWorkbook workbook = Workbook.createWorkbook(os);

            /** **********创建工作表************ */

            WritableSheet sheet = workbook.createSheet("Sheet1", 0);

            /** **********设置纵横打印（默认为纵打）、打印纸***************** */
            jxl.SheetSettings sheetset = sheet.getSettings();
            sheetset.setProtected(false);

            /** ************设置单元格字体************** */
            WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
            WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,
                    WritableFont.BOLD);

            /** ************以下设置三种单元格样式，灵活备用************ */
            // 用于标题居中
            WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
            wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
            //背景颜色设置为"那什么"色   
            wcf_center.setBackground(Colour.YELLOW);
            wcf_center.setWrap(false); // 文字是否换行

            // 用于正文居左
            WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
            wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
            wcf_left.setWrap(false); // 文字是否换行

            /** ***************以下是EXCEL开头大标题，暂时省略********************* */
            // sheet.mergeCells(0, 0, colWidth, 0);
            // sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
            /** ***************以下是EXCEL第一行列标题********************* */
            for (int i = 0; i < Title.length; i++) {
                sheet.addCell(new Label(i, 0, Title[i], wcf_center));
            }
            /** ***************以下是EXCEL正文数据********************* */
            Field[] fields = null;
            int i = 1;
            for (Object obj : listContent) {
                fields = obj.getClass().getDeclaredFields();
                int j = 0;
                for (Field v : fields) {
                    v.setAccessible(true);
                    Object va = v.get(obj);
                    if (va == null) {
                        va = "";
                    }
                    sheet.addCell(new Label(j, i, va.toString(), wcf_left));
                    j++;
                }
                i++;
            }
            /** **********将以上缓存中的内容写到EXCEL文件中*********/
            workbook.write();
            /** *********关闭文件************* */
            workbook.close();

        } catch (Exception e) {
            result = "系统提示：Excel文件导出失败，原因：" + e.toString();
            System.out.println(result);
            e.printStackTrace();
        }
        return result;
    }
}

效果图