查询MySQL数据库中数据，导出excel、pdf类型文档

1.数据库表格

CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT COMMENT '编号',`name` varchar(255) DEFAULT NULL COMMENT '姓名',`age` int DEFAULT NULL COMMENT '年龄',`addr` varchar(255) DEFAULT NULL COMMENT '住址1',`addr2` varchar(255) DEFAULT NULL COMMENT '住址2',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

2.pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.example</groupId><artifactId>net</artifactId><version>0.0.1-SNAPSHOT</version><name>net</name><description>net</description><properties><java.version>1.8</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><spring-boot.version>2.6.13</spring-boot.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.2</version></dependency><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency>
<!--        pdf--><dependency><groupId>com.itextpdf</groupId><artifactId>itextpdf</artifactId><version>5.5.13</version> <!-- 或使用最新版本 --></dependency><!-- pdf输出中文要用的jar --><dependency><groupId>com.itextpdf</groupId><artifactId>itext-asian</artifactId><version>5.2.0</version></dependency></dependencies><dependencyManagement><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-dependencies</artifactId><version>${spring-boot.version}</version><type>pom</type><scope>import</scope></dependency>
<!--   excel表格导出--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>org.xmlunit</groupId><artifactId>xmlunit-core</artifactId></dependency></dependencies></dependencyManagement><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.1</version><configuration><source>1.8</source><target>1.8</target><encoding>UTF-8</encoding></configuration></plugin><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><version>${spring-boot.version}</version><configuration><mainClass>com.example.net.NetApplication</mainClass><skip>true</skip></configuration><executions><execution><id>repackage</id><goals><goal>repackage</goal></goals></execution></executions></plugin></plugins></build></project>

3.User.java

package com.example.net.demos.entity;import lombok.Data;@Data
public class User {private Integer id;private String name;private Integer age;private String addr;private String addr2;
}

4.UserMapper.java

package com.example.net.demos.mapper;import com.example.net.demos.entity.User;
import org.apache.ibatis.annotations.Mapper;import java.util.List;@Mapper
public interface UserMapper {List<User> selectList();
}

5.UserMapper.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.example.net.demos.mapper.UserMapper"><resultMap id="user" type="com.example.net.demos.entity.User"><id column="id" property="id"/><result column="name" property="name"/><result column="age" property="age"/><result column="addr" property="addr"/><result column="addr2" property="addr2"/></resultMap><select id="selectList" resultMap="user">select * from user</select>
</mapper>

6.service

6.1 UserService.java

package com.example.net.demos.service;import com.example.net.demos.entity.User;
import org.apache.ibatis.annotations.Mapper;import java.util.List;@Mapper
public interface UserService {List<User> selectAll();
}

6.2 UserServiceImpl.java

package com.example.net.demos.service.impl;import com.example.net.demos.entity.User;
import com.example.net.demos.mapper.UserMapper;
import com.example.net.demos.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class UserServiceImpl implements UserService {@AutowiredUserMapper userMapper;@Overridepublic List<User> selectAll() {return userMapper.selectList();}
}

7.UserController

