2020-12-23

//批量添加
@ApiOperation("员工导入Excel")
@PostMapping("/excelImport")
public R addQuestions(@RequestParam("file") MultipartFile file,String subInstId) throws IOException {
    ResultExcel resultExcel = new ResultExcel(0);
    try {
        // 获取所有的内容
        Map<String, Map<Integer, Map<Integer, String>>> sheets = UploadUtil.readExcelToMap(file);
        Set<String> keys = sheets.keySet();
        List<String> steetsNames = new ArrayList<>();
        // 获取所有的key ==页名称
        for (String steetsName : keys) {
            steetsNames.add(steetsName);
        }
        ArrayList<ArrayList<String>> outErrorData = new ArrayList<ArrayList<String>>();
        // 遍历所有的列
        ManInstEntity entity = manInstService.queryInstEntityByInstId(subInstId);
        for (int i = 0; i < sheets.size(); i++) {
            // 根据页名称获取页
            Map<Integer, Map<Integer, String>> rows = sheets.get(steetsNames.get(i));
            if (rows.size() > 0) {
                // 根据所有的行 遍历
                for (int j = 1; j < rows.size(); j++) {
                    // 遍历当前行的所有列
                    Map<Integer, String> columns = rows.get(j);
                    String jobNum = columns.get(0);
                    String mobile = columns.get(1);
                    String empName = columns.get(2);
                    String nickName = columns.get(3);
                    String sex = columns.get(4);
                    String position = columns.get(5);
                    String faceGroup = columns.get(6);
                    String facePic = columns.get(7);
                    ManEmployeeEntity employeeEntity = new ManEmployeeEntity();
                    employeeEntity.setFacePic(StringUtils.isEmpty(facePic) ? null : facePic);
                    employeeEntity.setNickName(nickName);
                    employeeEntity.setEmpName(empName);
                    employeeEntity.setMobile(mobile);
                    employeeEntity.setSex(sex);
                    employeeEntity.setPosition(position);
                    employeeEntity.setJobNumber(jobNum);
                    employeeEntity.setSubInstId(subInstId);
                    employeeEntity.setAddress(entity == null ? "" : entity.getAddress());
                    //获取线路linds
                    if(StringUtils.isNotEmpty(faceGroup)){
                        List<String> list = Arrays.asList(faceGroup.split(","));
                        List<String> linds = manLineDataService.queryDataEntitiesByLineName(list,subInstId);
                        if(CollectionUtils.isNotEmpty(linds)){
                            LineAndEmployeeForm employeeForm = new LineAndEmployeeForm();
                            employeeForm.setLineIds(linds);
                            employeeForm.setType(0);
                            employeeEntity.setEmpLines(employeeForm);
                        }
                    }
                    String errorMessage = manEmployeeService.getErrorMessage(employeeEntity);
                    ArrayList<String> outData = new ArrayList<>();
                    boolean isError = false;
                    if (StringUtils.isNotEmpty(errorMessage)) {
                        isError = true;
                    } else {
                        try {
                            manEmployeeService.saveOrUpdateEntity(employeeEntity);
                        } catch (Exception e) {
                            isError = true;
                            errorMessage += e.getMessage();
                        }
                    }
                    if (isError) {
                        outData.add(employeeEntity.getJobNumber());
                        outData.add(employeeEntity.getMobile());
                        outData.add(employeeEntity.getEmpName());
                        outData.add(employeeEntity.getNickName());
                        outData.add("0".equals(employeeEntity.getSex()) ? "男" : "女");
                        outData.add(employeeEntity.getPosition());
                        outData.add(employeeEntity.getPhoto());
                        outData.add(employeeEntity.getFacePic());
                        outData.add(errorMessage);
                        outErrorData.add(outData);
                        continue;
                    }
                }
            }
        }
        if (outErrorData != null && outErrorData.size() > 0) {
            ArrayList<String> excelTitleName = new ArrayList<String>();
            excelTitleName.add("工号");
            excelTitleName.add("手机号");
            excelTitleName.add("姓名");
            excelTitleName.add("昵称");
            excelTitleName.add("性别");
            excelTitleName.add("职位");
            excelTitleName.add("权限组");
            excelTitleName.add("人脸图片");
            excelTitleName.add("错误");
            // 写入临时文件
            ExcelOutPut errorOutPut = new ExcelOutPut(excelTitleName, outErrorData);
            String createExcelTempPath = manEmployeeService.getCreateExcelTempPath(errorOutPut);
            resultExcel.setDetail(createExcelTempPath);
            resultExcel.setCode(100);
            resultExcel.setMessage("新增失败");
        }
    } catch (Exception e) {
        // TODO: handle exception
        System.out.println(e.getMessage());
        //return setResultError("表格出现未知错误!错误码:20");
    }
    if (resultExcel.getCode().intValue() == 0) {
        return R.ok().put("resultExcel", resultExcel);
    } else {
        log.info(JSON.toJSONString(resultExcel));
        return R.error().put("resultExcel", resultExcel);
    }
}

