|
需要添加的依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
<scope>compile</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-collections4 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
代码:
package com.XX.XX.util;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
/**
* Description:读取Excel数据
*/
public class ExcelData {
private XSSFSheet sheet;
/**
* 构造函数，初始化excel数据
*
* @param filePath excel路径
* @param sheetName sheet表名
*/
ExcelData(String filePath, String sheetName) {
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(filePath);
XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
//获取sheet
sheet = sheets.getSheet(sheetName);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 根据行和列的索引获取单元格的数据
*
* @param row
* @param column
* @return
*/
public String getExcelDateByIndex(int row, int column) {
XSSFRow row1 = sheet.getRow(row);
String cell = row1.getCell(column) == null ? "" : row1.getCell(column).toString();
return cell;
}
/**
* 根据某一列值为“******”的这一行，来获取该行第x列的值
*
* @param caseName
* @param currentColumn 当前单元格列的索引
* @param targetColumn 目标单元格列的索引
* @return
*/
public String getCellByCaseName(String caseName, int currentColumn, int targetColumn) {
String operateSteps = "";
//获取行数
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < rows; i++) {
XSSFRow row = sheet.getRow(i);
String cell = row.getCell(currentColumn).toString();
if (cell.equals(caseName)) {
operateSteps = row.getCell(targetColumn).toString();
break;
}
}
return operateSteps;
}
//打印excel数据
public void readExcelData() {
//获取行数
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < rows; i++) {
//获取列数
XSSFRow row = sheet.getRow(i);
int columns = row.getPhysicalNumberOfCells();
for (int j = 0; j < columns; j++) {
String cell = row.getCell(j).toString();
System.out.println(cell);
}
}
}
public static List<City> getDemos(){
ExcelData sheet1 = new ExcelData("D:/XX/XX/abc.xlsx", "Sheet1");
String provinceCode = "";
String cityCode = "";
String provinceNm = "";
String cityNm = "";
List<City> demos = new ArrayList<>();
for (int k = 0; k < 3526; k++) {//3526行
String excelDateByIndex = sheet1.getExcelDateByIndex(k, 0);
City demo = new City(excelDateByIndex.substring(0, 6),
sheet1.getExcelDateByIndex(k, 1), sheet1.getExcelDateByIndex(k, 2),
sheet1.getExcelDateByIndex(k, 3));
if (StringUtils.isNotBlank(demo.getProvinceNm())) {//省
provinceNm = demo.getProvinceNm();
provinceCode = demo.getCode();
demo.setProvinceCode(provinceCode);
demo.setLevel(0);
} else if (StringUtils.isNotBlank(demo.getCityNm())) {//市
cityNm = demo.getCityNm();
cityCode = demo.getCode();
demo.setProvinceNm(provinceNm);
demo.setProvinceCode(provinceCode);
demo.setCityCode(cityCode);
demo.setLevel(1);
} else {//区县
demo.setProvinceNm(provinceNm);
demo.setCityNm(cityNm);
demo.setProvinceCode(provinceCode);
demo.setCityCode(cityCode);
demo.setCountyCode(demo.getCode());
demo.setCountyNm(demo.getCountyNm());
demo.setLevel(2);
}
// System.out.println(JSON.toJSONString(demo));
demos.add(demo);
}
return demos;
}
}
package com.XX.XX.util;
public class City {
private Integer id;
private String provinceCode;
private String cityCode;
private String countyCode;
private String provinceNm;
private String cityNm;
private String countyNm;
private Integer level;//0省,1市,2县
private String code;
public City(String code, String provinceNm, String cityNm, String countyNm) {
this.provinceNm = provinceNm;
this.cityNm = cityNm;
this.countyNm = countyNm;
this.code = code;
}
public City() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getProvinceCode() {
return provinceCode;
}
public void setProvinceCode(String provinceCode) {
this.provinceCode = provinceCode;
}
public String getCityCode() {
return cityCode;
}
public void setCityCode(String cityCode) {
this.cityCode = cityCode;
}
public String getCountyCode() {
return countyCode;
}
public void setCountyCode(String countyCode) {
this.countyCode = countyCode;
}
public String getProvinceNm() {
return provinceNm;
}
public void setProvinceNm(String provinceNm) {
this.provinceNm = provinceNm;
}
public String getCityNm() {
return cityNm;
}
public void setCityNm(String cityNm) {
this.cityNm = cityNm;
}
public String getCountyNm() {
return countyNm;
}
public void setCountyNm(String countyNm) {
this.countyNm = countyNm;
}
public Integer getLevel() {
return level;
}
public void setLevel(Integer level) {
this.level = level;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
}
数据库:
CREATE TABLE `citys` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`province_code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省级编码',
`city_code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '市级编码',
`county_code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '县级编码',
`province_nm` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省名称',
`city_nm` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '市名称',
`county_nm` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '县名称',
`level` tinyint(1) NOT NULL COMMENT '0省1市2县',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3527 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
excel文件:
https://download.csdn.net/download/can_not_miao/13065185 | 
转播
