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我就传了，

 

另外这个也是我从别的地方转载的，修改了一些地方，也有很多不完善的地方，如果在项目中最好再做优化