org.jeecgframework.poi.excel.entity.TemplateExportParams Java Examples

/**
 * 往Sheet 填充正常数据
 * 
 * @param params
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private static void addDataToSheet(TemplateExportParams params,
		Class<?> pojoClass, Collection<?> dataSet, Sheet sheet,
		Workbook workbook) throws Exception {
	Drawing patriarch = sheet.createDrawingPatriarch();
	List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
	// 得到所有字段
	Field fileds[] = ExcelPublicUtil.getClassFields(pojoClass);
	ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
	String targetId = null;
	if (etarget != null) {
		targetId = etarget.id();
	}
	getAllExcelField(targetId, fileds, excelParams, pojoClass, null);
	sortAllParams(excelParams);
	Iterator<?> its = dataSet.iterator();
	int index = sheet.getLastRowNum();
	while (its.hasNext()) {
		Object t = its.next();
		index += createCells(patriarch, index, t, excelParams, sheet,
				workbook);
	}
}
 

/**
 * 克隆excel防止操作原对象,workbook无法克隆,只能对excel进行克隆
 * @param params 
 * @throws Exception 
 *@Author JueYue
 *@date   2013-11-11
 */
private static Workbook getCloneWorkBook(TemplateExportParams params) throws Exception {
	Workbook wb = null;
	String path = Thread.currentThread().getContextClassLoader().getResource("").toURI().getPath();
	path = path.replace("WEB-INF/classes/","");
	path = path.replace("file:/","")+params.getTemplateUrl();
	FileInputStream fileis  = new FileInputStream(path);
	ByteArrayOutputStream baos = new ByteArrayOutputStream();
    byte[] buffer = new byte[1024];
    int len;
    while ((len = fileis.read(buffer)) > -1 ) {
        baos.write(buffer, 0, len);
    }
    baos.flush();
    InputStream is = new ByteArrayInputStream(baos.toByteArray()); 
    wb = WorkbookFactory.create(is);
    //关闭用到的流
	baos.close();
    fileis.close();
    is.close();
	return wb;
	
}
 

@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
	String codedFileName = "临时文件";
	Workbook workbook = ExcelExportUtil.exportExcel((TemplateExportParams) model.get(TemplateExcelConstants.PARAMS), (Class<?>) model.get(TemplateExcelConstants.CLASS), (List<?>) model.get(TemplateExcelConstants.LIST_DATA), (Map<String, Object>) model.get(TemplateExcelConstants.MAP_DATA));
	if (model.containsKey(NormalExcelConstants.FILE_NAME)) {
		codedFileName = (String) model.get(NormalExcelConstants.FILE_NAME);
	}
	if (workbook instanceof HSSFWorkbook) {
		codedFileName += HSSF;
	} else {
		codedFileName += XSSF;
	}
	if (isIE(request)) {
		codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8");
	} else {
		codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1");
	}
	response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
	ServletOutputStream out = response.getOutputStream();
	workbook.write(out);
	out.flush();
}
 

@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
	String codedFileName = "临时文件";
	Workbook workbook = ExcelExportUtil.exportExcel((TemplateExportParams) model.get(TemplateExcelConstants.PARAMS), (Class<?>) model.get(TemplateExcelConstants.CLASS), (List<?>) model.get(TemplateExcelConstants.LIST_DATA), (Map<String, Object>) model.get(TemplateExcelConstants.MAP_DATA));
	if (model.containsKey(NormalExcelConstants.FILE_NAME)) {
		codedFileName = (String) model.get(NormalExcelConstants.FILE_NAME);
	}
	if (workbook instanceof HSSFWorkbook) {
		codedFileName += HSSF;
	} else {
		codedFileName += XSSF;
	}
	if (isIE(request)) {
		codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8");
	} else {
		codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1");
	}
	response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
	ServletOutputStream out = response.getOutputStream();
	workbook.write(out);
	out.flush();
}
 

