一、需求
- 以xlsx格式導出所選表格中的內(nèi)容
- 要求進行分級
- 設(shè)置表頭顏色。
二、前端代碼實現(xiàn)
2.1 顯示實現(xiàn)
首先我們需要添加一個用于到導出的按鈕上去，像這樣的：
<a-button @click="exportBatchlistVerify">批量導出</a-button>
至于它放哪里，是什么樣式可以根據(jù)自己的需求決定。
2.2 代碼邏輯
按鈕有了，下來我們開始實現(xiàn)這個按鈕的功能。
- 導出數(shù)據(jù)確定。
表格設(shè)置： 表頭添加以下代碼
<s-table
:row-key="(record) => record.id"
:row-selection="options.rowSelection"
>
Vue代碼 ：獲取選中的目標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è)置復選框的寬度，可以刪掉
columnWidth : 6
}
}
按鈕功能實現(xiàn)：
const exportBatchlistVerify = () => {
if (selectedRowKeys.value.length < 1) {
message.warning('請輸入查詢條件或勾選要導出的信息')
}
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: 導入部分和 baseRequest 請參考 Vue封裝axios實現(xiàn)
import { baseRequest } from '@/utils/request'
const request = (url, ...arg) => baseRequest(`/list/` + url, ...arg)
listExport(data) {
return request('export', data, 'get', {
responseType: 'blob'
})
},
三、后端代碼實現(xiàn)
3.1 實體類
我們首先建一個簡單的實體，展示將要導出的數(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 導出功能測試類
*/
@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ù)：
import lombok.Data;
/**
* Auth lhd
* Date 2023/6/21 9:46
* Annotate
*/
@Data
public class UserTestExportParam {
private String listIds;
}
這部分很簡單，我們只需要即將打印的內(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({"基本信息","不能外露","賬號密碼 "})
private String password;
}
打印模板定義了我們們即將打印的表格的表頭結(jié)構(gòu)和列名、表頭顏色。
備注：通過修改打印模板類的注解，可以實現(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
核心邏輯接口和實現(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;
}
- 接口實現(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ù)可導出");
}
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)建臨時文件
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 不然無法顯示背景顏色.頭默認了 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è)置邊框樣式，細實線
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 水平垂直居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 這個策略是 頭是頭的樣式 內(nèi)容是內(nèi)容的樣式 其他的策略可以自己實現(xiàn)
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,
contentWriteCellStyle);
// 寫excel
EasyExcel.write(tempFile.getPath(), UserTestResult.class)
// 自定義樣式
.registerWriteHandler(horizontalCellStyleStrategy)
// 自動列寬
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// 機構(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(">>> 人物信息表導出異常：", e);
CommonResponseUtil.renderError(response, "導出失敗");
} finally {
FileUtil.del(tempFile);
}
}
}
這里只展示具體邏輯，common開頭的公共類和工具類感興趣的伙伴可以私信我獲取~
3.4 Contorller
最后寫一個簡單的controller類即可：文章來源：http://www.zghlxwxcb.cn/news/detail-495614.html
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);
}
}
我們看看打印效果：
文章來源地址http://www.zghlxwxcb.cn/news/detail-495614.html
到了這里，關(guān)于SpringBoot + Ant Design Vue實現(xiàn)數(shù)據(jù)導出功能的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
