java代码导出excel模板，并添加下拉选

我使用的是VUE + springboot

引包

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>


//以下是传参数时候从新引入的jar包
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>2.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.1</version>
        </dependency>

先上VUE页面代码，下载模板的话貌似不能使用ajax，只能使用url方式请求，查了好久，以下方法好用

  <button class="layui-btn layui-btn-normal" @click="downLoadInfo">下载模板</button>



 //下载模板
        downLoadInfo(){
          var url = config.userUrl + 'manager/getHeadTeacherExcelTemplate';  //你的方法
          fetch(url,{
            method: 'POST',
            headers: new Headers({
              'token':dataUtils.getData(config.key.tokenKey) // 指定提交方式为表单提交
            }),
          }).then(res => res.blob().then(blob => {  //这段不是很清楚
            var a = document.createElement('a');
            var url = window.URL.createObjectURL(blob);
            var filename = '班主任信息模板.xlsx';
            a.href = url;
            a.download = filename;
            a.click();
            window.URL.revokeObjectURL(url);
          }));
        },

如果需要参数需要更改成如下：

fetch(url,{
            method: 'POST',
           body: JSON.stringify({'business':'1'}),
            headers: new Headers({ 'Content-Type': 'application/json',
              'token':dataUtils.getData(config.key.tokenKey) // 指定提交方式为表单提交
            }),

 

springboot代码

工具  1

package com.baokang.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
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.util.List;

/**
 * 导出工具类
 *
 * @author ty
 * @version 2019-2-13
 */

public class DownExcelTemp {

    /**
     * @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);
        for (int i = 0; i < headers.size(); i++) {
            cell1.setCellValue(headers.get(i));
            cell1.setCellStyle(style);
            //设置单元格宽度
            sheetPro.setColumnWidth(i, 20 * 256);
            //设置居中样式
            sheetPro.setDefaultColumnStyle(i, style);
            cell1 = row0.createCell((short) i + 1);
        }
        //创建一个专门用来存放地区信息的隐藏sheet页
        //因此也不能在现实页之前创建，否则无法隐藏。
        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 {

//            FileInputStream fileInput = new FileInputStream(filePath);
//            // 创建一个webbook，对应一个Excel文件
//            XSSFWorkbook workbook = new XSSFWorkbook(fileInput);
//            //对应Excel文件中的sheet，0代表第一个
//            XSSFSheet sheet1 = workbook.getSheetAt(0);
//            XSSFSheet sheet2 = workbook.getSheetAt(1);
        //修改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();
    }

    /**
     * @Title: SetDataValidation
     * @Description: 下拉列表元素很多的情况(255以上的下拉)
     * @param @param strFormula
     * @param @param firstRow 起始行
     * @param @param endRow 终止行
     * @param @param firstCol 起始列
     * @param @param endCol 终止列
     * @param xls格式
     * @return HSSFDataValidation @throws
     */
	/*private static HSSFDataValidation SetDataValidation(String strFormula, int firstRow, int endRow, int firstCol,
			int endCol) {
		
		// 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
		HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);

		return dataValidation;
	}*/

    /**
     * @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);
        // validation.setShowErrorBox(true);
        //sheet.addValidationData(validation);
        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);
        // 07默认setSuppressDropDownArrow(true);
        // validation.setSuppressDropDownArrow(true);
        // validation.setShowErrorBox(true);
        return validation;
        //xls 格式下拉菜单begin  DataValidation
		/*DataValidationHelper helper = sheet.getDataValidationHelper();
		// 加载下拉列表内容
		DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
		// DVConstraint constraint = new DVConstraint();
		constraint.setExplicitListValues(textList);
		// 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol,
				(short) endCol);
		// 数据有效性对象
		DataValidation data_validation = helper.createValidation(constraint, regions);
		//xls 格式下拉菜单end
		return data_validation;*/
    }

}

工具 2

package com.dosion.core.common.utils.excel;

import com.ctrip.framework.apollo.core.utils.PropertiesUtil;
import com.dosion.core.common.utils.StringUtils;
import org.springframework.core.io.DefaultResourceLoader;

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

public class DownLoadUtil {
	/**
     * 
     * @Title: encodeChineseDownloadFileName 
     * 
     * @param @param request
     * @param @param pFileName
     * @param @return
     * @param @throws UnsupportedEncodingException
     * @return String
     * @throws
     */
    public static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName)
            throws UnsupportedEncodingException {
        
        String filename = null;
        String agent = request.getHeader("USER-AGENT");
        if (null != agent) {
            if (-1 != agent.indexOf("Firefox")) {
            	//Firefox  
                filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?=";
            } else if (-1 != agent.indexOf("Chrome")) {
            	//Chrome  
                filename = new String(pFileName.getBytes(), "ISO8859-1");
            } else {
            	//IE7+  
                filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
                //替换特殊字符
                filename = StringUtils.replace(filename, "+", "%20");
            }
        } else {
            filename = pFileName;
        }
        
        return filename;
    }
    
    public static String getPysicalPath(String virtualPath,HttpServletRequest request) {
		//获得根绝对路径
		String physicalPath = getProjectPath();
		//获得项目路径
		String basePath = request.getContextPath();
		if(virtualPath.startsWith(basePath)){
			virtualPath = virtualPath.substring(basePath.length());
		}
		return physicalPath + virtualPath;
	}
    
    /**
     * @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=\""+encodeChineseDownloadFileName(request, fileName+".xlsx") +"\"");
             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();
        }  
	}
	
	/**
     * 获取工程项目根路径
     * @return
     */
    public static String getProjectPath(){
    	// 如果配置了工程路径，则直接返回，否则自动获取。
		String projectPath = null;
//		if (StringUtils.isNotBlank(projectPath)){
//			return projectPath;
//		}
		try {
			File file = new DefaultResourceLoader().getResource("").getFile();
			if (file != null){
				while(true){
					File f = new File(file.getPath() + File.separator + "src" + File.separator + "main");
					if (f == null || f.exists()){
						break;
					}
					if (file.getParentFile() != null){
						file = file.getParentFile();
					}else{
						break;
					}
				}
				projectPath = file.toString();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return projectPath;
    }
}

下载方法

    @ApiOperation("下载模板")
    @RequestMapping(value = "exportCustomerTemplate", method = RequestMethod.POST)
    @Permission("customer:customer:edit")
    public void exportCustomerTemplate(CustomerInputDTO model, HttpServletRequest request, HttpServletResponse response) throws IOException {
        //指定文件路径
        String uploadPath = "/src/main/resources/temp/";
        // 文件新名
        String newFileName = DateUtils.formatDate(new Date(), "yyyyMMddHHmmssSSS") + ".xlsx";
        //将虚拟路径转换物理路径
        String newFilePath = DownLoadUtil.getPysicalPath(uploadPath + newFileName, request);
        //模板名称
        String fileName = "客户信息模板";
        //设置头
        String[] headers = {"客户姓名", "服务方向", "性别", "联系方式", "负责人id", "来源"};
        List<String> headerList = new ArrayList<>();
        for (int i = 0; i < headers.length; i++) {
            headerList.add(headers[i]);
        }
        //处理数据
        DictInputDTO dictInputDTO = new DictInputDTO();
        List<DictOutDTO> dictOutDTO = userService.findBusiness(dictInputDTO);
        List<CustomerDO> sexList = customerService.findSexList();
        List<CustomerDO> sourceList = customerService.findSourceList();
        //创建数据集
        String sourceStr = "";
        String sexStr = "";
        String serviceStr = "";
        //存放数据集
        List<String[]> downData = new ArrayList<>();
        //数据所在列
        List<String> downRows = Lists.newArrayList();
        //下拉框数据处理
        for (int i = 0; i < sourceList.size(); i++) {
            sourceStr += sourceList.get(i).getSourceStr() + ",";
        }
        for (int i = 0; i < sexList.size(); i++) {
            sexStr += sexList.get(i).getSex() + ",";
        }
        for (int i = 0; i < dictOutDTO.size(); i++) {
            serviceStr += dictOutDTO.get(i).getLabel() + ",";
        }
        String[] service = serviceStr.substring(0, serviceStr.length()-1).split(",");
        String[] sex = sexStr.substring(0, sexStr.length()-1).split(",");
        String[] source = sourceStr.substring(0, sourceStr.length()-1).split(",");
        downData.add(service);
        downRows.add("1");
        downData.add(sex);
        downRows.add("2");
        downData.add(source);
        downRows.add("5");
        //生成待下载excel模板
        DownExcelTemp.createExcelTemplate(newFilePath, fileName, headerList, downData, downRows);
        //下载模板
        DownLoadUtil.downFile(newFilePath, fileName, request, response);
        //删除临时文件
        File delFile = new File(newFilePath);
        if (delFile.exists()) {
            delFile.delete();

        }
    }

获取参数后台接收参数方式:

加入参数  @RequestBody Map<String, Object> map

 

 

导出效果