Java实现导出Excel
阅读原文时间:2023年07月11日阅读:1

项目中实现的代码:

一.
//controller
@RequestMapping(value = "/exportAccountManager")
public void exportAccountManager(HttpServletRequest request, HttpServletResponse response,
@RequestParam String fileName, String orgNo, String consNo, String consName, Integer consState) {
String templateFileName = request.getSession().getServletContext().getRealPath("/") + "resources/templates/"
+ fileName;
try (InputStream input = new BufferedInputStream(new FileInputStream(templateFileName));
OutputStream output = response.getOutputStream();) {
Map params = buildParam(orgNo, consNo, consName, consState);
List dataList = accountManagerService.queryAccountManagerList(params);
CommonUtils.exportExcel(response, fileName, "客户清单.xls", input, output, dataList);
} catch (Throwable ex) {
LOGGER.error("导出客户清单失败:", ex);
}
}

//参数
private Map buildParam(String orgNo, String consNo, String consName, Integer consState) {
Map params = new HashMap();
CommonUtils.addOrgParam(orgNo, commonService, params);
params.put("consNo", consNo);
params.put("consName", consName);
params.put("consState", consState);
return params;
}
二.工具类中导出相关
public static void exportExcel(HttpServletResponse response, String fileName, String cnFileName, InputStream input,
OutputStream output, List dataList) throws IOException {
cnFileName = URLEncoder.encode(cnFileName, StandardCharsets.UTF_8.name());
response.setHeader("Content-Disposition", "attachment;filename=" + cnFileName);
response.setContentType("application/vnd.ms-excel");
Map params = new HashMap();
params.put("dataList", dataList);
exportExcel(input, output, params);
output.flush();
}

public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> params) throws IOException {  
    Context context = PoiTransformer.createInitialContext();  
    if (MapUtils.isNotEmpty(params)) {  
        Iterator<Map.Entry<String, Object>> iterator = params.entrySet().iterator();  
        while (iterator.hasNext()) {  
            Map.Entry<String, Object> entry = iterator.next();  
            context.putVar(entry.getKey(), entry.getValue());  
        }  
    }  
    JxlsHelper jxlsHelper = JxlsHelper.getInstance();  
    Transformer transformer = jxlsHelper.createTransformer(is, os);  
    JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig()  
            .getExpressionEvaluator();  
    evaluator.getJexlEngine().setSilent(true); // 设置静默模式,不报警告

    // 函数强制,自定义功能  
    Map<String, Object> funcs = new HashMap<String, Object>();  
    funcs.put("utils", new JxlsUtils());  
    evaluator.getJexlEngine().setFunctions(funcs);

    // 必须要这个,否者表格函数统计会错乱  
    jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);  
}

三.service
@Override
public List queryAccountManagerList(Map params) throws Exception {
return accountManagerMapper.queryAccountManagerList(params);
}

//service接口
public List queryAccountManagerList(Map params) throws Exception;
四.mapper
//mapper接口
public List queryAccountManagerList(Map params) throws Exception;
////mapper映射xml

五.前端Html

六.js

function exportXls() {
var params = getSearchTableParams();
var url = getUrl("accountManager/exportAccountManager?fileName=cons.xls", params, true);
window.location.href = basePath + url;
}

/**
* 获取查询表格参数
* @returns Array
*/
function getSearchTableParams() {
var params = [];
$(".search_table input,.search_table select").each(function () {
var _this = $(this);
params.push({
name: _this.attr('name'),
value: _this.val()
});
});
return params;
}

/**
* 获取URL
*
* @param url URL
* @param params URL参数
* @param urlContainParam URL是否包含参数,默认不含参数
* @returns
*/
function getUrl(url, params, urlContainParam) {
if (params == null || params.length == 0)
return url;
if (urlContainParam == null)
urlContainParam = false;
var arr = [url];
$(params).each(function (i, e) {
if (i == 0 && urlContainParam == false) {
arr.push("?" + e.name + "=" + e.value);
} else {
arr.push("&" + e.name + "=" + e.value);
}
});
return arr.join('');
}

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器