Entity实体类
package com.cbb.entity;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import org.springframework.format.annotation.DateTimeFormat;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 这是一个关于每个银行卡有多少金额的实体类 && 导出excel表格与pdf文档
*
* @author 陈斌斌
*
* @date 2022年5月11日 09点23分
*
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("card")
public class Card {
/**
* 主键id
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 银行卡号
*/
@TableField("cid")
private String cid;
/**
* 银行卡类别
*/
@TableField("type")
private String type;
/**
* 金额
*/
@TableField("price")
private BigDecimal price;
/**
* 起始金额
*/
@TableField(exist = false)
private BigDecimal beginPrice;
/**
* 结束金额
*/
@TableField(exist = false)
private BigDecimal endPrice;
/**
* 注册时间
*/
@DateTimeFormat(pattern = "yyyy-MM-dd")
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
@TableField("createTime")
private Date createTime;
/**
* 起始时间
*/
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
@TableField(exist = false)
private Date beginTime;
/**
* 结束时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@TableField(exist = false)
private Date endTime;
/**
* 导出excel模板
*/
public static List<String> listName = Arrays.asList("序号", "银行卡号", "银行类别", "金额", "创建时间");
/**
* 导出pdf
*/
public static List<String> listNames = Arrays.asList("银行卡号", "银行类别", "金额", "创建时间");
}
Mapper接口
package com.cbb.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cbb.entity.Card;
/**
* 这是一个关于银行卡的接口
*
* @author 陈斌斌
* @Date 2022年5月11日 09点25分
*
*/
public interface CardMapper extends BaseMapper<Card> {
}
Service接口
package com.cbb.service;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.multipart.MultipartFile;
import com.cbb.entity.Card;
/**
* 这是一个关于银行卡的业务处理的接口
*
* @author 陈斌斌
* @Date 2022年5月11日 09点25分
*
*/
public interface CardService {
/**
* 导出excel报表
*
* @param response
*/
void exportFixed(HttpServletResponse response);
}
工具类
package com.cbb.util;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.prefs.BackingStoreException;
/**
* @author 陈斌斌
* @date 2022年5月11日 13点59分
*/
public class ExcelPortUtil{
/**
* @param sheetName 工作表名,文件名,头部信息
* @param listName 列名
* @param list 需要写入的数据
* @param listBottom 底部写入信息:<列位置,数据>
* @param response 返回
*/
public static void excelPort(String sheetName, List<String> listName, List<Map<String, String>> list, List<Map<Integer, String>> listBottom, HttpServletResponse response) {
try {
if (list.size() == 0) {
throw new BackingStoreException("数据为空");
}
// 声明一个工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 创建sheet页
XSSFSheet sheet = wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(19);
// 表头
XSSFRow rowReportTitle = sheet.createRow(0);
Cell cell1 = rowReportTitle.createCell(0); // 0列
// 设置值
cell1.setCellValue(sheetName);
// 合并表头
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, listName.size() - 1));
rowReportTitle.setHeight((short) 600); // 行高
//设置表头字体
Font headFont = wb.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 18);// 字体大小
CellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 头部样式添加
cell1.setCellStyle(headStyle);
// 全局加线样式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 记录标题信息
TreeMap<String, Integer> headMap = new TreeMap<>();
// 标题写入
XSSFRow row = sheet.createRow(1);
for (int i = 0; i < listName.size(); i++) {
row.setHeight((short) 450);
XSSFCell cell = row.createCell(i);
String headName = listName.get(i);
cell.setCellValue(headName); // 写入列名
headMap.put(headName, i);
cell.setCellStyle(cellStyle);
}
// 写入内容数据
int ind = 2;
for (Map<String, String> map : list) {
XSSFRow r = sheet.createRow(ind++);
for (Map.Entry<String, Integer> m : headMap.entrySet()) {
String name = m.getKey(); // 列名
String value = map.get(name); // value 不一定存在
XSSFCell cell2 = r.createCell(m.getValue());
if (value != null) {
cell2.setCellValue(value);
}
cell2.setCellStyle(cellStyle);
}
}
// 底部样式
CellStyle bottomStyle = wb.createCellStyle();
bottomStyle.setBorderBottom(BorderStyle.THIN); //下边框
bottomStyle.setBorderLeft(BorderStyle.THIN);//左边框
bottomStyle.setBorderTop(BorderStyle.THIN);//上边框
bottomStyle.setBorderRight(BorderStyle.THIN);//右边框
bottomStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 写入底部
if (listBottom != null) {
int columnNum = listName.size(); // 当前表有多少列
for (Map<Integer, String> map : listBottom) {
XSSFRow bottom = sheet.createRow(ind);
bottom.setHeight((short) 400); // 行高
// 当前行分几列
int size = map.size();
if (columnNum % 2 == 0 & size % 2 == 0) {
// 都是偶数执行
int c = columnNum / size; // 列大小
for (int i = 0; i < size; i++) {
CellRangeAddress cellAddresses0 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
sheet.addMergedRegion(cellAddresses0);
XSSFCell c0 = bottom.createCell(cellAddresses0.getFirstColumn());
c0.setCellValue(map.get(i));
c0.setCellStyle(bottomStyle);
for (int n = cellAddresses0.getFirstColumn() + 1; n <= cellAddresses0.getLastColumn(); n++) {
XSSFCell cn = bottom.createCell(n);
cn.setCellStyle(bottomStyle);
}
}
} else if (!(columnNum % 2 == 0) & !(size % 2 == 0)) {
// 都是奇数执行
int c = columnNum - 1 / size;
for (int i = 0; i < size; i++) {
CellRangeAddress cellAddresses1;
if (size - i <= 1) {
cellAddresses1 = new CellRangeAddress(ind, ind, i * c, i * c + c);
} else {
cellAddresses1 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
}
sheet.addMergedRegion(cellAddresses1);
XSSFCell c0 = bottom.createCell(cellAddresses1.getFirstColumn());
c0.setCellValue(map.get(i));
c0.setCellStyle(bottomStyle);
for (int n = cellAddresses1.getFirstColumn() + 1; n <= cellAddresses1.getLastColumn(); n++) {
XSSFCell cn = bottom.createCell(n);
cn.setCellStyle(bottomStyle);
}
}
} else {
// 奇偶不同
int c = (columnNum + 1) / size;
for (int i = 0; i < size; i++) {
CellRangeAddress cellAddresses2;
if (size - i <= 1) {
cellAddresses2 = new CellRangeAddress(ind, ind, i * c, i * c + c - 2);
} else {
cellAddresses2 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
}
sheet.addMergedRegion(cellAddresses2);
XSSFCell c0 = bottom.createCell(cellAddresses2.getFirstColumn());
c0.setCellValue(map.get(i));
c0.setCellStyle(bottomStyle);
for (int n = cellAddresses2.getFirstColumn() + 1; n <= cellAddresses2.getLastColumn(); n++) {
XSSFCell cn = bottom.createCell(n);
cn.setCellStyle(bottomStyle);
}
}
}
ind++;
}
}
// 输出Excel文件
OutputStream output = response.getOutputStream();
response.reset();
// 设置文件头
response.setHeader("Content-Disposition",
"attchement;filename=" + new String((sheetName + ".xlsx").getBytes("gb2312"), "ISO8859-1"));
response.setContentType("application/msexcel");
wb.write(output);
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
实现类imple
package com.cbb.serviceimple;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.cbb.entity.Card;
import com.cbb.entity.ImportCard;
import com.cbb.mapper.CardMapper;
import com.cbb.service.CardService;
import com.cbb.service.ImportCardService;
import com.cbb.util.ExcelPortUtil;
import com.cbb.util.ExportPDF;
import com.cbb.util.FileExport;
import lombok.RequiredArgsConstructor;
/**
* 这是一个关于银行卡的业务处理的实现类,处理业务逻辑
*
* @author 陈斌斌
* @Date 2022年5月11日 09点25分
*
*/
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class CardServiceImple implements CardService {
/**
* 导出excel报表
*
* @param response
*/
@Override
public void exportFixed(HttpServletResponse response) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// 查询数据
List<Card> cardList = cardMapper.selectList(null);
if (cardList != null && cardList.size() > 0) {
// 列名 数据
List<Map<String, String>> list = new ArrayList<>();
for (Card vo : cardList) {
Map<String, String> map = new HashMap<>();
map.put("序号", vo.getId().toString());
map.put("银行卡号", vo.getCid());
map.put("银行类别", vo.getType());
map.put("金额", vo.getPrice().toString());
map.put("创建时间", sdf.format(vo.getCreateTime()));
list.add(map);
}
ExcelPortUtil.excelPort("账户信息", Card.listName, list, null, response);
}
}
}
controller控制层
package com.cbb.controller;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.cbb.entity.Card;
import com.cbb.service.CardService;
import lombok.RequiredArgsConstructor;
/**
* 这是一个关于银行卡的控制层
*
* @author 陈斌斌
* @Date 2022年5月11日 09点25分
*
*/
@RestController
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class CardController {
/**
* 银行卡的业务接口
*/
private final CardService cardService;
/**
* 导出excel报表
*
* @param response
*/
@RequestMapping("exportFixedCard")
public void exportFixed(HttpServletResponse response) {
cardService.exportFixed(response);
}
}