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文件中的sheetHSSFSheet 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中導入查詢的數據---------------------- //創建HSSFWorkbookHSSFWorkbook 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" οnclick="exporExcel()">導出Excel</button></xblock><script>function exporExcel() {var obj = document.getElementById("contrller"); //定位idvar 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>

?

轉載于:https://www.cnblogs.com/appium/p/10008801.html

總結

以上是生活随笔為你收集整理的jsp+servlet 导出Excel表格的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。