Java导出Excel表格

问题描述：

　　Web开发是，我们经常会遇见客户要求，把某些数据导出Excel，有利于客户分析和打印等等。下面是导出Excel下载的对应是JSP.

1.点击“导出”按钮时，触发exportExcel()的JS方法，JS方法跳到对应的JSP，实现导出功能。

//导出成EXCEL
function exportExcel(){
    var checkboxes = j$("input[name='serial_no_check']:checked");            //得到所有选中的checkbox对象
    if(checkboxes.length <= 1){
        sl_alert("请至少选择一条记录!");
        return false;
    }
    //获取所有选中的serial_no
    var serial_no_list = "";
    j$.each(checkboxes,function(i, val){    //该val是dom对象
            serial_no_list += j$(val).val()+ ",";
    });
    location = "list_cc_export.jsp?serial_no_list=" + serial_no_list;
}

2.在list_cc_export.jsp里面实现导出功能，里面主要是JAVA代码

<%@ page contentType="text/html; charset=GBK"  import="com.enfo.intrust.risk.vo.*,com.enfo.intrust.risk.*,com.enfo.intrust.dao.*,com.enfo.intrust.tools.*,com.enfo.intrust.intrust.*,java.math.*,jxl.write.*,,com.enfo.intrust.project.gain.*"%>

