Java EXCEL 表格导入导出(带下拉选-带VLOOKUP函数封装)
对于excel Java POI 使用
目前简单导出导入功能网上很多,但是对于有下拉选,样式等缺点却是最大硬伤,故此封装一个通用点的Excel 表格工具类,方便大家使用!
- 模板数据填充
- 下拉选数据规则
- 导出实体类映射
本次工具类设计工作原理
1.你需要有一个模板 把样式设置好
2.需要有三个工作簿2.1第一个是用户使用的添加数据的, 第二个是下拉选项,第三个是列对应后台实体类属性,只不过是第二个和第三个工作薄在代码中读取后会自动为您隐藏
直接上图
- 一定要看下边俩个图哦.
表格工具类
package com.techhero.base.exam.utils.excel;
import com.techhero.base.exam.model.abs.ExcelList;
import com.techhero.base.exam.model.abs.ExcelTemplateModel;
import com.techhero.base.exam.model.abs.OptionNode;
import com.techhero.base.exam.model.abs.SelectOption;
import com.techhero.common.utils.req.CMap;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.Converter;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationErrorStyle;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import .URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
@Slf4j
public class ExcelUtils {
/**
* 导入数据
* 根据传入的实体对象,将数据构建成一个List
*/
public static <T> ExcelList<T> importData(MultipartFile multipartFile, Integer readFirstIndex, Class clazz, String... validName) {
Workbook workBook = getWorkbook(multipartFile);
/*列名工作区*/
assert workBook != null;
Sheet colNameSheet = workBook.getSheetAt(2);
/*第一行*/
Row row = colNameSheet.getRow(0);
/*获取表格中定义的列名称(一般对应Class中的字段)*/
List<String> excelColNames = new ArrayList<>(row.getLastCellNum());
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
excelColNames.add(cell.getStringCellValue());
}
/*数据工作薄*/
Sheet dataSheet = workBook.getSheetAt(0);
int lastRowNum = dataSheet.getLastRowNum();
ExcelList<T> list = new ExcelList<>();
List<Integer> errorRowNums = new ArrayList<>();
validFor:
for (int i = readFirstIndex - 1; i < lastRowNum; i++) {
Row dataRow = dataSheet.getRow(i);
/*Map对应一行数据*/
Map<String, String> beanMap = new HashMap<>(excelColNames.size());
for (int n = 0; n < excelColNames.size(); n++) {
Cell cell = dataRow.getCell(n);
String cellValue = getCellValue(cell);
if (n == 0 && StringUtils.isBlank(cellValue)) {
break validFor;
}
beanMap.put(excelColNames.get(n), cellValue);
}
/*校验数据为空合法性*/
if (validName != null) {
for (int k = 0; k < validName.length; k++) {
if (beanMap.get(validName[k]) == null && StringUtils.isNotEmpty(beanMap.get(validName[k]))) {
errorRowNums.add(i);
continue validFor;
}
}
}
try {
T o = (T) clazz.newInstance();
ConvertUtils.register(new Converter() {
public Object convert(Class type, Object value) {
if (value != null) {
try {
return DateUtils.parseDate(value.toString(),
"yyyy-MM-dd HH:mm:ss",
"yyyy-MM-dd HH:mm",
"yyyy-MM-dd",
"yyyy/MM/dd HH:mm:ss",
"yyyy/MM/dd HH:mm",
"yyyy/MM/dd",
"yyyyMMdd HH:mm:ss",
"yyyyMMdd HH:mm",
"yyyyMMdd",
"yyyy年MM月dd日 HH:mm:ss",
"yyyy年MM月dd日 HH:mm",
"yyyy年MM月dd日",
"yyyyMMddHHmmss");
} catch (ParseException e) {
log.info("[时间转换失败]");
return null;
}
}
return null;
}
}, Date.class);
/*将Map中的属性拷贝到实体类中*/
BeanUtils.populate(o, beanMap);
list.add(o);
} catch (Exception e) {
log.info("[数据导入-{}条数据有问题]", i, e);
}
}
list.setErrorRows(errorRowNums);
Row supperDataRow = dataSheet.getRow(1);
Cell cell = supperDataRow.getCell(0);
String supperData = cell.getStringCellValue();
list.setSupperData(supperData);
return list;
}
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 判断数据的类型
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
//short s = cell.getCellStyle().getDataFormat();
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf;
// 验证short值
if (cell.getCellStyle().getDataFormat() == 14) {
sdf = new SimpleDateFormat("yyyy/MM/dd");
} else if (cell.getCellStyle().getDataFormat() == 21) {
sdf = new SimpleDateFormat("HH:mm:ss");
} else if (cell.getCellStyle().getDataFormat() == 22) {
sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
} else {
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
cell.setCellType(CellType.STRING);
cellValue = String.valueOf(cell.getRichStringCellValue().getString());
}
break;
case STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: // 公式
try {
cellValue = String.valueOf(cell.getStringCellValue());
} catch (Exception exception) {
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
case BLANK: // 空值
cellValue = null;
break;
case ERROR: // 故障
cellValue = "非法";
break;
default:
cellValue = "未知";
break;
}
return cellValue;
}
/*获取工作簿*/
public static Workbook getWorkbook(MultipartFile multipartFile) {
String originalFilename = multipartFile.getOriginalFilename();
String fileType = originalFilename.substring(originalFilename.lastIndexOf("."));
try {
return getWorkbook(multipartFile.getInputStream(), fileType);
} catch (IOException e) {
log.error("EXCEL 导入 操作失败!", e);
return null;
}
}
/*获取工作簿*/
public static Workbook getWorkbook(InputStream inStr, String fileName) {
String fileType = fileName.substring(fileName.lastIndexOf("."));
try {
if (".xls".equals(fileType)) {
return new XSSFWorkbook(inStr);
}
return new XSSFWorkbook(inStr);
} catch (IOException e) {
return null;
}
}
/*Excel模板下载*/
public static void downLoadExcelTemplate(HttpServletResponse response, ExcelTemplateModel model) {
log.info("[EXCEL TOOL BUILDER -- BUILDER PARAMS LOOK]\n{}", model);
/*初始化模板行*/
model.initTemplateInitRowSize();
Workbook workbook = downLoadExcelTemplate(model);
try (ServletOutputStream outputStream = response.getOutputStream()) {
response.setContentType("application/vnd.ms-excel");
response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
response.addHeader("charset", "utf-8");
response.addHeader("Pragma", "no-cache");
String encodeName = URLEncoder.encode(model.getExcelName(), StandardCharsets.UTF_8.toString());
response.setHeader("Content-Disposition", "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);
assert workbook != null;
workbook.write(outputStream);
} catch (IOException e) {
log.info("[下载Excel模板失败!]", e);
}
}
/*获取下载Excel模板*/
public static Workbook downLoadExcelTemplate(ExcelTemplateModel model) {
/*创建工作薄*/
try {
XSSFWorkbook workBook = new XSSFWorkbook(model.getResourceTemplateFile());
/*头部设置*/
if (model.isHasHand()) {
handHandler(workBook, model);
}
/*下拉设定*/
if (model.isHasSelectOptions()) {
/*列名称处理*/
colNamesHandler(workBook, model);
/*下拉选数据填充*/
loadSelectOptionHandler(workBook, model);
/*绑定下拉选*/
bindSelectOptionHandler(workBook, model);
/*绑定下拉选对应编码函数*/
bindSelectOptionCodeHandler(workBook, model);
/*编码列隐藏处理*/
hiddenColHandler(workBook, model);
}
return workBook;
} catch (Exception exception) {
log.error("[下载模板-{}]==>异常", model.getFilePath(), exception);
}
return null;
}
/*编码列隐藏处理*/
private static void hiddenColHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
/*模板工作薄*/
XSSFSheet templateSheet = workBook.getSheetAt(0);
if (model.isHasSelectOptions()) {
model.getSelectOptions().forEach(selectOption -> templateSheet.setColumnHidden(selectOption.getColNameCodeIndex(), selectOption.isCodeColHiddenFlag()));
}
}
/*绑定下拉选对应编码函数*/
private static void bindSelectOptionCodeHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
final String[] colNames = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
/*模板工作薄*/
XSSFSheet templateSheet = workBook.getSheetAt(0);
/*下拉选数据工作簿*/
XSSFSheet optionDataSheet = workBook.getSheetAt(1);
String sheetName = optionDataSheet.getSheetName();
int templateInitRowSize = model.getTemplateInitRowSize();
List<SelectOption> selectOptions = model.getSelectOptions();
if (model.isHasSelectOptions()) {
for (int i = model.getSelectOptionRowNum() - 1; i < templateInitRowSize; i++) {
/*创建行*/
XSSFRow row = templateSheet.createRow(i);
int finalI = i + 1;
selectOptions.forEach(selectOption -> {
if (selectOption.isNeedBindCodeFunction()) {
/*创建对应的编码列*/
XSSFCell cell = row.createCell(selectOption.getColNameCodeIndex());
String cellFormula = "VLOOKUP({0},{1},2,0)";
/*当前模板工作薄名称对应列下标*/
int colNameIndex = selectOption.getColNameIndex();
/*当前模板工作薄名称对应列下标字母*/
String colNameForTemplateSheet = colNames[colNameIndex];
/*通过那个数据列的数据关联当前表格*/
String sourceDataCol = colNameForTemplateSheet + finalI;
int keyColIndex = selectOption.getKeyColIndex();
String keyColName = colNames[keyColIndex];
int valueColIndex = selectOption.getValueColIndex();
String valueColName = colNames[valueColIndex];
String dataArea = sheetName + "!" + valueColName + ":" + keyColName;
MessageFormat format = new MessageFormat(cellFormula);
cellFormula = format.format(new String[]{sourceDataCol, dataArea});
cell.setCellFormula(cellFormula);
}
});
}
}
}
/*绑定下拉选*/
private static void bindSelectOptionHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
/*模板工作薄*/
XSSFSheet templateSheet = workBook.getSheetAt(0);
/*下拉选数据工作簿*/
XSSFSheet optionDataSheet = workBook.getSheetAt(1);
String sheetName = optionDataSheet.getSheetName();
List<SelectOption> selectOptions = model.getSelectOptions();
selectOptions.forEach(selectOption -> templateSheet.addValidationData(getValidationData(sheetName, model, selectOption)));
}
/*设置有效性*/
private static DataValidation getValidationData(String sheetName, ExcelTemplateModel model, SelectOption selectOption) {
int firstRowNum = model.getSelectOptionRowNum() - 1;
int templateInitRowSize = model.getTemplateInitRowSize();
/*对应模板工作薄列下标*/
int colNameIndex = selectOption.getColNameIndex();
/*唯一属性对应模板工作薄列*/
int maxRowNum = selectOption.getMaxRowNum();
/*值对应数据薄列*/
int valueColIndex = selectOption.getValueColIndex();
final String[] colNames = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
String colName = colNames[valueColIndex];
/*有效性表达式*/
String strFormula = sheetName + "!$" + colName + "$1:$" + colName + "$" + maxRowNum;
XSSFDataValidationConstraint xssfDataValidationConstraint = new XSSFDataValidationConstraint(0x03, strFormula);
/*设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列*/
CellRangeAddressList regions = new CellRangeAddressList(firstRowNum, templateInitRowSize, colNameIndex, colNameIndex);
CTDataValidation newDataValidation = CTDataValidation.Factory.newInstance();
CellRangeAddress[] cellRangeAddresses = regions.getCellRangeAddresses();
List<String> ref = new ArrayList<String>();
for (int i = 0; i < cellRangeAddresses.length; i++) {
CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
ref.add(cellRangeAddress.formatAsString());
}
newDataValidation.setSqref(ref);
newDataValidation.setType(STDataValidationType.LIST);
newDataValidation.setFormula1(strFormula);
newDataValidation.setAllowBlank(true);
newDataValidation.setErrorStyle(STDataValidationErrorStyle.STOP);
return new XSSFDataValidation(xssfDataValidationConstraint, regions, newDataValidation);
}
/*下拉选数据填充*/
private static void loadSelectOptionHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
/*下拉选数据工作簿*/
XSSFSheet optionDataSheet = workBook.getSheetAt(1);
/*设置工作簿名称*/
workBook.setSheetName(1, "DATA");
/*设置工作簿隐藏*/
workBook.setSheetHidden(1, true);
/*下拉选数据*/
List<SelectOption> selectOptions = model.getSelectOptions();
/*获取最大行号*/
AtomicInteger maxRowNumAtomicInteger = new AtomicInteger();
selectOptions.forEach(selectOption -> {
List<OptionNode> options = selectOption.getOptions();
if (selectOption.getMaxRowNum() > maxRowNumAtomicInteger.get()) {
maxRowNumAtomicInteger.set(options.size());
}
});
List<List<OptionNode>> rowOptions = new ArrayList<>();
for (int i = 0; i < maxRowNumAtomicInteger.get(); i++) {
List<OptionNode> rowOption = new ArrayList<>();
int finalI = i;
selectOptions.forEach(selectOption -> {
List<OptionNode> options = selectOption.getOptions();
if (finalI < options.size()) {
rowOption.add(options.get(finalI));
} else {
rowOption.add(OptionNode.builder().build());
}
});
rowOptions.add(rowOption);
}
log.info("[拼装表格下拉选数据]#{}", rowOptions);
for (int i = 0; i < rowOptions.size(); i++) {
XSSFRow row = optionDataSheet.createRow(i);
List<OptionNode> optionNodes = rowOptions.get(i);
for (int k = 0, x = 0; k < optionNodes.size() * 2; k = k + 2, x++) {
OptionNode optionNode = optionNodes.get(x);
if (optionNode.isAddCol()) {
XSSFCell valueCell = row.createCell(k);
valueCell.setCellType(CellType.STRING);
XSSFCell keyCell = row.createCell(k + 1);
keyCell.setCellType(CellType.STRING);
keyCell.setCellValue(optionNode.getKey());
valueCell.setCellValue(optionNode.getValue());
}
}
}
}
/*列名称处理*/
private static void colNamesHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
/*列名工作区*/
XSSFSheet colNameSheet = workBook.getSheetAt(2);
/*设置工作簿名称*/
workBook.setSheetName(2, "COLNAMES");
/*设置工作簿隐藏*/
workBook.setSheetHidden(2, true);
/*第一行*/
XSSFRow row = colNameSheet.getRow(0);
/*下拉选项*/
List<SelectOption> selectOptions = model.getSelectOptions();
/*数据薄角标标记*/
int index = 0;
for (SelectOption selectOption : selectOptions) {
selectOption.setKeyAndValueIndex(index);
String colName = selectOption.getColName();
String colNameCode = selectOption.getColNameCode();
rowFor:
for (int i = 0; i < row.getLastCellNum(); i++) {
XSSFCell cell = row.getCell(i);
if (StringUtils.isNotBlank(cell.getStringCellValue()) && cell.getStringCellValue().trim().equals(colName)) {
selectOption.setColNameIndex(i);
break rowFor;
}
}
if (selectOption.isNeedBindCodeFunction()) {
rowFor:
for (int i = 0; i < row.getLastCellNum(); i++) {
XSSFCell cell = row.getCell(i);
if (StringUtils.isNotBlank(cell.getStringCellValue()) && cell.getStringCellValue().trim().equals(colNameCode)) {
selectOption.setColNameCodeIndex(i);
break rowFor;
}
}
}
index += 2;
}
}
/*头部数据处理*/
private static void handHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
/*第一个工作区*/
XSSFSheet templateSheet = workBook.getSheetAt(0);
/*标题行*/
XSSFRow titleRow = templateSheet.getRow(0);
/*标题行第一列*/
XSSFCell titleRowCell = titleRow.getCell(0);
/*头信息值*/
String handValue = titleRowCell.getStringCellValue();
/*替换占位符数据*/
MessageFormat format = new MessageFormat(handValue);
handValue = format.format(model.getHandData());
/*将数据回填头部第一列中*/
titleRowCell.setCellValue(handValue);
/*回显数据设置*/
if (model.isHasSupperData()) {
Row row = templateSheet.getRow(1);
Cell cell = row.getCell(0);
cell.setCellValue(model.getSupperData());
}
}
/*测试模板生成函数*/
public static void downLoadExcelTemplateToLocal(ExcelTemplateModel model) {
Workbook workbook = downLoadExcelTemplate(model);
File file = new File("/Users/cnnoter/Data/TempData/CsTemplate2.xlsx"); //写文件
try (FileOutputStream out = new FileOutputStream(file)) {
out.flush();
assert workbook != null;
workbook.write(out);
} catch (Exception e) {
log.info("测试失败", e);
}
}
/*数据导出*/
public static void exportData(HttpServletResponse response, ExcelTemplateModel model) {
try (InputStream in = new FileInputStream(model.getFilePath())) {
Workbook workBook = getWorkbook(in, model.getFilePath());
/*第一个工作区*/
assert workBook != null;
Sheet templateSheet = workBook.getSheetAt(0);
/*标题行*/
Row titleRow = templateSheet.getRow(0);
/*标题行第一列*/
Cell titleRowCell = titleRow.getCell(0);
/*头信息值*/
String handValue = titleRowCell.getStringCellValue();
/*替换占位符数据*/
MessageFormat format = new MessageFormat(handValue);
handValue = format.format(model.getHandData());
/*将数据回填头部第一列中*/
titleRowCell.setCellValue(handValue);
/*列名工作区*/
Sheet colNameSheet = workBook.getSheetAt(1);
/*设置工作簿名称*/
workBook.setSheetName(1, "COLNAMES");
/*设置工作簿隐藏*/
workBook.setSheetHidden(1, true);
/*第一行*/
Row row = colNameSheet.getRow(0);
short lastCellNum = row.getLastCellNum();
/*列名*/
Map<String, Integer> colNames = new HashMap<>();
for (int i = 0; i < lastCellNum; i++) {
Cell cell = row.getCell(i);
String colName = cell.getStringCellValue();
colNames.put(colName, i);
}
Set<String> colNameSet = colNames.keySet();
List exportData = model.getExportData();
for (int i = 0; i < exportData.size(); i++) {
Object obj= exportData.get(i);
CMap data = CMap.builderParamByBean(obj);
Row exportRow = templateSheet.createRow(i + model.getExportFirstRowNum() - 1);
/*序号列*/
Cell cell = exportRow.createCell(0);
cell.setCellValue(i + 1);
/*数据列*/
colNameSet.forEach(colName -> {
Integer indexCell = colNames.get(colName);
Cell dataCell = exportRow.createCell(indexCell+1);
dataCell.setCellValue(data.getValue(colName));
});
}
log.info("[EXCEL TOOL BUILDER -- BUILDER PARAMS LOOK]\n{}", model);
try (ServletOutputStream outputStream = response.getOutputStream()) {
response.setContentType("application/vnd.ms-excel");
response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
response.addHeader("charset", "utf-8");
response.addHeader("Pragma", "no-cache");
String encodeName = URLEncoder.encode(model.getExcelName(), StandardCharsets.UTF_8.toString());
response.setHeader("Content-Disposition", "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);
workBook.write(outputStream);
outputStream.flush();
} catch (IOException e) {
log.info("[下载Excel模板失败!]", e);
}
} catch (Exception e) {
log.info("导出模板失败", e);
}
}
}参数封装对象
package com.techhero.base.exam.model.abs;
import com.techhero.common.utils.StringUtil;
import lombok.*;
import java.io.File;
import java.io.Serializable;
import java.util.List;
/*Excel 创建模板数据封装对象*/
@Data
@ToString
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class ExcelTemplateModel implements Serializable {
/*模板路径*/
private String filePath;
/*表格名称*/
private String excelName;
/*下拉选从第几行设置*/
private int selectOptionRowNum;
/*导出表格时从第几行开始添加数据*/
private int exportFirstRowNum;
/*初始化模板工作薄行数*/
private int templateInitRowSize;
/*头部数据-使用占位符代替*/
private String[] handData;
/*存放一些导入有关的数据*/
private String supperData;
/*选项*/
private List<SelectOption> selectOptions;
/*导出数据*/
private List<?> exportData;
/*获取文件路径*/
public File getResourceTemplateFile(){
return new File(this.filePath);
}
/*是否有头信息更换*/
public boolean isHasHand(){
return handData!=null&& handData.length>0;
}
/*是否有下拉选*/
public boolean isHasSelectOptions(){
return selectOptions!=null && !selectOptions.isEmpty();
}
/*是否需要填充回显数据*/
public boolean isHasSupperData(){
return StringUtil.isNotEmpty(supperData);
}
public void initTemplateInitRowSize(){
if(this.templateInitRowSize==0){
this.templateInitRowSize=5000;
}
}
}package com.techhero.base.exam.model.abs;
import lombok.Data;
import lombok.ToString;
import org.apache.commons.lang3.StringUtils;
import java.io.Serializable;
import java.util.List;
@Data
@ToString
public class SelectOption implements Serializable {
/*列名称(对应英文名称)*/
private String colName;
/*列名称代码(对应英文名称代码)*/
private String colNameCode;
/*列隐藏标记*/
private boolean codeColHiddenFlag;
/*对应模板工作薄名称列下标*/
private int colNameIndex;
/*对应模板工作薄名称代码列下标*/
private int colNameCodeIndex;
/*唯一属性对应模板工作薄列*/
private int keyColIndex;
/*值对应数据薄列*/
private int valueColIndex;
/*选项*/
private List<OptionNode> options;
/*私有化全参构造函数*/
private SelectOption(String colName, List<OptionNode> options) {
this.colName = colName;
this.options = options;
}
/*私有化全参构造函数*/
private SelectOption(String colName,String colNameCode, List<OptionNode> options,boolean codeColHiddenFlag) {
this.colName = colName;
this.options = options;
this.colNameCode= colNameCode;
this.codeColHiddenFlag=codeColHiddenFlag;
}
public static SelectOption builder(String colName, List<OptionNode> options) {
return new SelectOption(colName,options);
}
public static SelectOption builder(String colName,String colNameCode, List<OptionNode> options,boolean codeColHiddenFlag) {
return new SelectOption(colName,colNameCode,options, codeColHiddenFlag);
}
public static SelectOption builder(String colName,String colNameCode, List<OptionNode> options) {
return new SelectOption(colName,colNameCode,options, true);
}
/*设置数据薄列对应管理*/
public void setKeyAndValueIndex(int index){
this.valueColIndex=index;
this.keyColIndex=index+1;
}
/*是否需要绑定编码函数*/
public boolean isNeedBindCodeFunction(){
return StringUtils.isNotBlank(this.colNameCode);
}
/*获取最大行号*/
public int getMaxRowNum(){
if(this.options!=null){
return this.options.size();
}
return 0;
}
}package com.techhero.base.exam.model.abs;
import lombok.*;
// 使用map有点不方便,还不如自己封装一个key-value 对象
@Data
@AllArgsConstructor
@ToString
@NoArgsConstructor
@Builder
public class OptionNode {
/*唯一数据*/
private String key;
/*唯一值*/
private String value;
public boolean isAddCol(){
return key!=null&&value!=null;
}
}package com.techhero.common.utils.req;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.annotation.JsonIgnore;
import com.techhero.common.model.base.ToDate;
import com.techhero.common.utils.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
//其实就是一个HashMap 但是里边有很多工具方法,方便使用
@Slf4j
public class CMap extends HashMap<String, Object> implements Serializable {
/*分页页码标记*/
public static final String PAGE_NUM = "pageNum";
/*分页步长*/
public static final String PAGE_SIZE = "pageSize";
/* 当前时间 */
public static final String CURRENT_DATE = "CURRENT_DATE";
/*获取分页页码*/
public Integer getPageNum() {
return getPageParam(PAGE_NUM);
}
/*获取分页步长*/
public Integer getPageSize() {
return getPageParam(PAGE_SIZE);
}
private Integer getPageParam(String pageParamType) {
try {
Object v = this.get(pageParamType);
if (v == null)
return -1;
return Integer.valueOf(v.toString());
} catch (Exception e) {
e.printStackTrace();
}
return -1;
}
/**
* 获取字符串值
*/
public String getValue(String key) {
Object v = this.get(key);
if (v == null) return null;
return v.toString();
}
/*获取Integer数据*/
public Integer getIntegerValue(String key) {
try {
Object v = this.get(key);
if (v == null)
return null;
return Integer.valueOf(v.toString());
} catch (Exception e) {
log.info("cMap数据转换Integer 错误");
}
return null;
}
/*带默认值获取Integer数据*/
public Integer getIntegerValue(String key, Integer defaultValue) {
Integer value = getIntegerValue(key);
return value == null ? defaultValue : value;
}
/*获取Short数据*/
public Short getShortValue(String key) {
try {
Object v = this.get(key);
if (v == null)
return null;
return Short.valueOf(v.toString());
} catch (Exception e) {
log.info("cMap数据转换Short 错误");
}
return null;
}
/*获取带默认值Short数据*/
public Short getShortValue(String key, Short defaultValue) {
Short value = getShortValue(key);
return defaultValue == null ? value : defaultValue;
}
/*获取Date类型数据*/
public Date getDateValue(String key) {
Object value = get(key);
if (value == null) return null;
String dateStr = value.toString();
long ltime;
Date date = null;
if (StringUtils.isNotEmpty(dateStr)) {
try {
ltime = Long.parseLong(dateStr);
date = new Date(ltime);
} catch (Exception e) {
try {
date = DateUtils.parseDate(dateStr);
} catch (Exception ex) {
log.error("DATE 类型转换失败", ex);
}
}
}
return date;
}
/**
* 获取Double数据
*/
public Double getDoubleValue(String key) {
try {
Object v = this.get(key);
if (v == null)
return null;
return Double.valueOf(v.toString());
} catch (Exception e) {
log.info("cMap数据转换Double 错误");
}
return null;
}
/**
* 获取当前时间,前端使用获取本人写的获取请求参数时候回自动在该map中放入一个时间. 此时间是为了全程统一
*/
public Date getCurrentDate() {
try {
Object v = this.get(CURRENT_DATE);
if (v == null) {
Date date = new Date();
super.put(CURRENT_DATE, date);
return date;
}
return (Date) v;
} catch (Exception e) {
log.info("cMap数据转换Date 错误");
}
return null;
}
/*换PUT方法可以连缀*/
public CMap putValue(String key, Object value) {
super.put(key, value);
return this;
}
/*换PUT方法可以连缀*/
public CMap putDateValue(String key, Object value) {
if (value instanceof java.sql.Timestamp) {
java.sql.Timestamp o = (java.sql.Timestamp) value;
String dateStr = com.techhero.common.utils.DateUtils.formatDate(new Date(o.getTime()));
super.put(key, dateStr);
return this;
} else if (value instanceof Date) {
Date date = (Date) value;
String dateStr = com.techhero.common.utils.DateUtils.formatDate(date);
super.put(key, dateStr);
return this;
}
log.info("日期数据保存失败");
return this;
}
/*换PUT方法可以连缀*/
public CMap putDateTimeValue(String key, Object value) {
if (value instanceof java.sql.Timestamp) {
java.sql.Timestamp o = (java.sql.Timestamp) value;
String dateStr = com.techhero.common.utils.DateUtils.formatDateTime(new Date(o.getTime()));
super.put(key, dateStr);
return this;
} else if (value instanceof Date) {
Date date = (Date) value;
String dateStr = com.techhero.common.utils.DateUtils.formatDateTime(date);
super.put(key, dateStr);
return this;
}
log.info("日期数据保存失败");
return this;
}
/**
* 主要针对数据MYBATIS返回数据,对类型java.sql.Timestamp进行转换为String类型
* 如果你想要保存一个原生类型,你可以选择CMap.putValue(String key, Object value);
* 作者: 吴 波
* 时间: 2020-03-06 13:43
* 笔名: 那年秋天的茄子^^
*/
@Override
public Object put(String key, Object value) {
if (value instanceof java.sql.Timestamp) {
java.sql.Timestamp o = (java.sql.Timestamp) value;
String dateStr = DateUtils.formatDateTime(new Date(o.getTime()));
super.put(key, dateStr);
return dateStr;
}
return super.put(key, value);
}
/*通过一个Map构建为当前类*/
public static CMap builder(Map<? extends String, ?> map) {
CMap param = new CMap();
param.putAll(map);
return param;
}
/*通过一个Bean对象转换为Param对象*/
public static CMap builderParamByBean(Object bean) {
if (bean == null) return null;
CMap param = new CMap();
try {
BeanInfo beanInfo = Introspector.getBeanInfo(bean.getClass());
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for (PropertyDescriptor property : propertyDescriptors) {
String key = property.getName();
/*过滤class属性*/
if (!key.equals("class")) {
/*得到property对应的getter方法*/
Method getter = property.getReadMethod();
Object value = getter.invoke(bean);
if(value!=null&&value instanceof Date){
Field declaredField = bean.getClass().getDeclaredField(key);
log.debug("[当前为日期类型-反射获取是否有ToDate注解]");
if(declaredField.isAnnotationPresent(ToDate.class)){
ToDate annotation = declaredField.getAnnotation(ToDate.class);
String format = annotation.value();
value= DateUtils.formatDate((Date) value, format);
}
}
param.put(key, value);
}
}
} catch (Exception e) {
log.error("转换数据错误!",e);
}
return param;
}
/**
* 如果你在其中放入的是一个实例自定义对象,
* 则你可以通过这个方法获取那个bean对象
*/
public <T> T getBean(String key, Class<T> t) {
Object v = this.get(key);
if (v != null) {
if (t.isAssignableFrom(v.getClass())) {
try {
@SuppressWarnings("unchecked")
T newInstance = (T) v;
return newInstance;
} catch (Exception e) {
e.printStackTrace();
}
}
}
return null;
}
/**
* 获取某前缀开始的数据集
*/
public CMap getComMapByPre(String pre) {
CMap param = new CMap();
Set<String> keySet = this.keySet();
for (String key : keySet) {
if (key.startsWith(pre)) {
Object v = super.get(key);
String newKey = key.substring(pre.length());
param.put(newKey, v);
}
}
return param;
}
/*获取布尔值数据默认为False*/
public Boolean getBooleanValue(String key) {
Object v = this.get(key);
if (v == null)
return false;
if (v instanceof Boolean) {
return (Boolean) v;
}
return false;
}
/*获取BigDecimal数据*/
public BigDecimal getBigDecimalValue(String key) {
Double doubleValue = this.getDoubleValue(key);
if (doubleValue != null) return new BigDecimal(doubleValue);
return null;
}
/**
* 获取对象
*/
public <T> T getMapBean(Class<T> t) {
return transMapToBean(this, t);
}
/**
* 利用org.apache.commons.beanutils
*/
public static <T> T transMapToBean(Map<?, ?> map, Class<T> clazz) {
T object = null;
try {
object = clazz.newInstance();
if (map == null || map.size() < 1) {
return object;
}
} catch (Exception e) {
e.printStackTrace();
}
try {
BeanInfo beanInfo = Introspector.getBeanInfo(clazz); // 获取类属性
/*给 JavaBean 对象的属性赋值*/
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
StringBuffer sb = new StringBuffer(clazz.getName());
sb.append("\n");
for (PropertyDescriptor descriptor : propertyDescriptors) {
String propertyName = descriptor.getName();
sb.append(propertyName);
sb.append("=");
if (map.containsKey(propertyName)) {
// 下面一句可以 try 起来，这样当一个属性赋值失败的时候就不会影响其他属性赋值。
Object value = map.get(propertyName);
Class<?> propertyType = descriptor.getPropertyType();
Object[] args = new Object[1];
try {
if (value != null) {
if (propertyType.equals(Integer.class)) {
value = Integer.valueOf(value.toString());
} else if (propertyType.equals(Short.class)) {
value = Short.valueOf(value.toString());
} else if (propertyType.equals(Long.class)) {
value = Long.valueOf(value.toString());
} else if (propertyType.equals(BigDecimal.class)) {
value = new BigDecimal(value.toString());
} else if (propertyType.equals(Double.class)) {
value = Double.valueOf(value.toString());
} else if (propertyType.equals(Float.class)) {
value = Float.valueOf(value.toString());
} else if (propertyType.equals(Date.class)) {
if (value.getClass().equals(String.class)) {
try {
value = DateUtils.parseDate(value.toString());
} catch (Exception e) {
log.info("转换失败",e);
}
}
}
}
args[0] = value;
sb.append(value);
sb.append("\n");
Method writeMethod = descriptor.getWriteMethod();
if (writeMethod == null) {
continue;
}
writeMethod.invoke(object, args);
} catch (Exception e) {
log.info("[CMap 转换异常]\n{}",sb, e);
}
}
}
return object;
} catch (Exception e) {
log.info("转换失败",e);
}
return object;
}
/*构造函数*/
public CMap(int initialCapacity, float loadFactor) {
super(initialCapacity, loadFactor);
}
public CMap(int initialCapacity) {
super(initialCapacity, 1);
}
public CMap() {
}
public CMap(Map<? extends String, ?> m) {
super(m);
}
@Override
public String toString() {
return JSONObject.toJSONString(this);
}
}使用方式如下:
public static void main(String[] args) {
/*岗位*/
List<OptionNode> gwOptions = new ArrayList<>();
gwOptions.add(OptionNode.builder().key("1").value("考区负责人").build());
gwOptions.add(OptionNode.builder().key("2").value("考点负责人").build());
gwOptions.add(OptionNode.builder().key("3").value("考场负责人").build());
gwOptions.add(OptionNode.builder().key("4").value("钥匙保管员").build());
gwOptions.add(OptionNode.builder().key("5").value("武警").build());
/*没有编码列*/
SelectOption gw = SelectOption.builder("GW", gwOptions);
/* 性别类型*/
List<OptionNode> xbOptions = new ArrayList<>();
xbOptions.add(OptionNode.builder().key("A").value("男").build());
xbOptions.add(OptionNode.builder().key("B").value("女").build());
xbOptions.add(OptionNode.builder().key("C").value("未知").build());
/*有编码列-且隐藏编码列*/
SelectOption xb = SelectOption.builder("XB", "XBBM", xbOptions, true);
/*证件类型*/
List<OptionNode> typeOptions = new ArrayList<>();
typeOptions.add(OptionNode.builder().key("A1").value("身份证").build());
typeOptions.add(OptionNode.builder().key("B2").value("军官证").build());
typeOptions.add(OptionNode.builder().key("C3").value("驾驶证").build());
/*有编码列-且不隐藏编码列*/
SelectOption type = SelectOption.builder("ZJLX", "ZJLXBM", typeOptions, false);
List<SelectOption> selectOptions = new ArrayList<>();
selectOptions.add(xb);
selectOptions.add(type);
selectOptions.add(gw);
ExcelTemplateModel build = ExcelTemplateModel.builder()
/*文件路劲*/
.filePath("/Users/cnnoter/Data/TempData/CsTempldate.xlsx")
/*默认初始化行数*/
.templateInitRowSize(5000)
/*头部信息渲染*/
.handData(new String[]{"湖北省考试院", "KS1X03"})
/*下拉选项*/
.selectOptions(selectOptions)
/*其实行未知*/
.selectOptionRowNum(3)
.build();
downLoadExcelTemplateToLocal(build);下边是service中直接调用的一个函数(参考)
/*考点工作人员模板下载*/
private void examPointWorkPersonTemplateDownLoad(String code,String examPlanCode, HttpServletResponse response) {
/*考试管理机构*/
ZhJgKsgljgxxb examMngOrg = zhJgKsgljgxxbMapper.selectOne(ZhJgKsgljgxxb.builder().ksgljgid(code).build());
/*头部替换[考试管理机构名称/管理机构标识]*/
String[] handData = new String[]{examMngOrg.getKsgljgmc(), examMngOrg.getKsgljgid()};
/*下拉选数据*/
List<SelectOption> selectOptions = new ArrayList<>();
/*性别*/
List<Dict> xbDicts = dictMapper.select(Dict.builder().sjlx("zd_xbdm").build());
List<OptionNode> xbOptionNodes = xbDicts.stream().map(dict -> OptionNode.builder().key(dict.getDm()).value(dict.getDmmc()).build()).collect(Collectors.toList());
selectOptions.add(SelectOption.builder("xb", "xbm", xbOptionNodes));
/*获取岗位*/
List<Job> jobs = jobMapper.select(Job.builder().gwflDm(JobTypeEnum.KS_JOB.getOrgCode()).build());
List<OptionNode> jobOptionNodes = jobs.stream().map(job -> OptionNode.builder().key(job.getGwDm()).value(job.getGwmc()).build()).collect(Collectors.toList());
selectOptions.add(SelectOption.builder("gwzz", "gwzzm", jobOptionNodes));
/*是否在编*/
List<OptionNode> isOpenList = new ArrayList<>();
isOpenList.add(OptionNode.builder().key("0").value("否").build());
isOpenList.add(OptionNode.builder().key("1").value("是").build());
selectOptions.add(SelectOption.builder("sfzb", isOpenList));
/*学历*/
List<Dict> xlDicts = dictMapper.select(Dict.builder().sjlx("xl").build());
List<OptionNode> xlOptionNodes = xlDicts.stream().map(dict -> OptionNode.builder().key(dict.getDm()).value(dict.getDmmc()).build()).collect(Collectors.toList());
selectOptions.add(SelectOption.builder("xl", "xlbm", xlOptionNodes));
/*职称*/
List<Dict> zcDicts = dictMapper.select(Dict.builder().sjlx("zc").build());
List<OptionNode> zcOptionNodes = zcDicts.stream().map(dict -> OptionNode.builder().key(dict.getDm()).value(dict.getDmmc()).build()).collect(Collectors.toList());
selectOptions.add(SelectOption.builder("zc", "zcbm", zcOptionNodes));
/*考点*/
List<DcKdxxb> examPoints = dcKdxxbMapper.select(DcKdxxb.builder().kdxzqhm(code).ksjhbm(examPlanCode).build());
List<OptionNode> examPointOptionNodes = examPoints.stream().filter(dcKdxxb -> StringUtil.isNotBlank(dcKdxxb.getBzhkdid())&&StringUtil.isNotBlank(dcKdxxb.getKdmc())).map(dcKdxxb -> OptionNode.builder().key(dcKdxxb.getId()).value(dcKdxxb.getKdmc()).build()).collect(Collectors.toList());
selectOptions.add(SelectOption.builder("bzhkdmc", "bzhkdid", examPointOptionNodes));
/*培训考核情况*/
List<Dict> pxkhqkDicts = dictMapper.select(Dict.builder().sjlx("pxkhqk").build());
List<OptionNode> pxkhqkOptionNodes = pxkhqkDicts.stream().map(dict -> OptionNode.builder().key(dict.getDm()).value(dict.getDmmc()).build()).collect(Collectors.toList());
selectOptions.add(SelectOption.builder("pxkhqk", "pxkhqkbm", pxkhqkOptionNodes));
String supperData = handData[1] + ",1,身份证," + code;
ExcelUtils.downLoadExcelTemplate(response,
ExcelTemplateModel.builder()
.handData(handData)
.supperData(supperData)
.selectOptionRowNum(4)
.templateInitRowSize(5000)
.selectOptions(selectOptions)
.filePath(templatePath.getExamPointWorkPersonPath())
.excelName(examMngOrg.getKsgljgmc() + "考点工作人员.xlsx")
.build());
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
