先引入依赖
< ! -- poi start -- >
< dependency>
< groupId> org. apache. poi< / groupId>
< artifactId> poi< / artifactId>
< version> 4.1 .2 < / version>
< / dependency>
< dependency>
< groupId> org. apache. poi< / groupId>
< artifactId> poi- ooxml< / artifactId>
< version> 4.1 .2 < / version>
< / dependency>
< dependency>
< groupId> org. apache. poi< / groupId>
< artifactId> poi- ooxml- schemas< / artifactId>
< version> 4.1 .2 < / version>
< / dependency>
< ! -- poi end -- >
封装PageUtil 工具类
public class PageUtil {
public static int count ( int rows, int eachPageSize) {
int pageCount = ( rows- 1 ) / eachPageSize+ 1 ;
return pageCount;
}
public static Map< String, Object> page ( Integer whichPage, Integer pageCount) {
if ( whichPage == null || pageCount == null) {
return null;
}
Map< String, Object> result = new HashMap < > ( ) ;
int rowsStart = ( whichPage - 1 ) * pageCount;
result. put ( "rowsStart" , rowsStart) ;
result. put ( "pageCount" , pageCount) ;
return result;
}
}
controller
@Resource
Testmapper testmapper;
@RequestMapping ( "/daochu" )
public void daochu ( @RequestParam ( value = "filename" , defaultValue = "测试导出" ) String filename,
@RequestParam ( value = "sheetName" , defaultValue = "sheet" ) String sheetName,
@RequestParam ( value = "title" , defaultValue = "主键id,随机UUID,名字,性别,地址,身份证号," +
"昵称,昵称2,昵称3,昵称4,昵称5,昵称6,昵称7,昵称8,昵称9,昵称10" ) String title,
@RequestParam ( value = "titleColumn" , defaultValue = "id,uid,name,sex,address,idcard," +
"nickname,nickname2,nickname3,nickname4,nickname5,nickname6,nickname7,nickname8," +
"nickname9,nickname10" ) String titleColumn,
HttpServletRequest request,
HttpServletResponse response) throws IOException {
exportExcel ( filename, sheetName, title, titleColumn, request, response) ;
}
public void exportExcel ( String filename, String sheetName,
String title, String titleColumn,
HttpServletRequest request, HttpServletResponse response) {
String titleGroup[ ] = title. split ( "," ) ;
String titleColumnGroup[ ] = titleColumn. split ( "," ) ;
if ( StringUtils. isEmpty ( filename) ) {
System. out. println ( "文件名为空" ) ;
return ;
}
int everySheetCount = 100000 * 10 ;
int eachPageSize = 10000 ;
filename = filename + ".xlsx" ;
int sum = testmapper. sums ( ) ;
int count = new PageUtil ( ) . count ( sum, eachPageSize) ;
SXSSFWorkbook wb = null;
try {
long startTime = System. currentTimeMillis ( ) ;
wb = new SXSSFWorkbook ( ) ;
wb. setCompressTempFiles ( false ) ;
Sheet sheet = null;
int sheetNumber = 1 ;
Row row;
Cell cell;
int whichRow = 0 ;
List< Map< String, Object> > Pagelist = new ArrayList < > ( ) ;
for ( int rowNumber = 1 ; rowNumber <= count; rowNumber++ ) {
Map< String, Object> page = PageUtil. page ( rowNumber, eachPageSize) ;
Pagelist = testmapper. pagelist ( page) ;
for ( int i = 0 ; i< Pagelist. size ( ) ; i++ ) {
if ( ( whichRow) % everySheetCount == 0 ) {
sheet = wb. createSheet ( sheetName+ sheetNumber) ;
whichRow = 0 ;
sheetNumber++ ;
row = sheet. createRow ( whichRow) ;
CellStyle style = this . rowHeadStyle ( wb) ;
for ( int cellNumber = 0 ; cellNumber < titleGroup. length; cellNumber++ ) {
cell = row. createCell ( cellNumber) ;
cell. setCellStyle ( style) ;
cell. setCellValue ( titleGroup[ cellNumber] ) ;
}
whichRow++ ;
}
row = sheet. createRow ( whichRow) ;
System. err. println ( "创建第 " + whichRow+ " 行" ) ;
for ( int cellNumber = 0 ; cellNumber < titleColumnGroup. length; cellNumber++ ) {
cell = row. createCell ( cellNumber) ;
cell. setCellValue ( Pagelist. get ( i) . get ( titleColumnGroup[ cellNumber] ) . toString ( ) ) ;
}
whichRow ++ ;
}
Pagelist. clear ( ) ;
}
download1 ( filename, wb, request, response) ;
long endTime = System. currentTimeMillis ( ) ;
System. out. println ( "process " + sum + " spent time:" + ( endTime - startTime) + " ms." ) ;
} catch ( Exception ex) {
ex. printStackTrace ( ) ;
} finally {
if ( wb != null) {
wb. dispose ( ) ;
}
try {
if ( wb != null) wb. close ( ) ;
} catch ( IOException e) {
e. printStackTrace ( ) ;
}
}
}
public CellStyle rowHeadStyle ( SXSSFWorkbook wb) {
CellStyle style = wb. createCellStyle ( ) ;
style. setAlignment ( HorizontalAlignment. CENTER) ;
style. setVerticalAlignment ( VerticalAlignment. CENTER) ;
style. setFillPattern ( FillPatternType. SOLID_FOREGROUND) ;
style. setFillForegroundColor ( IndexedColors. CORNFLOWER_BLUE. getIndex ( ) ) ;
Font font = wb. createFont ( ) ;
font. setBold ( true ) ;
font. setFontHeightInPoints ( ( short ) 11 ) ;
font. setColor ( Font. COLOR_NORMAL) ;
font. setFontName ( "宋体" ) ;
style. setFont ( font) ;
return style;
}
public void download1 ( String filename, SXSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws IOException {
ServletOutputStream out1 = response. getOutputStream ( ) ;
try {
response. reset ( ) ;
response. setContentType ( "application/msexcel;charset=utf-8" ) ;
String userAgent = request. getHeader ( "user-agent" ) ;
if ( userAgent != null && userAgent. indexOf ( "Edge" ) >= 0 ) {
filename = URLEncoder. encode ( filename, "UTF8" ) ;
} else if ( userAgent. indexOf ( "Firefox" ) >= 0 || userAgent. indexOf ( "Chrome" ) >= 0
|| userAgent. indexOf ( "Safari" ) >= 0 ) {
filename = new String ( ( filename) . getBytes ( StandardCharsets. UTF_8) , "ISO8859-1" ) ;
} else {
filename = new String ( filename. getBytes ( ) , "ISO8859-1" ) ;
}
response. setHeader ( "content-disposition" , "attachment;filename=" + filename) ;
wb. write ( out1) ;
out1. flush ( ) ;
} catch ( Exception e) {
} finally {
out1. close ( ) ;
}
}
mapper 接口
@Mapper
public interface Testmapper {
int sums ( ) ;
List< Map< String, Object> > pagelist ( @Param ( "page" ) Map< String, Object> page) ;
}
mapper.xml
< select id = " sums" resultType = " Integer" >
select count(1) from testuser_copy ;
</ select>
< select id = " pagelist" resultType = " java.util.HashMap" >
select
id,
uid,
name,
sex,
address,
idcard,
nickname,
nickname2,
nickname3,
nickname4,
nickname5,
nickname6,
nickname7,
nickname8,
nickname9,
nickname10
from testuser_copy
limit ${page.rowsStart},${page.pageCount}
</ select>