POI实现导出Excel报表小例

来源：互联网发布：sqlserver自增长id 编辑：程序博客网时间：2024/06/09 17:27

package com.iori.webapp.servlet;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
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.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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;
import org.apache.poi.hssf.util.Region;

public final class ExportExcelServlet extends HttpServlet {

private static final long serialVersionUID = -2495088366920758628L;
private static final Log log = LogFactory.getLog(ExportExcelServlet.class);

// --------------------------------------------------------- Public Methods

/**
* Validates the Init and Context parameters, configures authentication URL
*
* @throws ServletException
*             if the init parameters are invalid or any other problems
*             occur during initialisation
*/
public void init() throws ServletException {

}

/**
* Route the user to the execute method
*
* @param request
*            The HTTP request we are processing
* @param response
*            The HTTP response we are creating
*
* @exception IOException
*                if an input/output error occurs
* @exception ServletException
*                if a servlet exception occurs
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
   throws IOException, ServletException {
  execute(request, response);
}

/**
* Route the user to the execute method
*
* @param request
*            The HTTP request we are processing
* @param response
*            The HTTP response we are creating
*
* @exception IOException
*                if an input/output error occurs
* @exception ServletException
*                if a servlet exception occurs
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
   throws IOException, ServletException {
  execute(request, response);
}

/**
* 账务对账生成报表
* @author iori
*/
public void execute(HttpServletRequest request, HttpServletResponse response)
   throws IOException, ServletException {
   //开始时间
   String startDate = request.getParameter("startDate");
   //结束时间
   String endDate = request.getParameter("endDate");
   //报表类型
   String expType = request.getParameter("expType");

   response.setContentType("application/ms-excel");
   response.addHeader("Content-Disposition", "attachment;filename=test.xls");
   //输出流
   BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());
   //list中放的是数据记录
   List list = new ArrayList();
   list.add("12312");
   list.add("大林");
   if ("1".equals(expType)) {
    String title = this.getTitle(startDate, endDate) + "充值报表";
    String[] subTitle = {"订单号","姓名","账号","交易时间","交易状态","交易金额（元）","金额"};
    this.exportExcel(title, subTitle, list, out);
   } else {
    String title = this.getTitle(startDate, endDate) + "消费报表";
    String[] subTitle = {"姓名","账号","交易时间","交易金额","备注"};
    this.exportExcel(title, subTitle, list, out);
   }

}

/**
* 取报表标题
* @author iori
* @param startDate
* @param endDate
* @return
*/
private String getTitle(String startDate, String endDate) {
  if (startDate != null && !"".equals(startDate) && endDate != null && !"".equals(endDate)) {
   //都不为空
   return startDate + "至" + endDate;
  } else if (startDate != null && !"".equals(startDate) && (endDate == null || "".equals(endDate))) {
   //开始时间不为空，结束时间为空
   return startDate + "至今";
  } else if ((startDate == null || "".equals(startDate)) && endDate != null && !"".equals(endDate)) {
   //开始时间为空，结束时间不为空
   return endDate + "之前";
  } else {
   //都为空
   return "所有";
  }
}

/**
* 导出Excel文件
* @author iori
* @param date
* @param list
* @param out
*/
private void exportExcel(String title, String[] subTitle, List list, BufferedOutputStream out) {
  //新建一个文档
  HSSFWorkbook workBook = new HSSFWorkbook();
  //新建一个表
  HSSFSheet sheet = workBook.createSheet(title);
  //新建一行(标题)
  HSSFRow row = sheet.createRow(0);
  //设置行高
  row.setHeightInPoints(30);
  //新建一个单元格
  HSSFCell cell = row.createCell((short)0);
  HSSFRichTextString text = new HSSFRichTextString(title);
  //单元格写入内容
  cell.setCellValue(text);
  //标题样式
  HSSFCellStyle style = this.getTitleStyle(workBook);
  cell.setCellStyle(style);
  //标题，合并单元格
  sheet.addMergedRegion(new Region(0,(short)0,0,(short)(subTitle.length - 1)));
  //新建一行（列名）
  row = sheet.createRow(1);
  //设置行高
  row.setHeightInPoints(20);
  //子标题样式
  HSSFCellStyle style2 = this.getSubTitleStyle(workBook);
  for (int i = 0; i < subTitle.length; i++) {
   cell = row.createCell((short)i);
   text = new HSSFRichTextString(subTitle[i]);
   cell.setCellValue(text);
   cell.setCellStyle(style2);
   //设置单元格的宽度
   sheet.setColumnWidth((short) i, (short) 6000);
  }
  //list中放的是数据记录
  Iterator it = list.iterator();
  int index = 1;
  while (it.hasNext()) {
   index++;
   row = sheet.createRow(index);
   cell = row.createCell((short)0);
   text = new HSSFRichTextString((String)it.next());
   cell.setCellValue(text);
  }
  try {
   //写入，这样一次性写入buffer流，好像缓冲起不到什么做用
   workBook.write(out);
   out.flush();
  } catch (IOException e) {
   e.printStackTrace();
  } finally {
   try {
    if (out != null) {
     out.close();
    }
   } catch (IOException e) {
    e.printStackTrace();
   }
  }
}

/**
* 标题样式
* @author iori
* @param workBook
* @return
*/
private HSSFCellStyle getTitleStyle(HSSFWorkbook workBook) {
  //新建一个字体
  HSSFFont font = workBook.createFont();
  //字体大小
  font.setFontHeightInPoints((short)18);
  //字体名称
  font.setFontName("宋体");
  //新建一个样式
  HSSFCellStyle style = workBook.createCellStyle();
  //设置背景色，两行代码要一起出现
  style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
  style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  //垂直居中
  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  //水平居中
  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  style.setFont(font);
  return style;
}

/**
* 子标题样式
* @author iori
* @param workBook
* @return
*/
private HSSFCellStyle getSubTitleStyle(HSSFWorkbook workBook) {
  //新建一个字体
  HSSFFont font2 = workBook.createFont();
  //字体大小
  font2.setFontHeightInPoints((short)14);
  //字体名称
  font2.setFontName("宋体");
  //新建一个样式
  HSSFCellStyle style2 = workBook.createCellStyle();
  //设置背景色，两行代码要一起出现
  style2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
  style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  //垂直居中
  style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  //水平居中
  style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  style2.setFont(font2);
  return style2;
}
}

需要的JAR包（不知道怎么上传文件）：

poi-3.1-beta2-20080526.jar
poi-contrib-3.1-beta2-20080526.jar
poi-scratchpad-3.1-beta2-20080526.jar