POI操作EXCEL

.搭建环境

<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
</dependencies>

2.POI结构说明

HSSF提供读写Microsoft Excel XLS格式档案的功能。

XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。

HWPF提供读写Microsoft Word DOC格式档案的功能。

HSLF提供读写Microsoft PowerPoint格式档案的功能。

HDGF提供读Microsoft Visio格式档案的功能。

HPBF提供读Microsoft Publisher格式档案的功能。

HSMF提供读Microsoft Outlook格式档案的功能。

3.API介绍

4.基本操作

4.1 创建Excel

package cn.itcast.poi.test;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;

/**
 * 使用POI创建excel
 */
public class PoiTest01 {


    public static void main(String[] args) throws Exception {
        //1.创建工作簿  HSSFWorkbook -- 2003
        Workbook wb=new XSSFWorkbook(); //2007版本
        //2.创建表单sheet
        Sheet sheet=wb.createSheet("test");
        //3.文件流
        FileOutputStream pis=new FileOutputStream("E:\\excel\\poi\\test11.xlsx");
        //4.写入文件
        wb.write(pis);
        pis.close();
    }
}

4.2 创建单元格

package cn.itcast.poi.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

/**
 * 创建单元格写入内容
 */
public class PoiTest02 {


    public static void main(String[] args) throws Exception {
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb=new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet=wb.createSheet("test");
        //创建行对象  参数：索引（从0开始）
        Row row=sheet.createRow(2);
        //创建单元格对象  参数：索引（从0开始）
        Cell cell=row.createCell(2);
        //向单元格中写入内容
        cell.setCellValue("传智播客");
        //文件流
        FileOutputStream pis=new FileOutputStream("E:\\excel\\poi\\test1.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }
}

4.3 设置格式

package cn.itcast.poi.test;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

/**
 * 单元格样式处理
 */
public class PoiTest03 {


    public static void main(String[] args) throws Exception {
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb=new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet=wb.createSheet("test");
        //创建行对象  参数：索引（从0开始）
        Row row=sheet.createRow(2);
        //创建单元格对象  参数：索引（从0开始）
        Cell cell=row.createCell(2);
        //向单元格中写入内容
        cell.setCellValue("传智播客");

        //样式处理
        //创建样式对象
        CellStyle style=wb.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        //创建字体对象
        Font font=wb.createFont();
        font.setFontName("华文行楷"); //字体
        font.setFontHeightInPoints((short)28);//字号
        style.setFont(font);

        //行高和列宽
        row.setHeightInPoints(50);//行高
        //列宽的宽度  字符宽度
        sheet.setColumnWidth(2,31 * 256);//列宽

        //剧中显示
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        //向单元格设置样式
        cell.setCellStyle(style);

        //文件流
        FileOutputStream pis=new FileOutputStream("E:\\excel\\poi\\test2.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }
}

4.4 绘制图形

package cn.itcast.poi.test;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;

/**
 * 插入图片
 */
public class PoiTest04 {


    public static void main(String[] args) throws Exception {
        //创建工作簿  HSSFWorkbook -- 2003
        Workbook wb=new XSSFWorkbook(); //2007版本
        //创建表单sheet
        Sheet sheet=wb.createSheet("test");

        //读取图片流
        FileInputStream stream=new FileInputStream("E:\\excel\\poi\\logo.jpg");
        //转化二进制数组
        byte[] bytes=IOUtils.toByteArray(stream);
        stream.read(bytes);
        //向POI内存中添加一张图片，返回图片在图片集合中的索引
        int index=wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);//参数一：图片的二进制数据，参数二：图片类型
        //绘制图片工具类
        CreationHelper helper=wb.getCreationHelper();
        //创建一个绘图对象
        Drawing<?> patriarch=sheet.createDrawingPatriarch();
        //创建锚点，设置图片坐标
        ClientAnchor anchor=helper.createClientAnchor();
        anchor.setRow1(0);
        anchor.setCol1(0);
        //绘制图片
        Picture picture=patriarch.createPicture(anchor, index);//图片位置，图片的索引
        picture.resize();//自适应渲染图片

        //文件流
        FileOutputStream pis=new FileOutputStream("E:\\excel\\poi\\test3.xlsx");
        //写入文件
        wb.write(pis);
        pis.close();
    }
}

4.5 加载Excel

、前言

针对以excel的方式从数据库要导出百万条数据的需要，因此基于apache.poi封装一个分页导出excel文档的工具

2、具体的实现代码

package com.ljxy.score.excelHandler.handler;

import com.ljxy.score.excelHandler.annotation.DictMapping;
import com.ljxy.score.excelHandler.annotation.ExcelAlias;
import com.ljxy.score.excelHandler.entity.ParamEntity;
import com.ljxy.score.excelHandler.entity.ResultData;
import com.ljxy.score.util.SpringContextUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

/**
 * @ClassName: ExcelExportHandler
 * @Description: TODO
 * @Author: shenshixi
 * @Date 2023-06-29 18:58:22
 * @Version 1.0.0
 */
@Component
public class ExcelExportHandler<T> {

    @Value("${excelTmpPath}")
    private String excelTmpPath;

    public ResponseEntity<StreamingResponseBody> downloadExcel(Class<T> clazz, String methodName, ParamEntity paramEntity, String sheetName) throws Exception {
        //文件名
        String fileName=UUID.randomUUID().toString() + ".xlsx";
        //产生的临时文件全路径
        String tmpFilePath=excelTmpPath + fileName;
        File file=new File(excelTmpPath, fileName);

        //创建工作簿
        XSSFWorkbook workbook=new XSSFWorkbook();
        //创建工作表
        XSSFSheet sheet=workbook.createSheet(sheetName);
        //设置默认值
        int currentPageNum=paramEntity.getPageNum()==0 ? 1 : paramEntity.getPageNum();
        //每页记录数
        int pageSize=paramEntity.getPageSize()==0 ? 1000 : paramEntity.getPageSize();
        paramEntity.setParam(currentPageNum);
        paramEntity.setPageSize(pageSize);
        //获取记录
        ResultData<T> resultData=getDataList(clazz, methodName, paramEntity);
        long total=resultData.getTotal();
        List<T> dataList=(List<T>) resultData.getData();
        //创建报文头的标识
        boolean flag=true;
        int rowIndex=1;
        if ((Objects.isNull(total) || total==0) && !CollectionUtils.isEmpty(dataList)) {
            try (FileOutputStream fileOutputStream=new FileOutputStream(file,true)) {
                generateData(dataList, flag, workbook, sheet, fileOutputStream, rowIndex);
                ResponseEntity<StreamingResponseBody> responseEntity=downloadExcelData(tmpFilePath, fileName);
                return responseEntity;
            } catch (FileNotFoundException e) {
                throw new RuntimeException(e);
            }
        }

        int datas=0;
        int totalPages=(int) Math.ceil((double) total / pageSize);
        for (int i=currentPageNum; i <=totalPages ; i++) {
            try (FileOutputStream fileOutputStream=new FileOutputStream(file,true)) {
                paramEntity.setPageNum(currentPageNum);
                ResultData<T> dataList1=getDataList(clazz, methodName, paramEntity);
                List<T> dataList2=(List<T>) dataList1.getData();
                generateData(dataList2, flag, workbook, sheet, fileOutputStream, rowIndex);
                datas +=dataList2.size();
                currentPageNum++;
                flag=false;
                rowIndex=datas + 1;
            } catch (FileNotFoundException e) {
                throw new RuntimeException(e);
            }
        }
        ResponseEntity<StreamingResponseBody> responseEntity=downloadExcelData(tmpFilePath, fileName);
        return responseEntity;
    }

    /**
     * 下载excel文件
     *
     * @param fileFullPath
     * @param fileName
     * @return
     */
    private ResponseEntity<StreamingResponseBody> downloadExcelData(String fileFullPath, String fileName) {
        //读取的文件
        File file=new File(fileFullPath);
        //设置响应头
        HttpHeaders headers=new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", fileName + ".xlsx");
        StreamingResponseBody responseBody=outputStream -> {
            try (FileInputStream fileInputStream=new FileInputStream(file);) {
                byte[] buffer=new byte[4096];
                int bytesRead;
                while ((bytesRead=fileInputStream.read(buffer)) !=-1) {
                    outputStream.write(buffer, 0, bytesRead);
                }
            } catch (FileNotFoundException e) {
                throw new RuntimeException(e);
            } finally {
                if (outputStream !=null) {
                    outputStream.close();
                }
                boolean delete=file.delete();
                if (!delete) {
                    System.out.println("下载失败！");
                }
            }
        };
        return ResponseEntity.ok().headers(headers).contentType(MediaType.APPLICATION_OCTET_STREAM).body(responseBody);
    }

    /**
     * 构建excel结构和内容
     *
     * @param dataList
     * @param flag
     * @param workbook
     * @param sheet
     * @param outputStream
     * @param rowIndex
     * @throws Exception
     */
    private void generateData(List<T> dataList, boolean flag, Workbook workbook, Sheet sheet, FileOutputStream outputStream, int rowIndex) throws Exception {
        //构建表头
        if (flag) {
            buildRowHeader(dataList, sheet, ExcelAlias.class);
        }
        //构建表数据
        buildRowData(dataList, sheet, rowIndex);
        //构建excel表格样式
        buildExcelStyle(sheet);
        //将数据写入工作簿
        workbook.write(outputStream);
    }

    /**
     * 构建excel表格的样式
     *
     * @param sheet
     */
    private void buildExcelStyle(Sheet sheet) {
        //创建工作簿
        Workbook workbook=sheet.getWorkbook();
        //表头样式
        CellStyle headerCellStyle=workbook.createCellStyle();
        headerCellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerCellStyle.setBorderTop(BorderStyle.THIN);
        headerCellStyle.setBorderBottom(BorderStyle.THIN);
        headerCellStyle.setBorderRight(BorderStyle.THIN);
        headerCellStyle.setBorderLeft(BorderStyle.THIN);
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
        headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //字体
        Font headFont=workbook.createFont();
        headFont.setBold(true);
        headerCellStyle.setFont(headFont);

        //excel表格内容样式
        CellStyle cellStyle=workbook.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);

        //设置表头和单元样式到单元格
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (row.getRowNum()==0) {
                    cell.setCellStyle(headerCellStyle);
                } else {
                    cell.setCellStyle(cellStyle);
                }
            }
        }

    }

