excel导出工具类--java开发

淡淡的烟草味﹌ 2022-06-01 08:37 257阅读 0赞

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","分类名称",
          "付费订单数","付费订单总金额","平台分成","新增关注人数","累计订单数",
          "累计订单收入","总平台分成","关注人数","当天是否开课","新建话题数",
          "新建系列课数"};