查询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>
< dependency>
< groupId> com.itextpdf</ groupId>
< artifactId> itextpdf</ artifactId>
< version> 5.5.13</ version>
</ dependency>
< 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>
< 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 {
@Autowired
UserMapper userMapper;
@Override
public 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 ) ;
@Autowired
UserService userService;
@PostMapping ( "/list" )
public R selectList ( ) {
List < User > users = userService. selectAll ( ) ;
logger. info ( users. toString ( ) ) ;
return R . ok ( users) ;
}
@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 {
Document document = new Document ( PageSize . A4 . rotate ( ) ) ;
String savePath= destination+ "/" + "user_" + currentTime+ ".pdf" ;
File file = new File ( savePath) ;
file. createNewFile ( ) ;
PdfWriter writer = PdfWriter . getInstance ( document, new FileOutputStream ( file) ) ;
document. open ( ) ;
Paragraph paragraph = new Paragraph ( "用户表" , titlefont_16) ;
paragraph. setAlignment ( 1 ) ;
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 ;
}
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 ( ) ;
}
}
@PostMapping ( "/excel" )
public void downloadExcel ( HttpServletResponse response) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook ( ) ;
HSSFSheet sheet = workbook. createSheet ( "用户表" ) ;
List < User > list= userService. selectAll ( ) ;
String fileName= "user" + ".xls" ;
int rowNum= 1 ;
String [ ] headers= { "ID" , "姓名" , "年龄" , "住址" , "住址2" } ;
HSSFRow row = sheet. createRow ( 0 ) ;
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;
@Override
public 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 ( 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 ) ;
this . totalCount = ( length) ;
this . pageSize = ( size) ;
this . content = ( list1) ;
this . count = ( list1. size ( ) ) ;
this . 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 ;
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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
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 ) ;
cb. showText ( text) ;
cb. endText ( ) ;
cb. addTemplate ( tpl, 283 , 10 ) ;
cb. stroke ( ) ;
cb. restoreState ( ) ;
cb. closePath ( ) ;
}
}
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;
}
}