Go directly to the code, from beginning to end.
/**
* The first step is to create the information required by the template
* @return
*/
public Map<String, String> getMetaBean() {
Map<String, String> metaMap = new LinkedHashMap<>();
metaMap.put("name", "name");
metaMap.put("ageData", "age date");
metaMap.put("contextPath", "path");
metaMap.put("age", "age");
metaMap.put("gender", "gender");
metaMap.put("datastate", "type");
metaMap.put("ifNotKey", "Is it old age");
return metaMap;
}
/**
* The second step calls excel to start downloading
*/
@GetMapping("excel/file/export")
public void export(HttpServletRequest request, HttpServletResponse response) throws Exception{
//List to array
// String[] allowedValues = domainName. stream(). toArray(String[]::new);
this.exportTemplates(request, response, "veryGood","XXXXX", getMetaBean());
}
public void exportTemplates(HttpServletRequest request, HttpServletResponse response,
String tableName, String SheetName, Map<String, String> tablefields) throws Exception {
List<String> excelHeader = new ArrayList<>();
//Save the dictionary information
for (String key : tablefields. keySet()) {
String name = tablefields. get(key);
excelHeader. add(name);
}
XSSFWorkbook wb = new XSSFWorkbook();
// Save the rules corresponding to each field
XSSFSheet sheet = wb.createSheet(SheetName);
//Fill in the template information
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFRow row = sheet. createRow((int) 0);
XSSFCellStyle style = wb.createCellStyle();
// create drawing object
XSSFDrawing p = sheet.createDrawingPatriarch();
CellStyle invisibleStyle = wb.createCellStyle();
Font font = wb. createFont();
font.setColor(IndexedColors.WHITE.getIndex()); // Set the font color to be the same as the background color (eg white)
invisibleStyle. setFont(font);
for (int i = 0; i < excelHeader. size(); i ++ ) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader.get(i));
cell.setCellStyle(style);
String name = excelHeader. get(i);
sheet.setColumnWidth(i, 20 * 200);
if ("path".equals(name)) {
//The data of the path is stored in the V column in the table and hidden
String[] contextPath = {"A/B/C","C/O/D","DD/AA/JJ"};
CellRangeAddressList addressList = new CellRangeAddressList(1, 60000, i, i);
//setting is text format
DataValidationConstraint validationConstraint = dvHelper.createExplicitListConstraint(contextPath);
// DataValidationConstraint validationConstraint = dvHelper.createFormulaListConstraint(infoSheetName + "!$" + cellNum + "$1:$" + cellNum + "$" + contextPath.length);
DataValidation validation = dvHelper.createValidation(validationConstraint, addressList);
// Set the error message displayed by data validation
String join = String. join(",", contextPath);
join = join. length() > 245 ? join. substring(0, 245) : join;
validation.createErrorBox("error", "can only fill in (" + join + "..) equal values");
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
sheet. addValidationData(validation);
// hide the selected column
} else if ("gender".equals(name)) {
String[] allowedValues = {"Male", "Female"};
CellRangeAddressList addressList = new CellRangeAddressList(1, 60000, i, i);
DataValidationConstraint validationConstraint = dvHelper.createExplicitListConstraint(allowedValues);
DataValidation validation = dvHelper.createValidation(validationConstraint, addressList);
// Set the error message displayed by data validation
validation.createErrorBox("error", "can only fill in" + String.join(",", allowedValues));
validation.setShowErrorBox(true);
sheet. addValidationData(validation);
} else if ("Is it old".equals(name)) {
String[] allowedValues = {"Yes", "No"};
CellRangeAddressList addressList = new CellRangeAddressList(1, 60000, i, i);
DataValidationConstraint validationConstraint = dvHelper.createExplicitListConstraint(allowedValues);
DataValidation validation = dvHelper.createValidation(validationConstraint, addressList);
// Set the error message displayed by data validation
validation.createErrorBox("error", "can only fill in" + String.join(",", allowedValues));
validation.setShowErrorBox(true);
sheet. addValidationData(validation);
}else if ("Age Date".equals(name)){
//Set the valid range of validation
CellRangeAddressList addressList = new CellRangeAddressList(1, 60000, i, i);
//Set the verification method
DataValidationConstraint constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,"1900/01/01","2099/12/31","yyyy/mm/dd");
//Create validation object
DataValidation dataValidation = dvHelper.createValidation(constraint, addressList);
// error message
dataValidation.createErrorBox("Prompt","Please enter the date in [yyyy/mm/dd] format, the period is: 1900/01/01 to 2099/12/31");
dataValidation.setShowErrorBox(true);
// Validation and workbook binding
sheet. addValidationData(dataValidation);
}else if ("age".equals(name)){
//Set the valid range of validation
CellRangeAddressList addressList1 = new CellRangeAddressList(1, 60000, i, i);
//Set the verification method
DataValidationConstraint constraint1 =dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(0), String.valueOf(200));
//Create validation object
DataValidation validation = dvHelper.createValidation(constraint1, addressList1);
// error message
validation.createErrorBox("Prompt", "Please enter an integer between [0-200]");
/ / Set whether to display the error window
validation.setShowErrorBox(true);
// Validation and workbook binding
sheet. addValidationData(validation);
}
}
String filename = tableName + ".xlsx";
String header = request. getHeader("User-Agent");
if (header. contains("Firefox")) {
//Description is Firefox browser, use Base64Encoder class to encode
BASE64Encoder base = new BASE64Encoder();
filename = "=?utf-8?B?" + base.encode(filename.getBytes("utf-8")) + "?=";
} else {
//For other browsers, use the URLEncoder class for encoding
filename = URLEncoder. encode(filename, "utf-8");
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("UTF-8");
// response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(name, "utf-8"));
OutputStream ouputStream = null;
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
try {
ouputStream = response. getOutputStream();
wb.write(ouputStream);
ouputStream. flush();
ouputStream. close();
} finally {
wb. close();
}
}
By the way, I found a very simple one, I hope everyone can make progress together