最近做java的web项目,用到了excel导出到客户端(本地),通过IE可以实现用浏览器保存、另存为、取消。我使用的是POI来完成的,poi架包是poi-3.7-20101029.jar这个架包在网上很容易找到。话不多说,进入实战demo。
项目结构:
第一步用MyEclipse创建一个web项目,名称:excelExport
第二步将下载的poi架包放到lib目录下,并添加到Build Path中:右击架包--》Build Path--》Add to Build Path.
第三步创建一个servlet,名称为:ExcelExportServlet.java,其代码如下:
- package com.lujinyong.servlet;
-
- import java.io.FileNotFoundException;
- import java.io.IOException;
- import java.io.OutputStream;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- 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.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFFont;
- import org.apache.poi.hssf.usermodel.HSSFRichTextString;
- 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.hssf.util.HSSFColor;
-
- public class ExcelExportServlet extends HttpServlet {
- //访问网址:http://localhost:8080/excelExport/servlet/ExcelExportServlet
- public void doGet(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- response.setContentType("octets/stream");
- // response.addHeader("Content-Disposition", "attachment;filename=test.xls");
- String excelName = "学生信息表";
- //转码防止乱码
- response.addHeader("Content-Disposition", "attachment;filename="+new String( excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");
- String[] headers = new String[]{"编号","姓名","年龄","性别"};
- try {
- OutputStream out = response.getOutputStream();
- exportExcel(excelName,headers, getList(), out,"yyyy-MM-dd");
- out.close();
- System.out.println("excel导出成功!");
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
-
- public void doPost(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- doGet(request, response);
- }
- /**
- *
- * @Description: 模拟从数据库中查询出来的数据,一般是数据表中的几列
- * @Auther: lujinyong
- * @Date: 2013-8-22 下午2:53:58
- */
- public List<Map<String,Object>> getList(){
- List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
- for(int i = 0; i<100;i++){
- Map<String,Object> map = new HashMap<String, Object>();
- map.put("number",1000+i);
- map.put("name", "张三"+i);
- int age = (int)(Math.random()*100);
- do{
- age = (int)(Math.random()*100);
- }while(age<10||age>15);
- map.put("age", age);
- map.put("sex", age%2==0?0:1);//获得随机性别
- list.add(map);
- }
- return list;
- }
- /**
- *
- * @Description: 生成excel并导出到客户端(本地)
- * @Auther: lujinyong
- * @Date: 2013-8-22 下午3:05:49
- */
- protected void exportExcel(String title,String[] headers,List mapList,OutputStream out,String pattern){
- //声明一个工作簿
- HSSFWorkbook workbook = new HSSFWorkbook();
- //生成一个表格
- HSSFSheet sheet = workbook.createSheet(title);
- //设置表格默认列宽度为15个字符
- sheet.setDefaultColumnWidth(20);
- //生成一个样式,用来设置标题样式
- HSSFCellStyle style = workbook.createCellStyle();
- //设置这些样式
- style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- //生成一个字体
- HSSFFont font = workbook.createFont();
- font.setColor(HSSFColor.VIOLET.index);
- font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- //把字体应用到当前的样式
- style.setFont(font);
- // 生成并设置另一个样式,用于设置内容样式
- HSSFCellStyle style2 = workbook.createCellStyle();
- style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
- style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
- style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
- style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- // 生成另一个字体
- HSSFFont font2 = workbook.createFont();
- font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
- // 把字体应用到当前的样式
- style2.setFont(font2);
- //产生表格标题行
- HSSFRow row = sheet.createRow(0);
- for(int i = 0; i<headers.length;i++){
- HSSFCell cell = row.createCell(i);
- cell.setCellStyle(style);
- HSSFRichTextString text = new HSSFRichTextString(headers[i]);
- cell.setCellValue(text);
- }
- for (int i=0;i<mapList.size();i++) {
- Map<String,Object> map = (Map<String, Object>) mapList.get(i);
- row = sheet.createRow(i+1);
- int j = 0;
- Object value = null;
- value=map.get("number");
- if(value instanceof Integer){
- row.createCell(j++).setCellValue(String.valueOf(value));
- }
- row.createCell(j++).setCellValue(map.get("name").toString());
- value=map.get("age");
- if(value instanceof Integer){
- row.createCell(j++).setCellValue(String.valueOf(value));
- }
- row.createCell(j++).setCellValue("0".equals(map.get("sex").toString())?"女":"男");
- }
- try {
- workbook.write(out);
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
-
- }
第四步:将工程发布的tomcat中,运行,打开浏览器访问。如何知道访问的地址呢?打开WEB-INF下面的web.xml文件,我的是MyEclipse创建servlet时自动配置好的,代码如下,红色部分很关键,就是你访问网址最后面的两个excelExport是你的项目名称:
[url]
http://localhost:8080/excelExport/servlet/ExcelExportServlet
[/url]
- <?xml version="1.0" encoding="UTF-8"?>
- <web-app version="2.5"
- xmlns="http://java.sun.com/xml/ns/javaee"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
- http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
- <display-name></display-name>
- <servlet>
- <description>This is the description of my J2EE component</description>
- <display-name>This is the display name of my J2EE component</display-name>
- <servlet-name>ExcelExportServlet</servlet-name>
- <servlet-class>com.lujinyong.servlet.ExcelExportServlet</servlet-class>
- </servlet>
- <servlet-name>ExcelExportServlet</servlet-name>
- <url-pattern>[color=red]/servlet/ExcelExportServlet[/color]</url-pattern>
- </servlet-mapping>
- <welcome-file-list>
- <welcome-file>index.jsp</welcome-file>
- </welcome-file-list>
- </web-app>
在浏览器中打开即可该网址就可以将“学生信息表.xls”下载下来。
转载请注明出处:http://kevin12.iteye.com/blog/1929341
- package com.lujinyong.servlet;
- import java.io.FileNotFoundException;
- import java.io.IOException;
- import java.io.OutputStream;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- 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.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFFont;
- import org.apache.poi.hssf.usermodel.HSSFRichTextString;
- 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.hssf.util.HSSFColor;
- public class ExcelExportServlet extends HttpServlet {
- //访问网址:http://localhost:8080/excelExport/servlet/ExcelExportServlet
- public void doGet(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- response.setContentType("octets/stream");
- // response.addHeader("Content-Disposition", "attachment;filename=test.xls");
- String excelName = "学生信息表";
- //转码防止乱码
- response.addHeader("Content-Disposition", "attachment;filename="+new String( excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");
- String[] headers = new String[]{"编号","姓名","年龄","性别"};
- try {
- OutputStream out = response.getOutputStream();
- exportExcel(excelName,headers, getList(), out,"yyyy-MM-dd");
- out.close();
- System.out.println("excel导出成功!");
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- public void doPost(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- doGet(request, response);
- }
- /**
- *
- * @Description: 模拟从数据库中查询出来的数据,一般是数据表中的几列
- * @Auther: lujinyong
- * @Date: 2013-8-22 下午2:53:58
- */
- public List<Map<String,Object>> getList(){
- List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
- for(int i = 0; i<100;i++){
- Map<String,Object> map = new HashMap<String, Object>();
- map.put("number",1000+i);
- map.put("name", "张三"+i);
- int age = (int)(Math.random()*100);
- do{
- age = (int)(Math.random()*100);
- }while(age<10||age>15);
- map.put("age", age);
- map.put("sex", age%2==0?0:1);//获得随机性别
- list.add(map);
- }
- return list;
- }
- /**
- *
- * @Description: 生成excel并导出到客户端(本地)
- * @Auther: lujinyong
- * @Date: 2013-8-22 下午3:05:49
- */
- protected void exportExcel(String title,String[] headers,List mapList,OutputStream out,String pattern){
- //声明一个工作簿
- HSSFWorkbook workbook = new HSSFWorkbook();
- //生成一个表格
- HSSFSheet sheet = workbook.createSheet(title);
- //设置表格默认列宽度为15个字符
- sheet.setDefaultColumnWidth(20);
- //生成一个样式,用来设置标题样式
- HSSFCellStyle style = workbook.createCellStyle();
- //设置这些样式
- style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- //生成一个字体
- HSSFFont font = workbook.createFont();
- font.setColor(HSSFColor.VIOLET.index);
- font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- //把字体应用到当前的样式
- style.setFont(font);
- // 生成并设置另一个样式,用于设置内容样式
- HSSFCellStyle style2 = workbook.createCellStyle();
- style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
- style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
- style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
- style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- // 生成另一个字体
- HSSFFont font2 = workbook.createFont();
- font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
- // 把字体应用到当前的样式
- style2.setFont(font2);
- //产生表格标题行
- HSSFRow row = sheet.createRow(0);
- for(int i = 0; i<headers.length;i++){
- HSSFCell cell = row.createCell(i);
- cell.setCellStyle(style);
- HSSFRichTextString text = new HSSFRichTextString(headers[i]);
- cell.setCellValue(text);
- }
- for (int i=0;i<mapList.size();i++) {
- Map<String,Object> map = (Map<String, Object>) mapList.get(i);
- row = sheet.createRow(i+1);
- int j = 0;
- Object value = null;
- value=map.get("number");
- if(value instanceof Integer){
- row.createCell(j++).setCellValue(String.valueOf(value));
- }
- row.createCell(j++).setCellValue(map.get("name").toString());
- value=map.get("age");
- if(value instanceof Integer){
- row.createCell(j++).setCellValue(String.valueOf(value));
- }
- row.createCell(j++).setCellValue("0".equals(map.get("sex").toString())?"女":"男");
- }
- try {
- workbook.write(out);
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- <?xml version="1.0" encoding="UTF-8"?>
- <web-app version="2.5"
- xmlns="http://java.sun.com/xml/ns/javaee"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
- http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
- <display-name></display-name>
- <servlet>
- <description>This is the description of my J2EE component</description>
- <display-name>This is the display name of my J2EE component</display-name>
- <servlet-name>ExcelExportServlet</servlet-name>
- <servlet-class>com.lujinyong.servlet.ExcelExportServlet</servlet-class>
- </servlet>
- <servlet-name>ExcelExportServlet</servlet-name>
- <url-pattern>[color=red]/servlet/ExcelExportServlet[/color]</url-pattern>
- </servlet-mapping>
- <welcome-file-list>
- <welcome-file>index.jsp</welcome-file>
- </welcome-file-list>
- </web-app>