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

偏执的太偏执、 2022-05-27 09:14 112阅读 0赞

#### 准备步骤： ####

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

2.地址：[http://mirrors.tuna.tsinghua.edu.cn/apache/poi/release/bin/poi-bin-3.17-20170915.zip][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. 
 	 */
 	public void destroy() {
 		super.destroy(); // Just puts "destroy" string in log
 		// Put your code here
 	}
 
 	/**
 	 * The doGet method of the servlet. 
 	 *
 	 * 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. 
 	 *
 	 * 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. 
 	 *
 	 * 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. 
 	 *
 	 * @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

####     ####

[http_mirrors.tuna.tsinghua.edu.cn_apache_poi_release_bin_poi-bin-3.17-20170915.zip]: http://mirrors.tuna.tsinghua.edu.cn/apache/poi/release/bin/poi-bin-3.17-20170915.zip