excel导出工具类--java开发

package com.qlchat.admin.common.util;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.qlchat.common.util.ConfigUtil;
import com.qlchat.common.util.DateUtil;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class FileUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(FileUtil.class);
    
    private static FileUtil instance = new FileUtil();
    
    public static FileUtil getInstance(){
        return instance;
    }

    /**
     * 设置文件下载相应头信息
     *
     * @param response 响应对象
     * @param fileName 文件名称
     * @throws UnsupportedEncodingException
     */
    private void setDownLoadHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setContentType("application/octet-stream");
        String downLoadName = new String(fileName.getBytes("gbk"), "iso8859-1"); 
        response.setHeader("Content-Disposition",
                String.format("attachment;filename=%s", downLoadName));
    }

    /**
     * 
     * 导出Excel文件
     *
     * @param response 文件导出请求的相应对象
     * @param data  要导出的文件数据
     * @param titles 导出的数据标题 必须和keys同时存在，且顺序保持一致，否则会出现数据错乱
     * @param keys  导出数据的keys，必须喝title同时存，且顺序保持一致，否则会出现数据错乱
     * @param fileName  要导出的Excel文件名称默认：yyyy年MM月dd日 HH时mm分ss秒.xls
     * @param sheetName 数据所在的Excel工作表名称默认：sheet1
     */
    public void exportExcel(HttpServletResponse response, List<Map<String, Object>> data, String[] titles,
            String[] keys, String fileName, String sheetName) {
        if (response != null && data != null && !data.isEmpty()) {
            if (StringUtils.isBlank(fileName)) {
                fileName = DateUtil.DateToString(new Date());
            } else {
                fileName = fileName.trim();
            }
            if (fileName.indexOf(ConfigUtil.EXCEL_2003) == -1) {
                fileName = fileName.concat(ConfigUtil.EXCEL_2003);
            }
            try {
                //设置文件下载头信息
                this.setDownLoadHeader(response, fileName);
            } catch (UnsupportedEncodingException e) {
                LOGGER.error(String.format("设置Excel文件[%s]导出时出现异常：%s", new Object[] { fileName, e.getMessage() }));
            }
            if (titles == null || (titles!=null && titles.length <= 0)) {
             Set<String> titleKeys = data.get(0).keySet();
             if(!titleKeys.isEmpty()){
                titles = titleKeys.toArray(new String[titleKeys.size()]);
             }
          }
          //设置文件数据的keys
          if (keys == null || (keys!=null && keys.length <= 0)) {
             Set<String> keyKeys = data.get(0).keySet();
             if(!keyKeys.isEmpty()){
                keys = keyKeys.toArray(new String[keyKeys.size()]);
             }
          }
            if (StringUtils.isBlank(sheetName)) {
                sheetName = "sheet1";
            }
            try {
                WritableWorkbook excelFile = this.createExcel(response.getOutputStream());
                WritableSheet sheet = excelFile.createSheet(sheetName, 0);
                this.addExcelSheetRows(sheet, data, titles, keys);
                excelFile.write();
                excelFile.close();
            } catch (IOException e) {
                LOGGER.error("创建Excel文件[".concat(fileName).concat("]导出出现异常：").concat(e.getMessage()));
            LOGGER.error(e.getMessage(), e);
            } catch (RowsExceededException e) {
                LOGGER.error("添加Excel文件[".concat(fileName).concat("]数据出现异常：").concat(e.getMessage()));
            LOGGER.error(e.getMessage(), e);
            } catch (WriteException e) {
                LOGGER.error("写入Excel文件[".concat(fileName).concat("]出现异常：").concat(e.getMessage()));
            LOGGER.error(e.getMessage(), e);
            }
        }
    }
    
    
    
    public void exportExcel(HttpServletResponse response, List<Map<String, Object>> data, String[] titles,
            String[] keys, String fileName,Integer maxNumber,String ... sheetName) {
        if (response != null && data != null && !data.isEmpty()) {
            if (StringUtils.isBlank(fileName)) {
                fileName = DateUtil.DateToString(new Date());
            } else {
                fileName = fileName.trim();
            }
            if (fileName.indexOf(ConfigUtil.EXCEL_2003) == -1) {
                fileName = fileName.concat(ConfigUtil.EXCEL_2003);
            }
            try {
                //设置文件下载头信息
                this.setDownLoadHeader(response, fileName);
            } catch (UnsupportedEncodingException e) {
                LOGGER.error(String.format("设置Excel文件[%s]导出时出现异常：%s", new Object[] { fileName, e.getMessage() }));
            }
            if (titles == null || (titles!=null && titles.length <= 0)) {
             Set<String> titleKeys = data.get(0).keySet();
             if(!titleKeys.isEmpty()){
                titles = titleKeys.toArray(new String[titleKeys.size()]);
             }
          }
          //设置文件数据的keys
          if (keys == null || (keys!=null && keys.length <= 0)) {
             Set<String> keyKeys = data.get(0).keySet();
             if(!keyKeys.isEmpty()){
                keys = keyKeys.toArray(new String[keyKeys.size()]);
             }
          }
          if(sheetName == null || sheetName.length == 0){
             sheetName = new String[]{"sheet1"};
          }
            try {
                WritableWorkbook excelFile = this.createExcel(response.getOutputStream());
                int beginIndex = 0;
                int endIndex = maxNumber-1;
                if(sheetName.length == 1){
                   beginIndex =  0;
                   endIndex = data.size()-1;
                }
                for (int i = 0; i < sheetName.length; i++) {
                   //默认设置为一个worksheet 最大的导出行数为maxNumber行
                   if(i > 0 && i != sheetName.length-1){
                      beginIndex = beginIndex +maxNumber;
                      endIndex = endIndex + maxNumber;
                   }else if(i > 0 && i == sheetName.length-1){
                      beginIndex = beginIndex +maxNumber;
                      endIndex = data.size()-1;
                   }
                   WritableSheet sheet = excelFile.createSheet(sheetName[i], 0);
                   this.addExcelSheetRows(sheet, data.subList(beginIndex, endIndex+1), titles, keys);
               
            }
                excelFile.write();
                excelFile.close();
            } catch (IOException e) {
                LOGGER.error("创建Excel文件[".concat(fileName).concat("]导出出现异常：").concat(e.getMessage()));
            LOGGER.error(e.getMessage(), e);
            } catch (RowsExceededException e) {
                LOGGER.error("添加Excel文件[".concat(fileName).concat("]数据出现异常：").concat(e.getMessage()));
            LOGGER.error(e.getMessage(), e);
            } catch (WriteException e) {
                LOGGER.error("写入Excel文件[".concat(fileName).concat("]出现异常：").concat(e.getMessage()));
            LOGGER.error(e.getMessage(), e);
            }
        }
    }

    /**
     * 
     * 为工作表添加数据
     *
     * @param sheet
     * @param data
     * @param titles
     * @param keys
     * @throws RowsExceededException
     * @throws WriteException
     */
    private void addExcelSheetRows(WritableSheet sheet, List<Map<String, Object>> data, String[] titles, String[] keys)
            throws RowsExceededException, WriteException {
        if (sheet != null && !data.isEmpty() && isNotEmpty(titles) && isNotEmpty(keys)) {
            int rowIndex = 1;
            int columnIndex = 0;
            for (String title : titles) {
                sheet.addCell(new Label(columnIndex++, 0, title));
            }
            String value = null;
            for (Map<String, Object> row : data) {
                columnIndex = 0;
                if (row != null && !row.isEmpty()) {
                    for (String key : keys) {
                        value = isNotEmpty(row.get(key) + "") ? row.get(key).toString() : "";
                        sheet.addCell(new Label(columnIndex++, rowIndex, value));
                    }
                    rowIndex++;
                }
            }
        }
    }

    /**
     * 根据OutputStream创建要道出Excel文件
     * 〈功能详细描述〉
     *
     * @param os
     * @return
     * @throws IOException
     */
    private WritableWorkbook createExcel(OutputStream os) throws IOException {
        WritableWorkbook workBook = null;
        if (os != null) {
            workBook = Workbook.createWorkbook(os); // 建立excel文件
        }
        return workBook;
    }

    private static boolean isNotEmpty(String resource) {
        return StringUtils.isNotBlank(resource) && !"null".equals(resource);
    }

    private static boolean isNotEmpty(String[] resource) {
        return resource != null && resource.length > 0;
    }

}

使用方式

FileUtil.getInstance().exportExcel(response, listOrders, exportTitles, exportProps, "直播间日常监控信息.xls", "直播间日常监控信息");

private String[] exportProps = new String[]{"date","liveId","liveName","liveLevel","tagId","tagName"
      ,"payOrderNum","payOrderMoney","platformIncome","newFocusNum","totalOrderNum"
      ,"totalOrderIncome","totalPlatformIncome","totalFocusNum","isInClass","newTopic"
      ,"newChannel" };

private String[] exportTitles = new String[]{"日期","直播间id","直播间名称","直播间类型","分类id","分类名称",
      "付费订单数","付费订单总金额","平台分成","新增关注人数","累计订单数",
      "累计订单收入","总平台分成","关注人数","当天是否开课","新建话题数",
      "新建系列课数"};