POI导出支持合并表头及单元格锁定的Excel

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 org.apache.commons.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 java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.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;