简单的excel表格导入

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工作薄对象，表示整个excelWorkbook workbook = null;try {//获取excel文件的io流InputStream is = new FileInputStream(file);//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象if(fileName.endsWith("xls")){//2003workbook = new HSSFWorkbook(is);}else if(fileName.endsWith("xlsx")){//2007workbook = 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: //BooleancellValue = 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();}}
}

文件下载地址