java工具封装之poi的导入导出工具、自定义导出Template模板excel

封装的工具不用自己去判断excel中的每一行每一单元，直接根据名称属性赋值给对象，可直接file文件转成list对象数据

 

==============================================================2022年10月9日更新

工具类ExcelUtil.java代码：

package com.jzproject.common.util.ExcelUtil;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil<T> {

    Class<T> clazz;  

    public ExcelUtil(Class<T> clazz) {  
        this.clazz = clazz;  
    }  

    public List<T> importExcel(String sheetName, InputStream input) {  
        int maxCol = 0;  
        List<T> list = new ArrayList<T>();  
        try {  
            HSSFWorkbook workbook = new HSSFWorkbook(input);  
            HSSFSheet sheet = workbook.getSheet(sheetName);  
            if (!sheetName.trim().equals("")) {  
                sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
            }  
            if (sheet == null) {  
                sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
            }  
            int rows = sheet.getPhysicalNumberOfRows();  

            if (rows > 0) {// 有数据时才处理
                // Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
                List<Field> allFields = getMappedFiled(clazz, null);  

                Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();// 定义一个map用于存放列的序号和field.
                for (Field field : allFields) {  
                    //将有注解的field存放到map中.
                    if (field.isAnnotationPresent(ExcelVOAttribute.class)) {  
                        ExcelVOAttribute attr = field  
                                .getAnnotation(ExcelVOAttribute.class);  
                        int col = getExcelCol(attr.column());// 获得列号
                        maxCol = Math.max(col, maxCol);  
                        // System.out.println(col + "====" + field.getName());  
                        field.setAccessible(true);// 设置类的私有字段属性可访问.
                        fieldsMap.put(col, field);  
                    }  
                }  
                for (int i = 1; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.
                    HSSFRow row = sheet.getRow(i);  
                    // int cellNum = row.getPhysicalNumberOfCells();  
                    // int cellNum = row.getLastCellNum();  
                    int cellNum = maxCol;  
                    T entity = null;  
                    for (int j = 0; j < cellNum; j++) {  
                        HSSFCell cell = row.getCell(j);  
                        if (cell == null) {  
                            continue;  
                        }  
                        int cellType = cell.getCellType();  
                        String c = "";  
                        if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {  
                            c = String.valueOf(cell.getNumericCellValue());  
                        } else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {  
                            c = String.valueOf(cell.getBooleanCellValue());  
                        } else {  
                            c = cell.getStringCellValue();  
                        }  
                        if (c == null || c.equals("")) {  
                            continue;  
                        }  
                        entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
                        // System.out.println(cells[j].getContents());  
                        Field field = fieldsMap.get(j);// 从map中得到对应列的field.
                        if (field==null) {  
                            continue;  
                        }
                        // 取得类型,并根据对象类型设置值.
                        Class<?> fieldType = field.getType();  
                        if (String.class == fieldType) {  
                            field.set(entity, String.valueOf(c));  
                        } else if ((Integer.TYPE == fieldType)  
                                || (Integer.class == fieldType)) {  
                            field.set(entity, Integer.parseInt(c));  
                        } else if ((Long.TYPE == fieldType)  
                                || (Long.class == fieldType)) {  
                            field.set(entity, Long.valueOf(c));  
                        } else if ((Float.TYPE == fieldType)  
                                || (Float.class == fieldType)) {  
                            field.set(entity, Float.valueOf(c));  
                        } else if ((Short.TYPE == fieldType)  
                                || (Short.class == fieldType)) {  
                            field.set(entity, Short.valueOf(c));  
                        } else if ((Double.TYPE == fieldType)  
                                || (Double.class == fieldType)) {  
                            field.set(entity, Double.valueOf(c));  
                        } else if (Character.TYPE == fieldType) {  
                            if ((c != null) && (c.length() > 0)) {  
                                field.set(entity, Character  
                                        .valueOf(c.charAt(0)));  
                            }  
                        }  

                    }  
                    if (entity != null) {  
                        list.add(entity);  
                    }  
                }  
            }  

        } catch (IOException e) {  
            e.printStackTrace();  
        } catch (InstantiationException e) {  
            e.printStackTrace();  
        } catch (IllegalAccessException e) {  
            e.printStackTrace();  
        } catch (IllegalArgumentException e) {  
            e.printStackTrace();  
        }  
        return list;  
    }

    /**
    * @Description: 导入excel文件 (因为2003和2007版本问题, .xls使用HssFworkbook, .xlsx使用XSSFWorkbook)
    **/
    public List<T> importExcelXlsx(String sheetName, InputStream input) {
        int maxCol = 0;
        List<T> list = new ArrayList<T>();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(input);
            XSSFSheet sheet = workbook.getSheet(sheetName);
//            HSSFWorkbook workbook = new HSSFWorkbook(input);
//            HSSFSheet sheet = workbook.getSheet(sheetName);
            if (!sheetName.trim().equals("")) {
                sheet = workbook.getSheet(sheetName);//
            }
            if (sheet == null) {
                sheet = workbook.getSheetAt(0);
            }
            int rows = sheet.getPhysicalNumberOfRows();

            if (rows > 0) {
                // Field[] allFields = clazz.getDeclaredFields();
                List<Field> allFields = getMappedFiled(clazz, null);

                Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
                for (Field field : allFields) {
                    // ????????field????map??.
                    if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
                        ExcelVOAttribute attr = field
                                .getAnnotation(ExcelVOAttribute.class);
                        int col = getExcelCol(attr.column());
                        maxCol = Math.max(col, maxCol);
                        // System.out.println(col + "====" + field.getName());
                        field.setAccessible(true);
                        fieldsMap.put(col, field);
                    }
                }
                for (int i = 1; i < rows; i++) {
//                    HSSFRow row = sheet.getRow(i);
                    XSSFRow row = sheet.getRow(i);
                    // int cellNum = row.getPhysicalNumberOfCells();
                    // int cellNum = row.getLastCellNum();
                    int cellNum = maxCol;
                    T entity = null;
                    for (int j = 0; j < cellNum; j++) {
//                        HSSFCell cell = row.getCell(j);
                        XSSFCell cell = row.getCell(j);
                        if (cell == null) {
                            continue;
                        }
                        int cellType = cell.getCellType();
                        String c = "";
                        if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
                            c = String.valueOf(cell.getNumericCellValue());
                        } else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
                            c = String.valueOf(cell.getBooleanCellValue());
                        } else {
                            c = cell.getStringCellValue();
                        }
                        if (c == null || c.equals("")) {
                            continue;
                        }
                        entity = (entity == null ? clazz.newInstance() : entity);
                        // System.out.println(cells[j].getContents());
                        Field field = fieldsMap.get(j);
                        if (field==null) {
                            continue;
                        }
                        // ???????,??????????????????.
                        Class<?> fieldType = field.getType();
                        if (String.class == fieldType) {
                            field.set(entity, String.valueOf(c));
                        } else if ((Integer.TYPE == fieldType)
                                || (Integer.class == fieldType)) {
                            field.set(entity, Integer.parseInt(c));
                        } else if ((Long.TYPE == fieldType)
                                || (Long.class == fieldType)) {
                            field.set(entity, Long.valueOf(c));
                        } else if ((Float.TYPE == fieldType)
                                || (Float.class == fieldType)) {
                            field.set(entity, Float.valueOf(c));
                        } else if ((Short.TYPE == fieldType)
                                || (Short.class == fieldType)) {
                            field.set(entity, Short.valueOf(c));
                        } else if ((Double.TYPE == fieldType)
                                || (Double.class == fieldType)) {
                            field.set(entity, Double.valueOf(c));
                        } else if (Character.TYPE == fieldType) {
                            if ((c != null) && (c.length() > 0)) {
                                field.set(entity, Character
                                        .valueOf(c.charAt(0)));
                            }
                        }

                    }
                    if (entity != null) {
                        list.add(entity);
                    }
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        }
        return list;
    }






    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetName
     *            工作表的名称
     * @param output
     *            java输出流
     */  
    public boolean exportExcel(List<T> lists[], String sheetNames[],  
            OutputStream output) {  
        if (lists.length != sheetNames.length) {  
            System.out.println("数组长度不一致");
            return false;  
        }  

        HSSFWorkbook workbook = new HSSFWorkbook();// 产生工作薄对象

        for (int ii = 0; ii < lists.length; ii++) {  
            List<T> list = lists[ii];  
            String sheetName = sheetNames[ii];  

            List<Field> fields = getMappedFiled(clazz, null);  

            HSSFSheet sheet = workbook.createSheet();// 产生工作表对象

            workbook.setSheetName(ii, sheetName);  

            HSSFRow row;  
            HSSFCell cell;// 产生单元格
            HSSFCellStyle style = workbook.createCellStyle();  
            style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);  
            style.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);  
            row = sheet.createRow(0);// 产生一行
            // 写入各个字段的列头名称
            for (int i = 0; i < fields.size(); i++) {  
                Field field = fields.get(i);  
                ExcelVOAttribute attr = field  
                        .getAnnotation(ExcelVOAttribute.class);  
                int col = getExcelCol(attr.column());// 获得列号
                cell = row.createCell(col);// 创建列
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型
                cell.setCellValue(attr.name());// 写入列名

                // 如果设置了提示信息则鼠标放上去提示.
                if (!attr.prompt().trim().equals("")) {  
                    setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, col, col);// 这里默认设了2-101列提示.
                }
                // 如果设置了combo属性则本列只能选择不能输入
                if (attr.combo().length > 0) {  
                    setHSSFValidation(sheet, attr.combo(), 1, 100, col, col);// 这里默认设了2-101列只能选择不能输入.
                }  
                cell.setCellStyle(style);  
            }  

            int startNo = 0;  
            int endNo = list.size();
            // 写入各条记录,每条记录对应excel表中的一行
            for (int i = startNo; i < endNo; i++) {  
                row = sheet.createRow(i + 1 - startNo);  
                T vo = (T) list.get(i); // 得到导出对象.
                for (int j = 0; j < fields.size(); j++) {  
                    Field field = fields.get(j);// 获得field.
                    field.setAccessible(true);// 设置实体类私有属性可访问
                    ExcelVOAttribute attr = field  
                            .getAnnotation(ExcelVOAttribute.class);  
                    try {
                        // 根据ExcelVOAttribute中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
                        if (attr.isExport()) {  
                            cell = row.createCell(getExcelCol(attr.column()));// 创建cell
                            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
                            cell.setCellValue(field.get(vo) == null ? ""  
                                    : String.valueOf(field.get(vo)));// 如果数据存在就填入,不存在填入空格.
                        }  
                    } catch (IllegalArgumentException e) {  
                        e.printStackTrace();  
                    } catch (IllegalAccessException e) {  
                        e.printStackTrace();  
                    }  
                }  
            }  
        }  

        try {  
            output.flush();  
            workbook.write(output);  
            output.close();  
            return true;  
        } catch (IOException e) {  
            e.printStackTrace();  
            System.out.println("Output is closed ");  
            return false;  
        }  

    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetName
     *            工作表的名称
     * @param sheetSize
     *            每个sheet中数据的行数,此数值必须小于65536
     * @param output
     *            java输出流
     *
     */
    @SuppressWarnings("unchecked")
    public boolean exportExcel(List<T> list, String sheetName,  
            OutputStream output) {
        //此处 对类型进行转换
        List<T> ilist = new ArrayList<T>();
        for (T t : list) {
            ilist.add(t);
        }
        List<T>[] lists = new ArrayList[1];  
        lists[0] = ilist;  

        String[] sheetNames = new String[1];  
        sheetNames[0] = sheetName;  

        return exportExcel(lists, sheetNames, output);  
    }

    /**
     * 将EXCEL中A,B,C,D,E列映射成0,1,2,3
     *
     * @param col
     */
    public static int getExcelCol(String col) {  
        col = col.toUpperCase();
        // 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
        int count = -1;  
        char[] cs = col.toCharArray();  
        for (int i = 0; i < cs.length; i++) {  
            count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);  
        }  
        return count;  
    }

    /**
     * 设置单元格上提示
     *
     * @param sheet
     *            要设置的sheet.
     * @param promptTitle
     *            标题
     * @param promptContent
     *            内容
     * @param firstRow
     *            开始行
     * @param endRow
     *            结束行
     * @param firstCol
     *            开始列
     * @param endCol
     *            结束列
     * @return 设置好的sheet.
     */
    public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle,  
            String promptContent, int firstRow, int endRow, int firstCol,  
            int endCol) {  
        /// 构造constraint对象
        DVConstraint constraint = DVConstraint  
                .createCustomFormulaConstraint("DD1");
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,  
                endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_view = new HSSFDataValidation(  
                regions, constraint);  
        data_validation_view.createPromptBox(promptTitle, promptContent);  
        sheet.addValidationData(data_validation_view);  
        return sheet;  
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet
     *            要设置的sheet.
     * @param textlist
     *            下拉框显示的内容
     * @param firstRow
     *            开始行
     * @param endRow
     *            结束行
     * @param firstCol
     *            开始列
     * @param endCol
     *            结束列
     * @return 设置好的sheet.
     */
    public static HSSFSheet setHSSFValidation(HSSFSheet sheet,  
            String[] textlist, int firstRow, int endRow, int firstCol,  
            int endCol) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint  
                .createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,  
                endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_list = new HSSFDataValidation(  
                regions, constraint);  
        sheet.addValidationData(data_validation_list);  
        return sheet;  
    }

    /**
     * 得到实体类所有通过注解映射了数据表的字段
     *
     * @param map
     * @return
     */
    @SuppressWarnings("rawtypes")
    private List<Field> getMappedFiled(Class clazz, List<Field> fields) {  
        if (fields == null) {  
            fields = new ArrayList<Field>();  
        }  

        Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段
        // 得到所有field并存放到一个list中.
        for (Field field : allFields) {  
            if (field.isAnnotationPresent(ExcelVOAttribute.class)) {  
                fields.add(field);  
            }  
        }  
        if (clazz.getSuperclass() != null  
                && !clazz.getSuperclass().equals(Object.class)) {  
            getMappedFiled(clazz.getSuperclass(), fields);  
        }  

        return fields;  
    }  
    
}

 

