SpringBoot+Ant Design Vue實(shí)現(xiàn)數(shù)據(jù)導(dǎo)出功能方式

更新時(shí)間：2024年01月15日 09:13:36 作者：timi先生

這篇文章主要介紹了SpringBoot+Ant Design Vue實(shí)現(xiàn)數(shù)據(jù)導(dǎo)出功能方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

一、需求

以xlsx格式導(dǎo)出所選表格中的內(nèi)容
要求進(jìn)行分級(jí)
設(shè)置表頭顏色。

二、前端代碼實(shí)現(xiàn)

2.1 顯示實(shí)現(xiàn)

首先我們需要添加一個(gè)用于到導(dǎo)出的按鈕上去，像這樣的：

<a-button @click="exportBatchlistVerify">批量導(dǎo)出</a-button>

至于它放哪里，是什么樣式可以根據(jù)自己的需求決定。

2.2 代碼邏輯

按鈕有了，下來我們開始實(shí)現(xiàn)這個(gè)按鈕的功能。

導(dǎo)出數(shù)據(jù)確定。

表格設(shè)置： 表頭添加以下代碼

<s-table	
	:row-key="(record) => record.id"
	:row-selection="options.rowSelection"
	>

Vue代碼 ：獲取選中的目標(biāo)ID數(shù)組

   import listApi from '@/api/listApi'
	let selectedRowKeys = ref([])
	const options = {
		alert: {
			show: false,
			clear: () => {
				selectedRowKeys = ref([])
			}
		},
		rowSelection: {
			onChange: (selectedRowKey, selectedRows) => {
				selectedRowKeys.value = selectedRowKey
			},
			//這里是設(shè)置復(fù)選框的寬度，可以刪掉
			columnWidth : 6
		}
	}

按鈕功能實(shí)現(xiàn)：

	const exportBatchlistVerify = () => {
		if (selectedRowKeys.value.length < 1) {
			message.warning('請(qǐng)輸入查詢條件或勾選要導(dǎo)出的信息')
		}
		if (selectedRowKeys.value.length > 0) {
			const params = {
				checklistIds: selectedRowKeys.value
					.map((m) => {
						return m
					})
					.join()
			}
			exportBatchChecklist(params)
			return
		}
		exportBatchList(params)

	}
	const exportBatchList= (params) => {
		listApi.listExport(params).then((res) => {
			downloadUtil.resultDownload(res)
			table.value.clearSelected()
		})
	}

listApi: 導(dǎo)入部分和 baseRequest 請(qǐng)參考 Vue封裝axios實(shí)現(xiàn)

import { baseRequest } from '@/utils/request'
const request = (url, ...arg) => baseRequest(`/list/` + url, ...arg)
	listExport(data) {
		return request('export', data, 'get', {
			responseType: 'blob'
		})
	},

三、后端代碼實(shí)現(xiàn)

3.1 實(shí)體類

我們首先建一個(gè)簡(jiǎn)單的實(shí)體，展示將要導(dǎo)出的數(shù)據(jù)內(nèi)容：

import com.baomidou.mybatisplus.annotation.TableName;
import com.fhs.core.trans.vo.TransPojo;
import lombok.Data;

/**
 * Auth lhd
 * Date 2023/6/21 9:42
 * Annotate 導(dǎo)出功能測(cè)試類
 */
@Data
@TableName("userTest")
public class UserTest implements TransPojo {

    private String id;
    private String name;
    private String tel;
    private String password;
    private String address;
}

3.2 接收參數(shù)和打印模板

有了實(shí)體類后，我們將開始進(jìn)行具體的邏輯編寫，但在這之前我們需要定義接收前端傳參的類，和定義我們的打印模板。

接收參數(shù)：

import lombok.Data;

/**
 * Auth lhd
 * Date 2023/6/21 9:46
 * Annotate
 */
@Data
public class UserTestExportParam {
    private String listIds;
}

這部分很簡(jiǎn)單，我們只需要即將打印的內(nèi)容ID即可。

