欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Springmvc poi导出excel

程序员文章站 2022-07-13 13:16:01
...

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;
	}
}

配置完成!