poi_0">poi下载模板含下拉框

第一种方法

新建QuestionsImport类

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;

public class QuestionsImport {

    /**
     * @param
     * @param filePath Excel文件路径
     * @param
     * @param headers  Excel列标题(数组)
     * @param
     * @param downData 下拉框数据(数组)
     * @param
     * @param downRows 下拉列的序号(数组,序号从0开始)
     * @return void
     * @throws String filePath, String sheetTitle, List<String> headers, Map<String,String[]> map, String[] fatherOption
     * @Title: testFunction
     * @Description: 生成Excel导入模板
     */
    public static void createExcelTemplate(String filePath, String sheetTitle, List<String> headers, List<String[]> downData, List<String> downRows) throws IOException {
        // 创建一个excel
        @SuppressWarnings("resource")
        XSSFWorkbook book = new XSSFWorkbook();
        CellStyle style = book.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HorizontalAlignment.CENTER);
        // 创建需要用户填写的sheet
        XSSFSheet sheetPro = book.createSheet(sheetTitle);
        Row row0 = sheetPro.createRow(0);
        Cell cell1 = row0.createCell((short) 0);
        //row0.setHeight((short) 400);
        for (int i = 0; i < headers.size(); i++) {
            cell1.setCellValue(headers.get(i));
            cell1.setCellStyle(style);
            //设置单元格宽度
            sheetPro.setColumnWidth(i, 24 * 256);
            //设置居中样式
            sheetPro.setDefaultColumnStyle(i, style);
            cell1 = row0.createCell((short) i + 1);
        }

