<poi.version>4.1.2</poi.version>
public class ExcelsUtil {private static final Logger logger = LoggerFactory.getLogger(ExcelsUtil.class);/*** 时间格式*/private static final String TIME_PATTERN = "yyyy-MM-dd HH:mm:ss";/*** 默认列的宽度*/private static final int COLUMN_WIDTH = 24;/*** 表头字体大小*/private static final int TITLE_FONT_SIZE = 11;/*** 表头单元格**/private static final int TITLE_CELL = 0;/*** 内容单元格**/private static final int CONTENT_CELL = 1;/*** 每个sheeet的最大数据数量*/private static final int MAX_LENGTH = 60000;/*** 导出* 2003版本的xls** @param title 表格标题名,文件名* @param headers 表格头部标题中文集合* @param headerZhWords 表格头部标题的字段名* @param dataSet 需要显示的数据集合*/public static <T> void exportExcel(String title, String[] headers, String[] headerZhWords, Collection<T> dataSet,HttpServletResponse response) throws UnsupportedEncodingException {response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setHeader("Content-Disposition", String.format("attachment;filename=%s.xls", URLEncoder.encode(title, "UTF-8")));try (HSSFWorkbook workbook = new HSSFWorkbook();ServletOutputStream out = response.getOutputStream()) {int pageNum = 1;int dataLength = dataSet.size();List<T> dataAll = new ArrayList<>(dataSet);List<T> dataPage;//分sheet处理 每个sheet数据条数为MAX_LENGTHwhile (dataLength >= 0) {if (dataLength > MAX_LENGTH) {dataPage = dataAll.subList((pageNum - 1) * MAX_LENGTH, pageNum * MAX_LENGTH);} else {dataPage = dataAll.subList((pageNum - 1) * MAX_LENGTH, dataAll.size());}// 生成一个sheetHSSFSheet sheet = workbook.createSheet(String.format("%s -%s-", title, pageNum));dataLength -= MAX_LENGTH;pageNum++;// 设置表格默认列宽度sheet.setDefaultColumnWidth(COLUMN_WIDTH);// 生成标题样式HSSFCellStyle titleStyle = setCellStyle(workbook, TITLE_CELL);// 产生表格标题行HSSFRow row = sheet.createRow(0);HSSFCell cellHeader;for (int i = 0; i < headers.length; i++) {cellHeader = row.createCell(i);cellHeader.setCellStyle(titleStyle);cellHeader.setCellValue(new HSSFRichTextString(headers[i]));}//填充内容fillContent(headerZhWords, dataPage, workbook, sheet, 0);}workbook.write(out);} catch (IOException e) {logger.error("excel生成错误", e);}}/*** 设置样式**/private static HSSFCellStyle setCellStyle(HSSFWorkbook workbook, int cellType) {HSSFCellStyle style = null;//边框BorderStyle borderStyle = BorderStyle.THIN;short borderColor = HSSFColor.HSSFColorPredefined.BLACK.getIndex();if (cellType == TITLE_CELL) {style = workbook.createCellStyle();style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setBorderBottom(borderStyle);style.setBorderLeft(borderStyle);style.setBorderRight(borderStyle);style.setBorderTop(borderStyle);style.setAlignment(HorizontalAlignment.CENTER_SELECTION);style.setTopBorderColor(borderColor);style.setLeftBorderColor(borderColor);style.setRightBorderColor(borderColor);style.setBottomBorderColor(borderColor);//垂直居中style.setAlignment(HorizontalAlignment.CENTER_SELECTION);style.setVerticalAlignment(VerticalAlignment.CENTER);//自动换行style.setWrapText(false);// 生成标题字体HSSFFont font = workbook.createFont();font.setBold(true);font.setFontName("宋体");font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());font.setFontHeightInPoints((short) TITLE_FONT_SIZE);// 把字体应用到当前的样式style.setFont(font);} else if (cellType == CONTENT_CELL) {style = workbook.createCellStyle();style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setBorderBottom(borderStyle);style.setBorderLeft(borderStyle);style.setBorderRight(borderStyle);style.setBorderTop(borderStyle);style.setAlignment(HorizontalAlignment.CENTER_SELECTION);style.setVerticalAlignment(VerticalAlignment.CENTER);//设置自动换行style.setWrapText(false);// 生成内容字体HSSFFont font = workbook.createFont();font.setBold(false);style.setFont(font);}return style;}/*** 填充sheet内容** @param headerWords 填充数据的字段* @param dataSet 数据集* @param workbook 工作上下文* @param sheet sheet* @param contentStartIndex 从第contentStartIndex+1行开始填充数据*/private static <T> void fillContent(String[] headerWords, Collection<T> dataSet, HSSFWorkbook workbook, HSSFSheet sheet,Integer contentStartIndex) {//内容样式HSSFCellStyle contentStyle = setCellStyle(workbook, CONTENT_CELL);SimpleDateFormat sdf = new SimpleDateFormat(TIME_PATTERN);//字段名String fieldName;//get方法名称String getMethodName;//单元格HSSFCell cell;Method getMethod;Object value = "";HSSFRow row;contentStartIndex = contentStartIndex == null ? 0 : contentStartIndex;T t;int index = 0;Iterator<T> it = dataSet.iterator();while (it.hasNext()) {index++;row = sheet.createRow(index + contentStartIndex);t = it.next();for (int i = 0; i < headerWords.length; i++) {cell = row.createCell(i);cell.setCellStyle(contentStyle);fieldName = headerWords[i];if (t instanceof Map) {//Map类型value = ((Map<?, ?>) t).get(fieldName);} else {getMethodName = getBeanMethodName(fieldName);//Bean类try {getMethod = t.getClass().getMethod(getMethodName);value = getMethod.invoke(t);} catch (SecurityException | NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {logger.error("excel生成错误", e);}}if (null == value) {cell.setCellValue("");} else if (value instanceof Integer) {cell.setCellValue((Integer) value);} else if (value instanceof Float || value instanceof Double) {cell.setCellValue(String.valueOf(value));} else if (value instanceof Long) {cell.setCellValue((Long) value);} else if (value instanceof Date) {cell.setCellValue(sdf.format((Date) value));} else {cell.setCellValue(String.valueOf(value));}}}}/*** 获取bean的获取值的方法** @param fieldName* @return*/private static String getBeanMethodName(String fieldName) {String cacheName = CommonConstant.CachePre.METHOD + fieldName;String methodName = String.valueOf(CommonConstant.BaseCache.COMMON_CACHE.get(cacheName));if (CommonConstant.BaseConstant.NULL.equals(methodName)) {methodName = "get" + fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);CommonConstant.BaseCache.COMMON_CACHE.put(cacheName, methodName);}return methodName;}
}