//创建临时文件，记录错误信息

@Override
public String getCreateExcelTempPath(ExcelOutPut excelOutPut) {
   XSSFWorkbook wb = null;
   String filePath = "";
   try {
      ArrayList<String> titleList = excelOutPut.getTitleList();
      ArrayList<ArrayList<String>> dataList = excelOutPut.getDataList();
      wb = new XSSFWorkbook();
      XSSFSheet sheet = wb.createSheet();
      XSSFRow row = sheet.createRow((short) 0);
      for (int i = 0; i < titleList.size(); i++) {
         row.createCell(i).setCellValue(titleList.get(i));
      }

      for (int i = 0; i < dataList.size(); i++) {
         row = sheet.createRow((short) i + 1);
         for (int j = 0; j < dataList.get(i).size(); j++) {
            row.createCell(j).setCellValue(dataList.get(i).get(j));
         }
      }

      for (int columnNum = 0; columnNum < dataList.get(0).size(); columnNum++) {
         int columnWidth = sheet.getColumnWidth(columnNum) / 256;
         for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
            XSSFRow currentRow;
            if (sheet.getRow(rowNum) == null) {
               currentRow = sheet.createRow(rowNum);
            } else {
               currentRow = sheet.getRow(rowNum);
            }

            if (currentRow.getCell(columnNum) != null) {
               XSSFCell currentCell = currentRow.getCell(columnNum);
               int length = currentCell.toString().getBytes().length;
               if (columnWidth < length) {
                  columnWidth = length;
               }
            }
         }
         sheet.setColumnWidth(columnNum, columnWidth * 256 + 256 * 1);
      }
      Date currentTime = new Date();
      SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmssSSS");
      String fileName = formatter.format(currentTime);
      String filedisplay = fileName + ".xlsx";
      filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
      String suffix = filedisplay.substring(filedisplay.lastIndexOf("."));
      ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
      wb.write(byteArrayOutputStream);// 写入磁盘
      byte[] bytes = byteArrayOutputStream.toByteArray();
      ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
      filePath = OSSFactory.build().uploadSuffix(byteArrayInputStream, suffix, true);
      int indexOf = filePath.indexOf("#");
      if (indexOf > -1) {
         filePath = filePath.substring(0, indexOf);
      }
   } catch (Exception e) {
      e.printStackTrace();
      filePath = "";
   } finally {
      try {
         wb.close();
      } catch (IOException e) {
         e.printStackTrace();
      }
   }
   return filePath;
}

package com.oftoo.common.entity;

