1.单元格合并
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.ArrayList;
import java.util.List;
public class ReportMergeStrategy extends AbstractMergeStrategy {
//合并坐标集合
private List<CellRangeAddress> cellRangeAddresss;
//构造
public ReportMergeStrategy(int num) {
List<CellRangeAddress> list = new ArrayList<>();
for (int k = 0; k < num; k++) {
int i = k * 3 + 2;
list.add(new CellRangeAddress(i, i + 2, 0, 0));
list.add(new CellRangeAddress(i, i + 2, 5, 5));
list.add(new CellRangeAddress(i, i + 2, 6, 6));
list.add(new CellRangeAddress(i, i + 2, 7, 7));
list.add(new CellRangeAddress(i, i + 2, 8, 8));
list.add(new CellRangeAddress(i, i + 2, 9, 9));
list.add(new CellRangeAddress(i, i + 2, 10, 10));
list.add(new CellRangeAddress(i, i + 2, 11, 11));
list.add(new CellRangeAddress(i, i + 2, 12, 12));
}
this.cellRangeAddresss = list;
}
/**
* merge
*
* @param sheet
* @param cell
* @param head
* @param relativeRowIndex
*/
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (CollectionUtils.isNotEmpty(cellRangeAddresss)) {
if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {
for (CellRangeAddress item : cellRangeAddresss) {
sheet.addMergedRegionUnsafe(item);
}
}
}
}
}
2. 表格样式(字体/边框/背景色等)
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 CellStyleStrategy {
/**
* 设置表头 和内容样式
*
* @return
*/
public static HorizontalCellStyleStrategy StyleStrategy() {
// 表头样式策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//表头前景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setBold(true);
headWriteFont.setFontName("宋体");
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont);
//2 内容样式策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
//内容字体大小
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//设置自动换行
contentWriteCellStyle.setWrapped(true);
//设置垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 头默认了 FillPatternType所以可以不指定。
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//设置水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//设置边框样式
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
3.多行表头颜色设置
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 org.apache.poi.ss.usermodel.*;
import java.util.List;
public class RowWriteHandler implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
int i = cell.getColumnIndex();
writeSheetHolder.getSheet().getRow(cell.getRowIndex()).setHeight((short) (2.34 * 256));
if (1 == cell.getRowIndex()) {
// 根据单元格获取workbook
Workbook workbook = cell.getSheet().getWorkbook();
//设置行高
// writeSheetHolder.getSheet().getRow(cell.getRowIndex()).setHeight((short) (1.4 * 256));
writeSheetHolder.getSheet().setColumnWidth(i, (short) (13.18 * 256));
// 单元格策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置背景颜色白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
WriteFont contentWriteFont = new WriteFont();
//内容字体大小
contentWriteFont.setFontName("宋体");
contentWriteFont.setBold(false);
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
//设置当前行第i列的样式
cell.getRow().getCell(i).setCellStyle(cellStyle);
}
}
}
4.输出excel
List<List<String>> head = ExcelHead();
EasyExcel.write(fileUrl).head(head).sheet(sheetname)
.registerWriteHandler(StyleStrategy())
.registerWriteHandler(new ReportMergeStrategy(devicecheck.size()))//自定义合并 单元格
.registerWriteHandler(new RowWriteHandler())
.doWrite(tosave);