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);
}
}