本文主要是介绍java中生成30万的excel（采用多个excel，每个上面放6万数据，最后打包zip保存），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
首先要说明的是excel03 每个sheet最多放65535行所以，每行不能超过这个数，如果想放的多，可以考虑生成excel2007，
好像excel2007可以放100W多行数据
大数据量生成excel我只想到三种方法，当然基本也是网上看到的
1 生成多个excel打包 2 利用xml方式生成 3 用最新的包
现在讲的是第一种 第二种也可以以后会做下测试 第三种好像不行，一直内存溢出
直接上代码
这个是servlet：
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.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
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 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 TEST_EXPORT ");//查询数据集合toExcel(list, request, 50000, 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");// 压缩文件 // 生成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.getRealPath("/excel") + "/" + f + "-" + j + ".xls";fileNames.add(file);FileOutputStream o = null;try {o = new FileOutputStream(file);// 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("REMARK");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());}
// 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 {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();}
}
实体类
package domain;
public class Person {private String id;private String name;private String remark;public String getId() {return id;}public void setId(String id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getRemark() {return remark;}public void setRemark(String remark) {this.remark = remark;}}
这个是打包的工具类
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(); } } }
jsp我就传了，
另外这个也是我从别的地方转载的，修改了一些地方，也有很多不完善的地方，如果在项目中最好再做优化
这篇关于java中生成30万的excel（采用多个excel，每个上面放6万数据，最后打包zip保存）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
