一. 下载excel文件到本地
controller层
/**
* 下载excel文件
*
* @param request 请求对象
* @param response 响应对象
*/
@RequestMapping("/downLoadAreaHoseFile")
@ResponseBody
@ApiOperation(value = "下载excel文件", notes = "下载excel文件", httpMethod = "GET")
public void downLoadAreaHoseFile(HttpServletRequest request, HttpServletResponse response) throws Exception {
leAreaService.downLoadAreaHoseFile(request,response);
}
service层
@Override
public void downLoadAreaHoseFile(HttpServletRequest request, HttpServletResponse response) {
HSSFWorkbook workbook = null;
try {
//1, 获取文件路径, 创建文件输入流对象, 获取excel文件
String path = request.getSession().getServletContext().getRealPath("template/areahouseimport.xls");
FileInputStream fileInputStream = new FileInputStream(path);
workbook = new HSSFWorkbook(fileInputStream);
fileInputStream.close();
// 动态写入数据, 如果不需要对excel文件进行数据写入操作可直接返回
// 2,获取excel中的工作表 , 根据索引获取
// 获取小区表]
// getSheetAt(索引) 获取excel中的工作表sheet 索引从0开始
HSSFSheet areaSheet = workbook.getSheetAt(1);
List<PartnerArea> areaList = selectLeArea(new HashMap<String, Object>());
// Columns.getColumnLabels(columnNum) 返回[1,columnNum] 共columnNum个对应xls列字母的数组
// columnNum 列的个数,至少要为1
String[] columnLabels = Columns.getColumnLabels(areaList.size());
//创建存放行的集合
List<HSSFRow> hssfRowList = new ArrayList<>();
//创建存放小区名称的行
HSSFRow headRow = areaSheet.createRow(0);
hssfRowList.add(headRow);
for (int i = 0; i < areaList.size(); i++) {
//添加数据到对应单元格
PartnerArea area = areaList.get(i);
//添加小区字段信息
headRow.createCell(i, CellType.STRING).setCellValue(area.getAreaName() + "_" + area.getId());
Map<String, Object> map = new HashMap<>();
map.put("areaId", area.getId());
List<AreaPropertyCharges> chargesList = getChargesByArea(map);
//初始化命名区域
HSSFName name = workbook.createName();
//设置命名区域名称 设置数据的所属范围
String reference = areaSheet.getSheetName()+"!" + columnLabels[i]+"2:"+columnLabels[i] + (chargesList.size()+2);
name.setNameName(area.getAreaName()+"_"+area.getId());
name.setRefersToFormula(reference);
for (int j = 1; j <= chargesList.size(); j++) {
//判断是否创建行存储标准信息的行
if (hssfRowList.size()<= j)
hssfRowList.add(areaSheet.createRow(j));
//获取到当前数据行
HSSFRow crow = hssfRowList.get(j);
AreaPropertyCharges charges = chargesList.get(j - 1);
crow.createCell(i, CellType.STRING).setCellValue(charges.getName() + "_" + charges.getId());
}
}
// 5, 写出文件下载
// 5.1 获取response自带的输出流
OutputStream fos = response.getOutputStream();
String userAgent = request.getHeader("USER-AGENT");
String fileName = "areahouseimport";
try {
if (StringUtils.contains(userAgent, "Mozilla")) {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
fileName = URLEncoder.encode(fileName, "utf-8");
}
areaSheet.protectSheet("Do you want to know the password");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8"); //设置contentType为excel格式
response.setHeader("content-Disposition", "Attachment;Filename=" + fileName + ".xls");
//写入到流
workbook.write(fos);
fos.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (!Objects.isNull(workbook)) {
workbook.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
二.上传excel保存数据并返回错误信息文件
- 根据传入的excel文件 获取正确的数据集合, 将正确数据保存.
- 获取正确数据集合的过程对错误信息进行判断整理, 并将错误信息保存.
- 如果有错误信息, 将错误信息写入文件, 上传云端并返回错误文件路径.
controller层
/**
* 导入信息
*
* @param request
* @return
*/
@RequestMapping("/importAreaHose")
@ResponseBody
@ApiOperation(value = "导入信息", notes = "导入信息", httpMethod = "POST")
public String importAreaHose(MultipartHttpServletRequest request,
HttpServletRequest servletRequest,HttpServletResponse response) throws Exception {
MultipartFile multipartFile = request.getFile("areahouseimport");
if (Objects.isNull(multipartFile)) {
return "0";
}
// 上传文件不对
if (!".xls".equals(multipartFile.getOriginalFilename().substring(
multipartFile.getOriginalFilename().indexOf("."),
multipartFile.getOriginalFilename().length()
))) {
return "-2";
}
return leAreaService.importAreaHose(multipartFile.getInputStream(),servletRequest,response);
}
service层
// 导入信息
@Override
public String importAreaHose(InputStream inputStream, HttpServletRequest servletRequest, HttpServletResponse response) {
try {
//1, 读取文件中的数据
List<AreaHouse> areaHouseList = getAreaHourseByFile(inputStream);
if (CollectionUtils.isEmpty(areaHouseList) && errorAreaHouseMap.size() == 0) { //为空, 返回0
return "0";
}
//没有错误信息, 正常执行
if (errorAreaHouseMap.size() == 0) {
// 2, 遍历添加数据到数据库中
for (AreaHouse areaHouse : areaHouseList) {
submitAreaHouse(areaHouse);
}
return "1";
} else {
String url = downLoadErrorAreaHouseFile(servletRequest, errorAreaHouseMap, errorMap,inputStream);
return url;
}
} finally {
if (inputStream!=null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
//保存文件导入错误信息
Map<Integer, String> errorMap = new HashMap<>();
//保存文件导入错误对象
Map<Integer, AreaHouse> errorAreaHouseMap = new HashMap<>();
/**
* 获取文件导入的实体
*
* @param inputStream 输入流
* @return 返回房间信息实体
*/
private List<AreaHouse> getAreaHourseByFile(InputStream inputStream) {
//每次调用, 清空
errorMap.clear();
errorAreaHouseMap.clear();
List<AreaHouse> areaHousesList = new ArrayList<>();
POIFSFileSystem poifsFileSystem = null;
HSSFWorkbook workbook = null;
try {
//1,解析输入流, 获取excel文件对象
poifsFileSystem = new POIFSFileSystem(inputStream);
workbook = new HSSFWorkbook(poifsFileSystem);
// 2, 获取房间信息的sheet表
HSSFSheet sheet = workbook.getSheetAt(0);
// 3,遍历每一行获取行数据
for (int rownum = 1; rownum < sheet.getLastRowNum(); rownum++) {
HSSFRow row = sheet.getRow(rownum);
if (row == null) {
continue;
}
// 4,获取单元格数据
AreaHouse areaHouse = new AreaHouse();
// 判断数据是否存在和是否有错误
// 所属小区
HSSFCell aid = row.getCell(0);
if (Objects.isNull(aid)||StringUtils.isEmpty(aid.getStringCellValue().trim())) {
continue;
} else {
aid.setCellType(CellType.STRING);
String[] areaIdAndName = aid.getStringCellValue().split("_");
areaHouse.setAreaId(((Long.parseLong(areaIdAndName[1]))));
areaHouse.setAreaName(areaIdAndName[0]);
}
// 收费标准
HSSFCell charges = row.getCell(1);
if (Objects.isNull(charges)||StringUtils.isEmpty(charges.getStringCellValue().trim())) {
//如果map中有值就进行值追加
if (errorMap.containsKey(rownum) && errorMap.get(rownum) != null) {
String value = errorMap.get(rownum) + " ; ";
errorMap.put(rownum, value + "收费标准不能为空");
} else {
errorMap.put(rownum, "收费标准不能为空");
}
} else {
charges.setCellType(CellType.STRING);
areaHouse.setPropertyChargesId(Long.parseLong(charges.getStringCellValue().split("_")[1]));
}
// 身份证号码
HSSFCell cartId = row.getCell(3);
if (Objects.nonNull(cartId)) {
areaHouse.setCardId(cartId.getStringCellValue());
}
// 根据小区、楼号、单元、门牌号 查询房间是否重复
if (areaHouse.getAreaId() != null && areaHouse.getBuildingNumber() != null
&& areaHouse.getUnitNumber() != null && areaHouse.getHouseNumber() != null) {
Map<String, Object> map = new HashMap<>();
map.put("areaId", areaHouse.getAreaId());
map.put("buildingNumber", areaHouse.getBuildingNumber());
map.put("unitNumber", areaHouse.getUnitNumber());
map.put("houseNumber", areaHouse.getHouseNumber());
int count = getExistsAreaHouse(map);
if (count > 0) {
if (errorMap.containsKey(rownum) && errorMap.get(rownum) != null) {
String value = errorMap.get(rownum) + " ; ";
errorMap.put(rownum, value + "小区、楼号、单元、门牌号不能重复");
} else {
errorMap.put(rownum, "小区、楼号、单元、门牌号不能重复");
}
}
}
// 平米数
HSSFCell square = row.getCell(9);
if (square!=null)square.setCellType(CellType.NUMERIC);
if (Objects.isNull(square)|| square.getNumericCellValue()==0) {
if (errorMap.containsKey(rownum) && errorMap.get(rownum) != null) {
String value = errorMap.get(rownum) + " ; ";
errorMap.put(rownum, value + "平米数不能为空");
} else {
errorMap.put(rownum, "平米数不能为空");
}
} else {
areaHouse.setSquareMeters(new BigDecimal(square.getNumericCellValue()));
}
// 物业费到期时间
HSSFCell dueData = row.getCell(10);
if (Objects.isNull(dueData)||Objects.isNull(dueData.getDateCellValue())) {
if (errorMap.containsKey(rownum) && errorMap.get(rownum) != null) {
String value = errorMap.get(rownum) + " ; ";
errorMap.put(rownum, value + "物业费到期时间不能为空");
} else {
errorMap.put(rownum, "物业费到期时间不能为空");
}
} else {
//获取时间为Date类型
Date dateCellValue = dueData.getDateCellValue();
//转换为 LocalDateTime 类型
LocalDateTime dueDate = LocalDateTime.ofInstant(dateCellValue.toInstant(), ZoneId.systemDefault());
areaHouse.setDueDate(dueDate);
}
// 创建时间
areaHouse.setCreateTime(LocalDateTime.now());
//默认值
areaHouse.setType(2);
if (!errorMap.containsKey(rownum) && errorMap.get(rownum) == null) {
areaHousesList.add(areaHouse);
} else {
// 将错误数据放入错误文件夹
errorAreaHouseMap.put(rownum, areaHouse);
}
}
return areaHousesList;
} catch (IOException e) {
e.printStackTrace();
return Collections.emptyList();
}
}
/**
* 上传错误信息的房间文件
*
* @param request
* @param errorAreaHouseMap
* @param errorMap
* @return
*/
private String downLoadErrorAreaHouseFile(HttpServletRequest request,
Map<Integer, AreaHouse> errorAreaHouseMap,
Map<Integer, String> errorMap,
InputStream inputStream) {
String url = null;
HSSFWorkbook workbook = null;
InputStream inputStream2 = null;
ByteArrayOutputStream bos = null;
String path = request.getSession().getServletContext().getRealPath("template/areahouseimport_error.xls");
try {
inputStream.reset();
workbook = new HSSFWorkbook(inputStream);
// 2,获取excel中的工作表 , 根据索引获取
//添加小区信息表
HSSFSheet houseSheet = workbook.getSheetAt(0);
//添加房间数据
int hlastRowNum = houseSheet.getLastRowNum();
HSSFRow row11 = houseSheet.getRow(0);
HSSFCell cell1 = row11.createCell(11);
cell1.setCellType(CellType.STRING);
cell1.setCellValue("错误信息");
//定义红色字体
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.RED.index);
cellStyle.setFont(font);
//引用红色字体
cell1.setCellStyle(cellStyle);
// 设置行数和错误信息
for (Integer key : errorAreaHouseMap.keySet()) {
String error = errorMap.get(key);
HSSFRow row = houseSheet.getRow(key);
if (row == null) {
continue;
}
HSSFCell cell = row.getCell(11);
cell.setCellType(CellType.STRING);
String oldStringCellValue = cell.getStringCellValue();
cell.setCellValue(oldStringCellValue+","+error);
cell.setCellStyle(cellStyle);
}
//保存Excel数据到字节流中
bos = new ByteArrayOutputStream();
workbook.write(bos);
byte[] bytes = bos.toByteArray();
inputStream2 = new ByteArrayInputStream(bytes);
// 5, 上传文件到云端 (可自行修改)
url = YunUploadUtils.getInstance().uploadFileToUpYun(
upYunMapper.queryUpYunSetting().getUpYunConf(),
inputStream2, bytes, path,"UploadOwnerInfoError");
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (!Objects.isNull(workbook)) {
workbook.close();
}
if (!Objects.isNull(inputStream2)){
inputStream2.close();
}
if (!Objects.isNull(bos)){
bos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return url;
}