java通过查询结果导出Excel java通过查询结果导出Excel

1.前端发送请求：

$("#exp").click(function() { var state = $(" ").val(); //根据自己命名的标签获取值 var address = $(" ").val(); var netname = $(" ").text(); window.location.href = https://www.it610.com/article/encodeURI("${APP_PATH }/re?state="+state+"&ad="+address+"&n="+netname); });

2.controller接收请求

@RequestMapping("/re") public String getmsg(HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException{ request.setCharacterEncoding("utf-8"); String re = request.getParameter("state"); String ad = request.getParameter("ad"); String n = request.getParameter("n"); String state = new String(re.getBytes("ISO-8859-1"),"UTF-8"); String address = new String(ad.getBytes("ISO-8859-1"),"UTF-8"); String netname = new String(n.getBytes("ISO-8859-1"),"UTF-8"); Record record= new Record(address, null, null, netname, state, null); List list = irs.recordsByCheck(record); List title = new ArrayList(); title.add(" "); //这里添加的是你的table里面所有里的值 title.add(" "); title.add(" "); title.add(" "); title.add(" "); title.add(" "); title.add(" "); ExcelUtil.createExcel(request, response, list, "命名Excel", title); return null; }

3.这里用到一个封装类ExcelUtil

package com.aobo.util; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.List; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import com.aobo.entity.Record; public class ExcelUtil { public static void createExcel(HttpServletRequest request, HttpServletResponse response, List list, String fileName, List title){ try { // 创建Excel的工作书册 Workbook,对应到一个excel文档 HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle(); // 生成一个字体 HSSFFont font = workbook.createFont(); // 字体增粗 //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 11); style.setFont(font); // 创建Excel的工作sheet,对应到一个excel文档的tab HSSFSheet sheet = workbook.createSheet("sheet1"); // 创建Excel的sheet的一行 (表头) HSSFRow row = sheet.createRow(0); // 表头内容填充 for (int i = 0; i < title.size(); i++) { // 设置excel每列宽度 sheet.setColumnWidth(i, 5000); HSSFCell cell = row.createCell(i); cell.setCellValue(title.get(i)); cell.setCellStyle(style); } // 创建内容行 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setWrapText(true); // 自动换行 cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); for (int j = 0; j < list.size(); j++) { HSSFRow contentRow = sheet.createRow(j + 1); for (int k = 0; k < title.size(); k++) { HSSFCell cell = contentRow.createCell(k); switch (k) { case 0: if (list.get(j).getAddress() != null) {// 每一列的名称 cell.setCellValue(list.get(j).getAddress()); } break; case 1: if (list.get(j).getShake() != null) {//每一列的名称 cell.setCellValue(list.get(j).getShake() ); } break; case 2: if (list.get(j).getNetname() != null) {//每一列的名称 cell.setCellValue(list.get(j).getNetname()); } break; case 3: if (list.get(j).getNet() != null) {// cell.setCellValue(list.get(j).getNet()); } break; case 4: if (list.get(j).getState() != null) {// cell.setCellValue(list.get(j).getState()); } break; case 5: if (list.get(j).getUsetime() >0 ) {// cell.setCellValue(list.get(j).getUsetime()); } break; case 6: if (list.get(j).getTime() != null) {/ cell.setCellValue(list.get(j).getTime()); } break; default: break; } cell.setCellStyle(cellStyle); } }ByteArrayOutputStream os = new ByteArrayOutputStream(); try { workbook.write(os); } catch (IOException e) { e.printStackTrace(); } byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 response.reset(); response.setContentType("application/vnd.ms-excel; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + new String((fileName + ".xls").getBytes("gb2312"), "iso-8859-1")); ServletOutputStream out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (final IOException e) { throw e; } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); } } catch (Exception e) { e.printStackTrace(); } } }

最后的效果，点击导出，浏览器左下角出现： 【java通过查询结果导出Excel】