POI导出工具类的实现

最新推荐文章于 2024-01-02 11:29:15 发布

原创最新推荐文章于 2024-01-02 11:29:15 发布 · 302 阅读

0 ·

CC 4.0 BY-SA版权

POI 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了一款用于导出Excel文件的工具类，包括针对2003版和2007+版的不同实现方式。该工具类支持批量数据导出至Excel，并能自定义表头，适用于Java Web开发中的报表导出需求。

2003版本的（HSSFWorkbook ） 2007+（XSSFWorkbook）

注意：2007工具类获取contentType的方式如下：

 String contentType = request.getSession().getServletContext().getMimeType(fileName);

2003版本的（HSSFWorkbook ）工具类如下：

package com.ocean.utils;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

/**
 * Excel导出工具类
 * @Date 2018-10-31	 
 */
public class ExcelExport extends AbstractExcelView {

	    private String[] titles;
	    
	    //传入指定的标题头
	    public ExcelExport(String[] titles) {
	        this.titles=titles;
	    }
	    
	    public void buildExcel(List<Map<String,String>> list,
	            HSSFWorkbook workbook, HttpServletRequest request,
	            HttpServletResponse response) throws Exception {
	        //在workbook添加一个sheet
	        HSSFSheet sheet = workbook.createSheet();
	        sheet.setDefaultColumnWidth(15);
	        HSSFCell cell=null;
	        //遍历标题
	        for (int i = 0; i < titles.length; i++) {
	            //获取位置
	            cell = getCell(sheet, 0, i);
	            setText(cell, titles[i]);
	        }
	        //数据写出
	        for (int i = 0; i < list.size(); i++) {
	            //获取每一个map
	            Map<String, String> map=list.get(i);
	            //一个map一行数据
	            HSSFRow row = sheet.createRow(i+1);
	            for (int j = 0; j < titles.length; j++) {
	                //遍历标题，把key与标题匹配
	                String title=titles[j];
	                //判断该内容存在mapzhong
	                if(map.containsKey(title)){
	                    row.createCell(j).setCellValue(map.get(title));
	                }
	            }
	        }
	         //设置下载时客户端Excel的名称     
	        String fileName = "导出"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";
	      //转码，免得文件名中文乱码
	        fileName = URLEncoder.encode(fileName,"UTF-8");
	        response.setContentType("application/vnd.ms-excel");     
	        response.setHeader("Content-disposition", "attachment;filename=" + fileName); 
	        OutputStream ouputStream = response.getOutputStream();     
	        workbook.write(ouputStream);     
	        ouputStream.flush();     
	        ouputStream.close();     
	    }

		@Override
		protected void buildExcelDocument(Map<String, Object> arg0,
				HSSFWorkbook arg1, HttpServletRequest arg2,
				HttpServletResponse arg3) throws Exception {
			// TODO Auto-generated method stub
			
		}

}

ExcelExport.java的buildExcel方法
使用实例如下：

List<Map<String,String>> list = memberStdsService.selectMemberStds(memberStds);
String[] titles={"标准号","标准名称","标准状态","实施日期"};
ExcelExport excelExport = new ExcelExport(titles);    //实例化该工具类的时候，传入String数组，告诉其Excel的表头
try {
	excelExport.buildExcel(list,workbook, request,response);     //list是数据泛型是Map
} catch (Exception e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}

//下边来看一看List<Map<String,String>>是如何构造出来的

List<MemberStds> memberStdsList = memberStdsMapper.select(memberStds);
List<Map<String, String>> mapList=new ArrayList<Map<String,String>>();
for (MemberStds memberstd : memberStdsList) {
	Map<String, String> map=new HashMap<String, String>();
	map.put("标准号", memberstd.getA100());
	map.put("标准名称", memberstd.getA298());
	if("W".equals(memberstd.getA200())){
		map.put("标准状态", "作废");
	}else if("N".equals(memberstd.getA200())){
		map.put("标准状态", "未生效");
	}else if("D".equals(memberstd.getA200())){
		map.put("标准状态", "草案");
	}else{
		map.put("标准状态", "有效");
	}
	if(!"".equals(memberstd.getA205())&&memberstd.getA200()!=null){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		map.put("实施日期", sdf.format(memberstd.getA205())+"");
	}else{
		map.put("实施日期", "");
	}
	mapList.add(map);
	
}
return mapList;

Map中存着数据，key和表头保持一致，value是这一列表头的值，存在多行数值，使用list包裹。

2007（XSSFWorkbook）及以上的版本工具类如下：

import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;


public class ExcelExport extends AbstractExcelView {

	    private String[] titles;
	    
	    //传入指定的标题头
	    public ExcelExport(String[] titles) {
	        this.titles=titles;
	    }
	    
	    public void buildExcel(List<Map<String,String>> list,
	    		XSSFWorkbook  workbook, HttpServletRequest request,
	            HttpServletResponse response) throws Exception {
	        //在workbook添加一个sheet
	        Sheet sheet = workbook.createSheet();
	        sheet.setDefaultColumnWidth(15);
	        Cell cell=null;
	        Row row0 = sheet.createRow(0);   //创建第一行
	        //遍历标题
	        for (int i = 0; i < titles.length; i++) {
	            cell=row0.createCell(i);
	        	cell.setCellValue(titles[i]);
	        }
	        //数据写出
	        for (int i = 0; i < list.size(); i++) {
	            //获取每一个map
	            Map<String, String> map=list.get(i);
	            //一个map一行数据
	            Row row = sheet.createRow(i+1);
	            for (int j = 0; j < titles.length; j++) {
	                //遍历标题，把key与标题匹配
	                String title=titles[j];
	                //判断该内容存在mapzhong
	                if(map.containsKey(title)){
	                    row.createCell(j).setCellValue(map.get(title));
	                }
	            }
	        }
	         //设置下载时客户端Excel的名称     
	        String fileName = System.currentTimeMillis()+"_"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xlsx";
	      //转码，免得文件名中文乱码
	        fileName = URLEncoder.encode(fileName,"UTF-8");
	        String contentType = request.getSession().getServletContext().getMimeType(fileName);
	        response.setContentType(contentType);   //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
	        System.out.println(contentType);
	        response.setHeader("Content-disposition", "attachment;filename=" + fileName); 
	        OutputStream ouputStream = response.getOutputStream();     
	        workbook.write(ouputStream);     
	        ouputStream.flush();     
	        ouputStream.close();     
	    }

		@Override
		protected void buildExcelDocument(Map<String, Object> arg0,
				HSSFWorkbook arg1, HttpServletRequest arg2,
				HttpServletResponse arg3) throws Exception {
			// TODO Auto-generated method stub
			
		}

}