Excel操作

最新推荐文章于 2024-01-28 21:23:22 发布

Bruk.Liu

最新推荐文章于 2024-01-28 21:23:22 发布

阅读量188

点赞数

CC 4.0 BY-SA版权

分类专栏： java 文章标签： excel servlet java

本文链接：https://blog.csdn.net/weixin_46054799/article/details/128537694

java 专栏收录该内容

50 篇文章

订阅专栏

该文章介绍了如何利用阿里巴巴的EasyExcel工具和ApachePOI库进行Excel文件的读取和导出。提供了导入时通过回调接口处理数据的方法以及导出时的样式设置。示例代码展示了如何处理MultipartFile对象，以及在FacilityInfoController中导入网络设备数据的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 <!-- excel操作 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

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

工具类

package com.snp.extranet.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.snp.extranet.listener.ExcleDataListener;
import com.snp.extranet.listener.ExcleFinshCallBack;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;

/**
 * @author liujiwei
 * @date 2023/1/3
 * @package com.snp.extranet.util
 * @describe 备注 : EasyExcel工具类
 */
public class EasyExcelUtils {


    /**
     * excel读取
     * @param file excel文件
     * @param head 列名 使用@ExcelProperty("姓名")对字段进行注释,对应exxcel表头
     * @param callBack 回调 导入时传入定义好的回调接口，excel数据解析完毕之后监听器将数据传入回调函数
     *                这样我们调用工具类时可以通过回调函数获取导入的数据，如果数据量过大可根据实际情况进行分配入库
     */
    public static void importExcel(MultipartFile file, Class head, ExcleFinshCallBack callBack) {
        try {
            EasyExcel.read(file.getInputStream(), head, new ExcleDataListener(callBack)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出数据
     * @param head 表头
     * @param excelname 表名
     * @param data 数据
     */
    public static void exportExcel(Class head, String excelname, List data) {
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        //这里URLEncoder.encode可以防止中文乱码
        try {
            //表格头部样式
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置头字体
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)13);
            headWriteFont.setBold(true);
            headWriteCellStyle.setWriteFont(headWriteFont);
            //设置头居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //表格内容样式
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            String fileName = URLEncoder.encode(excelname, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), head).registerWriteHandler(horizontalCellStyleStrategy).sheet("Sheet1").doWrite(data);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

使用导入

package com.snp.extranet.core.unit.facilityInfo.controller;


import com.snp.extranet.common.vo.Result;
import com.snp.extranet.core.unit.facilityInfo.bean.FacilityInfoDO;
import com.snp.extranet.enums.FacilityTypeEnum;
import com.snp.extranet.listener.ExcleFinshCallBack;
import com.snp.extranet.util.EasyExcelUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

/**
 * <p>
 * 设备信息表 前端控制器
 * </p>
 *
 * @author liujiwei
 * @since 2022-12-29
 */
@Api(tags = "系统详情-设备信息相关")
@RestController
@RequestMapping("/facilityInfo")
public class FacilityInfoController {


    /**
     * 导入网络设备
     * @param file 文件
     * @return Result
     */
    @ApiOperation(value = "导入网络设备")
    @GetMapping(value = "/importNetwork")
    public Result importNetworkExcel(@RequestParam("file") MultipartFile file, @RequestParam("systemId") String systemId){
        EasyExcelUtils.importExcel(file, FacilityInfoDO.class, result -> {
            result.stream().forEach(t->{
                //数据处理
                FacilityInfoDO facilityInfoDO = (FacilityInfoDO)t;
                facilityInfoDO.setFacilityType(FacilityTypeEnum.NETWORK.name());
                facilityInfoDO.setSystemId(systemId);
                System.out.println(facilityInfoDO);
            });
        });

        return Result.ok();
    }

}

文件头字段类中使用@ExcelProperty("姓名")进行注释