Java批量数据导出 java导出大量数据

1. 首先创建一个DTO类来存储要导出的数据

public class People {
     private String name;//名字
     private String age;//年龄
     public String getName() {
         return name;
     }
     public void setName(String name) {
         this.name = name;
     }
     public String getAge() {
         return age;
     }
     public void setAge(String age) {
         this.age = age;
     }
     public People(String name, String age) {
         super();
         this.name = name;
         this.age = age;
     }
     public People() {
         super();
     }
     
 }
2.创建一个servlet，导出excel
代码如下：
import java.io.IOException;
 import java.io.OutputStream;
 import java.util.ArrayList;
 import java.util.List;

 import javax.servlet.ServletException;
 import javax.servlet.annotation.WebServlet;
 import javax.servlet.http.HttpServlet;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;

 import org.apache.poi.hssf.usermodel.HSSFCell;
 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 import org.apache.poi.hssf.usermodel.HSSFRow;
 import org.apache.poi.hssf.usermodel.HSSFSheet;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 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.xssf.streaming.SXSSFWorkbook;

 /**
  * 简单的导出excel_demo
  * 
  */
 @WebServlet("/downExcel")
 public class downExcel extends HttpServlet {
     private static final long serialVersionUID = 1L;

     public downExcel() {
         super();

     }

     protected void doGet(HttpServletRequest request, HttpServletResponse response)
             throws ServletException, IOException {
         /**
          * 备注：HSSFWorkbook对象无法导出大批量的数据智能导出65535条(xls excel2003)， poi3.7版本后支持大批量数据导出xlsx(excel2007)
          * 
          * 但需要导入dom4j-1.6.1.jar 包（解析XML文件的包）
          * 使用方法基本一致，只是SXSSFWorkbook 对象在创建的过程中可以定义内存中的数据量，
          * 在运行时会把一部分数据写入到磁盘中，减少内存的压力
          * 结束后需要将临时工作簿备份到磁盘上wb.dispose();
          */
         //downExcel2003(request,response);
         downExcel2007(request,response);
         
     }

     /**
      * 旧版excel导出
      * @param request
      * @param response
      * @throws IOException 
      */
     public void downExcel2003(HttpServletRequest request, HttpServletResponse response)  {
         // 1.导入Excel需要的jar包,Maven在pom.xml 中添加,本案例见lib下
         /**
          * <!-- excel文件生成 --> <dependency> <groupId>org.apache.poi</groupId>
          * <artifactId>poi</artifactId> <version>3.9</version> </dependency>
          * 
          * <dependency> <groupId>org.apache.poi</groupId>
          * <artifactId>poi-ooxml</artifactId>
          * <version>3.9</version> </dependency>
          * <dependency> <groupId>org.apache.poi</groupId>
          * <artifactId>poi-scratchpad</artifactId>
          * <version>3.9</version> </dependency>
          * <dependency> <groupId>org.apache.poi</groupId>
          * <artifactId>poi-ooxml-schemas</artifactId>
          * <version>3.7</version> </dependency>
          * <dependency> <groupId>org.apache.xmlbeans</groupId>
          * <artifactId>xmlbeans</artifactId>
          * <version>2.3.0</version> </dependency>
          */
         // 2.创建一笔要导出的数据
         List<People> list = new ArrayList<People>();
         People p1 = new People("张三", "13");
         People p2 = new People("李四", "24");
         list.add(p1);
         list.add(p2);
         // 3.创建Excel 生成的类,即导入jar包中的类
         // 3-1.创建excel 必须的对象
         HSSFWorkbook wb = new HSSFWorkbook();
         // 3-2.创建样式对象
     
         HSSFCellStyle style = wb.createCellStyle();
         // 3-3.创建文件内容页面名称
         HSSFSheet sheet = wb.createSheet("人员信息页");
         // 3-4.设置居中样式
         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
         // 3-5.开始填充内容，创建第一行 即文件头标题的内容
         HSSFRow row = sheet.createRow((int) 0);
         // 3-5-1.向第一行，即文件头标题填充内容
         String[] title = new String[] { "姓名", "年龄" };
         for (int i = 0; i < title.length; i++) {
             // 获取第一行的第i个格子
             HSSFCell cell = row.createCell((short) i);
             cell.setCellValue(title[i]);
             // 设置每个格子样式
             cell.setCellStyle(style);
         }
         // 3-5-2.填充第二行以后的内容
         for (int i = 0; i < list.size(); i++) {
             // 创建一行写值
             row = sheet.createRow((int) i + 1);
             row.createCell((short) 0).setCellValue(list.get(i).getName());
             row.createCell((short) 1).setCellValue(list.get(i).getAge());
             row.createCell((short) 0).setCellStyle(style);
             row.createCell((short) 1).setCellStyle(style);
         }
         // 3-6.数据填充完毕后，导出
         response.setContentType("application/octet-stream");
         response.setHeader("Content-disposition", "attachment;filename=" + "demo.xls");
         // 开始关闭流
         OutputStream ouputStream = null;
         try {
             ouputStream = response.getOutputStream();
             wb.write(ouputStream);
             ouputStream.flush();
             ouputStream.close();
         } catch (Exception e) {
         
             e.printStackTrace();
         }
     
     }
     /**
      * 新版excel导出
      * @param request
      * @param response
      */
     private void downExcel2007(HttpServletRequest request, HttpServletResponse response) {
                 //1.再导入dom4j-1.6.1.jar包
                 // 2.创建一笔要导出的数据
                 List<People> list = new ArrayList<People>();
                 People p1 = new People("张三", "13");
                 People p2 = new People("李四", "24");
                 list.add(p1);
                 list.add(p2);
                 // 3.创建Excel 生成的类,即导入jar包中的类
                 // 3-1.创建excel 必须的对象 -- 内存中保留100条数据，其余写入磁盘
                 SXSSFWorkbook wb = new SXSSFWorkbook(100);
                 // 3-2.创建样式对象
             
                 CellStyle style = wb.createCellStyle();
                 // 3-3.创建文件内容页面名称
                 Sheet sheet = wb.createSheet("人员信息页");
                 // 3-4.设置居中样式
                 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                 // 3-5.开始填充内容，创建第一行 即文件头标题的内容
                 Row row = sheet.createRow((int) 0);
                 // 3-5-1.向第一行，即文件头标题填充内容
                 String[] title = new String[] { "姓名", "年龄" };
                 for (int i = 0; i < title.length; i++) {
                     // 获取第一行的第i个格子
                     Cell cell = row.createCell((short) i);
                     cell.setCellValue(title[i]);
                     // 设置每个格子样式
                     cell.setCellStyle(style);
                 }
                 // 3-5-2.填充第二行以后的内容
                 for (int i = 0; i < list.size(); i++) {
                     // 创建一行写值
                     row = sheet.createRow((int) i + 1);
                     row.createCell((short) 0).setCellValue(list.get(i).getName());
                     row.createCell((short) 1).setCellValue(list.get(i).getAge());
                     row.createCell((short) 0).setCellStyle(style);
                     row.createCell((short) 1).setCellStyle(style);
                 }
                 // 3-6.数据填充完毕后，导出
                 response.setContentType("application/octet-stream");
                 response.setHeader("Content-disposition", "attachment;filename=" + "demo.xls");
                 // 开始关闭流
                 OutputStream ouputStream = null;
                 try {
                     ouputStream = response.getOutputStream();
                     wb.write(ouputStream);
                     ouputStream.flush();
                     ouputStream.close();
                     // 3-7.将临时工作簿备份到磁盘上
                     wb.dispose();
                 } catch (Exception e) {
                 
                     e.printStackTrace();
                 }
         
     }
     protected void doPost(HttpServletRequest request, HttpServletResponse response)
             throws ServletException, IOException {

         doGet(request, response);
     }

 }