本章将使用Apache POI的功能
一、 POI简介
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。二、 HSSF概况
HSSF 是Horrible SpreadSheet Format的缩写,通过HSSF,你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API:usermodel和eventusermodel,即“用户模型”和“事件-用户模型”。
三、 POI EXCEL文档结构类
HSSFWorkbook excel文档对象
HSSFSheet excel的sheet
HSSFRow excel的行
HSSFCell excel的单元格
HSSFCell excel的单元格
HSSFFont excel字体
HSSFName 名称
HSSFName 名称
HSSFDataFormat 日期格式
HSSFHeader sheet头
HSSFFooter sheet尾
HSSFCellStyle cell样式
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
基本介绍介绍完了,那么通过小例子来理解真正的用法:
HSSFHeader sheet头
HSSFFooter sheet尾
HSSFCellStyle cell样式
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
基本介绍介绍完了,那么通过小例子来理解真正的用法:
步骤说明
1、代码请参考—— 第十八篇JDBC操作数据库之二级菜单联动。2、在第十八篇基础上继续实现功能——第十九篇JDBC操作数据库之导出Excel。
一、pom.xml
导入org.apache.poi包
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
二、ExcelDao.java
package com.model;
import com.dbutil.Dbconn;
import com.entity.Book;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
/**
* Created by Ray on 2018/3/19 0019.
**/
public class ExcelDao {
Dbconn dbconn = new Dbconn();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
HSSFWorkbook hssfWorkbook = null;
/**
* @Author: Ray
* @Date: 2018/3/19 0019
* @Description: 导出Excel
* @Return: HSSFWorkbook
*/
public HSSFWorkbook Output() {
try {
conn = dbconn.getConnection();
//查询语句
String sql = "select * from booktable";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
//创建Excel文档
hssfWorkbook = new HSSFWorkbook();
//生成一个表格-->表名为"图书信息"
HSSFSheet hssfSheet = hssfWorkbook.createSheet("图书信息");
//产生表格标题行
HSSFRow row = hssfSheet.createRow(0);
//每列信息
row.createCell(0).setCellValue("编号");
row.createCell(1).setCellValue("图书名");
row.createCell(2).setCellValue("价格");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("作者");
//跳过标题行
int index = 1;
while (rs.next()) {
//下一行开始
row = hssfSheet.createRow(index++);
//每列信息
row.createCell(0).setCellValue(rs.getInt("id"));
row.createCell(1).setCellValue(rs.getString("name"));
row.createCell(2).setCellValue(rs.getDouble("price"));
row.createCell(3).setCellValue(rs.getInt("bookCount"));
row.createCell(4).setCellValue(rs.getString("author"));
}
//关闭连接
rs.close();
ps.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return hssfWorkbook;
}
}
三、OutputExcel.java
package com.control;
import com.dbutil.Dbconn;
import com.entity.Book;
import com.model.ExcelDao;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
/**
* Created by Ray on 2018/3/19 0019.
**/
public class OutputExcel extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//设置上下文类型
response.setContentType("application/vnd.ms-excel");
//设置响应头
response.setHeader("Content-Disposition","attachment;filename='test.xls'");
//调用模型
ExcelDao excelDao = new ExcelDao();
//调用方法
HSSFWorkbook hssfWorkbook = excelDao.Output();
//执行
hssfWorkbook.write(response.getOutputStream());
}
}
四、web.xml
<servlet>
<servlet-name>OutputExcel</servlet-name>
<servlet-class>com.control.OutputExcel</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>OutputExcel</servlet-name>
<url-pattern>/OutputExcel</url-pattern>
</servlet-mapping>
五、页面效果
<li><a href="<%=request.getContextPath()%>/OutputExcel">导出Excel</a></li>
ok!