首页 > 其他分享 >Excel数据导入（有模板）

Excel数据导入（有模板）

时间：2022-12-14 22:12:18浏览次数：73  
标签：ApiModelProperty String Excel private cell 导入 CellType value 模板

项目经常有这个需求：提前设置好excel模板，然后把模板中的数据解析出来并存到数据库
本次示例中的excel模板中有4页（sheet1、sheet2、sheet3、sheet4），要把每一页的数据都解析出来，并分别用一个实体类封装 2022-12-14 21:53:16 星期三

一、使用到的依赖

点击查看代码 
<!--excel模板导入相关依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

二、5个实体类的代码如下：

1.AllRecordDTO

点击查看代码 
package cn.itCast.manor.modules.test.excel_import;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 * @Classname ArchivesRecordDTO
 * @Description: 总资料数据
 * @Date: 2022/12/14 0014 18:56
 * @AUTHOR: 无泪之城
 * @Version 1.0
 */
@Data
public class AllRecordDTO implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "档案资料list")
    private List<ArchivesRecordDTO> archivesRecordDTOList;

    @ApiModelProperty(value = "原始资料list")
    private List<YsRecordDTO> ysRecordDTOList;

    @ApiModelProperty(value = "成果资料list")
    private List<CgRecordDTO> cgRecordDTOList;

    @ApiModelProperty(value = "实物资料list")
    private List<SwRecordDTO> swRecordDTOList;

}

2.ArchivesRecordDTO

点击查看代码 
package cn.itCast.manor.modules.test.excel_import;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

/**
 * @Classname ArchivesRecordDTO
 * @Description: 档案资料数据
 * @Date: 2022/12/14 0014 18:56
 * @AUTHOR: 无泪之城
 * @Version 1.0
 */
@Data
public class ArchivesRecordDTO  implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "档案号")
    private String code;

    @ApiModelProperty(value = "资料名称")
    private String name;

    @ApiModelProperty(value = "图幅号")
    private  String tfCode;

    @ApiModelProperty(value = "资料类别")
    private String dataType;

    @ApiModelProperty(value = "省")
    private  String province;

    @ApiModelProperty(value = "市")
    private String city;

    @ApiModelProperty(value = "工作程度")
    private  String workDegree;

    @ApiModelProperty(value = "形成单位")
    private String xcDept;

    @ApiModelProperty(value = "形成时间")
    private  String xcDate;

    @ApiModelProperty(value = "起始时间")
    private String startDate;

    @ApiModelProperty(value = "终止时间")
    private  String overDate;

    @ApiModelProperty(value = "起始经度")
    private String startLongitude;

    @ApiModelProperty(value = "终止经度")
    private  String overLongitude;

    @ApiModelProperty(value = "起始纬度")
    private String startLatitude;

    @ApiModelProperty(value = "终止纬度")
    private  String overLatitude;

    @ApiModelProperty(value = "矿产")
    private String mineralProducts;

    @ApiModelProperty(value = "内容摘要")
    private  String content;

    @ApiModelProperty(value = "备注")
    private String remark;
}

3.CgRecordDTO

点击查看代码 
package cn.itCast.manor.modules.test.excel_import;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

/**
 * @Classname ArchivesRecordDTO
 * @Description: 成果资料数据
 * @Date: 2022/12/14 0014 18:56
 * @AUTHOR: 无泪之城
 * @Version 1.0
 */
@Data
public class CgRecordDTO implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "档案号")
    private String code;

    @ApiModelProperty(value = "文件类型")
    private String fileType;

    @ApiModelProperty(value = "文件名称")
    private  String name;

    @ApiModelProperty(value = "顺序号")
    private String fileCode;

    @ApiModelProperty(value = "比例尺")
    private  String scale;

    @ApiModelProperty(value = "载体类型")
    private String carrierType;

    @ApiModelProperty(value = "涉密信息")
    private  String secretMsg;

    @ApiModelProperty(value = "附图类型")
    private  String ftType;
}

