添加链接描述
通过Java代码读取excle里面的内容加载到mysql中，使用mybatis框架
public static void main(String[] args) {
try {
import2016EnrollPlan();
}catch (Exception e) {
e.printStackTrace();
}
}
public static void import2016EnrollPlan() throws Exception{
InputStream ip = new FileInputStream("G:\\实验的excle2\\34-47_2016年本科一批A类录取院校（专业）录取新生分数分布统计（文史类).xlsx");
Workbook workbook = new XSSFWorkbook(ip);
Sheet sheet = workbook.getSheetAt(0);
workbook.close();
//定义变量
String collegeName = null;
//String collegeCode = null;
CollegeSpeciality collegeSpeciality =null;
String cellContent = null;
//String specialityname = null;
//读取一行分析
for(int index= 1 ;index<=sheet.getLastRowNum();index++ ) {
Row row = sheet.getRow(index);
System.out.println("显示："+index);
//第三行可能出现的情况：1、学校 2、专业 3、空行
Cell cell = row.getCell(4);
cell.setCellType(CellType.STRING);
collegeSpeciality = new CollegeSpeciality();
collegeSpeciality.setProvince("天津");
collegeSpeciality.setYear_no(2016);
collegeSpeciality.setScience_art("文史类");
collegeSpeciality.setBatches("本科一批A类");
cellContent = cell.getStringCellValue();
if (cellContent.indexOf("：")>0) {
//这行基本就是大学行了
// 将院校写到数据库中
cell = row.getCell(1);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
collegeName = cellContent;
//collegeSpeciality.setCollege_name(collegeName);
//CareerDataOper.insertCollegeSpecaility(collegeSpeciality);
}else {
//处理专业
collegeSpeciality.setCollege_name(collegeName);
cell = row.getCell(1);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
String pattern = "^[0-9]{0}";
Pattern p=Pattern.compile(pattern);
Matcher m=p.matcher(cellContent);
//specialityname = cellContent;
if (m.find()) {
if (cellContent.indexOf(")")>0) {
collegeSpeciality.setSpeciality_name(cellContent.substring(m.end(0),cellContent.indexOf("(")));
collegeSpeciality.setRemark(cellContent.substring(cellContent.indexOf("(")));
}else {
collegeSpeciality.setSpeciality_name(cellContent.substring(m.end()));
}
}
//将计划人数人数写到数据库中
cell = row.getCell(2);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue();
pattern = "^[0-9]{0}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if(m.find()){
collegeSpeciality.setPlanned_enrollment(Integer.valueOf(cellContent));
}else{
throw new Exception("计划人数解析错误:"+cellContent);
}
//将录取人数写到数据库中
cell = row.getCell(3);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue();
pattern = "^[0-9]{0}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if(m.find()){
collegeSpeciality.setEnrollment(Integer.valueOf(cellContent));
}else{
throw new Exception("录取人数解析错误:"+cellContent);
}
//检测第四列是否有数据
cell = row.getCell(4);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("645"));
}
//检测第五列是否有数据
cell = row.getCell(5);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("635"));
}
//检测第六列是否有数据
cell = row.getCell(6);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("625"));
}
//检测第七列是否有数据
cell = row.getCell(7);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("615"));
}
//检测第八列是否有数据
cell = row.getCell(8);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("605"));
}
//检测第九列是否有数据
cell = row.getCell(9);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("595"));
}
//检测第十列是否有数据
cell = row.getCell(10);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("585"));
}
//检测第十一列是否有数据
cell = row.getCell(11);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("575"));
}
//检测第十二列是否有数据
cell = row.getCell(12);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("565"));
}
//检测第十三列是否有数据
cell = row.getCell(13);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("555"));
}
//检测第十四列是否有数据
cell = row.getCell(14);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("545"));
}
//检测第十五列是否有数据
cell = row.getCell(15);
cell.setCellType(CellType.STRING);
cellContent = cell.getStringCellValue().replace(" ","");
pattern = "^[0-9]{1}";
p=Pattern.compile(pattern);
m=p.matcher(cellContent);
if (m.find()) {
collegeSpeciality.setLowest_score(Integer.valueOf("536"));
}
//通过省份，学校，专业，年份查询是否有此专业
List<CollegeSpeciality> listCollegeSpeciality = CareerDataOper.findCollegeSpecialityByAAA(
collegeSpeciality.getProvince(),collegeSpeciality.getCollege_name(),collegeSpeciality.getSpeciality_name(),
collegeSpeciality.getYear_no(),collegeSpeciality.getRemark());
if(listCollegeSpeciality==null||listCollegeSpeciality.size()<1){
//刷新数据到数据库
CareerDataOper.insertCollegeSpecaility(collegeSpeciality);
}else{
System.out.println("此条信息已存在");
//throw new Exception("此条信息已存在");
}
if(collegeSpeciality.getSpeciality_name().length()>15)
System.out.println(collegeSpeciality.getRemark());
}
}
}
}
//这是实体类
package entity;
public class CollegeSpeciality {
private Integer id;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
private String province; //此条数据属于哪个省会
private String college_name;//大学名称
private String college_code;//此省的大学代码
private String speciality_name;//专业名字
private String speciality_code;
private Integer year_no;
private String science_art;
private Integer planned_enrollment;//招生计划人数。
private Integer enrollment;//录取人数
private String tuition;//学费
private String elective;//选考科目
private String batches;//录取批次
private String major_years;//专业学制
private Integer avg_score;
private Integer one_score;
private Integer one_rank;
private Integer two_score;
private Integer two_rank;
private Integer three_score;
private Integer three_rank;
private Integer remain;
private String remark;
private Integer lowest_score;
private Integer lowest_rank;
private Integer avg_rank;
private Integer highest_score;
private Integer highest_rank;
private Integer archive_highest_score;
private Integer archive_lowest_score;
private String foreign_language;
private String major_level;
private String in_majors;
public String getMajor_level() {
return major_level;
}
public void setMajor_level(String major_level) {
this.major_level = major_level;
}
public String getIn_majors() {
return in_majors;
}
public void setIn_majors(String in_majors) {
this.in_majors = in_majors;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCollege_name() {
return college_name;
}
public void setCollege_name(String college_name) {
this.college_name = college_name;
}
public String getCollege_code() {
return college_code;
}
public void setCollege_code(String college_code) {
this.college_code = college_code;
}
public String getSpeciality_name() {
return speciality_name;
}
public void setSpeciality_name(String speciality_name) {
this.speciality_name = speciality_name;
}
public String getSpeciality_code() {
return speciality_code;
}
public void setSpeciality_code(String speciality_code) {
this.speciality_code = speciality_code;
}
public Integer getYear_no() {
return year_no;
}
public void setYear_no(Integer year_no) {
this.year_no = year_no;
}
public String getScience_art() {
return science_art;
}
public void setScience_art(String science_art) {
this.science_art = science_art;
}
public Integer getPlanned_enrollment() {
return planned_enrollment;
}
public void setPlanned_enrollment(Integer planned_enrollment) {
this.planned_enrollment = planned_enrollment;
}
public Integer getEnrollment() {
return enrollment;
}
public void setEnrollment(Integer enrollment) {
this.enrollment = enrollment;
}
public String getTuition() {
return tuition;
}
public void setTuition(String tuition) {
this.tuition = tuition;
}
public String getElective() {
return elective;
}
public void setElective(String elective) {
this.elective = elective;
}
public String getBatches() {
return batches;
}
public void setBatches(String batches) {
this.batches = batches;
}
public String getMajor_years() {
return major_years;
}
public void setMajor_years(String major_years) {
this.major_years = major_years;
}
public Integer getAvg_score() {
return avg_score;
}
public void setAvg_score(Integer avg_score) {
this.avg_score = avg_score;
}
public Integer getOne_score() {
return one_score;
}
public void setOne_score(Integer one_score) {
this.one_score = one_score;
}
public Integer getOne_rank() {
return one_rank;
}
public void setOne_rank(Integer one_rank) {
this.one_rank = one_rank;
}
public Integer getTwo_score() {
return two_score;
}
public void setTwo_score(Integer two_score) {
this.two_score = two_score;
}
public Integer getTwo_rank() {
return two_rank;
}
public void setTwo_rank(Integer two_rank) {
this.two_rank = two_rank;
}
public Integer getThree_score() {
return three_score;
}
public void setThree_score(Integer three_score) {
this.three_score = three_score;
}
public Integer getThree_rank() {
return three_rank;
}
public void setThree_rank(Integer three_rank) {
this.three_rank = three_rank;
}
public Integer getRemain() {
return remain;
}
public void setRemain(Integer remain) {
this.remain = remain;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public Integer getLowest_score() {
return lowest_score;
}
public void setLowest_score(Integer lowest_score) {
this.lowest_score = lowest_score;
}
public Integer getLowest_rank() {
return lowest_rank;
}
public void setLowest_rank(Integer lowest_rank) {
this.lowest_rank = lowest_rank;
}
public Integer getAvg_rank() {
return avg_rank;
}
public void setAvg_rank(Integer avg_rank) {
this.avg_rank = avg_rank;
}
public Integer getHighest_score() {
return highest_score;
}
public void setHighest_score(Integer highest_score) {
this.highest_score = highest_score;
}
public Integer getHighest_rank() {
return highest_rank;
}
public void setHighest_rank(Integer highest_rank) {
this.highest_rank = highest_rank;
}
public Integer getArchive_highest_score() {
return archive_highest_score;
}
public void setArchive_highest_score(Integer archive_highest_score) {
this.archive_highest_score = archive_highest_score;
}
public Integer getArchive_lowest_score() {
return archive_lowest_score;
}
public void setArchive_lowest_score(Integer archive_lowest_score) {
this.archive_lowest_score = archive_lowest_score;
}
public String getForeign_language() {
return foreign_language;
}
public void setForeign_language(String foreign_language) {
this.foreign_language = foreign_language;
}
}
<insert id="insertCollegeSpecaility" parameterType="entity.CollegeSpeciality">
insert into t_college_speciality
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="province != null" >
province,
</if>
<if test="college_name != null" >
college_name,
</if>
<if test="college_code != null" >
college_code,
</if>
<if test="speciality_name != null" >
speciality_name,
</if>
<if test="speciality_code != null" >
speciality_code,
</if>
<if test="year_no != null" >
year_no,
</if>
<if test="science_art != null" >
science_art,
</if>
<if test="planned_enrollment != null" >
planned_enrollment,
</if>
<if test="enrollment != null" >
enrollment,
</if>
<if test="tuition != null" >
tuition,
</if>
<if test="elective != null" >
elective,
</if>
<if test="major_years != null" >
major_years,
</if>
<if test="avg_score != null" >
avg_score,
</if>
<if test="avg_rank != null" >
avg_rank,
</if>
<if test="one_score != null" >
one_score,
</if>
<if test="one_rank != null" >
one_rank,
</if>
<if test="two_score != null" >
two_score,
</if>
<if test="two_rank != null" >
two_rank,
</if>
<if test="three_score != null" >
three_score,
</if>
<if test="three_rank!= null" >
three_rank,
</if>
<if test="remain != null" >
remain,
</if>
<if test="remark != null" >
remark,
</if>
<if test="lowest_score != null" >
lowest_score,
</if>
<if test="lowest_rank != null" >
lowest_rank,
</if>
<if test="foreign_language !=null">
foreign_language,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="province != null" >
#{province},
</if>
<if test="college_name != null" >
#{college_name},
</if>
<if test="college_code != null" >
#{college_code},
</if>
<if test="speciality_name != null" >
#{speciality_name},
</if>
<if test="speciality_code != null" >
#{speciality_code},
</if>
<if test="year_no != null" >
#{year_no},
</if>
<if test="science_art != null" >
#{science_art},
</if>
<if test="planned_enrollment != null" >
#{planned_enrollment},
</if>
<if test="enrollment != null" >
#{enrollment},
</if>
<if test="tuition != null" >
#{tuition},
</if>
<if test="elective != null" >
#{elective},
</if>
<if test="major_years != null" >
#{major_years},
</if>
<if test="avg_score != null" >
#{avg_score},
</if>
<if test="avg_rank != null" >
#{avg_rank},
</if>
<if test="one_score != null" >
#{one_score},
</if>
<if test="one_rank != null" >
#{one_rank},
</if>
<if test="two_score != null" >
#{two_score},
</if>
<if test="two_rank != null" >
#{two_rank},
</if>
<if test="three_score != null" >
#{three_score},
</if>
<if test="three_rank!= null" >
#{three_rank},
</if>
<if test="remain != null" >
#{remain},
</if>
<if test="remark != null" >
#{remark},
</if>
<if test="lowest_score != null" >
#{lowest_score},
</if>
<if test="lowest_rank != null" >
#{lowest_rank},
</if>
<if test="foreign_language != null">
#{foreign_language}
</if>
</trim>
</insert>
//实体类传入的位置
public static List<CollegeSpeciality> findCollegeSpecialityByAAA(String province,String collegeName,
String speciality,Integer yearNo,String remark){
SqlSession session = sqlSessionFactory.openSession();
try {
MajorMapper mapper = session.getMapper(MajorMapper.class);
return mapper.findCollegeSpecialityByAAA(province,collegeName,speciality,yearNo,remark);
}finally {
session.close();
}
}
//MajorMapper接口中的方法
@Select("select * from t_college_speciality where province=#{province}"
+ " and college_name=#{collegeName}"
+ " and speciality_name=#{speciality}"
+ " and remark=#{remark}"
+ " and year_no=#{yearNo}")
List<CollegeSpeciality> findCollegeSpecialityByAAA(@Param("province")String province,
@Param("collegeName")String collegeName,
@Param("speciality")String speciality,
@Param("yearNo")Integer yearNo,
@Param("remark")String remark);
``
对于这张excle我的处理方式是：
1、通过代码先获取这个文件的地址
2、打开文件，一行一行的识别excle表格，然后通过getrow方法调用到对应的行
3、通过getCell方法识别对应的单元格：比如我这代码是通过识别第四列是否存在"："这个符号来判断这行是否为学校，如果存在我就把这行的第二列中的内容识别出来存到实体类：CollegeSpeciality中的college_name。
4、剩下来的行基本就是专业行了，然后通过substring方法我截取了"（"前面的字存到实体类的变量中的speciality_name。剩下的数据我就不一一介绍了，基本也是差不多的方法。
5、接下来我通过mybatis框架，建立了sqlSessionFactory，然后打开sqlSession，接下来连接到MajorMapper接口还有MajorMapper.xml文件，将实体类的数据传到MajorMapper接口看是否存在重复数据，MajorMapper.xml文件中是sql语句，是我用来将数据插入到mysql的语句。从上往下的第三片代码是xml里的插入数据库的sql语句，第四片代码片是MajorMapper接口的判断方法，用来判断数据库是否存在重复数据。
6、然后就可以开始运行了。
6215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