    /**
     * 构建excel表数据
     *
     * @param dataList
     * @param sheet
     * @param rowIndex
     * @throws Exception
     */
    private void buildRowData(List<T> dataList, Sheet sheet, int rowIndex) throws Exception {
        //获取对象对应的字段
        Field[] declaredFields=dataList.get(0).getClass().getDeclaredFields();
        for (T data : dataList) {
            //从第二行开始追加数据
            Row row=sheet.createRow(rowIndex);
            rowIndex++;
            buildCellData(data, row, declaredFields);
        }
    }

    /**
     * 构建单元格数据
     *
     * @param data
     * @param row
     * @param declaredFields
     * @throws Exception
     */
    private void buildCellData(T data, Row row, Field[] declaredFields) throws Exception {
        //单元格坐标
        int cellIndex=0;
        for (Field field : declaredFields) {
            field.setAccessible(true);
            Object value=field.get(data);
            ExcelAlias fieldAnnotation=field.getAnnotation(ExcelAlias.class);
            if (Objects.isNull(fieldAnnotation)) {
                continue;
            }
            //翻译字段的字典值
            dictValMap(field, fieldAnnotation, data, value);
            Object newValue=field.get(data);
            Cell cell=row.createCell(cellIndex++);
            if (Objects.nonNull(cell)) {
                cell.setCellValue(newValue.toString());
            }
        }
    }

