比较好用的java导出大数据量Excel
转载于:http://blog.csdn.net/u010003835/article/details/51590101
注:代码还没仔细研究过……………..
原理:指定条数生成一个Excel文件,写入到本机,然后将这些Excel一起打成一个压缩包,发给客户端.
1.将多个文件一起生成压缩文件和设置Http响应头的Utils
package com.tiglle.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import javax.servlet.http.HttpServletResponse;
/**
*
* @author http://javaflex.iteye.com/
*
*/
public class FileZip {
/**
*
* @param srcfile 文件名数组
* @param zipfile 压缩后文件名称
* @throws IOException
*/
public static void ZipFiles(File[] srcfile, File zipfile) throws IOException {
if(zipfile.exists()){
zipfile.createNewFile();
}
byte[] buf = new byte[1024];
try {
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(
zipfile));
for (int i = 0;
i < srcfile.length;
i++) {
File tempFile = srcfile[i];
FileInputStream in = new FileInputStream(tempFile);
out.putNextEntry(new ZipEntry(srcfile[i].getName()));
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
out.closeEntry();
in.close();
//删除文件,免得占用服务器内存
tempFile.delete();
}
out.close();
} catch (IOException e) {
e.printStackTrace();
}
} /** 设置响应头信息和默认文件名称 */
/**
*
* @param response
* @param defaultFileName 默认文件名称
*/
public static void setResponseHeader(HttpServletResponse response,String defaultFileName) {
try {
response.setContentType("application/octet-stream;
charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;
filename=" + java.net.URLEncoder.encode(defaultFileName, "UTF-8")+ ".zip");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}/**
* 根据指定名称加时间生成字符串
*/
public static String generateName(String name){
Date date = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
return name + format.format(date);
}
}2.装数据的实体类:
package com.tiglle.bean;
public class Person {private Integer id;
private String name;
private String address;
private String tel;
private Double money=0.0;
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
public Person(Integer id, String name, String address, String tel,Double money) {
super();
this.id = id;
this.name = name;
this.address = address;
this.tel = tel;
this.money=money;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
}3.生成Excel和发送给客户端的servlet
package com.tiglle.servlet;
import Java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
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 org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import com.tiglle.bean.Person;
import com.tiglle.utils.FileZip;
/**
*
* @author http://javaflex.iteye.com/
*
*/
public class PersonServlet extends HttpServlet {public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
doGet(request, response);
} public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//文件名获取
String zipFileName = FileZip.generateName("中奖记录");
//设置响应头信息
FileZip.setResponseHeader(response, zipFileName);
OutputStream out = null;
try {
out = response.getOutputStream();
//模拟从数据库获得数据
List list = new ArrayList();
//PersonService.getPerson();
for(int i=0;
i<999999;
i++){
Person p = new Person(i, "xiaoming"+i, "湖北随机", "12123123", 30.3);
list.add(p);
}//每个xls文件的全路径
String fileName = request.getRealPath("/files") + "/" + zipFileName;
//生成n多个Excel,并将多个xls文件名+n用于区分,返回每个生成的xls的文件全路径
List fileNames = toExcel(list,10000,fileName,out);
//将生成的n个xls压缩成一个zip文件,并删除xls文件
File zip = new File(fileName + ".zip");
// 压缩文件
File srcfile[] = new File[fileNames.size()];
for (int i = 0, n = fileNames.size();
i < n;
i++) {
srcfile[i] = new File(fileNames.get(i));
//根据路径生成File对象
}
//压缩成zip
FileZip.ZipFiles(srcfile, zip);
//将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();
//删除zip文件,免得占用服务器内存
zip.delete();
} catch (IOException e1) {
e1.printStackTrace();
} finally {
try {
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}@SuppressWarnings({"rawtypes","unchecked","deprecation"})
/**
*
* @param list 里面组装的实体类
* @param request
* @param length 多少条分一个文件
* @param f 文件名称
* @param out
* @throws IOException
*/
public List toExcel(List【比较好用的java导出大数据量Excel】 list,/* HttpServletRequest request,*/int length, String fileName, OutputStream out) throws IOException {
List fileNames = new ArrayList();
// 用于存放生成的文件名称s
// 生成excel（一个对象一行）
for (int j = 0, n = list.size() / length + 1;
j < n;
j++) {
Workbook book = new HSSFWorkbook();
//创建Excel的第一页并指定名称
Sheet sheet = book.createSheet("中奖记录");
//将每个xls+j用于区分
String tempFileName = fileName + "-" + j + ".xls";
//将名字纪录起来
fileNames.add(tempFileName);
FileOutputStream o = null;
try {
o = new FileOutputStream(tempFileName);
// sheet.addMergedRegion(new
// CellRangeAddress(list.size()+1,0,list.size()+5,6));
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("ID");
row.createCell(1).setCellValue("NAME");
row.createCell(2).setCellValue("ADDRESS");
row.createCell(3).setCellValue("TEL");
row.createCell(4).setCellValue("Money");
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);
Double dd = user.getMoney();
if (dd == null) {
dd = 0.0;
}
row = sheet.createRow(i);
row.createCell(0).setCellValue(user.getId());
row.createCell(1).setCellValue(user.getName());
row.createCell(2).setCellValue(user.getAddress());
row.createCell(3).setCellValue(user.getTel());
row.createCell(4).setCellValue(dd);
}
CellStyle cellStyle2 = book.createCellStyle();
cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);
row = sheet.createRow(m);
// Cell cell0 = row.createCell(0);
// cell0.setCellValue(“Total”);
// cell0.setCellStyle(cellStyle2);
// Cell cell4 = row.createCell(4);
// cell4.setCellValue(d);
// cell4.setCellStyle(cellStyle2);
//没注意看
sheet.addMergedRegion(new CellRangeAddress(m, m, 0, 3));
} catch (Exception e) {
e.printStackTrace();
}
try {
book.write(o);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
book.close();
o.flush();
o.close();
}
}
return fileNames;
}
}推荐阅读
- 《跨界歌手》:亲情永远比爱情更有泪点
- 烦恼和幸福
- 汇讲-勇于突破
- 幸福是个比较级
- 做学生的良师益友
- 呼吸练习心得
- python青少年编程比赛_第十一届蓝桥杯大赛青少年创意编程组比赛细则
- 厨房装修如何选择水槽（一个干净好用的厨房，从这里开始。）
- 很多网红在用的素颜霜，你知道它是属于护肤品还是化妆品吗（）
- 西安临潼区夏令营性价比高