如何将分页显示的网页数据快速导出树据到excel?_sql

需要写代码。

第一种是，修改查询代码，查询后直接写入 excel 。最简单快捷了

第二种是，将分页内容读取后写入 excel （很显然这个麻烦多了）。最关键是，excel支持 2000多页吗？不行。所以，需要把多页写入 1个sheet，当这个sheet满后，写下一个sheet

我写过的代码，仅供参考

类：package action

import java.io.IOException

import java.sql.Connection

import java.sql.DriverManager

import java.sql.ResultSet

import java.sql.SQLException

import java.sql.Statement

import java.util.ArrayList

import java.util.List

import javax.servlet.ServletException

import javax.servlet.http.HttpServlet

import javax.servlet.http.HttpServletRequest

import javax.servlet.http.HttpServletResponse

import data.User

public class UserListAction extends HttpServlet {

private static final long serialVersionUID = 1L

public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {

request.setCharacterEncoding("UTF-8")

response.setContentType("text/htmlcharset=UTF-8")

String sql = " SELECT COUNT(*) AS RECORD_COUNT FROM (SELECT * FROM system_user) AS RECORD_TABLE "

Connection conn = null

Statement stmt = null

ResultSet rs = null

int showPage = 1

int pageSize = 3

int recordCount = -1

int pageCount = -1

try {

Class.forName("com.mysql.jdbc.Driver")

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root")

stmt = conn.createStatement()

rs = stmt.executeQuery(sql)

if(rs.next()) {

recordCount = rs.getInt(1)

}

pageCount=(recordCount + pageSize -1) / pageSize//总页数

String strShowPage = request.getParameter("showPage")

if(null!= strShowPage &&!"".equals(strShowPage.trim())) {

showPage = Integer.parseInt(strShowPage.trim())

}

if(showPage <= 1) {

showPage = 1

} else if(showPage >= pageCount) {

showPage=pageCount

}

String querySql = "SELECT T1.* FROM system_user T1"

rs = stmt.executeQuery(querySql + " LIMIT " +( showPage - 1) * pageSize + " , " + pageSize)

List<User>userList = new ArrayList<User>()

while(rs.next()) {

userList.add(new User(rs.getInt(1),rs.getString(2),rs.getString(3)))

}

request.setAttribute("userList",userList)

request.setAttribute("showPage",showPage)

request.setAttribute("pageSize",pageSize)

request.setAttribute("recordCount",recordCount)

request.setAttribute("pageCount",pageCount)

} catch (ClassNotFoundException e) {

e.printStackTrace()

} catch (SQLException e) {

e.printStackTrace()

} finally {

try {

if(null !=rs &&! rs.isClosed()) {

rs.close()

}

if(null !=stmt &&! stmt.isClosed()) {

stmt.close()

}

if(null !=conn &&! conn.isClosed()) {

conn.close()

}

} catch (SQLException e) {

e.printStackTrace()

}

request.getRequestDispatcher("page03.jsp").forward(request, response)

}

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

this.doPost(request, response)

}

JavaBean:package data

public class User {

private Integer userid

private String username

private String password

public User() {

super()

}

public User(Integer userid, String username, String password) {

super()

this.userid = userid

this.username = username

this.password = password

}

public Integer getUserid() {

return userid

}

public void setUserid(Integer userid) {

this.userid = userid

}

public String getUsername() {

return username

}

public void setUsername(String username) {

this.username = username

}

public String getPassword() {

return password

}

public void setPassword(String password) {

this.password = password

}

数据库分页：<%@ page language="java" contentType="text/htmlcharset=UTF-8"pageEncoding="UTF-8"%>

<%@page import="java.sql.Connection"%>

<%@page import="java.sql.DriverManager"%>

<%@page import="java.sql.Statement"%>

<%@page import="java.sql.ResultSet"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>数据库分页</title>

</head>

<body>

String strShowPage = request.getParameter("showPage")

String sql = "SELECT T1.* FROM system_user T1"

String countSql = "SELECT COUNT(*) AS RECORD_COUNT FROM (SELECT T1.* FROM system_user T1) AS RECORED_TABLE"

int recordCount = 0

int pageSize = 3

int showPage = -1

Class.forName("com.mysql.jdbc.Driver")

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root")

Statement stmt = conn.createStatement()

//获取总记录数

ResultSet countRS = stmt.executeQuery(countSql)

if(countRS.next()){

recordCount = countRS.getInt(1)

}

//计算总页数

int pageCount = (recordCount + pageSize - 1) / pageSize

if(null == strShowPage || "".equals(strShowPage)){

showPage = 1

} else {

showPage = Integer.parseInt(strShowPage)

if(showPage<=1){

showPage = 1

}else if(showPage >= pageCount){

showPage = pageCount

}

//获取当前页记录信息

ResultSet rs = stmt.executeQuery(sql + " LIMIT " + (showPage - 1) * pageSize + " , " + pageSize)

<%while(rs.next()){%>

<%}%>

</table>

共<%=recordCount%>条记录

有<%=pageSize%>条记录

当前第<%=showPage%>页共<%=pageCount%>页

<%if(showPage == 1){%>

首页 上一页

<%}else{%>

<%}%>

<%if(showPage == pageCount){%>

下一页 尾页

<%}else{%>

<%}%>

<%conn.close()%>

</body>

</html>

分页：<%@ page language="java" contentType="text/htmlcharset=UTF-8"pageEncoding="UTF-8"%>

<%@page import="java.util.List" %>

<%@page import="data.User"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

</head>

<body>

<%List<?>userList = (List<?>)request.getAttribute("userList")%>

<%for(int i = 0i <userList.size()i++){ %>

<%}%>

</table>

int showPage = (Integer)request.getAttribute("showPage")

int pageSize = (Integer)request.getAttribute("pageSize")

int recordCount = (Integer)request.getAttribute("recordCount")

int pageCount = (Integer)request.getAttribute("pageCount")

<%if(showPage == 1){%>

首页上一页

<%}else{%>

<%}%>

<%if(showPage == pageCount){%>

下一页尾页

<%}else{%>

<%}%>

</body>

</html>

欢迎分享，转载请注明来源：内存溢出

原文地址: https://outofmemory.cn/sjk/10835531.html

如何将分页显示的网页数据快速导出树据到excel?

发表评论

评论列表（0条）