javaweb中如何使用POI把数据导出为Excel(有下载提示框)详细教程

准备步骤:

1.首先要在Apache官网上下载poi的jar包(以poi-3.17为例)

2.地址:http://mirrors.tuna.tsinghua.edu.cn/apache/poi/release/bin/poi-bin-3.17-20170915.zip

3.下载完成后解压文件

4.将里面的jar包都引到项目中去,也可以单独引poi的jar包

示例代码:

package com.lzy.Controller;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
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.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import com.lzy.dao.LoginDao;
import com.lzy.dao.UserDao;
import com.lzy.entity.LoginLog;
import com.lzy.entity.Users;
import com.lzy.imp.LoginDaoimpl;
import com.lzy.imp.UserDaoimpl;

public class Excel extends HttpServlet {

	/**
	 * Constructor of the object.
	 */
	public Excel() {
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		this.doPost(request, response);
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse res)
			throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		res.setCharacterEncoding("UTF-8");
		// 以下为生成Excel操作
		// 创建一个workbook,对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 在workbook中添加一个sheet,对应Excel中的一个sheet
		HSSFSheet sheet = wb.createSheet("XXX表");
		// 在sheet中添加表头第0行,老版本poi对excel行数列数有限制short
		HSSFRow row = sheet.createRow((int) 0);
		// 创建单元格,设置值表头,设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		// 居中格式
		style.setAlignment(HorizontalAlignment.CENTER);
		// 设置表头
		List<LoginLog> logs = new ArrayList<LoginLog>();
		//接受LogListServelt传过来的日志集合
		logs = (List<LoginLog>) request.getAttribute("sr");
		String url = (String) request.getAttribute("url");
		Map<Integer,String> users = new HashMap<Integer,String>();
		users=(Map<Integer, String>) request.getAttribute("user");
		String[] titles =(String[]) request.getAttribute("title");
		HSSFCell cell;
		for(int x=0;x<titles.length;x++){
		    cell = row.createCell(x);
		    cell.setCellValue(titles[x]);
			cell.setCellStyle(style);
		}
		//生成excel格式后要将数据写入excel:
		// 循环将数据写入Excel
		      for (int i = 0; i < logs.size(); i++) {
		        row = sheet.createRow((int) i + 1);
		        LoginLog list= logs.get(i);
		        row.createCell(0).setCellValue(list.getLogId());
		        row.createCell(1).setCellValue(users.get(list.getUserId()));
		        row.createCell(2).setCellValue(list.getLoginDate());
		        row.createCell(3).setCellValue(list.getRemoteIP());
		        row.createCell(4).setCellValue(list.getRemark());
		      }
		 

		/*之后将生成的Excel以流输出。 
		*不弹出下载框
		*/
		 
		//FileOutputStream out =new FileOutputStream("E:/XXX.xls");
		//wb.write(out); 
		//out.close();
		 

		//弹出下载框

		String fileName = "example";
		      ByteArrayOutputStream os = new ByteArrayOutputStream();
		      wb.write(os);
		      byte[] content = os.toByteArray();
		      InputStream is = new ByteArrayInputStream(content);
		      // 设置response参数,可以打开下载页面
		      res.reset();
		      //设置编码格式
		      res.setContentType("application/vnd.ms-excel;charset=utf-8");
		      res.setHeader("Content-Disposition", "attachment;filename="
		          + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
		      ServletOutputStream out1 = res.getOutputStream();
		      BufferedInputStream bis = null;
		      BufferedOutputStream bos = null;
		 
		      try {
		        bis = new BufferedInputStream(is);
		        bos = new BufferedOutputStream(out1);
		        byte[] buff = new byte[2048];
		        int bytesRead;
		        // Simple read/write loop.
		        while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
		          bos.write(buff, 0, bytesRead);
		        }
		      } catch (Exception e) {
		        // TODO: handle exception
		        e.printStackTrace();
		      } finally {
		        if (bis != null)
		          bis.close();
		        if (bos != null)
		          bos.close();
		      }
		 
		     request.getRequestDispatcher(url).forward(request,res);
	}

	/**
	 * The doPut method of the servlet. <br>
	 *
	 * This method is called when a HTTP put request is received.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPut(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		// Put your code here
	}

	/**
	 * Initialization of the servlet. <br>
	 *
	 * @throws ServletException if an error occurs
	 */
	public void init() throws ServletException {
		// Put your code here
	}

}

注释:

1.这是一段我写好的servelt,用来处理转出Excel表格!

2.传入url是为了在处理完成后再跳转的页面地址

3.传入map集合是用来书写每行的列名

4.res是request



阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页