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: 2022/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);}
}