excel 表格导入 - java 实现

前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到教程。 

import com.alibaba.druid.support.json.JSONUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.foreveross.security.service.extension.ApplicationException;
import com.foreveross.springboot.dubbo.utils.Payload;
import com.XXX.XXX.domain.dto.ProjectImportDto;
import com.xxx.xxx.service.rest.api.ProjectImportRestService;
import com.google.common.collect.Lists;
import com.sargeraswang.util.ExcelUtil.ExcelLogs;
import com.sargeraswang.util.ExcelUtil.ExcelUtil;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceUnit;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.Consumes;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * 导入xxxx数据
 */
@Service("projectImportRestService")
@Path("/api/v1/project")
public class ProjectImportRestServiceImpl implements ProjectImportRestService {

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

    @PersistenceUnit
    private EntityManagerFactory emf;

    private List<String> questionIds; // 存储问题数据

    @POST
    @Path("/import")
    @Consumes({MediaType.MULTIPART_FORM_DATA})
    @Produces({MediaType.APPLICATION_JSON})
    @Override
    public Payload importProject(@Context HttpServletRequest request, @Context HttpServletResponse response) {

        response.setCharacterEncoding("UTF-8");
        boolean isMultipart = ServletFileUpload.isMultipartContent(request);
        if (isMultipart) {

            FileItemFactory factory = new DiskFileItemFactory();
            ServletFileUpload upload = new ServletFileUpload(factory);
            // 支持中文文件名
            upload.setHeaderEncoding("utf-8");
            List<FileItem> fileItems = Lists.newArrayList(); // guava 不用处理泛型，左边是什么类型，右边可以自动匹配。如果list里面放map这种，就可以自动转换。
            try {
                fileItems = upload.parseRequest(request);
                FileItem fileItem = fileItems.get(0); //　可以多文件上传，只有一个文件，所以get(0)，取第一个。
                //获得上传的文件名
                String name = UUID.randomUUID().toString() + "-" + fileItem.getName();

                String filePath = "/home/logs";
                this.upload4Stream(name, filePath, fileItem.getInputStream());
            } catch (ApplicationException ex) {
                ex.printStackTrace();
//                return new Payload(ex.getMessage());
                throw new ApplicationException(Response.Status.NOT_IMPLEMENTED, ex.getMessage());
            } catch (Exception e) {
                e.printStackTrace();
                throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "导入发生异常：", e);
            }
        } else {
            throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "请上传文件");
        }
        return new Payload("Success "+questionIds);
    }


    /**
     * 上传文件具体操作     *
     * @param fileName 文件名
     * @param filePath 文件上传路径
     * @param inStream 文件流
     * @return 上传是否成功
     */
    private boolean upload4Stream(String fileName, String filePath,
                                  InputStream inStream) throws Exception {
        boolean result = false;
        if ((filePath == null) || (filePath.trim().length() == 0)) {
            return result;
        }

        File outputFile = null;
        try {
            String wholeFilePath = filePath + File.separator + fileName;
            File dir = new File(filePath);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            outputFile = new File(wholeFilePath);
            boolean isFileExist = outputFile.exists();
            boolean canUpload = true;
            if (isFileExist) {
                canUpload = outputFile.delete();
            }
            if (canUpload) {
                FileUtils.copyInputStreamToFile(inStream, outputFile);

                //导入数据库
                importData(outputFile);
            }
        } finally {
            FileUtils.deleteQuietly(outputFile);
        }
        return result;
    }

    /**
     * 导入Excel 数据到数据库
     * @param file
     * @throws Exception
     */
    private void importData(File file) throws Exception {

        InputStream inputStream = new FileInputStream(file);
        ExcelLogs logs = new ExcelLogs();
        Collection<ProjectImportDto> list = ExcelUtil.importExcel(ProjectImportDto.class, inputStream,
                "yyyy-MM-dd HH:mm:ss", logs);
        EntityManager em = getEm();
        try {
            int i = 0;
            for (ProjectImportDto dto : list) {

                if (StringUtils.isEmpty(dto.getProjectCode())) {
                    throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "xxx编号不能为空，请输入xxx编号，并检查是否为文本类型");
                }
                String querySql = "select * from GAEI_PROJECT where CODE = '" + dto.getProjectCode() + "'";
                List<Object> objectArrayList = em.createNativeQuery(querySql).getResultList();
                int result;
                try {
                    em.getTransaction().begin();

                    if (!CollectionUtils.isEmpty(objectArrayList)) {

                        Object[] data = (Object[]) objectArrayList.get(0);
                        JSONArray jsonArray = JSON.parseArray(data[9].toString());
//                        -----------------------------------------
                        checkManagerId(dto,em);
//                        -----------------------------------------
                        String professionJson = toProfessionJson(jsonArray, dto);
                        String updateSql = "update GAEI_PROJECT set PROFESSIONAL_GROUP='" +
                                professionJson + "' where CODE = '" + dto.getProjectCode() + "'";
                        result = em.createNativeQuery(updateSql).executeUpdate();
                    } else {
//                        -----------------------------------------
                        checkManagerId(dto,em);
//                        -----------------------------------------
                        JSONArray jsonArray = new JSONArray();
                        String insertSql = "insert into GAEI_PROJECT (SOURCE, PROJECT_DIRECTOR_NO, CODE, STATUS,CREATED_AT, PROFESSIONAL_GROUP) " +
                                " values ('本系统新增', " +
                                "'" + gedProjectDirectorId(dto.getProjectManagerCode(),em) + "'," +
                                "'" + dto.getProjectCode() + "'," +
                                "'正常'," +
                                "'" + DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss") + "'," +
                                "'" + toProfessionJson(jsonArray, dto) + "')";

                        result = em.createNativeQuery(insertSql).executeUpdate();
                    }
                    em.getTransaction().commit();

                } catch (ApplicationException ex) {
                    ex.printStackTrace();
                    throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, ex.getMessage());
                } catch (Exception ex) {
                    ex.printStackTrace();
                    em.getTransaction().rollback();
                    throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "导入xxxx数据失败", ex);
                }

                if (result <= 0) {
                    throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "插入xxx数据失败");
                }

            }
        } catch (ApplicationException ex) {
            ex.printStackTrace();
            throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, ex.getMessage());
        }
        catch (Exception ex) {
            ex.printStackTrace();
            throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, ex.getMessage());
        } finally {
            if (null != em) {
                em.close();
            }
        }
    }

    /**
     * 得xxxxid
     * @param projectManagerCode
     * @param em
     * @return
     */
    private String gedProjectDirectorId(String projectManagerCode,EntityManager em){

        if(projectManagerCode.length() <3){ // 没有工号
            return "00000";
        }
        String getIdSql = "select id from appbricks_user where usernumber = '" + projectManagerCode + "'";
        String projectDirectorId = (String)em.createNativeQuery(getIdSql).getSingleResult();
        if(null == projectDirectorId){
            questionIds.add("xxx工号:"+projectManagerCode);
            throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "xxx工号： "+projectManagerCode+" 有误，请核查");
        }
        return projectDirectorId;
    }

    /**
     * 设置xxxxid
     * @param dto
     * @param em
     */
    private ProjectImportDto checkManagerId(ProjectImportDto dto,EntityManager em){
        String getManagerIdSql = "select id from appbricks_user where usernumber = '" + dto.getProfessionGroupManagerCode() + "'";
        if(null != dto && null != dto.getProfessionGroupManagerCode()){
            if(dto.getProfessionGroupManagerCode().length()>4 ){ // 长度大于4
                String managerId = null;
                try{
                    managerId = (String)em.createNativeQuery(getManagerIdSql).getSingleResult();
                }catch ( Exception e){
                    questionIds.add("xxxx工号:"+dto.getProfessionGroupManagerCode());
                    logger.info("------------------------------------------------------------------\nxxx导入，xxxid不正确"+ e.getMessage());
                    throw new ApplicationException(Response.Status.INTERNAL_SERVER_ERROR, "xxxx工号： "+dto.getProfessionGroupManagerCode()+" 有误，请核查");
                }
                dto.setProfessionGroupManagerCode(String.valueOf(managerId));
            }else {
                dto.setProfessionGroupManagerCode("无");
            }
        }else {
            dto.setProfessionGroupManagerCode("无");
        }
        return dto;
    }

    /**
     * 得EntityManager
     *
     * @return
     */
    private EntityManager getEm() {
        return emf.createEntityManager();
    }

    /**
     * xxx转json
     * @param jsonArray
     * @param dto
     * @return
     */
    private String toProfessionJson(JSONArray jsonArray, ProjectImportDto dto) {

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("name", dto.getProfessionGroup());
        jsonObject.put("departmentId", "");
        jsonObject.put("managerId", dto.getProfessionGroupManagerCode());
        jsonObject.put("managerName", dto.getProfessionGroupManagerName());
        if(null == jsonArray){
            jsonArray = new JSONArray();
        }
        jsonArray.add(jsonObject);
        return JSONUtils.toJSONString(jsonArray);
    }
}