public class CustomCellShellWriteConfig implements SheetWriteHandler {
private static final Integer COLUMN = 100 ;
@Override
public void beforeSheetCreate ( WriteWorkbookHolder wbh, WriteSheetHolder wsh) {
SheetWriteHandler . super . beforeSheetCreate ( wbh, wsh) ;
}
@Override
public void afterSheetCreate ( WriteWorkbookHolder wbh, WriteSheetHolder wsh) {
for ( int i = 0 ; i < COLUMN ; i++ ) {
SXSSFSheet sheet = ( SXSSFSheet ) wsh. getSheet ( ) ;
CellStyle cellStyle = wbh. getWorkbook ( ) . createCellStyle ( ) ;
cellStyle. setDataFormat ( ( short ) 49 ) ;
sheet. setDefaultColumnStyle ( i, cellStyle) ;
}
}
}
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
private static final Integer DEFAULT_HEIGHT = 300 ;
@Override
protected void setHeadColumnHeight ( Row row, int relativeRowIndex) {
}
@Override
protected void setContentColumnHeight ( Row row, int relativeRowIndex) {
Iterator < Cell > cellIterator = row. cellIterator ( ) ;
if ( ! cellIterator. hasNext ( ) ) {
return ;
}
int maxHeight = 1 ;
while ( cellIterator. hasNext ( ) ) {
Cell cell = cellIterator. next ( ) ;
if ( cell. getCellTypeEnum ( ) == CellType . STRING ) {
String value = cell. getStringCellValue ( ) ;
int len = value. length ( ) ;
int num = 0 ;
if ( len > 50 ) {
num = len % 50 > 0 ? len / 50 : len / 2 - 1 ;
}
if ( num > 0 ) {
for ( int i = 0 ; i < num; i++ ) {
value = value. substring ( 0 , ( i + 1 ) * 50 + i) + "\n" + value. substring ( ( i + 1 ) * 50 + i, len + i) ;
}
}
if ( value. contains ( "\n" ) ) {
int length = value. split ( "\n" ) . length;
maxHeight = Math . max ( maxHeight, length) + 1 ;
}
}
}
row. setHeight ( ( short ) ( ( maxHeight) * DEFAULT_HEIGHT ) ) ;
}
}
public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {
private final Map < Integer , Map < Integer , Integer > > CACHE = new HashMap < > ( ) ;
@Override
protected void setColumnWidth ( WriteSheetHolder writeSheetHolder, List < WriteCellData < ? > > cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || ! CollectionUtils . isEmpty ( cellDataList) ;
if ( needSetWidth) {
Map < Integer , Integer > maxColumnWidthMap = CACHE . computeIfAbsent ( writeSheetHolder. getSheetNo ( ) , k -> new HashMap < > ( ) ) ;
Integer columnWidth = this . dataLength ( cellDataList, cell, isHead) ;
if ( columnWidth >= 0 ) {
if ( columnWidth > 60 ) {
columnWidth = 60 ;
}
Integer maxColumnWidth = maxColumnWidthMap. get ( cell. getColumnIndex ( ) ) ;
if ( maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap. put ( cell. getColumnIndex ( ) , columnWidth) ;
Sheet sheet = writeSheetHolder. getSheet ( ) ;
sheet. setColumnWidth ( cell. getColumnIndex ( ) , columnWidth * 256 ) ;
}
}
}
}
private Integer dataLength ( List < WriteCellData < ? > > cellDataList, Cell cell, Boolean isHead) {
if ( isHead) {
return cell. getStringCellValue ( ) . getBytes ( ) . length;
} else {
CellData < ? > cellData = cellDataList. get ( 0 ) ;
CellDataTypeEnum type = cellData. getType ( ) ;
if ( type == null ) {
return - 1 ;
} else {
switch ( type) {
case STRING :
int index = cellData. getStringValue ( ) . indexOf ( "\n" ) ;
return index != - 1 ?
cellData. getStringValue ( ) . substring ( 0 , index) . getBytes ( ) . length + 1 : cellData. getStringValue ( ) . getBytes ( ) . length + 1 ;
case BOOLEAN :
return cellData. getBooleanValue ( ) . toString ( ) . getBytes ( ) . length;
case NUMBER :
return cellData. getNumberValue ( ) . toString ( ) . getBytes ( ) . length;
default :
return - 1 ;
}
}
}
}
}
public class EasyExcelUtils {
public static HorizontalCellStyleStrategy getStyleStrategy ( ) {
WriteCellStyle headWriteCellStyle = new WriteCellStyle ( ) ;
headWriteCellStyle. setFillForegroundColor ( IndexedColors . GREY_25_PERCENT . getIndex ( ) ) ;
WriteFont headWriteFont = new WriteFont ( ) ;
headWriteFont. setFontHeightInPoints ( ( short ) 12 ) ;
headWriteFont. setFontName ( "宋体" ) ;
headWriteCellStyle. setWriteFont ( headWriteFont) ;
headWriteCellStyle. setWrapped ( true ) ;
headWriteCellStyle. setBorderBottom ( BorderStyle . THIN ) ;
headWriteCellStyle. setBorderLeft ( BorderStyle . THIN ) ;
headWriteCellStyle. setBorderRight ( BorderStyle . THIN ) ;
headWriteCellStyle. setBorderTop ( BorderStyle . THIN ) ;
headWriteCellStyle. setBottomBorderColor ( IndexedColors . BLACK . getIndex ( ) ) ;
headWriteCellStyle. setTopBorderColor ( IndexedColors . BLACK . getIndex ( ) ) ;
headWriteCellStyle. setLeftBorderColor ( IndexedColors . BLACK . getIndex ( ) ) ;
headWriteCellStyle. setRightBorderColor ( IndexedColors . BLACK . getIndex ( ) ) ;
headWriteCellStyle. setHorizontalAlignment ( HorizontalAlignment . CENTER ) ;
headWriteCellStyle. setVerticalAlignment ( VerticalAlignment . CENTER ) ;
WriteCellStyle contentStyle = new WriteCellStyle ( ) ;
contentStyle. setWrapped ( true ) ;
contentStyle. setVerticalAlignment ( VerticalAlignment . CENTER ) ;
WriteFont contentWriteFont = new WriteFont ( ) ;
contentWriteFont. setFontHeightInPoints ( ( short ) 12 ) ;
contentWriteFont. setFontName ( "宋体" ) ;
contentStyle. setWriteFont ( contentWriteFont) ;
return new HorizontalCellStyleStrategy ( headWriteCellStyle, contentStyle) ;
}
}
public void exportTemplate ( HttpServletResponse response) throws IOException {
try {
response. setContentType ( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ) ;
response. setCharacterEncoding ( "utf-8" ) ;
String fileName = URLEncoder . encode ( "表格" + System . currentTimeMillis ( ) , "UTF-8" ) . replaceAll ( "\\+" , "%20" ) ;
response. setHeader ( "Content-disposition" , "attachment;filename*=utf-8''" + fileName + ".xlsx" ) ;
try ( ExcelWriter excelWriter = EasyExcel . write ( response. getOutputStream ( ) , ImportDeviceToTaskByExcelBO . class ) . autoCloseStream ( Boolean . FALSE ) . build ( ) ) {
WriteSheet writeSheet = EasyExcel . writerSheet ( 1 , "sheet" )
. registerWriteHandler ( new CustomCellWriteWidthConfig ( ) )
. registerWriteHandler ( new CustomCellWriteHeightConfig ( ) )
. registerWriteHandler ( EasyExcelUtils . getStyleStrategy ( ) )
. registerWriteHandler ( new CustomCellShellWriteConfig ( ) )
. build ( ) ;
List < ImportDeviceToTaskByExcelBO > deviceListForExcel = null ;
excelWriter. write ( deviceListForExcel, writeSheet) ;
}
} catch ( Exception e) {
response. reset ( ) ;
response. setContentType ( "application/json" ) ;
response. setCharacterEncoding ( "utf-8" ) ;
Map < String , String > map = MapUtils . newHashMap ( ) ;
map. put ( "status" , "failure" ) ;
map. put ( "message" , "下载文件失败: " + e. getMessage ( ) ) ;
response. getWriter ( ) . println ( JSON . toJSONString ( map) ) ;
}
}