POI导入Excel、JAVA根据POI封装Excel的导入

说明

使用前需要明白导入参数的含义。

代码

代码从接口调用开始说明
poi版本

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-math3</artifactId>
            <version>3.6.1</version>
        </dependency>
在这里插入代码片

 @PostMapping("/load")
    @ResponseBody
    public Map importTemplate(@RequestParam(value = "file") MultipartFile file) throws Exception {
    	//参数说明看工具类说明
        List<ImportExcelDTO> importExcelDTOS = ExcelUtils.importExcel(file, ImportExcelDTO.class, 2, 0, 1, 1);
       
    }

这个是业务相关的实体类：实体类的字段顺序一定要和Excel的字段顺序一样

@Data
public class ImportExcelDTO {
    /**
     * 地区
     */
    private String area;

    /**
     * 货币名称
     */
    private String name;

    /**
     * 货币编码
     */
    private String code;

    /**
     * 货币编号
     */
    private Integer num;
    
}

以下为封装需要的代码

public class ExcleVO {
    /**
     * 字段名称
     */
    private List<String> colums;

    /**
     * 表名
     */
    private String name;

    /**
     * 记录在excle中的列数
     */
    private Map<String, Integer> position;

    public List<String> getColums() {
        return colums;
    }

    public void setColums(List<String> colums) {
        this.colums = colums;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Map<String, Integer> getPosition() {
        return position;
    }

    public void setPosition(Map<String, Integer> position) {
        this.position = position;
    }
}

封装好的ExcelUtils 导出工具类

package com.owinfo.mpw.cash.util;

import com.alibaba.druid.util.StringUtils;

import com.owinfo.mpw.cash.service.entity.vo.ExcleVO;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.annotation.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelUtils {


    /**
     * 导入Excel
     * 样例：List<DeclarationElementExcelParam> params = ExcelUtils.importExcel(file, DeclarationElementExcelParam.class, NumberConstant.THREE);
     *
     * @param file       文件；文件格式要求，“第一行”为表头，从“第二行”为正式数据，并且“第一列”必须是序号
     * @param clazz      接收解析Excel内容的实体；实体要求，实体的“字段顺序”必须和Excel“表头的顺序”一样;
     *                   实体的字段类型，只支持String，Integer，Double，Date
     * @param index      表示到实体的第几个字段结束。例：Excel表中，除去序号那一列，真实的表头有10个，那么在实体中
     *                   就有10个字段来接收，但是实体中还会有常用字段之类的，所以要指定字段的数量，这里index就传10。
     * @param sheetIndex 表示获取第几个sheet的数据
     * @param rowIndex   表示从第几行开始读数据
     * @param cell       表示从第几列读
     * @return 返回Excel中的数据
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz, int index, int sheetIndex, int rowIndex, int cell) throws Exception {
        List<T> params = new ArrayList<>();
        String fileName = file.getOriginalFilename();
        if (ExcelUtils.checkExcel(file)) {
            throw new Exception("请上传Excel格式");
        }
        boolean isExcel2003 = true;
        String match = "^.+\\.(?i)(xlsx)$";
        if (fileName.matches(match)) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(sheetIndex);
        if (sheet == null) {
            throw new Exception("文件为空");
        }
        for (int r = rowIndex; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);

            if (isRowEmpty(row)) {
                continue;
            }
            //接收参数对象
            T t = clazz.newInstance();
            //NumberConstant.TWENTY_FOUR 表示循环到对象里面的第几个参数
            ExcelUtils.getRowValue(t, row, index, cell);
            params.add(t);
        }
        return params;
    }


    /**
     * 判断行是否为空
     *
     * @param row 行对象
     * @return 判断结果
     */
    private static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 导出Excel
     * 
     * @param datas     导出的数据
     * @param selColumns  动态字段集合，不需要动态字段传null
     * @param clazz    封装导出数据的实体，就是T是什么，这里就传他的class对象
     * @param response 下载响应
     */
    public static <T> void downExcel(List<T> datas, Class<?> clazz, List<String> selColumns, HttpServletResponse response) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        ExcleVO excleVO = getNameAndColumn(clazz, selColumns);
        SimpleDateFormat formatter1 = new SimpleDateFormat("yyyyMMddHHmmss");
        try {
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excleVO.getName() + formatter1.format(new Date()) + ".xlsx", "utf-8"));
            //======第一个sheet=========
            //表格1的名称
            String sheetName1 = excleVO.getName();
            XSSFSheet sheet1 = wb.createSheet(sheetName1);
            XSSFDrawing patriarch = sheet1.createDrawingPatriarch();
            sheet1.setDefaultColumnWidth(20);
            sheet1.setDefaultRowHeight((short) (45 * 10));

