maven 依赖
< 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.apache.poi</ groupId>
< artifactId> poi-ooxml-schemas</ artifactId>
< version> 3.17</ version>
</ dependency>
工具类
package com. base. utils ;
import lombok. extern. slf4j. Slf4j ;
import org. apache. poi. ss. usermodel. * ;
import org. apache. poi. xssf. usermodel. XSSFWorkbook ;
import org. springframework. util. CollectionUtils ;
import org. springframework. web. multipart. MultipartFile ;
import javax. servlet. ServletOutputStream ;
import javax. servlet. http. HttpServletResponse ;
import java. beans. BeanInfo ;
import java. beans. Introspector ;
import java. beans. MethodDescriptor ;
import java. beans. PropertyDescriptor ;
import java. io. InputStream ;
import java. lang. reflect. Method ;
import java. nio. charset. StandardCharsets ;
import java. util. * ;
import java. util. regex. Pattern ;
@Slf4j
public class ExcelUtil {
public static < T > List < T > importExcelByFile ( MultipartFile file,
Map < Integer , String > excelRowToTitleMap,
Map < String , String > excelTitleToPojoMap,
List < String > sheetList, Integer startRow, Class < T > clazz) {
List < T > list = new ArrayList < > ( ) ;
try ( InputStream fis = file. getInputStream ( ) ) {
list = importExcel ( fis, excelRowToTitleMap, excelTitleToPojoMap, sheetList, startRow, clazz) ;
} catch ( Exception e) {
log. info ( "import excel exception:{}" , e. getMessage ( ) ) ;
}
return list;
}
public static < T > List < T > importExcel ( InputStream inputStream,
Map < Integer , String > excelRowToTitleMap,
Map < String , String > excelTitleToPojoMap,
List < String > sheetList, Integer startRow, Class < T > clazz) {
Set < Integer > rows = excelRowToTitleMap. keySet ( ) ;
List < T > list = new ArrayList < > ( ) ;
try ( Workbook workbook = WorkbookFactory . create ( inputStream) ) {
for ( Sheet sheet : workbook) {
String sheetName = sheet. getSheetName ( ) ;
if ( sheetList != null && ! sheetList. contains ( sheetName) ) {
continue ;
}
for ( Row row : sheet) {
if ( row. getRowNum ( ) + 1 < startRow) {
continue ;
}
T obj = clazz. newInstance ( ) ;
for ( int i = 0 ; i < row. getLastCellNum ( ) ; i++ ) {
Cell cell = row. getCell ( i) ;
String cellValue = cell != null ? cell. toString ( ) : null ;
if ( rows. contains ( i + 1 ) ) {
String rowTitleName = excelRowToTitleMap. get ( i + 1 ) ;
String propertyName = excelTitleToPojoMap. get ( rowTitleName) ;
if ( propertyName != null ) {
PropertyDescriptor propertyDescriptor = new PropertyDescriptor ( propertyName, clazz) ;
Method writeMethod = propertyDescriptor. getWriteMethod ( ) ;
if ( writeMethod != null ) {
Class < ? > propertyType = propertyDescriptor. getPropertyType ( ) ;
Object value = convertValue ( cellValue, propertyType) ;
writeMethod. invoke ( obj, value) ;
}
}
}
}
list. add ( obj) ;
}
}
} catch ( Exception e) {
log. info ( "import excel exception:{}" , e. getMessage ( ) ) ;
}
return list;
}
public static void exportListToExcel ( List < ? > list, Class obj, String [ ] header, String [ ] rows, String fileName, HttpServletResponse response) {
try {
Workbook book = new XSSFWorkbook ( ) ;
Sheet sheet = book. createSheet ( ) ;
List < Method > methods = new LinkedList < > ( ) ;
if ( rows == null || rows. length == 0 ) {
BeanInfo beanInfo = Introspector . getBeanInfo ( obj) ;
MethodDescriptor [ ] methodDescriptors = beanInfo. getMethodDescriptors ( ) ;
for ( int i = 0 ; i < methodDescriptors. length; i++ ) {
String method = methodDescriptors[ i] . getName ( ) ;
if ( Pattern . matches ( "[g][e][t]\\w*" , method) && ! "getClass" . equals ( method) ) {
methods. add ( methodDescriptors[ i] . getMethod ( ) ) ;
}
}
} else {
for ( int i = 0 ; i < rows. length; i++ ) {
PropertyDescriptor propertyDescriptor = new PropertyDescriptor ( rows[ i] , obj) ;
methods. add ( propertyDescriptor. getReadMethod ( ) ) ;
}
}
if ( header == null || header. length == 0 ) {
header = new String [ methods. size ( ) ] ;
for ( int i = 0 ; i < header. length; i++ ) {
String methodName = methods. get ( i) . getName ( ) ;
header[ i] = methodName. substring ( 3 , 4 ) . toLowerCase ( ) + methodName. substring ( 4 ) ;
}
}
Row headerRow = sheet. createRow ( 0 ) ;
CellStyle headerStyle = getHeaderStyle ( book) ;
setHeaderRow ( header, headerRow, headerStyle) ;
int sheetFlag = 0 ;
if ( ! CollectionUtils . isEmpty ( list) ) {
for ( int i = 0 ; i < list. size ( ) ; i++ ) {
if ( ( i+ 1 ) % 1048575 == 0 ) {
sheet = book. createSheet ( "sheet" + sheetFlag) ;
headerRow = sheet. createRow ( 0 ) ;
setHeaderRow ( header, headerRow, headerStyle) ;
sheetFlag ++ ;
}
Row row = sheet. createRow ( sheet. getLastRowNum ( ) + 1 ) ;
Object o = list. get ( i) ;
for ( int j = 0 ; j < methods. size ( ) ; j++ ) {
Object value = methods. get ( j) . invoke ( o) ;
row. createCell ( j) . setCellValue ( value != null ? value. toString ( ) : "-" ) ;
}
}
}
response. setContentType ( "application/vnd.ms-excel;charset=utf-8" ) ;
response. setCharacterEncoding ( "utf-8" ) ;
response. setHeader ( "Content-Disposition" , "attachment;filename=" + new String ( fileName. getBytes ( StandardCharsets . UTF_8 ) , "ISO8859-1" ) + ".xlsx" ) ;
ServletOutputStream outputStream = response. getOutputStream ( ) ;
book. write ( outputStream) ;
outputStream. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
}
private static Object convertValue ( String cellValue, Class < ? > targetType) {
if ( cellValue == null || targetType == null ) {
return null ;
}
if ( String . class . equals ( targetType) ) {
return cellValue;
} else if ( Integer . class . equals ( targetType) || int . class . equals ( targetType) ) {
return Integer . valueOf ( cellValue) ;
} else if ( Double . class . equals ( targetType) || double . class . equals ( targetType) ) {
return Double . valueOf ( cellValue) ;
} else if ( Boolean . class . equals ( targetType) || boolean . class . equals ( targetType) ) {
return Boolean . valueOf ( cellValue) ;
}
return cellValue;
}
private static void setHeaderRow ( String [ ] header, Row headerRow, CellStyle headerStyle) {
for ( int i = 0 ; i < header. length; i++ ) {
Cell headerCell = headerRow. createCell ( i) ;
headerCell. setCellValue ( header[ i] ) ;
headerCell. setCellStyle ( headerStyle) ;
}
}
private static CellStyle getHeaderStyle ( Workbook book) {
CellStyle headerStyle = book. createCellStyle ( ) ;
Font headerFont = book. createFont ( ) ;
headerFont. setBold ( true ) ;
headerFont. setFontName ( "表头加粗字体" ) ;
headerStyle. setFont ( headerFont) ;
return headerStyle;
}
}