package com.pactera.vds.regionalSchedule.controller;
import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.cloud.context.config.annotation.RefreshScope; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile;
import com.alibaba.fastjson.JSONObject; import com.pactera.vds.regionalSchedule.service.RegionalScheduleService;
import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import io.swagger.annotations.ApiParam;
@RestController @Api(value = "区域排期API controller", tags = { "区域排期操作接口" }) @RequestMapping("/regionalSchedule") @RefreshScope public class RegionalScheduleController {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
JSONObject message = new JSONObject(); @Autowired RegionalScheduleService regionalScheduleService;
/** * 读取excel表Controller * file excel文件名 AccountName 用户名 * @param file * @return */ @ApiOperation(value = "读取excel...", notes = "") @RequestMapping(value = "/importAdd", method = RequestMethod.POST) public JSONObject importEmp(@RequestParam("file") MultipartFile file, @RequestParam @ApiParam(name = "AccountName", value = "") String AccountName) { String fileName = file.getOriginalFilename(); try { JSONObject batchImport = regionalScheduleService.batchImport(fileName, file,AccountName); return batchImport; } catch (Exception e) { message.put("success", false); message.put("message", e.getMessage()); return message; } }
}
================================================================================================
package com.pactera.vds.regionalSchedule.service.impl;
import java.io.InputStream; import java.util.ArrayList; import java.util.List; import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile;
import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.pactera.vds.regionalSchedule.mapper.RegionalScheduleMapper; import com.pactera.vds.regionalSchedule.model.RegionalActivity; import com.pactera.vds.regionalSchedule.model.RegionalSchedule;
/** * * @author wstdf 2018-08-17 * */ @Service("regionalScheduleService") public class RegionalScheduleImpl { private final Logger logger = LoggerFactory.getLogger(this.getClass()); @Autowired RegionalScheduleMapper regionalScheduleMapper; /** * 导入 excel */ public JSONObject batchImport(String fileName, MultipartFile file,String AccountName) throws Exception { JSONObject result = new JSONObject(); if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) throw new Exception("上传文件格式不正确"); boolean isExcel2003 = true; if (fileName.matches("^.+\\.(?i)(xlsx)$")) isExcel2003 = false; InputStream is = file.getInputStream(); Workbook wb = null; if (isExcel2003) wb = new HSSFWorkbook(is); else wb = new XSSFWorkbook(is); Sheet sheet = wb.getSheetAt(0); String mediumName = sheet.getRow(0).getCell(0).getStringCellValue(); Map<String,String> user= regionalScheduleMapper.selectUser(AccountName);//获取大区 //logger.info("导入区域排期-" + mediumName + "开始, 总条数: " + (sheet.getLastRowNum() - 2)); // 区域排期-活动 List<String> importAddActivity = importAddActivity(sheet, 0,user); result.put("errorMessage", importAddActivity); if(importAddActivity.contains("您只能导入本大区的数据")){ result.put("message", "新增失败!"); result.put("success", false); }else{ result.put("message", "新增成功!"); result.put("success", true); } return result; }
// 导入-方法 public List<String> importAddActivity(Sheet sheet, Integer mediumType, Map<String, String> user) throws Exception { List<String> errorList = new ArrayList<String>(); JSONArray regionalScheduleArr = new JSONArray(); for (int r = 0; r <= sheet.getLastRowNum(); r++) { //实体类 RegionalSchedule regionalSchedule = new RegionalSchedule(); RegionalActivity regionalActivity = new RegionalActivity();
Row row = sheet.getRow(r); if (row == null) { continue; } regionalSchedule.setMediumType(mediumType);// 101 regionalActivity.setMediumType(mediumType); // uuid String uuid = row.getCell(1).getStringCellValue(); //logger.info("1 == " + uuid);// uuid regionalSchedule.setUuid(uuid);
String mediumName = row.getCell(2).getStringCellValue(); String fromArea = row.getCell(3).getStringCellValue(); // logger.info("3 == " + fromArea);// fromArea regionalSchedule.setFromArea(fromArea); regionalActivity.setFromArea(fromArea);
String province = row.getCell(4).getStringCellValue(); // logger.info("4 == " + province); regionalActivity.setProvince(province);
String city = row.getCell(5).getStringCellValue(); //logger.info("5 == " + city); regionalActivity.setCity(city);
Cell year = row.getCell(6); //logger.info("6 == " + year);// 年 beginTime 2019.0
Cell month = row.getCell(8); //logger.info("8 == " + month);// 月 endTime 3月 regionalSchedule.setBeginTime(month.toString()); regionalScheduleArr.add(regionalSchedule);
} //logger.info("读取完成,开始新增..."); int totalNumber = regionalScheduleArr.size(); int trueNumber = 0; //logger.info("新增总条数 : " + totalNumber); for (int i = 0; i < regionalScheduleArr.size(); i++) { // 遍历新增 todo } //logger.info("新增完成! 新增总数 : " + totalNumber + "条 , 新增成功 : " + trueNumber + " 条 " + " 新增错误的信息: " + errorList); errorList.add("新增总数 : " + totalNumber + "条 "); errorList.add("新增成功 : " + trueNumber + " 条 "); return errorList; } }
