package com.fzzy.igds.service;
|
|
import com.fzzy.igds.constant.FoodVariety;
|
import com.fzzy.igds.domain.InoutRecord;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.commons.lang3.time.DateUtils;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* @Description
|
* @Author CZT
|
* @Date 2026/01/19 18:52
|
*/
|
@Service
|
public class ExportService {
|
|
private static final String XLS = "xls";
|
private static final String XLSX = "xlsx";
|
|
@Resource
|
private FileService fileService;
|
|
|
/**
|
* 读取Excel文件内容
|
*
|
* @param
|
* @return
|
*/
|
public List<InoutRecord> readExcel(String fileName) {
|
|
//获取文件后缀名并判断
|
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
|
|
//获取保存路径
|
String path = fileService.getFileSavePath("TEMP");
|
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();
|
}
|
}
|
}
|
|
/**
|
* 解析Excel数据
|
*
|
* @param workbook
|
* @return
|
*/
|
private static List<InoutRecord> parseExcel(Workbook workbook) throws Exception{
|
List<InoutRecord> resultDataList = new ArrayList<>();
|
|
// 解析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 + 2;
|
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;
|
}
|
|
InoutRecord resultData = convertRowToData(row);
|
resultDataList.add(resultData);
|
}
|
if(flag){
|
break;
|
}
|
}
|
return resultDataList;
|
}
|
|
/**
|
* 根据文件后缀名类型获取对应的工作簿对象
|
* @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;
|
}
|
|
/**
|
* 提取每一行中需要的数据，构造成为一个结果数据对象
|
*
|
* @param row
|
* @return
|
*/
|
private static InoutRecord convertRowToData(Row row) throws Exception {
|
InoutRecord resultData = new InoutRecord();
|
|
Cell cell;
|
int cellNum = 0;
|
|
//第一列序号，不做处理
|
cell = row.getCell(cellNum++);
|
|
//日期
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setCustomerName("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String time = cell.getStringCellValue().trim().replaceAll(" ","");
|
if(StringUtils.isNotEmpty(time)){
|
resultData.setRegisterTime(DateUtils.parseDate(time,"yyyy-MM-dd"));
|
}
|
}
|
|
//类型
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setType("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String type = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(type)){
|
resultData.setType(type);
|
}
|
}
|
|
//车牌号
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setPlateNum("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String plateNum = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(plateNum)){
|
resultData.setPlateNum(plateNum);
|
}
|
}
|
|
//承运人
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setUserName("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String userName = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(userName)){
|
resultData.setUserName(userName);
|
}
|
}
|
|
//往来单位
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setCustomerName("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String customerName = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(customerName)){
|
resultData.setCustomerName(customerName);
|
}
|
}
|
|
//通知单编码
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setNoticeId("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String noticeId = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(noticeId)){
|
resultData.setNoticeId(noticeId);
|
}
|
}
|
|
//装卸仓库
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setDepotId("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String depotName = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(depotName)){
|
resultData.setDepotId(depotName);
|
}
|
}
|
|
//粮食品种
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setFoodVariety("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String foodVariety = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(foodVariety)){
|
resultData.setFoodVariety(FoodVariety.getCode(foodVariety.trim()));
|
}
|
}
|
|
//粮食年份
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setFoodYear("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String year = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(year)){
|
resultData.setFoodYear(year);
|
}
|
}
|
|
//满车重量
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setFullWeight(0.0);
|
}else {
|
cell.setCellType(CellType.STRING);
|
String fullWeight = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(fullWeight)){
|
resultData.setFullWeight(Double.valueOf(fullWeight));
|
}
|
}
|
|
//空车重量
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setEmptyWeight(0.0);
|
}else {
|
cell.setCellType(CellType.STRING);
|
String emptyWeight = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(emptyWeight)){
|
resultData.setEmptyWeight(Double.valueOf(emptyWeight));
|
}
|
}
|
|
//扣重
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setDeOther(0.0);
|
}else {
|
cell.setCellType(CellType.STRING);
|
String deOther = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(deOther)){
|
resultData.setDeOther(Double.valueOf(deOther));
|
}
|
}
|
|
//结算重量
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setSettleWeight(0.0);
|
resultData.setRecordWeight(0.0);
|
}else {
|
cell.setCellType(CellType.STRING);
|
String recordWeight = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(recordWeight)){
|
resultData.setSettleWeight(Double.valueOf(recordWeight));
|
resultData.setRecordWeight(Double.valueOf(recordWeight));
|
}
|
}
|
|
//单价
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setPrice(0.0);
|
}else {
|
cell.setCellType(CellType.STRING);
|
String price = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(price)){
|
resultData.setPrice(Double.valueOf(price));
|
}
|
}
|
|
//水分
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setPerWet(0.0);
|
}else {
|
cell.setCellType(CellType.STRING);
|
String perWet = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(perWet)){
|
resultData.setPerWet(Double.valueOf(perWet));
|
}
|
}
|
|
//杂质
|
cell = row.getCell(cellNum++);
|
if(cell == null || cell.getCellType() == CellType.BLANK){
|
resultData.setPerImpurity(0.0);
|
}else {
|
cell.setCellType(CellType.STRING);
|
String perImpurity = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(perImpurity)){
|
resultData.setPerImpurity(Double.valueOf(perImpurity));
|
}
|
}
|
|
//备注
|
cell = row.getCell(cellNum++);
|
if(null == cell || cell.getCellType() == CellType.BLANK){
|
resultData.setRemarks("");
|
}else {
|
cell.setCellType(CellType.STRING);
|
String remarks = cell.getStringCellValue().trim();
|
if(StringUtils.isNotEmpty(remarks)){
|
resultData.setRemarks(remarks);
|
}
|
}
|
|
if(null != resultData.getFullWeight() && null != resultData.getEmptyWeight()){
|
resultData.setNetWeight(resultData.getFullWeight() - resultData.getEmptyWeight());
|
}
|
return resultData;
|
}
|
|
}
|
