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

最新推荐文章于 2023-07-13 11:29:46 发布

abrahu

最新推荐文章于 2023-07-13 11:29:46 发布

阅读量2.8k

点赞数

分类专栏：报表文章标签： download upload excel java exception powerpoint

本文链接：https://blog.csdn.net/abrahu/article/details/7450714

版权

报表专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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 />");
		}
		
	}

}

abrahu

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
A Simple POI3.8 Excel Download and Upload in Java Web

POI基本可以生成Office的任何文档，Word、Excel、PowerPoint、Visio等。POI官网：http://poi.apache.org/可以直接用下面的网址下载POI3.8http://labs.renren.com/apache-mirror/poi/release/bin/poi-bin-3.8-20120326.zip下载的文件中包含了所需要的jar包
复制链接

扫一扫