打印模板：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import lombok.Data;

/**
 * Auth lhd
 * Date 2023/6/21 10:10
 * Annotate
 */
@Data
public class UserTestResult {

    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
    @ExcelProperty({"人物名稱"})
    private String name;

    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 29)
    @ExcelProperty({"基本信息","聯(lián)系方式 "})
    private String tel;

    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 31)
    @ExcelProperty({"基本信息","地址 "})
    private String address;

    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 26)
    @ExcelProperty({"基本信息","不能外露","賬號(hào)密碼 "})
    private String password;

}

打印模板定義了我們們即將打印的表格的表頭結(jié)構(gòu)和列名、表頭顏色。

備注：通過修改打印模板類的注解，可以實(shí)現(xiàn)自定義的表頭和表頭顏色

3.3 正式的邏輯

映射接口和XML：

接口 UserTestMapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.modular.userTest.entity.UserTest;

/**
 * Auth lhd
 * Date 2023/6/21 10:02
 * Annotate
 */
public interface UserTestMapper extends BaseMapper<UserTest> {
}

XML UserTestMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.modular.userTest.mapper.UserTestMapper">

</ma

核心邏輯接口和實(shí)現(xiàn)：

邏輯接口 UserTestService

import com.baomidou.mybatisplus.extension.service.IService;
import com.modular.userTest.entity.UserTest;
import com.modular.userTest.param.UserTestExportParam;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Auth lhd
 * Date 2023/6/21 9:44
 * Annotate
 */
public interface UserTestService extends IService<UserTest> {
    void exportUserTestList(UserTestExportParam listExportParam, HttpServletResponse response) throws IOException;
}

接口實(shí)現(xiàn) UserTestServiceImpl

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
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.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.fhs.trans.service.impl.TransService;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import com.modular.userTest.entity.UserTest;
import com.modular.userTest.mapper.UserTestMapper;
import com.modular.userTest.param.UserTestExportParam;
import com.modular.userTest.result.UserTestResult;
import com.modular.userTest.service.UserTestService;
import com.common.excel.CommonExcelCustomMergeStrategy;
import com.common.exception.CommonException;
import com.common.util.CommonDownloadUtil;
import com.common.util.CommonResponseUtil;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.stream.Collectors;

/**
 * Auth lhd
 * Date 2023/6/21 10:01
 * Annotate
 */
@Service
public class UserTestServiceImpl extends ServiceImpl<UserTestMapper, UserTest> implements UserTestService {

    @Resource
    private TransService transService;
    