4.SwRecordDTO

点击查看代码 
package cn.itCast.manor.modules.test.excel_import;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

/**
 * @Classname ArchivesRecordDTO
 * @Description: 实物资料数据
 * @Date: 2022/12/14 0014 18:56
 * @AUTHOR: 无泪之城
 * @Version 1.0
 */
@Data
public class SwRecordDTO implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "档案号")
    private String code;

    @ApiModelProperty(value = "岩矿心（孔）")
    private String ykx;

    @ApiModelProperty(value = "总进尺")
    private  String zjc;

    @ApiModelProperty(value = "样品")
    private String yp;

    @ApiModelProperty(value = "岩屑")
    private  String yx;

    @ApiModelProperty(value = "薄片")
    private String bp;

    @ApiModelProperty(value = "标本")
    private  String bb;

    @ApiModelProperty(value = "取心")
    private  String qx;

    @ApiModelProperty(value = "光片")
    private  String gp;

    @ApiModelProperty(value = "大地构造位置")
    private  String ddgzwz;

    @ApiModelProperty(value = "成矿带")
    private  String ckd;

    @ApiModelProperty(value = "主要矿种")
    private  String zykz;
}

5.YsRecordDTO

点击查看代码 
package cn.itCast.manor.modules.test.excel_import;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

/**
 * @Classname ArchivesRecordDTO
 * @Description: 原始资料数据
 * @Date: 2022/12/14 0014 18:56
 * @AUTHOR: 无泪之城
 * @Version 1.0
 */
@Data
public class YsRecordDTO implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "档案号")
    private String code;

    @ApiModelProperty(value = "文件类型")
    private String fileType;

    @ApiModelProperty(value = "文件名称")
    private  String name;

    @ApiModelProperty(value = "顺序号")
    private String fileCode;

    @ApiModelProperty(value = "比例尺")
    private  String scale;

    @ApiModelProperty(value = "载体类型")
    private String carrierType;

    @ApiModelProperty(value = "涉密信息")
    private  String secretMsg;

}

三、解析excel工具类代码如下：

ReadPatientExcelUtil

点击查看代码 
package cn.itCast.manor.modules.test.excel_import;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @Classname ReadPatientExcelUtil
 * @Description:数据导入解析工具类
 * @Date: 2022/12/14 0014 19:16
 * @AUTHOR: 无泪之城
 * @Version 1.0
 */
@Slf4j
public class ReadPatientExcelUtil {

    //总行数
    private static int totalRows = 0;
    //总条数
    private static int totalCells = 0;
    //错误信息接收器
    private static String errorMsg;

