下面是Java用jxl读取excel并保存到数据库的攻略:
1. 环境准备
1.1 安装jxl、mysql-connector-java,可以在maven仓库中获取对应的依赖。
1.2 准备好JDBC连接数据库的连接信息,例如:数据库的地址、端口、用户名、密码等。
1.3 准备Excel文件。
2. 读取Excel文件
2.1 使用jxl的Workbook类读取Excel文件,例如:
File file = new File("example.xls");
Workbook workbook = Workbook.getWorkbook(file);
2.2 利用获取到的workbook对象获取sheet对象、row对象、cell对象,获取Excel表中的数据,例如:
Sheet sheet = workbook.getSheet(0);
for(int i=1; i<sheet.getRows(); i++){
Cell nameCell = sheet.getCell(0,i);
Cell ageCell = sheet.getCell(1,i);
String name = nameCell.getContents();
int age = Integer.parseInt(ageCell.getContents());
}
2.3 读取完数据后,关闭workbook对象,例如:
workbook.close();
3. 存储到数据库
3.1 使用JDBC连接数据库,例如:
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_name", "username", "password");
3.2 利用connection对象创建statement或者PreparedStatement,利用SQL语句存储数据到数据库中,例如:
String sql = "INSERT INTO user(name,age) VALUES(?,?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, name);
statement.setInt(2, age);
statement.executeUpdate();
3.3 存储完数据后,关闭statement对象和connection对象,例如:
statement.close();
connection.close();
示例一:读取Excel文件并保存到数据库
File file = new File("example.xls");
Workbook workbook = Workbook.getWorkbook(file);
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_name", "username", "password");
Sheet sheet = workbook.getSheet(0);
for(int i=1; i<sheet.getRows(); i++){
Cell nameCell = sheet.getCell(0,i);
Cell ageCell = sheet.getCell(1,i);
String name = nameCell.getContents();
int age = Integer.parseInt(ageCell.getContents());
String sql = "INSERT INTO user(name,age) VALUES(?,?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, name);
statement.setInt(2, age);
statement.executeUpdate();
statement.close();
}
workbook.close();
connection.close();
示例二:从Excel文件中读取数据并在控制台上输出
File file = new File("example.xls");
Workbook workbook = Workbook.getWorkbook(file);
Sheet sheet = workbook.getSheet(0);
for(int i=1; i<sheet.getRows(); i++){
Cell nameCell = sheet.getCell(0,i);
Cell ageCell = sheet.getCell(1,i);
String name = nameCell.getContents();
int age = Integer.parseInt(ageCell.getContents());
System.out.println("name:"+name+",age:"+age);
}
workbook.close();
以上就是Java用jxl读取excel并保存到数据库的攻略,希望能对你有所帮助。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:Java用jxl读取excel并保存到数据库的方法 - Python技术站