java导入excel自定义模板数据

java导入excel自定义模板数据

1. 前言

根据java导出excel自定义模板下载（包含生成模板的excel下拉框数据） 导出的模板，导入数据处理

2. maven的pom文件引入以下内容

如果已经引入过java导出excel自定义模板下载（包含生成模板的excel下拉框数据）中的信息，则无需在引入，否则引入一下内容：

        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

3. 枚举文件

import lombok.Getter;

/**
 * Created by Intellij IDEA.
 *
 */
@Getter
public enum PersonTypeEnum {

    PRINCIPAL("1", "校长"),
    CLASS_TEACHER("2", "班主任"),
    TEACHER("3", "代课老师"),
    ;

    private String code;
    private String value;

    PersonTypeEnum(String code, String value) {
        this.code = code;
        this.value = value;
    }

    public static String getValueByCode(String code) {
        for (PersonTypeEnum personTypeEnum : PersonTypeEnum.values()) {
            if (personTypeEnum.getCode().equals(code)) {
                return personTypeEnum.getValue();
            }
        }
        return null;
    }

    public static String getCodeByValue(String value) {
        for (PersonTypeEnum personTypeEnum : PersonTypeEnum.values()) {
            if (personTypeEnum.getValue().equals(value)) {
                return personTypeEnum.getCode();
            }
        }
        return null;
    }
}

4. 对应模板的实体类

import lombok.Data;

/**
 * Created by Intellij IDEA.
 *
 */
@Data
public class UserModel {

    private String name;

    private String phone;

    private String age;

    private String sex;

    private String personType;
}

5. Controller类

import com.example.util.service.ExportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;


/**
 * 导出工具Controller
 *
 */
@RestController
@RequestMapping("/exportUtil")
@Slf4j
public class ExportController {

    @Resource
    private ExportService exportService;
    /**
     * 模板数据导入
     * @param file
     * @return
     */
    @RequestMapping(value = "/importCustomData",method = RequestMethod.POST)
    public String importCustomData(MultipartFile file){
        return exportService.importCustom(file);
    }

}

6. Service

import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;

/**
 * Created by Intellij IDEA.
 *
 */
public interface ExportService {
    /**
     *
     * @param file
     * @return
     */
    String importCustom(MultipartFile file);
}

7. Service实现类

import com.example.util.enums.PersonTypeEnum;
import com.example.util.model.UserModel;
import com.example.util.service.ExportService;
import com.example.util.utils.excel.DownLoadTrendsTitleUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.catalina.User;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Intellij IDEA.
 *
 */
@Slf4j
@Service
public class ExportServiceImpl implements ExportService {
	 @Override
    public String importCustom(MultipartFile file) {
        String message = "";
        try {
            InputStream inputStream = file.getInputStream();
            Workbook workbook = new XSSFWorkbook(inputStream);
            //此处需与模板sheet名称保持一致
            Sheet sheet = workbook.getSheet("导出数据");
            //封装excel头信息
            Map<Integer, String> headerMap = new HashMap<>();
            Row row = sheet.getRow(0);
            for (Cell cell : row) {
                headerMap.put(cell.getColumnIndex(), cell.getStringCellValue());
            }
            //获取数据信息
            List<Map<String, String>> dataList = new ArrayList<>();
            //sheet.getLastRowNum()获取工作表的最后一行下标值
            for (int i =1; i < sheet.getLastRowNum() + 1; i ++) {
                Row dataRow = sheet.getRow(i);
                if (null == dataRow) {
                    continue;
                }
                Map<String, String> dataMap = new HashMap<>();
                for (Cell cell : dataRow) {
                    CellType cellType = cell.getCellType();
                    if (CellType.STRING.equals(cellType) || CellType.BLANK.equals(cellType)) {
                        dataMap.put(headerMap.get(cell.getColumnIndex()), cell.getStringCellValue());
                    } else  {
                        dataMap.put(headerMap.get(cell.getColumnIndex()), String.valueOf(((XSSFCell) cell).getRawValue()));
                    }

                }
                dataList.add(dataMap);
            }
            if (CollectionUtils.isEmpty(dataList)) {
                return "导入数据不能为空";
            }
            List<UserModel> userModelList = new ArrayList<>();
            //转换数据,封装导入数据
            message = makeImportElderData(dataList, userModelList, message);
            if (!StringUtils.isEmpty(message)) {
                return message;
            }
            /**
             * 业务逻辑
             * 新增进数据库
             * 此demo此次仅做演示，打印数据
             */
            log.info("导入数据：{}", userModelList);
            message = "导入成功！";
            return message;
        } catch (Exception e) {
            e.printStackTrace();
            return "数据异常，请确认数据！";
        }
    }

	
    /**
     * 封装导入数据信息
     * @param dataList
     * @param userModelList
     */
    private String makeImportElderData(List<Map<String, String>> dataList,
                                       List<UserModel> userModelList,
                                       String message) {
        //返回数据
        List<String> messageList = new ArrayList<>();
        //数据行数
        int number = 1;
        for (Map<String, String> data : dataList) {
            /**
             * 此处可处理必填项等校验信息
             */
            if (!StringUtils.isEmpty(message)) {
                number++;
                messageList.add(message);
                continue;
            }
            UserModel userModel = new UserModel();
            userModel.setName(data.get("姓名"));
            userModel.setPhone(data.get("手机号"));
            userModel.setAge(data.get("性别"));
            userModel.setSex(data.get("年龄"));
            String personTypeCode = PersonTypeEnum.getCodeByValue(data.get("人员类型"));
            if (StringUtils.isEmpty(personTypeCode)) {
                message = "第" + number + "行人员类型数据异常，请确认数据！";
                continue;
            }
            userModel.setPersonType(personTypeCode);
            userModelList.add(userModel);
            number++;
        }
        return String.join(",", messageList);
    }

}

8.总结

根据上一篇文章java导出excel自定义模板下载（包含生成模板的excel下拉框数据） 导出的模板处理导入数据。
以上内容，仅供各位兄弟姐妹参考，如发现问题，请各位多提出宝贵意见。