<%@ include file="/includes/operator.inc" %>
<%@ include file="/includes/parameter.inc" %>
<%
try{

Integer app_problem_id = Utility.parseInt(Utility.trimNull(request.getParameter("app_problem_id")),Integer.valueOf("0"));
Integer sub_product_id = Utility.parseInt(Utility.trimNull(request.getParameter("sub_product_id")),Integer.valueOf("0"));
String serial_no_list =Utility.trimNull(request.getParameter("serial_no_list"));


//调用过程
List list = null;
Object[] params = new Object[1];
params[0] = serial_no_list;
list = DBManager.listProcAll("{call SP_QUERY_TPLRULENOTICELIST_CC_EXCEL(?)}", params);


response.setContentType("application/vnd.ms-excel");   
String excelName = "信托划款指令模板";
response.addHeader("Content-Disposition","attachment;filename="+new String(excelName.getBytes("GBK"), "ISO-8859-1") +".xls");
 
java.io.OutputStream os = response.getOutputStream();
out.clear();
out = pageContext.pushBody();
jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(os);

jxl.write.WritableSheet  ws = wwb.createSheet("外部指令",0);

// 1、标题的格式
// 制定子字串格式
WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
        16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
// 指定单元格的各种属性
WritableCellFormat format = new WritableCellFormat(font);
// 指定水平对齐的方式居中
format.setAlignment(Alignment.CENTRE);
// 制定垂直对齐的方式居中
format.setVerticalAlignment(VerticalAlignment.CENTRE);
// 合并单元格
//ws.mergeCells(0, 0, 13, 0);
//ws.setRowView(0, 500);// 行高
// 添加标题
//jxl.write.Label labelC = new jxl.write.Label(0, 0, "受益人明细表" , format);
//ws.addCell(labelC);

// 2、表头的格式
WritableFont fontTop = new WritableFont(WritableFont.createFont("宋体"),
        10, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
WritableCellFormat formatTop = new WritableCellFormat(fontTop);
formatTop.setAlignment(Alignment.CENTRE);
formatTop.setVerticalAlignment(VerticalAlignment.CENTRE);
formatTop.setBorder(Border.ALL, BorderLineStyle.THIN);

// 3、内容的格式
// 居左
WritableFont fontLeft = new WritableFont(WritableFont.createFont("宋体"),
        10, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
WritableCellFormat formatLeft = new WritableCellFormat(fontLeft);
formatLeft.setAlignment(Alignment.LEFT);
formatLeft.setVerticalAlignment(VerticalAlignment.CENTRE);
formatLeft.setBorder(Border.ALL, BorderLineStyle.THIN);
// 剧中
WritableFont fontCenter = new WritableFont(WritableFont
        .createFont("宋体"), 10, WritableFont.NO_BOLD, false,
        jxl.format.UnderlineStyle.NO_UNDERLINE);
WritableCellFormat formatCenter = new WritableCellFormat(fontCenter);
formatCenter.setAlignment(Alignment.CENTRE);
formatCenter.setVerticalAlignment(VerticalAlignment.CENTRE);
formatCenter.setBorder(Border.ALL, BorderLineStyle.THIN);
// 居右
WritableFont fontRight = new WritableFont(
        WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false,
        jxl.format.UnderlineStyle.NO_UNDERLINE);
WritableCellFormat formatRight = new WritableCellFormat(fontRight);
formatRight.setAlignment(Alignment.RIGHT);
formatRight.setVerticalAlignment(VerticalAlignment.CENTRE);
formatRight.setBorder(Border.ALL, BorderLineStyle.THIN);

WritableFont font_1 = new WritableFont(WritableFont.createFont("宋体"),
        10, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
// 指定单元格的各种属性
WritableCellFormat format_1 = new WritableCellFormat(font_1);
// 指定水平对齐的方式居中
format_1.setAlignment(Alignment.CENTRE);
// 制定垂直对齐的方式居中
format_1.setVerticalAlignment(VerticalAlignment.CENTRE);

// 指定单元格的各种属性
WritableCellFormat format_2 = new WritableCellFormat(font_1);
// 指定水平对齐的方式居中
format_2.setAlignment(Alignment.RIGHT);
// 制定垂直对齐的方式居中
format_2.setVerticalAlignment(VerticalAlignment.CENTRE);

jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#,##0.00");
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(fontRight, nf);
wcfN.setAlignment(Alignment.RIGHT);
wcfN.setVerticalAlignment(VerticalAlignment.CENTRE);
wcfN.setBorder(Border.ALL, BorderLineStyle.THIN);


ws.setColumnView(0, 15);
ws.setColumnView(1, 15);
ws.setColumnView(2, 15);
ws.setColumnView(3, 15);

ws.setColumnView(4, 25);
ws.setColumnView(5, 25);
ws.setColumnView(6, 25);
ws.setColumnView(7, 25);
ws.setColumnView(8, 25);
ws.setColumnView(9, 25);
ws.setColumnView(10, 25);
ws.setColumnView(11, 25);
ws.setColumnView(12, 25);
ws.setColumnView(13, 25);
ws.setColumnView(14, 25);
ws.setColumnView(15, 25);
ws.setColumnView(16, 25);
ws.setColumnView(17, 25);
ws.setColumnView(18, 25);
ws.setColumnView(19, 25);
ws.setColumnView(20, 25);
ws.setColumnView(21, 25);
int startRowNum = 0;

ws.addCell(new jxl.write.Label(0, startRowNum, "*发生日期" , formatCenter));
ws.addCell(new jxl.write.Label(1, startRowNum, "*交收日期" , formatCenter));
ws.addCell(new jxl.write.Label(2, startRowNum, "*基金代码" , formatCenter));
ws.addCell(new jxl.write.Label(3, startRowNum, "*金额" , formatCenter));
ws.addCell(new jxl.write.Label(4, startRowNum, "款项用途" , formatCenter));
ws.addCell(new jxl.write.Label(5, startRowNum, "*本方银行编号" , formatCenter));
ws.addCell(new jxl.write.Label(6, startRowNum, "*本方开户行名称" , formatCenter));
ws.addCell(new jxl.write.Label(7, startRowNum, "*本方银行账号" , formatCenter));
ws.addCell(new jxl.write.Label(8, startRowNum, "*本方银行户名" , formatCenter));
ws.addCell(new jxl.write.Label(9, startRowNum, "本方银行省份代码" , formatCenter));
ws.addCell(new jxl.write.Label(10, startRowNum, "本方银行城市代码" , formatCenter));
ws.addCell(new jxl.write.Label(11, startRowNum, "*对方银行编号" , formatCenter));
ws.addCell(new jxl.write.Label(12, startRowNum, "*对方开户行名称" , formatCenter));
ws.addCell(new jxl.write.Label(13, startRowNum, "*对方银行账号" , formatCenter));

ws.addCell(new jxl.write.Label(14, startRowNum, "*对方银行户名" , formatCenter));
ws.addCell(new jxl.write.Label(15, startRowNum, "对方银行省份代码" , formatCenter));
ws.addCell(new jxl.write.Label(16, startRowNum, "对方银行城市代码" , formatCenter));
ws.addCell(new jxl.write.Label(17, startRowNum, "对方银行大额支付号" , formatCenter));
ws.addCell(new jxl.write.Label(18, startRowNum, "备注" , formatCenter));
ws.addCell(new jxl.write.Label(19, startRowNum, "托管行联系人" , formatCenter));
ws.addCell(new jxl.write.Label(20, startRowNum, "托管行联系电话" , formatCenter));
ws.addCell(new jxl.write.Label(21, startRowNum, "托管行传真号码" , formatCenter));
//查数据库,并把数据写入到EXCEL
int row = 0;
int rowAdd = 1;
for(; list != null && row < list.size() ; row++ ) {
    int cols = 0;
    Map map = (Map)list.get(row);
    BigDecimal sy_money = Utility.parseDecimal(Utility.trimNull(map.get("SY_MONEY3")),new BigDecimal(0));
    BigDecimal rg_money2 = Utility.parseDecimal(Utility.trimNull(map.get("RG_MONEY2")),new BigDecimal(0));
    String sy_amount = user_id.intValue() == 11 ? Utility.trimNull(Format.formatMoney(Utility.parseDecimal(Utility.trimNull(map.get("SY_AMOUNT")),new BigDecimal(0)))) : Utility.trimNull(Format.formatMoney(rg_money2));
    BigDecimal sy_rate = Utility.parseDecimal(Utility.trimNull(map.get("SY_RATE")),new BigDecimal(0));
    
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DATE1")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DATE2")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("PRODUCT_CODE")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, sy_money.toString(), formatRight));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("SUMMARY")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BANK_CODE")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BANK_ALL_NAME")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("TG_BANK_ACCT")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("TG_ACCT_NAME")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BANK_CITY_CODE")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BANK_CITY")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_CODE")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_ALL_NAME")), formatCenter));

    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_ACCT")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("CUST_ACCT_NAME")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_CITY_CODE")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_CITY")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BIG_BANK_CODE")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_SUMMARY")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_LIKE_NAME")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_LINK_PHONE")), formatCenter));
    ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_LINK_FAX")), formatCenter));
}

/*    
WritableCellFormat formatLeft_1 = new WritableCellFormat(fontLeft);
formatLeft_1.setAlignment(Alignment.LEFT);
formatLeft_1.setVerticalAlignment(VerticalAlignment.CENTRE);
formatLeft_1.setBorder(Border.ALL, BorderLineStyle.THIN);
formatLeft_1.setWrap(true);

ws.addCell(new jxl.write.Label(0, row + 2, "以上述信息为准进行收入和支付操作

信托团队负责人:        信托团队填表人:        信财部复核人:        信财部负责人:", formatLeft_1));
ws.mergeCells(0, row + 2, 13, row + 3);
ws.setRowView(row + 2, 800);// 行高

*/
wwb.write();
wwb.close();
os.close();
}catch(Exception e){ throw e ;}
%>

浏览器就会弹出下载保存界面