jsp+servlet 导出Exsle表格

package csh.entity;/** * @author 悦文 * @create 2018-10-24 18:41 **/public class Order { ???private int id; ???private String name; ???private int status; ???private String updatetime; ???public Order(int id, String name, int status, String updatetime) { ???????this.id = id; ???????this.name = name; ???????this.status = status; ???????this.updatetime = updatetime; ???} ???public int getId() { ???????return id; ???} ???public void setId(int id) { ???????this.id = id; ???} ???public String getName() { ???????return name; ???} ???public void setName(String name) { ???????this.name = name; ???} ???public int getStatus() { ???????return status; ???} ???public void setStatus(int status) { ???????this.status = status; ???} ???public String getUpdatetime() { ???????return updatetime; ???} ???public void setUpdatetime(String updatetime) { ???????this.updatetime = updatetime; ???}}

package jdbc.dao;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.HorizontalAlignment;/** * 创建Excel基础类 * * @author 悦文 * @create 2018-11-15 10:07 **/public class ExcelUtil { ???public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) { ???????// 第一步，创建一个HSSFWorkbook，对应一个Excel文件 ???????if (wb == null) { ???????????wb = new HSSFWorkbook(); ???????} ???????// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet ???????HSSFSheet sheet = wb.createSheet(sheetName); ???????// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 ???????HSSFRow row = sheet.createRow(0); ???????// 第四步，创建单元格，并设置值表头 设置表头居中 ???????HSSFCellStyle style = wb.createCellStyle(); ???????style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式 ???????//声明列对象 ???????HSSFCell cell = null; ???????//创建标题 ???????for (int i = 0; i < title.length; i++) { ???????????cell = row.createCell(i); ???????????cell.setCellValue(title[i]); ???????????cell.setCellStyle(style); ???????} ???????//创建内容 ???????for (int i = 0; i < values.length; i++) { ???????????row = sheet.createRow(i + 1); ???????????for (int j = 0; j < values[i].length; j++) { ???????????????//将内容按顺序赋给对应的列对象 ???????????????row.createCell(j).setCellValue(values[i][j]); ???????????} ???????} ???????return wb; ???}}

public static List<Order> getOrderByDate(String start, String end, int currentpage, int maximum) throws SQLException { ???????//int id = -1; ???????try { ???????????//HH:mm:ss:SSS ???????????//currentpage=currentpage-1; ???????????//分页起始点 ???????????int maximumstart = maximum * (currentpage - 1); ???????????Long startstamp = Timestamp.dateToStamp(start + ":000"); ???????????Long endstamp = Timestamp.dateToStamp(end + ":000"); ???????????Connection conn = Dao.getConnection(); ???????????//select * from order_user WHERE update_time BETWEEN 1541001600000 AND 1543507200000 GROUP BY update_time DESC limit 0,2 ; ???????????PreparedStatement ps = (PreparedStatement) conn ???????????????????.prepareStatement("select * from order_user WHERE update_time BETWEEN ? AND ? GROUP BY id DESC ?limit " + maximumstart + "," + maximum); ???????????ps.setLong(1, startstamp); ???????????ps.setLong(2, endstamp);// ???????????ps.setInt(3, Integer.parseInt(contrller)); ???????????// ps.setString(1, name); ???????????ResultSet rs = ps.executeQuery(); ???????????List<Order> orderlist = new ArrayList(); ???????????while (rs.next()) { ???????????????int id = rs.getInt("id"); ???????????????String name = rs.getString("name"); ???????????????int status = rs.getInt("status"); ???????????????long updatetime = rs.getLong("update_time"); ???????????????String updatetimes = String.valueOf(updatetime); ???????????????Order order = new Order(id, name, status, Timestamp.stampToDate(updatetimes)); ???????????????orderlist.add(order); ???????????} ???????????Dao.close(rs, ps, conn); ???????????return orderlist; ???????} catch (SQLException e) { ???????????e.printStackTrace(); ???????????return null; ???????} catch (ParseException e) { ???????????e.printStackTrace(); ???????} ???????return null; ???}

package com.csh.servlet;import csh.entity.Order;import jdbc.dao.ExcelUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import search.SearchDao;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.OutputStream;import java.io.UnsupportedEncodingException;import java.util.List;import java.util.Map;/** * Created by admin on 2018/11/16. */@WebServlet(name = "ExportExcelServlet")public class ExportExcelServlet extends HttpServlet { ???protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ???????String start = ""; ???????String end = ""; ???????String contrller = ""; ???????String uri = request.getRequestURL() + (request.getQueryString() != null ? "?" + request.getQueryString() : ""); ???????System.out.println(uri); ???????Map<String, String[]> querymap = request.getParameterMap(); ???????//判断不为空 ???????for (String key : querymap.keySet()) { ???????????String t = querymap.get(key)[0]; ???????????if (key.equals("startdate")) { ???????????????if (!t.equals("")) { ???????????????????start = querymap.get(key)[0]; ???????????????} ???????????} else if (key.equals("startend")) { ???????????????if (!t.equals("")) { ???????????????????end = querymap.get(key)[0]; ???????????????} ???????????} else if (key.equals("contrller")) { ???????????????if (!t.equals("")) { ???????????????????contrller = querymap.get(key)[0]; ???????????????} ???????????} ???????} ???????if (start.equals("") || end.equals("") || start.equals("null") || end.equals("null")) { ???????????//判断时间为空且状态为空 ???????????if (contrller.equals("") || contrller.equals("null")) { ???????????????try { ???????????????????export(request, response,SearchDao.getAll()); ???????????????} catch (Exception e) { ???????????????????e.printStackTrace(); ???????????????} ???????????????//判断时间为空但是状态不为空 ???????????} else { ???????????????try { ???????????????????export(request, response,SearchDao.exportOrderByStatus(contrller)); ???????????????} catch (Exception e) { ???????????????????e.printStackTrace(); ???????????????} ???????????} ???????} else { ???????????//判断时间不为空且状态不为空 ???????????if (!contrller.equals("") && !contrller.equals("null")) { ???????????????try { ???????????????????export(request, response,SearchDao.exportOrderByDateAndContrller(start, end, contrller)); ???????????????} catch (Exception e) { ???????????????????e.printStackTrace(); ???????????????} ???????????} else { ???????????????//判断时间不为空，状态为空 ???????????????try { ???????????????????export(request, response,SearchDao.exportOrderByDate(start, end)); ???????????????} catch (Exception e) { ???????????????????e.printStackTrace(); ???????????????} ???????????} ???????} ???} ???protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ???????doPost(request, response); ???} ???public void export(HttpServletRequest request, HttpServletResponse response,List<Order> order) throws Exception { ???????//获取数据 ???????List<Order> list = order; ???????//excel标题 ???????String[] title = {"ID", "name", "status", "update_time", "comment"}; ???????//excel文件名 ???????String fileName = "订餐统计表" + System.currentTimeMillis() + ".xls"; ???????//sheet名 ???????String sheetName = "订餐统计"; ???????String[][] content=new String[list.size()][title.length]; ???????for (int i = 0; i < list.size(); i++) { ???????????content[i] = new String[title.length]; ???????????Order obj = list.get(i); ???????????content[i][0] = String.valueOf(obj.getId()); ???????????content[i][1] = obj.getName(); ???????????if(obj.getStatus()==1){ ???????????????content[i][2] = "加班已订餐"; ???????????}else if(obj.getStatus()==2){ ???????????????content[i][2] = "加班未订餐"; ???????????} ???????????//content[i][2] = String.valueOf(obj.getStatus()); ???????????content[i][3] = obj.getUpdatetime(); ???????????//content[i][4] = obj.get("comment").tostring(); ???????}
//------------------------以下是向excel中导入查询的数据----------------------//创建HSSFWorkbook ???????HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);//响应到客户端 ???????try { ???????????this.setResponseHeader(response, fileName); ???????????OutputStream os = response.getOutputStream(); ???????????wb.write(os); ???????????os.flush(); ???????????os.close(); ???????} catch (Exception e) { ???????????e.printStackTrace(); ???????} ???} ???//发送响应流方法 ???public void setResponseHeader(HttpServletResponse response, String fileName) { ???????try { ???????????try { ???????????????fileName = new String(fileName.getBytes(), "ISO8859-1"); ???????????} catch (UnsupportedEncodingException e) { ???????????????// TODO Auto-generated catch block ???????????????e.printStackTrace(); ???????????} ???????????response.setContentType("application/octet-stream;charset=ISO8859-1"); ???????????response.setHeader("Content-Disposition", "attachment;filename=" + fileName); ???????????response.addHeader("Pargam", "no-cache"); ???????????response.addHeader("Cache-Control", "no-cache"); ???????} catch (Exception ex) { ???????????ex.printStackTrace(); ???????} ???}}

<%@ page contentType="text/html;charset=UTF-8" language="java" %><%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><html><head> ???<meta charset="UTF-8"> ???<title>欢迎页面-X-admin2.0</title> ???<meta name="renderer" content="webkit"> ???<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"> ???<meta name="viewport" ?????????content="width=device-width,user-scalable=yes, minimum-scale=0.4, initial-scale=0.8,target-densitydpi=low-dpi"/> ???<link rel="shortcut icon" href="/favicon.ico" type="image/x-icon"/> ???<link rel="stylesheet" href="./css/font.css"> ???<link rel="stylesheet" href="./css/xadmin.css"> ???<script type="text/javascript" src="https://cdn.bootcss.com/jquery/3.2.1/jquery.min.js"></script> ???<script type="text/javascript" src="./lib/layui/layui.js" charset="utf-8"></script> ???<script type="text/javascript" src="./js/xadmin.js"></script> ???<!-- 让IE8/9支持媒体查询，从而兼容栅格 --> ???<!--[if lt IE 9]> ???<script src="https://cdn.staticfile.org/html5shiv/r29/html5.min.js"></script> ???<script src="https://cdn.staticfile.org/respond.js/1.4.2/respond.min.js"></script> ???<![endif]--></head><body> ??<xblock> ???????<button class="layui-btn layui-btn-danger" onclick="exporExcel()">导出Excel</button> ???</xblock><script> ???function exporExcel() { ???????var obj = document.getElementById("contrller"); //定位id ???????var index = obj.selectedIndex; // 选中索引 ???????//var text = obj.options[index].text; // 选中文本 ???????var contrller = obj.options[index].value; // 选中值 ???????var startdate = document.getElementById("start").value; ???????var startend = document.getElementById("end").value; ???????window.location.href = "/javawebservlet/ExportExcelServlet?startdate="+startdate+"&"+"startend="+startend+"&"+"contrller="+contrller; ???}</script></body></html>