package com.ld.igds.inout.manager;
|
|
import com.bstek.dorado.annotation.Expose;
|
import com.ld.igds.constant.FoodVariety;
|
import com.ld.igds.constant.RespCodeEnum;
|
import com.ld.igds.data.PageResponse;
|
import com.ld.igds.file.CoreFileService;
|
import com.ld.igds.inout.dto.InoutParam;
|
import com.ld.igds.m.dto.NoticeInData;
|
import com.ld.igds.m.service.InoutCommonService;
|
import com.ld.igds.m.service.HInoutCustomerService;
|
import com.ld.igds.models.InoutCustomer;
|
import com.ld.igds.util.ContextUtil;
|
import org.apache.commons.lang3.StringUtils;
|
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.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Component;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* 解析Excel文件
|
*
|
* @author
|
*/
|
@Component(ExcelManager.BEAN_ID)
|
public class ExcelManager {
|
|
public static final String BEAN_ID = "sys.execlManager";
|
|
private static final String XLS = "xls";
|
private static final String XLSX = "xlsx";
|
|
@Autowired
|
private CoreFileService fileService;
|
@Autowired
|
private InoutCommonService inoutManagerService;
|
@Autowired
|
private HInoutCustomerService customerService;
|
|
/**
|
* sys.execlManager#updateExcelData
|
*
|
* @param fileName
|
* @return
|
*/
|
@Expose
|
public PageResponse<String> updateExcelData(String fileName) {
|
|
// 读取的Excel文件数据
|
List<NoticeInData> readResult = readExcel(fileName);
|
if (null == readResult) {
|
return new PageResponse<String>(RespCodeEnum.CODE_1111.getCode(),
|
"导入失败：没有解析到文件中数据！");
|
}
|
|
// 查询到的所有供应商信息
|
InoutParam param = new InoutParam();
|
List<InoutCustomer> allCustomer = inoutManagerService.listCustomer(param);
|
|
//新建任务存放集合
|
List<NoticeInData> newCustomerTaskList = new ArrayList<>();
|
Map<String, NoticeInData> newMap = new HashMap<>();
|
|
//存放编码和名称不一致的信息
|
StringBuilder stringBuilder = new StringBuilder();
|
|
//用tempFlag在后面来判断解析到的客户是否在客户表中存在
|
boolean tempFlag;
|
|
int max = 0;
|
for (NoticeInData noticeInData : readResult) {
|
//获取客户任务数据中的客户名称和编码
|
String customerName = noticeInData.getCustomerName();
|
String customerId = noticeInData.getCustomerId();
|
//判断编码是否为空,为空则给出提示,不进行操作
|
if(StringUtils.isEmpty(customerName)){
|
stringBuilder.append("客户‘").append(customerName).append("’信息不完整，不导入此条数据；\n");
|
continue;
|
}
|
|
tempFlag = true;
|
|
for (InoutCustomer customer : allCustomer) {
|
//获取供应商名称和编码
|
String name = customer.getName();
|
String id = customer.getId();
|
//判断名称是否相同
|
if(customerName.equals(name)){
|
//名称相同，则客户在表中存在
|
tempFlag = false;
|
//判断编码是否相同
|
if(StringUtils.isEmpty(customerId) || !id.equals(customerId)){
|
|
noticeInData.setCustomerId(id);
|
}
|
noticeInData.setCompanyId(customer.getCompanyId());
|
//身份证号
|
if(StringUtils.isEmpty(noticeInData.getCardId())){
|
noticeInData.setCardId(customer.getCardId());
|
}
|
//地址
|
if(StringUtils.isEmpty(noticeInData.getAddress())){
|
noticeInData.setAddress(customer.getAddress());
|
}
|
//电话
|
if(StringUtils.isEmpty(noticeInData.getPhone())){
|
noticeInData.setPhone(customer.getPhone());
|
}
|
//一卡通号
|
if(StringUtils.isEmpty(noticeInData.getBankNum())){
|
noticeInData.setBankNum(customer.getBankNum());
|
}
|
}
|
}
|
|
if(tempFlag){
|
if(max == 0){
|
max = Integer.parseInt(inoutManagerService.getMaxCustomerId(null));
|
}
|
max += 1;
|
noticeInData.setCustomerId(max + "");
|
}
|
|
newCustomerTaskList.add(noticeInData);
|
|
newMap.putIfAbsent(noticeInData.getCustomerName(), noticeInData);
|
}
|
|
//更新客户信息表
|
if(newMap.size() > 0){
|
for (NoticeInData noticeInData : newMap.values()) {
|
int i = inoutManagerService.updateCustomer(noticeInData);
|
if (i == 0) {
|
//说明没有更新到客户信息，进行新增
|
InoutCustomer data = new InoutCustomer();
|
data.setId(noticeInData.getCustomerId());
|
data.setName(noticeInData.getCustomerName());
|
data.setCardId(noticeInData.getCardId());
|
data.setBankNum(noticeInData.getBankNum());
|
data.setAddress(noticeInData.getAddress());
|
data.setPhone(noticeInData.getPhone());
|
customerService.saveOrUpdateData(data);
|
}
|
}
|
}
|
|
//判断任务集合是否为空
|
if (newCustomerTaskList.isEmpty()) {
|
return new PageResponse<String>(RespCodeEnum.CODE_1111.getCode(),
|
"导入失败！\n" + stringBuilder.toString());
|
} else {
|
//更新任务表
|
int temp = 1;
|
for (NoticeInData noticeInData : newCustomerTaskList) {
|
//设置客户通知单的组织编码等信息
|
noticeInData.setCompanyId(ContextUtil.getCompanyId());
|
noticeInData.setDeptId(ContextUtil.subDeptId(null));
|
noticeInData.setCreateUser(ContextUtil.getLoginUserCName());
|
if(temp < 10){
|
noticeInData.setId(ContextUtil.getTimeId() + "00" + temp);
|
}else if(temp < 100){
|
noticeInData.setId(ContextUtil.getTimeId() + "0" + temp);
|
}else {
|
noticeInData.setId(ContextUtil.getTimeId() + temp);
|
}
|
|
//更新客户任务信息,如果更新失败,则进行插入操作
|
inoutManagerService.updateNoticeIn(noticeInData);
|
temp += 1;
|
}
|
if(StringUtils.isEmpty(stringBuilder.toString())){
|
return new PageResponse<String>(RespCodeEnum.CODE_0000.getCode(), "数据全部导入成功！");
|
}else {
|
String message = "数据部分导入成功！\n"+ stringBuilder.toString();
|
return new PageResponse<String>(RespCodeEnum.CODE_0000.getCode(), message);
|
}
|
|
}
|
}
|
|
/**
|
* 读取Excel文件内容
|
*
|
* @param
|
* @return
|
*/
|
public List<NoticeInData> readExcel(String fileName) {
|
|
//获取文件后缀名并判断
|
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
|
|
//获取保存路径
|
String path = fileService.getTempFilePath();
|
path = path + fileName;
|
|
Workbook workbook = null;
|
FileInputStream inputStream = null;
|
|
try {
|
// 获取Excel文件
|
File excelFile = new File(path);
|
if (!excelFile.exists()) {
|
return null;
|
}
|
// 获取Excel工作簿
|
inputStream = new FileInputStream(excelFile);
|
workbook = getWorkbook(inputStream, fileType);
|
|
// 读取excel中的数据
|
return parseExcel(workbook);
|
} catch (Exception e) {
|
return null;
|
} finally {
|
try {
|
if (null != inputStream) {
|
inputStream.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 根据文件后缀名类型获取对应的工作簿对象
|
* @param inputStream 读取文件的输入流
|
* @param fileType 文件后缀名类型（xls或xlsx）
|
* @return 包含文件数据的工作簿对象
|
* @throws IOException
|
*/
|
public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
|
Workbook workbook = null;
|
if (fileType.equalsIgnoreCase(XLS)) {
|
workbook = new HSSFWorkbook(inputStream);
|
} else if (fileType.equalsIgnoreCase(XLSX)) {
|
workbook = new XSSFWorkbook(inputStream);
|
}
|
return workbook;
|
}
|
|
/**
|
* 解析Excel数据
|
*
|
* @param workbook
|
* @return
|
*/
|
private static List<NoticeInData> parseExcel(Workbook workbook) {
|
List<NoticeInData> resultDataList = new ArrayList<>();
|
//int numberOfSheets = workbook.getNumberOfSheets();
|
// 解析sheet
|
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
|
Sheet sheet = workbook.getSheetAt(sheetNum);
|
|
// 校验sheet是否合法
|
if (sheet == null) {
|
continue;
|
}
|
|
// 获取第一行数据,不用处理
|
int firstRowNum = sheet.getFirstRowNum();
|
|
// 解析每一行的数据，构造数据对象
|
int rowStart = firstRowNum + 1;
|
int rowEnd = sheet.getPhysicalNumberOfRows();
|
|
//定义变量,用于判断数据是否解析完
|
boolean flag = false;
|
|
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
|
Row row = sheet.getRow(rowNum);
|
|
if (null == row) {
|
//此行为空，则之后不再解析，判定为解析完成
|
flag = true;
|
break;
|
}
|
|
NoticeInData resultData = convertRowToData(row);
|
|
String customerName = resultData.getCustomerName();
|
String customerId = resultData.getCustomerId();
|
if(customerName.isEmpty() && customerId.isEmpty()){
|
//此行数据中如果用户名和编码都为空，则之后不解析，判定解析完成
|
flag = true;
|
break;
|
}
|
resultDataList.add(resultData);
|
}
|
if(flag){
|
break;
|
}
|
}
|
return resultDataList;
|
}
|
|
/**
|
* 提取每一行中需要的数据，构造成为一个结果数据对象
|
*
|
* @param row
|
* @return
|
*/
|
private static NoticeInData convertRowToData(Row row) {
|
NoticeInData resultData = new NoticeInData();
|
|
Cell cell;
|
int cellNum = 0;
|
|
//获取并设置用户编码
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setCustomerId("");
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String customerId = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(customerId)){
|
resultData.setCustomerId(customerId);
|
}
|
}
|
|
//获取并设置姓名
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setCustomerName("");
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String name = cell.getStringCellValue().trim().replaceAll(" ","");
|
if(StringUtils.isNotEmpty(name)){
|
resultData.setCustomerName(name);
|
}
|
}
|
|
//获取并设置身份证号
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setCardId("");
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String cardId = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(cardId)){
|
resultData.setCardId(cardId);
|
}
|
}
|
|
//获取并设置一卡通号
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setBankNum("");
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String bankNum = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(bankNum)){
|
resultData.setBankNum(bankNum);
|
}
|
}
|
|
//获取并设置地址
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setAddress("");
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String address = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(address)){
|
resultData.setAddress(address);
|
}
|
}
|
|
//获取并设置电话
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setPhone("");
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String phoneNum = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(phoneNum)){
|
resultData.setPhone(phoneNum);
|
}
|
}
|
|
//获取并设置任务数量
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setTargetNumber(0.0);
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String taskNum = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(taskNum)){
|
resultData.setTargetNumber(Double.valueOf(taskNum));
|
}
|
}
|
|
//获取并设置面积
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setFoodArea(0.0);
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String area = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(area)){
|
resultData.setFoodArea(Double.valueOf(area));
|
}
|
}
|
|
//获取并设置品种
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setFoodVariety("");
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String breed = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(breed)){
|
resultData.setFoodVariety(FoodVariety.getCode(breed.trim()));
|
}
|
}
|
|
//获取并设置年份
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
|
resultData.setYear("");
|
}else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String year = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(year)){
|
resultData.setYear(year);
|
}
|
}
|
|
return resultData;
|
}
|
}
|
