接上回做了客户池的一些工作
今天任务
(1)excel数据的导入导出
(2)讨论团建、游戏等活动
数据导入
/**
* 导入客户池信息
*/
@ApiResponses({
@ApiResponse(code = 200, message = "ok"),
})
@ResponseBody
@ApiOperation(value = "POST", notes = "导入客户池信息")
@RequestMapping(value = "/importCustomerPool", produces = MediaType.APPLICATION_JSON_VALUE, method = RequestMethod.POST)
public Response importCustomerPool(MultipartFile multipartFile) throws Exception {
Workbook workbook = null;
String fileName = multipartFile.getOriginalFilename();
if (fileName.endsWith("xls") || fileName.endsWith("xlsx")) {
workbook = new HSSFWorkbook(multipartFile.getInputStream());
} else {
throw new Exception("文件不是Excel文件");
}
Sheet sheet = workbook.getSheet("sheet1");
int line = sheet.getLastRowNum();
if (line == 0) {
throw new Exception("请填写数据");
}
for (int i = 1; i < line + 1; i++) {
Row row = sheet.getRow(i);
if (row != null) {
CustomerPool customerPool = new CustomerPool();
customerPool.setAccountName(String.valueOf(row.getCell(0)));
customerPool.setRecipientsOf(String.valueOf(row.getCell(1)));
customerPool.setRecipientsWho(String.valueOf(row.getCell(2)));
customerPool.setDesignatedPerson(String.valueOf(row.getCell(3)));
customerPool.setRecipientsDate(String.valueOf(row.getCell(4)));
customerPool.setCustomerSource(String.valueOf(row.getCell(5)));
customerPool.setCustomerClassification(String.valueOf(row.getCell(6)));
customerPool.setCustomerIndustry(String.valueOf(row.getCell(7)));
customerPool.setAssessedValue(String.valueOf(row.getCell(8)));
customerPool.setTheDegreeOf(String.valueOf(row.getCell(9)));
customerPool.setProtectionOfState(String.valueOf(row.getCell(10)));
customerPool.setGiveUpTo(String.valueOf(row.getCell(11)));
customerPool.setGiveupAPerson(String.valueOf(row.getCell(12)));
iCustomerPoolService.save(customerPool);
}
}
return response.success("200","导入成功");
}
数据导出
遇坑。。要跟excel表格的表头对齐
(1)修改数据库字段的顺序,发现不行,sql查倒是ok(失败)
(2)修改类属性的顺序(成功)
/**
* 导出客户池信息
*/
@ApiResponses({
@ApiResponse(code = 200, message = "ok"),
})
@ResponseBody
@ApiOperation(value = "GET", notes = "导出客户池信息")
@RequestMapping(value = "/exportCustomerPool", method = RequestMethod.GET)
public Response exportCustomerPool() throws IOException, IllegalAccessException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
List<CustomerPool> jmsFlowContentEntities = iCustomerPoolService.query().eq("deleted",1).list();
System.out.println(jmsFlowContentEntities);
int a = 1;
String[] head = {"客户名称", "领用情况", "领用人", "指派人", "领用日期", "客户来源", "客户分类", "客户行业", "价值评估", "跟进程度", "保护状态", "放弃原因", "放弃人" };
HSSFRow row1 = sheet.createRow(0);
for (int j = 0; j < head.length; j++) {
HSSFCell cell = row1.createCell(j);
cell.setCellValue(head[j]);
}
for (int i = 0; i < jmsFlowContentEntities.size(); i++) {
HSSFRow row = sheet.createRow(a++);
Class<? extends CustomerPool> aClass = jmsFlowContentEntities.get(i).getClass();
Field[] fields = aClass.getDeclaredFields();
for (int j = 2; j < fields.length - 8; j++) {
Field f = fields[j];
System.out.println(f);
f.setAccessible(true);
HSSFCell cell = row.createCell(j - 2);
cell.setCellValue(String.valueOf(f.get(jmsFlowContentEntities.get(i))));
}
}
FileOutputStream fos = new FileOutputStream("D:/company/" + System.currentTimeMillis() + ".xls");
wb.write(fos);
fos.close();
return response.success(200, "导出成功!");
}