import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.junit.Test;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
public class TestEasyExcel {
@Data
@ColumnWidth(20)
public static class TestVO {
@ExcelProperty(value = "*姓名", index = 0)
private String name;
@ExcelProperty(value = "*年龄", index = 1)
private int age;
@ExcelProperty(value = "学校", index = 2)
private String school;
}
@Test
public void testExport1() throws FileNotFoundException {
OutputStream outputStream = new FileOutputStream(new File("F:\\test.xlsx"));
List<TestVO> dataList = new ArrayList<>();
List<Integer> columns = Arrays.asList(0, 1);
HashMap<Integer, String> annotationsMap = new HashMap<>();
annotationsMap.put(0,"第一列标题批注");
annotationsMap.put(1,"第二列标题批注");
TitleHandler titleHandler = new TitleHandler(columns, IndexedColors.RED.index,annotationsMap);
EasyExcelUtil.writeExcelWithModel(outputStream, dataList, TestVO.class, "sheetName", titleHandler);
}
public static void main(String [] args) throws FileNotFoundException {
TestEasyExcel testEasyExcel=new TestEasyExcel();
testEasyExcel.testExport1();
}
}
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.chinacreator.bdg.standard.service.utils.LogOutUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import java.util.HashMap;
import java.util.List;
public class TitleHandler implements CellWriteHandler {
private static final LogOutUtil LOGGER = new LogOutUtil(TitleHandler.class);
private List<Integer> columnIndexs;
private Short colorIndex;
private HashMap<Integer, String> annotationsMap;
public TitleHandler(List<Integer> columnIndexs, Short colorIndex, HashMap<Integer, String> annotationsMap) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
this.annotationsMap = annotationsMap;
}
public TitleHandler(List<Integer> columnIndexs, Short colorIndex) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
}
@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 afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
Sheet sheet = writeSheetHolder.getSheet();
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
Drawing<?> drawing = sheet.createDrawingPatriarch();
XSSFRichTextString richString = new XSSFRichTextString(cell.getStringCellValue());
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
richString.applyFont(0, 1, font);
cell.setCellValue(richString);
if (null != annotationsMap && annotationsMap.containsKey(cell.getColumnIndex())) {
String context = annotationsMap.get(cell.getColumnIndex());
Comment comment = drawing.createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), 0, (short) 5, 5));
comment.setString(new XSSFRichTextString(context));
cell.setCellComment(comment);
}
}
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
}
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.chinacreator.bdg.standard.service.utils.LogOutUtil;
public class CustomSheetWriteHandler implements SheetWriteHandler {
private static final LogOutUtil LOGGER = new LogOutUtil(CustomSheetWriteHandler.class);
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] { "测试1", "测试2" });
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
}
import java.io.OutputStream;
import java.util.List;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
public class EasyExcelUtil {
public static void writeExcelWithModel(OutputStream outputStream, List<? extends Object> dataList,
Class<? extends Object> classT, String sheetName, CellWriteHandler... cellWriteHandlers) {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,
contentWriteCellStyle);
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(outputStream, classT).inMemory(true)
.sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new CustomSheetWriteHandler());
if (null != cellWriteHandlers && cellWriteHandlers.length > 0) {
for (int i = 0; i < cellWriteHandlers.length; i++) {
excelWriterSheetBuilder.registerWriteHandler(cellWriteHandlers[i]);
}
}
excelWriterSheetBuilder.doWrite(dataList);
}
}