1. Add dependencies first
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
Second, without further ado, just look at the Export Style Settings, you can adjust according to your actual situation:
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
public class EasyExcelUtils {
/**
* Set excel style
*/
public static HorizontalCellStyleStrategy getStyleStrategy() {
// head strategy style adjustment
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// header background light green
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
// initial font size
headWriteFont. setFontHeightInPoints((short) 12);
// font style
headWriteFont.setFontName("宋体");
headWriteCellStyle.setWriteFont(headWriteFont);
// wrap automatically
headWriteCellStyle.setWrapped(true);
// set thin border
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
// Set border color 25 grayscale
headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// horizontal alignment
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// vertical alignment
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// content strategy Arial
WriteCellStyle contentStyle = new WriteCellStyle();
// Set vertical center
contentStyle. setWrapped(true);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// set horizontal center
// contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
// content font size
contentWriteFont. setFontHeightInPoints((short) 12);
// font style
contentWriteFont.setFontName("宋体");
contentStyle.setWriteFont(contentWriteFont);
// This strategy is that the head is the style of the head, and the content is the style of the content. Other strategies can be implemented by yourself
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);
}
}
3. Adaptive column width
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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);
// Cell text length is greater than 60 newlines
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);
}
}
}
}
/**
* calculate the length
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
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:
// newline character (data needs to be parsed in advance)
int index = cellData.getStringValue().indexOf("\\
");
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;
}
}
}
}
}
4. Adaptive row height
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import java.util.Iterator;
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
/**
* default height
*/
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;
}
// Default is 1 row height
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) + "\\
" + value.substring((i + 1) * 50 + i, len + i);
}
}
if (value. contains("\\
")) {
int length = value. split("\\
"). length;
maxHeight = Math.max(maxHeight, length) + 1;
}
}
}
row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
}
}
5. Export
- Create the export entity class first
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.terton.aisp.ssp.entity.Feedback;
import lombok. AllArgsConstructor;
import lombok.Data;
import lombok. NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelIgnoreUnannotated // Only export fields with ExcelProperty annotations
public class ExcelFeedbackVO extends Feedback {
/**
* Site ID
*/
// Set the header name, it is best to bring the index, there will be no problem.
@ExcelProperty(value = "site id", index = 0)
private String siteId;
/**
* search content
*/
@ExcelProperty(value = "Search content", index = 1)
private String title;
/**
* Feedback content
*/
@ExcelProperty(value = "Feedback content", index = 2)
private String content;
/**
* IP address
*/
@ExcelProperty(value = "Search IP", index = 3)
private String ip;
}
2. Create an externally exposed interface.
@PostMapping("/downloadBack")
public void downloadBack(HttpServletResponse response, String siteId, Integer content) throws TException, IOException {
List<Feedback> list = feedbackService. searchBack(siteId, content);
setResponse(response, "list");
EasyExcel.write(response.getOutputStream(), ExcelFeedbackVO.class /* Reference the fields that need to be exported*/)
.sheet("List")
.registerWriteHandler(new CustomCellWriteWidthConfig()) /*adaptive column width*/
.registerWriteHandler(new CustomCellWriteHeighConfig()) /* Adaptive line height (choose to use according to your own situation, I don't use it here) */
.registerWriteHandler(EasyExcelUtils.getStyleStrategy()) /*reference style*/
.doWrite(list); /* If you want to export templates, just give an empty collection here*/
}
3. Set the response body information (because my project has many imports and exports, this method has become a public method)
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
public class SetResponse {
/**
* Export setting response information
*
* @param response response
*/
public static void setResponse(HttpServletResponse response,String fileName) {
// file name
String sheetName = URLEncoder.encode(fileName, StandardCharsets.UTF_8) + ".xlsx";
// contentType The type of response content
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// set character
response.setCharacterEncoding("utf-8");
// set the file name
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + sheetName);
}
}
4. Access interface
Templates
Export normally
Six. Import
1. Listener (key)
Note: The service cannot be injected into the listener, so the constructor can be used
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.terton.aisp.common.enums.ExcelNameModelEnum;
import com.terton.aisp.common.util.PingYinUtil;
import com.terton.aisp.word.entity.MassWord;
import com.terton.aisp.word.enums.UploadBatchCountEnum;
import com.terton.aisp.word.service.IMassWordService;
import com.terton.aisp.word.service.impl.MassWordServiceImpl;
import com.terton.aisp.word.vo.ExcelMassWordModelVO;
import com.terton.framework.util.StringUtil;
import lombok. AllArgsConstructor;
import lombok. NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
@Slf4j
@AllArgsConstructor
@NoArgsConstructor
public class UploadMassWordListener extends AnalysisEventListener<ExcelMassWordModelVO> {
private IMassWordService massWordService;
private String siteId;
private Integer wordLevel;
private String userName;
private List<ExcelMassWordModelVO> list = new ArrayList<>();
private List<String> massWordList = new ArrayList<>();
private List<String> massWords = new ArrayList<>();
private Integer successNum = 0;
private Integer errorNum = 0;
/**
* Header verification In this method, the format of the imported excel can be judged, first execute
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
// headMap header data, just take it out and make a judgment
}
/**
* Triggered every time a line is read
*
* @param modelVO"
* @param analysisContext "
*/
@Override
public void invoke(ExcelMassWordModelVO modelVO, AnalysisContext analysisContext) {
log.info("Read a piece of data: {}",modelVO)
// You can judge whether the data is valid. Judging according to your actual business logic,
// Judgment completed data can be received with a List
// In order to prevent too much data, the data is always stored in the memory. A threshold can be set here. When the data in the list exceeds this value, the batch of data will be added to the database first.
if (StringUtil.notBlankAndNull(modelVO.getMassWord()) & amp; & amp; StringUtil.notBlankAndNull(modelVO.getTargetWord())) {
if (!CollectionUtils. isEmpty(massWords) & amp; & amp; massWords. contains(modelVO. getMassWord())) {
throw new RuntimeException(ExcelNameModelEnum.FailImport.RESULT_FIELD.getText() + modelVO.getMassWord());
}
massWords.add(modelVO.getMassWord());
massWordList.add(modelVO.getMassWord());
list.add(modelVO);
try {
if (list.size() >= UploadBatchCountEnum.MASS_WORD.getCount() /*I set the threshold here to be 500*/) {
save();
}
} catch (Exception e) {
throw new RuntimeException(e. getMessage());
}
}
}
/**
* complete trigger
*
* @param analysisContext "
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// write to database
if (!CollectionUtils. isEmpty(list)) {
save();
}
massWords. clear();
log.info("Number of successful writes: {}", successNum);
log.info("Number of failed writes: {}", errorNum);
successNum = 0;
errorNum = 0;
}
private void save() {
try {
List<MassWord> recordList = massWordService.getByMasswordsAndSiteId(massWordList, siteId);
if (!CollectionUtils. isEmpty(recordList)) {
List<String> collect = recordList. stream(). map(MassWord::getMassWord). collect(Collectors. toList());
if (!CollectionUtils. isEmpty(collect)) {
throw new RuntimeException(ExcelNameModelEnum.FailImport.RESULT_FIELD.getText() + collect);
}
}
for (ExcelMassWordModelVO wordModelVO : list) {
MassWord newMassWord = getMassWord(wordModelVO);
massWordService.save(newMassWord);
successNum += 1;
}
} catch (Exception e) {
errorNum + = 1;
throw new RuntimeException(e. getMessage());
}
massWordList. clear();
list. clear();
}
private MassWord getMassWord(ExcelMassWordModelVO wordModelVO) {
MassWord massWord = new MassWord();
massWord.setMassWord(wordModelVO.getMassWord());
massWord.setTargetWord(wordModelVO.getTargetWord());
massWord.setWordLevel(wordLevel);
massWord.setSiteId(siteId);
massWord.setAcronym(PingYinUtil.getFirstSpell(wordModelVO.getMassWord()));
massWord.setFullPinYin(PingYinUtil.getFullSpell(wordModelVO.getMassWord()));
massWord.setCrTime(new Date());
massWord.setCrUser(userName);
return massWord;
}
public UploadMassWordListener(MassWordServiceImpl massWordService, String siteId, Integer wordLevel, String userName) {
this.massWordService = massWordService;
this.siteId = siteId;
this. wordLevel = wordLevel;
this. userName = userName;
}
}
2. Import and use of listeners.
public void upload(MultipartFile file, String siteId, Integer wordLevel, String userName) throws IOException {
UploadMassWordListener listener = new UploadMassWordListener(this, siteId, wordLevel, userName);
EasyExcel.read(file.getInputStream(), ExcelMassWordModelVO.class, listener)
.sheet(0)
.headRowNumber(1)
.doRead();
}
This is probably the end here, I hope it is useful to you.