    /**
     * 字典项翻译
     *
     * @param field
     * @param excelAlias
     * @param data
     * @param value
     * @throws IllegalAccessException
     */
    private void dictValMap(Field field, ExcelAlias excelAlias, T data, Object value) throws IllegalAccessException {
        //具体字典项的字段
        DictMapping[] dictMappings=excelAlias.enumMap();
        if (Objects.nonNull(dictMappings) && dictMappings.length > 0) {
            List<DictMapping> dictMappingList=Arrays.asList(dictMappings);
            if (CollectionUtils.isEmpty(dictMappingList)) {
                return;
            }
            for (DictMapping dictMapping : dictMappingList) {
                String dictValue=dictMapping.value();
                String alias=dictMapping.alias();
                if (Objects.nonNull(value) && value.toString().equals(dictValue)) {
                    field.set(data, alias);
                } else {
                    field.set(data, value);
                }
            }

        }
    }


    /**
     * 构建表头信息
     *
     * @param dataList
     * @param sheet
     * @param annotationClass
     */
    private void buildRowHeader(List<T> dataList, Sheet sheet, Class<? extends Annotation> annotationClass) {
        //创建表头
        Row headRow=sheet.createRow(0);
        //获取数据对象
        T firstData=dataList.get(0);
        //获取对象所有的字段
        Field[] fields=firstData.getClass().getDeclaredFields();
        //获取被指定注解修饰的对象的字段个数
        List<Field> newFields=new ArrayList<>();
        for (Field field : fields) {
            if (field.isAnnotationPresent(annotationClass)) {
                newFields.add(field);
            }
        }
        for (int i=0; i < newFields.size(); i++) {
            //获取字段
            Field field=newFields.get(i);
            //获取字段修饰的注解设置别名
            ExcelAlias fieldAnnotation=field.getAnnotation(ExcelAlias.class);
            if (Objects.isNull(fieldAnnotation)) {
                continue;
            }
            //获取注解的值
            String fieldAlias=fieldAnnotation.value();
            //没有别名，则用字段名
            String columName=StringUtils.isBlank(fieldAlias) ? field.getName() : fieldAlias;
            //创建单元格
            Cell cell=headRow.createCell(i);
            //设置表头名称
            cell.setCellValue(columName);
        }


    }


