jsp+servlet 导出Excel表格
1.项目的目录结构
2.创建一个用户类，下面会通过查询数据库把数据封装成用户实例列表
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; } }
3.ExcelUtil.java文件,创建Excel基础类
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; } }
4.查询数据库得到导出的数据
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; }
5.ExportExcelServlet.java,创建Excel响应流
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(); } } }
6.请求页面
<%@ 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>
