A Simple POI3.8 Excel Download and Upload in Java Web
白君之
POI基本可以生成Office的任何文档，Word、Excel、PowerPoint、Visio等。
POI官网：
http://poi.apache.org/
可以直接用下面的网址下载POI3.8
http://labs.renren.com/apache-mirror/poi/release/bin/poi-bin-3.8-20120326.zip
下载的文件中包含了所需要的jar包以及详细的帮助文档。
为了实现文件的上传功能，需要下载commons-fileupload
官方网站：
http://commons.apache.org/fileupload/
可以直接用下面的网址下载commons-fileupload-1.2.2
http://mirror.bit.edu.cn/apache//commons/fileupload/binaries/commons-fileupload-1.2.2-bin.zip
使用帮助可以在官网上查看
使用commons-fileupload还需要commons-io的支持
commons-io的官方网站：
http://commons.apache.org/io/
可以直接用下面的网址下载commons-io-2.2
http://labs.renren.com/apache-mirror//commons/io/binaries/commons-io-2.2-bin.zip
项目环境
JDK1.6 Tomcat6.0 STS2.5.1
项目所需库
commons-fileupload-1.2.2.jar
dom4j-1.6.1.jar
poi-3.8-20120326.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
xmlbeans-2.3.0.jar
另外commons-fileupload-1.2.2.jar和commons-io-2.2.jar需要放到Tomcat服务器的lib中。
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Index</title>
</head>
<body>
<form name="form1" action="DownloadServlet" method="post">
<input type="submit" value="download" />
</form>
<form name="form2" action="UploadServlet" method="post" enctype="multipart/form-data">
<input type="file" name="fileName" />
<input type="submit" value="upload" />
</form>
</body>
</html>
DownloadServlet.java
package servlets;
//import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
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.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class DownloadServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test", "root", "123456");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from users");
try {
exportExcel("测试报表Testing Report.xlsx",rs,response);
} catch (Exception e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
conn = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (rs != null) {
rs.close();
rs = null;
}
} catch (java.sql.SQLException e) {
e.printStackTrace();
}
}
}
private void exportExcel(String filename,ResultSet rs,HttpServletResponse response) throws Exception{
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
Workbook wb = null;
if(filename.toUpperCase().endsWith(".XLS")){
wb = new HSSFWorkbook();
}else if(filename.toUpperCase().endsWith(".XLSX")){
wb = new XSSFWorkbook();
}else{
throw new Exception("filename is:"+filename +" filename should end with .xls or .xlsx");
}
// create a new sheet
Sheet s = wb.createSheet();
// create a cellstyle
CellStyle cs = wb.createCellStyle();
Font f = wb.createFont();
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
cs.setFont(f);
// create first row
Row r = s.createRow(0);
for(int i = 0; i < columnCount; i++){
Cell c = r.createCell(i);
c.setCellStyle(cs);
c.setCellValue(md.getColumnName(i+1));
}
// create few rows
int rowNum = 1;
while(rs.next()){
Row row = s.createRow(rowNum++);
for(int i = 0; i < columnCount; i++){
Cell c = row.createCell(i);
c.setCellValue(rs.getString(i+1));
}
}
response.setCharacterEncoding("utf-8");
String saveFileName = URLEncoder.encode(filename,"utf-8");
OutputStream out = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition","attachment;filename=" + saveFileName);
//response.setContentType("application/ms-excel");
wb.write(out);
out.close();
// below used for applications
//FileOutputStream out = new FileOutputStream(filename);
//wb.write(out);
//out.close();
}
}
UploadServlet.java
package servlets;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUpload;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class UploadServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
importExcel(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}
private void importExcel(HttpServletRequest request, HttpServletResponse response) throws Exception{
Workbook wb = null;
InputStream in = null;
DiskFileItemFactory factory = new DiskFileItemFactory();
FileUpload fileUpload = new FileUpload(factory);
@SuppressWarnings({ "rawtypes", "deprecation" })
List items = fileUpload.parseRequest(request);
@SuppressWarnings("rawtypes")
Iterator iter = items.iterator();
while(iter.hasNext()){
FileItem item = (FileItem)iter.next();
if(!item.isFormField()){
in = item.getInputStream();
}
}
wb = WorkbookFactory.create(in);
Sheet s = wb.getSheetAt(0);
int lastRowNum = s.getLastRowNum();
PrintWriter out = response.getWriter();
for(int i = 0; i<= lastRowNum; i++){
Row r = s.getRow(i);
for(int k=0; k < r.getLastCellNum(); k++){
out.write(r.getCell(k).toString());
}
out.write("<br />");
}
}
}
类似资料：