    /**
     * 读EXCEL文件，获取信息集合
     * @return
     */
    //
    public static AllRecordDTO getExcelInfo(MultipartFile mFile) {
        String fileName = mFile.getOriginalFilename();//获取文件名
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            AllRecordDTO dto = createExcel(mFile.getInputStream(), isExcel2003);
            return dto;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 根据excel里面的内容读取客户信息
     * @param is 输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public static AllRecordDTO createExcel(InputStream is, boolean isExcel2003) {
        try{
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            AllRecordDTO dto = readExcelValue(wb);// 读取Excel里面客户的信息
            return dto;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 读取Excel里面客户的信息
     * @param wb
     * @return
     */
    private static AllRecordDTO readExcelValue(Workbook wb) {
        AllRecordDTO dto=new AllRecordDTO();
        List<ArchivesRecordDTO> archivesRecordDTOList=new ArrayList<>();
        List<YsRecordDTO> ysRecordDTOList=new ArrayList<>();
        List<CgRecordDTO> cgRecordDTOList=new ArrayList<>();
        List<SwRecordDTO> swRecordDTOList=new ArrayList<>();
        //【解析sheet1-档案资料】###########################################################################################
        //默认会跳过第一行标题
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        totalRows = sheet.getPhysicalNumberOfRows();
        System.out.println("档案资料行数"+totalRows);
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        System.out.println("档案资料列数"+totalCells);
        // 循环Excel行数
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            ArchivesRecordDTO archivesRecordDTO = new ArchivesRecordDTO();
            String  province1 = "";
            String  province2= "";
            String  city1= "";
            String  city2= "";
            // 循环Excel的列
            for (int c = 0; c < totalCells-1; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {           //第一列
                        //如果是纯数字,将单元格类型转为String
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setCode(cell.getStringCellValue());//将单元格数据赋值给user
                    }
                    else if (c == 1){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setName(cell.getStringCellValue());
                    }
                    else if (c == 2){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setTfCode(cell.getStringCellValue());
                    }
                    else if (c == 3){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setDataType(cell.getStringCellValue());
                    }
                    else if (c == 4){//省1
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                          province1=cell.getStringCellValue()+"";
                    }
                    else if (c == 5){//市1
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                          city1=cell.getStringCellValue()+"";
                    }
                    else if (c == 6){//省2
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                          province2=cell.getStringCellValue()+"";
                    }
                    else if (c == 7){//市2
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                          city2=cell.getStringCellValue()+"";
                    }else if (c == 8){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setWorkDegree(cell.getStringCellValue());
                    }
                    else if (c == 9){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setXcDept(cell.getStringCellValue());
                    }
                    else if (c == 10){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setXcDate(cell.getStringCellValue());
                    }
                    else if (c == 11){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setStartDate(cell.getStringCellValue());
                    }
                    else if (c == 12){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setOverDate(cell.getStringCellValue());
                    }
                    else if (c == 13){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setStartLongitude(cell.getStringCellValue());
                    }
                    else if (c == 14){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setOverLongitude(cell.getStringCellValue());
                    }
                    else if (c == 15){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setStartLatitude(cell.getStringCellValue());
                    }
                    else if (c == 16){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setOverLatitude(cell.getStringCellValue());
                    }
                    else if (c == 17){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setMineralProducts(cell.getStringCellValue());
                    }
                    else if (c == 18){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setContent(cell.getStringCellValue());
                    }
                    else if (c == 19){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        archivesRecordDTO.setRemark(cell.getStringCellValue());
                    }
                    //拼接省份+城市
                    String province=province1+","+province2;
                    String city=city1+","+city2;
                    archivesRecordDTO.setProvince(province);
                    archivesRecordDTO.setCity(city);
                }
            }
            archivesRecordDTOList.add(archivesRecordDTO);
            dto.setArchivesRecordDTOList(archivesRecordDTOList);
        }

        //【解析sheet2-原始资料】################################################################################################
        Sheet sheet2 = wb.getSheetAt(1);
        totalRows = sheet2.getPhysicalNumberOfRows();
        System.out.println("原始资料行数"+totalRows);
        if (totalRows > 1 && sheet2.getRow(0) != null) {
            totalCells = sheet2.getRow(0).getPhysicalNumberOfCells();
        }
        System.out.println("原始资料列数"+totalCells);
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet2.getRow(r);
            if (row == null){
                continue;
            }
            YsRecordDTO ysRecordDTO = new YsRecordDTO();
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        ysRecordDTO.setCode(cell.getStringCellValue());
                    }
                    else if (c == 1){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        ysRecordDTO.setFileType(cell.getStringCellValue());
                    }
                    else if (c == 2){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        ysRecordDTO.setName(cell.getStringCellValue());
                    }
                    else if (c == 3){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        ysRecordDTO.setFileCode(cell.getStringCellValue());
                    }
                    else if (c == 4){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        ysRecordDTO.setScale(cell.getStringCellValue());
                    }
                    else if (c == 5){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        ysRecordDTO.setCarrierType(cell.getStringCellValue());
                    }
                    else if (c == 6){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        ysRecordDTO.setSecretMsg(cell.getStringCellValue());
                    }
                }
            }
            ysRecordDTOList.add(ysRecordDTO);
            dto.setYsRecordDTOList(ysRecordDTOList);
        }

        //【解析sheet3-成果资料】################################################################################################
        Sheet sheet3 = wb.getSheetAt(2);
        totalRows = sheet3.getPhysicalNumberOfRows();
        System.out.println("成果资料行数"+totalRows);
        if (totalRows > 1 && sheet3.getRow(0) != null) {
            totalCells = sheet3.getRow(0).getPhysicalNumberOfCells();
        }
        System.out.println("成果资料列数"+totalCells);
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet3.getRow(r);
            if (row == null){
                continue;
            }
            CgRecordDTO cgRecordDTO = new CgRecordDTO();
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        cgRecordDTO.setCode(cell.getStringCellValue());
                    }
                    else if (c == 1){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        cgRecordDTO.setFileType(cell.getStringCellValue());
                    }
                    else if (c == 2){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        cgRecordDTO.setName(cell.getStringCellValue());
                    }
                    else if (c == 3){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        cgRecordDTO.setFileCode(cell.getStringCellValue());
                    }
                    else if (c == 4){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        cgRecordDTO.setScale(cell.getStringCellValue());
                    }
                    else if (c == 5){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        cgRecordDTO.setCarrierType(cell.getStringCellValue());
                    }
                    else if (c == 6){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        cgRecordDTO.setSecretMsg(cell.getStringCellValue());
                    }
                    else if (c == 7){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        cgRecordDTO.setFtType(cell.getStringCellValue());
                    }
                }
            }
            cgRecordDTOList.add(cgRecordDTO);
            dto.setCgRecordDTOList(cgRecordDTOList);
        }

        //【解析sheet4-实物资料】################################################################################################
        Sheet sheet4 = wb.getSheetAt(3);
        totalRows = sheet4.getPhysicalNumberOfRows();
        System.out.println("实物资料行数"+totalRows);
        if (totalRows > 1 && sheet4.getRow(0) != null) {
            totalCells = sheet4.getRow(0).getPhysicalNumberOfCells();
        }
        System.out.println("实物资料列数"+totalCells);
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet4.getRow(r);
            if (row == null){
                continue;
            }
            SwRecordDTO swRecordDTO = new SwRecordDTO();
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setCode(cell.getStringCellValue());
                    }
                    else if (c == 1){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setYkx(cell.getStringCellValue());
                    }
                    else if (c == 2){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setZjc(cell.getStringCellValue());
                    }
                    else if (c == 3){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setYp(cell.getStringCellValue());
                    }
                    else if (c == 4){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setYx(cell.getStringCellValue());
                    }
                    else if (c == 5){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setBp(cell.getStringCellValue());
                    }
                    else if (c == 6){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setBb(cell.getStringCellValue());
                    }
                    else if (c == 7){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setQx(cell.getStringCellValue());
                    }
                    else if (c == 8){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setGp(cell.getStringCellValue());
                    }
                    else if (c == 9){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setDdgzwz(cell.getStringCellValue());
                    }
                    else if (c == 10){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setCkd(cell.getStringCellValue());
                    }
                    else if (c == 11){
                        if(cell.getCellTypeEnum()  == CellType.NUMERIC){
                            cell.setCellType(CellType.STRING);
                        }
                        swRecordDTO.setZykz(cell.getStringCellValue());
                    }
                }
            }
            swRecordDTOList.add(swRecordDTO);
            dto.setSwRecordDTOList(swRecordDTOList);
        }
        log.info("解析结果：{}",dto);
        return dto;
    }


    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }
    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }
    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
}

四、测试用的excel文件：

链接 密码
下载:https://wwuh.lanzout.com/inPiP0ip1iib atrb

五、数据入库的操作，我这里没有写（很好写），自己记得加一下就行~

标签：ApiModelProperty,String,Excel,private,cell,导入,CellType,value,模板
From： https://www.cnblogs.com/xiaoguo-java/p/16983713.html

相关文章

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99