Excel导出之poi详解（实例代码）

POI简介
POI是Apache软件基金会用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。通常被我们用来操作Excel相关文件。
这里记录一下poi导出功能的调用实例
jar包

maven依赖

方法调用

	@Remark("日志导出")
	@OptionalLog(module="邮件日志", methods="日志导出")
	@RequestMapping(value = "/export", method = RequestMethod.GET)
    public void exportExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {
		String resellerName = request.getParameter("resellerName");
		String deployToName = request.getParameter("deployToName");
		String isOpenFlag = request.getParameter("isOpenFlag");
		String tss = request.getParameter("tss");
		String tse = request.getParameter("tse");
		
		List<MailLog> list =  mailLogService.getlogsBySearch(resellerName,deployToName,isOpenFlag,tss,tse);
		
		String fileName ="邮件日志明细.xlsx";
		String header = "经销商,最终用户名称,邮件主题,创建时间,是否打开,打开次数";
		String[] csvHeader = header.split(",");		
		
		response.addHeader("Content-Disposition",
				"attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
		response.setContentType("application/octet-stream;charset=UTF-8");
		ServletOutputStream out = response.getOutputStream();
		List<MailLogDownload> md = new ArrayList<MailLogDownload>();
		MultiPartFormUtil.ExportExcel<MailLogDownload> exportExcel = new MultiPartFormUtil().new ExportExcel<MailLogDownload>();
		
		for (MailLog mailLog : list) {
			MailLogDownload m = new MailLogDownload();
		    m.setDeployToName(mailLog.getDeployToName());
		    if(mailLog.getIsOpenFlag()){
		    	m.setIsOpenFlag("已打开");
		    }else{
		    	m.setIsOpenFlag("未打开");
		    }
		    m.setOpenTimes(mailLog.getOpenTimes());
		    m.setResellerName(mailLog.getResellerName());
		    m.setSubject(mailLog.getSubject());
		    SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		    m.setTs(sf.format(mailLog.getTs()));
		    md.add(m);		
		}
		exportExcel.exportExcel(fileName, csvHeader, md, out, "YYYY-MM-DD");
	}

工具类
MultiPartFormUtil.java

package com.rs.common.excel;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
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.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

/**
 * 多媒体表单工具类
 * 
 * @author Administrator
 *
 */
public class MultiPartFormUtil {

	/**
	 * 读取excel文件，返回单列数据的字符串数组
	 * 
	 * @param myfiles
	 * @param request
	 * @return
	 * @throws IOException
	 */
	public static Map<String,String> loadData(MultipartFile myfile, HttpServletResponse response) throws IOException {
		StringBuilder sb = new StringBuilder();
		Map<String,String> map =new HashMap<>();
		List<String> list=new ArrayList<String>();
		if (myfile.isEmpty()) {
			response.setStatus(HttpServletResponse.SC_BAD_REQUEST);
			return null;
		} else {
			InputStream stream = myfile.getInputStream();
			Workbook wb = new XSSFWorkbook(stream);
            //int i=0;
			Sheet sheet0 = wb.getSheetAt(0);
			for (Row row : sheet0) {
				if(row.getRowNum()<=0) continue; //跳过第一行标题
				Cell cell = row.getCell(0);
			    Cell cell2=row.getCell(1);
			    String rawData = numberToString(cell);
				String rowData = numberToString(cell2);
				Cell cell3 = row.getCell(2);
				String posNo;
				
				if(cell3==null){
					posNo="0000";
				}else{
					// 取值后会带一个E的问题  
					double cellValue = cell3.getNumericCellValue();   
					posNo=new DecimalFormat("#").format(cellValue); 
					if(posNo.equals("0")){
						posNo="0000";
					}
				}
				rowData=rowData+"!"+posNo;
				//double posNo = cell3.getNumericCellValue();/*.toString()*/
				if (!rawData.isEmpty()) {
					//sb.append(rawData).append(":").append(rowData).append(",");
					map.put(rawData, rowData);
				
					sb.delete( 0, sb.length());
				}      
			}
			return map;
			/*if (sb.length() > 0) {
				//map.put("posSnType", sb.substring(0, sb.length() - 1).toString());
				return map;
			} else {
				return null;
			}*/
		}
	}


	private static String numberToString(Cell cell) {
		String stringValue;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC:
			double numberValue = cell.getNumericCellValue();
			stringValue = new DecimalFormat("#").format(numberValue);
			break;
		case Cell.CELL_TYPE_STRING:
			stringValue = cell.getStringCellValue().trim();
			break;
		default:
			stringValue = "";
			break;
		}
		return stringValue;
	}

	public class ExportExcel<T> {  
	    public void exportExcel(Collection<T> dataset, OutputStream out){  
	        exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd");  
	    }  
	  
	    public void exportExcel(String[] headers, Collection<T> dataset,  
	            OutputStream out){  
	        exportExcel("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");  
	    }  
	  
	    public void exportExcel(String[] headers, Collection<T> dataset,  
	            OutputStream out, String pattern){  
	        exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern);  
	    }  
	    
	    public void exportExcel(String[] headers, Collection<T> dataset,  
	    		OutputStream out, String pattern,String ss){  
	    	exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern);  
	    }  
	    
	    
	    @SuppressWarnings("unchecked")  
	    public void exportExcel(String title, String[] headers,  
	            Collection<T> dataset, OutputStream out, String pattern){
	        // 声明一个工作薄
			SXSSFWorkbook workbook = new SXSSFWorkbook();
			// 生成一个表格
			Sheet sheet = workbook.createSheet(title);
			// 设置表格默认列宽度为15个字节
			sheet.setDefaultColumnWidth((short) 15);
			// 生成一个样式
			CellStyle style = workbook.createCellStyle();
			// 设置这些样式
			style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 生成一个字体
			Font font = workbook.createFont();
			font.setColor(HSSFColor.VIOLET.index);
			font.setFontHeightInPoints((short) 12);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			// 把字体应用到当前的样式
			style.setFont(font);
			// 生成并设置另一个样式
			CellStyle style2 = workbook.createCellStyle();
			style2.setFillForegroundColor(HSSFColor.WHITE.index);
			style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			// 生成另一个字体
			Font font2 = workbook.createFont();
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
			// 把字体应用到当前的样式
			style2.setFont(font2);
			Font font3 = workbook.createFont();
			font3.setColor(HSSFColor.BLACK.index);

			// 声明一个画图的顶级管理器
//	        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
////	        // 定义注释的大小和位置,详见文档
////	        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
////	                0, 0, 0, (short) 4, 2, (short) 6, 5));
////	        // 设置注释内容
////	        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
////	        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
////	        comment.setAuthor("leno");

			// 产生表格标题行
			Row row = sheet.createRow(0);
			for (short i = 0; i < headers.length; i++){
				Cell cell = row.createCell(i);
				cell.setCellStyle(style);
				XSSFRichTextString text = new XSSFRichTextString(headers[i]);
				cell.setCellValue(text);
			}
	  
	        // 遍历集合数据，产生数据行  
	        Iterator<T> it = dataset.iterator();  
	        int index = 0;  
	        while (it.hasNext()){
	            index++;
	            row = sheet.createRow(index);
	            T t = (T) it.next();
	            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
	            Field[] fields = t.getClass().getDeclaredFields();
	            for (short i = 0; i < fields.length; i++){
					Cell cell = row.createCell(i);
	                cell.setCellStyle(style2);
	                Field field = fields[i];
	                String fieldName = field.getName();
	                String getMethodName = "get"
	                        + fieldName.substring(0, 1).toUpperCase()
	                        + fieldName.substring(1);
	                try {
	                    Class tCls = t.getClass();
	                    Method getMethod = tCls.getMethod(getMethodName,
	                            new Class[]
	                            {});
	                    Object value = getMethod.invoke(t, new Object[]
	                    {});
	                    // 判断值的类型后进行强制类型转换
	                    String textValue = null;
	                    if (value instanceof Date){
	                        Date date = (Date) value;
	                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
	                        textValue = sdf.format(date);
	                    }else if(value==null||"".equals(value)){

	                    } else {
	                        // 其它数据类型都当作字符串简单处理
	                        textValue = value.toString();
	                    }
	                    if (textValue != null) {
	                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");
	                        Matcher matcher = p.matcher(textValue);
	                        if (matcher.matches()) {
	                            // 是数字当作double处理
	                            cell.setCellValue(Double.parseDouble(textValue));
	                        } else {
	                            XSSFRichTextString richString = new XSSFRichTextString(
	                                    textValue);
	                            richString.applyFont(font3);
	                            cell.setCellValue(richString);
	                        }
	                    }
	                }catch (SecurityException e){
	                    e.printStackTrace();
	                }catch (NoSuchMethodException e){
	                    e.printStackTrace();
	                }catch (IllegalArgumentException e){
	                    e.printStackTrace();
	                }catch (IllegalAccessException e){
	                    e.printStackTrace();
	                }catch (InvocationTargetException e){
	                    e.printStackTrace();
	                } finally{
	                    // 清理资源
	                }
	            }
	        }
	        try  
	        {  
	            workbook.write(out);  
	        }  
	        catch (IOException e)  
	        {  
	            e.printStackTrace();  
	        }  
	    }
    }  	
}

