Tip: After the article is written, the table of contents can be automatically generated. For how to generate it, please refer to the help document on the right.
Article directory
- Preface
- 1. Dependence
- 2. Export to Excel
-
- 2.1. Basic export content
- 2.2. Simple style
- 2.3. Row height and column width
- 2.4. Column merging
- 2.5. Freeze window
- 3. Import Excel table
-
- 3.1. Import information
Foreword
Official website document: https://easyexcel.opensource.alibaba.com/
This article does not give an overview of the advantages and disadvantages. The official website is very clear. This article directly starts the code and operates the java code to realize the export (style adjustment), import and other operations of the table
1. Dependence
Add maven dependency, the minimum version of poi dependency is 3.17
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
2. Export Excel
2.1. Basic export content
The code is as follows (example):
package com.easyexcel.easyexcel.service;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.easyexcel.easyexcel.util.CustomRowHeightStyleStrategy;
import com.easyexcel.easyexcel.util.EasyExcelUtils;
import com.easyexcel.easyexcel.util.LongestCellWidthHandlerProject;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class importTemplate {<!-- -->
public static void main(String[] args) {<!-- -->
//Define the location where the computer needs to be saved
String path = "E:\exercise\Excel\test.xlsx";
//Construct header content============================================ ===========================================
//Define simple data. The data here is only used in the table header.
String[][] headCol = new String[][]{<!-- -->
{<!-- -->"Header 1"},
{<!-- -->"Top 2"},
{<!-- -->"Top three"},
};
//Define the data format required for the header
List<List<String>> headList = new ArrayList<>();
//Traverse the two-digit array, modify the format and add it
Arrays.stream(headCol).forEach(item -> headList.add(Arrays.asList(item)));
//Construct the following content of the table============================================ ===========================================
//Similar to table header, define simple data
String[][] contextCol = new String[][]{<!-- -->
{<!-- -->"A-A", "B-A", "C-A"},
{<!-- -->"A-B", "B-B", "C-B"},
{<!-- -->"A-C", "B-C", "C-C"},
{<!-- -->"A-ding", "B-ding", "C-ding"},
};
List<List<Object>> dataList = new ArrayList<>();
Arrays.stream(contextCol).forEach(item -> dataList.add(Arrays.asList(item)));
//Build table main style============================================ ===========================================
ExcelWriter writer = EasyExcelFactory.write(path)
.inMemory(Boolean.TRUE)
.build();
//Define the sheet below the table
WriteSheet sheet1 = new WriteSheet();
sheet1.setSheetName("sheet name 1");
sheet1.setSheetNo(0);
WriteTable writeTable = EasyExcel.writerTable(0).head(headList)
.needHead(true)
.build();
writer.write(dataList, sheet1, writeTable);
//closure
writer.finish();
writer.close();
System.out.println("Export successful...");
}
}
After the code is run, the code definition save location can be seen
Table style, the content is displayed, and the style is slowly adjusted later.
2.2, Simple style
In the above code, add a registerWriteHandler handler and create a new tool class
WriteTable writeTable = EasyExcel.writerTable(0).head(headList)
.registerWriteHandler(EasyExcelUtils.getStyleStrategy()) //Reference table style
.needHead(true)
.build();
Required styling tool classes
package com.easyexcel.easyexcel.util;
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.*;
/**
* excel style
*/
public class EasyExcelUtils {<!-- -->
public static HorizontalCellStyleStrategy getStyleStrategy() {<!-- -->
// Header strategy style adjustment
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//Header background
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
// Automatic line wrapping
headWriteCellStyle.setWrapped(true);
//Set thin border
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
// horizontal alignment
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//vertical alignment
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//------------------------------------------------ ----------------------------------
// Content strategy Song Dynasty
WriteCellStyle contentStyle = new WriteCellStyle();
//Set vertical centering
contentStyle.setWrapped(true);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// Set horizontal centering
contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
//Content font size
contentWriteFont.setFontHeightInPoints((short) 12);
// border
contentStyle.setBorderBottom(BorderStyle.THIN);
contentStyle.setBorderLeft(BorderStyle.THIN);
contentStyle.setBorderRight(BorderStyle.THIN);
contentStyle.setBorderTop(BorderStyle.THIN);
contentStyle.setWriteFont(contentWriteFont);
// This strategy is that the header is the style of the header and the content is the style of the content. Other strategies can be implemented by yourself.
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);
}
}
2.3, row height, column width
WriteTable writeTable = EasyExcel.writerTable(0).head(headList)
.registerWriteHandler(new CustomRowHeightStyleStrategy(1))//Reference row height
.registerWriteHandler(EasyExcelUtils.getStyleStrategy()) //Reference table style
.registerWriteHandler(new LongestCellWidthHandlerProject())//Set column width
.needHead(true)
.build();
Row height:
package com.easyexcel.easyexcel.util;
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Row;
/**
* row height
*/
public class CustomRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {<!-- -->
/**
* Set the line number for special processing
*/
Integer rowNum;
public CustomRowHeightStyleStrategy(Integer rowNum) {<!-- -->
this.rowNum = rowNum;
}
/**
* Set the row height of the table header
*/
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {<!-- -->
if (relativeRowIndex + 1 == rowNum) {<!-- -->
//Specify the height of the row
row.setHeightInPoints((120));
}else {<!-- -->
//Default header height
row.setHeightInPoints((20));
}
}
/**
* Set the row height of the content
*/
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {<!-- -->
//Default body height
row.setHeightInPoints(20);
}
}
Column width:
package com.easyexcel.easyexcel.util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.style.column.AbstractHeadColumnWidthStyleStrategy;
/**
* Column width
*/
public class LongestCellWidthHandlerProject extends AbstractHeadColumnWidthStyleStrategy {<!-- -->
/**
* Set the column width of each column
*/
@Override
protected Integer columnWidth(Head head, Integer columnIndex) {<!-- -->
switch (columnIndex) {<!-- -->
case 0:
return 6;
case 1:
return 20;
case 2:
return 20;
default:
return 13;
}
}
}
2.4. Column merging
ExcelWriter writer = EasyExcelFactory.write(path).inMemory(Boolean.TRUE)
.registerWriteHandler(new ExcelFillCellRowMergeStrategy(0, new int[]{<!-- -->0, 1, 2, 3}))//Merge columns 1, 2, and 3 downward, ↓
.build();
package com.easyexcel.easyexcel.util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* Project Certification Template - Column Merge, ↓
*/
public class ExcelFillCellRowMergeStrategy implements CellWriteHandler {<!-- -->
//The array stores the columns that need to be merged in this row [0,1,2] Merge columns 0, 1, and 2 in this mergeRowIndex row
private int[] mergeColumnIndex;
// Store rows that need to be merged upwards
private int mergeRowIndex;
// Do not merge rows
private Integer noMergeRowIndex;
public ExcelFillCellRowMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {<!-- -->
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
public ExcelFillCellRowMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex, Integer noMergeRowIndex) {<!-- -->
this.mergeColumnIndex = mergeColumnIndex;
this.mergeRowIndex = mergeRowIndex;
this.noMergeRowIndex = noMergeRowIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {<!-- -->
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {<!-- -->
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {<!-- -->
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {<!-- -->
int curRowIndex = cell.getRowIndex();
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {<!-- -->
for (int i = 0; i < mergeColumnIndex.length; i + + ) {<!-- -->
if (curColIndex == mergeColumnIndex[i]) {<!-- -->
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* Merge current cells upward
* .
*
* @param writeSheetHolder writeSheetHolder
* @param cell current cell
* @param curRowIndex current row
* @param curColIndex current column
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {<!-- -->
Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Row preRow = cell.getSheet().getRow(curRowIndex - 1);
if (preRow == null) {<!-- -->
// When the previous row of data cannot be obtained, use the data in the cache sheet
preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
}
Cell preCell = preRow.getCell(curColIndex);
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
//Columns that do not need to be merged jump out directly
if ((noMergeRowIndex != null) & amp; & amp; noMergeRowIndex == (curRowIndex - 1)) {<!-- -->
return;
}
// Compare the current cell data with the previous cell data
boolean dataBool = preData.equals(curData);
//Note here: So get the first column data of each row and compare it with the first column data of the previous row. If they are equal, merge them
boolean equals = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
if (dataBool & amp; & amp; equals) {<!-- -->
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() & amp; & amp; !isMerged; i + + ) {<!-- -->
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// If the previous cell has been merged, remove the original merged unit first, and then add the merged unit again.
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {<!-- -->
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// If the previous cell has not been merged, add a new merged cell
if (!isMerged) {<!-- -->
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
2.5, Freeze window
ExcelWriter writer = EasyExcelFactory.write(path).inMemory(Boolean.TRUE)
.registerWriteHandler(new FreezeAndFilter())//Freeze window
.build();
package com.easyexcel.easyexcel.util;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;
/**
* Project Certification Template-Freeze Window
*/
public class FreezeAndFilter implements SheetWriteHandler {<!-- -->
/**
* Horizontal split position
*/
private int colSplit = 0;
/**
* The vertical position of the split
*/
private int rowSplit = 6;
/**
* The left column visible in the right pane
*/
private int leftmostColumn = 0;
/**
* Top row visible in bottom pane
*/
private int topRow = 6;
/**
* Set filter range
* (Parameters A2:D2 represent the position from the second row, column A to the second row, column J to be used as the filter box)
*/
public String autoFilterRange = "A2:D2";
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {<!-- -->
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {<!-- -->
Sheet sheet = writeSheetHolder.getSheet();
//Set freeze
sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
}
}
3. Import Excel table
3.1. Import information
controller
package com.easyexcel.easyexcel.controller;
import com.easyexcel.easyexcel.service.ImportDataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
@RestController
public class ImportDataController {<!-- -->
@Autowired
private ImportDataService importDataService;
@PostMapping("/importAbilityPoint")
public String importData(MultipartFile file) throws Exception {<!-- -->
String s = importDataService.importData(file);
return "success~~~";
}
}
service
package com.easyexcel.easyexcel.service;
import org.springframework.web.multipart.MultipartFile;
public interface ImportDataService {<!-- -->
String importData(MultipartFile file) throws Exception;
}
service-impl
package com.easyexcel.easyexcel.service.impl;
import com.easyexcel.easyexcel.service.ImportDataService;
import com.easyexcel.easyexcel.util.EasyExcelUtil;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
@Service
public class ImportDataServiceImpl implements ImportDataService {<!-- -->
@Override
public String importData(MultipartFile file) throws Exception {<!-- -->
//Get the data in the table
List<Object> data = EasyExcelUtil.readExcel(file.getInputStream());
System.out.println("The data obtained is " + data);
return null;
}
}
util
package com.easyexcel.easyexcel.util;
import com.alibaba.excel.EasyExcel;
import org.springframework.stereotype.Service;
import java.io.InputStream;
import java.util.List;
@Service
public class EasyExcelUtil {<!-- -->
public static List<Object> readExcel(InputStream fileInputStream) {<!-- -->
ExcelListener excelListener = new ExcelListener();
EasyExcel.read(fileInputStream, null, excelListener).sheet().doRead();
return excelListener.getDataList();
}
}
package com.easyexcel.easyexcel.util;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
public class ExcelListener extends AnalysisEventListener<Object> {<!-- -->
private List<Object> dataList = new ArrayList<>();
@Override
public void invoke(Object data, AnalysisContext context) {<!-- -->
dataList.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {<!-- -->
}
public List<Object> getDataList() {<!-- -->
return dataList;
}
}
I don’t understand the comments. The levels are very clear. Just follow them and it’s OK.
