Springmvc poi导出excel

Easyui DataGrid Spring mvc poi 数据导出Excel，具体代码：

1、html:

<div class="btnbar-tools">
	<a href="javascript:void(0);" class="count" id="exportHnStatics"><i class="fa fa-pie-chart purple"></i>导出</a>
</div>

2、JS实现，(用Ajax无法导出)

$(document).ready(function(){
	//导出
	$('#exportHnStatics').on('click', function(){
		var mouth=$('#st_mouth').val();
		//查询参数
		param = "?mouth="+mouth;
		window.location.href = basePath+"/api/exportHnStatics"+param;
		
	});
});

3、后台pom.xml依赖添加：

<dependency>
	<groupId>commons-codec</groupId>
	<artifactId>commons-codec</artifactId>
	<version>1.10</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.directory.studio</groupId>
    <artifactId>org.apache.commons.io</artifactId>
    <version>2.4</version>
</dependency>

4、后台Controller实现：

@RequestMapping(value = "/exportHnStatics")
@ResponseBody
public void exportHnStatics(HttpServletResponse response, HttpServletRequest request, String mouth) {	
	List<Map> listData = this.dataLogService.selectLoginStatistics(params);

	//listData格式如下：
	//Map map = new HashMap<>();
	//map.put("date", "2018-09-27");
	//map.put("bjTotal", "100");
	//map.put("tjTotal", "200");
	//map.put("shTotal", "300");
	//List<Map> list = new ArrayList<>();
	//list.add(map);

	String columnNames[] = { "日期",  "北京", "天津", "上海"};// 列名
	String keys[] = { "date", "bjTotal", "tjTotal", "shTotal"};// map中的key
	String fileName = "test";
	//导出
	Map<Short, HorizontalAlignment> styleMap = new HashMap<Short, HorizontalAlignment>();
	//可以设置列样式，靠左靠右
	styleMap.put(new Short("1"), HorizontalAlignment.LEFT);
	//调用导出，数据内容为List<Map>格式
	ExportExcelUtils.exportExcel(listData, keys, columnNames, response, fileName, styleMap, request);
}

5、导出ExportExcelUtils工具类封装：

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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

import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;


/**
 * @ClassName: ExportExcelUtils
 * @Description: 导出Excel文件
 * @author sam
 * @date 2017年6月23日 上午9:45:03
 */
