导出Excel
/**
* 导出Excel
* @param page
* @param rows
* @param sort
* @param sortType
* @param search
* @param condition
* @param request
* @return
*/
@SuppressWarnings("rawtypes")
@RequestMapping(value = "/exprotExcel.json", method = RequestMethod.GET)
public ReturnVo exprotExcel(
@RequestParam(value = "page", defaultValue = "") Integer page,
@RequestParam(value = "rows", defaultValue = "") Integer rows,
@RequestParam(value = "sort", defaultValue = "") String sort,
@RequestParam(value = "order", defaultValue = "") String sortType,
@RequestParam(value = "search", defaultValue = "") String search,
@RequestParam(value = "condition", defaultValue = "") String condition,
@RequestParam(value = "userNameAlk", defaultValue = "") String userNameAlk,
@RequestParam(value = "userNikenameAlk", defaultValue = "") String userNikenameAlk,
@RequestParam(value = "orderStatusAlk", defaultValue = "") String orderStatusAlk,
@RequestParam(value = "cardIdAlk", defaultValue = "") String cardIdAlk,
@RequestParam(value = "bankCodeAlk", defaultValue = "") String bankCodeAlk,
@RequestParam(value = "cardHolderAlk", defaultValue = "") String cardHolderAlk,
HttpServletRequest request) {
//创建webbook
HSSFWorkbook wb = new HSSFWorkbook();
//在webbook中添加一个sheet
HSSFSheet sheet = wb.createSheet("提现报表");
//在sheet中添加表头第0行
HSSFRow row = sheet.createRow((int) 0);
//创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("会员昵称");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("提现金额");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("银行编码");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("卡号");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("持卡人姓名");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("状态");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue("拒绝原因");
cell.setCellStyle(style);
cell = row.createCell((short) 7);
cell.setCellValue("完成时间");
cell.setCellStyle(style);
//查询数据
List<Map<String, Object>> list = withdrawDepositBusiness.queryAll(null, 1, 999999999,search, condition,userNameAlk,userNikenameAlk,orderStatusAlk,cardIdAlk,bankCodeAlk,cardHolderAlk);
for (int i = 0; i < list.size(); i++)
{
row = sheet.createRow((int) i + 1);
Map<String, Object> map = list.get(i);
System.out.print("amount"+map.get("amount"));
//创建单元格,并设置值
row.createCell((short) 0).setCellValue((String) map.get("userNikename"));
row.createCell((short) 1).setCellValue((double) Double.valueOf(map.get("amount").toString()));
row.createCell((short) 2).setCellValue((String) map.get("bankCode"));
row.createCell((short) 3).setCellValue((String) map.get("cardId"));
row.createCell((short) 4).setCellValue((String) map.get("cardHolder"));
WithdrawStatus orderStatus = Enum.valueOf(WithdrawStatus.class, map.get("orderStatus").toString());
String status = orderStatus.message();
row.createCell((short) 5).setCellValue((String) status);
row.createCell((short) 6).setCellValue((String) map.get("refuseReason"));
cell = row.createCell((short) 7);
cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd HH:mm:ss").format(map.get("finishedTime")));
}
//将文件存到指定位置
FileOutputStream fileOut = null;
try
{
fileOut = new FileOutputStream("E:/withdraw_"+DateUtils.getyyyyMMddHHmmssSSS()+".xls");
wb.write(fileOut);
fileOut.close();
}
catch (Exception e)
{
e.printStackTrace();
}
finally{
if(fileOut != null){
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/* try {
// path是指欲下载的文件的路径。
File file = new File(path);
// 取得文件名。
String filename = file.getName();
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename="
+ new String(filename.getBytes()));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(
response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
} */
return new ReturnVo<>();
}