excel上传/下载使用注解方式,非常实用的一个demo工程

最新推荐文章于 2024-08-01 18:45:00 发布

qq_42151769

最新推荐文章于 2024-08-01 18:45:00 发布

阅读量287

点赞数

分类专栏： Java 文章标签： excel导入导出注解

转载请注明原创

本文链接：https://blog.csdn.net/qq_42151769/article/details/88720476

版权

该博客介绍了一个通过注解方式处理Excel导入导出的实用示例工程。首先，在项目中引入了Apache POI依赖。接着，定义了ExcelField、ExcelCodeToString和ExcelFormat等注解，分别用于标记对象属性、枚举值转换和日期格式化。此外，还提供了一个Excel工具类以及通用工具类和对象复制工具类。最后，展示了如何在Controller中实现查询数据和Excel导入的测试方法。

摘要由CSDN通过智能技术生成

在pom添加poi依赖:

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

完整的依赖如下:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.hf</groupId>
    <artifactId>hantopy</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>hantopy</name>
    <description>汉字和拼音,简繁互转工具实现</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.github.stuxuhai/jpinyin -->
        <dependency>
            <groupId>com.github.stuxuhai</groupId>
            <artifactId>jpinyin</artifactId>
            <version>1.1.8</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.belerweb/pinyin4j -->
        <dependency>
            <groupId>com.belerweb</groupId>
            <artifactId>pinyin4j</artifactId>
            <version>2.5.1</version>
        </dependency>

        <!-- mysql驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.17</version>
        </dependency>
        <!--mybatis依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

        <!--poi start-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.12</version>
        </dependency>
        <!--poi start-->

        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

定义一些excel注解

ExcelField注解用来表明需要导出的对象的属性数据

package com.hf.hantopy.utils.excel;

import java.lang.annotation.*;

/**
 * @Description:
 * @Date: 2019/3/3
 * @Auther:
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelField {
    /**
     * 列名称
     *
     * @return String
     */
    String name() default "";
}

ExcelCodeToString:用来将一些一些数据对应的枚举值之间的互转

package com.hf.hantopy.utils.excel;

/**
 *  *code转换为对应的中文注释
 *  * @ExcelCodeToString(values={"0:失效","1:正常"})
 * @Date: 2019/3/21
 * @Auther: 
 */

import java.lang.annotation.*;

/**
 *
 * @ExcelCodeToString(values={"0:失效","1:正常"})
 * @author
 *
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelCodeToString {
    /**
     * code 对应的中文注释
     * @ExcelCodeToString(values={"0:失效","1:正常"})
     * @return String[]
     */
    String[] values() ;
}

ExcelFormat注解用来格式化日期的

package com.hf.hantopy.utils.excel;

/**
 * @Description:
 * @Date: 2019/3/21
 * @Auther: wm yu
 */

import java.lang.annotation.*;

/**
 * 列属性信息
 *
 *      支持Java对象数据类型：Date
 *      支持Excel的Cell类型为：String
 *
 * @author 
 * @date  2018年11月26日09:19:13
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelFormat {
    /**
     * 时间格式化，日期类型时生效
     *
     * @return String
     */
    String pattern() default "yyyy-MM-dd HH:mm:ss";
}

excel的工具类:

package com.hf.hantopy.utils.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.*;

/**
 * @Description: 用于导出数据
 * @Author 
 * @Date 2018年11月26日09:18:44
 */
public class ExcelUtil {

    public static int DEFAULT_COLOUMN_WIDTH = 17;


    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title            标题行
     * @param headerList       属性-列头
     * @param sheetDataListArr 数据集
     * @param colWidth         列宽 默认 至少17个字节
     * @param out              输出流
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     */
    public static void exportExcel(String title, List<String> headerList, List<?> sheetDataListArr,
                                   int colWidth, OutputStream out)
            throws IllegalArgumentException, IllegalAccessException {
        // 声明一个工作薄缓存
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        // 表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        header