http://blog.csdn.net/caisini_vc/article/details/52387842
excel导出基本上是必备的功能,如果条目超过65535 是csv,否则xls。
一句话使用:
- OrderInfoExcelBuilder.getInstance().createExcel(OrderType.MAIN_ORDER, orderInfoDetailList, response);
结构比较简单,抽象类+子类实现。
先看下抽象类:
- <pre name="code" class="java">package com.qunar.flight.ib2b.trade.center.util.excel;
- import java.io.OutputStream;
- import java.net.URLEncoder;
- import java.util.List;
- import javax.mail.internet.MimeUtility;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.commons.lang3.StringUtils;
- import org.apache.poi.ss.usermodel.Row;
- import org.apache.poi.ss.usermodel.Sheet;
- import org.apache.poi.xssf.usermodel.XSSFCellStyle;
- import org.apache.poi.xssf.usermodel.XSSFDataFormat;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import com.google.common.base.Throwables;
- import com.qunar.flight.ib2b.trade.center.enums.OrderType;
- /**
- * Excel文件导出 定义类
- *
- * @author xiongxo.chen
- */
- public abstract class AbstractExcelBuilder<T> {
- private static final Logger logger = LoggerFactory.getLogger(AbstractExcelBuilder.class);
- public void createExcel(final OrderType type, List<T> datas, HttpServletResponse response) {
- if (0 == datas.size()) {
- return;
- }
- XSSFWorkbook workBook = new XSSFWorkbook();
- OutputStream os;
- try {
- Sheet sheet = createSheet(workBook);
- if (sheet == null)
- return;
- // 写表头
- writeHead(type, sheet, datas);
- // 写内容
- XSSFCellStyle cellStyle = workBook.createCellStyle();
- XSSFDataFormat format = workBook.createDataFormat();
- writeBody(type, cellStyle, format, sheet, datas);
- response.setContentType("application/vnd.ms-excel");
- response.setHeader("Content-disposition", "attachment;filename=OrderDetail.xls");
- os = response.getOutputStream();
- workBook.write(response.getOutputStream());
- os.flush();
- } catch (Throwable t) {
- logger.error("导出 excel出错!", t);
- Throwables.propagate(t);
- }
- }
- /**
- * 写表头
- */
- protected void writeHead(OrderType type, Sheet sheet, List<T> datas) {
- String[] head = getHead(type, datas.get(0));
- Row row = sheet.createRow(0);
- for (int i = 0; i < head.length; i++) {
- row.createCell(i).setCellValue(head[i]);
- sheet.setColumnWidth(i, head[i].getBytes().length * 256);
- }
- }
- /**
- * 写表体
- */
- protected abstract void writeBody(OrderType type, XSSFCellStyle cellStyle, XSSFDataFormat format, Sheet sheet, List<T> datas)
- throws Exception;
- /**
- * 表头内容
- */
- protected abstract String[] getHead(OrderType type, T datas);
- /**
- * Sheet页名称
- */
- protected String getSheetName() {
- return "xo";
- }
- /**
- * 来个Sheet
- */
- private XSSFSheet createSheet(XSSFWorkbook workBook) {
- if (StringUtils.isBlank(getSheetName())) {
- return workBook.createSheet();
- } else {
- return workBook.createSheet(getSheetName());
- }
- }
- /**
- * 设置下载文件中文件的名称
- */
- public static String encodeFilename(String filename) {
- /**
- * 获取客户端浏览器和操作系统信息 在IE浏览器中得到的是:User-Agent=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Maxthon; Alexa
- * Toolbar) 在Firefox中得到的是:User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.7.10) Gecko/20050717
- * Firefox/1.0.6
- */
- try {
- // 先检查IE浏览器的
- String newFileName = URLEncoder.encode(filename, "UTF-8");
- newFileName = StringUtils.replace(newFileName, "+", "%20");
- if (newFileName.length() > 150) {
- newFileName = new String(filename.getBytes("GB2312"), "ISO8859-1");
- newFileName = StringUtils.replace(newFileName, " ", "%20");
- }
- if (StringUtils.isBlank(newFileName)) {
- // 若不是IE,则检查Firxbox
- return MimeUtility.encodeText(filename, "UTF-8", "B");
- }
- return filename;
- } catch (Exception ex) {
- return filename;
- }
- }
- }
子类实现: 将订单信息传入写内容到row叠加就好
- package com.qunar.flight.ib2b.trade.center.util.excel;
- import java.util.List;
- import javax.annotation.Resource;
- import org.apache.poi.ss.usermodel.Cell;
- import org.apache.poi.ss.usermodel.Row;
- import org.apache.poi.ss.usermodel.Sheet;
- import org.apache.poi.xssf.usermodel.XSSFCellStyle;
- import org.apache.poi.xssf.usermodel.XSSFDataFormat;
- import com.qunar.flight.ib2b.trade.center.bean.OrderInfoDetail;
- import com.qunar.flight.ib2b.trade.center.enums.OrderType;
- import com.qunar.flight.ib2b.trade.center.persistence.dao.TOrderStatusChangeLogMapper;
- /**
- * 订单信息导出构建器
- *
- * @author xiongxo.chen
- *
- */
- public class OrderInfoExcelBuilder extends AbstractExcelBuilder<OrderInfoDetail> {
- private static OrderInfoExcelBuilder builder;
- @Resource
- TOrderStatusChangeLogMapper tOrderStatusChangeLogMapper;
- private OrderInfoExcelBuilder() {
- }
- /**
- * 单例
- */
- public synchronized static OrderInfoExcelBuilder getInstance() {
- if (null == builder)
- builder = new OrderInfoExcelBuilder();
- return builder;
- }
- /**
- * 写Excel内容
- */
- @Override
- protected void writeBody(OrderType type, XSSFCellStyle cellStyle, XSSFDataFormat format, Sheet sheet,
- List<OrderInfoDetail> datas) throws Exception {
- int columnIndex = 0;
- int rowIndex = 1;
- for (OrderInfoDetail order : datas) {
- columnIndex = 0;
- Row row = sheet.createRow(rowIndex);
- Cell idCell = row.createCell(columnIndex);
- // 文本格式
- cellStyle.setDataFormat(format.getFormat("@"));
- idCell.setCellStyle(cellStyle);
- idCell.setCellValue(order.getId());
- row.createCell(columnIndex++).setCellValue("写内容1");
- row.createCell(columnIndex++).setCellValue("写内容2");
- rowIndex++;
- }
- }
- /**
- * 写Excel表头
- */
- @Override
- protected String[] getHead(OrderType type, OrderInfoDetail order) {
- return new String[] { "订单创建日期", "订单号"};
- }
- }