    /**
     * 执行目标方法获取记录数据
     *
     * @param clazz
     * @param methodName
     * @param param
     * @return
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    private ResultData<T> getDataList(Class<T> clazz, String methodName, ParamEntity param) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        //获取具体的bean
        T bean=SpringContextUtil.getBean(clazz);
        Method method=bean.getClass().getMethod(methodName, ParamEntity.class);
        ResultData<T> resultData=(ResultData<T>) method.invoke(bean, param);
        return resultData;
    }


}

3、修饰具体实体的注解，包括基于注解实现的字典项标注

package com.ljxy.score.excelHandler.annotation;

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

/**
 * @ClassName: Alias
 * @Description: 实体对象别名
 * @Author: shenshixi
 * @Date 2023-06-19 16:44:43
 * @Version 1.0.0
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelAlias {
    /**
     * 实体对象字段的别名
     *
     * @return
     */
    String value();

    /**
     * 字段的字典集
     *
     * @return
     */
    DictMapping[] enumMap() default {};
}

package com.ljxy.score.excelHandler.annotation;

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

/**
 * @ClassName: DictMapping
 * @Description: TODO
 * @Author: shenshixi
 * @Date 2023-06-29 18:52:28
 * @Version 1.0.0
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DictMapping {
    /**
     * 字典值
     *
     * @return
     */
    String value() default "";

    /**
     * 字典描述
     *
     * @return
     */
    String alias() default "";
}

4、测试使用

package com.ljxy.score.controller;

import com.ljxy.score.excelHandler.entity.ParamEntity;
import com.ljxy.score.excelHandler.handler.ExcelExportHandler;
import com.ljxy.score.service.FncConfDefFmtService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

/**
 * @ClassName: ExportController
 * @Description: TODO
 * @Author: shenshixi
 * @Date 2023-06-19 17:00:47
 * @Version 1.0.0
 */
@Api(tags="Excel处理模块")
@RestController
@RequestMapping("/export")
public class ExportController {

    @Autowired
    private ExcelExportHandler excelExportHandler;


    @ApiOperation("分片导出-针对大数据量")
    @PostMapping(value="/export-excel-seg-test")
    public  ResponseEntity<StreamingResponseBody> exportExcel(@RequestBody ParamEntity param) throws Exception {
        Class<FncConfDefFmtService> confDefFmtServiceClass=FncConfDefFmtService.class;
        return excelExportHandler.downloadExcel(confDefFmtServiceClass, "queryByPage", param, "ex3333");
    }

}

测试结果

1），jar包依赖

        <!--POI 相关jar start -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>${poi.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-examples</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <!--POI 相关jar end -->

        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.2</version>
        </dependency>

（2），multipartResolver配置bean

	<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
		<property name="defaultEncoding" value="utf-8"></property>
		<!--上传文件上限单位字节，10M-->
		<property name="maxUploadSize" value="10485760"></property>
		<property name="maxInMemorySize" value="40960"></property>
	</bean>

（3），选择上传wps表格模板文件的jsp页面

注意此行不是代码：http://localhost:8085/bl_mave_wf_war_exploded/testExamp/studentListJsp.jsp 这是跳转本页的链接

<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jstl/core_rt" prefix="c"%>

<%
    String path=request.getContextPath();
    String contextPath=request.getContextPath();
    String basePath=request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">
    <title></title>
</head>
<body>
    <form action="<%=contextPath %>/studentConter/importExcelFile" method="post" enctype="multipart/form-data">
        请选择要导入的excel模板文件：<input type="file" value="请选择" name="upFileName" />
        <input type="submit" value="导入" />
    </form>
</body>
<script type="text/javascript">
</script>
</html>

（4），后端java代码解析表格文件内容

在线咨询

上一篇：Bootstrap-Table10分钟入门，怎么学和
下一篇：HTML 简单的<small&

您的项目需求

*请认真填写需求信息，我们会在24小时内与您取得联系。

整合营销服务商

POI操作EXCEL

、前言

2、具体的实现代码

3、修饰具体实体的注解，包括基于注解实现的字典项标注

4、测试使用

您的项目需求