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、測試使用

您的項目需求

、前言

2、具體的實現代碼

3、修飾具體實體的注解，包括基于注解實現的字典項標注