public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {
	// step 1. 判断模板的地址
	if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
		throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
	}
	Workbook wb = null;
	// step 2. 判断模板的Excel类型,解析模板
	try {
		this.teplateParams = params;
		wb = getCloneWorkBook();
		// 创建表格样式
		setExcelExportStyler((IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
		// step 3. 解析模板
		for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets() : params.getSheetNum().length; i < le; i++) {
			if (params.getSheetName() != null && params.getSheetName().length > i && StringUtils.isNotEmpty(params.getSheetName()[i])) {
				wb.setSheetName(i, params.getSheetName()[i]);
			}
			tempCreateCellSet.clear();
			parseTemplate(wb.getSheetAt(i), map);
		}
		if (dataSet != null) {
			// step 4. 正常的数据填充
			dataHanlder = params.getDataHanlder();
			if (dataHanlder != null) {
				needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields());
			}
			addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
		}
	} catch (Exception e) {
		LOGGER.error(e.getMessage(), e);
		return null;
	}
	return wb;
}
 

public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {
	// step 1. 判断模板的地址
	if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
		throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
	}
	Workbook wb = null;
	// step 2. 判断模板的Excel类型,解析模板
	try {
		this.teplateParams = params;
		wb = getCloneWorkBook();
		// 创建表格样式
		setExcelExportStyler((IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
		// step 3. 解析模板
		for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets() : params.getSheetNum().length; i < le; i++) {
			if (params.getSheetName() != null && params.getSheetName().length > i && StringUtils.isNotEmpty(params.getSheetName()[i])) {
				wb.setSheetName(i, params.getSheetName()[i]);
			}
			tempCreateCellSet.clear();
			parseTemplate(wb.getSheetAt(i), map);
		}
		if (dataSet != null) {
			// step 4. 正常的数据填充
			dataHanlder = params.getDataHanlder();
			if (dataHanlder != null) {
				needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields());
			}
			addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
		}
	} catch (Exception e) {
		LOGGER.error(e.getMessage(), e);
		return null;
	}
	return wb;
}
 

private static Workbook createSheetInUserModel2FileByTemplate(
		TemplateExportParams params, Class<?> pojoClass,
		Collection<?> dataSet, Map<String, Object> map) {
	// step 1. 判断模板的地址
	if (StringUtils.isEmpty(params.getTemplateUrl())) {
		return null;
	}
	Workbook wb = null;
	// step 2. 判断模板的Excel类型,解析模板
	try {
		wb = getCloneWorkBook(params);
		// step 3. 删除其他的sheet
		for (int i = wb.getNumberOfSheets()-1; i >= 0; i--) {
			if (i != params.getSheetNum()) {
				wb.removeSheetAt(i);
			}
		}
		if(StringUtils.isNotEmpty(params.getSheetName())){
			wb.setSheetName(0, params.getSheetName());
		}
		// step 4. 解析模板
		parseTemplate(wb.getSheetAt(0), map);
		if(dataSet != null){
			// step 5. 正常的数据填充
			addDataToSheet(params, pojoClass, dataSet, wb.getSheetAt(0), wb);
		}
	} catch (Exception e) {
		e.printStackTrace();
		return null;
	}
	return wb;
}
 

public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass,
                                      Collection<?> dataSet, Map<String, Object> map) {
    // step 1. 判断模板的地址
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }
    Workbook wb = null;
    // step 2. 判断模板的Excel类型,解析模板
    try {
        this.teplateParams = params;
        wb = getCloneWorkBook();
        // 创建表格样式
        setExcelExportStyler((IExcelExportStyler) teplateParams.getStyle()
            .getConstructor(Workbook.class).newInstance(wb));
        // step 3. 解析模板
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets() : params
            .getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map);
        }
        if (dataSet != null) {
            // step 4. 正常的数据填充
            dataHanlder = params.getDataHanlder();
            if (dataHanlder != null) {
                needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields());
            }
            addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return null;
    }
    return wb;
}
 

