SpringCloud把xml报文导出Excel（csv格式）文

作者: 程序大视界 | 来源:发表于2018-12-15 20:55 被阅读0次

SpringCloud把xml报文导出Excel（csv格式）文
在linux中将excel表格数据导入Mysql数据库（含中文数
python导出excel，csv
zabbix导出的CSV文件乱码
刘硕的Scrapy笔记(九,exporter导出器)
小程序云开发数据库导出svc数据乱码
python pandas 入门(1)-- 数据导入，清洗，导出
利用tablib、make_response 进行文件的下载
HSSFWorkbook(poi)导出excel表格
mongoldb数据导入导出

导出excel报表之类，相信有过1~2年开发经验的至少都做过了。但是大多应该都是传统的SSH或SSM架构，相对于在最近流行的SpringCloud分布式架构上做类似导出，可能经历不是那么多。

鄙人做过的导出excel报表，有2种方案：

Poi原生的，jar类库

<groupId>org.apache.poi</groupId>

</dependency>

HSSFWorkbook、HSSFSheet、HSSFCellStyle等一系列，poi包中类库。优势：可以根据业务需要最大限度的格式化excel报表（合并单元格/居中/字体颜色等）；此种方案见另一篇资料：

poi格式导出excel

poi方法参考另一篇博文：poi方法导出excel文档

apache系列的commons-csv类库：CSVPrinter

<groupId>org.apache.commons</groupId>

<artifactId>commons-csv</artifactId>

</dependency>

由于本人项目中，之前统一了导出方案由：CSVPrinter格式，也妥协于api-zuul网关的输入输出流格式的限制，最终选用此种。优势：可与查询接口合并，查询数据供前端页面调用，导出直接输出excel文档。

今天跟大家分享一下，在SpringCloud架构下，导出excel报表的经验。同时又错误或遗漏之处欢迎指正建议，感激。

xml报文内容格式：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<Root>

</SubItemList>

</BatchInf>

</BatchList>

</SttlInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</SubItemList>

</BatchInf>

</BatchList>

</SttlInf>

</SttlList>

</Root>

可以看到嵌套了好几层：SttlList—>SttlInf—>BatchList—>BatchInf—>SubItemList—>SubItemInf

其中，要先把xml报文解析成obj对象，根据xml报文拆分后封装的obj对象，对于xml报文解析可查看另一篇：

Dom4j解析xml报文

核心类库是：

1、org.apache.commons.csv 类库中的CSVPrinter进行打印输出。

2、借助java.io的输入输出流等

HttpServletRequest

HttpServletResponse

OutputStream/OutputStreamWriter

3、org.apache.commons.io关闭CSVPrinter打印输出流。

上代码：接口Controller控制入口类:

@RequestMapping(value = "/export")

@Logable(businessTag = "accountInfoExport")

@Exceptionable

@Validatable

@ApiOperation(value = "对账文件导出", notes = "对账文件导出", httpMethod = "GET")

@ApiImplicitParams({

@ApiImplicitParam(name = "accountDate", value = "对账日期（yyyy-MM-dd）", required = true, dataType = "String", length = 100, paramType = "query"),

@ApiImplicitParam(name = "download", value = "是否下载:true/false", required = true, dataType = "boolean", length = 100, paramType = "query"),

@ApiImplicitParam(name = "fileName", value = "例:AccountInfo", required = true, dataType = "String", length = 100, paramType = "query"),

@ApiImplicitParam(name = "type", value = "格式:csv", required = true, dataType = "String", length = 100, paramType = "query")})

