A Simple POI3.8 Excel Download and Upload in Java Web

来源：互联网发布：美工刀片厚度编辑：程序博客网时间：2024/06/05 02:05

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 sheetSheet s = wb.createSheet();// create a cellstyleCellStyle cs = wb.createCellStyle();Font f = wb.createFont();f.setBoldweight(Font.BOLDWEIGHT_BOLD);cs.setFont(f);// create first rowRow 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 rowsint 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 />");}}}