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 {
/**
*
* @param srcfile 文件名数组
* @param 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>
8920
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
