package com.whyc.service;
|
|
import com.whyc.dto.Response;
|
import com.whyc.dto.StandardFeatureCurve;
|
import com.whyc.mapper.CommonMapper;
|
import com.whyc.mapper.StandardFeatureCurveMapper;
|
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
|
import org.apache.poi.ss.usermodel.*;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import javax.annotation.Resource;
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class StandardFeatureCurveService {
|
|
@Resource
|
private StandardFeatureCurveMapper mapper;
|
|
@Resource
|
private CommonMapper commonMapper;
|
|
@Transactional
|
public void excelImport(InputStream inputStream) throws IOException, InvalidFormatException {
|
List<StandardFeatureCurve> curveList = new LinkedList<>();
|
//获取book
|
Workbook book = WorkbookFactory.create(inputStream);
|
int numberOfSheets = book.getNumberOfSheets();
|
for (int i = 0; i < numberOfSheets; i++) {
|
Sheet sheet = book.getSheetAt(i);
|
String sheetName = sheet.getSheetName();
|
int lastRowNum = sheet.getLastRowNum();
|
Row row1 = sheet.getRow(0);
|
//根据row1的列数来生成对象
|
short lastCellNum = row1.getLastCellNum();
|
for (int j = 1; j < lastRowNum; j++) {
|
//获取当前行
|
Row row = sheet.getRow(j);
|
Cell cellTime = row.getCell(0);
|
Date dateCellValue = cellTime.getDateCellValue();
|
String time = new SimpleDateFormat("HH:mm:ss").format(dateCellValue);
|
for (int k = 1; k < lastCellNum; k++) {
|
Cell cell = row.getCell(k);
|
if(cell == null){
|
break;
|
}
|
double value = cell.getNumericCellValue();
|
if(value == 0.0d){
|
break;
|
}
|
|
String titleNameUnion = sheetName+"_"+row1.getCell(k).getStringCellValue();
|
|
StandardFeatureCurve curve = new StandardFeatureCurve();
|
curve.setTitleNameUnion(titleNameUnion);
|
curve.setTime(time);
|
curve.setValue(value);
|
|
curveList.add(curve);
|
}
|
}
|
}
|
//存入数据库
|
Map<String, List<StandardFeatureCurve>> curveListMap = curveList.stream().collect(Collectors.groupingBy(StandardFeatureCurve::getTitleNameUnion));
|
curveListMap.forEach((titleNameUnion,curveList2)->{
|
//查询表是否存在,不存在需要先创建表
|
String tableName = commonMapper.existTable("web_site", "tb_standard_feature_curve_"+titleNameUnion);
|
if(tableName==null){
|
mapper.createTable(titleNameUnion);
|
}else{ //存在表,先清空数据
|
commonMapper.truncate("web_site", "tb_standard_feature_curve_"+titleNameUnion);
|
}
|
mapper.insertBatch(titleNameUnion,curveList2);
|
});
|
|
}
|
|
public List<StandardFeatureCurve> getList(String va, String c) {
|
if(commonMapper.existTable("web_site","tb_standard_feature_curve_"+va+"_"+c) == null){
|
return null;
|
}
|
return mapper.getList(va,c);
|
}
|
}
|
