java jxl 创建excel_Java使用JXL生成Excel --- （1）工具类简单思路

JXL导出Excel工具类---(1)简单思路

简单介绍

项目中经常有Excel报表生成和Html文件生成，对于不同的Excel样式，都还花部分时间去调样式，工作耗时耗力并且代码还杂乱无章，所以有了封装工具类的想法。

Excel常用样式汇总

根据常用的Excel模板，把单元格的情况，分为以下几种情况：

普通的Excel单元格

存在按列合并单元格

存在按行合并单元格

存在多行的单元格，把A事情、B事情和C事情看为一个整体。

工具类思路

采用PieceData类封装4种单元格类型，多个PieceData就能组成为一行，多行就能组成一个Sheet页。

部分代码如下：

/**

* PieceData类实现4种单元格类型

* 1，普通型 1*1 值只有一个

* 2，合并列型 1*n 值只有一个

* 3，合并行型 n*1 值只有一个

* 4，多行型 n*1 值有多个

*

* 类采用创建者模式进行生成，防止写多个构造函数

**/

@Data

public class PieceData {

private List pieceValue;

private Integer rowSize;

private Integer columnSize;

private Type type;

public String getSingleValue() {

if (pieceValue == null || pieceValue.size() == 0) {

return "";

} else {

return pieceValue.get(0);

}

}

PieceData(Builder builder) {

this.pieceValue = builder.pieceValue;

this.rowSize = builder.rowSize;

this.columnSize = builder.columnSize;

this.type = builder.type;

}

@Data

@Accessors(chain = true)

public static class Builder {

private List pieceValue;

private Integer rowSize = 1;

private Integer columnSize = 1;

private Type type;

public PieceData build() {

if (pieceValue.size() > 1) {

type = Type.MORE_ROWS;

rowSize = pieceValue.size();

} else if (columnSize == 1 && rowSize == 1) {

type = Type.NORMAL;

} else if (columnSize > 1) {

type = Type.MERGE_COLUMNS;

} else if (rowSize > 1) {

type = Type.MERGE_ROWS;

}

return new PieceData(this);

}

//屏幕Type类型进行赋值

private void setType(Type type) {

}

}

public enum Type {

NORMAL,

MERGE_COLUMNS,

MERGE_ROWS,

MORE_ROWS

}

}

行的类型封装ExcelRow

/**

* 对一行数据进行封装

* 包括该行的状态(最大行数，最大列数)，以及每个单元格的信息

*

**/

@Getter

@Accessors(chain = true)

public class ExcelRow {

private RowStatus status = new RowStatus();

List pieceDataList = new ArrayList<>();

public void addPieceData(PieceData pieceData) {

pieceDataList.add(pieceData);

status.setMaxRow(status.getMaxRow().compareTo(pieceData.getRowSize()) > 0 ? status.getMaxRow() : pieceData.getRowSize());

status.setMaxColumn(status.getMaxColumn().compareTo(pieceData.getColumnSize()) > 0 ? status.getMaxColumn() : pieceData.getColumnSize());

}

public void setRevise(boolean isRevise) {

status.setRevise(isRevise);

}

public Integer getMaxRow() {

return status.getMaxRow();

}

public Integer getMaxColumn() {

return status.getMaxColumn();

}

}

/**

* RowStatus类

* 主要记录该行最大行数，最大列数，是否需要自适应

*

**/

@Data

public class RowStatus {

private Integer maxRow = 0;

private Integer maxColumn = 0;

// 默认为自适应的修正

private boolean isRevise = true;

}

生成类包括如下：

WritableWorkbook workbook = Workbook.createWorkbook(new File("X"));

workbook.createSheet(sheetName, sheetNum);

WritableSheet sheet = workbook.getSheet(0);

int rowNum = 0;

for (ExcelRow header : headerRows) {

int column = 0;

for (PieceData piece : header.getPieceDataList()) {

writeCellData(sheet, piece, rowNum, column, header.getStatus());

column += piece.getColumnSize();

}

rowNum += header.getMaxRow();

}

workbook.write();

workbook.close();

// 根据不同的单元格类型进行处理

private void writeCellData(WritableSheet sheet, PieceData pieceData, int startRow, int startColumn, RowStatus status) {

try {

switch (pieceData.getType()) {

case NORMAL:

sheet.addCell(new Label(startColumn, startRow, pieceData.getSingleValue()));

if (status.isRevise() && status.getMaxRow().compareTo(pieceData.getRowSize()) > 0) {

sheet.mergeCells(startColumn, startRow, startColumn, startRow + status.getMaxRow() - 1);

}

break;

case MERGE_ROWS:

sheet.addCell(new Label(startColumn, startRow, pieceData.getSingleValue()));

if (status.isRevise() && status.getMaxRow().compareTo(pieceData.getRowSize()) > 0) {

sheet.mergeCells(startColumn, startRow, startColumn, startRow + status.getMaxRow() - 1);

} else {

sheet.mergeCells(startColumn, startRow, startColumn, startRow + pieceData.getRowSize() - 1);

}

break;

case MERGE_COLUMNS:

sheet.addCell(new Label(startColumn, startRow, pieceData.getSingleValue()));

if (status.isRevise() && status.getMaxRow().compareTo(pieceData.getRowSize()) > 0) {

sheet.mergeCells(startColumn, startRow, startColumn + pieceData.getColumnSize() - 1, startRow + status.getMaxRow() - 1);

} else {

sheet.mergeCells(startColumn, startRow, startColumn + pieceData.getColumnSize() - 1, startRow);

}

break;

case MORE_ROWS:

for (int i = 0; i < pieceData.getRowSize(); i++) {

sheet.addCell(new Label(startColumn, startRow + i, pieceData.getPieceValue().get(i)));

}

break;

default:

break;

}

} catch (WriteException e) {

e.printStackTrace();

}

}

Demo样式

public static void main(String[] args) {

List excelRows = new ArrayList<>();

ExcelRow row1 = new ExcelRow();

row1.addPieceData(new PieceData.Builder().setPieceValue(Collections.singletonList("A")).build());

row1.addPieceData(new PieceData.Builder().setPieceValue(Collections.singletonList("B")).build());

row1.addPieceData(new PieceData.Builder().setPieceValue(Collections.singletonList("C")).setColumnSize(2).build());

row1.addPieceData(new PieceData.Builder().setPieceValue(Collections.singletonList("D")).setRowSize(2).build());

row1.addPieceData(new PieceData.Builder().setPieceValue(Arrays.asList("E", "F", "G")).build());

ExcelRow row2 = new ExcelRow();

row2.addPieceData(new PieceData.Builder().setPieceValue(Collections.singletonList("A")).build());

row2.addPieceData(new PieceData.Builder().setPieceValue(Collections.singletonList("B")).build());

row2.addPieceData(new PieceData.Builder().setPieceValue(Collections.singletonList("C")).setColumnSize(2).build());

row2.addPieceData(new PieceData.Builder().setPieceValue(Collections.singletonList("D")).setRowSize(2).build());

row2.addPieceData(new PieceData.Builder().setPieceValue(Arrays.asList("E", "F", "G")).build());

excelRows.add(row1);

excelRows.add(row2);

excelWriter.write(null, excelRows, "测试", 1);

}

代码中包含了工具类的简单思路，详细的封装类以及样例格式调整，待续。