public class ExportExcelUtils {
	/**
	 * @Description: 创建工作簿导出
	 * @param list 导出数据集合
	 * @param keys 表头数组集合
	 * @param columnNames  导出excel里面的列名属性
	 * @param styleMap  样式列表[可以使用默认样式]
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public static Workbook createWorkbook(List<Map> list, String[] keys,
			String[] columnNames,Map<Short, HorizontalAlignment> styleMap) {
		// 创建excel工作簿
		SXSSFWorkbook wb = new SXSSFWorkbook(1000);
		// 创建第一个sheet（页），并命名
		Sheet sheet = null;
		
		// 创建两种单元格格式
		CellStyle cs = wb.createCellStyle();
		CellStyle cs2 = wb.createCellStyle();

		// 创建两种字体
		Font f = wb.createFont();
		Font f2 = wb.createFont();

		// 创建第一种字体样式（用于列名）
		f.setFontHeightInPoints((short) 10);
		f.setColor(IndexedColors.BLACK.getIndex());
		f.setBold(true);

		// 创建第二种字体样式（用于值）
		f2.setFontHeightInPoints((short) 10);
		f2.setColor(IndexedColors.BLACK.getIndex());

		// 设置第一种单元格的样式（用于列名）
		cs.setFont(f);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setAlignment(HorizontalAlignment.CENTER);

		// 设置第二种单元格的样式（用于值）
		cs2.setFont(f2);
		cs2.setBorderLeft(BorderStyle.THIN);
		cs2.setBorderRight(BorderStyle.THIN);
		cs2.setBorderTop(BorderStyle.THIN);
		cs2.setBorderBottom(BorderStyle.THIN);
		cs2.setAlignment(HorizontalAlignment.CENTER);
		
		int startNum = 0;//sheet开始行
		
		// 设置每行每列的值
		for (int i = 0; i < list.size(); i++) {
			// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
			//超过50000条新建sheet
			int num = i%50000;
			if(0==num){
				startNum = 0;
				sheet = wb.createSheet();
				// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
				for (int k = 0; k < keys.length; k++) {
					sheet.setColumnWidth((short) k, (short) (45 * 150));
				}

				// 创建第一行
				Row row = sheet.createRow((short) 0);
				// 设置列名
				for (int l = 0; l < columnNames.length; l++) {
					Cell cell = row.createCell(l);
					cell.setCellValue(columnNames[l]);
					cell.setCellStyle(cs);
				}
			}
			
			// 创建一行，在页sheet上
			Row row1 = sheet.createRow( startNum + 1);
			startNum++;
			// 在row行上创建一个方格
			for (short j = 0; j < keys.length; j++) {
				Cell cell = row1.createCell(j);
				cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
				//判断是否使用特定样式
				if (styleMap!=null && styleMap.containsKey(j)) {
					CellStyle csItem = wb.createCellStyle();
					csItem.setFont(f2);
					csItem.setBorderLeft(BorderStyle.THIN);
					csItem.setBorderRight(BorderStyle.THIN);
					csItem.setBorderTop(BorderStyle.THIN);
					csItem.setBorderBottom(BorderStyle.THIN);
					csItem.setAlignment(styleMap.get(j));
					cell.setCellStyle(csItem);
				}else{
					cell.setCellStyle(cs2);
				}
			}
		}
		return wb;
	}
	

	/**
	 * @Description: 导出Excel
	 * @param list   导出数据集合
	 * @param keys  表头集合
	 * @param columnNames  列属性集合
	 * @param styleMap  样式列表[可以使用默认样式]
	 * @param response  response请求对象
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	public static void exportExcel(List<Map> list, String keys[], String columnNames[], HttpServletResponse response,
			String fileName,Map<Short, HorizontalAlignment> styleMap,HttpServletRequest request) throws Exception {
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		// 调用工具类创建excel工作簿
		if(list==null || list.size()==0){
			createEmptyWorkbook(columnNames).write(os);
		} else {
			createWorkbook(list, keys, columnNames,styleMap).write(os);
		}
		byte[] content = os.toByteArray();
		InputStream is = new ByteArrayInputStream(content);
		// 设置response参数，可以打开下载页面
		response.reset();
		fileName = getDownloadFileName(fileName+".xlsx",request);
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setHeader("Content-Disposition",
				"attachment;filename=" + fileName);
		OutputStream out = response.getOutputStream();
		byte[] b = new byte[2048];
		int length;
		while ((length = is.read(b)) > 0) {
			out.write(b, 0, length);
		}
		// 关闭。
		os.flush();
		os.close();
		is.close();
	}

	/**
	 * @Description: List数据案例
	 * @return
	 */
	public static List<Map> getData() {

		String keys[] = { "companyName", "companyUserCount", "userCount", "notLoginCount", "useCount", "userPercent" };// map中的key

		Map map = new HashMap<>();
		map.put("companyName", 1);
		map.put("companyUserCount", "aaa");
		map.put("userCount", "北京");
		map.put("notLoginCount", "aaa");
		map.put("useCount", "北京");
		map.put("userPercent", "aaa");

		Map map2 = new HashMap<>();
		map2.put("companyName", 2);
		map2.put("companyUserCount", "bbb");
		map2.put("userCount", "武汉");
		map2.put("notLoginCount", "bbb");
		map2.put("useCount", "武汉");
		map2.put("userPercent", "bbb");

		List<Map> list = new ArrayList<>();
		list.add(map);
		list.add(map2);

		return list;
	}
	
	public static String getDownloadFileName(String fileName,HttpServletRequest request) {
		String finalFileName = null;
	    try {
	    	String userAgent = request.getHeader("USER-AGENT");
	        if(StringUtils.contains(userAgent, "MSIE")||StringUtils.contains(userAgent, "Edge")||StringUtils.contains(userAgent, "like Gecko")){//IE浏览器
	            finalFileName = URLEncoder.encode(fileName,"UTF8");
	        }else if(StringUtils.contains(userAgent, "Mozilla")){//google,火狐浏览器
	            finalFileName = new String(fileName.getBytes(), "ISO8859-1");
	        }else{
	            finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
	        }
	    } catch (Exception e) {
	    	
	    }
	    return finalFileName;
    }
	
	public static Workbook createEmptyWorkbook(String[] columnNames){
		// 创建excel工作簿
		SXSSFWorkbook wb = new SXSSFWorkbook(1000);
		// 创建第一个sheet（页），并命名
		Sheet sheet = null;
		
		// 创建两种单元格格式
		CellStyle cs = wb.createCellStyle();

		// 创建两种字体
		Font f = wb.createFont();

		// 创建第一种字体样式（用于列名）
		f.setFontHeightInPoints((short) 10);
		f.setColor(IndexedColors.BLACK.getIndex());
		f.setBold(true);
		
		cs.setFont(f);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setAlignment(HorizontalAlignment.CENTER);
		
		sheet = wb.createSheet();
		// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
		for (int k = 0; k < columnNames.length; k++) {
			sheet.setColumnWidth((short) k, (short) (45 * 150));
		}

		// 创建第一行
		Row row = sheet.createRow((short) 0);
		// 设置列名
		for (int l = 0; l < columnNames.length; l++) {
			Cell cell = row.createCell(l);
			cell.setCellValue(columnNames[l]);
			cell.setCellStyle(cs);
		}
		
		Row row2 = sheet.createRow(1);
		Cell cell = row2.createCell(0);
		cell.setCellValue("未查询到符合条件的数据");
		
		return wb;
	}
}

配置完成！