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