Generate excel template (drop-down selection, multi-level linkage)

Write excel Templates

package com;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.security.SecureRandom;
import java.util.*;

public class DownExcelUtil {
    public static void main(String[] args) {
        getFile();
    }

    public static void getFile(){
        FileOutputStream outputStream = null;
        String path = "";
        try{
            // Create a template .xlsx Format 
            XSSFWorkbook workbook = new XSSFWorkbook();
            // Create a sheet surface , Multiple can be set sheet
            Sheet sheet = workbook.createSheet(" Performance appraisal results ");
            sheet.setDefaultColumnWidth(15);
            // Get the template header 
            HashMap<String,String> map = findTemplate();
            // Generate template header 
            createTemplate(workbook, sheet, map);
            List<Integer> countList = new ArrayList<Integer>();
            countList.add(4);
            countList.add(8);
            List<String> assessList = new ArrayList<String>();
            assessList.add("A");
            assessList.add("B");
            List<String> provinceList = new ArrayList<String>();
            provinceList.add(" Hubei province ");
            provinceList.add(" Guangdong province, ");
            List<String> cityList = new ArrayList<String>();
            cityList.add(" Huanggang City ");
            cityList.add(" Wuhan City ");
            cityList.add(" Huangshi City ");
            cityList.add(" shenzhen ");
            cityList.add(" guangzhou ");
            cityList.add(" huizhou ");
            cityList.add(" Dongguan city, ");

            // Generate file name 
            String excelName = "performance.result";
            String displayName = String.format("%s_Template.xlsx",excelName);
            String fileName = displayName;
            path = "D:\\appfile\\temp\\downloadTempFile" + File.separator + fileName;
            // Create instructions Sheet page 
            createExplainSheet(workbook);
            // Create a province 、 City 、 Assess performance sheet page 
            createOtherSheet(workbook," province ",provinceList);
            createOtherSheet(workbook," City ",cityList);
            createOtherSheet(workbook," Assess performance ",assessList);

            // Set month drop-down selection 
            String[] monthArr = {"202007"};
            createSelect(sheet, 0, monthArr);
            // Set Province 、 City 、 Performance appraisal drop-down selection 
            createSelectSheetGrade(workbook, sheet, provinceList, countList);
            createSelectSheet(workbook, sheet, 3," Assess performance ",assessList.size()+1);

            // take excel write in 
            outputStream = FileUtils.openOutputStream(new File(path));
            workbook.write(outputStream);
            outputStream.flush();
            workbook.close();
        }catch (IOException e){
            System.out.println(e);
        }finally {
            try {
                outputStream.close();
            }catch (IOException e){
                System.out.println(e);
            }
        }
        System.out.println("pathNewUrl:"+ path);
    }

    // Generate template header 
    public static void createTemplate(XSSFWorkbook workbook, Sheet sheet, HashMap<String,String> map){
        String[] tableHeaderTemp = map.get("performanceResultsTemplateTemp").toString().split(",");
        String[] tableHeader = map.get("performanceResultsTemplate").toString().split(",");
        XSSFRow firstRow = (XSSFRow)sheet.createRow((short)0);
        firstRow.setHeight((short)600);
        for(int i=0;i<tableHeader.length;i++){
            XSSFCell cell = firstRow.createCell((short)i);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
            if(i<5){
                XSSFRichTextString richString = new XSSFRichTextString();
                richString.append("*",createFont(workbook,HSSFColor.HSSFColorPredefined.RED.getIndex()));
                richString.append(tableHeaderTemp[i],createFont(workbook,HSSFColor.HSSFColorPredefined.BLACK.getIndex()));
                cell.setCellValue(richString);
            }else{
                cell.setCellValue(tableHeaderTemp[i]);
            }
        }
        XSSFRichTextString richStringLast = new XSSFRichTextString();
        richStringLast.append("*",createFont(workbook,HSSFColor.HSSFColorPredefined.RED.getIndex()));
        richStringLast.append(" Total assessment score ",createFont(workbook,HSSFColor.HSSFColorPredefined.BLACK.getIndex()));
        XSSFCell cellFirstLast = firstRow.createCell((short)tableHeader.length);
        cellFirstLast.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
        cellFirstLast.setCellValue(richStringLast);
        for(int i=tableHeader.length;i<tableHeaderTemp.length;i++){
            XSSFCell cell = firstRow.createCell((short)i+1);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
            cell.setCellValue(tableHeaderTemp[i]);
        }
        firstRow.setZeroHeight(true);
        XSSFRow secondRow = (XSSFRow)sheet.createRow((short)1);
        secondRow.setHeight((short)600);
        for(int i=0;i<tableHeader.length;i++){
            XSSFCell cell = secondRow.createCell((short)i);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
            if(i<5){
                XSSFRichTextString richString = new XSSFRichTextString();
                richString.append("*",createFont(workbook,HSSFColor.HSSFColorPredefined.RED.getIndex()));
                richString.append(tableHeader[i],createFont(workbook,HSSFColor.HSSFColorPredefined.BLACK.getIndex()));
                cell.setCellValue(richString);
            }else{
                cell.setCellValue(tableHeader[i]);
            }
        }

        // Generate the last line * Total assessment score 
        XSSFCell cellSecondLast = secondRow.createCell((short)tableHeader.length);
        cellSecondLast.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
        cellSecondLast.setCellValue(richStringLast);
    }