import com.oftoo.modules.oss.cloud.OSSFactory;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class UploadUtil {
    //读取上传的文件
    public static Map<String, Map<Integer, Map<Integer, String>>> readExcelToMap(MultipartFile file) throws IOException {

        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        // 声明所有页的集合
        Map<String, Map<Integer, Map<Integer, String>>> mapSheet = new LinkedHashMap<>();
        wb = readExcel(file);
        if (wb != null) {
            // 获取总页数
            int pageSize = wb.getNumberOfSheets();
            for (int i = 0; i < pageSize; i++) {

                // 声明当前页的行和列
                Map<Integer, Map<Integer, String>> map = new HashMap<>();
                // 获取当前页
                sheet = wb.getSheetAt(i);
                //声明当前页图片的集合
                Map<String, PictureData> pMap = null;
                //获取图片
                if(file.getOriginalFilename().endsWith(".xls")){
                    pMap = getPictures1((HSSFSheet) sheet);
                }else{
                    pMap = getPictures2((XSSFSheet) sheet);
                }
                String sheetName = sheet.getSheetName();

                // System.out.println("获取当前页的最大行数");
                int rowSize = sheet.getPhysicalNumberOfRows();

//          System.out.println("总行数:"+rowSize);
                // System.out.println("遍历所有行");
                for (int j = 0; j < rowSize; j++) {
                    // System.out.println("获取第"+j+"行");
                    row = sheet.getRow(j);
                    // System.out.println("获取当前页的最大列数");
                    int columnSize = row.getPhysicalNumberOfCells();
                    // 声明当前列
                    Map<Integer, String> columnMap = new HashMap<>();
//             System.out.println("列大小:"+columnSize);
                    for (int j2 = 0; j2 < columnSize; j2++) {
                        // System.out.println("获取第"+j2+"列的内容");
                        String value = (String)getCellFormatValue(row.getCell(j2));
                        // 添加当前列的内容 j2代表第几列 value是内容
                        columnMap.put(j2, value);

                    }
                    // 添加当前行的内容 j代表第几行 value是列的内容 意思是第几行第几列的内容
                    map.put(j, columnMap);
                }
                //解析图片并上传到服务器 并设置该字段的值为字符串类型添加到map中 进行数据库上传
                Object key[] = pMap.keySet().toArray();

                for (int v = 0; v < pMap.size(); v++) {
                    PictureData pic = pMap.get(key[v]);
                    String picName = key[v].toString();
                    String ext = pic.suggestFileExtension();
                    byte[] data = pic.getData();
                    try {
                        InputStream input = new ByteArrayInputStream(data);
                        String url = upload(input, "." + ext);
                        if(StringUtils.isNotEmpty(url)){
                            //解析key 并根据key 设置 某一行的某一列的 图片链接
                            String[] split = picName.split("-");
                            Integer rowIndex = Integer.parseInt(split[0].toString()),columnIndex = Integer.parseInt(split[1].toString());
                            //根据行下标 获取所有的列
                            Map<Integer, String> columns = map.get(rowIndex);

                            //根据列下标 设置图片链接值
                            columns.put(columnIndex, url);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                // 添加当前页的所有内容
                mapSheet.put(sheetName, map);
            }
        }
        return mapSheet;
    }

    public static String upload( InputStream inputStream,String suffix){
        String url = OSSFactory.build().uploadSuffix(inputStream, suffix);
        String url1 = url;
        String url2 = "";
        if (url.contains("#")) {
            String[] urls = url.split("#");
            if (urls != null && urls.length > 1) {
                url1 = urls[0];
                url2 = urls[1];
            } else {
                url1 = url.substring(0, url.lastIndexOf("#"));
            }
        }
        return url1;
    }

    //获取表格字段属性
    private static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC: {
                    cellValue = String.valueOf(cell.getNumericCellValue());

                    break;
                }
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        // 数字
                        cellValue = String.valueOf(cell.getNumericCellValue());

                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();

                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    // 读取图片
    public static Map<String, PictureData> getPictures1(HSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                map.put(key, pdata);
            }
        }
        return map;
    }

    //读取图片
    public  static Map<String, PictureData> getPictures2(XSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, picture.getPictureData());
                }
            }
        }
        return map;
    }

    // 读取excel
    @SuppressWarnings("unused")
    private static Workbook readExcel(MultipartFile file) {
        Workbook wb = null;

        if (file == null) {
            return null;
        }
        String filename = file.getOriginalFilename();
        InputStream is = null;
        try {
            is = file.getInputStream();
            if (filename.endsWith(".xls")) {
                return wb = new HSSFWorkbook(is);
            } else if (filename.endsWith(".xlsx")) {
                return wb = new XSSFWorkbook(is);
            } else {
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

}