package com.example.net.demos.controller;import com.example.net.demos.entity.User;
import com.example.net.demos.service.UserService;
import com.example.net.demos.util.PageUtil;
import com.example.net.demos.util.PdfFUtil;
import com.example.net.demos.util.R;
import com.itextpdf.text.Document;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;import java.io.File;
import java.io.FileOutputStream;
import java.util.List;@RestController
@RequestMapping("/user")
public class UserController {private Logger logger= LoggerFactory.getLogger(UserController.class);@AutowiredUserService userService;/*** 查询所有数据* @return*/@PostMapping("/list")public R selectList(){List<User> users = userService.selectAll();logger.info(users.toString());return R.ok(users);}/*** 导出pdf格式文档* @param destination* @return* @throws Exception*/@PostMapping("/pdf")public List<User> getUser(@RequestParam("destination") String destination) throws Exception {List<User> list=userService.selectAll();long currentTime=System.currentTimeMillis();int total=list.size();try {
//            1.新建document对象Document document = new Document(PageSize.A4.rotate());  //建立一个Document对象
//            pdf文档存储的地址String savePath=destination+"/"+"user_"+currentTime+".pdf";//            2.建立一个书写器（writer）与document对象关联File file = new File(savePath);  //修改要生成pdf的位置路径file.createNewFile();PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(file));//            3.打开文档document.open();//            标题Paragraph paragraph = new Paragraph("用户表", titlefont_16);paragraph.setAlignment(1); //设置文字居中，0靠左，1居中，2靠右paragraph.setIndentationLeft(12); //设置左缩进paragraph.setIndentationRight(12);  //设置右缩进paragraph.setFirstLineIndent(24); //设置首行缩进paragraph.setLeading(20f);  //设置行间距paragraph.setSpacingBefore(5f);  //设置段落上空白paragraph.setSpacingAfter(10f);  //设置段落下空白document.add(paragraph);   //标题int pn = 1;int ps = 34;for (int j = 0; j < (total / ps) + 1; j++) {PageUtil pageUtil1 = new PageUtil();List<User> listPage=pageUtil1.pageUtil(list,pn,ps);
//                表格PdfPTable table = PdfFUtil.createTable(new float[]{75, 110, 75, 140,75});table.addCell(PdfFUtil.createCell("ID",textfont_10));table.addCell(PdfFUtil.createCell("姓名",textfont_10));table.addCell(PdfFUtil.createCell("年龄",textfont_10));table.addCell(PdfFUtil.createCell("住址",textfont_10));table.addCell(PdfFUtil.createCell("住址2",textfont_10));for (int i = 0; i < listPage.size(); i++) {table.addCell(PdfFUtil.createCell(String.valueOf(listPage.get(i).getId()), textfont_10));table.addCell(PdfFUtil.createCell(listPage.get(i).getName(), textfont_10));table.addCell(PdfFUtil.createCell(String.valueOf(listPage.get(i).getAge()), textfont_10));table.addCell(PdfFUtil.createCell(String.valueOf(listPage.get(i).getAddr()), textfont_10));table.addCell(PdfFUtil.createCell(String.valueOf(listPage.get(i).getAddr2()), textfont_10));}document.add(table);PdfFUtil.onEndPage(writer, document);pn++;ps = 36;}
//            5.关闭文档document.close();}catch (Exception e){e.printStackTrace();}return null;}/*** 全局变量*/
//    定义全局的字体静态变量private static Font titlefont_16;private static Font titlefontnormal_16;private static Font headfont_14;private static Font headfontnormal_14;private static Font headfont_12;private static Font headfontnormal_12;private static Font keyfont_10;private static Font textfont_10;private static Font underlinefont_10;
//    静态代码块static{try{BaseFont bfChinese = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);titlefont_16=new Font(bfChinese,16,Font.BOLD);headfont_14=new Font(bfChinese,14,Font.BOLD);headfont_12=new Font(bfChinese,12,Font.BOLD);keyfont_10=new Font(bfChinese,10,Font.BOLD);titlefontnormal_16=new Font(bfChinese,16,Font.NORMAL);headfontnormal_14=new Font(bfChinese,14,Font.NORMAL);headfontnormal_12=new Font(bfChinese,12,Font.NORMAL);textfont_10=new Font(bfChinese,10, Font.NORMAL);underlinefont_10=new Font(bfChinese,10,Font.UNDERLINE);}catch (Exception e){e.printStackTrace();}}/*** 导出excel表格* @param response* @throws Exception*/@PostMapping("/excel")public void downloadExcel(HttpServletResponse response) throws Exception {
//        创建HSSFWorkbook对象，excel的文档对象HSSFWorkbook workbook = new HSSFWorkbook();
//        excel的表单HSSFSheet sheet = workbook.createSheet("用户表");//        数据库表中的数据List<User> list=userService.selectAll();//        设置要导出的文件名String fileName="user"+".xls";
//        新增数据行，并且设置单元格数据int rowNum=1;String[] headers={"ID","姓名","年龄","住址","住址2"};
//        headers 标识excel表中第一行的表头HSSFRow row = sheet.createRow(0);
//        在excel表中添加表头for(int i=0;i<headers.length;i++){HSSFCell cell=row.createCell(i);HSSFRichTextString text=new HSSFRichTextString(headers[i]);cell.setCellValue(text);}
//        在表头中存放查询到的数据放入对应的列for(User user:list){HSSFRow row1=sheet.createRow(rowNum);row1.createCell(0).setCellValue(user.getId());row1.createCell(1).setCellValue(user.getName());row1.createCell(2).setCellValue(user.getAge());row1.createCell(3).setCellValue(user.getAddr());row1.createCell(4).setCellValue(user.getAddr2());rowNum++;}response.setContentType("application/octet-stream");response.setHeader("Content-disposition", "attachment;filename=" + fileName);response.flushBuffer();workbook.write(response.getOutputStream());}
}

8.工具类

8.1 PageUtil.java

package com.example.net.demos.util;import java.io.Serializable;
import java.util.List;
import java.util.stream.Collectors;public class PageUtil<T> implements Serializable {/*** 实体类列表*/private List<T> content;/*** 是否首页*/private boolean first;/*** 是否尾页*/private boolean last;/*** 总记录数*/private Integer totalCount;/*** 总页数*/private Integer totalPages;/*** 当前页记录数*/private Integer count;/*** 每页记录数*/private Integer pageSize;/*** 当前页*/private Integer pageNum;@Overridepublic String toString() {return "PageUtil{" +"content=" + content +", first=" + first +", last=" + last +", totalCount=" + totalCount +", totalPages=" + totalPages +", count=" + count +", pageSize=" + pageSize +", pageNum=" + pageNum +'}';}public Integer getTotalCount() {return totalCount;}public void setTotalCount(Integer totalCount) {this.totalCount = totalCount;}public Integer getCount() {return count;}public void setCount(Integer count) {this.count = count;}public void setContent(List<T> content) {this.content = content;}public void setFirst(boolean first) {this.first = first;}public void setLast(boolean last) {this.last = last;}public void setTotalPages(Integer totalPages) {this.totalPages = totalPages;}public void setPageSize(Integer pageSize) {this.pageSize = pageSize;}public void setPageNum(Integer pageNum) {this.pageNum = pageNum;}public List<T> getContent() {return content;}public boolean isFirst() {return first;}public boolean isLast() {return last;}public Integer getTotalPages() {return totalPages;}public Integer getPageSize() {return pageSize;}public Integer getPageNum() {return pageNum;}//public List<T> pageUtil(Integer page, Integer size, List<T> list) {public List<T> pageUtil( List<T> list,Integer page, Integer size) {page = page <= 0 ? 1 : page;List<T> list1 = list.stream().skip((page - 1) * size).limit(size).collect(Collectors.toList());int length = list.size();//是否第一页this.first = (page == 1);//是否最后一页this.last = (page == (length - 1) / size);//总页数this.totalPages = ((length - 1) / size + 1);//总elementsthis.totalCount = (length);//每页多少elementsthis.pageSize = (size);//内容this.content = (list1);//当前页数据量this.count = (list1.size());//当前页数，第一页是1this.pageNum = (page);return list1;}}

8.2 PdfFUtil.java

package com.example.net.demos.util;import com.itextpdf.text.*;
import com.itextpdf.text.pdf.*;import java.io.IOException;public class PdfFUtil {// 最大宽度private static int maxWidth = 720;/**------------------------创建表格单元格的方法start----------------------------*//*** 创建单元格(指定字体)** @param value* @param font* @return*/public static PdfPCell createCell(String value, Font font) {PdfPCell cell = new PdfPCell();cell.setVerticalAlignment(Element.ALIGN_MIDDLE); //垂直居中cell.setHorizontalAlignment(Element.ALIGN_CENTER); //水平居中cell.setPhrase(new Phrase(value, font));return cell;}/*** 创建单元格(指定字体、设置单元格高度)** @param value* @param font* @return 申请事由——这行使用的方法*/public static PdfPCell createCell(String value, Font font, float f) {PdfPCell cell = new PdfPCell();cell.setVerticalAlignment(Element.ALIGN_MIDDLE);cell.setHorizontalAlignment(Element.ALIGN_CENTER);cell.setPhrase(new Phrase(value, font));cell.setFixedHeight(f); // 设置表格中的单行高度return cell;}/*** 创建单元格（指定字体、水平局左/中/右）** @param value* @param font* @param align* @return*/public static PdfPCell createCell(String value, Font font, int align) {PdfPCell cell = new PdfPCell();cell.setVerticalAlignment(Element.ALIGN_MIDDLE); //垂直居中cell.setHorizontalAlignment(align); //水平居中cell.setPhrase(new Phrase(value, font));return cell;}/*** 创建单元格（指定字体、水平局左/中/右、单元格跨x列合并）** @param value* @param font* @param align* @param colspan* @return*/public PdfPCell createCell(String value, Font font, int align, int colspan) {PdfPCell cell = new PdfPCell();cell.setVerticalAlignment(Element.ALIGN_MIDDLE); //垂直居中cell.setHorizontalAlignment(align); //水平居中cell.setColspan(colspan);cell.setPhrase(new Phrase(value, font));return cell;}/*** 创建单元格（指定字体、水平居..、单元格跨x列合并、设置单元格内边距）** @param value* @param font* @param align* @param colspan* @param boderFlag* @return*/public static PdfPCell createCell(String value, Font font, int align, int colspan, boolean boderFlag) {PdfPCell cell = new PdfPCell();cell.setVerticalAlignment(Element.ALIGN_MIDDLE);cell.setHorizontalAlignment(align);cell.setColspan(colspan);cell.setPhrase(new Phrase(value, font));cell.setPadding(3.0f);if (!boderFlag) {cell.setBorder(0);cell.setPaddingTop(10.0f);cell.setPaddingBottom(7.0f);} else if (boderFlag) {cell.setBorder(0);cell.setPaddingTop(0.0f);cell.setPaddingBottom(15.0f);}return cell;}/*** 创建单元格（指定字体、水平..、边框宽度：0表示无边框、内边距）** @param value* @param font* @param align* @param borderWidth* @param paddingSize* @param flag* @return*/public static PdfPCell createCell(String value, Font font, int align, float[] borderWidth, float[] paddingSize, boolean flag) {PdfPCell cell = new PdfPCell();cell.setVerticalAlignment(Element.ALIGN_MIDDLE);cell.setHorizontalAlignment(align);cell.setPhrase(new Phrase(value, font));cell.setBorderWidthLeft(borderWidth[0]);cell.setBorderWidthRight(borderWidth[1]);cell.setBorderWidthTop(borderWidth[2]);cell.setBorderWidthBottom(borderWidth[3]);cell.setPaddingTop(paddingSize[0]);cell.setPaddingBottom(paddingSize[1]);if (flag) {cell.setColspan(2);}return cell;}
/**------------------------创建表格单元格的方法end----------------------------*//**--------------------------创建表格的方法start----------------------------*//*** 创建默认列宽，指定列数、水平(居中、右、左)的表格** @param colNumber* @param align* @return*/public PdfPTable createTable(int colNumber, int align) {PdfPTable table = new PdfPTable(colNumber);try {table.setTotalWidth(maxWidth);table.setLockedWidth(true);table.setHorizontalAlignment(align);table.getDefaultCell().setBorder(1);} catch (Exception e) {e.printStackTrace();}return table;}/*** 创建指定列宽、列数的表格** @param widths* @return*/public static PdfPTable createTable(float[] widths) {PdfPTable table = new PdfPTable(widths);try {table.setTotalWidth(maxWidth);table.setLockedWidth(true);table.setHorizontalAlignment(Element.ALIGN_CENTER);table.getDefaultCell().setBorder(1);} catch (Exception e) {e.printStackTrace();}return table;}/*** 创建空白的表格** @return*/public PdfPTable createBlankTable() throws IOException, DocumentException {BaseFont bfChinese = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);Font keyfont = new Font(bfChinese, 10, Font.BOLD);PdfPTable table = new PdfPTable(1);table.getDefaultCell().setBorder(0);table.addCell(createCell("", keyfont));table.setSpacingAfter(20.0f);table.setSpacingBefore(20.0f);return table;}
/**--------------------------创建表格的方法end----------------------------*//*** --------------------------页码方法start----------------------------*/public static void onEndPage(PdfWriter writer, Document document) throws IOException, DocumentException {PdfContentByte cb = writer.getDirectContent();PdfTemplate tpl; // 页码模板用来固定显示数据BaseFont bfChinese = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);tpl = writer.getDirectContent().createTemplate(100, 100);cb.saveState();String text = "第" + writer.getPageNumber() + "页";cb.beginText();cb.setFontAndSize(bfChinese, 8);cb.setTextMatrix(480, 35);//定位“第x页” 在具体的页面调试时候需要更改这xy的坐标cb.showText(text);cb.endText();//** 创建以及固定显示总页数的位置cb.addTemplate(tpl, 283, 10);//定位“y页” 在具体的页面调试时候需要更改这xy的坐标cb.stroke();cb.restoreState();cb.closePath();
/**--------------------------页码方法end----------------------------*/}
}

8.3 R.java

package com.example.net.demos.util;import java.io.Serializable;public class R<T> implements Serializable {/*** 成功*/public static final int SUCCESS = 200;/*** 失败*/public static final int FAIL = 500;private static final long serialVersionUID = 1L;private int code;private String msg;private T data;public static <T> R<T> ok() {return restResult(null, SUCCESS, "操作成功");}public static <T> R<T> ok(T data) {return restResult(data, SUCCESS, "操作成功");}public static <T> R<T> ok(T data, String msg) {return restResult(data, SUCCESS, msg);}public static <T> R<T> fail() {return restResult(null, FAIL, "操作失败");}public static <T> R<T> fail(String msg) {return restResult(null, FAIL, msg);}public static <T> R<T> fail(T data) {return restResult(data, FAIL, "操作失败");}public static <T> R<T> fail(T data, String msg) {return restResult(data, FAIL, msg);}public static <T> R<T> fail(int code, String msg) {return restResult(null, code, msg);}private static <T> R<T> restResult(T data, int code, String msg) {R<T> apiResult = new R<>();apiResult.setCode(code);apiResult.setData(data);apiResult.setMsg(msg);return apiResult;}public static <T> Boolean isError(R<T> ret) {return !isSuccess(ret);}public static <T> Boolean isSuccess(R<T> ret) {return R.SUCCESS == ret.getCode();}public int getCode() {return code;}public void setCode(int code) {this.code = code;}public String getMsg() {return msg;}public void setMsg(String msg) {this.msg = msg;}public T getData() {return data;}public void setData(T data) {this.data = data;}
}