Excel数据导入（有模板）
项目经常有这个需求：提前设置好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 |
五、数据入库的操作，我这里没有写（很好写），自己记得加一下就行~
本文来自博客园，作者：青喺半掩眉砂，转载请注明原文链接：https://www.cnblogs.com/xiaoguo-java/p/16983713.html
