Apache POI 操作Excel文件：
POI简介：
Apache POI[1] 是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。
web开发中，经常会遇到数据的导入和导出功能。我们通过POI技术操作一定规则的Excel文档就可以完成对所需功能的实现。例如，某模块报表的导出，某模块数据的录入等。
POI可以很好的操作2003版的xls文件以及2007版的xlsx文件，所这也是选择Apache POI的原因。
下载下来目录结构如下：
我们Eclipse 新建java project 项目后，把以下jar文件引入到lib里即可使用。
- poi-3.16.jar
- poi-ooxml-3.16.jar
- poi-ooxml-schemas-3.16.jar
- curvesapi-1.04.jar
- xmlbeans-2.6.0.jar
- commons-collections4-4.1.jar
也就是根目录的poi-3.16.jar，poi-ooxml-3.16.jar，poi-ooxml-schemas-3.16.jar，和ooxml-lib目录下的curvesapi-1.04.jar，xmlbeans-2.6.0.jar以及lib目录下的commons-collections4-4.1.jar
创建数据库以及链接数据库：
Mysql 语句:
CREATE TABLE user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
address VARCHAR(30),
email VARCHAR(20),
phone VARCHAR(20),
age INT,
pass VARCHAR(20));
JDBCutils,记得引入mysql驱动包:
public class JDBCutils {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
简单测试，从数据库中读取数据然后写入到excel文件中，代码如下
@org.junit.Test
public void testWrite() throws SQLException{
//先从数据库读取数据
String sql = "select * from user";
ResultSet executeQuery = JDBCutils.getConnection().createStatement().executeQuery(sql);
ArrayList<UserBean> arrayList = new ArrayList<UserBean>();
//将数据封装成java bean放到集合中，方便后续使用
while(executeQuery.next()){
int id = executeQuery.getInt(1);
String name = executeQuery.getString(2);
String address = executeQuery.getString(3);
String email = executeQuery.getString(4);
String phone = executeQuery.getString(5);
int age = executeQuery.getInt(6);
String password = executeQuery.getString(7);
arrayList.add(new UserBean(id, name, address, email, phone, password, age));
}
//开始写入到excel文件中
//1.创建工作薄
XSSFWorkbook book = new XSSFWorkbook();
//2.创建工作布
XSSFSheet createSheet = book.createSheet();
//3.title --- 用户管理列表
XSSFCellStyle titleStyle = book.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
XSSFFont titleFont = book.createFont();
titleFont.setBold(true);
titleFont.setFontHeightInPoints((short) 20);
titleStyle.setFont(titleFont);
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 6);
createSheet.addMergedRegion(region);
XSSFCell createCell = createSheet.createRow(0).createCell(0);
createCell.setCellValue("用户管理列表");
createCell.setCellStyle(titleStyle);
//4.设置列描述
XSSFRow headerRow = createSheet.createRow(2);
headerRow.createCell(0).setCellValue("用户ID");
headerRow.createCell(1).setCellValue("名称");
headerRow.createCell(2).setCellValue("地址");
headerRow.createCell(3).setCellValue("邮箱");
headerRow.createCell(4).setCellValue("手机号码");
headerRow.createCell(5).setCellValue("年龄");
headerRow.createCell(6).setCellValue("密码");
for(int i = 0; i < arrayList.size() ; i ++){
//5.创建行
XSSFRow createRow = createSheet.createRow(i+3);
UserBean bean = arrayList.get(i);
//6.设置每列的数据
createRow.createCell(0).setCellValue(bean.getId());
createRow.createCell(1).setCellValue(bean.getName());
createRow.createCell(2).setCellValue(bean.getAddress());
createRow.createCell(3).setCellValue(bean.getEmail());
createRow.createCell(4).setCellValue(bean.getPhone());
createRow.createCell(5).setCellValue(bean.getAge());
createRow.createCell(6).setCellValue(bean.getPassword());
}
try {
//7.写入，这里测试使用XSSFWorkbook对象写xls以及xlsx可以
FileOutputStream fileOutputStream = new FileOutputStream(new File("D://test.xls"));
book.write(fileOutputStream);
book.close();
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
运行以后，打开文件进行测试
读取测试，把刚才写入的Excel文件在读取出来，然后写入到数据库中。
@org.junit.Test
public void textRead() throws FileNotFoundException, IOException, SQLException{
String filePath = "D://test.xlsx";
FileInputStream input = new FileInputStream(new File(filePath));
Workbook workBook = null;
//读取xls和xlsx文件的时候，使用的实例对象并不一样，我们这里进行判断
if(filePath.endsWith(".xlsx")){ // 2007版本的excel
workBook = new XSSFWorkbook(input);
}else{ //2003版的excel
workBook = new HSSFWorkbook(input);
}
ArrayList<UserBean> arrayList = new ArrayList<UserBean>();
//获取工作布
int numberOfSheets = workBook.getNumberOfSheets();
for(int i = 0; i < numberOfSheets ; i ++){
Sheet sheetAt = workBook.getSheetAt(i);
int lastRowNum = sheetAt.getLastRowNum();
//根据规则，我们从第4行开始读取
for(int j = 3 ; j <= lastRowNum ; j ++){
Row row = sheetAt.getRow(j);
int id = (int) row.getCell(0).getNumericCellValue();
String username = row.getCell(1).getStringCellValue();
String address = row.getCell(2).getStringCellValue();
String email = row.getCell(3).getStringCellValue();
String phone = row.getCell(4).getStringCellValue();
int age = (int) row.getCell(5).getNumericCellValue();
String pass = row.getCell(6).getStringCellValue();
arrayList.add(new UserBean(id,username,address,email,phone,pass,age));
}
}
input.close();
input = null;
workBook.close();
workBook = null;
//写入到数据库中
Connection connection = JDBCutils.getConnection();
String sql = "insert into user(username,phone,email,address,age,pass) value(?,?,?,?,?,?)";
PreparedStatement prepareStatement = connection.prepareStatement(sql);
for(int i = 0 ;i < arrayList.size(); i ++){
UserBean bean = arrayList.get(i);
prepareStatement.setString(1, bean.getName());
prepareStatement.setString(2, bean.getPhone());
prepareStatement.setString(3, bean.getEmail());
prepareStatement.setString(4, bean.getAddress());
prepareStatement.setInt(5, bean.getAge());
prepareStatement.setString(6, bean.getPassword());
//添加到批处理中
prepareStatement.addBatch();
}
//执行批处理
int[] executeBatch = prepareStatement.executeBatch();
System.out.println(executeBatch);
}
查看数据库中，数据正确的写入了进去，到此基本的POI操作完毕。
