java导出excel 浏览器直接下载或者或以文件形式导出

java导出excel 浏览器直接下载或者或以文件形式导出

看代码吧~

/**

* excel表格直接下载

*/

public static void exportExcelByDownload(HSSFWorkbook wb,HttpServletResponse httpServletResponse,String fileName) throws Exception {

//响应类型为application/octet- stream情况下使用了这个头信息的话，那就意味着不想直接显示内容

httpServletResponse.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

//attachment为以附件方式下载

httpServletResponse.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(

fileName +http:// ".xls",

"utf-8"));

/**

* 代码里面使用Content-Disposition来确保浏览器弹出下载对话框的时候。

* response.addHeader("Content-Disposition","attachment");一定要确保没有做过关于禁止浏览器缓存的操作

*/

httpServletResponse.setHeader("Cache-Control", "No-cache");

httpServletResponse.flushBuffer();

wb.write(httpServletResponse.getOutputStream());

wb.close();

}

/**

* excel以文件的形式导出

* @throws Exception

*/

public static void exportExcelByFile(HSSFWorkbook wb,String fileName,String path) throws Exception{

ByteArrayOutputStream stream = new ByteArrayOutputStream();

wb.write(stream);

FileOutputStream outputStream = new FileOutputStream(path + fileName);

outputStream.write(stream.toByteArray());

stream.close();

outputStream.close();

}

java查询数据导出excel并返回给浏览器下载

效果图：

1.点击导出表按钮

2.接着就会出现下图

3.点击上图中的确定按钮再接着就会出现下图

4.点击上图中的保存按钮接着就会出现下图，浏览器下载完成后的提示

5.打开下载好的文件如下图

好了，废话不多少，上代码

jsp前端代码

时间：

-

消费类型：

所有

报名费

酒水零食类

支付状态：

所有

未支付

已支付

data-options="iconCls:'icon-search'" style="margin-left:10px">查询

data-options="iconCls:'icon-search'" style="margin-left:10px">查询

导出表

js前端代码

$(function() {

//导出excel表

$('#consumesOutExcel').on('click',function(){

exportExcel();

});

});

function exportExcel() {

$.messager.confirm('确认', '确认把该搜索结果导出Excel表格 ？', function(r) {

if (r) {

var startTime = $('#startDateConsume').val();

var endTime = $('#endDateConsume').val();

var consumesType = $('#consumesType').val();

var conPaymentStatus = $('#conPaymentStatus').val();

$.messager.progress({

title : '处理中',

msg : '请稍后',

});

$.messager.progress('close');

location.href="web/vip/exportExcel.xlsx?startTime=" rel="external nofollow" +startTime+"&endTime="+endTime+"&consumesType="+consumesType+"&conPaymentStatus="+conPaymentStatus;

}

});

}

java后端代码

@Controller

@RequestMapping("/vip")

