ApiOperation(value = "通过excel导入数据", notes = "通过excel导入数据")
@RequestMapping(value = "/importExcelNew", method = RequestMethod.POST)
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public Result<?> importExcelNew(HttpServletRequest request, HttpServletResponse response) throws IOException, ParseException {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
List<Category> temp = new ArrayList();
ArrayList<Integer> rowsNum = new ArrayList<>();
ArrayList<Integer> rowsNumZC = new ArrayList<>();
HashMap<String, Integer> categoryMap = new HashMap<>();
HashMap<String, String> locationNameMapList = ExcelTemplateUtil.getLocationNameMapList(inStockService);
for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
MultipartFile file = entity.getValue();
InputStream inputStream = file.getInputStream();
HSSFWorkbook sheets = ExcelTemplateUtil.getimportExcel(inputStream);
Sheet rows = sheets.getSheetAt(0);
for (Row row : rows) {
if (row.getRowNum() == 0) {
Cell cell = row.getCell(0);
String stringCellValue = cell.getStringCellValue();
if ("xxxxx信息".equals(stringCellValue) || "Inbound Material Details".equals(stringCellValue)) {
continue;
}
return Result.error("导入的模板不符合,请下载模板重新导入!");
}
}
for (Row r : rows) {
if (r.getRowNum() <= 2) {
continue;
}
String key = r.getCell(0).getStringCellValue();
if (categoryMap.containsKey(key)){
Integer num = categoryMap.get(key);
categoryMap.put(key,num+1);
}else {
categoryMap.put(key,1);
}
}
boolean flagAllData = false;
boolean flag = false;
Cell cell = null;
for (Row r : rows) {
flag = false;
String errMsg = "";
if (r.getRowNum() == 2) {
cell = r.createCell(7);
cell.setCellValue("错误原因");
continue;
}
if (r.getRowNum() <= 2) {
continue;
}
if (ExcelTemplateUtil.isRowEmpty(r)) {
continue;
}
Category category = new Category();
cell = r.getCell(0);
String key = cell.getStringCellValue();
if (categoryMap.get(key)>1){
log.error("导入数据存在相同数据");
errMsg += "导入数据存在相同数据";
flag = true;
flagAllData = true;
}
if (Objects.nonNull(cell) && Objects.nonNull(cell.getStringCellValue()) && !("".equals(cell.getStringCellValue()))) {
cell.setCellType(CellType.STRING);
Category category1 = categoryMapper.selectByCode(cell.getStringCellValue().trim());
if (category1 == null) {
log.error("物料编码查询不到对应信息;");
errMsg += "物料编码查询不到对应信息;";
flag = true;
flagAllData = true;
}else{
category.setCategoryId(category1.getId());
category.setCode(category1.getCode());
category.setTeileNumber(category1.getTeileNumber());
category.setEngName(category1.getEngName());
category.setChsName(category1.getChsName());
category.setPosNumber(category1.getPosNumber());
category.setMaterial(category1.getMaterial());
category.setIsValuables(category1.getIsValuables());
category.setTeileSet(category1.getTeileSet());
}
} else {
log.error("物料编码不允许为空;");
errMsg += "物料编码不允许为空;";
flag = true;
flagAllData = true;
}
cell = r.getCell(1);
if (Objects.nonNull(cell) && Objects.nonNull(cell.getStringCellValue()) && !("".equals(cell.getStringCellValue()))) {
cell.setCellType(CellType.STRING);
category.setLocationName(cell.getStringCellValue());
category.setLocationId(locationNameMapList.get(cell.getStringCellValue()));
} else {
log.error("货位不允许为空;");
errMsg += "货位不允许为空;";
flag = true;
flagAllData = true;
}
cell = r.getCell(2);
if (Objects.nonNull(cell) && Objects.nonNull(cell.getNumericCellValue())) {
cell.setCellType(CellType.NUMERIC);
category.setQuantity((int) cell.getNumericCellValue());
}
cell = r.getCell(3);
if (Objects.nonNull(cell) && Objects.nonNull(cell.getNumericCellValue())) {
cell.setCellType(CellType.NUMERIC);
category.setPrice(BigDecimal.valueOf(cell.getNumericCellValue()).setScale(2, RoundingMode.HALF_UP));
}
cell = r.getCell(4);
if (Objects.nonNull(cell) && Objects.nonNull(cell.getStringCellValue()) && !("".equals(cell.getStringCellValue()))) {
cell.setCellType(CellType.STRING);
category.setPurchaseType_dictText(cell.getStringCellValue());
category.setPurchaseType(purchaseType.get(cell.getStringCellValue()));
}
cell = r.getCell(5);
if (Objects.nonNull(cell) && Objects.nonNull(cell.getStringCellValue()) && !("".equals(cell.getStringCellValue()))) {
cell.setCellType(CellType.STRING);
category.setSupplierName(cell.getStringCellValue());
category.setSupplierId(supplierMapList.get(cell.getStringCellValue()));
}
cell = r.getCell(6);
if (Objects.nonNull(cell) && Objects.nonNull(getStringCellValue(cell))) {
try {
String date = getStringCellValue(cell);
SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd");
Date parse = sim.parse(date);
category.setProductDate(parse);
} catch (Exception e) {
log.error("生产时间格式错误;");
errMsg += "生产时间格式错误;";
flag = true;
flagAllData = true;
}
}
category.setTeileSet(teileSetMap == null ? "" : teileSetMap.get(category.getTeileSet()));
category.setMaterial_dictText(materialMap == null ? "" : materialMap.get(category.getMaterial()));
Integer count = inventoryMapper.selectCountByCategoryId(category.getId()).intValue();
category.setInventoryNum(count);
Integer countExist = inventoryMapper.selectExistCountByCategoryId(category.getId()).intValue();
category.setExistQuantity(countExist);
cell = r.createCell(7);
cell.setCellValue(errMsg);
if (!flag) {
temp.add(category);
rowsNum.add(r.getRowNum() );
}else{
rowsNumZC.add(r.getRowNum());
}
}
inputStream.close();
boolean b = categoryService.saveBatch(temp);
if (flagAllData) {
if (Objects.nonNull(rowsNum)) {
for (Integer integer : rowsNum) {
rows.removeRow(rows.getRow(integer));
}
for (Integer integeryd : rowsNumZC) {
int yd = 0;
for (Integer integer : rowsNum) {
if(integer>integeryd){
break;
}
yd++;
}
rows.shiftRows(integeryd,integeryd,-yd);
}
}
String fileName = "InStockImportExcel_" + System.currentTimeMillis() + ".xls";
sheets.write(new File(uploadpath + File.separator + fileName));
return Result.error(500,"导入失败", fileName);
}
}
return Result.OK("导入成功", temp);
}
package org.jeecg.modules.util;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.system.api.ISysBaseAPI;
import org.jeecg.common.system.vo.DictModel;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.modules.system.entity.SysDepart;
import org.jeecg.modules.system.entity.SysDictItem;
import org.jeecg.modules.system.service.impl.SysDepartServiceImpl;
import org.jeecg.modules.teilemgmt.entity.Location;
import org.jeecg.modules.teilemgmt.entity.Supplier;
import org.jeecg.modules.teilemgmt.service.IInStockService;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.*;
public class ExcelTemplateUtil {
public static HSSFWorkbook CreateWorkbook(){
HSSFWorkbook wb = new HSSFWorkbook();
return wb;
}
public static HSSFSheet CreateTemplateTitle(HSSFWorkbook wb, String sheetName, String titleName, Integer fixedCol, String warn) {
HSSFSheet sheet = wb.createSheet(sheetName);
HSSFRow row1 = sheet.createRow(0);
HSSFCell cell = row1.createCell(0);
HSSFRow row2 = sheet.createRow(1);
HSSFCell cell1 = row2.createCell(0);
cell1.setCellValue(warn);
HSSFCellStyle Style = wb.createCellStyle();
HSSFFont font2 = wb.createFont();
font2.setFontHeightInPoints((short) 15);
Style.setFont(font2);
cell1.setCellStyle(Style);
row2.setHeightInPoints(20);
row1.setHeightInPoints(60);
cell.setCellValue(titleName);
HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 25);
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,fixedCol));
sheet.addMergedRegion(new CellRangeAddress(1,1,0,fixedCol));
return sheet;
}
public static void CreateProperty(HSSFWorkbook wb, HSSFSheet sheet, List<String> param) {
HSSFRow row2 = sheet.createRow(2);
HSSFCellStyle cellStyle2 = wb.createCellStyle();
HSSFFont font2 = wb.createFont();
font2.setFontHeightInPoints((short) 10);
cellStyle2.setFont(font2);
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle2.setFillForegroundColor(HSSFColor.GOLD.index);
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
for (int i=0;i<param.size();i++){
row2.createCell(i).setCellValue(param.get(i));
row2.getCell(i).setCellStyle(cellStyle2);
}
}
public static void CreateFile(HSSFWorkbook wb,HttpServletResponse response) throws IOException {
OutputStream output=response.getOutputStream();
response.reset();
response.setHeader("Access-control-Allow-Origin", ("*"));
response.setHeader("Access-Control-Allow-Methods", "GET,POST,OPTIONS,PUT,DELETE");
response.setHeader("Access-Control-Allow-Credentials", "true");
response.setHeader("Content-disposition", "attachment; filename=details.xls");
response.setContentType("application/msexcel");
wb.write(output);
output.close();
}
public static HSSFSheet setHSSFValidation(HSSFWorkbook wb, HSSFSheet sheet, String namebook,
String[] textlist, int firstRow, int endRow, int firstCol,
int endCol, Integer index) {
HSSFSheet hidden = wb.createSheet(namebook);
wb.setSheetHidden(index, true);
HSSFCell cell = null;
for (int i = 0, length = textlist.length; i < length; i++) {
String name = textlist[i];
HSSFRow row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
cell.setCellType(Cell.CELL_TYPE_STRING);
}
Name namedCell = wb.createName();
namedCell.setNameName(namebook);
namedCell.setRefersToFormula(namebook+"!$A$1:$A$" + textlist.length);
DVConstraint constraint = DVConstraint.createFormulaListConstraint(namebook);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,endCol);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
if (null != validation) {
sheet.addValidationData(validation);
}
return sheet;
}
public static void setTesxtType(HSSFWorkbook wb, HSSFSheet sheet, Integer Col) {
CellStyle css = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
css.setDataFormat(format.getFormat("@"));
sheet.setDefaultColumnStyle(Col, css);
}
public static String[] getSysOrg(SysDepartServiceImpl sysDepartService, ISysBaseAPI sysBaseAPI, HttpServletRequest request) {
List<SysDepart> list5 = sysDepartService.list();
ArrayList<String> strings = new ArrayList<>();
ArrayList<String> stringName = new ArrayList<>();
for (SysDepart sysDepart : list5) {
String str[] = getFaAdmCheckStandard(sysDepart.getId(), list5, new String[]{"",""});
if(str[1].length()<18){
String temp = "";
for(int i = str[1].length();i<18;i++){
temp += "0";
}
str[1] = str[1]+temp;
}
strings.add(str[1]);
stringName.add(str[0].substring(0,str[0].length()-1));
}
String [] strArr = strings.toArray(new String[]{});
String [] strArrName = stringName.toArray(new String[]{});
return arraySort(strArr,strArrName);
}
public static String[] arraySort(String[] input,String[] inputName){
for (int i=0;i<input.length-1;i++){
for (int j=0;j<input.length-i-1;j++) {
if(input[j].compareTo(input[j+1])>0){
String temp=input[j];
input[j]=input[j+1];
input[j+1]=temp;
String tempName=inputName[j];
inputName[j]=inputName[j+1];
inputName[j+1]=tempName;
}
}
}
return inputName;
}
public static String[] getFaAdmCheckStandard(String id, List<SysDepart> listPart, String[] endIds) {
if(listPart != null && listPart.size()>0) {
for(SysDepart m : listPart) {
if(m.getId().equals(id)) {
if(oConvertUtils.isNotEmpty(m.getId())) {
endIds[0] = m.getDepartName()+"_"+endIds[0];
endIds[1] = m.getDepartOrder()+endIds[1];
endIds = getFaAdmCheckStandard(m.getParentId(),listPart,endIds);
}
}
}
}
return endIds;
}
public static Sheet importExcel(InputStream inputStream) throws IOException {
Workbook wb0 = new HSSFWorkbook(inputStream);
return wb0.getSheetAt(0);
}
public static HSSFWorkbook getimportExcel(InputStream inputStream) throws IOException {
HSSFWorkbook sheets = new HSSFWorkbook(inputStream);
return sheets;
}
public static boolean isRowEmpty(Row row){
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
return false;
}
}
return true;
}
public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<String>> options, char keyColumn, char valueColumn, int fromRow, int endRow) {
String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
List<String> firstLevelItems = new ArrayList<>();
int rowIndex = 0;
for (Map.Entry<String, List<String>> entry : options.entrySet()) {
String parent = formatNameName(entry.getKey());
firstLevelItems.add(parent);
List<String> children = entry.getValue();
int columnIndex = 0;
Row row = hiddenSheet.createRow(rowIndex++);
Cell cell = null;
for (String child : children) {
cell = row.createCell(columnIndex++);
cell.setCellValue(child);
}
char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));
DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, valueColumn - 'A', valueColumn - 'A');
targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
}
addValidationToSheet(workbook, targetSheet, firstLevelItems.toArray(), keyColumn, fromRow, endRow);
}
public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Object[] options, char column, int fromRow, int endRow) {
String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
String nameName = column + "_parent";
int rowIndex = 0;
for (Object option : options) {
int columnIndex = 0;
Row row = optionsSheet.createRow(rowIndex++);
Cell cell = row.createCell(columnIndex++);
cell.setCellValue(option.toString());
}
createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);
DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
}
private static Name createName(Workbook workbook, String nameName, String formula) {
Name name = workbook.createName();
name.setNameName(nameName);
name.setRefersToFormula(formula);
return name;
}
static String formatNameName(String name) {
name = name.replaceAll(" ", "").replaceAll("-", "_").replaceAll(":", ".");
if (Character.isDigit(name.charAt(0))) {
name = "_" + name;
}
return name;
}
public static String[] getLocationNameList(IInStockService inStockService) {
List<Location> list = inStockService.findLocationList();
ArrayList<String> strings = new ArrayList<>();
for (Location location : list) {
strings.add(location.getName());
}
String [] strArr = strings.toArray(new String[]{});
return strArr;
}
public static HashMap getLocationNameMapList(IInStockService inStockService) {
List<Location> list = inStockService.findLocationList();
HashMap<String, String> map = new HashMap<>();
for (Location location : list) {
map.put(location.getName(),location.getId());
}
return map;
}
public static String[] getSysDictItem(IInStockService inStockService, String dictCode,String locale) {
List<SysDictItem> list = inStockService.findSysDictItemByDictCode(dictCode);
ArrayList<String> strings = new ArrayList<>();
for (SysDictItem sysDictItem : list) {
strings.add(getLanguageDictItem(sysDictItem,locale));
}
String [] strArr = strings.toArray(new String[]{});
return strArr;
}
private static String getLanguageDictItem(SysDictItem sysDictItem, String language) {
if (StringUtils.isEmpty(language)){
return sysDictItem.getItemText();
}
if (CommonConstant.LANGUAGE_ZH_CN.equals(language)){
return sysDictItem.getItemText();
}else if (CommonConstant.LANGUAGE_EN_US.equals(language)){
return sysDictItem.getEngItemText();
}else{
return sysDictItem.getItemText();
}
}
public static String[] getSupplierList(IInStockService inStockService) {
List<Supplier> list = inStockService.getSupplierList();
ArrayList<String> strings = new ArrayList<>();
for (Supplier supplier : list) {
strings.add(supplier.getName());
}
String [] strArr = strings.toArray(new String[]{});
return strArr;
}
public static void setDateType(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
CellStyle css = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
css.setDataFormat(format.getFormat("yyyy-m-d"));
sheet.setDefaultColumnStyle(Col, css);
}
public static void setNumberType(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
CellStyle css = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
css.setDataFormat(format.getFormat("0"));
sheet.setDefaultColumnStyle(Col, css);
}
public static void setDecimalType(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
CellStyle css = wb.createCellStyle();
css.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
sheet.setDefaultColumnStyle(Col, css);
}
public static HashMap<String, String> getLanguageSysDictMap(IInStockService inStockService, String dictCode,String language) {
List<SysDictItem> list = inStockService.findSysDictItemByDictCode(dictCode);
HashMap<String, String> map = new HashMap<>();
for (SysDictItem sysDictItem : list) {
if (StringUtils.isEmpty(language)){
map.put(sysDictItem.getItemText(),sysDictItem.getItemValue());
}
if (CommonConstant.LANGUAGE_ZH_CN.equals(language)){
map.put(sysDictItem.getItemText(),sysDictItem.getItemValue());
}else if (CommonConstant.LANGUAGE_EN_US.equals(language)){
map.put(sysDictItem.getEngItemText(),sysDictItem.getItemValue());
}else {
map.put(sysDictItem.getItemText(),sysDictItem.getItemValue());
}
}
return map;
}
public static HashMap<String, String> getSupplierMapList(IInStockService inStockService) {
List<Supplier> list = inStockService.getSupplierList();
HashMap<String, String> map = new HashMap<>();
for (Supplier supplier : list) {
map.put(supplier.getName(),supplier.getId());
}
return map;
}
public static HashMap<String, String> getLanguageSysDictMapByValue(IInStockService inStockService, String dictCode,String language) {
List<SysDictItem> list = inStockService.findSysDictItemByDictCode(dictCode);
HashMap<String, String> map = new HashMap<>();
for (SysDictItem sysDictItem : list) {
if (StringUtils.isEmpty(language)){
map.put(sysDictItem.getItemValue(),sysDictItem.getItemText());
}
if (CommonConstant.LANGUAGE_ZH_CN.equals(language)){
map.put(sysDictItem.getItemValue(),sysDictItem.getItemText());
}else if (CommonConstant.LANGUAGE_EN_US.equals(language)){
map.put(sysDictItem.getItemValue(),sysDictItem.getEngItemText());
}else {
map.put(sysDictItem.getItemValue(),sysDictItem.getItemText());
}
}
return map;
}
}