Excel导出工具类

自己整理了一个Excel导出的工具类:

package com.base.util;


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

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.HSSFFont;
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.util.CellRangeAddress;
import org.springframework.util.StringUtils;
/**
 * 
 * @param response
 * @param list里面的map含有三个属性:1:sheetTitle(excel里面的sheet的名称)
 * 							   2:head-->字符串数组格式的列名 
 * 							   3:List<Map<String, Object>> list(真实数据)
 * @param title 标题
 * @throws Exception
 */
public class ExcelUtil5 {
	
	@SuppressWarnings("deprecation")
	public static void exportExcel(HttpServletResponse response,List<Map<String, Object>> map, String title) throws Exception {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFCellStyle commonStyle = getCellStyle(workbook, "common");
		HSSFRow row = null;
		HSSFCell cell = null;
		if(map == null || map.size() == 0){
			HSSFSheet sheet = workbook.createSheet("无记录");
			createSheet(sheet, "无记录", new String[]{"无记录"}, "header");
		}else{
			for(int i = 0; i < map.size(); i++){
				String sheetTitle = (String)map.get(i).get("title");
				String[] headers = (String[])map.get(i).get("head");
				List<Map<String,Object>> cellList = (List<Map<String,Object>>)map.get(i).get("list");
				//如果sheetTitle为空，自动生成
				if(StringUtils.isEmpty(sheetTitle)){
					sheetTitle = "sheet" + new SimpleDateFormat("HH小时mm分ss秒").format(new Date());  
				}
				
				HSSFSheet sheet = workbook.createSheet(sheetTitle);
				
				//创建sheet页的标题
				createSheet(sheet, sheetTitle, headers, "header");
				//创建sheet页的列标题
				createSheet(sheet, sheetTitle, headers, "title");
				if(cellList != null && cellList.size() > 0){
					for(int j = 0; j < cellList.size(); j++){
						row = sheet.createRow(j + 2);
						row.setHeightInPoints(20);
						int num = 0;
						for (Map.Entry<String, Object> entry : cellList.get(j).entrySet()) {
							cell = row.createCell(num);
							cell.setCellStyle(commonStyle);
							Object obj = entry.getValue();
							HSSFRichTextString text = new HSSFRichTextString(obj == null ? "" : obj.toString());
							cell.setCellValue(text);
							num++;
						}
					}
				}
			}
		}
		
		String inputPath = title+".xls";  
		response.setCharacterEncoding("ISO8859-1");   
		response.setHeader("Content-Disposition", "attachment; filename=" + new String(inputPath.getBytes("GBK"), "ISO8859-1"));
		response.setHeader("Connection", "close");  
        response.setHeader("Content-Type", "application/octet-stream");
		OutputStream out=response.getOutputStream();
		workbook.write(out);
		out.flush();
		out.close();
	}
	
	private static void createSheet(HSSFSheet sheet, String sheetTitle, String[] headers, String type) {
		HSSFRow row = null;
		HSSFCell cell = null;
		HSSFCellStyle headerCellStyle = getCellStyle(sheet.getWorkbook(), "header");
		HSSFCellStyle titleCellStyle = getCellStyle(sheet.getWorkbook(), "title");
		
		if("header".equals(type)){
			row = sheet.createRow(0);
			row.setHeightInPoints(50);
			
			for(int i = 0; i < headers.length; i++){
				cell = row.createCell(i);
				sheet.setColumnWidth(i, 18 * 256);
				cell.setCellStyle(headerCellStyle);
				//将sheetTitle放在第0个单元格中
				if(i == 0){
					cell.setCellValue(sheetTitle);
				}
			}
			//合并单元格形成标题行
			sheet.addMergedRegion(new CellRangeAddress(0,0,0,headers.length - 1));
		}else{
			row = sheet.createRow(1);
			row.setHeightInPoints(30);
			for(int i = 0; i < headers.length; i++){
				
				cell = row.createCell(i);
				cell.setCellStyle(titleCellStyle);
				cell.setCellValue(headers[i]);
				
				
			}
		}	
	}

	private  static HSSFCellStyle getCellStyle(HSSFWorkbook workbook, String type){
		//生成标题样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        if("header".equals(type)){
        	font.setFontHeightInPoints((short) 24);
        	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }else if("title".equals(type)){
        	font.setFontHeightInPoints((short) 14);
        	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        	cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        }else{
        	font.setFontHeightInPoints((short) 12);
        }
        
        // 把字体应用到当前的样式
        cellStyle.setFont(font); 
        return cellStyle;
	}
}

Spring Mvc中Controller中的用法

package com.base.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.base.util.ExcelUtil;

@Controller
@RequestMapping("/test")
public class TestController {
	@RequestMapping("/excel")
	@ResponseBody
	public Object excel(HttpServletResponse response){
		List<Map<String, Object>> list=new ArrayList<Map<String,Object>>();
		String title="test";
		try {
			//第一个sheet
			Map<String, Object> listMap=new HashMap<String, Object>();
			List<Map<String, Object>> listdata=new ArrayList<Map<String,Object>>();
			Map<String, Object> map=new HashMap<String, Object>();
			map.put("t1", "t1");
			map.put("t2", "t2");
			map.put("t3", "t3");
			map.put("t4", "t4");
			Map<String, Object> map1=new HashMap<String, Object>();
			map1.put("t1", "t1");
			map1.put("t2", "t2");
			map1.put("t3", "t3");
			map1.put("t4", "t4");
			listdata.add(map);
			listdata.add(map1);
			listMap.put("title", "sheet1");
			listMap.put("head", new String[]{"列一","列二","列三","列四"});
			listMap.put("list", listdata);
			list.add(listMap);
			//第二个sheet
			Map<String, Object> listMap1=new HashMap<String, Object>();
			List<Map<String, Object>> listdata1=new ArrayList<Map<String,Object>>();
			Map<String, Object> map2=new HashMap<String, Object>();
			map2.put("t1", "t1");
			map2.put("t2", "t2");
			map2.put("t3", "t3");
			map2.put("t4", "t4");
			Map<String, Object> map3=new HashMap<String, Object>();
			map3.put("t1", "t1");
			map3.put("t2", "t2");
			map3.put("t3", "t3");
			map3.put("t4", "t4");
			listdata1.add(map2);
			listdata1.add(map3);
			listMap1.put("title", "sheet2");
			listMap1.put("head", new String[]{"列一","列二","列三","列四"});
			listMap1.put("list", listdata1);
			list.add(listMap1);
			ExcelUtil5.exportExcel(response, list, title);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}
}

效果图

源码地址:

https://github.com/LimxBoys/BaseJava/blob/master/BaseJava/src/main/java/com/base/util/ExcelUtil5.java