        //因此也不能在现实页之前创建，否则无法隐藏。
        XSSFSheet hideSheet = book.createSheet("hideSheet");
        //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        book.setSheetHidden(book.getSheetIndex(hideSheet), true);
        GetNewExcelTemplate(filePath, downData, downRows, sheetPro, hideSheet, book);
    }

    /**
     * @param filePath 生成的模板文件路径
     * @param downData 下拉框数据集合
     * @param downRows 设置列的序号集合(从0开始)
     */
    public static void GetNewExcelTemplate(String filePath, List<String[]> downData, List<String> downRows, XSSFSheet sheetPro, XSSFSheet hideSheet, XSSFWorkbook workbook) throws IOException {
        //修改excle表的数据
        // 设置下拉框数据
        if (downData.size() > 0 && downRows.size() > 0) {
            String[] arr = {"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"};
            int index = 0;
            //数据行
            Row row = null;
            for (int r = 0; r < downRows.size(); r++) {
                // 获取下拉对象
                String[] dlData = downData.get(r);
                int rownum = Integer.parseInt(downRows.get(r));
                // 255以内的下拉
                if (dlData.length < 5) {
                    // 255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
                    // 超过255个报错
                    sheetPro.addValidationData(setDataValidation(sheetPro, dlData, 1, 5000, rownum, rownum));
                } else {
                    // 255以上的下拉，即下拉列表元素很多的情况
                    // 1、设置有效性
                    // String strFormula = "Sheet2!$A$1:$A$5000" ;
                    // //Sheet2第A1到A5000作为下拉列表来源数据
                    // Sheet2第A1到A5000作为下拉列表来源数据
                    String strFormula = "hideSheet!$" + arr[index] + "$1:$" + arr[index] + "$" + (dlData.length + 1);
                    // 设置每列的列宽
                    hideSheet.setColumnWidth(r, 4000);
                    // 设置数据有效性加载在哪个单元格上,参数分别是：从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
                    // 下拉列表元素很多的情况
                    sheetPro.addValidationData(SetDataValidation(workbook, hideSheet, ("data" + r), strFormula, 1, 5000, rownum, rownum));
                    // 2、生成sheet2内容
                    for (int j = 0; j < dlData.length; j++) {
                        // 第1个下拉选项，直接创建行、列
                        if (index == 0) {
                            // 创建数据行
                            row = hideSheet.createRow(j);
                            // 设置每列的列宽
                            hideSheet.setColumnWidth(j, 4000);
                            // 设置对应单元格的值
                            row.createCell(0).setCellValue(dlData[j]);

                        } else {
                            // 非第1个下拉选项
                            int rowCount = hideSheet.getLastRowNum();
                            // 前面创建过的行，直接获取行，创建列
                            if (j <= rowCount) {
                                // 获取行，创建列
                                // 设置对应单元格的值
                                hideSheet.getRow(j).createCell(index).setCellValue(dlData[j]);
                            } else {
                                // 未创建过的行，直接创建行、创建列
                                // 设置每列的列宽
                                hideSheet.setColumnWidth(j, 4000);
                                // 创建行、创建列
                                // 设置对应单元格的值
                                hideSheet.createRow(j).createCell(index).setCellValue(dlData[j]);
                            }
                        }
                    }
                    index++;
                }
            }
        }
        //将修改后的文件写出到D:\\excel目录下
        // 写文件
        File f = new File(filePath);
        // 不存在则新增
        if (!f.getParentFile().exists()) {
            f.getParentFile().mkdirs();
        }
        if (!f.exists()) {
            f.createNewFile();
        }
        FileOutputStream out = new FileOutputStream(f);
        out.flush();
        workbook.write(out);
        //关闭流
        out.close();
    }

    /**
     * @param workbook   当前工作簿
     * @param sheet      存放下拉菜单数据的sheet
     * @param dataName   数据有效性名称
     * @param strFormula 下拉菜单取数据范围
     * @param firstRow
     * @param endRow
     * @param firstCol
     * @param endCol
     * @return
     * @Description: xlsx格式 255以上下拉菜单
     */
    private static XSSFDataValidation SetDataValidation(XSSFWorkbook workbook, XSSFSheet sheet, String dataName, String strFormula, int firstRow, int endRow, int firstCol, int endCol) {
        XSSFName name = workbook.createName();
        name.setNameName(dataName);
        name.setRefersToFormula(strFormula);
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createFormulaListConstraint(dataName);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        validation.setSuppressDropDownArrow(true);
        return validation;
    }
    /**
     * @Title: setDataValidation @Description:
     * 下拉列表元素不多的情况(255以内的下拉) @param @param sheet @param @param
     * textList @param @param firstRow @param @param endRow @param @param
     * firstCol @param @param endCol @param @return @return
     * DataValidation @throws
     */
    private static XSSFDataValidation setDataValidation(XSSFSheet sheet, String[] textList, int firstRow, int endRow,
                                                        int firstCol, int endCol) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textList);
        CellRangeAddressList addressList = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        return validation;
    }


    /**
     * @Title: downFile
     * @Description:
     * @param @param url文件url
     * @param @param fileName  文件名
     * @param @param response
     * @return void
     * @throws
     */
    public static void downFile(String url, String fileName, HttpServletRequest request, HttpServletResponse response) {
        try {
            //1.定义ContentType为("multipart/form-data")让浏览器自己解析文件格式
            response.setContentType("multipart/form-data");
            //2.中文名转码
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xlsx");
            //获得文件
            File file = new File(url);

            FileInputStream in = new FileInputStream(file);
            //3.将文件写入缓冲区OutputStream(out)
            OutputStream out = new BufferedOutputStream(response.getOutputStream());

            int b = 0;
            byte[] buffer = new byte[2048];
            while ((b=in.read(buffer)) != -1){
                //4.将缓冲区文件输出到客户端(out)
                out.write(buffer,0,b);
            }
            in.close();
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

接口调用
@GetMapping("/download")
    public Object download1(HttpServletRequest request, HttpServletResponse response) {
        // 读取模板
        String excelPath = request.getSession().getServletContext().getRealPath("/question/poi/questions123.xls");

        String fileName = "题库表"; //模板名称
        String[] headers = {"类型", "题目(不能重复,注意中英文)", "图片", "分值", "正确答案", "选项A", "选项B", "选项C", "选项D"};
        List<String> headerList = new ArrayList<>();
        for (int i = 0; i < headers.length; i++) {
            headerList.add(headers[i]);
        }
        //下拉框数据
        List<String[]> downData = new ArrayList();
        //数据所在列
        List<String> downRows = Lists.newArrayList();
        String[] str = {"单选", "多选", "判断", "填空", "简答"};
        downData.add(str);
        downRows.add("0");
        try {
            //生成待下载excel模板
            QuestionsImport.createExcelTemplate(excelPath, fileName, headerList, downData, downRows);
            QuestionsImport.downFile(excelPath, fileName, request, response);
            //删除临时文件
            File delFile = new File(excelPath);
            if (delFile.exists()) {
                delFile.delete();
            }
        } catch (Exception e) {
            System.out.println("批量导入信息异常：" + e.getMessage());
        }
        return null;
    }

第二种方法

另外一种工具类写法

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelTest1 {
    //0开始
    private static final int XLS_MAX_ROW = 65535;
    private static final String MAIN_SHEET_NAME = "题库";
    private static final String HIDDEN_SHEET1_NAME = "hidden1";
    private static final String DEVICE_NAMES = "devices";


    /**
     * 创建入库excel模版
     * @param filePath
     * @param headers
     * @param devices
     * @return
     *
     * @throws IOException
     */
    private static File createStoreInExcelTemplate(String filePath, List<String> headers, List<String> devices) throws IOException {
        FileOutputStream out = null;
        File file;
        try {
            //写文件
            file = new File(filePath);
//          file = File.createTempFile("入库数据模版", ".xls");
            out = new FileOutputStream(file);
            //创建工作薄
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet mainSheet = wb.createSheet(MAIN_SHEET_NAME);
            HSSFSheet dtHiddenSheet = wb.createSheet(HIDDEN_SHEET1_NAME);
            //将第二个用于存储下拉框数据的sheet隐藏
             wb.setSheetHidden(1, true);
            initHeaders01(wb, mainSheet, headers);
            initDevicesAndType02(wb, dtHiddenSheet, devices);
            initSheetNameMapping04(mainSheet);
            out.flush();
            wb.write(out);
//          file.deleteOnExit();
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return file;
    }


    private static void initDevicesAndType02(HSSFWorkbook wb, HSSFSheet dtHiddenSheet, List<String> devices) {
        writeDevices02_01(wb, dtHiddenSheet, devices);
        initDevicesNameMapping02_03(wb, dtHiddenSheet.getSheetName(), devices.size());
    }

    private static void writeDevices02_01(HSSFWorkbook wb, HSSFSheet dtHiddenSheet, List<String> devices) {
        for (int i = 0; i < devices.size(); i++) {
            HSSFRow row = dtHiddenSheet.createRow(i);
            HSSFCell cell1 = row.createCell(0);
            cell1.setCellValue(devices.get(i));
        }
    }

    private static void initDevicesNameMapping02_03(HSSFWorkbook workbook, String dtHiddenSheetName, int deviceQuantity) {
        Name name = workbook.createName();
        // 设置选项“名称”
        name.setNameName(DEVICE_NAMES);
        name.setRefersToFormula(dtHiddenSheetName + "!$A$1:$A$" + deviceQuantity);
    }

    /**
     * 初始化表头
     *
     * @param wb
     * @param mainSheet
     * @param headers
     */
    private static void initHeaders01(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {
        //表头样式
        HSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HorizontalAlignment.CENTER);
        //字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        fontStyle.setBold(true);

        style.setFont(fontStyle);
        //生成sheet1内容
        HSSFRow rowFirst = mainSheet.createRow(0);//第一个sheet的第一行为标题
        mainSheet.createFreezePane(0, 1, 0, 1); //冻结第一行
        //写标题
        for (int i = 0; i < headers.size(); i++) {
            HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
            mainSheet.setColumnWidth(i, 4000); //设置每列的列宽
            cell.setCellStyle(style); //加样式
            cell.setCellValue(headers.get(i)); //往单元格里写数据
        }
    }

    /**
     * 主sheet中下拉框初始化
     *
     * @param mainSheet
     */
    private static void initSheetNameMapping04(HSSFSheet mainSheet) {
        DataValidation deviceValidation = getDataValidationByFormula04_01(DEVICE_NAMES, 0);
        // 主sheet添加验证数据
        mainSheet.addValidationData(deviceValidation);
    }


    /**
     * 生成下拉框及提示
     *
     * @param formulaString
     * @param columnIndex
     * @return
     */
    public static DataValidation getDataValidationByFormula04_01(String formulaString, int columnIndex) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(1, XLS_MAX_ROW, columnIndex, columnIndex);
        // 数据有效性对象
        DataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
        dataValidationList.createErrorBox("Error", "请选择或输入有效的选项，或下载最新模版重试！");
        return dataValidationList;
    }

    public static File test() throws IOException {
        List<String> headers = Arrays.asList("类型", "问题(不能重复,注意中英文)", "图片", "分值", "正确答案", "选项A", "选项B", "选项C", "选项D");
        //下拉框
        List<String> devices = Arrays.asList("单选", "多选", "判断", "填空", "问答");
        return ExcelTest1.createStoreInExcelTemplate("D:/test"+getTimeStamp()+".xls", headers,devices);
    }

    public static void main(String[] args) throws IOException {
        ExcelTest1.test();
    }

    public static String getTimeStamp(){
        SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddhhmmssSSS");
        return sdf.format(new Date());
    }
}