public PageResult<SettInfoExportDto> exportAccountInfo(@RequestParam String accountDate,

@RequestParam(value = "download", required = true) boolean download,

@RequestParam(value = "fileName", required = true) String fileName,

@RequestParam(value = "type", required = true) String type,

HttpServletRequest request, HttpServletResponse response) {

PageResult<SettInfoExportDto> pageResult = new PageResult<SettInfoExportDto>();

AccountInfoEntityResp accountInfoEntityResp = accountInfoService.getLocalAccountInf(accountDate);

//1.结算场次列表

List<String> settListTitles = new ArrayList<>();

settListTitles.add("报文标识号");

settListTitles.add("场次借贷标识");

settListTitles.add("场次金额");

//2.批次列表

List<String> batchInfListTitles = new ArrayList<>();

batchInfListTitles.add("报文标识号");

batchInfListTitles.add("批次号");

batchInfListTitles.add("批次借贷标识");

batchInfListTitles.add("批次金额");

//3.分项列表

List<String> sttlInfListTitles = new ArrayList<>();

sttlInfListTitles.add("批次号");

sttlInfListTitles.add("业务类型");

sttlInfListTitles.add("银行金额机构标识");

sttlInfListTitles.add("账户类型");

sttlInfListTitles.add("分项借方发生额");

sttlInfListTitles.add("分项借方发生笔数");

sttlInfListTitles.add("分项贷方发生额");

sttlInfListTitles.add("分项贷方发生笔数");

//4.账务日期...

Map<String, String> statisticsData = new HashMap<>();

String settCount = accountInfoEntityResp.getSttlCntNb().toString();

String debitAmount = accountInfoEntityResp.getDebitCntAmt().toString();

String creditAmount = accountInfoEntityResp.getCreditCntAmt().toString();

statisticsData.put("财务日期:", accountDate.replace("//-","///"));

statisticsData.put("结算总笔数", settCount);

statisticsData.put("借方金额", debitAmount);

statisticsData.put("贷方金额", creditAmount);

//5.结算场次列表数据list

List<SettInfoExportDto> settInfoExportDtoList = new ArrayList<SettInfoExportDto>();

//6.批次列表数据list

List<BatchInfExportDto> batchInfExportDtoList = new ArrayList<BatchInfExportDto>();

//7.分项列表数据list

List<SubItemInfoExportDto> subItemInfoExportDtoList = new ArrayList<SubItemInfoExportDto>();

if(accountInfoEntityResp.getSttlList() != null){

//结算场次列表数据

for(int i=0; i<accountInfoEntityResp.getSttlList().size(); i++){

SettInfoExportDto settInfoExportDto = new SettInfoExportDto();

settInfoExportDto.setSttlAmt(accountInfoEntityResp.getSttlList().get(i).getSttlAmt());

settInfoExportDto.setSttlDCFlg(accountInfoServiceImpl.DCFlag(accountInfoEntityResp.getSttlList().get(i).getSttlDCFlg()));

settInfoExportDto.setSttlReptFlg(accountInfoEntityResp.getSttlList().get(i).getSttlReptFlg());

settInfoExportDtoList.add(settInfoExportDto);

//批次列表数据

if(accountInfoEntityResp.getSttlList().get(i).getBatchList() != null){

for(int j= 0; j<accountInfoEntityResp.getSttlList().get(i).getBatchList().size(); j++) {

BatchInfExportDto batchInfExportDto = new BatchInfExportDto();

batchInfExportDto.setSttlReptFlg(accountInfoEntityResp.getSttlList().get(i).getSttlReptFlg());

batchInfExportDto.setBatchDCFlg(accountInfoEntityResp.getSttlList().get(i).getBatchList().get(j).getBatchDCFlg());

batchInfExportDto.setBatchId(accountInfoEntityResp.getSttlList().get(i).getBatchList().get(j).getBatchId());

batchInfExportDto.setBatchNetAmt(accountInfoEntityResp.getSttlList().get(i).getBatchList().get(j).getBatchNetAmt());

batchInfExportDtoList.add(batchInfExportDto);

//分项列表数据

if(accountInfoEntityResp.getSttlList().get(i).getBatchList().get(j).getSubItemList() != null){

for(int f=0; f< accountInfoEntityResp.getSttlList().get(i).getBatchList().get(j).getSubItemList().size(); f++){

SubItemInfoExportDto subItemInfoExportDto = new SubItemInfoExportDto();

subItemInfoExportDto.setBatchId(accountInfoEntityResp.getSttlList().get(i).getBatchList().get(j).getBatchId());

String subStr = accountInfoEntityResp.getSttlList().get(i).getBatchList().get(j).getSubItemList().get(f).getSubItemInf();

subStr = subStr.replace("CNY","");

String[] subArray = {};

subArray = subStr.split("\\|");

subItemInfoExportDto.setBusinessType(accountInfoServiceImpl.busiType(subArray[0]));

subItemInfoExportDto.setBankOrgFlag(subArray[1]);

subItemInfoExportDto.setAccountType(accountInfoServiceImpl.accountType(subArray[2]));

subItemInfoExportDto.setDebitSplitAmount(subArray[3]);

subItemInfoExportDto.setDebitSplitCount(Integer.valueOf(subArray[4]));

subItemInfoExportDto.setCreditSplitAmount(subArray[5]);

subItemInfoExportDto.setCreditSplitCount(Integer.valueOf(subArray[6]));

subItemInfoExportDtoList.add(subItemInfoExportDto);

}

settListTitles.addAll(batchInfListTitles);

settListTitles.addAll(sttlInfListTitles);

CSVPrinter csvPrinter = accountInfoService.downloadCsv(request, response, settListTitles);

pageResult.setTitles(settListTitles);

pageResult.setResult(Constants.detailReturnCode.RETURN_SUCCESS.code);

pageResult.setRows(settInfoExportDtoList);

accountInfoService.printlnCsv(csvPrinter,statisticsData,settInfoExportDtoList,batchInfExportDtoList,

subItemInfoExportDtoList,settListTitles,batchInfListTitles,sttlInfListTitles);

IOUtils.closeQuietly(csvPrinter);

return null;

}

输入输出流，封装CSVPrinter对象：

public CSVPrinter downloadCsv(HttpServletRequest request, HttpServletResponse response, List<String> titles) {

try {

// 获取数据

String fileName = request.getParameter("fileName");

// 设置文件名, 用于下载

response.setContentType("application/csv;charset=UTF-8");

response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "utf-8"));

// 添加UTF-8的BOM头，避免Excel打开文件时使用系统默认编码产生乱码

byte[] byteBOM = new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF };

OutputStream outputStream = response.getOutputStream();

outputStream.write(byteBOM);

OutputStreamWriter outputStreamWriter = new OutputStreamWriter(outputStream, "utf-8");

CSVPrinter printer = CSVFormat.DEFAULT.withHeader(titles.toArray(new String[titles.size()])).print(outputStreamWriter);

return printer;

} catch (Exception e) {

e.printStackTrace();

return null;

}

csv导出记录：

public void printlnCsv(CSVPrinter printer, Map<String, String> statisticsData,List<SettInfoExportDto> settInfoExportDtoList,

List<BatchInfExportDto> batchInfExportDtoList,List<SubItemInfoExportDto> subItemInfoExportDtoList,

List<String> settListTitles,List<String> batchInfListTitles,List<String> sttlInfListTitles){

if(subItemInfoExportDtoList.size() > 0){

try{

//报文标识号

String sttlReptFlg = null;

//结算列表数据

for(SettInfoExportDto row:settInfoExportDtoList){

Field[] declaredFields = row.getClass().getDeclaredFields();

for (Field declaredField : declaredFields) {

declaredField.setAccessible(true);

FieldAnnotation annotation = declaredField.getAnnotation(FieldAnnotation.class);

if (annotation == null) {

// 只有注解了 FieldAnnotation 的字段，才会输出

continue;

}

Object value = declaredField.get(row);

if (StringUtils.equals(annotation.fieldName(), "报文标识号")) {

sttlReptFlg = (String) value;

}

printer.print(value + "\t");

}

// 换行

printer.println();

//打印批次列表数据

printBacthRecord(printer,sttlReptFlg, batchInfExportDtoList, subItemInfoExportDtoList, settListTitles

,batchInfListTitles, sttlInfListTitles);

}

// 换行

printer.println();

//手续费行

if (statisticsData != null && !statisticsData.isEmpty()) {

Iterator<String> iterator = statisticsData.keySet().iterator();

while (iterator.hasNext()) {

String key = (String) iterator.next();

printer.print(key);

printer.print(statisticsData.get(key));

// 换行

printer.println();

}

byte[] nulls = new byte[] { (byte) 00, (byte) 00, (byte) 00 };

printer.print(new String(nulls));

}

printer.flush();

}catch (Exception e) {

e.printStackTrace();

}

/**

* 打印批次列表数据

*/

public void printBacthRecord(CSVPrinter printer,String sttlReptFlg,List<BatchInfExportDto> batchInfExportDtoList,

List<SubItemInfoExportDto> subItemInfoExportDtoList,List<String> settListTitles ,

List<String> batchInfListTitles,List<String> sttlInfListTitles) throws IOException {

int i = settListTitles.size() - batchInfListTitles.size() - sttlInfListTitles.size();

//取出该报文标识号的批次列表

List<BatchInfExportDto> printBatchInfoList = new ArrayList<BatchInfExportDto>();

if(batchInfExportDtoList != null && batchInfExportDtoList.size()>0){

for(BatchInfExportDto record:batchInfExportDtoList){

if(record.getSttlReptFlg().equals(sttlReptFlg)){

printBatchInfoList.add(record);

}

if (printBatchInfoList != null && !printBatchInfoList.isEmpty()) {

String batchId = null;

for (BatchInfExportDto bacthInfo : printBatchInfoList) {

byte[] nulls = new byte[i];

for (byte b : nulls) {

printer.print(new String(nulls));

}

printer.print(sttlReptFlg);

printer.print(bacthInfo.getBatchId());

printer.print(bacthInfo.getBatchDCFlg());

printer.print(bacthInfo.getBatchNetAmt());

batchId = bacthInfo.getBatchId();

// 换行

printer.println();

//打印分项列表数据

printSubItemRecord(printer,batchId,subItemInfoExportDtoList,settListTitles,sttlInfListTitles);

}

/**

* 打印分项列表数据

*/

public void printSubItemRecord(CSVPrinter printer,String batchId,List<SubItemInfoExportDto> subItemInfoExportDtoList,

List<String> settListTitles ,List<String> sttlInfListTitles ) throws IOException {

int i = settListTitles.size() - sttlInfListTitles.size();

//取出该批次号的分项列表

List<SubItemInfoExportDto> subItemPrintRecordList = new ArrayList<SubItemInfoExportDto>();

if(subItemInfoExportDtoList != null && subItemInfoExportDtoList.size()>0){

for(SubItemInfoExportDto record : subItemInfoExportDtoList){

if(record.getBatchId().equals(batchId)){

subItemPrintRecordList.add(record);

}

if (subItemPrintRecordList != null && !subItemPrintRecordList.isEmpty()) {

for (SubItemInfoExportDto subItemInfo : subItemPrintRecordList) {

byte[] nulls = new byte[i];

for (byte b : nulls) {

printer.print(new String(nulls));

}

printer.print(batchId);

printer.print(subItemInfo.getBusinessType());

printer.print(subItemInfo.getBankOrgFlag());

printer.print(subItemInfo.getAccountType());

printer.print(subItemInfo.getDebitSplitAmount());

printer.print(subItemInfo.getDebitSplitCount());

printer.print(subItemInfo.getCreditSplitAmount());

printer.print(subItemInfo.getDebitSplitCount());

// 换行

printer.println();

}

主要核心代码以上。

最终导出效果如下：

关注个人技术公众号：nick_coding1024

不定期分享最新前沿技术框架和bat大厂常用技术等，加群不定期分享行业内大牛直播讲课以及获得内退一线互联网公司机会。

---------------------CSDN技术博客

原文：https://blog.csdn.net/xuri24/article/details/83110773

网友评论

Java技术博客专栏

本文标题：SpringCloud把xml报文导出Excel（csv格式）文

本文链接：https://www.haomeiwen.com/subject/zrczhqtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

SpringCloud把xml报文导出Excel（csv格式）文

相关文章