|
?1.用postman测试Controller接口，
@RequestMapping(value = "/exportExcelBatchProcessFile",method = {RequestMethod.GET, RequestMethod.POST})
@ApiOperation(value = "导出人员预发批次进度查询名单、审核流程记录到处人员名单")
public Result exportExcelBatchFile(AdvanceBatchProcessDetailsQueryDto dto) {
try {
auditsCommissionerAdvanceDomainService.exportAdvanceBatchInfo(dto);
return Result.success("导出人员预发批次名单文件成功！");
} catch (Exception e) {
e.printStackTrace();
return Result.error("导出人员预发批次名单文件失败!");
}
}
?
2.调用AuditsCommissionerAdvanceDomainService接口中的方法如下：
/**
* 预发批次进度列表-导出人员名单
*/
void exportAdvanceBatchInfo(AdvanceBatchProcessDetailsQueryDto dto);
3.AuditsCommissionerAdvanceDomainServiceImpl实现类调用导出方法如下：，入参可以不用管，header定义导出excel表头第一行内容，固定写法，创建ExportExcelUtil<T> 对象，调用该工具类写好的exportBankExcel(List<T> list , String filePath , String fileName)方法.
list代表内容，filePath是导出本地的一个路径，fileName是导出文件起一个名字。
@Override
public void exportAdvanceBatchInfo(AdvanceBatchProcessDetailsQueryDto dto) {
String[] header = {"学号", "姓名", "留学国家", "银行名称", "出国时间", "币种", "月资助标准", "艰苦地区补贴",
"发放月数", "实发金额", "CSC资助期（月）","资助类型", "留学身份", "项目名称", "项目代码", "渠道名称"};
ExportExcelUtil<AdvanceBatchProcessDetailsListDto> exportExcelUtil = new ExportExcelUtil<>(header);
List<AdvanceBatchProcessDetailsListDto> list = auditsCommissionerAdvanceRepository.getYfCountryCommissionerQueryListDtoInfo(dto);
String path = "D:\\test\\advance\\test01\\";
String name = "haha.xlsx";
exportExcelUtil.exportBankExcel(list,path,name);
}
/**
* 这个是ExportExcelutil工具类中的导出方法！！！
* @param list 入参
* @param fileName 文件名称
*/
public void exportBankExcel(List<T> list, String filePath, String fileName){
FileOutputStream fileOutputStream = null;
SXSSFWorkbook workbook = null;
try {
//1. 创建workbook对象
workbook = new SXSSFWorkbook();
//2. 根据workbook创建sheet
SXSSFSheet sheet = workbook.createSheet("学生信息");
//3. 根据sheet创建row
SXSSFRow row = sheet.createRow(0);
// 设置标题
for (int i = 0; i < header.length; i++) {
row.createCell(i).setCellValue(header[i]);
}
for (int i = 0; i < list.size(); i++) {
//4. 根据row创建cell
T dto = list.get(i);
Class clazz = dto.getClass();
Field[] fields = clazz.getDeclaredFields();
int rowLength = i + 1;
int kk = 0;
SXSSFRow row2 = sheet.createRow(rowLength);
for (Field field : fields) {
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Object o = clazz.getMethod(getMethodName).invoke(dto);
String val = String.valueOf(o) ;
row2.createCell(kk).setCellValue(isEmpty(val));
kk++;
if(kk == header.length){
break;
}
}
}
File file = new File(filePath);
if (!file.exists()){
file.mkdirs();
}
//6. 通过输出流写到文件里
fileOutputStream = new FileOutputStream(filePath + "/" + fileName);
workbook.write(fileOutputStream);
} catch (IOException | InvocationTargetException | IllegalAccessException | NoSuchMethodException e) {
e.printStackTrace();
} finally {
try {
if (fileOutputStream != null){
fileOutputStream.close();
}
if (workbook != null){
workbook.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
4.AuditsCommissionerAdvanceRepository接口中调用getYfCountryCommissionerQueryListDtoInfo(dto)该方法
auditsCommissionerAdvanceRepository.getYfCountryCommissionerQueryListDtoInfo(dto)；查询出符合导出的所有信息，导出的模板dto如下：
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@ApiModel(value = "预发批次精度查询详情/预发批次进度查询-批次名称/稽核专员- 复核预发批次详情 -回显")
public class AdvanceBatchProcessDetailsListDto {
/** SM_CARD_INFORMATION
* 奖学金信息表主键ID
*/
@ApiModelProperty(value = "奖学金信息表主键ID")
private String id;
// @ApiModelProperty(value = "留学信息表中的预发批次id")
// private String advanceBatchId;
/**SM_CARD_INFORMATION
* 学号
*/
@ApiModelProperty(value = "学号")
private String studentNum;
/**
* 姓名
*/
@ApiModelProperty(value = "姓名")
private String studentName;
//---
/**
* 留学国家
*/
@ApiModelProperty(value = "留学国家")
private String abroadCountryName;
/**
* 银行名称 --
*/
@ApiModelProperty(value = "银行名称")
private String depositBank;
/**
* 出国日期 --
*/
@ApiModelProperty(value = "出国日期")
private String dateDeparture;
/**
* 币种
*/
@ApiModelProperty(value = "币种")
private String scholarshipType;
/** 根据INFORMATION_ID查询
* 月资助标准 --SM_SUPPORT_INFORMATION-奖学金资助信息表--------------
*/
@ApiModelProperty(value = "月资助标准")
private String monthlySubsidyStandard;
/**
* 艰苦地区补贴
*/
@ApiModelProperty(value = "艰苦地区补贴")
private String hardshipSubsidy;
/**根据INFORMATION_ID查询
* 发放月数 --SM_SCHOLARSHIP_PAYMENT_RECORD-奖学金发放记录表--------------
*
*/
@ApiModelProperty(value = "发放月数")
private String distributingMonths;
/**根据INFORMATION_ID查询
* 实发金额 --SM_SCHOLARSHIP_PAYMENT-奖学金发放计算公式--------------
*/
@ApiModelProperty(value = "实发金额")
private String actualAmount;
/**
* CSC资助期（月）
*/
@ApiModelProperty(value = "CSC资助期（月）")
private String fundingMouth;
/**
* 资助类型
*
*/
@ApiModelProperty(value = "资助类型")
private String fundingType;
/**
* 留学身份
*/
@ApiModelProperty(value = "留学身份")
private String studyAbroadStatus;
/**
* 项目名称
*/
@ApiModelProperty(value = "项目名称")
private String projectName;
/**
* 项目代码
*/
@ApiModelProperty(value = "项目代码")
private String projectCode;
/**
* 渠道名称
*
*/
@ApiModelProperty(value = "渠道名称")
private String channelName;
}
?5.AuditsCommissionerAdvanceRepository接口中方法
//根据批次iD查询该批次下的所有信息
List<AdvanceBatchProcessDetailsListDto> getYfCountryCommissionerQueryListDtoInfo(AdvanceBatchProcessDetailsQueryDto dto);
6.AuditsCommissionerAdvanceRepositoryImpl实现类中条用mapper查询数据库信息
@Override
public List<AdvanceBatchProcessDetailsListDto> getYfCountryCommissionerQueryListDtoInfo(AdvanceBatchProcessDetailsQueryDto dto) {
return informationMapper.getAdvanceBatchProcessDetailsListDto02(dto);
}
7.informationMapper接口调用如下方法得到需要导出的信息：
public List<AdvanceBatchProcessDetailsListDto> getAdvanceBatchProcessDetailsListDto02(@Param("dto") AdvanceBatchProcessDetailsQueryDto dto);
@Select("<script>" +
"select " +
" sci.id, " +
" sci.STUDENT_NUM, " +
" sci.STUDENT_NAME, " +
" sci.ABROAD_COUNTRY_NAME, " +
" sci.DEPOSIT_BANK, " +
" sci.DATE_DEPARTURE, " +
" sci.SCHOLARSHIP_TYPE, " +
" ssi.MONTHLY_SUBSIDY_STANDARD , " +
" sci.HARDSHIP_SUBSIDY , " +
" sspr.DISTRIBUTING_MONTHS, " +
" ssp.ACTUAL_AMOUNT , " +
" sci.FUNDING_MOUTH , " +
" sci.FUNDING_TYPE , " +
" sci.STUDY_ABROAD_STATUS, " +
" sci.PROJECT_NAME , " +
" sci.PROJECT_CODE , " +
" sci.CHANNEL_NAME " +
"FROM " +
" SM_CARD_INFORMATION sci " +
" left join SM_SUPPORT_INFORMATION ssi on ssi.INFORMATION_ID = sci.id " +
" left join SM_SCHOLARSHIP_PAYMENT_RECORD sspr on sspr.INFORMATION_ID = sci.id " +
" left join SM_SCHOLARSHIP_PAYMENT ssp on ssp.INFORMATION_ID = sci.id " +
" left join SM_ADVANCE_BATCH_INFORMATION sabi on sabi.INFORMATION_ID = sci.id " +
" left join SM_ADVANCE_BATCH sab on sab.id = sabi.ADVANCE_BATCH_ID " +
"<where>" +
" sci.DELETE_FLAG = '0' " +
" and sci.INVALID_FLAG = '0' " +
" <if test = \"dto.studentNum != null and dto.studentNum != ''\">" +
" and sci.STUDENT_NUM like #{dto.studentNum}" +
" </if> " +
" <if test = \"dto.studentName !=null and dto.studentName !='' \"> " +
" and sci.STUDENT_NAME like #{dto.studentName}" +
" </if>" +
" <if test = \"dto.advanceBatchId !=null and dto.advanceBatchId !='' \"> " +
" and sab.ID = #{dto.advanceBatchId}" +
" </if>" +
"" +
"</where> " +
"group by " +
" sci.id, " +
" sci.STUDENT_NUM, " +
" sci.STUDENT_NAME, " +
" sci.ABROAD_COUNTRY_NAME, " +
" sci.DEPOSIT_BANK, " +
" sci.DATE_DEPARTURE, " +
" sci.SCHOLARSHIP_TYPE, " +
" ssi.MONTHLY_SUBSIDY_STANDARD , " +
" sci.HARDSHIP_SUBSIDY , " +
" sspr.DISTRIBUTING_MONTHS, " +
" ssp.ACTUAL_AMOUNT , " +
" sci.FUNDING_MOUTH , " +
" sci.FUNDING_TYPE , " +
" sci.STUDY_ABROAD_STATUS, " +
" sci.PROJECT_NAME , " +
" sci.PROJECT_CODE , " +
" sci.CHANNEL_NAME " +
" " +
"" +
"</script>")
public List<AdvanceBatchProcessDetailsListDto> getAdvanceBatchProcessDetailsListDto02(@Param("dto") AdvanceBatchProcessDetailsQueryDto dto);
|