EasyPoi导出excel(2)

此文档主要解决自动列宽及样式设置，比上篇导出更优化；直接上代码。

接口调用：

public void exportXsStTj(HttpServletRequest request, HttpServletResponse response) throws IOException
	{
		try
		{
			List<Map<String, Object>>  list = jdbcTemplate.queryForList(" select st.D_ID_,st.STMC,st.GSTRS, st.CJSQZRS from T_XG_XSST st ");
			// 必要的空元素
			if (CollectionUtils.isEmpty(list)) {
				Map<String, Object> map = new HashMap<String, Object>();
				list.add(map);
			}
			String title = "学生社团统计";
			List<String> columnKeyList = new ArrayList<String>();
			columnKeyList.add("STMC");
			columnKeyList.add("GSTRS");
			columnKeyList.add("CJSQZRS");
			List<String> columnNameList = new ArrayList<String>();
			columnNameList.add("社团名称");
			columnNameList.add("各社团人数");
			columnNameList.add("参加社团总人数");
			
			MyExportUtils.exportExcel2(request, response, title, title, "数据", columnKeyList.toArray(new String[columnKeyList.size()]), columnNameList.toArray(new String[columnNameList.size()]), list);
		} catch (Exception e)
		{
			e.printStackTrace();
		}
	}

工具类：

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.Assert;
import org.springframework.util.ObjectUtils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
/**
 * 导出Excel工具类
 * 
 * @author ZLT
 *
 */
public class MyExportUtils {
	/**
	 * easypoi导出Excel
	 * 
	 * @param request
	 * @param response
	 * @param fileName   文件名
	 * @param title      Excel的title
	 * @param sheetName
	 * @param columnKey  导出的列的key，与listsd的key对应
	 * @param columnName 导出的列名
	 * @param lists      导出的数据
	 * @throws Exception
	 */
	@SuppressWarnings("deprecation")
	public static void exportExcel2(HttpServletRequest request, HttpServletResponse response, String fileName,
			String title, String sheetName, String[] columnKey, String[] columnName, List<Map<String, Object>> lists)
			throws Exception {

		if (StringUtils.isBlank(sheetName)) {
			throw new IllegalArgumentException("sheetTitle 为空了");
		}
		Assert.notEmpty(columnKey);

		Assert.notEmpty(columnName);

		Assert.notEmpty(lists);
		if (columnKey.length != columnName.length) {
			throw new IllegalArgumentException("列名及对应的key数量不相同（columnKey和columnName数量要相同）");
		}
		// 获取列宽
		int[] maxWidth = getMaxWidth(lists, columnKey, columnName);
		List<ExcelExportEntity> columnList = new ArrayList<ExcelExportEntity>();
		for (int i = 0, len = columnKey.length; i < len; i++) {
			ExcelExportEntity excelExportEntity = new ExcelExportEntity(columnName[i], columnKey[i], maxWidth[i]);
			excelExportEntity.setWrap(true);
			columnList.add(excelExportEntity);
		}
		// 设置样式
		ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
		exportParams.setStyle(ExcelStyle.class);
		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, columnList,
				lists);