ExcelVOAttribute.java代码：

package com.jzproject.common.util.ExcelUtil;

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy; 
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)  
@Target( { java.lang.annotation.ElementType.FIELD })
public @interface ExcelVOAttribute {

    /** 
     * 导出到Excel中的名字.
     */  
    public abstract String name();  

    /** 
     * 配置列的名称,对应A,B,C,D....
     */  
    public abstract String column();  

    /** 
     * 提示信息
     */  
    public abstract String prompt() default "";  

    /** 
     * 设置只能选择不能输入的列内容.
     */  
    public abstract String[] combo() default {};  

    /** 
     * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
     */  
    public abstract boolean isExport() default true;
    
}

 

 

导出对应的数据 实休类TUserExt.java，想导出哪一列，就给对应的字段上面加上注解：

    @ExcelVOAttribute(name="userId", column="A")
    private String userId;

    @ExcelVOAttribute(name="用户名称", column="B")
    private String userName;

    @ExcelVOAttribute(name="电话号码", column="C")
    private String phone;

 

 

Controller层调用：

    /**
     * 测试导出excel
     * @param req
     * @param resp
     */
    @RequestMapping(value = "/testExcelExport", method = RequestMethod.GET)
    public void testExcelExport(HttpServletRequest req, HttpServletResponse resp){

        try {
            List<TUserExt> userExtList = new ArrayList<TUserExt>();
            TUserExt userExt = new TUserExt();
            userExt.setUserId("1");
            userExt.setUserName("张三");
            userExt.setPhone("13112345678");
            userExtList.add(userExt);

            String header = "xxxHeader";

            ServletOutputStream out = null;
//            String outTime = DateUtil.getCurrTimeStamp();
            resp.setContentType("application/application/vnd.ms-excel");
            resp.setHeader("Content-disposition",
                    "attachment;filename="+ URLEncoder.encode("test01.xls","UTF-8"));
            out = resp.getOutputStream();

            ExcelUtil<TUserExt> util = new ExcelUtil<TUserExt>(TUserExt.class);
            util.exportExcel(userExtList, "test01Sheet", out);

        } catch (Exception e) {
            e.printStackTrace();
        }

        }

 

 

 

