package com.thhy.materials.modules.biz.concret.service.impl;
|
|
import cn.hutool.core.io.IoUtil;
|
import cn.hutool.poi.excel.ExcelWriter;
|
import com.github.pagehelper.PageHelper;
|
import com.github.pagehelper.PageInfo;
|
import com.thhy.general.common.BasicResult;
|
import com.thhy.general.config.SysUserInfo;
|
import com.thhy.general.utils.UserInfoUtils;
|
import com.thhy.materials.modules.biz.concret.entity.SysSteel;
|
import com.thhy.materials.modules.biz.concret.entity.SysSteelRecord;
|
import com.thhy.materials.modules.biz.concret.entity.dto.RecordDto;
|
import com.thhy.materials.modules.biz.concret.entity.dto.RecordOutDto;
|
import com.thhy.materials.modules.biz.concret.entity.dto.SteelStatisticsDto;
|
import com.thhy.materials.modules.biz.concret.entity.dto.SteelStatisticsExcel;
|
import com.thhy.materials.modules.biz.concret.mapper.SysSteelMapper;
|
import com.thhy.materials.modules.biz.concret.mapper.SysSteelRecordMapper;
|
import com.thhy.materials.modules.biz.concret.service.SysSteelRecordService;
|
import com.thhy.materials.modules.biz.utils.ExcelUtil;
|
import org.apache.poi.hssf.usermodel.DVConstraint;
|
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddressList;
|
import org.apache.poi.util.IOUtils;
|
import org.apache.poi.xssf.streaming.SXSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFRow;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
import org.springframework.util.CollectionUtils;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.io.OutputStream;
|
import java.math.BigDecimal;
|
import java.text.DecimalFormat;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.time.LocalDate;
|
import java.util.*;
|
|
@Service
|
public class SysSteelRecordServiceImpl implements SysSteelRecordService {
|
@Resource
|
private SysSteelRecordMapper recordMapper;
|
@Resource
|
private SysSteelMapper steelMapper;
|
|
@Override
|
@Transactional
|
public BasicResult recordInsert(HashMap<String, Object> values) {
|
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
String userId = sysUserInfo.getUserId();
|
String companyId = sysUserInfo.getCompanyId();
|
values.put("createUser",userId);
|
values.put("companyId",companyId);
|
String steelId = values.get("steelId").toString();
|
Double changeStock = Double.valueOf(values.get("changeStock").toString());
|
Double stocks = recordMapper.steelSelectStock(steelId);
|
BigDecimal b1 = new BigDecimal(Double.toString(changeStock));
|
double stock =0;
|
if(stocks!=null && !stocks.equals("")&&!stocks.equals("0.0")&&Math.abs(stocks)>0.000000000001){
|
BigDecimal b2 = new BigDecimal(Double.toString(stocks));
|
stock = b1.add(b2).doubleValue();
|
}else {
|
stock=b1.doubleValue();
|
}
|
recordMapper.steelStockUpdate(steelId,stock);
|
values.put("id","");
|
recordMapper.recordInsert(values);
|
return BasicResult.success("入库成功");
|
}
|
|
@Override
|
public BasicResult recordList(Map<String, Object> values) {
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
String companyId = sysUserInfo.getCompanyId();
|
values.put("companyId",companyId);
|
|
Integer pageNum = Integer.valueOf(values.get("pageNum").toString());
|
Integer pageSize = Integer.valueOf(values.get("pageSize").toString());
|
PageHelper.startPage(pageNum,pageSize);
|
List<SysSteelRecord> recordList = recordMapper.recordList(values);
|
recordList.stream().forEach(record -> {
|
DecimalFormat df = new DecimalFormat("#.####");
|
String formattedNumber = df.format(record.getChangeStock());
|
record.setChangeStock(Double.valueOf(formattedNumber));
|
});
|
PageInfo<SysSteelRecord> sysSteelRecordPageInfo = new PageInfo<>(recordList);
|
return BasicResult.success(sysSteelRecordPageInfo);
|
}
|
|
@Override
|
public BasicResult recordThingJsList(String token) {
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo(token);
|
String companyId = sysUserInfo.getCompanyId();
|
List<SysSteelRecord> recordList = recordMapper.recordThingJsList(companyId);
|
return BasicResult.success(recordList);
|
}
|
|
@Override
|
@Transactional
|
public BasicResult recordDel(String id) {
|
SysSteelRecord recordInfo = recordMapper.recordInfo(id);
|
|
Double stocks = recordMapper.steelSelectStock(recordInfo.getSteelId());
|
Double changeStock = recordInfo.getChangeStock();
|
BigDecimal bd1 = new BigDecimal(Double.toString(stocks));
|
|
BigDecimal bd2 = new BigDecimal(Double.toString(changeStock));
|
|
double stockss =0;
|
if(stocks!=null && !stocks.equals("")&&!stocks.equals("0.0")&&Math.abs(stocks)>0.000000000001){
|
stockss = bd1.subtract(bd2).doubleValue();
|
}else {
|
stockss=(0-bd2.doubleValue());
|
}
|
|
|
//修改实时库存
|
recordMapper.updateStockSteelId(recordInfo.getSteelId(),stockss);
|
recordMapper.recordDel(id);
|
return BasicResult.success("删除成功");
|
}
|
|
@Override
|
public BasicResult recordInfo(String id) {
|
SysSteelRecord recordInfo = recordMapper.recordInfo(id);
|
return BasicResult.success(recordInfo);
|
}
|
|
@Override
|
public void recordExport(Map<String, Object> values, HttpServletResponse response) {
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
String companyId = sysUserInfo.getCompanyId();
|
values.put("companyId",companyId);
|
List<SysSteelRecord> recordList = recordMapper.recordList(values);
|
// if (recordList.size()<=0){
|
// response.setStatus(400);
|
// return;
|
// }
|
List<RecordDto> recordDtos = new ArrayList<>();
|
|
for (SysSteelRecord steelRecord : recordList){
|
RecordDto recordDto = new RecordDto();
|
recordDto.setCreateTime(steelRecord.getCreateTime());
|
recordDto.setSupplierName(steelRecord.getSupplierName());
|
recordDto.setFactoryName(steelRecord.getFactoryName());
|
recordDto.setBrand(steelRecord.getBrand());
|
recordDto.setSteelName(steelRecord.getSteelName());
|
recordDto.setSteelModel(steelRecord.getSteelModel());
|
recordDto.setChangeStock(steelRecord.getChangeStock());
|
recordDto.setUnit(steelRecord.getUnit());
|
recordDto.setRealName(steelRecord.getRealName());
|
recordDtos.add(recordDto);
|
}
|
|
Integer stockType = Integer.valueOf(values.get("stockType").toString());
|
|
ExcelWriter excel = new ExcelWriter();//定义excel
|
if(stockType==1){
|
excel.addHeaderAlias("createTime", "入库时间");
|
}else {
|
excel.addHeaderAlias("createTime", "质检合格时间");
|
}
|
excel.addHeaderAlias("supplierName", "供应商");
|
excel.addHeaderAlias("factoryName", "厂家");
|
excel.addHeaderAlias("brand", "品牌");
|
excel.addHeaderAlias("steelName", "钢筋名称");
|
excel.addHeaderAlias("steelModel", "规格型号");
|
|
if(stockType==1){
|
excel.addHeaderAlias("changeStock", "入库重量");
|
}else {
|
excel.addHeaderAlias("changeStock", "出库重量");
|
}
|
excel.addHeaderAlias("unit", "计量单位");
|
if(stockType==1){
|
excel.addHeaderAlias("realName", "入库人");
|
}else {
|
excel.addHeaderAlias("realName", "出库人");
|
}
|
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
|
response.setHeader("Content-Disposition", "attachment;filename=test.xls");
|
OutputStream out = null;
|
try {
|
out = response.getOutputStream();
|
excel.write(recordDtos);
|
excel.flush(out, true);
|
} catch (IOException e) {
|
e.printStackTrace();
|
} finally {
|
// 关闭writer，释放内存
|
excel.close();
|
}
|
IoUtil.close(out);
|
}
|
|
@Override
|
public void variateExcelTemplate(HttpServletResponse response) {
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
String companyId = sysUserInfo.getCompanyId();
|
List<String> supplierList = recordMapper.supplierPull(companyId);
|
System.out.println("----------------------------------||||||-----------------------"+supplierList);
|
|
|
if(CollectionUtils.isEmpty(supplierList)){
|
BasicResult.faild("11111","该公司没有供应商","该公司没有供应商");
|
}
|
|
String[] gys = supplierList.toArray(new String[supplierList.size()]);
|
|
XSSFWorkbook book = new XSSFWorkbook();
|
XSSFSheet sheet = book.createSheet();
|
XSSFRow row = sheet.createRow(0);
|
row.createCell(0).setCellValue("入库时间");
|
row.createCell(1).setCellValue("车牌号");
|
row.createCell(2).setCellValue("供应商");
|
// row.createCell(2).setCellValue("厂家");
|
// row.createCell(3).setCellValue("品牌");
|
row.createCell(3).setCellValue("钢筋名称--规格型号");
|
row.createCell(4).setCellValue("单位");
|
row.createCell(5).setCellValue("入库重量");
|
//row.createCell(6).setCellValue("计量单位");
|
// row.createCell(6).setCellValue("入库人");
|
|
//供应商
|
// CellRangeAddressList regions = new CellRangeAddressList(1, 500, 2, 2);
|
// DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
|
// DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(gys);
|
// DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
|
// sheet.addValidationData(createValidation);
|
//隐藏页
|
Sheet hidden = book.createSheet("供应商");
|
Cell cell = null;
|
for (int i = 0; i < gys.length; i++) {
|
String name = gys[i];
|
Row row1 = hidden.createRow(i);
|
cell = row1.createCell(0);
|
cell.setCellValue(name);
|
}
|
|
Name namedCell = book.createName();
|
namedCell.setNameName("供应商");
|
namedCell.setRefersToFormula("供应商" + "!$A$1:$A$" + gys.length);
|
//sheet设置为隐藏
|
book.setSheetHidden(book.getSheetIndex(hidden), true);
|
CellRangeAddressList addressList = new CellRangeAddressList(1, 500, 2, 2);
|
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
|
DataValidationConstraint gysList = dvHelper.createFormulaListConstraint("供应商");
|
DataValidation validation = dvHelper.createValidation(gysList, addressList);
|
sheet.addValidationData(validation);
|
// createDropDownListWithHiddenSheet(sheet,1, 500, 1, 2,gys,book,"供应商");
|
|
|
|
Map<String, Object> values = new HashMap<>();
|
values.put("companyId",companyId);
|
List<SysSteel> steels = steelMapper.steelList(values);
|
if(CollectionUtils.isEmpty(steels)){
|
BasicResult.faild("11111","请先添加钢筋类型","请先添加钢筋类型");
|
}
|
List<String> lx = new ArrayList<>();
|
steels.forEach(sysSteel -> {
|
lx.add(sysSteel.getSteelName()+"-"+sysSteel.getSteelModel());
|
});
|
String[] gjlx = lx.toArray(new String[lx.size()]);
|
|
|
//钢筋类型
|
CellRangeAddressList regions1 = new CellRangeAddressList(1, 500, 3, 3);
|
DataValidationHelper dataValidationHelper1 = sheet.getDataValidationHelper();
|
DataValidationConstraint createExplicitListConstraint1 = dataValidationHelper1.createExplicitListConstraint(gjlx);
|
DataValidation createValidation1 = dataValidationHelper1.createValidation(createExplicitListConstraint1, regions1);
|
sheet.addValidationData(createValidation1);
|
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
|
response.setHeader("Content-Disposition", "attachment;filename=test.xls");
|
OutputStream out = null;
|
try {
|
out = response.getOutputStream();
|
book.write(out);
|
out.close();
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
IOUtils.closeQuietly(out);
|
}
|
BasicResult.success();
|
}
|
|
@Override
|
public BasicResult variateExcel(MultipartFile file, HttpServletRequest request) {
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
|
|
List<List<Object>> lists = null;//去工具类处理，返回excel中的数据
|
try {
|
lists = ExcelUtil.getUserListByExcel(file.getInputStream(), file.getOriginalFilename());
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
lists.remove(0);
|
// row.createCell(0).setCellValue("入库时间");
|
// row.createCell(1).setCellValue("车牌号");
|
// row.createCell(2).setCellValue("供应商");
|
//// row.createCell(2).setCellValue("厂家");
|
//// row.createCell(3).setCellValue("品牌");
|
// row.createCell(3).setCellValue("钢筋名称--规格型号");
|
// row.createCell(4).setCellValue("单位");
|
// row.createCell(5).setCellValue("入库重量");
|
|
if(lists.size()>0){
|
for (List<Object> list : lists){
|
HashMap<String, Object> values = new HashMap<>();
|
//入库时间
|
String createTime = list.get(0).toString();
|
values.put("createTime",createTime);
|
//车牌号
|
String carName = list.get(1).toString();
|
values.put("carName",carName);
|
|
//供应商
|
String supplierName = list.get(2).toString();
|
String supplierId = recordMapper.supplierIdSelect(supplierName);
|
values.put("supplierId",supplierId);
|
values.put("stockType",1);
|
|
// //厂家
|
// String factoryName = list.get(2).toString();
|
// values.put("factoryName",factoryName);
|
// //品牌
|
// String brand = list.get(3).toString();
|
// values.put("brand",brand);
|
//钢筋-规格型号
|
String steel_id = list.get(3).toString();
|
String[] split = steel_id.split("-");
|
//钢筋类型
|
String steelName = split[0];
|
//钢筋型号
|
String steelModel = split[1];
|
SysSteel steelIds = recordMapper.steelNameAndSteelModelSelectSteelId(steelName,steelModel);
|
values.put("steelId",steelIds.getSteelId());
|
//入库重量
|
Double changeStock = Double.valueOf(list.get(5).toString());
|
values.put("changeStock",changeStock);
|
//计量单位
|
//入库人
|
//SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
String userId = sysUserInfo.getUserId();
|
values.put("createUser",userId);
|
values.put("companyId",sysUserInfo.getCompanyId());
|
//入库重量
|
BigDecimal b1 = new BigDecimal(Double.toString(changeStock));
|
Double stocks = steelIds.getStock();
|
double stock =0;
|
if(stocks!=null && !stocks.equals("")&&!stocks.equals("0.0")&&Math.abs(stocks)>0.000000000001){
|
BigDecimal b2 = new BigDecimal(Double.toString(stocks));
|
stock = b1.add(b2).doubleValue();
|
}else {
|
stock=b1.doubleValue();
|
}
|
|
recordMapper.steelStockUpdate(steelIds.getSteelId(),stock);
|
values.put("id","");
|
recordMapper.recordInsert(values);
|
}
|
}
|
return BasicResult.success("导入成功");
|
}
|
|
@Override
|
public BasicResult steelStatistics(Map<String, Object> values) {
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
String companyId = sysUserInfo.getCompanyId();
|
values.put("companyId",companyId);
|
String dateType = values.get("dateType").toString();
|
List<SysSteel>steels = recordMapper.steelPull(values);
|
|
String strTime = values.get("strTime").toString();
|
String endTime = values.get("endTime").toString();
|
if(dateType.equals("1")){
|
List<String> betweenDate = getBetweenDate(strTime, endTime);
|
if(steels.size()>0){
|
for (SysSteel s :steels){
|
values.put("steelId",s.getSteelId());
|
List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordStatisticsDay(values);
|
s.setSteelStatisticsDtos(steelStatisticsDtos);
|
s.setBetweenDate(betweenDate);
|
}
|
}
|
//日
|
return BasicResult.success(steels);
|
}else if (dateType.equals("2")){//月
|
|
List<String> monthBetweenDate = getMonthBetweenDate(strTime, endTime);
|
if(steels.size()>0){
|
for (SysSteel s :steels){
|
values.put("steelId",s.getSteelId());
|
List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordStatisticsMonth(values);
|
s.setSteelStatisticsDtos(steelStatisticsDtos);
|
s.setBetweenDate(monthBetweenDate);
|
}
|
}
|
return BasicResult.success(steels);
|
}
|
return BasicResult.faild("11111","类型错误","error");
|
}
|
|
@Override
|
public BasicResult recordSum(Map<String, Object> values) {
|
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
String companyId = sysUserInfo.getCompanyId();
|
values.put("companyId",companyId);
|
String dateType = values.get("dateType").toString();
|
|
if(dateType.equals("1")){
|
//日
|
List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordSumDay(values);
|
return BasicResult.success(steelStatisticsDtos);
|
}else if (dateType.equals("2")){//月
|
List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordSumMonth(values);
|
return BasicResult.success(steelStatisticsDtos);
|
}
|
return BasicResult.faild("11111","类型错误","error");
|
}
|
|
@Override
|
public BasicResult recordStatisticsExcel(Map<String, Object> values,HttpServletResponse response) {
|
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
String companyId = sysUserInfo.getCompanyId();
|
values.put("companyId",companyId);
|
String dateType = values.get("dateType").toString();
|
List<SteelStatisticsExcel> steelStatisticsExcels = new ArrayList<>();
|
|
if(dateType.equals("1")){
|
//日
|
List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordStatisticsDay(values);
|
for (SteelStatisticsDto sd : steelStatisticsDtos){
|
SteelStatisticsExcel sse = new SteelStatisticsExcel();
|
sse.setSteelName(sd.getSteelName());
|
sse.setSteelModel(sd.getSteelModel());
|
sse.setGetDate(sd.getGetDate());
|
sse.setChangeStock(sd.getChangeStock());
|
steelStatisticsExcels.add(sse);
|
}
|
|
}else if (dateType.equals("2")){//月
|
List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordStatisticsMonth(values);
|
for (SteelStatisticsDto sd : steelStatisticsDtos){
|
SteelStatisticsExcel sse = new SteelStatisticsExcel();
|
sse.setSteelName(sd.getSteelName());
|
sse.setSteelModel(sd.getSteelModel());
|
sse.setGetDate(sd.getGetDate());
|
sse.setChangeStock(sd.getChangeStock());
|
steelStatisticsExcels.add(sse);
|
}
|
}
|
ExcelWriter excel = new ExcelWriter();//定义excel
|
excel.addHeaderAlias("steelName", "钢筋名称");
|
excel.addHeaderAlias("steelModel", "规格型号");
|
excel.addHeaderAlias("getDate", "日期");
|
excel.addHeaderAlias("changeStock", "数量");
|
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
|
response.setHeader("Content-Disposition", "attachment;filename=test.xls");
|
OutputStream out = null;
|
try {
|
out = response.getOutputStream();
|
excel.write(steelStatisticsExcels);
|
excel.flush(out, true);
|
} catch (IOException e) {
|
e.printStackTrace();
|
} finally {
|
// 关闭writer，释放内存
|
excel.close();
|
}
|
IoUtil.close(out);
|
return BasicResult.success();
|
}
|
|
@Override
|
public void recordOutExport(Map<String, Object> values, HttpServletResponse response) {
|
SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
|
String companyId = sysUserInfo.getCompanyId();
|
values.put("companyId",companyId);
|
List<SysSteelRecord> recordList = recordMapper.recordList(values);
|
// if (recordList.size()<=0){
|
// response.setStatus(400);
|
// return;
|
// }
|
List<RecordOutDto> recordDtos = new ArrayList<>();
|
|
for (SysSteelRecord steelRecord : recordList){
|
RecordOutDto recordDto = new RecordOutDto();
|
recordDto.setCreateTime(steelRecord.getCreateTime());
|
recordDto.setSteelName(steelRecord.getSteelName());
|
recordDto.setSteelModel(steelRecord.getSteelModel());
|
recordDto.setChangeStock(steelRecord.getChangeStock());
|
recordDto.setUnit(steelRecord.getUnit());
|
recordDto.setLineCode(steelRecord.getLineCode());
|
recordDto.setProName(steelRecord.getProName());
|
recordDtos.add(recordDto);
|
}
|
|
Integer stockType = Integer.valueOf(values.get("stockType").toString());
|
|
ExcelWriter excel = new ExcelWriter();//定义excel
|
if(stockType==1){
|
excel.addHeaderAlias("createTime", "入库时间");
|
}else {
|
excel.addHeaderAlias("createTime", "质检合格时间");
|
}
|
excel.addHeaderAlias("steelName", "钢筋名称");
|
excel.addHeaderAlias("steelModel", "规格型号");
|
excel.addHeaderAlias("unit", "计量单位");
|
|
if(stockType==1){
|
excel.addHeaderAlias("changeStock", "入库重量");
|
}else {
|
excel.addHeaderAlias("changeStock", "出库重量");
|
}
|
excel.addHeaderAlias("lineCode", "钢筋笼编号");
|
excel.addHeaderAlias("proName", "项目名称");
|
|
|
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
|
response.setHeader("Content-Disposition", "attachment;filename=test.xls");
|
OutputStream out = null;
|
try {
|
out = response.getOutputStream();
|
excel.write(recordDtos);
|
excel.flush(out, true);
|
} catch (IOException e) {
|
e.printStackTrace();
|
} finally {
|
// 关闭writer，释放内存
|
excel.close();
|
}
|
IoUtil.close(out);
|
}
|
|
public static List<String> getBetweenDate(String startTime, String endTime){
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
// 声明保存日期集合
|
List<String> list = new ArrayList<String>();
|
try {
|
// 转化成日期类型
|
Date startDate = sdf.parse(startTime);
|
Date endDate = sdf.parse(endTime);
|
|
//用Calendar 进行日期比较判断
|
Calendar calendar = Calendar.getInstance();
|
while (startDate.getTime()<=endDate.getTime()){
|
// 把日期添加到集合
|
list.add(sdf.format(startDate));
|
// 设置日期
|
calendar.setTime(startDate);
|
//把日期增加一天
|
calendar.add(Calendar.DATE, 1);
|
// 获取增加后的日期
|
startDate=calendar.getTime();
|
}
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
/**
|
* 获取两个日期之间的所有月份 (年月)
|
*
|
* @param startTime
|
* @param endTime
|
* @return：list
|
*/
|
public static List<String> getMonthBetweenDate(String startTime, String endTime) {
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
|
// 声明保存日期集合
|
List<String> list = new ArrayList<>();
|
try {
|
// 转化成日期类型
|
Date startDate = sdf.parse(startTime);
|
Date endDate = sdf.parse(endTime);
|
|
//用Calendar 进行日期比较判断
|
Calendar calendar = Calendar.getInstance();
|
while (startDate.getTime() <= endDate.getTime()) {
|
|
// 把日期添加到集合
|
list.add(sdf.format(startDate));
|
|
// 设置日期
|
calendar.setTime(startDate);
|
|
//把月数增加 1
|
calendar.add(Calendar.MONTH, 1);
|
|
// 获取增加后的日期
|
startDate = calendar.getTime();
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
|
/**
|
* 隐藏Sheet方式创建下拉框(单元格下拉框数据大于255字节时使用)
|
*
|
* @return
|
*/
|
public void createDropDownListWithHiddenSheet(Sheet sheet, int firstRow,
|
int firstCol, int endRow,
|
int endCol, String[] dataArray,
|
Workbook wbCreat,
|
String hidddenSheetName) {
|
Sheet hidden = wbCreat.createSheet(hidddenSheetName);
|
Cell cell = null;
|
for (int i = 0, length = dataArray.length; i < length; i++) {
|
String name = dataArray[i];
|
Row row = hidden.createRow(i);
|
cell = row.createCell(0);
|
cell.setCellValue(name);
|
}
|
Name namedCell = wbCreat.createName();
|
namedCell.setNameName(hidddenSheetName);
|
namedCell.setRefersToFormula(hidddenSheetName + "!$A$1:$A$" + dataArray.length);
|
//sheet设置为隐藏
|
wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);
|
//加载数据,将名称为hidden的
|
DataValidationConstraint constraint = null;
|
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
|
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
|
endCol);
|
// 创建 DataValidation
|
DataValidation validation = null;
|
if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
|
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
|
constraint = dvHelper.createFormulaListConstraint(hidddenSheetName);
|
validation = dvHelper.createValidation(constraint, addressList);
|
} else {
|
constraint = DVConstraint.createFormulaListConstraint(hidddenSheetName);
|
validation = new HSSFDataValidation(addressList, constraint);
|
}
|
if (validation instanceof HSSFDataValidation ) {
|
validation .setSuppressDropDownArrow(false);
|
} else {
|
validation .setSuppressDropDownArrow(true);
|
validation .setShowErrorBox(true);
|
}
|
sheet.addValidationData(validation);
|
}
|
|
|
|
|
}
|