public class VipController {

//文件下载：导出excel表

@RequestMapping(value = "/exportExcel.xlsx",method = RequestMethod.GET)

@ResponseBody

public void exportExcel(HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException{

//一、从后台拿数据

if (null == request || null == response)

{

return;

}

List list bLFBsbx= null;

String startTime = request.getParameter("startTime");

String endTime = request.getParameter("endTime");

int consumesType = Integer.parseInt(request.getParameter("consumesType"));

int conPaymentStatus =Integer.parseInt(request.getParameter("conPaymentStatus"));

VipConsumesExample example = new VipConsumesExample();

if(consumesType!=0 && conPaymentStatus!=0){

example.createCriteria().andTimeBetween(startTime, endTime).andConsumeTypeEqualTo(consumesType).andStatusEqualTo(conPaymentStatus);

}else if(consumesType ==0 && conPaymentStatus!=0) {

example.createCriteria().andTimeBetween(startTime, endTime).andStatusEqualTo(conPaymentStatus);

}else if(consumesType!=0 && conPaymentStatus==0){

example.createCriteria().andTimeBetween(startTime, endTime).andConsumeTypeEqualTo(consumesType);

}else {

example.createCriteria().andTimeBetween(startTime, endTime);

}

list = this.vipConsumesDao.selectByExample(example);

//二、 数据转成excel

request.setCharacterEncoding("UTF-8");

response.setCharacterEncoding("UTF-8");

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

String fileName = "消费记录.xlsx";

fileName = URLEncoder.encode(fileName, "UTF-8");

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

// 第一步：定义一个新的工作簿

XSSFWorkbook wb = new XSSFWorkbook();

// 第二步：创建一个Sheet页

XSSFSheet sheet = wb.createSheet("startTimeendTime");

sheet.setDefaultRowHeight((short) (2 * 256));//设置行高

sheet.setColumnWidth(0, 4000);//设置列宽

sheet.setColumnWidth(1,5500);

sheet.setColumnWidth(2,5500);

sheet.setColumnWidth(3,5500);

sheet.setColumnWidth(11,3000);

sheet.setColumnWidth(12,3000);

sheet.setColumnWidth(13,3000);

XSSFFont font = wb.createFont();

font.setFontName("宋体");

font.setFontHeightInPoints((short) 16);

XSSFRow row = sheet.createRow(0);

XSSFCell cell = row.createCell(0);

cell.setCellValue("流水号 ");

cell = row.createCell(1);

cell.setCellValue("微信名 ");

cell = row.createCell(2);

cell.setCellValue("微信订单号");

cell = row.createCell(3);

cell.setCellValue("消费时间");

cell = row.createCell(4);

cell.setCellValue("消费类型");

cell = row.createCell(5);

cell.setCellValue("剩余积分 ");

cell = row.createCell(6);

cell.setCellValue("新增积分 ");

cell = row.createCell(7);

cell.setCellValue("扣除积分 ");

cell = row.createCell(8);

cell.setCellValue("消费金额");

cell = row.createCell(9);

cell.setCellValue("支付方式");

cell = row.createCell(10);

cell.setCellValue("支付状态 ");

cell = row.createCell(11);

cell.setCellValue("钱包原始金额");

cell = row.createCell(12);

cell.setCellValue("钱包扣除金额");

cell = row.createCell(13);

cell.setCellValue("钱包剩余金额");

XSSFRow rows;

XSSFCell cells;

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

// 第三步：在这个sheet页里创建一行

rows = sheet.createRow(i+1);

// 第四步：在该行创建一个单元格

cells = rows.createCell(0);

// 第五步：在该单元格里设置值

cells.setCellValue(list.get(i).getConsumeId());

cells = rows.createCell(1);

cells.setCellValue(list.get(i).getName());

cells = rows.createCell(2);

cells.setCellValue(list.get(i).getOrderNumber());

cells = rows.createCell(3);

cells.setCellValue(list.get(i).getTime());

cells = rows.createCell(4);

if (list.get(i).getConsumeType() == 2) {

cells.setCellValue("酒水零食费");

} else {

cells.setCellValue("报名费");

}

cells = rows.createCell(5);

cells.setCellValue(list.get(i).getIntegral());

cells = rows.createCelbLFBsbxl(6);

cells.setCellValue(list.get(i).getIntegralIn());

cells = rows.createCell(7);

cells.setCellValue(list.get(i).getIntegralOut());

cells = rows.createCell(8);

cells.setCellValue(list.get(i).getMoney());

cells = rows.createCell(9);

if (list.get(i).getPayment() == 2) {

cells.setCellValue("积分抵现");

} else if (list.get(i).getPayment() == 3) {

cells.setCellValue("微信支付");

} else if (list.get(i).getPayment() == 4) {

cells.setCellValue("现金");

} else if (list.get(i).getPayment() == 1) {

cells.setCellValue("钱包");

}

cells = rows.createCell(10);

if (list.get(i).getStatus() == 2) {

cells.setCellValue("已支付");

} else if (list.get(i).getStatus() == 1) {

cells.setCellValue("未支付");

}

cells = rows.createCell(11);

cells.setCellValue(list.get(i).getWalletOriginal());

cells = rows.createCell(12);

cells.setCellValue(list.get(i).getWalletOut());

cells = rows.createCell(13);

cells.setCellValue(list.get(i).getWalletSurplus());

}

try {

OutputStream out = response.getOutputStream();

wb.write(out);

out.close();

wb.close();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。