OperationExcelUtil.java

package com.rs.common.excel;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;

public class OperationExcelUtil {
	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";// 2003版的excel文件
	public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";// 2010版的excel文件
	public static final String EMPTY = "";// 空的
	public static final String POINT = ".";// 重要
	public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");// 日期格式化

	/**
	 * 根据文件名获取后缀名
	 * 
	 * @param path
	 * @return
	 */
	public static String getPostfix(String path) {
		if (path == "" || EMPTY.equals(path.trim())) { // 判断传入的文件名是否为空
			return EMPTY;
		}
		if (path.contains(POINT)) { // 判断文件名是否包含"."
			return path.substring(path.lastIndexOf(".") + 1, path.length());// 截取后缀名
		}
		return EMPTY;
	}

	/**
	 * 单元格格式
	 * 
	 * @param hssfCell
	 * @return
	 */
	@SuppressWarnings({ "static-access", "deprecation" })
	public static String getHValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			String cellValue = "";
			if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
				Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
				cellValue = sdf.format(date);
			} else {
				DecimalFormat df = new DecimalFormat("#.##");
				cellValue = df.format(hssfCell.getNumericCellValue());
				String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
				if (strArr.equals("00")) {
					cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
				}
			}
			return cellValue;
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

	public static String getXValue(XSSFCell xssfCell) {
		if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfCell.getBooleanCellValue());
		} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
			String cellValue = "";
			if(XSSFDateUtil.isCellDateFormatted(xssfCell)){
				Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());
				cellValue=sdf.format(date);
			}else{
				DecimalFormat df=new DecimalFormat("#.##");
				cellValue=df.format(xssfCell.getNumericCellValue());
				String strArr=cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
				if(strArr.equals("00")){
					cellValue=cellValue.substring(0,cellValue.lastIndexOf(POINT));
				}
			}
			return cellValue;
		}else{
			return String.valueOf(xssfCell.getStringCellValue());
		}
	}
}

XSSFDateUtil.java

package com.rs.common.excel;

import java.util.Calendar;

import org.apache.poi.ss.usermodel.DateUtil;

public class XSSFDateUtil extends DateUtil {

	public static int absoluteDay(Calendar cal,boolean use1904windowing){
		return DateUtil.absoluteDay(cal, use1904windowing);
	}
}

结果


结尾
以上就是java使用poi导出excel的一次使用实例，文中工具类可以直接调用。