@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
                                       HttpServletResponse response) throws Exception {
    String codedFileName = "临时文件";
    Workbook workbook = ExcelExportUtil.exportExcel(
        (TemplateExportParams) model.get(TemplateExcelConstants.PARAMS),
        (Class<?>) model.get(TemplateExcelConstants.CLASS),
        (List<?>) model.get(TemplateExcelConstants.LIST_DATA),
        (Map<String, Object>) model.get(TemplateExcelConstants.MAP_DATA));
    if (model.containsKey(NormalExcelConstants.FILE_NAME)) {
        codedFileName = (String) model.get(NormalExcelConstants.FILE_NAME);
    }
    if (workbook instanceof HSSFWorkbook) {
        codedFileName += HSSF;
    } else {
        codedFileName += XSSF;
    }
    if (isIE(request)) {
        codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8");
    } else {
        codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1");
    }
    response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
    ServletOutputStream out = response.getOutputStream();
    workbook.write(out);
    out.flush();
}
 

/**
 * 导出文件通过模板解析只有模板,没有集合
 * 
 * @param params
 *            导出参数类
 * @param map
 *            模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {
    return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, null, null, map);
}
 

/**
 * 导出文件通过模板解析只有模板,没有集合
 * 
  * @param entity  导出参数类
 * @param pojoClass 对应实体
 * @param map 模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params,
		Map<String, Object> map) {
	return ExcelExportOfTemplateUtil.exportExcel(params, null,
			null, map);
}
 

/**
 * 导出文件通过模板解析
 * 
 * @param entity  导出参数类
 * @param pojoClass 对应实体
 * @param dataSet 实体集合
 * @param map 模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params,
		Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {
	return ExcelExportOfTemplateUtil.exportExcel(params, pojoClass,
			dataSet, map);
}
 

/**
 * 导出文件通过模板解析只有模板,没有集合
 * 
  * @param entity  导出参数类
 * @param pojoClass 对应实体
 * @param map 模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params,
		Map<String, Object> map) {
	return createSheetInUserModel2FileByTemplate(params, null,
			null, map);
}
 

/**
 * 导出文件通过模板解析
 * 
 * @param entity  导出参数类
 * @param pojoClass 对应实体
 * @param dataSet 实体集合
 * @param map 模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params,
		Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {
	return createSheetInUserModel2FileByTemplate(params, pojoClass,
			dataSet, map);
}
 

/**
 * 导出文件通过模板解析,不推荐这个了,推荐全部通过模板来执行处理
 * 
 * @param params
 *            导出参数类
 * @param pojoClass
 *            对应实体
 * @param dataSet
 *            实体集合
 * @param map
 *            模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass,
                                   Collection<?> dataSet, Map<String, Object> map) {
    return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, pojoClass, dataSet,
        map);
}
 

/**
 * 导出文件通过模板解析只有模板,没有集合
 * 
 * @param params
 *            导出参数类
 * @param map
 *            模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {
	return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, null, null, map);
}
 

/**
 * 导出文件通过模板解析,不推荐这个了,推荐全部通过模板来执行处理
 * 
 * @param params
 *            导出参数类
 * @param pojoClass
 *            对应实体
 * @param dataSet
 *            实体集合
 * @param map
 *            模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {
	return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, pojoClass, dataSet, map);
}
 

/**
 * 导出文件通过模板解析只有模板,没有集合
 * 
 * @param params
 *            导出参数类
 * @param map
 *            模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {
	return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, null, null, map);
}
 

/**
 * 导出文件通过模板解析,不推荐这个了,推荐全部通过模板来执行处理
 * 
 * @param params
 *            导出参数类
 * @param pojoClass
 *            对应实体
 * @param dataSet
 *            实体集合
 * @param map
 * 
 *            模板集合
 * @return
 */
public static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {
	return new ExcelExportOfTemplateUtil().createExcleByTemplate(params, pojoClass, dataSet, map);
}