java项目中Excel文件的导入导出

 java项目中Excel文件的导入导出

Excel 工具类

package cn.cmodes.common.utils.poi;

import cn.cmodes.common.utils.DateUtils;
import cn.cmodes.common.utils.DictUtils;
import cn.cmodes.common.utils.StringUtils;
import cn.cmodes.framework.aspectj.lang.annotation.Excel;
import cn.cmodes.framework.config.SystemConfig;
import cn.cmodes.framework.web.domain.AjaxResult;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel相关处理
 */
public class ExcelUtil<T> {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    public Class<T> clazz;

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

    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param input 输入流
     * @return 转换后集合
     */
    public List<T> importExcel(InputStream input) throws Exception {
        return importExcel(StringUtils.EMPTY, input);
    }

    /**
     * 对excel表单指定表格索引名转换成list
     *
     * @param sheetName 表格索引名
     * @param input     输入流
     * @return 转换后集合
     */
    public List<T> importExcel(String sheetName, InputStream input) throws Exception {
        List<T> list = new ArrayList<T>();

        Workbook workbook = WorkbookFactory.create(input);
        Sheet sheet = null;
        if (StringUtils.isNotEmpty(sheetName)) {
            // 如果指定sheet名,则取指定sheet中的内容.
            sheet = workbook.getSheet(sheetName);
        } else {
            // 如果传入的sheet名不存在则默认指向第1个sheet.
            sheet = workbook.getSheetAt(0);
        }

        if (sheet == null) {
            throw new IOException("文件sheet不存在");
        }

        int rows = sheet.getPhysicalNumberOfRows();

        if (rows > 0) {
            // 默认序号
            int serialNum = 0;
            // 有数据时才处理 得到类的所有field.
            Field[] allFields = clazz.getDeclaredFields();
            // 定义一个map用于存放列的序号和field.
            Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
            for (int col = 0; col < allFields.length; col++) {
                Field field = allFields[col];
                // 将有注解的field存放到map中.
                if (field.isAnnotationPresent(Excel.class)) {
                    // 设置类的私有字段属性可访问.
                    field.setAccessible(true);
                    fieldsMap.put(++serialNum, field);
                }
            }
            for (int i = 1; i < rows; i++) {
                // 从第2行开始取数据,默认第一行是表头.
                Row row = sheet.getRow(i);
                int cellNum = serialNum;
                T entity = null;
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    } else {
                        // 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了
                        row.getCell(j).setCellType(CellType.STRING);
                        cell = row.getCell(j);
                    }

                    String c = cell.getStringCellValue();
                    if (StringUtils.isEmpty(c)) {
                        continue;
                    }

                    // 如果不存在实例则新建.
                    entity = (entity == null ? clazz.newInstance() : entity);
                    // 从map中得到对应列的field.
                    Field field = fieldsMap.get(j + 1);
                    // 取得类型,并根据对象类型设置值.
                    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)));
                        }
                    } else if (java.util.Date.class == fieldType) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            cell.setCellValue(sdf.format(cell.getNumericCellValue()));
                            c = sdf.format(cell.getNumericCellValue());
                        } else {
                            c = cell.getStringCellValue();
                        }
                    } else if (java.math.BigDecimal.class == fieldType) {
                        c = cell.getStringCellValue();
                    }
                }
                if (entity != null) {
                    list.add(entity);
                }
            }
        }

        return list;
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    public AjaxResult exportExcel(List<T> list, String sheetName) {
        OutputStream out = null;
        HSSFWorkbook workbook = null;
        try {
            // 得到所有定义字段
            Field[] allFields = clazz.getDeclaredFields();
            List<Field> fields = new ArrayList<Field>();
            // 得到所有field并存放到一个list中.
            for (Field field : allFields) {
                if (field.isAnnotationPresent(Excel.class)) {
                    fields.add(field);
                }
            }

            // 产生工作薄对象
            workbook = new HSSFWorkbook();
            // excel2003中每个sheet中最多有65536行
            int sheetSize = 65536;
            // 取出一共有多少个sheet.
            double sheetNo = Math.ceil(list.size() / sheetSize);
            for (int index = 0; index <= sheetNo; index++) {
                // 产生工作表对象
                HSSFSheet sheet = workbook.createSheet();
                if (sheetNo == 0) {
                    workbook.setSheetName(index, sheetName);
                } else {
                    // 设置工作表的名称.
                    workbook.setSheetName(index, sheetName + index);
                }
                HSSFRow row;
                HSSFCell cell; // 产生单元格

                // 产生一行
                row = sheet.createRow(0);
                // 写入各个字段的列头名称
                for (int i = 0; i < fields.size(); i++) {
                    Field field = fields.get(i);
                    Excel attr = field.getAnnotation(Excel.class);
                    // 创建列
                    cell = row.createCell(i);
                    // 设置列中写入内容为String类型
                    cell.setCellType(CellType.STRING);
                    HSSFCellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    if (attr.name().indexOf("注：") >= 0) {
                        HSSFFont font = workbook.createFont();
                        font.setColor(HSSFFont.COLOR_RED);
                        cellStyle.setFont(font);
                        cellStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());
                        sheet.setColumnWidth(i, 6000);
                    } else {
                        HSSFFont font = workbook.createFont();
                        // 粗体显示
                        font.setBold(true);
                        // 选择需要用到的字体格式
                        cellStyle.setFont(font);
                        cellStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());
                        // 设置列宽
                        sheet.setColumnWidth(i, 3766);
                    }
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cellStyle.setWrapText(true);
                    cell.setCellStyle(cellStyle);

                    // 写入列名
                    cell.setCellValue(attr.name());

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

                int startNo = index * sheetSize;
                int endNo = Math.min(startNo + sheetSize, list.size());
                // 写入各条记录,每条记录对应excel表中的一行
                HSSFCellStyle cs = workbook.createCellStyle();
                cs.setAlignment(HorizontalAlignment.CENTER);
                cs.setVerticalAlignment(VerticalAlignment.CENTER);
                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 field = fields.get(j);
                        // 设置实体类私有属性可访问
                        field.setAccessible(true);
                        Excel attr = field.getAnnotation(Excel.class);
                        try {
                            // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
                            if (attr.isExport()) {
                                // 创建cell
                                cell = row.createCell(j);
                                cell.setCellType(CellType.NUMERIC);
                                try {

                                    if (vo == null) {
                                        // 如果数据存在就填入,不存在填入空格.
                                        cell.setCellValue("");
                                    } else {
                                        if (StringUtils.isNotBlank(attr.dictType())) {
                                            cell.setCellValue(field.get(vo) == null ? "" : DictUtils.getDictName(field.get(vo).toString(), attr.dictType(), ""));
                                        } else {
                                            Object val = field.get(vo);
                                            // 如果数据存在就填入,不存在填入空格.
                                            String cellFormatString = "@";
                                            if (val instanceof String) {
                                                cell.setCellValue((String) val);
                                                cell.setCellType(CellType.STRING);
                                            } else if (val instanceof Integer) {
                                                cell.setCellValue((Integer) val);
                                                cellFormatString = "0";
                                            } else if (val instanceof Long) {
                                                cell.setCellValue((Long) val);
                                                cellFormatString = "0";
                                            } else if (val instanceof Double) {
                                                cell.setCellValue((Double) val);
                                                cellFormatString = "0.00";
                                            } else if (val instanceof Float) {
                                                cell.setCellValue((Float) val);
                                                cellFormatString = "0.00";
                                            } else if (val instanceof Date) {
                                                cell.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd HH:mm", (Date) val));
                                                cellFormatString = "yyyy-MM-dd HH:mm";
                                                cell.setCellType(CellType.STRING);
                                            } else {
                                                cell.setCellType(CellType.STRING);
                                                cell.setCellValue(field.get(vo) == null ? "" : String.valueOf(field.get(vo)));
                                            }
                                            cs.setDataFormat(workbook.createDataFormat().getFormat(cellFormatString));
                                        }
                                    }

                                    cell.setCellStyle(cs);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }

                            }
                        } catch (Exception e) {
                            log.error("导出Excel失败{}", e.getMessage());
                        }
                    }
                }
            }
            String filename = encodingFilename(sheetName);
            out = new FileOutputStream(getAbsoluteFile(filename));
            workbook.write(out);
            return AjaxResult.success(filename);
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
            return AjaxResult.error("导出Excel失败，请联系网站管理员！");
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    public void exportExcel(List<T> list, String sheetName, HttpServletResponse response) {
        OutputStream out = null;
        HSSFWorkbook workbook = null;
        try {
            // 得到所有定义字段
            Field[] allFields = clazz.getDeclaredFields();
            List<Field> fields = new ArrayList<Field>();
            // 得到所有field并存放到一个list中.
            for (Field field : allFields) {
                if (field.isAnnotationPresent(Excel.class)) {
                    fields.add(field);
                }
            }

            // 产生工作薄对象
            workbook = new HSSFWorkbook();
            // excel2003中每个sheet中最多有65536行
            int sheetSize = 65536;
            // 取出一共有多少个sheet.
            double sheetNo = Math.ceil(list.size() / sheetSize);
            for (int index = 0; index <= sheetNo; index++) {
                // 产生工作表对象
                HSSFSheet sheet = workbook.createSheet();
                if (sheetNo == 0) {
                    workbook.setSheetName(index, sheetName);
                } else {
                    // 设置工作表的名称.
                    workbook.setSheetName(index, sheetName + index);
                }
                HSSFRow row;
                HSSFCell cell; // 产生单元格

                // 产生一行
                row = sheet.createRow(0);
                // 写入各个字段的列头名称
                for (int i = 0; i < fields.size(); i++) {
                    Field field = fields.get(i);
                    Excel attr = field.getAnnotation(Excel.class);
                    // 创建列
                    cell = row.createCell(i);
                    // 设置列中写入内容为String类型
                    cell.setCellType(CellType.STRING);
                    HSSFCellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    if (attr.name().indexOf("注：") >= 0) {
                        HSSFFont font = workbook.createFont();
                        font.setColor(HSSFFont.COLOR_RED);
                        cellStyle.setFont(font);
                        cellStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());
                        sheet.setColumnWidth(i, 6000);
                    } else {
                        HSSFFont font = workbook.createFont();
                        // 粗体显示
                        font.setBold(true);
                        // 选择需要用到的字体格式
                        cellStyle.setFont(font);
                        cellStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());
                        // 设置列宽
                        sheet.setColumnWidth(i, 3766);
                    }
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cellStyle.setWrapText(true);
                    cell.setCellStyle(cellStyle);

                    // 写入列名
                    cell.setCellValue(attr.name());

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

                int startNo = index * sheetSize;
                int endNo = Math.min(startNo + sheetSize, list.size());
                // 写入各条记录,每条记录对应excel表中的一行
                HSSFCellStyle cs = workbook.createCellStyle();
                cs.setAlignment(HorizontalAlignment.CENTER);
                cs.setVerticalAlignment(VerticalAlignment.CENTER);
                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 field = fields.get(j);
                        // 设置实体类私有属性可访问
                        field.setAccessible(true);
                        Excel attr = field.getAnnotation(Excel.class);
                        try {
                            // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
                            if (attr.isExport()) {
                                // 创建cell
                                cell = row.createCell(j);
                                cell.setCellType(CellType.NUMERIC);
                                try {

                                    if (vo == null) {
                                        // 如果数据存在就填入,不存在填入空格.
                                        cell.setCellValue("");
                                    } else {
                                        if (StringUtils.isNotBlank(attr.dictType())) {
                                            cell.setCellValue(field.get(vo) == null ? "" : DictUtils.getDictName(field.get(vo).toString(), attr.dictType(), ""));
                                        } else {
                                            Object val = field.get(vo);
                                            // 如果数据存在就填入,不存在填入空格.
                                            String cellFormatString = "@";
                                            if (val instanceof String) {
                                                cell.setCellValue((String) val);
                                                cell.setCellType(CellType.STRING);
                                            } else if (val instanceof Integer) {
                                                cell.setCellValue((Integer) val);
                                                cellFormatString = "0";
                                            } else if (val instanceof Long) {
                                                cell.setCellValue((Long) val);
                                                cellFormatString = "0";
                                            } else if (val instanceof Double) {
                                                cell.setCellValue((Double) val);
                                                cellFormatString = "0.00";
                                            } else if (val instanceof Float) {
                                                cell.setCellValue((Float) val);
                                                cellFormatString = "0.00";
                                            } else if (val instanceof Date) {
                                                cell.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd HH:mm", (Date) val));
                                                cellFormatString = "yyyy-MM-dd HH:mm";
                                                cell.setCellType(CellType.STRING);
                                            } else {
                                                cell.setCellType(CellType.STRING);
                                                cell.setCellValue(field.get(vo) == null ? "" : String.valueOf(field.get(vo)));
                                            }
                                            cs.setDataFormat(workbook.createDataFormat().getFormat(cellFormatString));
                                        }
                                    }

                                    cell.setCellStyle(cs);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }

                            }
                        } catch (Exception e) {
                            log.error("导出Excel失败{}", e.getMessage());
                        }
                    }
                }
            }
            String filename = encodingFilename(sheetName);
            filename = URLEncoder.encode(filename,"UTF-8");
            out = new FileOutputStream(getAbsoluteFile(filename));
            response.addHeader("Content-Disposition", "attachment;filename=" + filename);
            //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("multipart/form-data");
            out = response.getOutputStream();
            workbook.write(out);
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }

    /**
     * 设置单元格上提示
     *
     * @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 dataValidationView = new HSSFDataValidation(regions, constraint);
        dataValidationView.createPromptBox(promptTitle, promptContent);
        sheet.addValidationData(dataValidationView);
        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 dataValidationList = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(dataValidationList);
        return sheet;
    }

    /**
     * 编码文件名
     */
    public String encodingFilename(String filename) {
        return filename + ".xls";
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public String getAbsoluteFile(String filename) {
        String downloadPath = SystemConfig.getProfile() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }


}

 

