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;