比较好用的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<Person> list = new ArrayList<Person>();//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<String> 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<String> toExcel(List<Person> list,/* HttpServletRequest request,*/int length, String fileName, OutputStream out) throws IOException {      List<String> 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;
}
}

“`