1、开发前提
目前项目上有这样的需求，支持Excel导出后再线下调至后导入，并且有关键字及其他部分字段不支持修改，所以需要写保护，按正常的理解来讲锁定特定的列即可，但是需求中需要在Excel中添加行数据，既是支持修改且支持新增数据。所以不能直接锁定列，因为锁列后是不能编辑新增行上该列的单元格，只能锁定到单元格级别。
2、代码展示
a. pom文件添加相应的依赖，POI包和文件上传包
<!-- POI --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.15</version></dependency><!--Excel上传 --><dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.1</version> </dependency> <dependency><groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency>
b. 编写ExcelHeader类，头部某列的抽象
package com.longfor.mdm2bi.utils.excel;import java.util.ArrayList;import java.util.List;import mons.collections.CollectionUtils;import org.apache.poi.ss.usermodel.HorizontalAlignment;/*** Excel 头部列名* @author jawjiang**/public class ExcelHeader {private String columnName;private Integer width;private Integer height;private HorizontalAlignment align;private boolean isLock;public ExcelHeader(String columnName){this.columnName = columnName;this.width = 1;this.height = 1;this.align = HorizontalAlignment.CENTER;this.setLock(false);}public ExcelHeader(String columnName,Integer width){this.columnName = columnName;this.width = width;this.height = 1;this.align = HorizontalAlignment.CENTER;this.setLock(false);} public ExcelHeader(String columnName,Integer width,Integer height){this.columnName = columnName;this.width = width;this.height = height;this.align = HorizontalAlignment.CENTER;this.setLock(false);} public boolean isLock() {return isLock;}public void setLock(boolean isLock) {this.isLock = isLock;}public String getColumnName() {return columnName;}public void setColumnName(String columnName) {this.columnName = columnName;}public Integer getWidth() {return width;}public void setWidth(Integer width) {this.width = width;}public Integer getHight() {return height;}public void setHight(Integer hight) {this.height = hight;}public HorizontalAlignment getAlign() {return align;}public void setAlign(HorizontalAlignment align) {this.align = align;}public static ExcelHeader getDefaultInstance(String columnName){ExcelHeader header = new ExcelHeader(columnName);return header;}public static List<List<ExcelHeader>> getDefaultHeadersList(List<String> columnList){List<ExcelHeader> headerList = new ArrayList<ExcelHeader>();if(CollectionUtils.isEmpty(columnList)){throw new RuntimeException("列集合为空");}for(String columnNmae : columnList){ExcelHeader header = new ExcelHeader(columnNmae);headerList.add(header);}List<List<ExcelHeader>> headersList = new ArrayList<>();headersList.add(headerList);return headersList;}}
c. 导出工具类
package com.longfor.mdm2bi.utils.excel;import java.io.IOException;import java.io.OutputStream;import .URLEncoder;import java.util.ArrayList;import java.util.List;import javax.servlet.http.HttpServletResponse;import mons.collections.CollectionUtils;import mons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.RichTextString;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.RegionUtil;import org.apache.poi.xssf.streaming.SXSSFCell;import org.apache.poi.xssf.streaming.SXSSFRow;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFRichTextString;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.mon.BusinessException;import com.longfor.mdm2bi.utils.UUIDUtil;public class MultiplExcelExportUtil {private static final Logger log = LoggerFactory.getLogger(MultiplExcelExportUtil.class);public static final String DEFAULT_SHEET_NAME = "sheet1";private String title;private Integer titleHight = 0; //默认为0private List<List<ExcelHeader>> headersList;//支持多行的表头,最下一层header,必须是width、height都是1private List<Object[]> dataList;private HttpServletResponse response;private Integer headerWidth = 0;private Integer headerHeight = 0;private boolean isLockSheet = false; //锁定sheetprivate List<Integer> unLockColumnIndexList; //不需要锁定的列索引集合private List<Integer> lockColumnIndexList; //需要锁定的列索引集合private List<Integer> columnIndexList = new ArrayList<>();public boolean isLockSheet() {return isLockSheet;}public void setLockSheet(boolean isLockSheet) {this.isLockSheet = isLockSheet;if(this.isLockSheet){lockColumnIndexList = new ArrayList<Integer> ();unLockColumnIndexList = new ArrayList<Integer> ();}}public MultiplExcelExportUtil(String title, List<List<ExcelHeader>> headersList,List<Object[]> dataList, HttpServletResponse response) {this.title = title;this.titleHight = 1;this.headersList = headersList;this.dataList = dataList;this.response = response;this.setHeaderWidthAndHight(headersList);}public MultiplExcelExportUtil(String title, List<List<ExcelHeader>> headersList,List<Object[]> dataList, HttpServletResponse response,boolean isLockSheet) {this.title = title;this.titleHight = 1;this.headersList = headersList;this.dataList = dataList;this.response = response;this.setLockSheet(isLockSheet);this.setHeaderWidthAndHight(headersList);}public MultiplExcelExportUtil(List<List<ExcelHeader>> headersList,List<Object[]> dataList,HttpServletResponse response) {this.headersList = headersList;this.dataList = dataList;this.response = response;this.setHeaderWidthAndHight(headersList);}public MultiplExcelExportUtil(List<List<ExcelHeader>> headersList,List<Object[]> dataList,HttpServletResponse response,boolean isLockSheet) {this.headersList = headersList;this.dataList = dataList;this.response = response;this.setLockSheet(isLockSheet);this.setHeaderWidthAndHight(headersList);}private void setHeaderWidthAndHight(List<List<ExcelHeader>> headersList){if(CollectionUtils.isEmpty(headersList)){throw new RuntimeException("Excel头部信息不为空");}for(int i=0 ; i < headersList.size() ; i++){List<ExcelHeader> headers = headersList.get(i);Integer headerWidth = 0;Integer headerHeight = 0;for(ExcelHeader header : headers){if(header.getWidth() == null || header.getWidth() < 1){throw new RuntimeException("必须设置列宽,至少大于等于1");}headerWidth += header.getWidth();if(header.getHight() == null || header.getHight() < 1){throw new RuntimeException("必须设置高度,至少大于等于1");}if(header.getHight() > headerHeight){headerHeight = header.getHight();}//最低层的header,高度和宽度必须为1if(i == headersList.size() -1){if(header.getWidth() != 1 || header.getHight() !=1){throw new RuntimeException("最低层的header,高度和宽度必须为1");}}}if(this.headerWidth == 0){this.headerWidth = headerWidth;}else if(this.headerWidth != headerWidth){throw new BusinessException("头部信息列宽合不一致");}else{this.headerWidth = headerWidth;}this.headerHeight += headerHeight;}}@SuppressWarnings("deprecation")public void exportExcel(){SXSSFWorkbook swb = null;OutputStream out = null;try {swb = new SXSSFWorkbook(1000);SXSSFSheet sheet = null;if(StringUtils.isBlank(title)){sheet = swb.createSheet(DEFAULT_SHEET_NAME);}else{//表头的名字作为sheet名称sheet = swb.createSheet(title);this.createExcelTitle(swb, sheet);}sheet.trackAllColumnsForAutoSizing();// sheet样式定义CellStyle headerStyle = this.getTitleStyle(swb); // 头样式CellStyle normalContentStyle = this.getContentStyle(swb); CellStyle unLockContentStyle = this.getContentStyle(swb); // 非锁定单元格样式unLockContentStyle.setLocked(false);CellStyle lockContentStyle = this.getLockContentStyle(swb);//锁定的单元格样式//生成headerint headerHightIndex = titleHight;//header接在title后开始for(int i=0 ; i < headersList.size() ; i++){List<ExcelHeader> headers = headersList.get(i);SXSSFRow headerRow = sheet.createRow(headerHightIndex); // 创建header行CellStyle headerCellStyle = swb.createCellStyle();headerCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);headerRow.setRowStyle(headerCellStyle);int widthBeginIndex = 0; int maxHight = 1;for(ExcelHeader header : headers){SXSSFCell headerCell = headerRow.createCell(widthBeginIndex);if(header.getHight() > maxHight){maxHight = header.getHight();}int widthEndIndex = widthBeginIndex + header.getWidth() - 1;int heightEndIndex = headerHightIndex + header.getHight() - 1;if(headerHightIndex == heightEndIndex && widthBeginIndex == widthEndIndex){//不做处理}else{CellRangeAddress region = new CellRangeAddress(headerHightIndex,heightEndIndex,widthBeginIndex, widthEndIndex);sheet.addMergedRegion(region);setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet);}if(i == headersList.size() -1){if(this.isLockSheet && !header.isLock()){unLockColumnIndexList.add(widthBeginIndex);}if(this.isLockSheet && !header.isLock()){lockColumnIndexList.add(widthBeginIndex);}columnIndexList.add(widthBeginIndex);}RichTextString text = new XSSFRichTextString(header.getColumnName());headerCell.setCellValue(text); // 设置列的值headerStyle.setAlignment(header.getAlign()); //对齐方式headerCell.setCellStyle(headerStyle); // 样式headerCell.setCellType(CellType.STRING); // 单元格类型//重置宽度索引起始位置widthBeginIndex = widthEndIndex + 1;}headerHightIndex = headerHightIndex + maxHight;}for(Integer unLockColumnIndex : columnIndexList){//设置列格式，注释1sheet.setColumnWidth(unLockColumnIndex, 4000); //设置宽度//行号，样式sheet.setDefaultColumnStyle(unLockColumnIndex, unLockContentStyle);}//生成数据for (int i = 0; i < dataList.size(); i++) {log.debug("{}行",i);Object[] obj = dataList.get(i);// 遍历每个对象SXSSFRow row = sheet.createRow(i + headerHeight + titleHight);//跳过表头和标题,创建所需的行数for (int j = 0; j < obj.length; j++) {SXSSFCell cell = null; // 设置单元格的数据类型if (j == 0) {// 第一列设置为序号cell = row.createCell(j, CellType.STRING); // 数值类型cell.setCellValue(i + 1);} else {cell = row.createCell(j, CellType.STRING); // 字符串if (!"".equals(obj[j]) && obj[j] != null) {cell.setCellValue(obj[j].toString()); // 设置单元格的值} else {cell.setCellValue(" ");}}if(this.isLockSheet){if(unLockColumnIndexList.contains(j)){cell.setCellStyle(unLockContentStyle);}else{cell.setCellStyle(lockContentStyle);}}else{cell.setCellStyle(normalContentStyle); // 样式}//sheet.autoSizeColumn(j); // 调整每列宽度自适应标题}}if(isLockSheet){sheet.protectSheet(UUIDUtil.getRandom32PK());}dataList.clear();if (swb != null) {try {String fileName = (StringUtils.isBlank(title) ? DEFAULT_SHEET_NAME : title) + ".xls";String fileNameCoded = URLEncoder.encode(fileName, "UTF-8"); // 设置编码格式 防止乱码String headStr = "attachment; filename=\"" + fileNameCoded + "\""; // filename为全文件名System.out.println(headStr + "文件名");/*** response.setContentType(MIME)的作用是使客户端浏览器， 区分不同种类的数据，* 并根据不同的MIME调用浏览器内不同的程序嵌入模块来处理相应的数据。* 设置MIME的类型为APPLICATION/OCTET-STREAM response.setHeader()解决下载中文文件名乱码问题*/response.setContentType("APPLICATION/OCTET-STREAM");response.setHeader("Content-Disposition", headStr); // 返回 文件全名out = response.getOutputStream();swb.write(out); // 写入文档out.flush();out.close();} catch (IOException e) {e.printStackTrace();}}}catch(Exception e){e.printStackTrace();}}private void createExcelTitle(SXSSFWorkbook swb, SXSSFSheet sheet) {// 产生表格标题行SXSSFRow rowm = sheet.createRow(0); // 行SXSSFCell cellTiltle = rowm.createCell(0); // 单元格// sheet样式定义CellStyle titleStyle = this.getTitleStyle(swb); // 头样式/*** 参数说明 从0开始 第一行 第一列 都是从角标0开始 行 列 行列 (0,0,0,5) 合并第一行 第一列 到第一行 第六列 起始行，* 起始列，结束行，结束列 列的类型为short*/// 标题,合并第一行的所有列 而且放入titlesheet.addMergedRegion(new CellRangeAddress(0, titleHight-1, 0,headerWidth - 1));cellTiltle.setCellStyle(titleStyle); //cellTiltle.setCellValue(title); // 表头名字}/*** 表头样式 * @param workbook* @return*/public CellStyle getTitleStyle(Workbook workbook) {// 设置字体Font font = workbook.createFont();// 设置字体大小font.setFontHeightInPoints((short) 11);// 字体加粗font.setBold(true);// 设置字体名字 Courier New 黑体font.setFontName("楷体");CellStyle style = workbook.createCellStyle();// 设置底边框;style.setBorderBottom(BorderStyle.THIN);// 设置底边框颜色;style.setBottomBorderColor(HSSFColor.BLACK.index);// 设置左边框;style.setBorderLeft(BorderStyle.THIN);// 设置左边框颜色;style.setLeftBorderColor(HSSFColor.BLACK.index);// 设置右边框;style.setBorderRight(BorderStyle.THIN);// 设置右边框颜色;style.setRightBorderColor(HSSFColor.BLACK.index);// 设置顶边框;style.setBorderTop(BorderStyle.THIN);// 设置顶边框颜色;style.setTopBorderColor(HSSFColor.BLACK.index);// 在样式用应用设置的字体;style.setFont(font);// 设置自动换行;style.setWrapText(false);// 设置水平对齐的样式为居中对齐;style.setAlignment(HorizontalAlignment.CENTER);// 设置垂直对齐的样式为居中对齐;style.setVerticalAlignment(VerticalAlignment.CENTER);return style;}/*** 内容样式的style* @param workbook* @return*/public CellStyle getContentStyle(Workbook workbook) {// 设置字体Font font = workbook.createFont();// 设置字体大小// font.setFontHeightInPoints((short)10);// 字体加粗// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体名字font.setFontName("Courier New");// 设置样式;CellStyle style = workbook.createCellStyle();// 设置底边框;style.setBorderBottom(BorderStyle.THIN);// 设置底边框颜色;style.setBottomBorderColor(HSSFColor.BLACK.index);// 设置左边框;style.setBorderLeft(BorderStyle.THIN);// 设置左边框颜色;style.setLeftBorderColor(HSSFColor.BLACK.index);// 设置右边框;style.setBorderRight(BorderStyle.THIN);// 设置右边框颜色;style.setRightBorderColor(HSSFColor.BLACK.index);// 设置顶边框;style.setBorderTop(BorderStyle.THIN);// 设置顶边框颜色;style.setTopBorderColor(HSSFColor.BLACK.index);// 在样式用应用设置的字体;style.setFont(font);// 设置自动换行;style.setWrapText(false);// 设置水平对齐的样式为居中对齐;style.setAlignment(HorizontalAlignment.CENTER);// 设置垂直对齐的样式为居中对齐;style.setVerticalAlignment(VerticalAlignment.CENTER);return style;}public CellStyle getLockContentStyle(Workbook workBook){// 设置字体Font font = workBook.createFont();// 设置字体大小// font.setFontHeightInPoints((short)10);// 字体加粗// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体名字font.setFontName("Courier New");// 设置样式;CellStyle style = workBook.createCellStyle();// 设置底边框;style.setBorderBottom(BorderStyle.THIN);// 设置底边框颜色;style.setBottomBorderColor(HSSFColor.BLACK.index);// 设置左边框;style.setBorderLeft(BorderStyle.THIN);// 设置左边框颜色;style.setLeftBorderColor(HSSFColor.BLACK.index);// 设置右边框;style.setBorderRight(BorderStyle.THIN);// 设置右边框颜色;style.setRightBorderColor(HSSFColor.BLACK.index);// 设置顶边框;style.setBorderTop(BorderStyle.THIN);// 设置顶边框颜色;style.setTopBorderColor(HSSFColor.BLACK.index);// 在样式用应用设置的字体;style.setFont(font);// 设置自动换行;style.setWrapText(false);// 设置水平对齐的样式为居中对齐;style.setAlignment(HorizontalAlignment.CENTER);// 设置垂直对齐的样式为居中对齐;style.setVerticalAlignment(VerticalAlignment.CENTER);style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setLocked(true);return style;}private void setBorderStyle(int border, CellRangeAddress region,Sheet sheet){RegionUtil.setBorderBottom(border, region, sheet);//下边框RegionUtil.setBorderLeft(border, region, sheet); //左边框RegionUtil.setBorderRight(border, region, sheet); //右边框RegionUtil.setBorderTop(border, region, sheet); //上边框}}
3、demo测试，一共2个例子，一个是导出有头部合并及单元格锁定，另外一个就是最简单的导出
package com.longfor.mdm2bi.controller;import java.util.ArrayList;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import com.longfor.mdm2bi.core.web.BaseController;import com.longfor.mdm2bi.utils.excel.ExcelHeader;import com.longfor.mdm2bi.utils.excel.MultiplExcelExportUtil;@Controller@RequestMapping("/excel")public class ExcelController extends BaseController {@GetMapping("download")public void exportMultiplExcel(HttpServletResponse response){ExcelHeader headerA1 = new ExcelHeader("A1-C1",3,1);ExcelHeader headerE1 = new ExcelHeader("D1-E1",2,1);ExcelHeader headerF1 = new ExcelHeader("F1");List<ExcelHeader> firstHeaderList = new ArrayList<ExcelHeader>();firstHeaderList.add(headerA1);firstHeaderList.add(headerE1);firstHeaderList.add(headerF1);ExcelHeader header3 = new ExcelHeader("CC");ExcelHeader header4 = new ExcelHeader("DD");ExcelHeader header5 = new ExcelHeader("EE");ExcelHeader header6 = new ExcelHeader("FF");ExcelHeader header7 = new ExcelHeader("GG");ExcelHeader header8 = new ExcelHeader("HH");header8.setLock(true);List<ExcelHeader> secondHeaderList = new ArrayList<ExcelHeader>();secondHeaderList.add(header3);secondHeaderList.add(header4);secondHeaderList.add(header5);secondHeaderList.add(header6);secondHeaderList.add(header7);secondHeaderList.add(header8);List<List<ExcelHeader>> headersList = new ArrayList<List<ExcelHeader>>();headersList.add(firstHeaderList);headersList.add(secondHeaderList);Object[] rowData1 = new Object[]{"1","2","3","4","5","6"};Object[] rowData2 = new Object[]{"A1","A2","A3","A4","A5","A6"};List<Object[]> dataList = new ArrayList<>();dataList.add(rowData1);dataList.add(rowData2);MultiplExcelExportUtil exportUtil = new MultiplExcelExportUtil("测试",headersList, dataList, response);exportUtil.setLockSheet(true);exportUtil.exportExcel();}@GetMapping("download/default")public void exportDefaultExcel(HttpServletResponse response){List<String> columnList = new ArrayList<String> ();columnList.add("COLUMN1");columnList.add("COLUMN2");columnList.add("COLUMN3");columnList.add("COLUMN4");columnList.add("COLUMN5");columnList.add("COLUMN6");columnList.add("COLUMN7");List<List<ExcelHeader>> headersList = ExcelHeader.getDefaultHeadersList(columnList);Object[] rowData1 = new Object[]{"1","2","3","4","5","6","7"};Object[] rowData2 = new Object[]{"A1","A2","A3","A4","A5","A6","A7"};List<Object[]> dataList = new ArrayList<>();dataList.add(rowData1);dataList.add(rowData2);MultiplExcelExportUtil exportUtil = new MultiplExcelExportUtil(headersList, dataList, response);exportUtil.exportExcel();}}
a. 合并表头及锁定单元格例子，点击锁定单元格时不可编辑
c . 最基础的数据导出
如果觉得《POI导出支持合并表头及单元格锁定的Excel》对你有帮助，请点赞、收藏，并留下你的观点哦！
