写入EXCEL 有两种方法,其一是通过 直接代码控制,缺点是不好掌握整个的页面设计。
合并策略。根据 行开始和结束 进行控制,
合并列, 参数 mergeRowIndex 开始行,endmergeRowIndex截至行 mergeColumnIndex[]哪几列 垂直合并
Boolean bool = cell.getRow().getCell(0).getStringCellValue()
.equals(cell.getSheet()
.getRow(curRowIndex - 1).getCell(0).getStringCellValue());
根据第一列的数据进行 合并, 可以按自己需求增加
package demo3;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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;
public class ExcelFillCellMergeStrategyTest implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
private int endmergeRowIndex;
public ExcelFillCellMergeStrategyTest() {
}
public ExcelFillCellMergeStrategyTest(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
public ExcelFillCellMergeStrategyTest(int mergeRowIndex, int endmergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
this.endmergeRowIndex = endmergeRowIndex;
}
@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, CellData cellData, 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) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex && curRowIndex < endmergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数据与上一个单元格数据比较
Boolean dataBool = preData.equals(curData);
//此处需要注意:因为我是按照工程名称确定是否需要合并的,所以获取每一行第二列数据和上一行第一列数据进行比较,如果相等合并,getCell里面的值,是工程名称所在列的下标
Boolean bool = cell.getRow().getCell(0).getStringCellValue()
.equals(cell.getSheet()
.getRow(curRowIndex - 1).getCell(0).getStringCellValue());
if (dataBool && bool) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并,则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
合并列 ,mergeRowIndex[] 合并的列号 mergeColumnIndex 从第几列开始合并行 ,
Boolean bool = cell.getSheet().getRow(13).getCell(curColIndex).getStringCellValue()
.equals(cell.getSheet()
.getRow(13).getCell(curColIndex-1).getStringCellValue());
根据14行 的数据进行合并,可以自行增加或减少
package demo3;
//合并单元格
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
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;
/**
* 单元格合并
*
* @author Jamin
* @date 2020/11/9 11:35
*/
@Data
public class ExcelMergeRowByRowUtil2 implements CellWriteHandler {
/**
* 合并字段的下标
*/
private int[] mergeRowIndex;
/**
* 合并几行
*/
private int mergeColumnIndex;
public ExcelMergeRowByRowUtil2() {
}
public ExcelMergeRowByRowUtil2(int mergeColumnIndex, int[] mergeRowIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@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 curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curColIndex > mergeColumnIndex) {
for (int i = 0; i < mergeRowIndex.length; i++) {
if (curRowIndex == mergeRowIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据,通过上一行数据是否相同进行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex-1);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
preCell.getNumericCellValue();
// 将当前单元格数据与上一个单元格数据比较
Boolean dataBool = preData.equals(curData);
//此处需要注意:因为我是按照工程名称确定是否需要合并的,所以获取每一行第二列数据和上一行第一列数据进行比较,如果相等合并,getCell里面的值,是工程名称所在列的下标
Boolean bool = cell.getSheet().getRow(13).getCell(curColIndex).getStringCellValue()
.equals(cell.getSheet()
.getRow(13).getCell(curColIndex-1).getStringCellValue());
if (dataBool && bool) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex , curColIndex-1)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastColumn(curColIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并,则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex-1,
curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
样式调整 ,可以调整头和 内容的样式,通过行号可以控制
package demo3; 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.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.AbstractCellStyleStrategy; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import java.util.Date; import java.util.HashMap; import java.util.List; /** * @Author wendy * @Date 2020/8/14 5:10 下午 * @Desc 拦截处理单元格创建 */ public class HeadStyleWriteHandler extends AbstractCellStyleStrategy implements CellWriteHandler { Workbook workbook; @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { super.beforeCellCreate(writeSheetHolder, writeTableHolder, row, head, columnIndex, relativeRowIndex, isHead); } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead); } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { this.initCellStyle(writeSheetHolder.getSheet().getWorkbook()); this.setHeadCellStyle(cell,head,relativeRowIndex); } @Override protected void initCellStyle(Workbook workbook) { this.workbook = workbook; } @Override protected void setHeadCellStyle(Cell cell, Head head, Integer integer) { if (cell.getRowIndex() == 0) { cell.setCellStyle(EasyExcelUtils.getColumnTopStyle(workbook, 18)); } else if (cell.getRowIndex() == 1) { cell.setCellStyle(EasyExcelUtils.getColumnTopStyle(workbook,11)); }else if (cell.getRowIndex()==2){ cell.setCellStyle(EasyExcelUtils.getColumnSecondLineStyle(workbook,20)); } if(cell.getRowIndex() > 2){ cell.setCellStyle(EasyExcelUtils.getColumnStyle(workbook)); } } @Override protected void setContentCellStyle(Cell cell, Head head, Integer integer) { } }
package demo3;
import org.apache.poi.ss.usermodel.*;
public class EasyExcelUtils {
/**
* 首行单元格
* @param workbook
* @param fontSize
* @return
*/
public static CellStyle getColumnTopStyle(Workbook workbook, int fontSize) {
if (fontSize == 0) {
fontSize = 10;
}
// 设置字体
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) fontSize);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("宋体");
//设置样式;
CellStyle style = workbook.createCellStyle();
//左右居中
style.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置边框
// style.setBorderBottom(BorderStyle.THIN);
// style.setBorderLeft(BorderStyle.THIN);
// style.setBorderRight(BorderStyle.THIN);
// style.setBorderTop(BorderStyle.THIN);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
return style;
}
/**
*
* @param workbook
* @param fontSize
* @return
*/
public static CellStyle getColumnSecondLineStyle(Workbook workbook, int fontSize) {
if (fontSize == 0) {
fontSize = 10;
}
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) fontSize);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("宋体");
//设置样式;
CellStyle style = workbook.createCellStyle();
//左右居中
style.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
return style;
}
/*
* 字段样式
*/
public static CellStyle getColumnStyle(Workbook workbook) {
// 设置字体
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 12);
//设置字体名字
font.setFontName("Arial");
//设置样式;
CellStyle style = workbook.createCellStyle();
//左右居中
style.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置边框
// style.setBorderBottom(BorderStyle.THIN);
// style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// style.setBorderTop(BorderStyle.THIN);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(true);
return style;
}
}
测试 输出
package demo3;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import demo3.entity.ComplexHeadStyles;
import org.apache.poi.ss.usermodel.*;
import org.junit.Before;
import org.junit.Test;
import java.io.File;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ArrayBlockingQueue;
public class testaa {
private Master master;
private List<Detail> details = new ArrayList<>();
SimpleDateFormat simple = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
Date date=new Date();
String format = simple.format(date);
/**
* 初始化测试数据
*/
@Before
public void initData() {
master = new Master();
master.setBigTitle("设备购置需求 " + "(" +format + ")");
master.setSmallTitle("()" + format);
master.setCreatedDate(format);
master.setTitle("购置需求");
Detail d0 = new Detail();
d0.setRow1("采购类别");
d0.setRow2("设备");
d0.setRow3("项目");
d0.setRow4("");
d0.setRow5("填表日期");
d0.setRow6("填表日期");
d0.setRow7("");
d0.setRow8("");
details.add(d0);
Detail d1 = new Detail();
d1.setRow1("申请");
d1.setRow2("采中");
d1.setRow3("联系人");
d1.setRow4("");
d1.setRow5("名称");
d1.setRow6("");
d1.setRow7("数量");
d1.setRow8("12");
details.add(d1);
Detail d2 = new Detail();
d2.setRow1("科");
d2.setRow2("采");
d2.setRow3("联系方式");
d2.setRow4("11122223122");
d2.setRow5("名称");
d2.setRow6("");
d2.setRow7("数量");
d2.setRow8("12");
details.add(d2);
Detail d3= new Detail();
d3.setRow1("项目总预算)");
d3.setRow2("11");
d3.setRow3("年份");
d3.setRow4("c");
d3.setRow5("区");
d3.setRow6("1");
d3.setRow7(" ");
d3.setRow8(" ");
details.add(d3);
Detail d4= new Detail();
d4.setRow1("aaa");
d4.setRow2("ww");
d4.setRow3("品行名号");
d4.setRow4("w");
details.add(d4);
Detail d5= new Detail();
d5.setRow1("推荐");
d5.setRow2("序号");
d5.setRow3("供应商");
d5.setRow4("型号");
d5.setRow5("品牌");
d5.setRow6("联系人");
d5.setRow7("电话");
d5.setRow8("电话");
details.add(d5);
Detail d6= new Detail();
d6.setRow1("狗东型号极其名称");
details.add(d6);
Detail d7= new Detail();
d7.setRow1("狗东型号极其名称");
d7.setRow2("插入项");
details.add(d7);
Detail d8= new Detail();
d8.setRow1("狗东型号极其名称");
details.add(d8);
details.add(d8);
Detail d9=new Detail();
d9.setRow1("此写");
d9.setRow2("采");
d9.setRow3("采购概况");
d9.setRow4("采购概况");
d9.setRow5("预算");
d9.setRow6("预算");
d9.setRow7("备注");
d9.setRow8("备注");
details.add(d9);
Detail d10 =new Detail();
d10.setRow1("此写");
details.add(d10);
details.add(d10);
details.add(d10);
Detail d11=new Detail();
d11.setRow1("会议");
details.add(d11);
details.add(d11);
Detail d12=new Detail();
d12.setRow1("会议");
d12.setRow2("详细描述");
details.add(d12);
Detail d13=new Detail();
d13.setRow1("设备");
details.add(d13);
details.add(d13);
Detail d14=new Detail();
d14.setRow1("设备");
d14.setRow2("参数");
details.add(d14);
Detail d15=new Detail();
d15.setRow1("意见");
details.add(d15);
details.add(d15);
details.add(d15);
Detail d16=new Detail();
d16.setRow1("意见");
details.add(d16);
details.add(d16);
Detail d17=new Detail();
d17.setRow1("意见");
d17.setRow2("修改附件");
details.add(d17);
Detail d18=new Detail();
d18.setRow1("分管领导意见");
details.add(d18);
details.add(d18);
details.add(d18);
}
/**
* 仅仅输出明细数据到excel文件
*/
// @Test
// public void writeSimpleExcelForDetail() {
// String path = this.getClass().getResource("/").getPath();
// System.out.println(path);
// String fileName = "D:\\bf\\simpleWrite" + System.currentTimeMillis() + ".xlsx";
// // 这里 需要指定写用哪个class去写,指定模板名称及数据
// EasyExcel.write(fileName, Detail.class).sheet("sheet名称").doWrite(details);
// }
// /**
// * 仅仅输出明细数据到excel文件, 排除部分字段
// */
// @Test
// public void writeSimpleExcelExculdeColumns() {
// String path = this.getClass().getResource("/").getPath();
// List<String> excludeColumns = new ArrayList<>();
// excludeColumns.add("memo");
// String fileName = "D:\\bf\\simpleWrite" + System.currentTimeMillis() + ".xlsx";
// // 这里 需要指定写用哪个class去写,指定模板名称及数据
// EasyExcel.write(fileName, Detail.class).excludeColumnFiledNames(excludeColumns).sheet("sheet名称").doWrite(details);
// }
/**
* 仅仅输出明细数据到excel文件, 增加自定义的头部
*/
@Test
public void writeSimpleExcelWithHeader() {
String path = this.getClass().getResource("/").getPath();
String fileName = "D:\\bf\\simpleWrite" + ".xlsx";
File file = new File(fileName);
if (file.exists()) {
file.delete();
}
// 准备 HEADER
List<List<String>> list = getHeader();
// 这里 需要指定写用哪个class去写,指定模板名称及数据
int rowArray[] =new int[]{0,1,2,3,4,5,6,7};
int rowArray2[] =new int[]{0};
int columnArray[] =new int[]{3,6,8,10,19,22};
int columnArray2[]=new int[]{9,11,12,13,14,15,14,17,18,20,21,23,24,25,26,28,29,31};
// int columnArray3[]=new int[]{};
//需要从第一行开始,列头第一行
EasyExcel.write(fileName).head(list)
.registerWriteHandler(new CustomizeColumnWidth()) //设置行宽
//设置标题和正文的内容
//合并单元格策略
//合并行,
.registerWriteHandler(new ExcelMergeRowByRowUtil(2,columnArray))
.registerWriteHandler(new ExcelMergeRowByRowUtil(1,columnArray2))
// .registerWriteHandler(new ExcelMergeRowByRowUtil2(1,columnArray3))
//合并列,开始,截至
.registerWriteHandler(new ExcelFillCellMergeStrategyTest(3,9,rowArray))
.registerWriteHandler(new ExcelFillCellMergeStrategyTest(11,19,rowArray))
.registerWriteHandler(new ExcelFillCellMergeStrategyTest(19,22,rowArray))
.registerWriteHandler(new ExcelFillCellMergeStrategyTest(22,28,rowArray))
.registerWriteHandler(new ExcelFillCellMergeStrategyTest(29,32,rowArray))
//合并列,开始,截至
.registerWriteHandler(new ExcelFillCellMergeStrategyTest(8,13,rowArray2))
.registerWriteHandler(new ExcelFillCellMergeStrategyTest(17,20,rowArray2))
.registerWriteHandler(new ExcelFillCellMergeStrategyTest(19,23,rowArray2))
.registerWriteHandler(new ExcelFillCellMergeStrategyTest(22,29,rowArray2))
.registerWriteHandler(new HeadStyleWriteHandler())
.sheet("sheet名称")
.doWrite(details);
}
private List<List<String>> getHeader() {
/**
* 打算展示成如下样子
* |客户:xxx 公司 (这一行需要合并单元格)
* |单号: SO22222222222222| 日期: 2020-01-01 (分别需要合并单元格)
* |产品ID|产品名称|价格|数量|总金额|备注|
*/
String customer = master.getBigTitle();
String sheetNo = master.getSmallTitle();
String dateStr = "日期: " + master.getCreatedDate();
String title = master.getTitle();
String emptytitle = master.getEmptyTitle();
List<List<String>> list = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<String>();
head0.add(customer);
head0.add(sheetNo);
head0.add(" ");
list.add(head0);
for (int i = 0; i < 7; i++) {
List<String> head = new ArrayList<String>();
head.add(customer);
head.add(sheetNo);
head.add(title);
list.add(head);
}
return list;
}
// private HorizontalCellStyleStrategy getStyleStrategy() {
// // 头的策略
// WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// // 设置对齐
// headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
// // 背景色, 设置为白色,也是默认颜色
// headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// // 字体
// WriteFont headWriteFont = new WriteFont();
// headWriteFont.setFontHeightInPoints((short) 18);
// headWriteCellStyle.setWriteFont(headWriteFont);
// headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//
// // 内容的策略
// WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// // contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//
// // 背景绿色
// //contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
// // 字体策略
// WriteFont contentWriteFont = new WriteFont();
// contentWriteFont.setFontHeightInPoints((short) 12);
// contentWriteCellStyle.setWriteFont(contentWriteFont);
// //设置 自动换行
// contentWriteCellStyle.setWrapped(true);
// //设置 垂直居中
// contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// //设置 水平居中
// contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// //设置边框样式
// contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
// contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
// contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
// contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
//
// // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
// HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
// return horizontalCellStyleStrategy;
// }
/**
* 自定义头部的 列的宽度设置 策略. .
*/
class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
// 测试为 COLUMN 宽度定制.
if (isHead && cell.getRowIndex() == 2) {
int columnWidth = cell.getStringCellValue().getBytes().length;
int cellIndex = cell.getColumnIndex();
switch (cellIndex) {
case 0:
columnWidth = 12;
break;
case 2:
case 3:
case 1:
columnWidth = 15;
break;
case 4:
case 5:
columnWidth = 15;
break;
default:
columnWidth = 12;
break;
}
if (columnWidth > 255) {
columnWidth = 255;
}
writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
}
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
// 设置行高测试
int rowIndex = row.getRowNum();
short height = 600;
System.out.println("当前行: " + rowIndex);
if (rowIndex==2)
height=1000;
row.setHeight(height);
}
}
}
输出参考
参考文章
【easyexcel根据数据相同导出动态合并单元格】_ekkcole的博客-CSDN博客_easyexcel导出合并单元格
easyexcel导出合并单元格 - 简书