jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.7</version>
</dependency>
控制层
@ApiOperation(value = "导出商户列表")
@ApiImplicitParams({
@ApiImplicitParam(name = "authorization", value = "加密后的摘要,每次请求后由服务端生成,通过Header返回", required = true, dataType = "String", paramType = "header")
})
@RequestMapping(value = "downloadExcel", method = RequestMethod.POST)
@ResponseBody
public Response<String> downloadExcel() {
try {
List<JSONObject> jsonObjects = Collections.synchronizedList(new ArrayList<>());
List<JdMerchantRecord> list = jdMerchantRecordService.queryList(new JdMerchantRecord());
if (DataUtil.isNotEmpty(list)){
list.parallelStream().forEach(JdAccountTrade ->{
JSONObject excel = getTradeDetail(JdAccountTrade);
if(DataUtil.isNotEmpty(excel)){
jsonObjects.add(excel);
}
});
}
String key = UUID.randomUUID().toString().replaceAll("-","");
RedisUtil.set(key,jsonObjects,1000*60*30L);
String url=jdMerchantExcel+"/"+key;
return Response.ok(url);
}catch (Exception e){
log.error("导出失败{}",e);
}
return Response.fail("导出失败");
}
private JSONObject getTradeDetail(JdMerchantRecord record) {
JSONObject object = new JSONObject();
object.put("companyType",getConpanyType(record.getCompanyType()));
object.put("contactName",record.getContactName());
object.put("regPhone",record.getRegPhone());
object.put("regEmail",record.getRegEmail());
object.put("contactAddress",record.getContactAddress());
object.put("blicCompanyName",record.getBlicCompanyName());
object.put("abMerchantAddress",record.getAbMerchantAddress());
object.put("blicUscc",record.getBlicUscc());
object.put("blicScope",record.getBlicScope());
object.put("blicAddress",record.getBlicAddress());
object.put("blicLongTerm",getLepLongTerm(record.getBlicLongTerm()));
object.put("lepName",record.getLepName());
object.put("lepCardNo",record.getLepCardNo());
object.put("lepValidityStart",record.getLepValidityStart());
object.put("lepValidityEnd",record.getLepValidityEnd());
object.put("bankAccountNo",record.getBankAccountNo());
object.put("bankName",record.getBankName());
object.put("subBankCode",record.getSubBankCode());
object.put("settleCardPhone",record.getSettleCardPhone());
object.put("blicUrla",visitUrl+record.getBlicUrla());//营业执照扫描件
object.put("occUrla",visitUrl+record.getOccUrla());//开户许可证照片
object.put("lepUrla",visitUrl+record.getLepUrla());//法人身份证正面照
object.put("lepUrlb",visitUrl+record.getLepUrlb());//法人身份证国徽照
return object;
}
工具类控制层
@GetMapping(value = "/downloadjdMerchantExcel/{key}")
@ResponseBody
public void downloadjdMerchantExcel(@PathVariable(value = "key") String key, HttpServletRequest request, HttpServletResponse response) throws Exception {
Object object = RedisUtil.get(key);
List<JSONObject> objectList = object == null ? null : (List<JSONObject>) object;
if (objectList == null){
log.error("key已经过期:{}",key);
return;
}
ExportExcelUtil<JSONObject> excelUtil=new ExportExcelUtil<>();
exportJdMerchantRecordTwo(JdMerchantRecordExcel.excelTitle,JdMerchantRecordExcel.excelTitleKey,objectList,request,response);
}
public void exportJdMerchantRecordTwo(String[] headers, String[] columns, List<JSONObject> lists, HttpServletRequest request, HttpServletResponse response) throws Exception {
// 声明一个工作薄
XSSFWorkbook wb = new XSSFWorkbook();
//声明一个sheet并命名
XSSFSheet sheet = wb.createSheet("商户记录");
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
//给sheet名称一个长度
sheet.setDefaultColumnWidth(18);
//创建第一行(也可以称为表头)
XSSFRow row = sheet.createRow(0);
ByteArrayOutputStream byteArrayOut = null; //读进图片
HttpURLConnection conn =null;
//给表头第一行一次创建单元格(对应字段创建对应单元格)
for (int i = 0; i < headers.length; i++) {
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFCell headerCell = row.createCell(i);
headerCell.setCellStyle(style);
headerCell.setCellValue(headers[i]);
}
int rowIndex = 0;
XSSFCell cell=null;
for(JSONObject obj:lists){
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(style.getAlignmentEnum().CENTER);//水平居中
style.setWrapText(true);//自动换行
rowIndex++;
row = sheet.createRow(rowIndex);
row.setHeight((short)(150*15));//设置行高
for (int i = 0; i < columns.length;i++){
cell=row.createCell(i);
cell.setCellStyle(style);
if(i == (columns.length-1) || i == (columns.length-2) || i == (columns.length-3) || i == (columns.length-4)){
byteArrayOut = new ByteArrayOutputStream();
String pictureUrl = obj.getString(columns[i]);
pictureUrl = pictureUrl.replaceAll(" ","");
if ( !(pictureUrl.startsWith("http")) && !(pictureUrl.startsWit