		response.setContentType("application/octet-stream");
		response.setHeader("Connection", "close");
		// 表示不能用浏览器直接打开
		response.setHeader("Accept-Ranges", "bytes");
		// 告诉客户端允许断点续传多线程连接下载
		// 文件名乱码处理
		fileName = getNewFileName(fileName, request, response);
		response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xlsx");
		OutputStream out = response.getOutputStream();
		workbook.write(out);
		workbook.close();
	}

	/**
	 * 计算每列的最大宽度
	 * 
	 * @param lists
	 * @param columnKey
	 * @param columnName
	 * @return
	 */
	public static int[] getMaxWidth(List<Map<String, Object>> lists, String[] columnKey, String[] columnName) {
		int oneStrWidth = 2;
		int dataLen = lists.size();
		int colLen = columnKey.length;
		int[][] colWidth = new int[dataLen][colLen];
		int[] colMaxWidth = new int[columnKey.length];
		int j = 0;
		// 设置内容行
		for (Map<String, Object> list : lists) {
			int k = 0;
			for (String field : columnKey) {
				Object objVal = list.get(field);

				String strVal = ObjectUtils.isEmpty(objVal) ? "" : objVal.toString();

				// 将每个单元格的宽度存放到一个二维数据中
				colWidth[j][k] = strVal.length() * oneStrWidth;

				k++;
			}
			j++;
		}

		// 找出每列最大宽，以每列的最大宽度作为此列的列宽
		for (int m = 0; m < colLen; m++) {
			int tempWidth = 10;
			for (int n = 0; n < dataLen; n++) {
				if (tempWidth < colWidth[n][m]) {
					tempWidth = colWidth[n][m];
				}
				colMaxWidth[m] = tempWidth;
			}

		}
		// 与标题比较
		for (int m = 0; m < colLen; m++) {
			int tempWidth = columnName[m].length() * oneStrWidth;
			if (tempWidth > colMaxWidth[m]) {
				colMaxWidth[m] = tempWidth;
			}
		}
		return colMaxWidth;
	}

	/**
	 * 文件名乱码处理
	 * 
	 * @param fileName
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	public static String getNewFileName(String fileName, HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		// 文件名乱码处理
		String userAgent = request.getHeader("user-agent").toLowerCase();
		if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
			// win10 ie edge 浏览器 和其他系统的ie
			fileName = URLEncoder.encode(fileName, "UTF-8");
		} else {
			// 非ie
			fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
		}
		return fileName;
	}

}

样式类：

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
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.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;


/**
*样式类
*/
public class ExcelStyle implements IExcelExportStyler {
	private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
	private static final short FONT_SIZE_TEN = 10;
	private static final short FONT_SIZE_ELEVEN = 11;
	private static final short FONT_SIZE_TWELVE = 12;
	/**
	 * 大标题样式
	 */
	private CellStyle headerStyle;
	/**
	 * 每列标题样式
	 */
	private CellStyle titleStyle;
	/**
	 * 数据行样式
	 */
	private CellStyle styles;

	public ExcelStyle(Workbook workbook) {
		this.init(workbook);
	}

	/**
	 * 初始化样式
	 *
	 * @param workbook
	 */
	private void init(Workbook workbook) {
		this.headerStyle = initHeaderStyle(workbook);
		this.titleStyle = initTitleStyle(workbook);
		this.styles = initStyles(workbook);
	}

	/**
	 * 大标题样式
	 *
	 * @param color
	 * @return
	 */
	@Override
	public CellStyle getHeaderStyle(short color) {
		return headerStyle;
	}

	/**
	 * 每列标题样式
	 *
	 * @param color
	 * @return
	 */
	@Override
	public CellStyle getTitleStyle(short color) {
		return titleStyle;
	}

	/**
	 * 数据行样式
	 *
	 * @param parity 可以用来表示奇偶行
	 * @param entity 数据内容
	 * @return 样式
	 */
	@Override
	public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
		return styles;
	}

	/**
	 * 获取样式方法
	 *
	 * @param dataRow 数据行
	 * @param obj     对象
	 * @param data    数据
	 */
	@Override
	public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
		return getStyles(true, entity);
	}

	/**
	 * 模板使用的样式设置
	 */
	@Override
	public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
		return null;
	}

	/**
	 * 初始化--大标题样式
	 *
	 * @param workbook
	 * @return
	 */
	private CellStyle initHeaderStyle(Workbook workbook) {
		CellStyle style = getBaseCellStyle(workbook);
		style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
		return style;
	}

	/**
	 * 初始化--每列标题样式
	 *
	 * @param workbook
	 * @return
	 */
	private CellStyle initTitleStyle(Workbook workbook) {
		CellStyle style = getBaseCellStyle(workbook);
		style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
		// 背景色
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		return style;
	}

	/**
	 * 初始化--数据行样式
	 *
	 * @param workbook
	 * @return
	 */
	private CellStyle initStyles(Workbook workbook) {
		CellStyle style = getBaseCellStyle(workbook);
		style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
		style.setDataFormat(STRING_FORMAT);
		return style;
	}

	/**
	 * 基础样式
	 *
	 * @return
	 */
	private CellStyle getBaseCellStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		// 下边框
		style.setBorderBottom(BorderStyle.THIN);
		// 左边框
		style.setBorderLeft(BorderStyle.THIN);
		// 上边框
		style.setBorderTop(BorderStyle.THIN);
		// 右边框
		style.setBorderRight(BorderStyle.THIN);
		// 水平居中
		style.setAlignment(HorizontalAlignment.CENTER);
		// 上下居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		// 设置自动换行
		style.setWrapText(true);
		return style;
	}

	/**
	 * 字体样式
	 *
	 * @param size   字体大小
	 * @param isBold 是否加粗
	 * @return
	 */
	private Font getFont(Workbook workbook, short size, boolean isBold) {
		Font font = workbook.createFont();
		// 字体样式
		font.setFontName("宋体");
		// 是否加粗
		font.setBold(isBold);
		// 字体大小
		font.setFontHeightInPoints(size);
		return font;
	}
}