Excel批量导出的一个Demo：
实体类：
package util;
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 domain.Person; public class DBConnectionManager { public Connection getConnection(){ Connection conn=null; try { Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://localhost:3306/ExcelDemo?user=root&password=123456"; conn = DriverManager.getConnection(url); } catch (Exception e) { e.printStackTrace(); } return conn; } public List<Person> queryDataList(String sql){ List<Person> list = new ArrayList<Person>(); Connection conn = getConnection(); for(int i=0;i<100000;i++){ Person per = new Person(); per.setId(i); per.setName("家第三方"); per.setRemark("合适的开发哈拉少发的好啦"); per.setCeshi("侧黑sIIS地方"); per.setGrend(i+"等级"); per.setSex(i+"男"); list.add(per); } try { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ Person per = new Person(); per.setId(rs.getInt("id")); per.setName(rs.getString("name")); per.setRemark(rs.getString("remark")); list.add(per); } } catch (SQLException e) { e.printStackTrace(); } return list; } } 工具类：package util;
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 domain.Person; public class DBConnectionManager { public Connection getConnection(){ Connection conn=null; try { Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://localhost:3306/ExcelDemo?user=root&password=123456"; conn = DriverManager.getConnection(url); } catch (Exception e) { e.printStackTrace(); } return conn; } public List<Person> queryDataList(String sql){ List<Person> list = new ArrayList<Person>(); Connection conn = getConnection(); for(int i=0;i<100000;i++){ Person per = new Person(); per.setId(i); per.setName("家第三方"); per.setRemark("合适的开发哈拉少发的好啦"); per.setCeshi("侧黑sIIS地方"); per.setGrend(i+"等级"); per.setSex(i+"男"); list.add(per); } try { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ Person per = new Person(); per.setId(rs.getInt("id")); per.setName(rs.getString("name")); per.setRemark(rs.getString("remark")); list.add(per); } } catch (SQLException e) { e.printStackTrace(); } return list; } } 压缩package util;
import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; public class FileZip { /** * * srcfile 文件名数组 * zipfile 压缩后文件 */ public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) { byte[] buf = new byte[1024]; try { ZipOutputStream out = new ZipOutputStream(new FileOutputStream( zipfile)); for (int i = 0; i < srcfile.length; i++) { FileInputStream in = new FileInputStream(srcfile[i]); out.putNextEntry(new ZipEntry(srcfile[i].getName())); int len; while ((len = in.read(buf)) > 0) { out.write(buf, 0, len); } out.closeEntry(); in.close(); } out.close(); } catch (IOException e) { e.printStackTrace(); } } } 导出package servlets;
import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import util.DBConnectionManager; import domain.Person; public class exportExcel extends HttpServlet { private String fileName; public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 文件名获取 Date date = new Date(); SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); String f = "Person-" + format.format(date); this.fileName = f; setResponseHeader(response); OutputStream out = null; try { System.out.println("导出excel开始~~~" + System.currentTimeMillis()); long startTime = System.currentTimeMillis(); out = response.getOutputStream(); DBConnectionManager db = new DBConnectionManager();// 该部分是用于链接数据库 List<Person> list = db.queryDataList("select * from person");// 查询数据集合 toExcel(list, request, 65000, f, out); System.out.println("导出excel结束~~~" + System.currentTimeMillis()); long endTime = System.currentTimeMillis(); System.out.println("导出excel共花费时间~~~" + (endTime - startTime) / 1000); } catch (IOException e1) { e1.printStackTrace(); } finally { try { out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } } } /** 设置响应头 */ public void setResponseHeader(HttpServletResponse response) { try { response.setContentType("application/octet-stream;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(this.fileName, "UTF-8") + ".zip"); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } public void init() throws ServletException { // Put your code here } @SuppressWarnings("deprecation") public void toExcel(List<Person> list, HttpServletRequest request, int length, String f, OutputStream out) throws IOException { List<String> fileNames = new ArrayList();// 用于存放生成的文件名称s // File zip = new File(request.getRealPath("/excel") + f + ".zip");// // 压缩文件 File zip = new File(request.getSession().getServletContext() .getRealPath("/excel") + f + ".zip");// 压缩文件 // 生成excel for (int j = 0, n = list.size() / length + 1; j < n; j++) { Workbook book = new HSSFWorkbook(); Sheet sheet = book.createSheet("person"); double d = 0;// 用来统计 String file = request.getSession().getServletContext() .getRealPath("/excel") + "/" + f + "-" + j+ ".xls"; fileNames.add(file); FileOutputStream o = null; try { o = new FileOutputStream(file); Row row = sheet.createRow(0); row.createCell(0).setCellValue("ID"); row.createCell(1).setCellValue("NAME"); row.createCell(2).setCellValue("REMARK"); row.createCell(3).setCellValue("sex"); row.createCell(4).setCellValue("grend"); row.createCell(5).setCellValue("ceshi"); int m = 1; for (int i = 1, min = (list.size() - j * length + 1) > (length + 1) ? (length + 1) : (list.size() - j * length + 1); i < min; i++) { m++; Person user = list.get(length * (j) + i - 1); row = sheet.createRow(i); row.createCell(0).setCellValue(user.getId()); row.createCell(1).setCellValue(user.getName()); row.createCell(2).setCellValue(user.getRemark()); row.createCell(3).setCellValue(user.getSex()); row.createCell(4).setCellValue(user.getGrend()); row.createCell(5).setCellValue(user.getCeshi()); } } catch (Exception e) { e.printStackTrace(); } try { book.write(o); } catch (Exception ex) { ex.printStackTrace(); } finally { if (o != null) { o.flush(); o.close(); } } } File srcfile[] = new File[fileNames.size()]; for (int i = 0, n = fileNames.size(); i < n; i++) { srcfile[i] = new File(fileNames.get(i)); } util.FileZip.ZipFiles(srcfile, zip); FileInputStream inStream = new FileInputStream(zip); byte[] buf = new byte[4096]; int readLength; while (((readLength = inStream.read(buf)) != -1)) { out.write(buf, 0, readLength); } inStream.close(); } } web.xml:<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <servlet> <servlet-name>SimpleUpload</servlet-name> <servlet-class>servlets.SimpleUpload</servlet-class> <!-- <load-on-startup>0</load-on-startup>--> </servlet> <servlet> <description>This is the description of my J2EE component</description> <display-name>This is the display name of my J2EE component</display-name> <servlet-name>Login</servlet-name> <servlet-class>servlets.Login</servlet-class> </servlet> <servlet> <description>This is the description of my J2EE component</description> <display-name>This is the display name of my J2EE component</display-name> <servlet-name>exportExcel</servlet-name> <servlet-class>servlets.exportExcel</servlet-class> </servlet> <servlet-mapping> <servlet-name>SimpleUpload</servlet-name> <url-pattern>/servlet/SimpleUpload</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>Login</servlet-name> <url-pattern>/servlet/Login</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>exportExcel</servlet-name> <url-pattern>/servlet/exportExcel</url-pattern> </servlet-mapping> <welcome-file-list> <welcome-file>exportExcel.jsp</welcome-file> </welcome-file-list> </web-app>