    @Override
    public void exportUserTestList(UserTestExportParam listExportParam, HttpServletResponse response) throws IOException {
            File tempFile = null;
            try {
                QueryWrapper<UserTest> queryWrapper = new QueryWrapper<>();
                if(ObjectUtil.isNotEmpty(listExportParam.getListIds())) {
                    queryWrapper.lambda().in(UserTest::getId, StrUtil.split(listExportParam.getListIds(), StrUtil.COMMA));
                }
                String fileName = "人物信息表.xlsx";
                List<UserTest> userlists = this.list(queryWrapper);
                if(ObjectUtil.isEmpty(userlists)) {
                    throw new CommonException("無數(shù)據(jù)可導(dǎo)出");
                }
                transService.transBatch(userlists);
                List<UserTestResult> listResults = userlists.stream()
                        .map(userlist -> {
                            UserTestResult listExportResult = new UserTestResult();
                            BeanUtil.copyProperties(userlist, listExportResult);
                            listExportResult.setName(ObjectUtil.isNotEmpty(listExportResult.getName())?
                                    listExportResult.getName():"無檢查地址");
                            return listExportResult;
                        }).collect(Collectors.toList());

                // 創(chuàng)建臨時(shí)文件
                tempFile = FileUtil.file(FileUtil.getTmpDir() + FileUtil.FILE_SEPARATOR + fileName);

                // 頭的策略
                WriteCellStyle headWriteCellStyle = new WriteCellStyle();
                WriteFont headWriteFont = new WriteFont();
                headWriteFont.setFontHeightInPoints((short) 14);
                headWriteCellStyle.setWriteFont(headWriteFont);

                // 水平垂直居中
                headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

                // 內(nèi)容的策略
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                // 這里需要指定 FillPatternType 為FillPatternType.SOLID_FOREGROUND 不然無法顯示背景顏色.頭默認(rèn)了 FillPatternType所以可以不指定
                contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                // 內(nèi)容背景白色
                contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                WriteFont contentWriteFont = new WriteFont();

                // 內(nèi)容字體大小
                contentWriteFont.setFontHeightInPoints((short) 12);
                contentWriteCellStyle.setWriteFont(contentWriteFont);

                //設(shè)置邊框樣式，細(xì)實(shí)線
                contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
                contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
                contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
                contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

                // 水平垂直居中
                contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

                // 這個(gè)策略是 頭是頭的樣式 內(nèi)容是內(nèi)容的樣式 其他的策略可以自己實(shí)現(xiàn)
                HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,
                        contentWriteCellStyle);

                // 寫excel
                EasyExcel.write(tempFile.getPath(), UserTestResult.class)
                        // 自定義樣式
                        .registerWriteHandler(horizontalCellStyleStrategy)
                        // 自動(dòng)列寬
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        // 機(jī)構(gòu)分組合并單元格
                        .registerWriteHandler(new CommonExcelCustomMergeStrategy(listResults.stream().map(UserTestResult::getName)
                                .collect(Collectors.toList()), 0))
                        // 設(shè)置第一行字體
                        .registerWriteHandler(new CellWriteHandler() {
                            @Override
                            public void afterCellDispose(CellWriteHandlerContext context) {
                                WriteCellData<?> cellData = context.getFirstCellData();
                                WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
                                Integer rowIndex = context.getRowIndex();
                                if(rowIndex == 0) {
                                    WriteFont headWriteFont = new WriteFont();
                                    headWriteFont.setFontName("宋體");
                                    headWriteFont.setBold(true);
                                    headWriteFont.setFontHeightInPoints((short) 16);
                                    writeCellStyle.setWriteFont(headWriteFont);
                                }
                            }
                        })
                        // 設(shè)置表頭行高
                        .registerWriteHandler(new AbstractRowHeightStyleStrategy() {
                            @Override
                            protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
                                if(relativeRowIndex == 0) {
                                    // 表頭第一行
                                    row.setHeightInPoints(34);
                                } else {
                                    // 表頭其他行
                                    row.setHeightInPoints(30);
                                }
                            }
                            @Override
                            protected void setContentColumnHeight(Row row, int relativeRowIndex) {
                                // 內(nèi)容行
                                row.setHeightInPoints(20);
                            }
                        })
                        .sheet("人物信息表信息")
                        .doWrite(listResults);
                CommonDownloadUtil.download(tempFile, response);
            } catch (Exception e) {
                log.error(">>> 人物信息表導(dǎo)出異常：", e);
                CommonResponseUtil.renderError(response, "導(dǎo)出失敗");
            } finally {
                FileUtil.del(tempFile);
            }
        }
}

這里只展示具體邏輯

3.4 Contorller

最后寫一個(gè)簡(jiǎn)單的controller類即可：

import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import com.modular.userTest.param.UserTestExportParam;
import com.modular.userTest.service.UserTestService;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Auth lhd
 * Date 2023/6/21 10:17
 * Annotate
 */
@RestController
public class UserTestController {

    @Resource
    private UserTestService userTestService;

    @GetMapping(value="/list/export",produces=MediaType.APPLICATION_OCTET_STREAM_VALUE)
    public void exportUser(UserTestExportParam listExportParam, HttpServletResponse response) throws IOException {
        userTestService.exportUserTestList(listExportParam, response);
    }
}

我們看看打印效果：