controller:mezura-sa UserAccountController
/**
* 导出会员账户列表
* @param userAccountVo
* @return
*/
@RequestMapping(BranchUrl.ACCOUNT_EXPORT)
public PageData ExportUserAccountList(UserAccountVo userAccountVo){
try {
int count = userAccountService.getUserAccountListNum(userAccountVo);
if(count == 0){
return PageData.getInstanceOfError(ConstTipCode.EXPORT_NO_DATA,"无数据,不允许导出");
}
//文件名
String fileName = "userAccountInfo" + DateUtils.formatDate(new Date());
String filePath = userAccountService.exportUserAccountList(isBranchcom,count, fileName,userAccountVo);
pageData.getResultMap().put("filePath", filePath);
pageData.getResultMap().put("fileName", fileName);
return pageData;
} catch (Exception e) {
return processException(e);
}
}
service:
/**
* 会员账户导出
*
* @param isBranchcom
* @param fileName
* @param userAccountVo
* @throws Exception
*/
public String exportUserAccountList(Boolean isBranchcom, int count, String fileName, UserAccountVo userAccountVo) throws Exception {
String templateRootPath = sysConfig.getFilePath();
String filePath = null;
String path = templateRootPath + File.separator + Const.TEMPLATE_EXCEL_USERACCOUNT;
if (isBranchcom) {//是公司级别,取没有国家字段的模板
path = FileUtils.AddSuffix(path, Const.TEMPLATE_EXCEL_SUFFIX, Const.TEMPLATE_EXCEL_BRANCHCOM);
}else {
//模版名称+国家编码做为唯一标识
String countryCode =sysCountryMapper.selectByPrimaryKey(userAccountVo.getCountryId()).getCountryCode();
String[] a =Const.TEMPLATE_EXCEL_USERACCOUNT.split("\\.");
String excelName=a[0];
path =templateRootPath+File.separator+excelName+countryCode+Const.TEMPLATE_EXCEL_SUFFIX;
}
File excelFile = new File(path);
if (!excelFile.exists()) {
throw new BusinessException(ConstTipCode.EXPORT_FAIL, path + ",模板文件未找到");
}
HSSFWorkbook workbook = null;
InputStream inputStream = null;
try {
inputStream = new FileInputStream(excelFile);
workbook = new HSSFWorkbook(inputStream);
} catch (IOException e) {
throw new BusinessException(ConstTipCode.EXPORT_FAIL, path + ",模板文件读取异常");
} finally {
if (inputStream != null) {
inputStream.close();
}
}
HSSFSheet sourceSheet = workbook.getSheetAt(0);
int pageNum = 1;
if (count % Const.SHEET_NUM == 0) {
pageNum = count / Const.SHEET_NUM;
} else {
pageNum = (count / Const.SHEET_NUM) + 1;
}
for (int page = 1; page <= pageNum; page++) {
if (workbook.getSheet("sheet" + page) == null) {
workbook.createSheet("sheet" + page);
}
HSSFSheet hssfSheet = workbook.getSheetAt(page - 1);//目标sheet
if (page > 1) {
PoiUtils.copyRows(sourceSheet, hssfSheet, 1, 1, 1);
}
int positionIndex = 2;
int rowSize = 0;
List<UserAccountVo> exportUserAccountList = getExportUserAccountList(userAccountVo, page);
for (UserAccountVo userAccount : exportUserAccountList) {
int rowIndex = (positionIndex) + rowSize;
PoiUtils.copyRows(sourceSheet, hssfSheet, 1, 1, rowIndex);
HSSFRow hssfRow = PoiUtils.getHSSFRow(hssfSheet, rowIndex);
PoiUtils.getHSSFCell(hssfRow, 1).setCellValue(rowSize + 1);
if (!isBranchcom) {
PoiUtils.getHSSFCell(hssfRow, 2).setCellValue(userAccount.getCountryName());
}
// PoiUtils.getHSSFCell(hssfRow,2).setCellValue(userAccount.getUserId());
PoiUtils.getHSSFCell(hssfRow, 3).setCellValue(userAccount.getUserName());
PoiUtils.getHSSFCell(hssfRow, 4).setCellValue(userAccount.getEcTotal());
PoiUtils.getHSSFCell(hssfRow, 5).setCellValue(userAccount.getEcUsable());
PoiUtils.getHSSFCell(hssfRow, 6).setCellValue(userAccount.getEcFrozen());
PoiUtils.getHSSFCell(hssfRow, 7).setCellValue(userAccount.getCreditTotal());
PoiUtils.getHSSFCell(hssfRow, 8).setCellValue(userAccount.getCreditUsable());
PoiUtils.getHSSFCell(hssfRow, 9).setCellValue(userAccount.getCreditFrozen());
rowSize = rowSize + 1;
}
}
//输出Excel文件
String exportPath = sysConfig.getExportFilePath();
String currentDateStr = new SimpleDateFormat("yyyyMMdd").format(new Date());
filePath = exportPath + currentDateStr + "/" + fileName + ".xls";
File file = new File(exportPath + currentDateStr);
if (!file.exists() &a