    // Get the template header 
    public static HashMap<String,String> findTemplate(){
        HashMap<String,String> map = new HashMap<String,String>();
        StringBuilder performanceResultsTemplate = new StringBuilder();
        StringBuilder performanceResultsTemplateTemp = new StringBuilder();
        performanceResultsTemplate.append(" month , province , City , Assess performance ");
        performanceResultsTemplateTemp.append(" month , province , City , Assess performance , Dynamic column 1, Dynamic column 2, Dynamic column 3, Dynamic column 4, Dynamic column 5, Dynamic column 6," +
                " Dynamic column 7, Dynamic column 8, Dynamic column 9, Dynamic column 10, Dynamic column 11, Dynamic column 12, Dynamic column 13, Dynamic column 14, Dynamic column 15, Dynamic column 16, Dynamic column 17, Dynamic column 18," +
                " Dynamic column 19, Dynamic column 20, Dynamic column 21, Dynamic column 22, Dynamic column 23, Dynamic column 24, Dynamic column 25, Dynamic column 26, Dynamic column 27, Dynamic column 28, Dynamic column 29, Dynamic column 30");
        map.put("performanceResultsTemplate",performanceResultsTemplate.toString());
        map.put("performanceResultsTemplateTemp",performanceResultsTemplateTemp.toString());
        return map;
    }

