欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

java导入excel操作多sheet页上传

程序员文章站 2022-06-09 15:47:46
依赖包 cn.afterturn easypoi-base 3.0.1 ...

依赖包

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.1</version>
        </dependency>

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.1</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
      <!--  <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>--> 

js代码

function confirmUpload() {
	var doc = document.getElementById('file');
	for(var i=0;i<doc.files.length;i++){
		var name = doc.files[i].name;
		var hz =name.substring( name.lastIndexOf(".")+1);
		if(hz!="xlsx"&&hz!="xls"){
			alert("文件格式错误");
			return false;
		}
	}
	var $file1 = $("#file").val();//用户文件内容(文件)
	// 判断文件是否为空
	if ($file1 == "") {
		alert("请选择上传的目标文件! ")
		return false;
	}
	var formData = new FormData();
	//数据赋值给format
	formData.append("file", $('#file')[0].files[0]);
	//执行上传
	$.ajax({
		type: "POST",
		url: baseURL + 'upload/importsheet',
		processData: false,
		contentType: false,
		data: formData,  //相当于 //data: "{'str1':'foovalue', 'str2':'barvalue'}",
		success: function (jsonResult) {
			if (jsonResult.msg="ok"){
               alert("上传成功",function(result){
                    if(result=="ok"){
                    window.location.reload();
            }
                })
			}else {
				alert("上传失败!")
			}

		},
	});

}; 

controller代码

package com.wgjn.modules.cms.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.wgjn.common.utils.R;
import com.wgjn.common.utils.UploadUtils;
import com.wgjn.modules.cms.entity.*;
import com.wgjn.modules.cms.service.SysDataUploadService;
import com.wgjn.modules.cms.service.SysFilesUploadService;
import org.apache.poi.ss.usermodel.Workbook;
import org.json.JSONException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/upload")
public class SysDataUploadController {

    @Autowired
    private SysDataUploadService sysDataUploadService;

    @PostMapping("/importsheet")
    public R importSheets(@RequestParam("file") MultipartFile file) {
        List<SystemBaseInfoEntity> systemBaseInfoEntityList = new ArrayList<>();
        List<DevopsSystemBaseInfoEntity> devopsSystemBaseInfoEntityList = new ArrayList<>();
        List<DevopsSystemMappingInfoEntity> devopsSystemMappingInfoEntityList = new ArrayList<>();
        List<SysDeviceEntity> deviceEntityList = new ArrayList<>();
        List<ComponentBaseInfoEntity> componentBaseInfoEntityList = new ArrayList<>();
        List<SystemDeviceComponentRelEntity> systemDeviceComponentRelEntityList = new ArrayList<>();
        try {
            // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
            Workbook workBook = UploadUtils.getWorkBook(file);

            ImportParams params = new ImportParams();
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
                String name = workBook.getSheetName(numSheet);
                // 表头在第几行
                params.setTitleRows(0);
                // 距离表头中间有几行不要的数据
                params.setStartRows(0);
                // 第几个sheet页
                params.setStartSheetIndex(numSheet);
                // 验证数据
                params.setNeedVerfiy(true);
                if ("OA系统字典项".equals(name)) {
                    ExcelImportResult<SystemBaseInfoEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            SystemBaseInfoEntity.class, params);

                    // 合格的数据
                    systemBaseInfoEntityList = result.getList();
//                    sysFilesUploadService.fileUpload(systemBaseInfoEntityList);
                    // 业务逻辑

                } else if ("运维系统字典信息".equals(name)) {
                    ExcelImportResult<DevopsSystemBaseInfoEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            DevopsSystemBaseInfoEntity.class, params);

                    // 校验合格的数据
                    devopsSystemBaseInfoEntityList = result.getList();
                    // 业务逻辑
//                    sysFilesUploadService.fileUpload(devopsSystemBaseInfoEntityList);
                } else if ("设备信息".equals(name)) {
                    ExcelImportResult<SysDeviceEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            SysDeviceEntity.class, params);

                    // 校验合格的数据
                    deviceEntityList = result.getList();
                    // 业务逻辑
                } else if ("组件字典信息".equals(name)) {
                    ExcelImportResult<ComponentBaseInfoEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            ComponentBaseInfoEntity.class, params);

                    // 校验合格的数据
                    componentBaseInfoEntityList = result.getList();
                    // 业务逻辑
                } else if ("设备组件".equals(name)) {
                    ExcelImportResult<SystemDeviceComponentRelEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            SystemDeviceComponentRelEntity.class, params);

                    // 校验合格的数据
                    systemDeviceComponentRelEntityList = result.getList();
                    // 业务逻辑
                } else if ("系统字典关联".equals(name)) {
                    ExcelImportResult<DevopsSystemMappingInfoEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            DevopsSystemMappingInfoEntity.class, params);

                    // 校验合格的数据
                    devopsSystemMappingInfoEntityList = result.getList();
                    // 业务逻辑
                }


            }
        } catch (Exception e) {

            return R.error("导入失败!请检查导入文档的格式是否正确");
        }

        String status = sysDataUploadService.fileUpload(systemBaseInfoEntityList, devopsSystemBaseInfoEntityList, deviceEntityList, componentBaseInfoEntityList, systemDeviceComponentRelEntityList, devopsSystemMappingInfoEntityList);
        if ("ok".equals(status)) {
            return R.ok();
        } else {
            return R.error();
        }

    }

} 

本文地址:https://blog.csdn.net/weixin_45805531/article/details/107684192

相关标签: java