成功结果：

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 ========================================== poi插件之 web网页请求 导出自定义模板excel

借鉴博客：https://blog.csdn.net/tanqingfu1/article/details/123545079

 

　自定义导出excel模板工具类：CreateExcelTemplateUtil.java

package org.springblade.modules.common.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.bind.annotation.GetMapping;

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

/**
 * @Description: TODO
 * @Author: zhouruntao
 * @Date: 2022/11/3 15:16
 **/
public class CreateExcelTemplateUtil {


    /**
    * @Description: web端传response调用此方法
    * @Author: zhouruntao
    * @DATE: 2022/11/3 15:24
    **/
    public static void getExcelTemplate(String filePath, String[] headers, List<String[]> downData, String[] downRows,
                                        HttpServletRequest request, HttpServletResponse response, String sheetName){

        HSSFWorkbook workbook = createExcelTemplate(filePath, headers, downData, downRows);

//        HSSFWorkbook workbook = ComExportExcelTemplateUtil.createExcelTemplate(filePath, headers, downData, downRows,sheetName);

        //postman调用导出excel文件接口会默认文件名：response.xls，不用在意，用浏览器调用就正常了

        String fileName = encodeFileName(sheetName,request);// 调用encodeFileName()方法对文件名进行编码转换
        response.setHeader("Content-Disposition","inline;filename="+ fileName + ".xls");
        response.setContentType("application/vnd.ms-excel");

        try {
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
            workbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }


    // 生成的模板文件临时路径
    private String filePath = "D://员工信息表.xls";
    /**
     * 下载excel模板  根据不同的内容生成的 ===================（本地测试）
     * @param response
     * @throws IOException
     */
    @GetMapping("/downTemplate")
    public void downTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //模板名称
        String fileName = filePath;
        //列标题
        String[] title = {"姓名","性别","证件类型","证件号码","服务结束时间","参保地","民族"};
        //下拉框数据
        List<String[]> downData = new ArrayList();
        String[] str1 = {"男","女","未知"};
        String[] str2 = {"北京","上海","广州","深圳","武汉","长沙","湘潭"};
        String[] str3 = {"01-汉族","02-蒙古族","03-回族","04-藏族","05-维吾尔族","06-苗族","07-彝族","08-壮族","09-布依族","10-朝鲜族","11-满族","12-侗族","13-瑶族","14-白族","15-土家族","16-哈尼族","17-哈萨克族","18-傣族","19-黎族","20-傈僳族","21-佤族","22-畲族","23-高山族","24-拉祜族","25-水族","26-东乡族","27-纳西族","28-景颇族","29-柯尔克孜族","30-土族","31-达斡尔族","32-仫佬族","33-羌族","34-布朗族","35-撒拉族","36-毛难族","37-仡佬族","38-锡伯族","39-阿昌族","40-普米族","41-塔吉克族","42-怒族","43-乌孜别克族","44-俄罗斯族","45-鄂温克族","46-德昂族","47-保安族","48-裕固族","49-京族","50-塔塔尔族","51-独龙族","52-鄂伦春族","53-赫哲族","54-门巴族","55-珞巴族","56-基诺族","98-外国血统","99-其他"};
        downData.add(str1);
        downData.add(str2);
        downData.add(str3);
        String [] downRows = {"1","5","6"}; //下拉的列序号数组(序号从0开始)
        try {
            createExcelTemplate(fileName, title, downData, downRows);
            //通过文件路径获得File对象
            File file = new File(filePath);
            //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //2.设置文件头：最后一个参数是设置下载文件名
            String fileName1 = URLEncoder.encode("员工信息", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName1 + ".xls");
            FileInputStream in = new FileInputStream(file);
            //3.通过response获取OutputStream对象(out)
            OutputStream out = new BufferedOutputStream(response.getOutputStream());
            int b = 0;
            byte[] buffer = new byte[2048];
            while ((b=in.read(buffer)) != -1){
                out.write(buffer,0,b); //4.写到输出流(out)中
            }
            in.close();
            out.flush();
            out.close();
            delFile(filePath);
        } catch (Exception e) {
//            log.error("批量导入信息异常：" + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * @Title: createExcelTemplate
     * @Description: 生成Excel导入模板
     * @param @param filePath  Excel文件路径
     * @param @param handers   Excel列标题(数组)
     * @param @param downData  下拉框数据(数组)
     * @param @param downRows  下拉列的序号(数组,序号从0开始)
     * @return void
     * @throws
     */
    private static HSSFWorkbook createExcelTemplate(String filePath, String[] title, List<String[]> downData, String[] downRows){
        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
        //表头样式
        HSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        // style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short)12);
        // fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(fontStyle);

        // 新建sheet
        HSSFSheet sheet1 = wb.createSheet("Sheet1");
        HSSFSheet sheet2 = wb.createSheet("Sheet2");
//        HSSFSheet sheet3 = wb.createSheet("Sheet3");

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

        // 设置下拉框数据
        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;
        HSSFRow row = null;
        for(int r=0;r<downRows.length;r++){
            // 获取下拉对象
            String[] dlData = downData.get(r);
            int rownum = Integer.parseInt(downRows[r]);
            // 255以内的下拉
            if(dlData.length<5){
                // 255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
                sheet1.addValidationData(setDataValidation(sheet1, dlData, 1, 500, rownum ,rownum)); //超过255个报错
            } else {
                // 255以上的下拉，即下拉列表元素很多的情况
                // 1、设置有效性
                // Sheet2第A1到A5000作为下拉列表来源数据
                // String strFormula = "Sheet2!$A$1:$A$5000" ;
                // Sheet2第A1到A5000作为下拉列表来源数据
                String strFormula = "Sheet2!$"+arr[index]+"$1:$"+arr[index]+"$" + dlData.length;
                // 设置每列的列宽
                sheet2.setColumnWidth(r, 4000);
                // 设置数据有效性加载在哪个单元格上,参数分别是：从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
                //下拉列表元素很多的情况
                sheet1.addValidationData(SetDataValidation(strFormula, 1, dlData.length, rownum, rownum));

                //2、生成sheet2内容
                for(int j=0;j<dlData.length;j++){
                    if(index==0){ //第1个下拉选项，直接创建行、列
                        // 创建数据行
                        row = sheet2.createRow(j);
                        // 设置每列的列宽
                        sheet2.setColumnWidth(j, 4000);
                        // 设置对应单元格的值
                        row.createCell(0).setCellValue(dlData[j]);

                    } else { //非第1个下拉选项

                        int rowCount = sheet2.getLastRowNum();
                        //System.out.println("========== LastRowNum =========" + rowCount);
                        // 前面创建过的行，直接获取行，创建列
                        if(j<=rowCount){
                            // 获取行，创建列
                            // 设置对应单元格的值
                            sheet2.getRow(j).createCell(index).setCellValue(dlData[j]);

                        } else { //未创建过的行，直接创建行、创建列
                            // 设置每列的列宽
                            sheet2.setColumnWidth(j, 4000);
                            // 创建行、创建列
                            // 设置对应单元格的值
                            sheet2.createRow(j).createCell(index).setCellValue(dlData[j]);
                        }
                    }
                }
                index++;
            }
        }
        wb.setSheetHidden(1, true); //隐藏掉专门放数据字典下拉列的sheet页
        return wb;

//        try {
//
//            File f = new File(filePath); //写文件
//
//            //不存在则新增
//            if(!f.getParentFile().exists()){
//                f.getParentFile().mkdirs();
//            }
//            if(!f.exists()){
//                f.createNewFile();
//            }
//
//            FileOutputStream out = new FileOutputStream(f);
//            out.flush();
//            wb.write(out);
//            out.close();
//        } catch (FileNotFoundException e) {
//            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
    }

    /**
     *
     * @Title: SetDataValidation
     * @Description: 下拉列表元素很多的情况 (255以上的下拉)
     * @param @param strFormula
     * @param @param firstRow   起始行
     * @param @param endRow     终止行
     * @param @param firstCol   起始列
     * @param @param endCol     终止列
     * @param @return
     * @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);

        dataValidation.createErrorBox("Error", "Error");
        dataValidation.createPromptBox("", null);
        return dataValidation;
    }

    /**
     *
     * @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 DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {

        DataValidationHelper helper = sheet.getDataValidationHelper();
        //加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        //DVConstraint constraint = new DVConstraint();
        constraint.setExplicitListValues(textList);

        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow,firstCol, endCol);
        //数据有效性对象
        DataValidation data_validation = helper.createValidation(constraint, regions);
        //DataValidation data_validation = new DataValidation(regions, constraint);
        return data_validation;
    }

    /**
     * @Title: delFile
     * @Description: 删除文件
     * @param @param filePath  文件路径
     * @return void
     * @throws
     */
    public static void delFile(String filePath) {
        java.io.File delFile = new java.io.File(filePath);
        delFile.delete();
    }



    public static String encodeFileName(String fileNames, HttpServletRequest request) {
        String codedFilename = null;
        try {
            String agent = request.getHeader("USER-AGENT");
            if (null != agent && -1 != agent.indexOf("MSIE") || null != agent && -1 != agent.indexOf("Trident")
                    || null != agent && -1 != agent.indexOf("Edge")) {// ie浏览器及Edge浏览器
                String name = java.net.URLEncoder.encode(fileNames, "UTF-8");
                codedFilename = name;
            } else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,Chrome等浏览器
                codedFilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");
            }else {
                codedFilename = new String(fileNames.getBytes("UTF-8"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return codedFilename;
    }



}

 

 

 

 

 调用导出excel测试：

　　

public void getExcelSafeQuestionTemplate(HttpServletRequest request, HttpServletResponse response){
        String fileName = "安全问题库模板"; //模板名称
        String[] headers = {"编号","安全风险项","问题分类","作业分类","安全风险事件","问题级别","责任部门","安全风险问题点描述"}; //列标题
        //下拉框数据
        List<String[]> downData = new ArrayList<String[]>();

        //下拉框字典数据：safe_type 安全风险项
        List<DictBiz> safeTypeList = DictBizCache.getList("safe_type");
        if(safeTypeList != null && safeTypeList.size() > 0){
            String[] values = new String[safeTypeList.size()];
            for(int i=0;i<safeTypeList.size();i++) {
                values[i] = safeTypeList.get(i).getDictValue();
            }
            downData.add(values);
        }




        //下拉框的列序号数组(序号从0开始)，有几列下拉框，downData里就要添加几个，不然导出的文件会变成txt或无后缀
        // String [] downRows = {"1","2","3","4","5"};
        String [] downRows = {"1"}; //downRows对象里1个，downData对象里也只能1个
        try {

//            BankSubjectService.getExcelTemplate(fileName,headers, downData, downRows, request, response,"题库模板");

//重写导出模板工具方法：下拉选项超255字符，没问题显示
CreateExcelTemplateUtil.getExcelTemplate(null, headers, downData, downRows, request, response,"安全问题库模板");

 } catch (Exception e) { 
　　e.printStackTrace(); 
    }
}

 

 

 测试成功结: 下拉列表几十、上百个选项没问题显示出来了

 

 

 

 

 

 

 

 

 

 

 

 

。