如下表格 导入 导出也这样：
导入代码 :
//导入 核心代码
@Override
public Map<String, Object> createBrandTml(String department, File excelData,String userName) {
// 根据部门不同，读取excel的内容栏位不同
Map<String, Object> resMap = new HashMap<String, Object>();
Workbook workBook = null;
try {
workBook = WorkbookFactory.create(new FileInputStream(excelData));
String message = "";
Sheet sheet = workBook.getSheetAt(0);
int allrow = sheet.getLastRowNum() + 1;
List<BrandShare> list = new ArrayList<BrandShare>();
List<BrandShareItem> itemList = new ArrayList<>();
for (int i = 3; i < allrow; i++) {
Row row = sheet.getRow(i);
if (row == null || StringUtils.isBlank(row.getCell(11).getStringCellValue())) {
break;
}
boolean isHebin = false;//是否合并
BrandShare brandShare = new BrandShare();
if (department.equals("1")) {
String uniqueValue = row.getCell(5) != null ? row.getCell(5).getStringCellValue() : "";
if (StringUtils.isEmpty(uniqueValue)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，物料号不能为空");
message += "第" + (i + 1) + "行，物料号不能为空";
}
String brandNo = row.getCell(7) != null ? row.getCell(7).getStringCellValue() : "";
if (StringUtils.isEmpty(brandNo)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，品名不能为空");
message += "第" + (i + 1) + "行，品名不能为空";
}
String xinghao = row.getCell(11) != null ? row.getCell(11).getStringCellValue() : "";
if (StringUtils.isEmpty(xinghao)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，面辅料型号不能为空");
message += "第" + (i + 1) + "行，面辅料型号不能为空";
}
if(StringUtils.isNotBlank(message)) {
resMap.put("msg", message);
return resMap;
}
int rowS = 1;
if(isMergedRegion(sheet, i, 5)) {//是否合并单元格 合并了几行
List<CellRangeAddress> combineCellList = getCombineCell(sheet);//获取所有合并的单元格
rowS = getRowSpan(combineCellList, i, 5);//物料号 跨了几行
isHebin = true;//是否合并
}
for(int j =0;j<rowS;j++) {//遍历面料型号
BrandShareItem bsItem = new BrandShareItem();
Row rowItem = sheet.getRow(i);
bsItem.setLiningModel(rowItem.getCell(11) != null ? rowItem.getCell(11).getStringCellValue() : "");
bsItem.setType(department);
bsItem.setBrandNo(brandNo);
bsItem.setCreateTime(new Date());
bsItem.setUniqueValue(uniqueValue);
itemList.add(bsItem);
//跨行的 下次忽略
i++;
}
Cell cel0 = row.getCell(0);// 名称
Double seq = cel0 != null ? cel0.getNumericCellValue() : 0;
int sq = seq.intValue();
brandShare.setSeq(sq);
String jj = row.getCell(1) != null ? row.getCell(1).getStringCellValue() : "";
brandShare.setSeason(jj);
String brand = row.getCell(2) != null ? row.getCell(2).getStringCellValue() : "";
String group = row.getCell(3) != null ? row.getCell(3).getStringCellValue() : "";
String series = row.getCell(4) != null ? row.getCell(4).getStringCellValue() : "";
uniqueValue = row.getCell(5) != null ? row.getCell(5).getStringCellValue() : "";
Date enteringTime = null;
try {
enteringTime = row.getCell(6) != null ? row.getCell(6).getDateCellValue() : null;
} catch (Exception e) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，计划录入时间格式错误");
message += "第" + (i + 1) + "行，计划录入时间格式错误";
return resMap;
}
String color = row.getCell(8) != null ? row.getCell(8).getStringCellValue() : "";
String level = row.getCell(9) != null ? row.getCell(9).getStringCellValue() : "";
String lining = row.getCell(10) != null ? row.getCell(10).getStringCellValue() : "";
String liningModel = row.getCell(11) != null ? row.getCell(11).getStringCellValue() : "";
String specSize = row.getCell(12) != null ? row.getCell(12).getStringCellValue() : "";
Double s = row.getCell(13) != null ? row.getCell(13).getNumericCellValue() : 0;
String sku = String.valueOf(s.intValue());
Date onlineTime = row.getCell(14) != null ? row.getCell(14).getDateCellValue() : null;
Date orderTime = row.getCell(15) != null ? row.getCell(15).getDateCellValue() : null;
String productManager = row.getCell(16) != null ? row.getCell(16).getStringCellValue() : "";
brandShare.setBrand(brand);
brandShare.setGroup(group);
brandShare.setSeries(series);
brandShare.setUniqueValue(uniqueValue);
brandShare.setEnteringTime(enteringTime);
brandShare.setBrandNo(brandNo);
brandShare.setColor(color);
brandShare.setLevel(level);
brandShare.setLining(lining);
brandShare.setLiningModel(liningModel);
brandShare.setSpecSize(specSize);
brandShare.setSku(sku);
brandShare.setOnlineTime(onlineTime);
brandShare.setOrderTime(orderTime);
brandShare.setProductManager(productManager);
list.add(brandShare);
} else if (department.equals("2")) {// 研发组
String uniqueValue = row.getCell(5) != null ? row.getCell(5).getStringCellValue() : "";
if (StringUtils.isEmpty(uniqueValue)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，物料号不能为空");
message += "第" + (i + 1) + "行，物料号不能为空";
}
String brandNo = row.getCell(7) != null ? row.getCell(7).getStringCellValue() : "";
if (StringUtils.isEmpty(brandNo)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，品名不能为空");
message += "第" + (i + 1) + "行，品名不能为空";
}
String xinghao = row.getCell(11) != null ? row.getCell(11).getStringCellValue() : "";
if (StringUtils.isEmpty(xinghao)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，面辅料型号不能为空");
message += "第" + (i + 1) + "行，面辅料型号不能为空";
}
if(StringUtils.isNotBlank(message)) {
resMap.put("msg", message);
return resMap;
}
int rowS = 1;
if(isMergedRegion(sheet, i, 5)) {//是否合并单元格 合并了几行
List<CellRangeAddress> combineCellList = getCombineCell(sheet);//获取所有合并的单元格
rowS = getRowSpan(combineCellList, i, 5);//物料号 跨了几行
isHebin = true;//是否合并
}
for(int j =0;j<rowS;j++) {//遍历面料型号
BrandShareItem bsItem = new BrandShareItem();
Row rowItem = sheet.getRow(i);
bsItem.setLiningModel(rowItem.getCell(11) != null ? rowItem.getCell(11).getStringCellValue() : "");
bsItem.setType(department);
bsItem.setBrandNo(brandNo);
bsItem.setUniqueValue(uniqueValue);
bsItem.setInspectTime(rowItem.getCell(17) != null ? rowItem.getCell(17).getDateCellValue() : null);
bsItem.setIsOk(rowItem.getCell(18) != null ? rowItem.getCell(18).getStringCellValue() : "");
bsItem.setNextStepTime(rowItem.getCell(19) != null ? rowItem.getCell(19).getDateCellValue() : null);
bsItem.setPlanTime(rowItem.getCell(20) != null ? rowItem.getCell(20).getDateCellValue() : null);
bsItem.setUploadTime(row.getCell(21) != null ? row.getCell(21).getDateCellValue() : null);
bsItem.setUpload2Time(row.getCell(22) != null ? row.getCell(22).getDateCellValue() : null);
bsItem.setDesigner(row.getCell(23) != null ? row.getCell(23).getStringCellValue() : "");
itemList.add(bsItem);
//跨行的 下次忽略
i++;
}
//合并单元格
Date uploadTime = row.getCell(21) != null ? row.getCell(21).getDateCellValue() : null;
Date upload2Time = row.getCell(22) != null ? row.getCell(22).getDateCellValue() : null;
String designer = row.getCell(23) != null ? row.getCell(23).getStringCellValue() : "";
brandShare.setDesigner(designer);
brandShare.setUniqueValue(uniqueValue);
brandShare.setBrandNo(brandNo);
brandShare.setUploadTime(uploadTime);
brandShare.setUpload2Time(upload2Time);
list.add(brandShare);
} else if (department.equals("3")) {// 面料开发组
String uniqueValue = row.getCell(5) != null ? row.getCell(5).getStringCellValue() : "";
if (StringUtils.isEmpty(uniqueValue)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，物料号不能为空");
message += "第" + (i + 1) + "行，物料号不能为空";
}
String brandNo = row.getCell(7) != null ? row.getCell(7).getStringCellValue() : "";
if (StringUtils.isEmpty(brandNo)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，品名不能为空");
message += "第" + (i + 1) + "行，品名不能为空";
}
String xinghao = row.getCell(11) != null ? row.getCell(11).getStringCellValue() : "";
if (StringUtils.isEmpty(xinghao)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，面辅料型号不能为空");
message += "第" + (i + 1) + "行，面辅料型号不能为空";
}
if(StringUtils.isNotBlank(message)) {
resMap.put("msg", message);
return resMap;
}
int rowS = 1;
if(isMergedRegion(sheet, i, 5)) {//是否合并单元格 合并了几行
List<CellRangeAddress> combineCellList = getCombineCell(sheet);//获取所有合并的单元格
rowS = getRowSpan(combineCellList, i, 5);//物料号 跨了几行
isHebin = true;//是否合并
}
for(int j =0;j<rowS;j++) {//遍历面料型号
BrandShareItem bsItem = new BrandShareItem();
Row rowItem = sheet.getRow(i);
bsItem.setLiningModel(rowItem.getCell(11) != null ? rowItem.getCell(11).getStringCellValue() : "");
bsItem.setType(department);
bsItem.setBrandNo(brandNo);
bsItem.setUniqueValue(uniqueValue);
bsItem.setLiningInspectTime(rowItem.getCell(24) != null ? rowItem.getCell(24).getDateCellValue() : null);
bsItem.setLiningQualifiedTime(rowItem.getCell(25) != null ? rowItem.getCell(25).getDateCellValue() : null);
bsItem.setLiningNextTime(rowItem.getCell(26) != null ? rowItem.getCell(26).getDateCellValue() : null);
bsItem.setLiningRemark( rowItem.getCell(27) != null ? rowItem.getCell(27).getStringCellValue() : null);
itemList.add(bsItem);
//跨行的 下次忽略
i++;
}
//brandShare.setUniqueValue(uniqueValue);
//brandShare.setBrandNo(brandNo);
//list.add(brandShare);
} else if (department.equals("4")) {// 采购供应
String uniqueValue = row.getCell(5) != null ? row.getCell(5).getStringCellValue() : "";
if (StringUtils.isEmpty(uniqueValue)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，物料号不能为空");
message += "第" + (i + 1) + "行，物料号不能为空";
}
String brandNo = row.getCell(7) != null ? row.getCell(7).getStringCellValue() : "";
if (StringUtils.isEmpty(brandNo)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，品名不能为空");
message += "第" + (i + 1) + "行，品名不能为空";
}
String xinghao = row.getCell(11) != null ? row.getCell(11).getStringCellValue() : "";
if (StringUtils.isEmpty(xinghao)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，面辅料型号不能为空");
message += "第" + (i + 1) + "行，面辅料型号不能为空";
}
if(StringUtils.isNotBlank(message)) {
resMap.put("msg", message);
return resMap;
}
int rowS = 1;
if(isMergedRegion(sheet, i, 5)) {//是否合并单元格 合并了几行
List<CellRangeAddress> combineCellList = getCombineCell(sheet);//获取所有合并的单元格
rowS = getRowSpan(combineCellList, i, 5);//物料号 跨了几行
isHebin = true;//是否合并
}
for(int j =0;j<rowS;j++) {//遍历面料型号
BrandShareItem bsItem = new BrandShareItem();
Row rowItem = sheet.getRow(i);
bsItem.setLiningModel(rowItem.getCell(11) != null ? rowItem.getCell(11).getStringCellValue() : "");
bsItem.setType(department);
bsItem.setBrandNo(brandNo);
bsItem.setUniqueValue(uniqueValue);
bsItem.setPurchaseEnterTime(rowItem.getCell(28) != null ? rowItem.getCell(28).getDateCellValue() : null);
bsItem.setProvider(rowItem.getCell(29) != null ? rowItem.getCell(29).getStringCellValue() : null);
bsItem.setSundryStyle(rowItem.getCell(30) != null ? rowItem.getCell(30).getStringCellValue() : null);
itemList.add(bsItem);
//跨行的 下次忽略
i++;
}
brandShare.setUniqueValue(uniqueValue);
brandShare.setBrandNo(brandNo);
brandShare.setProviderRemark(row.getCell(31) != null ? row.getCell(31).getStringCellValue() : null);
list.add(brandShare);
} else if (department.equals("5")) {// 商品计划
String uniqueValue = row.getCell(5) != null ? row.getCell(5).getStringCellValue() : "";
if (StringUtils.isEmpty(uniqueValue)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，物料号不能为空");
message += "第" + (i + 1) + "行，物料号不能为空";
}
String brandNo = row.getCell(7) != null ? row.getCell(7).getStringCellValue() : "";
if (StringUtils.isEmpty(brandNo)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，品名不能为空");
message += "第" + (i + 1) + "行，品名不能为空";
}
String xinghao = row.getCell(11) != null ? row.getCell(11).getStringCellValue() : "";
if (StringUtils.isEmpty(xinghao)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，面辅料型号不能为空");
message += "第" + (i + 1) + "行，面辅料型号不能为空";
}
if(StringUtils.isNotBlank(message)) {
resMap.put("msg", message);
return resMap;
}
int rowS = 1;
if(isMergedRegion(sheet, i, 5)) {//是否合并单元格 合并了几行
List<CellRangeAddress> combineCellList = getCombineCell(sheet);//获取所有合并的单元格
rowS = getRowSpan(combineCellList, i, 5);//物料号 跨了几行
isHebin = true;//是否合并
}
//for(int j =0;j<rowS;j++) {//遍历面料型号
//BrandShareItem bsItem = new BrandShareItem();
//Row rowItem = sheet.getRow(i);
//itemList.add(bsItem);
////跨行的 下次忽略
//i++;
//}
i=i+rowS;//这个部门全更新主表 跳
Date gpmSubTime = row.getCell(32) != null ? row.getCell(32).getDateCellValue() : null;
Date gpmPushTime = row.getCell(33) != null ? row.getCell(33).getDateCellValue() : null;
Date gpmNeedTime = row.getCell(34) != null ? row.getCell(34).getDateCellValue() : null;
brandShare.setGpmSubTime(gpmSubTime);
brandShare.setGpmPushTime(gpmPushTime);
brandShare.setGpmNeedTime(gpmNeedTime);
brandShare.setUniqueValue(uniqueValue);
brandShare.setBrandNo(brandNo);
list.add(brandShare);
} else if (department.equals("6")) {// 生产及物料计划
String uniqueValue = row.getCell(5) != null ? row.getCell(5).getStringCellValue() : "";
if (StringUtils.isEmpty(uniqueValue)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，物料号不能为空");
message += "第" + (i + 1) + "行，物料号不能为空";
}
String brandNo = row.getCell(7) != null ? row.getCell(7).getStringCellValue() : "";
if (StringUtils.isEmpty(brandNo)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，品名不能为空");
message += "第" + (i + 1) + "行，品名不能为空";
}
String xinghao = row.getCell(11) != null ? row.getCell(11).getStringCellValue() : "";
if (StringUtils.isEmpty(xinghao)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，面辅料型号不能为空");
message += "第" + (i + 1) + "行，面辅料型号不能为空";
}
if(StringUtils.isNotBlank(message)) {
resMap.put("msg", message);
return resMap;
}
int rowS = 1;
if(isMergedRegion(sheet, i, 5)) {//是否合并单元格 合并了几行
List<CellRangeAddress> combineCellList = getCombineCell(sheet);//获取所有合并的单元格
rowS = getRowSpan(combineCellList, i, 5);//物料号 跨了几行
isHebin = true;//是否合并
}
for(int j =0;j<rowS;j++) {//遍历面料型号
BrandShareItem bsItem = new BrandShareItem();
Row rowItem = sheet.getRow(i);
bsItem.setLiningModel(rowItem.getCell(11) != null ? rowItem.getCell(11).getStringCellValue() : "");
bsItem.setType(department);
bsItem.setBrandNo(brandNo);
bsItem.setUniqueValue(uniqueValue);
bsItem.setProMeter(rowItem.getCell(35) != null ? rowItem.getCell(35).getStringCellValue() : null);
bsItem.setProOrderTime(rowItem.getCell(36) != null ? rowItem.getCell(36).getDateCellValue() : null);
itemList.add(bsItem);
//跨行的 下次忽略
i++;
}
Date proFullTime = row.getCell(37) != null ? row.getCell(37).getDateCellValue() : null;
Date proConfirmTime = row.getCell(38) != null ? row.getCell(38).getDateCellValue() : null;
Date proAppleTime = row.getCell(39) != null ? row.getCell(39).getDateCellValue() : null;
String proRemark = row.getCell(40) != null ? row.getCell(40).getStringCellValue() : null;
brandShare.setProFullTime(proFullTime);
brandShare.setProConfirmTime(proConfirmTime);
brandShare.setProAppleTime(proAppleTime);
brandShare.setProRemark(proRemark);
brandShare.setUniqueValue(uniqueValue);
brandShare.setBrandNo(brandNo);
list.add(brandShare);
} else if (department.equals("7")) {// 技术部
String uniqueValue = row.getCell(5) != null ? row.getCell(5).getStringCellValue() : "";
if (StringUtils.isEmpty(uniqueValue)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，物料号不能为空");
message += "第" + (i + 1) + "行，物料号不能为空";
}
String brandNo = row.getCell(7) != null ? row.getCell(7).getStringCellValue() : "";
if (StringUtils.isEmpty(brandNo)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，品名不能为空");
message += "第" + (i + 1) + "行，品名不能为空";
}
String xinghao = row.getCell(11) != null ? row.getCell(11).getStringCellValue() : "";
if (StringUtils.isEmpty(xinghao)) {
resMap.put("status", "false");
resMap.put("msg", "第" + (i + 1) + "行，面辅料型号不能为空");
message += "第" + (i + 1) + "行，面辅料型号不能为空";
}
if(StringUtils.isNotBlank(message)) {
resMap.put("msg", message);
return resMap;
}
int rowS = 1;
if(isMergedRegion(sheet, i, 5)) {//是否合并单元格 合并了几行
List<CellRangeAddress> combineCellList = getCombineCell(sheet);//获取所有合并的单元格
rowS = getRowSpan(combineCellList, i, 5);//物料号 跨了几行
isHebin = true;//是否合并
}
//for(int j =0;j<rowS;j++) {//遍历面料型号
//BrandShareItem bsItem = new BrandShareItem();
//Row rowItem = sheet.getRow(i);
//itemList.add(bsItem);
////跨行的 下次忽略
//i++;
//}
i=i+rowS;//这个部门全更新主表
String tecStyle = row.getCell(41) != null ? row.getCell(41).getStringCellValue() : null;
String tecCraft = row.getCell(42) != null ? row.getCell(42).getStringCellValue() : null;
Date tecCheckTime = row.getCell(43) != null ? row.getCell(43).getDateCellValue() : null;
Date tecSampleTime = row.getCell(44) != null ? row.getCell(44).getDateCellValue() : null;
Date tecUploadTime = row.getCell(45) != null ? row.getCell(45).getDateCellValue() : null;
String tecIsFull = row.getCell(46) != null ? row.getCell(46).getStringCellValue() : null;
String tecCraftPerson = row.getCell(47) != null ? row.getCell(47).getStringCellValue() : null;
String tecIsDraw = row.getCell(48) != null ? row.getCell(48).getStringCellValue() : null;
Date tecCraftOverTime = row.getCell(49) != null ? row.getCell(49).getDateCellValue() : null;
Date tecBomOverTime = row.getCell(50) != null ? row.getCell(50).getDateCellValue() : null;
Date tecBomOver2Time = row.getCell(51) != null ? row.getCell(51).getDateCellValue() : null;
String tecUnfinishReason = row.getCell(52) != null ? row.getCell(52).getStringCellValue() : null;
Date tecCostOverTime = row.getCell(53) != null ? row.getCell(53).getDateCellValue() : null;
Date tecSubmitTime = row.getCell(54) != null ? row.getCell(54).getDateCellValue() : null;
int tecOverDay = row.getCell(55) != null ? (int) row.getCell(55).getNumericCellValue() : 0;
String tecUnfinishReason2 = row.getCell(56) != null ? row.getCell(56).getStringCellValue() : null;
Date tecActiveNeedTime = row.getCell(57) != null ? row.getCell(57).getDateCellValue() : null;
Date tecActiveOverTime = row.getCell(58) != null ? row.getCell(58).getDateCellValue() : null;
String tecUnfinishReason3 = row.getCell(59) != null ? row.getCell(59).getStringCellValue() : null;
String tecProblem = row.getCell(60) != null ? row.getCell(60).getStringCellValue() : null;
Date tecMeetingTime = row.getCell(61) != null ? row.getCell(61).getDateCellValue() : null;
Date tecOverMettingTime = row.getCell(62) != null ? row.getCell(62).getDateCellValue() : null;
brandShare.setBrandNo(brandNo);
brandShare.setTecStyle(tecStyle);
brandShare.setTecCraft(tecCraft);
brandShare.setTecCheckTime(tecCheckTime);
brandShare.setTecSampleTime(tecSampleTime);
brandShare.setTecUploadTime(tecUploadTime);
brandShare.setTecIsFull(tecIsFull);
brandShare.setTecCraftPerson(tecCraftPerson);
brandShare.setTecIsDraw(tecIsDraw);
brandShare.setTecCraftOverTime(tecCraftOverTime);
brandShare.setTecBomOverTime(tecBomOverTime);
brandShare.setTecBomOver2Time(tecBomOver2Time);
brandShare.setTecUnfinishReason(tecUnfinishReason);
brandShare.setTecCostOverTime(tecCostOverTime);
brandShare.setTecSubmitTime(tecSubmitTime);
brandShare.setTecOverDay(tecOverDay);
brandShare.setTecUnfinishReason2(tecUnfinishReason2);
brandShare.setTecActiveNeedTime(tecActiveNeedTime);
brandShare.setTecActiveOverTime(tecActiveOverTime);
brandShare.setTecUnfinishReason3(tecUnfinishReason3);
brandShare.setTecProblem(tecProblem);
brandShare.setTecMeetingTime(tecMeetingTime);
brandShare.setTecOverMettingTime(tecOverMettingTime);
list.add(brandShare);
} else {
resMap.put("status", "fail");
resMap.put("msg", "未找到该部门：" + department);
return resMap;
}
if(isHebin) {//如果有合并单元格 回头减1 下次循环会加上
i--;
}
}
if (department.equals("1")) { // 商品企划部 初始数据 增加，其他部门不能修改
List<BrandShare> newList = new ArrayList<BrandShare>(); // 表格中各部门追加数据，基础数据同一条，合并基础信息
Set<String> set = new HashSet<String>();
for (BrandShare brandShare : list) {
String brandNo = brandShare.getBrandNo();
if (StringUtils.isNotEmpty(brandNo) && !set.contains(brandNo)) { // set中不包含重复的
set.add(brandNo);
newList.add(brandShare);
}
}
for (BrandShare bs : newList) {
//检测为空插入
brandShareDao.addBrandShareInfoCheckIsNull(bs);
}
//if(list.size()>0) {
//需要特殊处理 可能update为空
//brandShareDao.updateBatch(list);//更新主表
//}
if(null != itemList && itemList.size()>0) {
for (BrandShareItem item : itemList) {
brandShareItemDao.insertCheckExists(item);
}
}
}
if (department.equals("2") || department.equals("3") || department.equals("4") || department.equals("6")) {
if(null != itemList && itemList.size()>0) {
for (BrandShareItem item : itemList) {
brandShareItemDao.updateItemInfo(item);//更新信息
}
}
if(list.size()>0) {
brandShareDao.updateBatch(list);//更新主表
}
}
if (department.equals("5") || department.equals("7")) {
brandShareDao.updateBatch(list);//只更新主表
}
//insert log
if (list.size() > 0) {
BrandShareLog log = new BrandShareLog();
log.setOperator(userName);
log.setOpt_department(BrandShare.getDepartmentName(department));
log.setCreate_time(new Date());
for (BrandShare bs : list) {
log.setOperation_type("brandShare");
log.setOperation_info(JSONObject.fromObject(bs).toString());
brandShareLogDao.insertBrandShareLog(log);
}
}
if (itemList.size()>0) {
BrandShareLog log = new BrandShareLog();
log.setOperator(userName);
log.setOpt_department(BrandShare.getDepartmentName(department));
log.setCreate_time(new Date());
for (BrandShareItem item : itemList) {
log.setOperation_type("brandShareItem");
log.setOperation_info(JSONObject.fromObject(item).toString());
brandShareLogDao.insertBrandShareLog(log);
}
}
} catch (Exception e) {
e.printStackTrace();
resMap.put("status", "error");
resMap.put("msg", e.getMessage());
return resMap;
}
resMap.put("status", "success");
resMap.put("msg", "操作成功");
return resMap;
}
/**
* 获取cell跨的列数
* @param list
* @param row
* @param column
* @return
*/
public static int getColSpan(List<CellRangeAddress> list,int row ,int column) {
int colSpan = 1;
// List<CellRangeAddress> list = sheet.getMergedRegions();
for (CellRangeAddress cellRangeAddress : list) {
if (cellRangeAddress.isInRange(row, column)) {
colSpan = cellRangeAddress.getLastColumn() - cellRangeAddress.getFirstColumn() + 1; // +1是因为如果没跨，就算1
break;
}
}
return colSpan;
}
/**
* 获取cell跨的行数
* @param cell
* @param sheet
* @return
*/
public static int getRowSpan(List<CellRangeAddress> list,int row ,int column) {
int rowSpan = 1;
// List<CellRangeAddress> list = sheet.getMergedRegions();
for (CellRangeAddress cellRangeAddress : list) {
if (cellRangeAddress.isInRange(row, column)) {
rowSpan = cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() + 1; // +1是因为如果没跨，就算1
break;
}
}
return rowSpan;
}
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private boolean isMergedRegion(Sheet sheet,int row ,int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return true;
}
}
}
return false;
}
/**
* 获取sheet中合并的单元格个数，并返回单元格list
* @param sheet
* @return List<CellRangeAddress>
*/
public List<CellRangeAddress> getCombineCell(Sheet sheet)
{
List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
//获得一个 sheet 中合并单元格的数量
int sheetmergerCount = sheet.getNumMergedRegions();
//遍历所有的合并单元格
for(int i = 0; i<sheetmergerCount;i++)
{
//获得合并单元格保存进list中
CellRangeAddress ca = sheet.getMergedRegion(i);
list.add(ca);
}
return list;
}
//MMMMMMMMMMMMMMMMMMMMMMMMMMMMMM
//导出 核心代码
/**
* 导出 处理数据 合并单元格等操作
* @return
*/
public String exprotExcuteInfo() {
Map<String, Object> parms = new HashMap<String, Object>();
if (StringUtils.isNotBlank(brandName)) {
parms.put("brandName", brandName);
}
if (StringUtils.isNotBlank(proName)) {
parms.put("brandNo", proName);
}
if (StringUtils.isNotBlank(uniqueValue)) {
parms.put("uniqueValue", uniqueValue);
}
if (StringUtils.isNotBlank(liningModel)) {
parms.put("liningModel", liningModel);
}
pageList.setPageNo(this.page);
pageList.setPageSize(this.rows);
pageList = brandShareService.listPage(pageList, parms);
//写入数据
try {
//读取模板路径 加载到流
String realpath = ServletActionContext.getServletContext().getRealPath("/");
Workbook workbook = null; //WorkbookFactory.create(new FileInputStream(realpath + "/templates/excel/exportBrandShareHebing.xlsx"));
XLSTransformer transformer = new XLSTransformer();
Map<String, Object> map = new HashMap<String, Object>();
map = new HashMap<String, Object>();
map.put("root", pageList.getResult());
//先 按一行一行导出 然后合并
workbook = transformer.transformXLS(new FileInputStream(realpath + "/templates/excel/exportBrandShare.xlsx"),map);
Sheet sheet1 = workbook.getSheetAt(0);//获取第一个sheet
//合并
if(pageList.getResult() != null && pageList.getResult().size() > 0) {
int total = 0;
int seqNum = 0;//序列号
for (int i = 3;i<(pageList.getResult().size()+3);i++) {
Row row = sheet1.getRow(i);//获取指定行开始
int liningModelNum = 0;//物料型号有几个
String currUniqueValue = "";//当前物料型号
int newForJ = 0;
seqNum++;
row.getCell(0).setCellValue(seqNum);//赋值 指定单元格的值
for(int j = total;j<(pageList.getResult().size()+3);j++) {
if(currUniqueValue.equals("")) {//不等于空 开始
currUniqueValue = pageList.getResult().get(j).get("unique_value").toString();
liningModelNum++;
}
if(pageList.getResult().size() > j && currUniqueValue.equals(pageList.getResult().get(j).get("unique_value").toString()) && j != 0 && newForJ!=0) {//第一行不比较
liningModelNum++;
}
newForJ =1;
//本次物料号 不等于上一个物料号 跳槽循环下一个物料号
if(j == pageList.getResult().size() || !currUniqueValue.equals(pageList.getResult().get(j).get("unique_value").toString())) {//最后一行
if(liningModelNum > 1) {//合并单元格
//1
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 0, 0);//起始行,结束行,起始列,结束列 季节
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 1, 1);//起始行,结束行,起始列,结束列 季节
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 2, 2);//起始行,结束行,起始列,结束列 品牌
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 3, 3);//起始行,结束行,起始列,结束列 接单组
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 4, 4);//起始行,结束行,起始列,结束列 系列
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 5, 5);//起始行,结束行,起始列,结束列 物料号
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 6, 6);//起始行,结束行,起始列,结束列 计划录入时间
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 7, 7);//起始行,结束行,起始列,结束列 品名
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 8, 8);//起始行,结束行,起始列,结束列 颜色
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 9, 9);//起始行,结束行,起始列,结束列 档次
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 10, 10);//起始行,结束行,起始列,结束列 面料
//sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 11, 11);//起始行,结束行,起始列,结束列 面辅料型号
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 12, 12);//起始行,结束行,起始列,结束列 规格
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 13, 13);//起始行,结束行,起始列,结束列 sku数量
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 14, 14);//起始行,结束行,起始列,结束列 事业部上市时间
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 15, 15);//起始行,结束行,起始列,结束列 企划下单表时间
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 16, 16);//起始行,结束行,起始列,结束列 产品经理
// TODO ...
.......
sheet1= hebingCell(sheet1, i, (i+liningModelNum-1), 62, 62);//起始行,结束行,起始列,结束列
i+=(liningModelNum-1);
}
total=j;
break;
}
}
}
}
String contentType = "application/vnd.ms-excel";
response.setContentType(contentType);
response.setHeader("Content-Disposition", "attachment;filename="+getDownloadFileName("品牌共享查询导出模板.xlsx"));
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 合并单元格
* @param sheet1
* @param firstRow 起始行
* @param lastRow 结束行
* @param firstCol 起始列
* @param lastCol 结束列
* @return
*/
private Sheet hebingCell(Sheet sheet1,int firstRow, int lastRow, int firstCol, int lastCol) {
//合并单元格 起始
CellRangeAddress raddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);//起始行,结束行,起始列,结束列
sheet1.addMergedRegion(raddress);
return sheet1;
}
