jsp导出excel mysql_java、jsp导出excel功能备份

问题踩坑：

ajax请求不能下载文件

必须这样：

点击下载Excel

或者

location.href = '/media';

js取表单数据：

var selectbatch = $("selectbatch").value

java代码：

package com.action;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import com.IHttpAction;

import com.sql.ExchangeSql;

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.NumberFormats;

import jxl.write.WritableCell;

import jxl.write.WritableCellFormat;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

/**

* @FileName: ExchangeSelectAction.java

* @Description:

* @Author: xingchong

* @CreateTime: Nov 15, 2018 8:42:45 PM

* @Copyright:Copyright (c) 2017

* @Version:1.0

*/

public class ExchangeExportAction implements IHttpAction {

private ExchangeSql exchangeSql = new ExchangeSql();

private Logger logger = Logger.getLogger(ExchangeExportAction.class);

/* (non-Javadoc)

* @see com.gm.console.IHttpAction#action(javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)

*/

@Override

public void action(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

int batch = Integer.parseInt(request.getParameter("selectbatch"));

logger.info("selectbatch = "+ batch);

try {

InputStream is = this.exportExchangeCode(batch);

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");

String fileName = "batch_"+batch+"_"+sdf.format(new Date()) + ".xls";

response.setContentType("applicaiton/x-download");

response.addHeader("Content-Disposition", "attachment;filename="+fileName);

ServletOutputStream out=response.getOutputStream();

int len=0;

byte[] buffer=new byte[1024];

while((len=is.read(buffer))>0){

out.write(buffer,0,len);

}

is.close();

out.close();

//response.getWriter().write("export excel ok...");

} catch (Exception e) {

e.printStackTrace();

}

}

/**

* 获取兑换码html信息

*

* @param batch

* @return

* @throws Exception

*/

private InputStream exportExchangeCode(int batch) throws Exception {

List> dataList = null;

if (batch > 0) {

dataList = this.exchangeSql.getExchangeData(batch, Integer.MAX_VALUE);

} else {

dataList = this.exchangeSql.getSimpleExchangeData(Integer.MAX_VALUE);

}

List titleList = dataList.get(0);

dataList.remove(0);

InputStream is = null;

try {

ByteArrayOutputStream os = new ByteArrayOutputStream();

WritableWorkbook wwb = Workbook.createWorkbook(os);

int step = 50000;

int size = dataList.size();

int sheetIndex = 0;

for(int start = 0; start < size; start+=step){

int end = start + step;

end = end > size? size:end;

List> dataListSub = new ArrayList>();

dataListSub.add(titleList);

dataListSub.addAll(dataList.subList(start, end));

this.writeDataToSheet(wwb, dataListSub, sheetIndex++);

}

wwb.write();

wwb.close();

is = new ByteArrayInputStream(os.toByteArray());

} catch (IOException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

}

returnis;

}

/**

* 写一页excel

* @param wwb

* @param dataList

* @param sheetIndex

* @throws RowsExceededException

* @throws WriteException

*/

private void writeDataToSheet(WritableWorkbook wwb, List> dataList, int sheetIndex)

throws RowsExceededException, WriteException {

WritableSheet sheet = wwb.createSheet("data_"+sheetIndex, sheetIndex);

WritableCellFormat wcf = new WritableCellFormat(NumberFormats.TEXT);

for (int r = 0; r < dataList.size(); r++) {

List list = dataList.get(r);

for (int c = 0; c < list.size(); c++) {

String string = list.get(c);

WritableCell writableCell = new Label(c, r, string, wcf);

sheet.addCell(writableCell);

}

}

}

}

jsp代码：

-//W3C//DTD HTML 4.0 Transitional//EN">

My JSP 'buildinglist.jsp' starting page

charset="utf-8">

charset="utf-8">

function btnAjax() {

$("resultTable").innerHTML = "正在紧张生成中...请勿重复点击...";

varmyAjax = newAjax.Request("customRequest.htm?catalog=exchange_add",

{

method : 'post',

parameters : Form.serialize('Form1'),

onComplete : function(x) { //提交成功回调

$("resultTable").innerHTML = x.responseText;

},

onError : function(x) { //提交失败回调

alert(x.statusText);

}

});

}

function selectAjax() {

$("selectTable").innerHTML = "正在查询中...请稍等...";

var myAjax = new Ajax.Request(

"customRequest.htm?catalog=exchange_select", {

method : 'post',

parameters : Form.serialize('Form1'),

onComplete : function(x) { //提交成功回调

$("selectTable").innerHTML = x.responseText;

},

onError : function(x) { //提交失败回调

alert(x.statusText);

}

});

}

//export excel

function exportExcel() {

var selectbatch = $("selectbatch").value

location.href = '/customRequest.htm?catalog=exchange_export&selectbatch='+selectbatch;

}