JavaSpringMVC框架开发之数据导出Excel文件格式实例详解

<%@ page language="java" contentType="text/html; charset=UTF-8"
  pageEncoding="UTF-8"%>






  导出

public class Person {
  private String name;
  private String age;
  private String addr;
  private String sex;
  // set/get方法和构造器省略。。。

package com.export.action;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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 com.export.pojo.Person;
/**
 * 数据导出到Excel工具类
 * 
 * @author zhang_cq
 * 
 */
public class ExportUtil {
  /**
   * 设置导出Excel的表名
   * 
   * @return
   */
  public String getSheetName() {
    return "测试导出数据";
  }
  /**
   * 设置导出Excel的列名
   * 
   * @return
   */
  public String getSheetTitleName() {
    return "序号,姓名,年龄,居住地,性别";
  }
  /**
   * 创建 sheet 的第一行,标题行
   * 
   * @param sheet
   * @param strTitle
   */
  private void createSheetTitle(HSSFSheet sheet, String strTitle) {
    HSSFRow row = sheet.createRow(0); // 创建该表格(sheet)的第一行
    sheet.setDefaultColumnWidth(4);
    HSSFCell cell = null;
    String[] strArray = strTitle.split(",");
    for (int i = 0; i < strArray.length; i++) {
      cell = row.createCell(i); // 创建该行的第一列
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      cell.setCellValue(new HSSFRichTextString(strArray[i]));
    }
  }
  @SuppressWarnings("resource")
  public InputStream getExcelStream(List personList) throws IOException {
    // 创建一个 Excel 文件
    HSSFWorkbook wb = new HSSFWorkbook();
    // 创建一个表格 Sheet
    HSSFSheet sheet = wb.createSheet(this.getSheetName());
    // 创建 sheet 的第一行,标题行
    // 行号从0开始计算
    this.createSheetTitle(sheet, this.getSheetTitleName());
    // 设置 sheet 的主体内容
    this.createSheetBody(personList, sheet);
    ByteArrayOutputStream output = new ByteArrayOutputStream();
    wb.write(output);
    byte[] ba = output.toByteArray();
    InputStream is = new ByteArrayInputStream(ba);
    return is;
  }
  private void createSheetBody(List personList, HSSFSheet sheet) {
    if (personList == null || personList.size() < 1) {
      return;
    }
    // 表格(sheet) 的第二行, 第一行是标题, Excel中行号, 列号 是由 0 开始的
    int rowNum = 1;
    HSSFCell cell = null;
    HSSFRow row = null;
    for (Iterator it = personList.iterator(); it.hasNext(); rowNum++) {
      Person person = (Person) it.next();
      if (person == null)
        person = new Person();
      row = sheet.createRow(rowNum);
      int i = 0;
      cell = row.createCell(i++);
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      cell.setCellValue(new HSSFRichTextString(rowNum + ""));
      cell = row.createCell(i++); // name
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      cell.setCellValue(new HSSFRichTextString(person.getName()));
      cell = row.createCell(i++); // age
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      cell.setCellValue(new HSSFRichTextString(person.getAge()));
      cell = row.createCell(i++); // addr
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      cell.setCellValue(new HSSFRichTextString(person.getAddr()));
      cell = row.createCell(i++); // sex
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      cell.setCellValue(new HSSFRichTextString(person.getSex()));
    }
  }
}

package com.export.action;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.export.pojo.Person;
/**
 * @author zhang_cq
 * @version V1.0
 */
@Controller
public class ExportData {
  /**
   * 起始页面
   * 
   * @return
   */
  @RequestMapping("/index")
  public String login() {
    return "index";
  }
  /**
   * 导出Excel
   * 
   * @author zhang_cq
   */
  @RequestMapping("/export")
  public String export(HttpServletRequest request, HttpServletResponse response) throws Exception {
    // 设置导出的编码格式，此处统一为UTF-8
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    // 设置导出文件的名称
    response.setHeader("Content-Disposition",
        "attachment;filename=" + new String("数据导出Excel测试.xls".getBytes(), "iso-8859-1"));
    // 模拟表格需要导出的数据
    Person p1 = new Person("张三", "22", "北京", "男");
    Person p2 = new Person("李四", "23", "济南", "女");
    Person p3 = new Person("王五", "24", "上海", "男");
    List personList = new ArrayList();
    personList.add(p1);
    personList.add(p2);
    personList.add(p3);
    // 实际应用中这个地方会判断获取的数据，如果没有对应的数据则不导出，如果超过2000条，则只导出2000条
    if (personList.size() == 0) {
      PrintWriter print = response.getWriter();
      print.write("没有需要导出的数据！");
      return null;
    }
    ServletOutputStream out = response.getOutputStream();
    BufferedInputStream bis = null;
    BufferedOutputStream bos = null;
    try {
      ExportUtil dataExportUtil = new ExportUtil();
      bis = new BufferedInputStream(dataExportUtil.getExcelStream(personList));
      bos = new BufferedOutputStream(out);
      byte[] buff = new byte[2048];
      int bytesRead;
      while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
        bos.write(buff, 0, bytesRead);
      }
      bos.flush();
    } catch (final IOException e) {
      System.out.println("数据导出列表导出异常！");
    } finally {
      if (bis != null) {
        bis.close();
      }
      if (bos != null) {
        bos.close();
      }
    }
    return "export";
  }
}