maven配置:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>gongwuyuan</groupId>
<artifactId>gongwuyuan.com</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!--数据库链接-->
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.4</version>
</dependency>
</dependencies>
<repositories>
<repository>
<id>nexus</id>
<name>nexus</name>
<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
<releases>
<enabled>true</enabled>
</releases>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
<repository>
<id>central</id>
<name>Maven Repository Switchboard</name>
<layout>default</layout>
<url>https://repo1.maven.org/maven2</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
</repositories>
<build>
<finalName>gongwuyuan</finalName>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.3.2</version>
<configuration>
<!--源代码编译版本-->
<source>1.8</source>
<!--目标平台编译版本-->
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>
导入工具类:
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* Created by tpc on 2018/3/26.
*/
public class Read {
/**
* 读入excel文件,解析后返回
* @param file
* @throws IOException
*/
public static List<String[]> readExcel(File file) throws IOException{
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环所有行
for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){
//获得当前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
//循环当前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
workbook.close();
}
return list;
}
public static Workbook getWorkBook(File file) {
//获得文件名
String fileName = file.getName();
//创建Workbook工作薄对象,表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = new FileInputStream(file);
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith("xls")){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith("xlsx")){
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把数字当成String来读,避免出现1读成1.0的情况
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}
导入运行类:
import org.apache.commons.lang3.StringUtils;
import java.io.File;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Created by tpc on 2018/3/26.
*/
public class Main {
public static void main(String[] args) {
// insertPostData();
insertPaidData();
}
public static void insertPaidData() {
boolean isInsert = true;//是否写入数据库
// boolean isInsert = false;
String fileName = "2018年四川省省级机关、市州公招公务员考试职位缴费人数统计表(截止3月23日上午10点).xlsx";
Integer start = 1;//跳过开头几行
Integer end = 0;//结尾去掉几行
Integer date = 23;//统计截止日期
String sql = "insert into sign values (?,?,?,?,?)";
//获取数据库连接
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try(
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/gwyks?useUnicode=true&characterEncoding=utf8","root","");
PreparedStatement preparedStatement = conn.prepareStatement(sql)
) {
URL url = Main.class.getClassLoader().getResource(fileName);
String filePath = url.getFile();
System.out.println(filePath);
File file = new File(url.toURI());
List<String[]> strings = Read.readExcel(file);
int i = -1;
int size = strings.size();
Integer pnoIndex=-1,signIndex=-1,approvedIndex=-1,paidIndex=-1;
Pattern pattern = Pattern.compile("([\\s\\S]*)(\\(\\d+\\))");
for (String[] string : strings) {
i++;
if (i< start) {
List<String> strList = Arrays.asList(string);
pnoIndex = pnoIndex==-1?strList.indexOf("职位名称(编号)"):pnoIndex;
signIndex = signIndex==-1?strList.indexOf("报名"):signIndex;
approvedIndex = approvedIndex==-1?strList.indexOf("已通过审核"):approvedIndex;
paidIndex = paidIndex==-1?strList.indexOf("已缴费"):paidIndex;
continue;
} else if (size - end == i || StringUtils.isBlank(string[0])) {
break;
}
String pnoStr = string[pnoIndex];
Matcher matcher = pattern.matcher(pnoStr);
String group="";
while (matcher.find()) {
group = matcher.group(2);
}
String pno = group.substring(group.indexOf("(")+1, group.indexOf(")"));
Integer sign = Integer.parseInt(string[signIndex].trim());
Integer approved = Integer.parseInt(string[approvedIndex].trim());
Integer paid = Integer.parseInt(string[paidIndex].trim());
System.out.println("["+pno+","+sign+","+approved+","+paid+"]");
int j = 1;
preparedStatement.setString(j++,pno);
preparedStatement.setInt(j++,sign);
preparedStatement.setInt(j++,approved);
preparedStatement.setInt(j++,paid);
preparedStatement.setInt(j++,date);
if (isInsert) {
preparedStatement.addBatch();
}
}
if (isInsert) {
preparedStatement.executeBatch();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void insertPostData() {
boolean isInsert = true;//是否写入数据库
// boolean isInsert = false;
String fileName = "雅安市2018年上半年公开考试录用公务员(参照公务员法管理工作人员)职位表 .xls";
Integer start = 2;//跳过开头几行
Integer end = 0;//结尾去掉几行
String city = "雅安市";//城市
String sql = "insert into position values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//获取数据库连接
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try(
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/gwyks?useUnicode=true&characterEncoding=utf8","root","");
PreparedStatement preparedStatement = conn.prepareStatement(sql)
) {
URL url = Main.class.getClassLoader().getResource(fileName);
String filePath = url.getFile();
System.out.println(filePath);
File file = new File(url.toURI());
List<String[]> strings = Read.readExcel(file);
int i = -1;
int size = strings.size();
Integer pnoIndex=-1,unitIndex=-1,postIndex=-1,descIndex=-1,
dutyIndex=-1,numberIndex=-1,scopeIndex=-1,
objectIndex=-1,educationIndex=-1,degreeIndex=-1,
majorIndex=-1,otherConditionIndex=-1,remarkIndex=-1;//职位代码
for (String[] string : strings) {
i++;
if (i< start) {
List<String> strList = Arrays.asList(string);
pnoIndex = pnoIndex==-1?strList.indexOf("职位编码"):pnoIndex;
unitIndex = unitIndex==-1?strList.indexOf("招录机关(县、区)"):unitIndex;
postIndex = postIndex==-1?strList.indexOf("职位名称"):postIndex;
descIndex = descIndex==-1?strList.indexOf("职位简介"):descIndex;
dutyIndex = dutyIndex==-1?strList.indexOf("拟任职务"):dutyIndex;
numberIndex = numberIndex==-1?strList.indexOf("名额"):numberIndex;
scopeIndex = scopeIndex==-1?strList.indexOf("招录范围"):scopeIndex;
objectIndex = objectIndex==-1?strList.indexOf("招录对象"):objectIndex;
educationIndex = educationIndex==-1?strList.indexOf("学历"):educationIndex;
degreeIndex = degreeIndex==-1?strList.indexOf("学位"):degreeIndex;
majorIndex = majorIndex==-1?strList.indexOf("专业"):majorIndex;
otherConditionIndex = otherConditionIndex==-1?strList.indexOf("其他条件"):otherConditionIndex;
remarkIndex = remarkIndex==-1?strList.indexOf("备注"):remarkIndex;
continue;
} else if (size - end == i || StringUtils.isBlank(string[0])) {
break;
}
String pno = string[pnoIndex];
String unit = string[unitIndex];
String post = string[postIndex];
String desc = descIndex==-1?"":string[descIndex];
String duty = string[dutyIndex];
Integer number = Integer.parseInt(string[numberIndex]);
String scope = string[scopeIndex];
String object = string[objectIndex];
String education = string[educationIndex];
String degree = degreeIndex==-1?"":string[degreeIndex];
String major = string[majorIndex];
String other_condition = string[otherConditionIndex];
String remark = string[remarkIndex];
System.out.println("["+city+","+pno+","+unit+","+post+","+desc+","+duty+","
+number+","+scope+","+object+","+education+","+degree+","
+major+","+other_condition+","+remark+"]");
int j = 1;
preparedStatement.setString(j++,pno);
preparedStatement.setString(j++,city);
preparedStatement.setString(j++,unit);
preparedStatement.setString(j++,post);
preparedStatement.setString(j++,desc);
preparedStatement.setString(j++,duty);
preparedStatement.setInt(j++,number);
preparedStatement.setString(j++,scope);
preparedStatement.setString(j++,object);
preparedStatement.setString(j++,education);
preparedStatement.setString(j++,degree);
preparedStatement.setString(j++,major);
preparedStatement.setString(j++,other_condition);
preparedStatement.setString(j++,remark);
if (isInsert) {
preparedStatement.addBatch();
}
}
if (isInsert) {
preparedStatement.executeBatch();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}