今天一个同学需要处理Excel文件,顺便自己研究一下。刚刚参考网上资料,使用poi库测试了一下读取excel文件,效果不错,跟大家分享一下。
POI:提供API给Java程序对Microsoft Office格式档案读和写的功能。
先是读取Excel文件:
/**
* 读取Excel表格表头的内容
*
* @param InputStream
* @return String 表头内容的数组
*/
public String[] readExcelTitle(InputStream is) {
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
// title[i] = getStringCellValue(row.getCell((short) i));
title[i] = getCellFormatValue(row.getCell((short) i));
}
return title;
}
/**
* 读取Excel数据内容
*
* @param InputStream
* @return 二维数组
*/
public String[][] readExcelContent(InputStream is) {
String [][] arr;
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
arr=new String[rowNum][colNum];
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i < arr.length; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < arr[i].length) {
arr[i][j]= getCellFormatValue(row.getCell((short) j)).trim();
j++;
}
}
return arr;
}
创建实体类:
package com.sanji.entity;
public class Excel {
private String id ;// id
private String name ;// 姓名
private String salary ;// 工资
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
@Override
public String toString() {
return "employee [id=" + id + ", name=" + name + ", salary=" + salary+ "]";
}
}
添加测试类:
package com.sanji.test;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import com.sanji.dao.ExcelDaoImpl;
import com.sanji.entity.Excel;
import com.sanji.io.ExcelReader;
public class test {
public static void main(String[] args) {
try {
// 对读取Excel表格标题测试
InputStream is = new FileInputStream("d:\\test2.xls");//读取excel文件
ExcelReader excelReader = new ExcelReader();//获取工具类
String[] title = excelReader.readExcelTitle(is);//读取标题
System.out.println("获得Excel表格的标题:");
for (String s : title) {
System.out.print(s + " ");
}
// 对读取Excel表格内容测试
InputStream is2 = new FileInputStream("d:\\test2.xls");
String[][] arr = excelReader.readExcelContent(is2);
List<Excel> list=new ArrayList<Excel>();
System.out.println("获得Excel表格的内容:");
for (int i = 1; i < arr.length; i++) {
Excel exc=new Excel();//将数据转换成对象
exc.setId(arr[i][0]);//添加数据
exc.setName(arr[i][1]);
exc.setSalary(arr[i][2]);
list.add(exc);
for(int j=0;j<arr[i].length;j++){
System.out.print(arr[i][j]+"\t");
}
System.out.println();
}
new ExcelDaoImpl().addList(list);
} catch (Exception e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
}
}
}
对数据库操作:
public void addList(List<Excel> list) throws Exception {
Connection conn = null;
PreparedStatement prep = null;
try {
conn = DBUtil.getConnection();
for(Excel exe:list){
prep = conn.prepareStatement(
"INSERT INTO " +
"emp(id,name,salary) " +
"VALUES(?,?,?)");
prep.setString(1, exe.getId());
prep.setString(2, exe.getName());
prep.setString(3, exe.getSalary());
}
prep.executeUpdate();
} catch (Exception e1) {
e1.printStackTrace();
throw e1;
}finally{
DBUtil.close(conn);
}
}
数据库连接工具类:
package com.sanji.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
/**
* 创建连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception{
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");//注册驱动
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf8",//数据库连接
"root",//用户
"1234");//密码
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return conn;
}
/**
* 关闭连接
* @param conn
*/
public static void close(Connection conn){
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
}
}
}
/**
* 测试
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
System.out.println(conn);
}
}
读取的Excel文件:
id name salary
1 武松 3463
2 和尚 3464
3 尼姑 456
4 道士 3466
5 大王 345
6 二王 5
7 三王 456
8 十分感激 3470
9 水电费 45
10 斯蒂芬妮 3472
11 说的 6
12 就是 3474
13 s发给 3475
14 都会更好 563
15 共和国 54
16 回家 3478