VO 实体类： @Excel(name = " ")//标注要写在的字段

package cn.cmodes.project.module.scholar.domain;

import cn.cmodes.framework.aspectj.lang.annotation.Excel;
import cn.cmodes.framework.web.domain.BaseEntity;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

/**
 * 学者（学人）表 t_scholar
 *
 * @author dqj
 * @date 2019-02-25
 */
@Data
@EqualsAndHashCode(callSuper = true)
@NoArgsConstructor
public class Scholar extends BaseEntity {
    private static final long serialVersionUID = 1L;

    /**
     * 主键
     */
    private String id;
    /**
     * 创建时间
     */
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date createTime;
    /**
     * 更新时间
     */
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date updateTime;
    /**
     * 学人姓名
     */
    @Excel(name = "学人姓名")
    private String title;
    /**
     * 首字母
     */
    @Excel(name = "首字母")
    private String initial;
    /**
     * 性别
     */
    @Excel(name = "性别")
    private String gender;
    /**
     * 国籍
     */
    @Excel(name = "国籍")
    private String nationality;
    /**
     * 民族
     */
    @Excel(name = "民族")
    private String nation;
    /**
     * 出生年月
     */
    @Excel(name = "出生年月")
    private String birth;
    /**
     * 毕业院校
     */
    @Excel(name = "毕业院校")
    private String university;
    /**
     * 工作单位
     */
    @Excel(name = "工作单位")
    private String jobAddress;
    /**
     * 照片
     */
    @Excel(name = "照片")
    private String photo;
    /**
     * 简历
     */
    @Excel(name = "简历")
    private String resume;
    /**
     * 任职情况
     */
    @Excel(name = "任职情况")
    private String positionStatus;
    /**
     * 职称
     */
    @Excel(name = "职称")
    private String position;
    /**
     * 研究领域
     */
    @Excel(name = "研究领域")
    private String researchField;
    /**
     * 承担课题
     */
    @Excel(name = "承担课题")
    private String commitment;
    /**
     * 个人简介
     */
    @Excel(name = "个人简介")
    private String personalProfile;
    /**
     * 主要著述
     */
    @Excel(name = "主要著述")
    private String mainWork;
    /**
     * 影音资料
     */
    @Excel(name = "影音资料")
    private String videoAudio;
    /**
     * 资源类型
     */
    private Integer resourceType = 22;
    /**
     * 附加1
     */
    private String pro1;
    /**
     * 附加2
     */
    private String pro2;
    /**
     * 附加3
     */
    private String pro3;

