做了一个利用POI导出数据库数据到Excel的例子,并把这个例子与大家一起分享,一起学习,共同进步!
先简单介绍一下poi,poi是apache组织的一个开源项目,最新版为poi-3.0.1,要使用poi需要到apache网站下载poi-bin3.0.1.zip压缩包,并将poi-3.0.1.jar加入到项目的ClassPath中。
下面是完成这个实例的步骤:
1、建立好一个web工程:poi;
2、导入所需要的jar包:poi-3.0.1.jar和mysql数据库驱动包;
3、编写一个StudentResultSet类,用于取出数据库中的数据;
package org.hnylj.poi;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class StudentResultSet {
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/poi";
private static final String USERNAME = "root";
private static final String PASSWORD = "123";
//查询数据库中的数据
public ResultSet query() {
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
pstmt = conn.prepareStatement("select * from student");
rs = pstmt.executeQuery();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
}
4、编写一个BuildExcelServlet,用于生成Excel;
package org.hnylj.poi;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class BuildExcelServlet extends HttpServlet {
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
StudentResultSet stuResultSet = new StudentResultSet();
ResultSet rs = stuResultSet.query();
String xlsName = "test.xls";
String sheetName = "sheetName";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(0, sheetName);
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell;
try {
ResultSetMetaData md = rs.getMetaData();
int nColumn = md.getColumnCount();
for (int i = 1; i <= nColumn; i++) {
cell = row.createCell((short) (i - 1));
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(md.getColumnLabel(i)));
}
int iRow = 1;
while (rs.next()) {
row = sheet.createRow((short) iRow);
for (int j = 1; j <= nColumn; j++) {
cell = row.createCell((short) (j - 1));
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(rs.getObject(j)
.toString()));
}
iRow++;
}
String filename = request.getRealPath("/") + xlsName;
request.setAttribute("filename", filename);
FileOutputStream fOut = new FileOutputStream(filename);
workbook.write(fOut);
fOut.flush();
fOut.close();
request.getRequestDispatcher("OpenExcelServlet").forward(request,
response);
} catch (Exception e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
}
5、编写一个OpenExcelServlet,用于把生成的Excel在页面上打开;
package org.hnylj.poi;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class OpenExcelServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("application/vnd.ms-excel");
PrintWriter out = response.getWriter();
String filePath = (String) request.getAttribute("filename");
BufferedInputStream bis = null;
try {
bis = new BufferedInputStream(new FileInputStream(filePath));
int bytesRead;
while ((bytesRead = bis.read()) != -1) {
out.write(bytesRead);
out.flush();
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (bis != null)
bis.close();
if (out != null)
out.close();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
}
}
6、在web.xml里配置一下servlet的映射;
<servlet> <servlet-name>BuildExcelServlet</servlet-name> <servlet-class>org.hnylj.poi.BuildExcelServlet</servlet-class> </servlet> <servlet> <servlet-name>OpenExcelServlet</servlet-name> <servlet-class>org.hnylj.poi.OpenExcelServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>OpenExcelServlet</servlet-name> <url-pattern>/OpenExcelServlet</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>BuildExcelServlet</servlet-name> <url-pattern>/BuildExcelServlet</url-pattern> </servlet-mapping>
7、启动tomcat,运行程序。
一个Java技术交流群,一起交流,共同进步,扣扣群号:513086638