本案例生产环境真是案例POI导出：
开发环境：idea+mybaits3+spring4+springmvc4+maven3+mysql5.7
poi依赖：
<dependency><groupId>poi</groupId><artifactId>poi-contrib-2.5-final</artifactId><version>0302</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.7</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.7</version></dependency>
控制层：
@CrossOrigin@RestController@RequestMapping("/mip/export")public class ExportController extends BaseController {@Autowiredprivate ExportService exportService;/*** 基础属性导出* @param insightType* @param brandCode* @param categoryCode* @param startCrowdType* @param endCrowdType* @param flowType* @param audienceCode* @param request* @return*/@GetMapping("/basicAttributeExport")public void basicAttributeExport(@Validated@NotNull(message = "%custom%人群洞察类型不可为空")@RequestParam(value = "insightType")Integer insightType,@Validated@NotNull(message = "%custom%品牌编码不可为空")@RequestParam(value = "brandCode")String brandCode,@RequestParam(value = "categoryCode")String categoryCode, String startCrowdType, String endCrowdType, String flowType,String audienceCode, HttpServletRequest request, HttpServletResponse response) {assertBrandCode(brandCode, request);ExportDto dto = new ExportDto().setInsightType(insightType).setBrandCode(brandCode).setCategoryCode(categoryCode).setStartCrowdType(startCrowdType).setEndCrowdType(endCrowdType).setFlowType(flowType).setAudienceCode(audienceCode);exportService.basicAttributeExport(dto, response);}}
业务层：
@Servicepublic class ExportServiceImpl implements ExportService {//log日志private static final Logger LOGGER = LoggerFactory.getLogger(ExportServiceImpl.class);@Autowiredprivate DalClient dalClient;@Autowiredprivate IExportAssertionService exportAssertionService;/*** 基础属性* @param dto* @param response*/public void basicAttributeExport(ExportDto dto, HttpServletResponse response) {//断言导出exportAssertionService.assertExport(dto);LOGGER.info("基础属性导出入参为:{}", new Gson().toJson(dto));//查询结果(查询抽取公共方法)List<ExportVo> list = queryBasicAttribute(dto);String fileName = SNConstant.BASICATTRIBUTE + DateUtils.getCurrentDateStr("yyyyMMddHHmmss");String sheetName = SNConstant.BASICATTRIBUTE;ExcelUtil.downLoadExcel(response, list, fileName, sheetName, ExportVo.class);}}
ExcelUtil工具类：
package com.suning.snmip.utils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.xssf.usermodel.*;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import javax.servlet.http.HttpServletResponse;import java.io.BufferedOutputStream;import java.lang.reflect.Method;import java.util.ArrayList;import java.util.List;public class ExcelUtil {//log日志private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);/*** poi导出excel* 备注：导出excel HSSFWorkbook 只能最多导出65535条数据* @param response* @param list* @param fileName* @param sheetName* @param clazz* @param <T>*/public static <T> void downLoadExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName,Class<T> clazz) {// 第一步，创建一个webbook，对应一个Excel文件XSSFWorkbook wb = new XSSFWorkbook();// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet,自定义sheetNameXSSFSheet sheet = wb.createSheet(sheetName);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制shortXSSFRow row = sheet.createRow(0);// 第四步，创建表头样式XSSFCellStyle headStyle = setHeadStyle(wb);// 给单元格内容设置另一个样式XSSFCellStyle cellStyle = setCellStyle(wb);//自定义表头英文值List<String> headEnglishList = SNConstant.HEAD_ENGLISH;//获取自定义的值放在数组中（英文状态）String[] titles = headEnglishList.toArray(new String[headEnglishList.size()]);//获取表头英文值对应的中文值List<String> headChinseList = changeEnglishToChiness(headEnglishList);//获取自定义的值放在数组中(中文状态)String[] titlesNew = headChinseList.toArray(new String[headChinseList.size()]);//循环表头XSSFCell cell;for (int i = 0; i < titlesNew.length; i++) {//获得单元格cell = row.createCell(i);//给单元格设置样式cell.setCellStyle(headStyle);//设置单元格为富文本类型XSSFRichTextString text = new XSSFRichTextString(titlesNew[i]);//给单元格设置值cell.setCellValue(text);//设置自动列宽(必须在单元格设值以后进行)sheet.autoSizeColumn(i);sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);}//循环单元格(采用反射机制)for (int m = 0; m < list.size(); m++) {//从第二行开始写数据（注意下标）row = sheet.createRow(m + 1);for (int i = 0; i < titles.length; i++) {//获取方法名称String name = toUpperCaseFirstOne(titles[i]);Method getMoth;try {getMoth = clazz.getMethod(name);//获取方法值String value = getMoth.invoke(list.get(m)) == null ? "" : (String) getMoth.invoke(list.get(m));//创建单元格cell = row.createCell(i);//给每个单元格内容设置样式cell.setCellStyle(cellStyle);//设置单元格为字符串类型cell.setCellType(HSSFCell.CELL_TYPE_STRING);//给每个单元格设置值cell.setCellValue(value);} catch (Exception e) {LOGGER.error("ExcelUtil->downLoad exception", e);}}}// 必须在单元格设值以后进行// 设置为根据内容自动调整列宽for (int k = 0; k < list.size(); k++) {sheet.autoSizeColumn(k);}// 处理中文不能自动调整列宽的问题setSizeColumn(sheet, list.size());//指定名称和路径setReportNameAndLoad(response, fileName, wb);}/*** 设置表头样式* @param wb* @return*/private static XSSFCellStyle setHeadStyle(XSSFWorkbook wb) {XSSFCellStyle headStyle = wb.createCellStyle();// 设置背景颜色白色headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置填充颜色headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 设置上下左右边框headStyle.setBorderBottom(BorderStyle.THIN);headStyle.setBorderLeft(BorderStyle.THIN);headStyle.setBorderRight(BorderStyle.THIN);headStyle.setBorderTop(BorderStyle.THIN);// 设置水平居中headStyle.setAlignment(HorizontalAlignment.CENTER);// 设置标题字体XSSFFont headFont = wb.createFont();// 设置字体大小headFont.setFontHeightInPoints((short) 14);// 设置字体headFont.setFontName("宋体");// 设置字体粗体headFont.setBold(true);// 把字体应用到当前的样式headStyle.setFont(headFont);return headStyle;}/*** 设置单元格内容样式* @param wb* @return*/private static XSSFCellStyle setCellStyle(XSSFWorkbook wb) {XSSFCellStyle cellStyle = wb.createCellStyle();// 设置上下左右边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);//设置左对齐cellStyle.setAlignment(HorizontalAlignment.LEFT);// 设置标题字体XSSFFont cellFont = wb.createFont();// 设置字体大小cellFont.setFontHeightInPoints((short) 11);// 设置字体cellFont.setFontName("等线");// 把字体应用到当前的样式cellStyle.setFont(cellFont);return cellStyle;}/*** 下载报表首行英文转换为中文* @param list* @return*/private static List<String> changeEnglishToChiness(List<String> list) {List<String> valueList = new ArrayList<>();for (String str : list) {valueList.add(Mapping.DownLoad_Type.get(str));}return valueList;}/*** 获取表头的方法名* @param s* @return*/public static String toUpperCaseFirstOne(String s) {if (Character.isUpperCase(s.charAt(0))) {return "get" + s;} else {return "get" + (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();}}/*** 自适应宽度(中文支持)* @param sheet* @param size*/private static void setSizeColumn(XSSFSheet sheet, int size) {for (int columnNum = 0; columnNum < size; columnNum++) {int columnWidth = sheet.getColumnWidth(columnNum) / 256;for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {XSSFRow currentRow;//当前行未被使用过if (sheet.getRow(rowNum) == null) {currentRow = sheet.createRow(rowNum);} else {currentRow = sheet.getRow(rowNum);}if (currentRow.getCell(columnNum) != null) {XSSFCell currentCell = currentRow.getCell(columnNum);if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {int length = currentCell.getStringCellValue().getBytes().length;if (columnWidth < length) {columnWidth = length;}}}}sheet.setColumnWidth(columnNum, columnWidth * 256);}}/*** 导出响应* @param response* @param name* @param wb*/public static void setReportNameAndLoad(HttpServletResponse response, String name, XSSFWorkbook wb) {BufferedOutputStream fos = null;try {// 设置响应输出的头类型//response.setContentType("application/vnd.ms-excel;charset=GBK");//导出xls格式response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GBK");//导出xlsx格式// 设置下载文件名称(注意中文乱码)response.setHeader("Content-Disposition","attachment;filename=" + new String((name).getBytes("GB2312"), "ISO8859-1") + ".xlsx");response.setHeader("Pragma", "No-cache");fos = new BufferedOutputStream(response.getOutputStream());wb.write(fos);} catch (Exception e) {LOGGER.error("ExcelUtil->setReportNameAndLoad exception:", e);} finally {if (fos != null) {try {fos.close();} catch (Exception e) {LOGGER.error("ExcelUtil->setReportNameAndLoad close outputStream exception:", e);}}}}}
常量类：
//导出表头英文属性public static final List<String> HEAD_ENGLISH = Arrays.asList("dimension", "feature", "insightPercent", "grailPercent", "comparePercent");
备注：该表头英文属性名称就是导出表Vo的实体属性名称
表头英文名与中文对应关系：
类Mapping：
public class Mapping {public static final Map<String, String> DownLoad_Type = Collections.unmodifiableMap(new HashMap<String, String>() {{put("dimension", "纬度");put("feature", "特征");put("insightPercent", "占比");put("grailPercent", "大盘");put("comparePercent", "对比大盘");}});}
导出实体Vo：
package com.suning.snmip.intf.vo;import lombok.Getter;import lombok.Setter;@Getter@Setterpublic class ExportVo {/*** 纬度*/private String dimension;/*** 特征*/private String feature;/*** 占比*/private String insightPercent;/*** 大盘*/private String grailPercent;/*** 对比大盘*/private String comparePercent;}
导出效果：
总结：
1.poi导出excel后缀xls和xlsx时设置响应不一样
xls格式：response.setContentType("application/vnd.ms-excel;charset=GBK");
xlsx格式：response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GBK");
2.poi导出xls和xlsx创建工作薄不一致
xls：Workbook workbook = new HSSFWorkbook();
xlsx：Workbook workbook = new XSSFWorkbook();
上诉若操作不对则导出excel报：
如果觉得《POI导出excel并设置单元格样式和单元格内容中文自适应》对你有帮助，请点赞、收藏，并留下你的观点哦！