    /**
     *  Set cell style 
     * @param workbook
     * @param color  Cell background color 
     * @param n 0 default font ,1 Bold font 
     * @return
     */
    public static CellStyle createStyle(XSSFWorkbook workbook, short color, int n){
        // Header style 
        CellStyle defaultStyle = workbook.createCellStyle();
        defaultStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        defaultStyle.setBorderRight(BorderStyle.THIN);// Right border 
        defaultStyle.setBorderTop(BorderStyle.THIN);// Top border 
        defaultStyle.setFillForegroundColor(color);// Set background color 
        defaultStyle.setBorderBottom(BorderStyle.THIN);// Bottom border 
        defaultStyle.setBorderLeft(BorderStyle.THIN);// The left border 
        defaultStyle.setAlignment(HorizontalAlignment.CENTER);// Horizontal center 
        defaultStyle.setVerticalAlignment(VerticalAlignment.CENTER);// Vertical center 
        // Generate a font 
        XSSFFont font = workbook.createFont();
        font.setFontName(" Song style ");
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());// The font color 
        font.setFontHeightInPoints((short)12);// font size 
        if(n==1){
            font.setBold(true);
        }
        // Apply the font to the current style 
        defaultStyle.setFont(font);
        return defaultStyle;
    }

    /**
     *  Set cell style 
     * @param workbook
     * @param color  The font color 
     * @return
     */
    public static XSSFFont createFont(XSSFWorkbook workbook, short color){
        XSSFFont defaultFont = workbook.createFont();
        defaultFont.setFontName(" Song style ");
        defaultFont.setColor(color);
        defaultFont.setFontHeightInPoints((short)12);
        defaultFont.setBold(true);
        return defaultFont;
    }

    /**
     *  Create a drop-down selection Sheet
     * @param workbook
     * @param sheetName
     * @param list  Drop down list 
     */
    public static void createOtherSheet(XSSFWorkbook workbook, String sheetName, List<String> list){
        Sheet sheet = workbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth(32);
        // Create title 
        XSSFRow rowTitle = (XSSFRow)sheet.createRow(0);
        XSSFCell cellTitle = rowTitle.createCell(0);
        cellTitle.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
        cellTitle.setCellValue(sheetName);
        // Create a new line 
        for(int i=0;i<list.size();i++){
            //i+1: The title of the first act 
            XSSFRow row = (XSSFRow)sheet.createRow(i+1);
            XSSFCell cell = row.createCell(0);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 0));
            cell.setCellValue(StringUtils.isBlank(list.get(i))?"":list.get(i).toString());
        }
    }

    /**
     *  Set the drop-down value （ Write the value directly into , Only a few values can be used in this way , Otherwise, an error will be reported ）
     * @param sheet
     * @param col  The first few columns 
     * @param arr  Drop down to select an array 
     */
    public static void createSelect(Sheet sheet, int col, String[] arr){
        CellRangeAddressList regions = new CellRangeAddressList(2,1000000,col,col);
        DataValidationHelper help = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = help.createExplicitListConstraint(arr);
        DataValidation validation = help.createValidation(constraint,regions);
        sheet.addValidationData(validation);
    }

    /**
     *  Set the drop-down value （ With sheet Page as a drop-down value ）
     * @param workbook
     * @param sheet
     * @param col  The first few columns 
     * @param sheetName
     * @param count  Number of drop-down lists +1
     */
    public static void createSelectSheet(XSSFWorkbook workbook, Sheet sheet, int col, String sheetName, int count){
        XSSFName nameCell = workbook.createName();
        nameCell.setNameName(sheetName);
        nameCell.setRefersToFormula(sheetName+"!$A$2:$A$"+count);
        CellRangeAddressList regions = new CellRangeAddressList(2,1000000,col,col);
        DataValidationHelper help = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = help.createFormulaListConstraint(sheetName);
        DataValidation validation = help.createValidation(constraint,regions);
        sheet.addValidationData(validation);
    }

    // Set multi-level drop-down values （ With sheet Page as a drop-down value , The name manager cannot have special characters such as spaces , Otherwise, an error will be reported ）
    public static void createSelectSheetGrade(XSSFWorkbook workbook, Sheet sheet, List<String> list, List<Integer> countList){
        XSSFName nameCell = workbook.createName();
        nameCell.setNameName(" province ");
        nameCell.setRefersToFormula(" province "+"!$A$2:$A$"+list.size()+1);
        CellRangeAddressList regionsSecond = new CellRangeAddressList(2,1000000,1,1);
        DataValidationHelper helpSecond = sheet.getDataValidationHelper();
        DataValidationConstraint constraintSecond = helpSecond.createFormulaListConstraint(" province ");
        DataValidation validationSecond = helpSecond.createValidation(constraintSecond,regionsSecond);
        sheet.addValidationData(validationSecond);

        // Cycle set city 
        for(int i=0;i<list.size();i++){
            nameCell = workbook.createName();
            try{
                nameCell.setNameName(list.get(i).replaceAll(" ",""));
            }catch (Exception e){
                nameCell.setNameName(" City "+new SecureRandom().nextDouble());
            }
            if(i==0){
                nameCell.setRefersToFormula(" City "+"!$A$2:$A$"+countList.get(i));
            }else{
                if(countList.get(i-1)!=countList.get(i)){
                    nameCell.setRefersToFormula(" City "+"!$A$"+(countList.get(i-1)+1)+":$A$"+countList.get(i));
                }else{
                    nameCell.setRefersToFormula(" City "+"!$A$"+(countList.get(list.size()-1)+1)+":$A$"+countList.get(list.size()-1)+1);
                }
            }
        }
        CellRangeAddressList regions = new CellRangeAddressList(2,1000000,2,2);
        DataValidationHelper help = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = help.createFormulaListConstraint("INDIRECT(SUBSTITUTE($B3,\"\",\"\"))");//=INDIRECT(SUBSTITUTE($A1,"",""))
        DataValidation validation = help.createValidation(constraint,regions);
        sheet.addValidationData(validation);
    }

    // Create instructions sheet page 
    public static void createExplainSheet(XSSFWorkbook workbook){
        String[] cells = {" month "," province "," City "," Assess performance ","XX indicators ","XX score "," Total assessment score "};
        String[] cellsFirst = {" Drop down to select "," Drop down to select "," Drop down to select "," Drop down to select "," Text or percentage "," The number "," The number "};
        String[] cellsSecond = {"202009"," Hubei province "," Huanggang City ","A","30%","1","2.02"};
        Sheet sheet = workbook.createSheet(" explain ");
        sheet.setDefaultColumnWidth(15);
        // Create title 
        XSSFRow rowTitle = (XSSFRow)sheet.createRow(0);
        XSSFCell cellExplain = rowTitle.createCell(1);
        cellExplain.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 1));
        cellExplain.setCellValue(" explain ");
        XSSFCell cellExample = rowTitle.createCell(2);
        cellExample.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 1));
        cellExample.setCellValue(" give an example ");
        // Fill in the field description 
        for(int i=0;i<cells.length;i++){
            XSSFRow row = (XSSFRow)sheet.createRow(i+1);
            XSSFCell cell = row.createCell(0);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
            cell.setCellValue(cells[i]);

            XSSFCell cellFirst = row.createCell(1);
            cellFirst.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 0));
            cellFirst.setCellValue(cellsFirst[i]);

            XSSFCell cellSecond = row.createCell(2);
            cellSecond.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 0));
            cellSecond.setCellValue(cellsSecond[i]);
        }
    }
}

pom File reference

<dependencies>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.4</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- Support xlsx Read -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.8.0</version>
        </dependency>
    </dependencies>

Console printing

pathNewUrl:D:\appfile\temp\downloadTempFile\performance.result_Template.xlsx

excel file