【JAVA poi Excel导入导出，可自定义单元格样式和锁定单元格，可设置单元格下拉数据】
依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>工具类
ps : 有三个类，一个实体，一个读取工具类，一个导出工具类
1，实体
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.awt.*;
import java.io.Serializable;
import java.util.List;
//数据实体
public class ExcelData implements Serializable {
private static final long serialVersionUID = 6133772627258154184L;
/**
* 备注(可空，没有备注)
*/
private List<RemarkData> remarkList;
/**
* 表头
*/
private List<String> titles;
/**
* 表头行高（可空，默认行高）
*/
private Integer titleHeight;
/**
* 数据--不可锁定（Object为单元格内容）
*/
private List<List<Object>> rows;
/**
* 数据--可设置锁定不可编辑
*/
private List<List<RowData>> rowsLock;
/**
* 下拉框设置数据（可空，不设置下拉）
*/
private List<SelectData> selectDataList;
/**
* 页签名称
*/
private String name;
/**
* 总数
*/
private int total;
public List<RemarkData> getRemarkList() {
return remarkList;
}
public void setRemarkList(List<RemarkData> remarkList) {
this.remarkList = remarkList;
}
public List<String> getTitles() {
return titles;
}
public void setTitles(List<String> titles) {
this.titles = titles;
}
public Integer getTitleHeight() {
return titleHeight;
}
public void setTitleHeight(Integer titleHeight) {
this.titleHeight = titleHeight;
}
public List<List<Object>> getRows() {
return rows;
}
public void setRows(List<List<Object>> rows) {
this.rows = rows;
}
public List<List<RowData>> getRowsLock() {
return rowsLock;
}
public void setRowsLock(List<List<RowData>> rowsLock) {
this.rowsLock = rowsLock;
}
public List<SelectData> getSelectDataList() {
return selectDataList;
}
public void setSelectDataList(List<SelectData> selectDataList) {
this.selectDataList = selectDataList;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getTotal() {
return total;
}
public void setTotal(int total) {
this.total = total;
}
public static RemarkData getRemarkData(String content,HorizontalAlignment horizontalAlignment,IndexedColors indexedColors,Color groundColor,Boolean isLocked,Integer mergedCell,Integer rowHeight,Integer fontSize) {
RemarkData remarkData= new RemarkData();
remarkData.setContent(content);
remarkData.setHorizontalAlignment(horizontalAlignment);
remarkData.setIndexedColors(indexedColors);
remarkData.setGroundColor(groundColor);
remarkData.setLocked(isLocked);
remarkData.setMergedCell(mergedCell);
remarkData.setRowHeight(rowHeight);
remarkData.setFontSize(fontSize);
return remarkData;
}
public static class RemarkData{
//内容
private String content;
//水平位置布局（水平居中：HorizontalAlignment.CENTER，水平居左：HorizontalAlignment.LEFT）
private HorizontalAlignment horizontalAlignment;
//字体颜色（黑色：IndexedColors.BLACK，红色：IndexedColors.RED）
private IndexedColors indexedColors;
//背景颜色（可空，默认背景颜色）
private Color groundColor;
//是否锁定不可编辑
private Boolean isLocked=false;
//合并几行单元格
private Integer mergedCell;
//行高（可空，取默认）
private Integer rowHeight;
//字体大小（可空，取默认）
private Integer fontSize;
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public HorizontalAlignment getHorizontalAlignment() {
return horizontalAlignment;
}
public void setHorizontalAlignment(HorizontalAlignment horizontalAlignment) {
this.horizontalAlignment = horizontalAlignment;
}
public IndexedColors getIndexedColors() {
return indexedColors;
}
public void setIndexedColors(IndexedColors indexedColors) {
this.indexedColors = indexedColors;
}
public Color getGroundColor() {
return groundColor;
}
public void setGroundColor(Color groundColor) {
this.groundColor = groundColor;
}
public Boolean getLocked() {
return isLocked;
}
public void setLocked(Boolean locked) {
isLocked = locked;
}
public Integer getMergedCell() {
return mergedCell;
}
public void setMergedCell(Integer mergedCell) {
this.mergedCell = mergedCell;
}
public Integer getRowHeight() {
return rowHeight;
}
public void setRowHeight(Integer rowHeight) {
this.rowHeight = rowHeight;
}
public Integer getFontSize() {
return fontSize;
}
public void setFontSize(Integer fontSize) {
this.fontSize = fontSize;
}
}
public static RowData getRowData(Object content,Boolean isLocked) {
RowData rowData= new RowData();
rowData.setContent(content);
rowData.setLocked(isLocked);
return rowData;
}
public static class RowData {
//内容
private Object content;
//是否锁定不可编辑
private Boolean isLocked=false;
public Object getContent() {
return content;
}
public void setContent(Object content) {
this.content = content;
}
public Boolean getLocked() {
return isLocked;
}
public void setLocked(Boolean locked) {
isLocked = locked;
}
}
public static SelectData getSelectData(List<String> selectList,int firstRow,int lastRow,int firstCol,int lastCol) {
SelectData data= new SelectData();
data.setSelectList(selectList);
data.setFirstRow(firstRow);
data.setLastRow(lastRow);
data.setFirstCol(firstCol);
data.setLastCol(lastCol);
return data;
}
public static class SelectData {
//下拉框中的值
List<String> selectList;
//下标-从0开始
int firstRow;
int lastRow;
int firstCol;
int lastCol;
public List<String> getSelectList() {
return selectList;
}
public void setSelectList(List<String> selectList) {
this.selectList = selectList;
}
public int getFirstRow() {
return firstRow;
}
public void setFirstRow(int firstRow) {
this.firstRow = firstRow;
}
public int getLastRow() {
return lastRow;
}
public void setLastRow(int lastRow) {
this.lastRow = lastRow;
}
public int getFirstCol() {
return firstCol;
}
public void setFirstCol(int firstCol) {
this.firstCol = firstCol;
}
public int getLastCol() {
return lastCol;
}
public void setLastCol(int lastCol) {
this.lastCol = lastCol;
}
}
}2，读取工具类
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ReadExcel {
protected final Log log = LogFactory.getLog(ReadExcel.class);
private static DataFormatter formatter=new DataFormatter();
// 总行数
private int totalRows = 0;
// 总条数
private int totalCells = 0;
// 错误信息接收器
private String errorMsg;
// 构造方法
public ReadExcel() {
}
public String getErrorInfo() {
return errorMsg;
}
/**
* 此方法两个参数InputStream是字节流。isExcel2003是excel是2003还是2007版本
*
* @param is
* @param isExcel2003
* @return
* @throws IOException
*/
public List<Map<String,Object>> getExcelInfo(InputStream is, boolean isExcel2003,List<String> columns,int startRow) {
List<Map<String,Object>> retList = null;
try {
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
// 当excel是2003时
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
log.info("before");
wb = new XSSFWorkbook(is);
log.info("after");
}
retList = readExcelValueMap(wb, columns,startRow);
} catch (Exception e) {
log.error("getExcelInfo exception : ",e);
log.info(e.getMessage());
}
return retList;
}
/** 描述 :读EXCEL文件
* @Description:
* @param: @param fileName
* @param: @param Mfile
* @param: @param columns
* @param: @param startRow >=0
* @param: @return
* @return: List<Map<String,Object>>
* @author wangjunqi
* @Date 2019年3月8日 上午10:05:54
*/
public List<Map<String, Object>> getExcelInfoMap(MultipartFile Mfile, List<String> columns, int startRow) {
List<Map<String, Object>> retList = null;
InputStream is = null;
try {
// 验证文件名是否合格
if (!validateExcel(Mfile.getOriginalFilename())) {
return null;
}
// 判断文件时2003版本还是2007版本
boolean isExcel2003 = true;
if (isExcel2007(Mfile.getOriginalFilename())) {
isExcel2003 = false;
}
is = Mfile.getInputStream();
retList = getExcelInfo(is, isExcel2003,columns,startRow);
is.close();
} catch (Exception e) {
log.error("getExcelInfo exception : ", e);
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
is = null;
e.printStackTrace();
}
}
}
return retList;
}
// 得到总列数
public int getTotalCells() {
return totalCells;
}
// 得到总行数
public int getTotalRows() {
return totalRows;
}
/**
* 描述：验证EXCEL文件
*
* @param filePath
* @return
*/
public boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
/** 读取Excel里面的信息
* @Description:
* @param: @param wb
* @param: @param columns
* @param: @param startRow 起始行>=1
* @param: @return
* @return: List<Map<String,Object>>
* @author wangjunqi
* @Date 2019年3月8日 上午9:59:46
*/
private List<Map<String,Object>> readExcelValueMap(Workbook wb, List<String> columns, int startRow) {
startRow = startRow>=0?startRow:0;
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
this.totalRows = sheet.getPhysicalNumberOfRows();
Row titleRow;
// 得到Excel的列数(前提是有行数)
if (totalRows >= 2 && sheet.getRow(1) != null) {
titleRow = sheet.getRow(1);
this.totalCells = titleRow.getPhysicalNumberOfCells();
}
List<Map<String,Object>> retList = new ArrayList<Map<String,Object>>();
for (int r = startRow; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (isRowEmpty(row)) {
continue;
}
Map<String,Object> retMap = new HashMap<String,Object>();
// 循环Excel的列
totalCells = columns.size();
int length = totalCells<columns.size()?totalCells:columns.size();
for (int c = 0; c < length; c++) {
Cell cell = row.getCell(c);
String value = formatter.formatCellValue(cell);
value = StringUtils.isNotEmpty(value) ? value : "";
retMap.put(columns.get(c) , value);
}
retList.add(retMap);
}
return retList;
}
public boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null) {
return false;
}
}
return true;
}
// 解决excel类型问题，获得数值
public String getValue(Cell cell) {
String value = "";
if (null == cell) {
return value;
}
return cell.getStringCellValue().toString();
}
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}3，导出工具类
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ExcelUtils {
static Logger logger= LoggerFactory.getLogger(ExcelUtils.class);
/**
* 使用浏览器选择路径下载
* @param response
* @param fileName
* @param data
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
// 告诉浏览器用什么软件可以打开此文件
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
exportExcel(data, response.getOutputStream());
}
public static int generateExcel(ExcelData excelData, String path) throws Exception {
File f = new File(path);
FileOutputStream out = new FileOutputStream(f);
return exportExcel(excelData, out);
}
private static int exportExcel(ExcelData data, OutputStream out) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
int rowIndex = 0;
try {
String sheetName = data.getName();
if (null == sheetName) {
sheetName = "Sheet1";
}
XSSFSheet sheet = wb.createSheet(sheetName);
if(CollectionUtils.isEmpty(data.getRows()) && CollectionUtils.isNotEmpty(data.getRowsLock())){
//设置表格锁定
sheet.enableLocking();
}
rowIndex = writeExcel(wb, sheet, data);
wb.write(out);
} catch (Exception e) {
logger.error("exportExcel系统异常：",e);
} finally {
//此处需要关闭 wb 变量
out.close();
}
return rowIndex;
}
/**
* 表不显示字段
* @param wb
* @param sheet
* @param data
* @return
*/
// private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
// int rowIndex = 0;
// writeTitlesToExcel(wb, sheet, data.getTitles());
// rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
// autoSizeColumns(sheet, data.getTitles().size() + 1);
// return rowIndex;
// }
/**
* 表显示字段
* @param wb
* @param sheet
* @param data
* @return
*/
private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
if(CollectionUtils.isNotEmpty(data.getSelectDataList())){
//设置下拉列表填充--row和col从0开始
for(ExcelData.SelectData selectData : data.getSelectDataList()){
setDropdownToExcel(sheet,selectData.getSelectList().toArray(new String[selectData.getSelectList().size()]),selectData.getFirstRow(),selectData.getLastRow(),selectData.getFirstCol(),selectData.getLastCol());
}
}
int rowIndex = 0;
rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles(),data.getRemarkList(),data.getTitleHeight());
if(CollectionUtils.isNotEmpty(data.getRows())){
rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
}else if(CollectionUtils.isNotEmpty(data.getRowsLock())){
rowIndex = writeRowsToExcelAllowLock(wb, sheet, data.getRowsLock(), rowIndex);
}
//设置列宽
autoSizeColumns(sheet, data.getTitles().size() + 1);
return rowIndex;
}
/**
* 设置表头
*
* @param wb
* @param sheet
* @param titles
* @return
*/
private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles, List<ExcelData.RemarkData> remarkList,Integer titleHeight) {
int rowIndex = 0;
//设置备注
if(CollectionUtils.isNotEmpty(remarkList)){
for(ExcelData.RemarkData remarkData : remarkList){
//备注样式
XSSFCellStyle remarkStyleLeft = getTitleStyle(wb,remarkData.getHorizontalAlignment(),remarkData.getIndexedColors(),remarkData.getGroundColor(),remarkData.getLocked(),false,false,remarkData.getFontSize());
//设置备注行
Row remarkRow = sheet.createRow(rowIndex);
if(remarkData.getRowHeight()!=null){
remarkRow.setHeightInPoints(remarkData.getRowHeight()); // 行高
}else{
remarkRow.setHeightInPoints(20); // 行高
}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, remarkData.getMergedCell()-1));
Cell remarkCell = remarkRow.createCell(0);
remarkCell.setCellValue(remarkData.getContent());
remarkCell.setCellStyle(remarkStyleLeft);
rowIndex++;
}
}
//标题样式水平居中
XSSFCellStyle titleStyle = getTitleStyle(wb,HorizontalAlignment.CENTER,IndexedColors.BLACK,null,true,true,false,null);
//标题样式水平居中换行
XSSFCellStyle titleStyleWrapText = getTitleStyle(wb,HorizontalAlignment.CENTER,IndexedColors.BLACK,null,true,true,true,null);
//设置标题
Row titleRow = sheet.createRow(rowIndex);
if(titleHeight!=null){
titleRow.setHeightInPoints(titleHeight); // 行高
}else{
titleRow.setHeightInPoints(20); // 行高
}
int colIndex = 0;
for (String field : titles) {
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
if(field.indexOf("\r\n")>-1) {
cell.setCellStyle(titleStyleWrapText);
}else {
cell.setCellStyle(titleStyle);
}
colIndex++;
}
rowIndex++;
return rowIndex;
}
/**
* 获取表头样式
*/
private static XSSFCellStyle getTitleStyle(XSSFWorkbook wb,HorizontalAlignment horizontalAlignment,IndexedColors indexedColors,Color groundColor,boolean isLocked,boolean isSetBorder,boolean isWrapText,Integer fontSize) {
Font titleFont = wb.createFont();
//设置字体
titleFont.setFontName("Century");
//设置粗体
titleFont.setBold(true);
//设置字号
if(fontSize==null){
fontSize=12;
}
titleFont.setFontHeightInPoints(Short.valueOf(String.valueOf(fontSize)));
//设置颜色
titleFont.setColor(indexedColors.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
//水平居中
titleStyle.setAlignment(horizontalAlignment);
//垂直居中
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
if(isWrapText){
titleStyle.setWrapText(true);
}
//设置图案颜色
if(groundColor!=null){
titleStyle.setFillForegroundColor(new XSSFColor(groundColor));
}else{
titleStyle.setFillForegroundColor(new XSSFColor(new Color(218, 218, 218)));
}
//设置图案样式
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFont(titleFont);
titleStyle.setLocked(isLocked);
if(isSetBorder){
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
}
return titleStyle;
}
/**
* 设置内容
*
* @param wb
* @param sheet
* @param rows
* @param rowIndex
* @return
*/
private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
int colIndex;
XSSFCellStyle dataStyle = getCellStyle(wb,false,false);
XSSFCellStyle dataStyleWrapText = getCellStyle(wb,true,false);
if(rows!=null && !rows.isEmpty()){
for (List<Object> rowData : rows) {
Row dataRow = sheet.createRow(rowIndex);
dataRow.setHeightInPoints(20);
colIndex = 0;
for (Object cellData : rowData) {
Cell cell = dataRow.createCell(colIndex);
if (cellData != null) {
cell.setCellValue(cellData.toString());
if(cellData.toString().indexOf("\r\n")>-1) {
cell.setCellStyle(dataStyleWrapText);
}else {
cell.setCellStyle(dataStyle);
}
} else {
cell.setCellValue("");
cell.setCellStyle(dataStyle);
}
colIndex++;
}
rowIndex++;
}
}
return rowIndex;
}
/**
* 设置内容--支持设置锁定不可编辑
*
* @param wb
* @param sheet
* @param rowsLock
* @param rowIndex
* @return
*/
private static int writeRowsToExcelAllowLock(XSSFWorkbook wb, Sheet sheet, List<List<ExcelData.RowData>> rowsLock, int rowIndex) {
int colIndex;
XSSFCellStyle dataStyle = getCellStyle(wb,false,false);
XSSFCellStyle dataStyleWrapText = getCellStyle(wb,true,false);
XSSFCellStyle dataStyleLocked = getCellStyle(wb,false,true);
XSSFCellStyle dataStyleWrapTextLocked = getCellStyle(wb,true,true);
if(CollectionUtils.isNotEmpty(rowsLock)){
for (List<ExcelData.RowData> rowDataList : rowsLock) {
Row dataRow = sheet.createRow(rowIndex);
dataRow.setHeightInPoints(20);
colIndex = 0;
for (ExcelData.RowData rowData : rowDataList) {
Cell cell = dataRow.createCell(colIndex);
boolean isLock=rowData.getLocked();
String content=rowData.getContent() == null?"":rowData.getContent().toString();
cell.setCellValue(content);
if(content.indexOf("\r\n")>-1) {
if(isLock){
cell.setCellStyle(dataStyleWrapTextLocked);
}else{
cell.setCellStyle(dataStyleWrapText);
}
}else {
if(isLock){
cell.setCellStyle(dataStyleLocked);
}else{
cell.setCellStyle(dataStyle);
}
}
colIndex++;
}
rowIndex++;
}
}
return rowIndex;
}
/**
* 自动调整列宽
*
* @param sheet
* @param columnNumber
*/
private static void autoSizeColumns(Sheet sheet, int columnNumber) {
for (int i = 0; i < columnNumber; i++) {
// int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
sheet.setColumnWidth(i, 3500);
// int newWidth = (int) (sheet.getColumnWidth(i) + 100);
// if (newWidth > orgWidth) {
// sheet.setColumnWidth(i, newWidth);
// } else {
// sheet.setColumnWidth(i, orgWidth);
// }
}
}
/**
* 获取单元格样式
*/
private static XSSFCellStyle getCellStyle(XSSFWorkbook wb,boolean isWrapText,boolean isLocked) {
Font dataFont = wb.createFont();
dataFont.setFontName("SimHei");
dataFont.setFontHeightInPoints((short) 12);
dataFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle dataStyle = wb.createCellStyle();
if(isWrapText){
dataStyle.setWrapText(true);
}
dataStyle.setAlignment(HorizontalAlignment.LEFT);//水平布局
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直布局
dataStyle.setFont(dataFont);
dataStyle.setLocked(isLocked);
setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
return dataStyle;
}
/**
* 设置边框
*
* @param style
* @param border
* @param color
*/
private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
style.setBorderBottom(border);
style.setBorderColor(BorderSide.TOP, color);
style.setBorderColor(BorderSide.LEFT, color);
style.setBorderColor(BorderSide.RIGHT, color);
style.setBorderColor(BorderSide.BOTTOM, color);
}
/**
* <p>
* Description: 设置下拉框到excel
* </p>
* @author tanxin
* @param sheet
* @param selectList 下拉框中的值
* @param firstRow 下标-从0开始
* @param lastRow
* @param firstCol
* @param lastCol
*/
private static void setDropdownToExcel(Sheet sheet,String[] selectList, int firstRow, int lastRow, int firstCol, int lastCol ){
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol) ;
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(selectList);
DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
//处理Excel兼容性问题
if (createValidation instanceof XSSFDataValidation) {
createValidation.setSuppressDropDownArrow(true);
createValidation.setShowErrorBox(true);
} else {
createValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(createValidation);
}
}测试类
//导入
@ResponseBody
@PostMapping("/imports")
public ResultVo<List<JSONObject>> imports(@ApiParam("导入文件") @RequestParam("file") MultipartFile file ) {
try{
// 处理EXCEL
ReadExcel readExcel = new ReadExcel();
// 获得解析excel方法，此处按列排序设置key，userName为第一列
List<String> columns = new ArrayList<>();
columns.add("userName");
columns.add("age");
columns.add("sex");
//读取数据，数字表示从第几行开始，第一行为0
List<Map<String, Object>> retList = readExcel.getExcelInfoMap(file,columns,1);
for(Map<String, Object> obj : retList) {
String userName=obj.get("userName")==null?"":obj.get("userName").toString();
}
}catch (Exception e){
e.printStackTrace();
}
}//导出
@ResponseBody
@PostMapping("/download")
public void download(@RequestBody Bean Bean, HttpServletResponse response ) throws Exception {
//设置下拉填充列表数据
List<String> nameSelect=new ArrayList<>();
nameSelect.add("1");
nameSelect.add("2");
nameSelect.add("3");
//设置下拉，第5-51行，4-11列，单元格填充下拉数据
List<ExcelData.SelectData> selectDataList=new ArrayList<>();
selectDataList.add(ExcelData.getSelectData(nameSelect,4,50,3,10));
//备注
int remarkMergedCell=11;//设置单行单元格合并数
List<ExcelData.RemarkData> remarkList=new ArrayList<>();
remarkList.add(ExcelData.getRemarkData("备注1", HorizontalAlignment.LEFT, IndexedColors.WHITE,new Color(62, 30, 102),true,remarkMergedCell,30,20));
remarkList.add(ExcelData.getRemarkData("备注2", HorizontalAlignment.LEFT, IndexedColors.RED,new Color(255, 255, 255),true,remarkMergedCell,null,null));
remarkList.add(ExcelData.getRemarkData("备注3", HorizontalAlignment.LEFT, IndexedColors.BLACK,new Color(255, 255, 255),true,remarkMergedCell,null,null));
//设置数据
List<List<ExcelData.RowData>> rowsLock = new ArrayList<>();
for(int i=0;i<50;i++){
List<ExcelData.RowData> row = new ArrayList<>();
row.add(ExcelData.getRowData("张三",true));//为true则锁定单元格不可编辑
row.add(ExcelData.getRowData("20",true));
row.add(ExcelData.getRowData("男",true));
rowsLock.add(row);
}
ExcelData data = new ExcelData();
data.setName("数据");
//设置表头
List<String> titles = new ArrayList<>();
titles.add("姓名");
titles.add("年龄");
titles.add("性别");
data.setRemarkList(remarkList);
data.setTitles(titles);
data.setTitleHeight(35);//标题行高
data.setRowsLock(rowsLock);//设置导出数据
data.setSelectDataList(selectDataList);//下拉数据设置
ExcelUtils.exportExcel(response, "数据文件" ,data);
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
