Java Excel导出包括普通导出及复杂表格样式，主要是对于需要进行行列合并的列进行特殊处理，计算清楚起始行，结束行，起始列，结束列。
普通导出可以是所有列，也可以是包含某些列，或者排除某些列；
1. 效果图
2. 原理
如对于上图中的覆盖能力，需要A2，A3，A4行列进行合并，则传参
//表示要执行合并的行是第一行，从第1行到第3行，从第0列到第0列；注意这里excel的行列编号下表index都是从0开始的。
mergeRowColCell(sheet, 1, 1, 3, 0, 0, ‘覆盖能力’,valStyle);
3. 源码
3.1 pom
<!--easyexcel 依赖--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.6</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.17</version></dependency>
3.2 ExcelUtil工具类
package com.example.restservice.utils;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.support.ExcelTypeEnum;import org.apache.poi.hssf.usermodel.*;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.util.CellRangeAddress;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.Arrays;import java.util.Date;import java.util.List;/**************************************Class Name: ExcelUtil*Description: <Excel导出类>*@author: Seminar*@create: /4/6*@since 1.0.0*************************************/public class ExcelUtil {public static final String UNDERLINE = "_";public static ServletOutputStream setResponseParam(HttpServletResponse response, String fileName) throws IOException {String value = "attachment; filename=" + new String((fileName + UNDERLINE + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLS.getValue()).getBytes("gb2312"), "ISO8859-1");response.setCharacterEncoding("utf-8");response.setContentType("application/vnd.openxmlformats.officedocument.spreadsheetml.sheet");response.setHeader("Content-disposition", value);return response.getOutputStream();}public static void exportExcel(HttpServletResponse response, String fileName, Class clazz, List data) throws IOException {OutputStream os = setResponseParam(response, fileName);EasyExcel.write(os, clazz).sheet(fileName).doWrite(data);}public static void exportExcelIncludeColumns(HttpServletResponse response, String fileName, Class clazz, List data, List<String> columns) throws IOException {OutputStream os = setResponseParam(response, fileName);EasyExcel.write(os, clazz).includeColumnFiledNames(columns).sheet(fileName).doWrite(data);}public static void exportExcelExcludeColumns(HttpServletResponse response, String fileName, Class clazz, List data, List<String> columns) throws IOException {OutputStream os = setResponseParam(response, fileName);EasyExcel.write(os, clazz).excludeColumnFiledNames(columns).sheet(fileName).doWrite(data);}/*** 定制导出Excel** @param response* @param fileName sheet名称* @param title 标题* @param data表格数据* @return*/public static void exportExcelPj(HttpServletResponse response, String fileName, String[] title, String[][] data) throws IOException {OutputStream os = setResponseParam(response, fileName);// 第一步，创建一个HSSFWorkbook，对应一个Excel文件HSSFWorkbook wb = new HSSFWorkbook();// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheetHSSFSheet sheet = wb.createSheet(fileName);// 设置列宽sheet.setColumnWidth(0, 5000);sheet.setColumnWidth(2, 5000);// 第三步，在sheet中添加表头第0行HSSFRow row1 = sheet.createRow(0);// 第四步，创建单元格，并设置表头居中及值的样式HSSFCellStyle style = wb.createCellStyle();style.setFillBackgroundColor(IndexedColors.GREEN.getIndex());style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中HSSFCellStyle leftStyle = wb.createCellStyle();leftStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());leftStyle.setAlignment(HorizontalAlignment.LEFT); // 创建一个居中格式HSSFCell cell = null;// 创建标题for (int i = 0; i < title.length; i++) {cell = row1.createCell(i);cell.setCellValue(title[i]);cell.setCellStyle(style);}//合并单元格的内容List<String> mergeCells = Arrays.asList(new String[]{"覆盖能力（20分）", "获取成本（10分）", "数据质量（60分）","产线支持（10分）", "附带价值（10分）", "总分"});for (int i = 0; i < data.length; i++) {//创建内容row1 = sheet.createRow(i + 1);for (int j = 0; j < data[i].length; j++) {//将内容按顺序赋给对应的列对象cell = row1.createCell(j);cell.setCellValue(data[i][j]);// 设置评价指标列居左，其他的列剧中if (j == 2) {cell.setCellStyle(leftStyle);} else {cell.setCellStyle(style);}}}mergeRowColCell(sheet, 1, 1, 3, 0, 0, mergeCells.get(0), style);mergeRowColCell(sheet, 4, 4, 6, 0, 0, mergeCells.get(1), style);mergeRowColCell(sheet, 7, 7, 13, 0, 0, mergeCells.get(2), style);mergeRowColCell(sheet, 14, 14, 14, 0, 0, mergeCells.get(3), style);mergeRowColCell(sheet, 15, 15, 15, 0, 0, mergeCells.get(4), style);mergeRowColCell(sheet, 16, 16, 16, 1, 3, "", style);mergeRowColCell(sheet, 16, 16, 16, 0, 0, mergeCells.get(5), style);wb.write(os);os.flush();os.close();}/*** 表格合并方法** @param sheet 表格sheet* @param rowIndex 要执行合并的行* @param firstRow 首行* @param lastRow 尾行* @param firstCol 首列* @param lastCol 尾列* @param cellVal 合并后的单元格值* @param valStyle 合并后的单元格样式*/private static void mergeRowColCell(HSSFSheet sheet, int rowIndex, int firstRow, int lastRow, int firstCol, int lastCol, String cellVal, HSSFCellStyle valStyle) {if (!(firstRow == lastRow && firstCol == lastCol)) {// 合并单元格CellRangeAddress callRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);//起始行,结束行,起始列,结束列sheet.addMergedRegion(callRangeAddress);}HSSFRow row = sheet.getRow(rowIndex);HSSFCell cell = row.createCell(0);cell.setCellValue(cellVal);cell.setCellStyle(valStyle);}}
