You can download the jar package by yourself, springMvc4.2 + version + poi-3.10.1 version
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10.1</version>
</dependency>
1. After 4.2, AbstractXlsxView and AbstractXlsxStreamingView are used instead of the original AbstractExcelView to build the view downloaded from excel
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractXlsxStreamingView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;
public class MpManageExcelView extends AbstractXlsxStreamingView {
@Override
protected SXSSFWorkbook createWorkbook(Map<String, Object> model, HttpServletRequest request) {
List<?> data = (List<?>) model.get("data");
if(CollectionUtils.isNotEmpty(data)) {
return ExcelExportUtil.export(data);
}
return super.createWorkbook(model, request);
}
@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
String filename = model.get("fileName") + ".xlsx"; // Get the name of the client Excel when downloading
//Process Chinese filename
String reqCharset = request.getCharacterEncoding(); /*Get the code of the request according to the getCharacterEncoding of the request*/
filename = new String(filename.getBytes(reqCharset), "ISO8859-1");
response.setCharacterEncoding(reqCharset);
// If you want to fill in the file name automatically when downloading, you need to set the "content disposition" of the response header
response.setHeader("Content-disposition", "attachment;filename=" + filename);
}
}
In fact, the method is obvious. Rewrite buildExcelDocument, modify and assign the model object and the workbook object in poi, and finally output the response. Some poi operations of createsheet and row can be performed on the workbook in the method. You can also override the createWorkbook in the parent class to create your own workbook object, as in the example. The original logic of the parent class creates an empty workbook object for the build method to use
*/
public abstract class AbstractXlsxStreamingView extends AbstractXlsxView {
/**
* This implementation creates a {@link SXSSFWorkbook} for streaming the XLSX format.
*/
@Override
protected SXSSFWorkbook createWorkbook(Map<String, Object> model, HttpServletRequest request) {
return new SXSSFWorkbook();
}
Of course, when downloading, the whole logic has been defined in the way of template mode in the parent class AbstractXlsxView, defining the general business logic and skeleton of the method, and rewriting by the child class itself to realize its own logic
@Override
protected final void renderMergedOutputModel(
Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
// Create a fresh workbook instance for this render step.
Workbook workbook = createWorkbook(model, request);
// Delegate to application-provided document code.
buildExcelDocument(model, workbook, request, response);
// Set the content type.
response.setContentType(getContentType());
// Flush byte array to servlet output stream.
renderWorkbook(workbook, response);
}
The rest is the code of some tool classes and actual controller classes:
Excel tool class:
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ExcelExportUtil {
private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);
private static final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
private static final int MAX_SHEET_ROWS = 60000;
public static SXSSFWorkbook export(List<?> dataList){
// data
if (dataList==null || dataList.size()==0) {
throw new RuntimeException("data is empty,can not generate excel file");
}
//Get object type annotation
Class<?> targetClass = dataList.get(0).getClass();
String sheetName = targetClass.getSimpleName();
ExcelSheet excelSheet = targetClass.getAnnotation(ExcelSheet.class);
if(excelSheet != null && StringUtils.isNotBlank(excelSheet.name())){
sheetName = excelSheet.name();
}
// Analytic field
List<Field> fields = new ArrayList<Field>();
if (targetClass.getDeclaredFields()!=null && targetClass.getDeclaredFields().length>0) {
for (Field field: targetClass.getDeclaredFields()) {
if (Modifier.isStatic(field.getModifiers())) {
continue;
}
fields.add(field);
}
}
if (fields==null || fields.size()==0) {
throw new RuntimeException("data field can not be empty");
}
SXSSFWorkbook wb = new SXSSFWorkbook(100);
int dataSize = dataList.size();
int index = 0;
do{
List<?> subList = dataList.subList(index,Math.min(dataSize,index+MAX_SHEET_ROWS));
resolveSheet(wb,fields,subList,sheetName+"-"+index);
index += MAX_SHEET_ROWS;
}while (index < dataSize);
return wb;
}
/**
* Process a certain number of sheet s
* @param workbook
* @param dataList
*/
private static void resolveSheet(Workbook workbook,List<Field> fields,List<?> dataList,String sheetName){
Sheet sheet = workbook.createSheet(sheetName);
//head
Row headRow = sheet.createRow(0);
CellStyle headStyle = generateHeadStyle(workbook);
for (int i = 0; i < fields.size(); i++) {
Field field = fields.get(i);
ExcelField excelField = field.getAnnotation(ExcelField.class);
String fieldName = field.getName();
if(excelField != null && StringUtils.isNotBlank(excelField.column())){
fieldName = excelField.column();
}
Cell cellX = headRow.createCell(i, Cell.CELL_TYPE_STRING);
cellX.setCellStyle(headStyle);
cellX.setCellValue(String.valueOf(fieldName));
}
//data
for (int dataIndex = 0; dataIndex < dataList.size(); dataIndex++) {
int rowIndex = dataIndex+1;
Object rowData = dataList.get(dataIndex);
Row rowX = sheet.createRow(rowIndex);
for (int i = 0; i < fields.size(); i++) {
Field field = fields.get(i);
try {
field.setAccessible(true);
Object fieldValue = field.get(rowData);
Cell cellX = rowX.createCell(i, Cell.CELL_TYPE_STRING);
cellX.setCellValue(generateValue(fieldValue));
} catch (IllegalAccessException e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
}
}
}
}
/**
*
* @param obj
* @return
*/
private static String generateValue(Object obj){
if(obj == null) return StringUtils.EMPTY;
if(obj instanceof Date){
return df.format(obj);
}
return String.valueOf(obj);
}
/**
* Generate head style
* @param wb
* @return
*/
private static CellStyle generateHeadStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
/*****************************Use default color**************************************************/
// Fill color
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(CellStyle.BIG_SPOTS);
// Font color
Font font = wb.createFont();
font.setColor(IndexedColors.BLACK.getIndex());
style.setFont(font);
return style;
}
}
controller:
@RequestMapping(value = "/download", method = RequestMethod.GET)
public ModelAndView download(@RequestParam String beginTime,
@RequestParam String endTime) {
List<MpPrizeExportVo> result = manageFade.export(beginTime,endTime);
Map<String,Object> map = new HashMap<>();
map.put("fileName","Data downloading-"+beginTime+"-"+endTime);
map.put("data",result);
return new ModelAndView(new MpManageExcelView(),map);
}