moooc_review/src/main/java/com/mooctest/service/ExcelInputService.java

package com.mooctest.service;

import com.mooctest.dao.ExtendBugDao;
import com.mooctest.dao.TaskDao;
import com.mooctest.event.EventUtil;
import com.mooctest.model.ExtendBug;
import com.mooctest.model.Task;
import com.mooctest.util.ImportDataMap;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.bson.Document;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;

@Service
public class ExcelInputService {

    private static Logger logger = LoggerFactory.getLogger(EventUtil.class);


    @Autowired
    TaskDao taskDao;

    @Autowired
    ExtendBugDao extendBugDao;

    @Autowired
    ImportDataMap importDataMap;

    // 接收前端文件，并将MultipartFile文件转换成File文件,并返回文件路径
    public String saveMultipartFile(MultipartFile file, String fileSaveName){
        OutputStream os = null;
        String fileFullPath = null;
        String fileName = file.getOriginalFilename();
        try (
                InputStream inputStream  = file.getInputStream();
        ) {
            String path = System.getProperty("user.dir");
            // 2、保存到临时文件
            // 1K的数据缓冲
            byte[] bs = new byte[1024];
            // 读取到的数据长度
            int len;
            // 输出的文件流保存到本地文件
            File tempFile = new File(path);
            if (!tempFile.exists()) {
                tempFile.mkdirs();
            }
            fileFullPath = tempFile.getPath() + File.separator + fileName;
            os = new FileOutputStream(fileFullPath);
            // 开始读取
            while ((len = inputStream.read(bs)) != -1) {
                os.write(bs, 0, len);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            IOUtils.closeQuietly(os);
        }
        return fileFullPath;
    }


    // 解析File文件，并将为数据库表中各字段赋值，并将数据数据插入数据库中
    public Map<String, Boolean> excelToMongo(String address, String fileSaveName, Integer comeFrom) {
        Integer orderId=UUID.randomUUID().toString().hashCode();
        orderId = orderId < 0 ? -orderId : orderId;
        Long task_id = Long.valueOf(orderId);
        Long case_id = task_id;
        String task_name = fileSaveName;
        List<String> fieldList = null;
        Task task = new Task(task_id, case_id, task_name);
        taskDao.save(task);
        Map<String, Boolean> insert_map = new HashMap<>();
        Map<String, String> typeMap = importDataMap.data.get(comeFrom);
        String appTitle = typeMap.get("title");
        String appBugCategor =  typeMap.get("bug_category");
        String appSeverity =  typeMap.get("severity");
        String appRecurrent=  typeMap.get("recurrent");
        String appDescription=  typeMap.get("description");


//        InputStream in = ExcelInputService.class.getClassLoader().getResourceAsStream(comeFrom+"-application.properties");
//        //读取文件
//        Properties properties=new Properties();
//        try {
//            properties.load(in);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }

//        String appTitle = null;
//        String appBugCategor = null;
//        String appSeverity = null;
//        String appRecurrent= null;
//        String appDescription= null;
//        Map<String,String> dataMap = ImportDateMap.data.get("1");
//        dataMap.get("tit")
//        try {
//            appTitle = new String(properties.getProperty("title").getBytes("ISO8859-1"), "GBK");
//            appBugCategor =new String(properties.getProperty("bug_category").getBytes("ISO8859-1"), "GBK");
//            appSeverity = new String(properties.getProperty("severity").getBytes("ISO8859-1"), "GBK");
//            appRecurrent= new String(properties.getProperty("recurrent").getBytes("ISO8859-1"), "GBK");
//            appDescription= new String(properties.getProperty("description").getBytes("ISO8859-1"), "GBK");
//        } catch (UnsupportedEncodingException e) {
//            e.printStackTrace();
//        }
//
//        System.out.println(appTitle);

        try {
            // 输入文件
            FileInputStream inputStream = new FileInputStream(address);
            // 根据输入流导入Excel产生Workbook对象
            Workbook workbook = new HSSFWorkbook(inputStream);


            fieldList = new ArrayList<String>();
            // 获取Excel文档中第一个表单
            Sheet sheet = workbook.getSheetAt(0);
            // 获取表单第一行作为表头
            Row topRow = sheet.getRow(0);
            if (topRow == null) {
                insert_map.put("file_is_null", true);
                insert_map.put("insert_success", false);
                return insert_map;
            } else {
                insert_map.put("file_is_null", false);

            }

            System.out.println("ok~~");
            for (Cell cell : topRow) {
                fieldList.add(cell.toString());
            }

            logger.info("文件列表" + fieldList);
            System.out.println("文件列表" + fieldList);
            // 获得表单的行数
            int rows = sheet.getLastRowNum() + 1;

            // 获得每行的列数
            int colunms = fieldList.size();
            // 从第二行开始遍历表格
            for (int i = 1; i < rows; i++) {
                ExtendBug extendBug = new ExtendBug();
                Row row = sheet.getRow(i);
                String exam_case_id = task_id+"-"+ case_id;
                long create_time_millis = System.currentTimeMillis();
                extendBug.setBugCategory("空");
                extendBug.setSeverity((short) 0);
                extendBug.setRecurrent((short) 0);
                extendBug.setTitle("空");
                extendBug.setDescription("空");
                extendBug.setExamId(task_id+"");
                extendBug.setCaseId(case_id+"");
                extendBug.setExamCaseId(exam_case_id);
                extendBug.setCreateTimeMillis(create_time_millis+"");
                // 每行从第一列开始遍历列
                /**
                 * 表格中字段的顺序必须是title、bug_category、severity、recurrent、description
                 */
                for (int j = 0; j < colunms; j++) {
                    Cell cell = row.getCell(j);
                    String topName = fieldList.get(j);
//                    if(topName.equals("title") || topName.equals("题目")){
                    if(topName.equals(appTitle)){
                        String title;
                        if(cell == null || cell.toString().equals("")){
                            title = "空";
                        }else{
                            title = cell.toString();
                        }
                        extendBug.setTitle(title);
//                    }else if(topName.equals("bug_category") || topName.equals("分类")){
                    }else if(topName.equals(appBugCategor)){
                        String bug_category;
                        if(cell == null || cell.toString().equals("")){
                            bug_category = "空";
                        }else{
                            bug_category = cell.toString();
                        }
                        extendBug.setBugCategory(bug_category);
//                    }else if(topName.equals("severity") || topName.equals("严重等级")){
                    }else if(topName.equals(appSeverity)){
                        int num;
                        if(cell == null || cell.toString().equals("")){
                            num = 0;
                        }else{
                            num = new Double(Double.parseDouble(cell.toString())).intValue();
                        }
                        short severity = (short)num;
                        extendBug.setSeverity(severity);
//                    }else if(topName.equals("recurrent") || topName.equals("复现程度")){
                    }else if(topName.equals(appRecurrent)){

                        int num;
                        if(cell == null || cell.toString().equals("")){
                            num = 0;
                        }else{
                            num = new Double(Double.parseDouble(cell.toString())).intValue();
                        }
                        short recurrent = (short)num;
                        extendBug.setRecurrent(recurrent);
                    }else if(topName.equals(appDescription)){
                        String description;
                        if(cell == null || cell.toString().equals("")){
                            description = "空";
                        }else{
                            description = cell.toString();
                        }

                        extendBug.setDescription(description);
                    }

                }
                System.out.println(extendBug);
                extendBugDao.save(extendBug);
            }
            logger.info("文件导入成功！");
            // 导入成功后将本地文件夹删除
            File file = new File(address);
            file.delete();
            insert_map.put("insert_success", true);
            return insert_map;
        } catch (FileNotFoundException e) {
            logger.info(e.getMessage());
            insert_map.put("insert_success", false);
            return insert_map;
        } catch (IOException e) {
            logger.info(e.getMessage());
            insert_map.put("file_is_null", true);
            insert_map.put("insert_success", false);
            return insert_map;
        }
    }
}