jsp(servlet)使用poi导出excel数据文件

缺乏、安全感 2024-02-17 15:22 12阅读 0赞

网站通过Excel导出数据已经成为各种网站的一个基本功能，下面就详细介绍下如何使用poi.jar方式来导出excel文件。具体步骤如下：

1、创建一个网站，下面我创建一个叫test的网站，然后将poi的jar包放入lib文件夹。我这里是下载链接[poi-2.5.1.jar][].

结构目录如下：

![Center][]

2、然后我们可以创建一个用来访问的jsp界面（主要是提交到servlet，可有可无）

代码如下：

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
      <head>
      </head>
      
      <body>
        <form action="excel">
        <input value="提交" type="submit">
        </form>
      </body>
    </html>

3、事先创建好的操作文档（可下载）ExcelFileGenerator

/**
     * 系统数据导出Excel 生成器
     * @version 1.0
     */
    package com.whp.test;
    
    import java.io.OutputStream;
    import java.util.ArrayList;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    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.hssf.util.HSSFColor;
    
    public class ExcelFileGenerator {
    
    	private final int SPLIT_COUNT = 1500; //Excel每个工作簿的行数
    
    	private ArrayList fieldName = null; //excel标题数据集
    
    	private ArrayList fieldData = null; //excel数据内容	
    
    	private HSSFWorkbook workBook = null;
    
    	/**
    	 * 构造器
    	 * @param fieldName 结果集的字段名
    	 * @param data
    	 */
    	public ExcelFileGenerator(ArrayList fieldName, ArrayList fieldData) {
    
    		this.fieldName = fieldName;
    		this.fieldData = fieldData;
    	}
    
    	/**
    	 * 创建HSSFWorkbook对象
    	 * @return HSSFWorkbook
    	 */
    	public HSSFWorkbook createWorkbook() {
    
    		workBook = new HSSFWorkbook();
    		int rows = fieldData.size();
    		int sheetNum = 0;
    
    		if (rows % SPLIT_COUNT == 0) {
    			sheetNum = rows / SPLIT_COUNT;
    		} else {
    			sheetNum = rows / SPLIT_COUNT + 1;
    		}
    
    		for (int i = 1; i <= sheetNum; i++) {
    			HSSFSheet sheet = workBook.createSheet("Page " + i);
    			HSSFRow headRow = sheet.createRow((short) 0); 
    			for (int j = 0; j < fieldName.size(); j++) {
    				HSSFCell cell = headRow.createCell((short) j);
    				//添加样式
    				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    				//添加样式
    				//设置所有单元格的宽度
    				sheet.setColumnWidth((short)j, (short)6000);
    				//创建样式(使用工作本的对象创建)
    				HSSFCellStyle cellStyle = workBook.createCellStyle();
    				//创建字体的对象
    				HSSFFont font = workBook.createFont();
    				//将字体加粗
    				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    				//设置字体的颜色
    				short color = HSSFColor.RED.index;
    				font.setColor(color);
    				//将新设置的字体属性放置到样式中
    				cellStyle.setFont(font);
    				if(fieldName.get(j) != null){
    					cell.setCellStyle(cellStyle);
    					cell.setCellValue((String) fieldName.get(j));
    				}else{
    					cell.setCellStyle(cellStyle);
    					cell.setCellValue("-");
    				}
    			}
    
    			for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
    				HSSFRow row = sheet.createRow((short) (k + 1));
    				//将数据内容放入excel单元格
    				ArrayList rowList = (ArrayList) fieldData.get((i - 1)
    						* SPLIT_COUNT + k);
    				for (int n = 0; n < rowList.size(); n++) {
    					HSSFCell cell = row.createCell((short) n);
    					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    					if(rowList.get(n) != null){
    						cell.setCellValue((String) rowList.get(n).toString());
    					}else{
    						cell.setCellValue("");
    					}
    				}
    			}
    		}
    		return workBook;
    	}
    
    	public void expordExcel(OutputStream os) throws Exception {
    		workBook = createWorkbook();
    		workBook.write(os);
    		os.close();
    	}
    
    }

4、创建一个名称为excel的servlet. ------ 通过getFieldName()和getFieldData()来模拟表格数据

package com.whp.test;
    
    import java.io.IOException;
    import java.io.OutputStream;
    import java.io.PrintStream;
    import java.io.PrintWriter;
    import java.util.ArrayList;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    public class excel extends HttpServlet {
    
    	
    	public void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		try {
    		//初始化fieldName，fieldDate
    		ArrayList fieldName=getFieldName();    //excel标题数据集
    		ArrayList fieldData=getFieldData();    //excel数据内容
            String myexcel="myexcel";
    		//回去输出流
    		OutputStream out=response.getOutputStream();
    		//重置输出流
    		response.reset();
    		//设置导出Excel报表的导出形式
    		response.setContentType("application/vnd.ms-excel");
    		response.setHeader("Content-Disposition","attachment;filename="+myexcel+".xls");
    		ExcelFileGenerator  efg=new ExcelFileGenerator(fieldName, fieldData);
    		
    			efg.expordExcel(out);
    		//设置输出形式
    		System.setOut(new PrintStream(out));
    		//刷新输出流
    		out.flush();
    		//关闭输出流
    		if(out!=null){
    			out.close();
    		}
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    
    	public void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    	     this.doGet(request, response);
    	}
    
    	//模拟提供excel中的标题数据集
    	public ArrayList getFieldName(){
    		String str[]={"姓名","学号","性别"};
    		ArrayList list=new ArrayList();
    		for(int i=0;i<str.length;i++){
    			list.add(str[i]);
    		}
    		return list;
    	}
    	//模拟提供excel中的标题数据内容
    	public ArrayList getFieldData(){
    		
    		ArrayList list1=new ArrayList();
    		String str[][]={
       {"wang","01","男"},{"hai","02","男"},{"ping","03","女"}};
    	    
    		for(int i=0;i<str.length;i++)
    		{
    			ArrayList list=new ArrayList();
    			for(int j=0;j<str[0].length;j++)
    			{
    				list.add(str[i][j]);
    			}
    			list1.add(list);
    		}
    		return list1;
    	}
    
    }

5、测试：

通过localhost:8080/test/excel.jsp访问，然后点击按钮即可得到下载文件。

测试数据结果如下：

![Center 1][]

[poi-2.5.1.jar]: http://download.csdn.net/download/wanghaiping1993/8201495
[Center]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/3a8bdb14c5434e03801f17fdc881684e.png
[Center 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/7601549080c64807964a50c30948cfd7.png