easyPoi单sheet与多sheet导出excel简单实用（重点：自动列宽的设置）

导入依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.2.0</version>
</dependency>

单sheet导出

@PostMapping("exportTeacherInfo")
	@ResponseBody
	public JSONObject exportTeacherInfo(HttpServletRequest request, HttpServletResponse response, @RequestBody ExprotBean exprotBean) throws IOException
	{
		try
		{
			List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();

			List<String> objects = exprotBean.getOptions();
            // 设置动态列
			initColm(objects, entity);
			// 动态列数据
			List<Map<String, Object>> dataArr = new ArrayList<Map<String, Object>>();

			List<String> zghs = exprotBean.getSels();
			if (zghs != null && CollectionUtils.isNotEmpty(zghs))
			{
				Map<String, Object> param = new HashMap<String, Object>(1);
				param.put("zghs", zghs);
				dataArr = jzgJbxxService.findQuery(" SELECT * FROM V_JZG_EXPORT_INFO V WHERE V.ZGH IN (:zghs) ", param);
			} else
			{
				dataArr = jzgJbxxService.findQuery(" SELECT * FROM V_JZG_EXPORT_INFO V  ");
			}
			// 创建工作簿
			Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("教职工信息表", "数据"), entity, dataArr);
			// 将文件写到系统下载位置，此处多数是直接将表格用输出流写到浏览器页面。
			String downFileName = FileUtil.writeWorkBookFileToSystem(workbook, systemConfig);
			return OutPutJson.success("导出成功", downFileName);
		} catch (Exception e)
		{
			logger.error(e.getMessage(), e);
			return OutPutJson.failure("导出失败");
		}
	}

/**
 * 初始化动态列
 * @param checkGzjl
 * @param entity
 */
private void initColm(List<String> checkGzjl, List<ExcelExportEntity> entity)
	{
		for (int i = 0; i < checkGzjl.size(); i++)
		{
			JSONObject obj = JSONObject.parseObject(checkGzjl.get(i));
			String MC = (String) obj.get("MC");      // 此处名称为列名
			String KEY = (String) obj.get("KEY");    // 此处KEY为与数据集合对应匹配字段代码
			entity.add(new ExcelExportEntity(MC, KEY));
		}
	}

多sheet导出

/**
	 * 导出教职工扩展信息
	 * 
	 * @param request
	 * @param response
	 * @param exportKzxxBean
	 * @return
	 * @throws IOException
	 */
	@SuppressWarnings("unchecked")
	@PostMapping("exportTeacherKzxx")
	@ResponseBody
	public JSONObject exportTeacherKzxx(HttpServletRequest request, HttpServletResponse response, @RequestBody ExportKzxxBean exportKzxxBean) throws IOException
	{
		try
		{
			// 多sheet
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            // 此为是否存在列
			List<String> checkGzjl = exportKzxxBean.getCheckGzjl(); 
			List<String> checkJtcy = exportKzxxBean.getCheckJtcy();

			List<String> zghs = exportKzxxBean.getSels();
			Map<String, Object> param = new HashMap<String, Object>(1);
			if (zghs != null && CollectionUtils.isNotEmpty(zghs))
			{
				param.put("zghs", zghs);
			}

			List<Map<String, Object>> dataArr = new ArrayList<Map<String, Object>>();
			
			// 工作经历
			if (checkGzjl.size() > 1)
			{
				List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
				initColm(checkGzjl, entity); //与单sheet导出所初始化列方法一样
				if (zghs != null && CollectionUtils.isNotEmpty(zghs))
				{
					dataArr = jzgJbxxService.findQuery(" SELECT G.D_ID_, G.ZGH,  G.SZDWMC,TO_CHAR(G.QSRQ,'YYYY-MM') QSNY,TO_CHAR(G.JZRQ,'YYYY-MM') JZNY,G.ZRDZZW,G.ZRJSZWDM,G.ZMR,G.CSGZNR,GETVALUEBYTABLE('STD_GB_ZYJSZW', 'NAME', 'CODE', G.ZRJSZWDM) ZRJSZWMC FROM T_JZG_GZJLXX G WHERE G.ZGH IN (:zghs) ORDER BY G.ZGH ASC, G.QSRQ DESC", param);
				} else
				{
					dataArr = jzgJbxxService.findQuery(" SELECT G.D_ID_,G.ZGH,  G.SZDWMC,TO_CHAR(G.QSRQ,'YYYY-MM') QSNY,TO_CHAR(G.JZRQ,'YYYY-MM') JZNY,G.ZRDZZW,G.ZRJSZWDM,G.ZMR,G.CSGZNR,GETVALUEBYTABLE('STD_GB_ZYJSZW', 'NAME', 'CODE', G.ZRJSZWDM) ZRJSZWMC FROM T_JZG_GZJLXX G  ORDER BY G.ZGH ASC, G.QSRQ DESC ");
				}
				Map<String, Object> data = new HashMap<String, Object>();
				data.put("title", new ExportParams("工作经历", "工作经历"));
				data.put("entity", entity);
				data.put("data", dataArr);
				list.add(data);
			}
			// 家庭成员
			if (checkJtcy.size() > 1)
			{
				List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
				initColm(checkJtcy, entity); //与单sheet导出所初始化列方法一样
				if (zghs != null && CollectionUtils.isNotEmpty(zghs))
				{
					dataArr = jzgJbxxService.findQuery(" SELECT * FROM V_JZG_JTCYXX V WHERE V.ZGH IN (:zghs) ORDER BY  V.ZGH ASC ", param);
				} else
				{
					dataArr = jzgJbxxService.findQuery(" SELECT * FROM V_JZG_JTCYXX V ORDER BY  V.ZGH ASC ");
				}
				Map<String, Object> data = new HashMap<String, Object>();
				data.put("title", new ExportParams("家庭成员", "家庭成员"));
				data.put("entity", entity);
				data.put("data", dataArr);
				list.add(data);
			}
			// 新建工作簿
			Workbook workbook = new HSSFWorkbook(); 
			// 循环创建sheet并匹配数据
			for (Map<String, Object> map : list)
			{
				ExcelExportService service = new ExcelExportService();
				service.createSheetForMap(workbook, (ExportParams) map.get("title"), (List<ExcelExportEntity>) map.get("entity"), (Collection<?>) map.get("data"));
			}
			// 将文件写到系统下载位置
			String downFileName = FileUtil.writeWorkBookFileToSystem(workbook, systemConfig);
			return OutPutJson.success("导出成功", downFileName);
		} catch (Exception e)
		{
			logger.error(e.getMessage(), e);
			return OutPutJson.failure("导出失败");
		}
	}

自动列宽的设置

因ExcelExportUtil.exportExcel()里面new ExcelExportService ().createSheetForMap(workbook, entity, entityList, dataSet);里面设置了固定列宽（可去看源码），导致导出列中字重叠。很不美观。可重写ExcelExportService 类的setCellWith方法。如下：

public void setCellWith(List<ExcelExportEntity> excelParams, Sheet sheet) {
        for (int i = 0; i < excelParams.size(); i++) {
            if (excelParams.get(i).getList() != null) {
                List<ExcelExportEntity> list = excelParams.get(i).getList();
                for (int j = 0; j < list.size(); j++) {
                	// 调整每一列宽度
                    sheet.autoSizeColumn((short) i);
                    // 解决自动设置列宽中文失效的问题
                    sheet.setColumnWidth(i, sheet.getColumnWidth(j) * 17 / 10);
                }
            } else {
            	// 调整每一列宽度
                sheet.autoSizeColumn((short) i);
                // 解决自动设置列宽中文失效的问题
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            }
        }