java解析excel表格的信息
这几天工作就是解析一大堆的Excel表格,期间遇到的问题就是一个空异常过时了(或者说我代码哪里写错了,就是跳不过去,报错),还有就是解析时间列打印出来的是一个数字
先说明第一个问题,其中偶然的机会,发现了Excel的格式可以避免这个问题,把Excel的表格设置成下图的格式,就可以轻松越过空列的问题,点击1所指的地方可以把表格弄成黑色边框
然后就是第二个问题,你仔细看一下,发现那个数据是代表表格的时间到目前时间的一个天数
使用idea创建maven工程,一个main函数搞定,就不需要什么框架了,启动费时间
pom.xml核心包为
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
<dependency>
<groupId>stax</groupId>
<artifactId>stax-api</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15-beta2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<!-- mysql驱动jar包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<!-- dbcp jar包 -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
</dependencies>
然后就是配置jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:数据库的信息?useUnicode=true&characterEncoding=utf8
username=账号
password=密码
接下来就是封装jdbc
package test001;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
public class DBUtils {
public static BasicDataSource ds;
static {
Properties p = new Properties();
InputStream ips = DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
p.load(ips);
} catch (IOException e) {
e.printStackTrace();
}
String driver = p.getProperty("driver");
String url = p.getProperty("url");
String username = p.getProperty("username");
String password = p.getProperty("password");
// 连接池相关
ds = new BasicDataSource();
ds.setDriverClassName(driver);
ds.setUrl(url);
ds.setUsername(username);
ds.setPassword(password);
// 初始连接数量
ds.setInitialSize(3);
ds.setMaxActive(5);
}
public static Connection getConn() throws SQLException {
return ds.getConnection();
}
}
然后就是最关键的main函数部分
package test002;
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 test001.DBUtils;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class ExcelUtilTest {
public static void main(String[] args) throws Exception {
Test test = new Test();
Connection conn = DBUtils.getConn();
// TODO Auto-generated method stub
try {
FileInputStream fStream = new FileInputStream("要解析的Excel文件的地址,后缀名必须是xlsx,不然后面报错");
Workbook wb = new XSSFWorkbook(fStream);
//针对每一个excel只有一个sheet,只需换下边的数字
Sheet sheet = wb.getSheetAt(0);
//因为Sheet接口继承了 java.lang.Iterable接口
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
//跳过空行
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
String value = " ";
for (int j = 0; j < row.getPhysicalNumberOfCells() - 1; j++) {
if (row.getCell(j) == null) {
continue;
}
//设置为String类型
row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
//读取为空值时,报异常
value = value + "'" + row.getCell(j).getStringCellValue() + "'" + ",";
/* //对cell进行判断后输出
value = value + "'" + getStringCell(cell) + "'" + ",";*/
}
//最后一列为时间日期,获得的是距离1900-01-01的天数,需要在此处理一下
for (int k = row.getPhysicalNumberOfCells() - 1; k < row.getPhysicalNumberOfCells(); k++) {
if (row.getCell(k) == null) {
continue;
}
row.getCell(k).setCellType(Cell.CELL_TYPE_STRING);
String day = row.getCell(k).getStringCellValue();
//如果day字符串中存在小数点,只要小数点前边的数据
if (day.contains(".")) {
day = day.substring(0, day.indexOf("."));
}
int days = Integer.parseInt(day);
String time = test.countTime(days);
//设置为String类型
value = value + "'" + time + "'" + ",";
}
value = value.substring(0, value.length() - 1);
// System.out.println(value);
String sql = "insert into (要插入的表名) values(" + value + ")";
System.out.println(sql);
PreparedStatement ps = conn.prepareStatement(sql);
ps.executeUpdate();
System.out.println();
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
数据库的表时间列的类型必须为date类型,不然入库会有错误
解析表格最烦的就是不停的要数据库新建表,代码部分只需要改一下要解析excel文件的路径和表名,如果是格式特别复杂的表格,这个估计就不适用了,以后遇到再说吧