    /************业务字段***************/
    private String publics = "公开";
    public boolean collectionStatus;  // 收藏状态

    @Override
    public String getDocumentId() {
        return this.resourceType + "-" + this.getId();
    }

}

 

使用：

/**
     * 模板下载
     */
    @GetMapping("/template")
    @ResponseBody
    public void template(HttpServletResponse response, String resourceType) throws IOException {
        ExcelUtil<Scholar> excelUtil = new ExcelUtil<Scholar>(Scholar.class);
        List<Scholar> list = new ArrayList<Scholar>();
        excelUtil.exportExcel(list, "学人模板", response);
    }

    /**
     * 导入
     */
    @PostMapping("/importExcel")
    @ResponseBody
    public void importExcel(HttpServletResponse response, HttpServletRequest request,@RequestParam MultipartFile file) throws IOException {
        //FileInputStream fileInputStream = new FileInputStream("F://a.xlsx");
        InputStream inputStream = file.getInputStream();
        ExcelUtil<Scholar> excelUtil = new ExcelUtil<Scholar>(Scholar.class);
        try {
            List<Scholar> scholar = excelUtil.importExcel(inputStream);
            if (!scholar.isEmpty()) {
                for (Scholar scholar1  :scholar){
                    int fileId = fileService.insertFile(scholar1.getPhoto());
                    scholar1.setResourceType(22);
                    scholar1.setPublics("公开");
                    scholarService.insertScholar(scholar1);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

 

前台：

<!DOCTYPE html>
<html lang="zh" xmlns:th="http://www.thymeleaf.org">
<meta charset="utf-8">
<head th:include="include :: header"></head>
<body class="white-bg">
<div class="wrapper wrapper-content animated fadeInRight ibox-content">
    <form class="form-horizontal m" id="form-excel" method="post" enctype="multipart/form-data">
        <input type="hidden" name="importExcelUrl" id="importExcelUrl" th:value="*{importExcelUrl}">

        <div class="form-group">
            <label class="col-sm-4 control-label ">批量导入数据：</label>
            <div class="col-sm-8">
                <input type="file" id="file" name="file" class="btn btn-success btn-rounded btn-sm"/>
            </div>
        </div>
        <div>
            <label class="col-sm-4 control-label ">模 板 下 载：</label>
            <div class="col-sm-8">
                <a th:href="*{templateUrl}" class="btn btn-success btn-rounded btn-sm">导入模板</a>
            </div>
        </div>
    </form>
</div>

<div th:include="include :: footer"></div>
<script type="text/javascript">
    $("#form-user-resetPwd").validate({
        rules: {}
    });

    function submitHandler() {
        var form = document.getElementById('form-excel');
        form .action = $('#importExcelUrl').val();
        form.submit();
    }
</script>
</body>

</html>

 

跳转的路径：

<!DOCTYPE HTML>
<html lang="zh" xmlns:th="http://www.thymeleaf.org" xmlns:shiro="http://www.pollix.at/thymeleaf/shiro">
<meta charset="utf-8">
<head th:include="include :: header"></head>
<body class="gray-bg">
<div class="container-div">

    <div class="row">
        <div class="col-sm-12 search-collapse">
            <form id="notice-form">
                <div class="select-list">
                    <ul>
                        <li>
                            学者姓名：<input type="title" name="title" id="title"/>
                        </li>
                        <li>
                            <a class="btn btn-primary btn-rounded btn-sm" onclick="$.table.search()"><i
                                    class="fa fa-search"></i>&nbsp;搜索</a>
                            <a class="btn btn-warning btn-rounded btn-sm" onclick="$.form.reset()"><i
                                    class="fa fa-refresh"></i>&nbsp;重置</a>
                            <a href="#" class="btn btn-success btn-rounded btn-sm" onclick="$.operate.excel()"><i
                                    class="fa fa-plus"></i>&nbsp;导入</a>
                        </li>
                    </ul>
                </div>
            </form>
        </div>


        <div class="btn-group-sm hidden-xs" id="toolbar" role="group">
            <a class="btn btn-success" onclick="$.operate.add(0)" shiro:hasPermission="module:scholar:add">
                <i class="fa fa-plus"></i> 添加
            </a>
            <a class="btn btn-primary btn-edit disabled" onclick="$.operate.edit()"
               shiro:hasPermission="module:scholar:edit">
                <i class="fa fa-edit"></i> 修改
            </a>
            <a class="btn btn-danger btn-del btn-del disabled" onclick="$.operate.removeAll()"
               shiro:hasPermission="module:scholar:remove">
                <i class="fa fa-remove"></i> 删除
            </a>
        </div>

        <div class="col-sm-12 select-table table-striped ">
            <table id="bootstrap-table" data-mobile-responsive="true"></table>
        </div>
    </div>
</div>
<div th:include="include :: footer"></div>
<script th:inline="javascript">
    var editFlag = [[${@permission.hasPermi('module:scholar:edit')}]];
    var removeFlag = [[${@permission.hasPermi('module:scholar:remove')}]];
    var prefix = ctx + "module/scholar";

    $(function () {
        var options = {
            url: prefix + "/list",
            createUrl: prefix + "/add",
            updateUrl: prefix + "/edit/{id}",
            removeUrl: prefix + "/remove",
            templateUrl: prefix + "/template?resourceType=" + 1,
            importExcelUrl: prefix + "/importExcel",
            modalName: "学者（学人）",
            uniqueId: "id",
            search: false,
            columns: [
                {
                    checkbox: true
                },
                {
                    title: '序号',
                    align: "center",
                    width: 40,
                    formatter: function (value, row, index) {
                        var table = $('#bootstrap-table');
                        var pageSize = table.bootstrapTable('getOptions').pageSize;
                        //获取当前是第几页
                        var pageNumber = table.bootstrapTable('getOptions').pageNumber;
                        //返回序号，注意index是从0开始的，所以要加上1
                        return pageSize * (pageNumber - 1) + index + 1;
                    }
                },
                /*{
                    field : 'id',
                    title : '主键'
                },*/
                /*{
                    field : 'createTime',
                    title : '创建时间'
                },
                {
                    field : 'updateTime',
                    title : '更新时间'
                },*/
                {
                    field: 'title',
                    title: '学人姓名',
                    formatter: function (value, row, index) {
                        if(value){
                            return value.length > 25 ? value.substr(0, 25) + "..." : value;
                        }

                    }
                },
                {
                    field: 'gender',
                    title: '性别',
                    width: '200px'
                },
                {
                    field: 'nationality',
                    title: '国籍',
                    formatter: function (value, row, index) {
                        if(value){
                            return value.length > 25 ? value.substr(0, 25) + "..." : value;
                        }
                    }
                },
                /*{
                    field : 'nation',
                    title : '民族'
                },
                {
                    field : 'birth',
                    title : '出生年月'
                },
                {
                    field : 'university',
                    title : '毕业院校'
                },*/
                {
                    field: 'jobAddress',
                    title: '工作单位',
                    formatter: function (value, row, index) {
                        if(value){
                            return value.length > 25 ? value.substr(0, 25) + "..." : value;
                        }
                    }
                },
                /* {
                     field : 'photo',
                     title : '照片'
                 },
                 {
                     field : 'resume',
                     title : '简历'
                 },
                 {
                     field : 'positionStatus',
                     title : '任职情况'
                 },
                 {
                     field : 'position',
                     title : '职称'
                 },
                 {
                     field : 'researchField',
                     title : '研究领域'
                 },
                 {
                     field : 'commitment',
                     title : '承担课题'
                 },
                 {
                     field : 'personalProfile',
                     title : '个人简介'
                 },
                 {
                     field : 'mainWork',
                     title : '主要著述'
                 },
                 {
                     field : 'videoAudio',
                     title : '影音资料'
                 },*/
                /*{
                    field : 'resourceType',
                    title : '资源类型'
                },*/
                {
                    field: 'pro1',
                    title: '首字母'
                },/*
                    {
                        field : 'pro2',
                        title : '附加2'
                    },
                    {
                        field : 'pro3',
                        title : '附加3'
                    },*/
                {
                    title: '操作',
                    align: 'center',
                    formatter: function (value, row, index) {
                        var actions = [];
                        actions.push('<a class="btn btn-success btn-xs ' + editFlag + '" href="#" onclick="$.operate.edit(\'' + row.id + '\')"><i class="fa fa-edit"></i>修改</a> ');
                        actions.push('<a class="btn btn-danger btn-xs ' + removeFlag + '" href="#" onclick="$.operate.remove(\'' + row.id + '\')"><i class="fa fa-remove"></i>删除</a>');
                        return actions.join('');
                    }
                }]
        };
        $.table.init(options);
    });
</script>
</body>
</html>

 

js:

  // excel 导入
            excel: function () {
                var url = ctx + "/common/excel?templateUrl=" + $.table._option.templateUrl + "&importExcelUrl=" + $.table._option.importExcelUrl;
                $.modal.open($.table._option.modalName + "导入", url, '800', '300');
            }