            String[] titles = excleVO.getColums().toArray(new String[excleVO.getColums().size()]);
            //定义表头
            //返回写到第几行了
            int rowIndex2 = ExcelUtils.writeTitlesToExcel1(wb, sheet1, titles);
            //写正式内容
            writeRowsToExcel1(wb, sheet1, datas, rowIndex2, excleVO.getPosition(), patriarch);
            //设置宽度
            ExcelUtils.autoSizeColumns(sheet1, titles.length + 1);
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取单元格内容
     *
     * @param obj   生成的对象
     * @param row   航对象
     * @param index 表示赋值到第几个字段结束
     */
    public static <T> void getRowValue(T obj, Row row, int index, int cell) throws Exception {
        Class<?> clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < index; i++) {
            Field field = fields[i];
            field.setAccessible(true);
            Class<?> type = field.getType();
            Method m = clazz.getMethod("set" + BaseUtils.upperCase(field.getName()), type);
            //给该方法设置值
            if (type == Date.class) {
                //处理日期
                Cell cell1 = row.getCell(i + cell);
                Date d = null;
                if (cell1 != null && !StringUtils.isEmpty(cell1.toString())) {
                    d = cell1.getDateCellValue();
                }
                if (d != null) {
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    String res = formater.format(d);
                    m.invoke(obj, formater.parse(res));
                }
            } else if (type == Integer.class) {
                String s = setFiled(row, i + cell);
                if (!StringUtils.isEmpty(s)) {
                    m.invoke(obj, Integer.valueOf(s));
                }
            } else if (type == Double.class) {
                String s = setFiled(row, i + cell);
                if (!StringUtils.isEmpty(s)) {
                    m.invoke(obj, Double.valueOf(s));
                }
            } else if (type == BigDecimal.class) {
                String s = setFiled(row, i + cell);
                if (!StringUtils.isEmpty(s)) {
                    m.invoke(obj, new BigDecimal(s));
                }
            } else {
                String s = setFiled(row, i + cell);
                m.invoke(obj, s);
            }
        }
    }

    /**
     * 获取单元格内容
     *
     * @param i   第几列
     * @param row 航对象
     * @return 获取结果
     */
    private static String setFiled(Row row, int i) throws Exception {
        if (row.getCell(i) != null) {
            row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
            return row.getCell(i).getStringCellValue().trim();
        }
        return "";
    }

    /**
     * 讲对象写入单元格
     *
     * @param g       填入的数据
     * @param dataRow 航对象
     */
    private static <T> void createExcel(Row dataRow, T g, XSSFCellStyle dataStyle, XSSFCellStyle dataStyle1, Map<String, Integer> position, XSSFWorkbook wb, XSSFDrawing patriarch) throws Exception {
        Class<?> clazz = g.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            //设置是否允许访问，不是修改原来的访问权限修饰词。
            if (position.containsKey(field.getName())) {
                field.setAccessible(true);
                Cell cell1 = dataRow.createCell(1 + position.get(field.getName()));
                cell1.setCellValue("" + (field.get(g) == null ? "" : "" + field.get(g)));
                if (field.getType() == String.class && !field.getName().equals("filePath")) {
                    cell1.setCellStyle(dataStyle1);
                } else {
                    cell1.setCellStyle(dataStyle);
                }
            }
        }
    }

    /**
     * 判断是否是excel
     *
     * @param file 文件
     * @return 获取结果
     */
    public static boolean checkExcel(@RequestBody MultipartFile file) {
        String fileName = file.getOriginalFilename();
        String match1 = "^.+\\.(?i)(xls)$";
        String match2 = "^.+\\.(?i)(xlsx)$";
        return !fileName.matches(match1) && !fileName.matches(match2);
    }

    /**
     * sheet1写正式数据
     *
     * @param wb 表信息
     */
    private static XSSFCellStyle getDataStyle(XSSFWorkbook wb) {
        Font dataFont = wb.createFont();
        dataFont.setFontName("宋体");
        dataFont.setColor(IndexedColors.BLACK.index);
        dataFont.setFontHeightInPoints((short) 16);
        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        dataStyle.setFont(dataFont);
        dataStyle.setWrapText(true);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));
        //设置标题单元格类型
        //下边框
        dataStyle.setBorderBottom(CellStyle.BORDER_THIN);
        //左边框
        dataStyle.setBorderLeft(CellStyle.BORDER_THIN);
        //上边框
        dataStyle.setBorderTop(CellStyle.BORDER_THIN);
        //右边框
        dataStyle.setBorderRight(CellStyle.BORDER_THIN);

        return dataStyle;
    }

    /**
     * sheet1 设置表头，包括格式和背景颜色
     *
     * @param wb    表信息
     * @param sheet 表信息
     * @param heads 表头信息
     * @return 返回到第几行
     */
    private static int writeTitlesToExcel1(XSSFWorkbook wb, Sheet sheet, String[] heads) {
        //字体样式
        Font dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        dataFont.setColor(IndexedColors.BLACK.index);
        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        dataStyle.setFont(dataFont);
        dataStyle.setWrapText(true);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));

        XSSFCellStyle black = getBlack(wb);
        //创建第一行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < heads.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(heads[i]);
            cell.setCellStyle(black);
        }
        //返回用了几行
        return 1;
    }

    /**
     * sheet1 设置表头，包括格式和背景颜色
     *
     * @param style  表信息
     * @param border 表信息
     * @param color  颜色
     */
    public static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }

    /**
     * sheet1 设置颜色
     *
     * @param wb 表信息
     * @return XSSFCellStyle
     */
    public static XSSFCellStyle getBlack(XSSFWorkbook wb) {
        //字体样式
        Font titleFont = wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setItalic(false);
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setColor(IndexedColors.BLACK.index);
        //表头样式
        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(133, 5, 11)));
        return titleStyle;
    }

    /**
     * sheet1 标题居中
     *
     * @param wb 表信息
     * @return XSSFCellStyle
     */
    public static XSSFCellStyle getBlackTitle(XSSFWorkbook wb) {
        //字体样式
        Font titleFont = wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setItalic(false);
        titleFont.setFontHeightInPoints((short) 22);
        titleFont.setColor(IndexedColors.BLACK.index);
        //表头样式
        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        return titleStyle;
    }


    /**
     * sheet1 标题下面的信息格式
     *
     * @param wb 表信息
     * @return XSSFCellStyle
     */
    public static XSSFCellStyle getBlack2(XSSFWorkbook wb) {
        //字体样式
        Font titleFont = wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setItalic(false);
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setColor(IndexedColors.BLACK.index);
        //表头样式
        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        return titleStyle;
    }

    /**
     * sheet1 设置表头，包括格式和背景颜色
     *
     * @param sheet        表信息
     * @param columnNumber 颜色号
     */
    public static void autoSizeColumns(Sheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = (sheet.getColumnWidth(i) + 100);
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    @Target({ElementType.FIELD})
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    public @interface Column {
        String name() default "";
    }

    @Target({ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    public @interface ExcelName {
        String name() default "";
    }

    public static ExcleVO getNameAndColumn(Class<?> clazz, List<String> selColumns) {
        ExcleVO excleVO = new ExcleVO();
        List<String> heads = new ArrayList<>();
        heads.add("序号");
        Map<String, Integer> position = new HashMap<>(16);
        excleVO.setColums(heads);
        excleVO.setPosition(position);
        Class<ExcelName> excelNameClass = ExcelName.class;
        Class<Column> columnClass = Column.class;
        if (clazz.isAnnotationPresent(excelNameClass)) {
            //获取类上的名称
            ExcelName annotation = clazz.getAnnotation(excelNameClass);
            excleVO.setName(annotation.name());
        }
        Field[] declaredFields = clazz.getDeclaredFields();
        int i = 0;
        for (Field field : declaredFields) {
            //((selColumns==null || selColumns.size() == 0)?true:selColumns.contains(field.getName()))  这个判断意思是，如果selColumns为空时，就表示不是动态字段，不需要再做后续判断
            if (field.isAnnotationPresent(columnClass) && ((selColumns==null || selColumns.size() == 0)?true:selColumns.contains(field.getName()))) {
                Column annotation = field.getAnnotation(columnClass);
                heads.add(annotation.name());
                position.put(field.getName(), i);
                i++;
            }
        }
        return excleVO;
    }

    /**
     * sheet1写正式数据
     *
     * @param wb       表信息
     * @param sheet    表信息
     * @param datas     写入的数据
     * @param rowIndex 从第几行开始
     */
    public static <T> void writeRowsToExcel1(XSSFWorkbook wb, Sheet sheet, List<T> datas, int rowIndex, Map<String, Integer> position, XSSFDrawing patriarch) throws Exception {
        XSSFCellStyle dataStyle = ExcelUtils.getDataStyle(wb);
        XSSFCellStyle dataStyle1 = ExcelUtils.getDataStyle(wb);
        XSSFDataFormat dataFormat = wb.createDataFormat();
        dataStyle1.setDataFormat(dataFormat.getFormat("@"));
        for (int i = 0; i < datas.size(); i++) {
            T g = datas.get(i);
            Row dataRow = sheet.createRow(i + rowIndex);
            Cell cell0 = dataRow.createCell(0);
            cell0.setCellValue(i + 1);
            cell0.setCellStyle(dataStyle);
            ExcelUtils.createExcel(dataRow, g, dataStyle, dataStyle1, position, wb, patriarch);
        }
    }
}