Java implementation to generate the complete code example of Excel tree header

This article mainly shares a complete code example of Java implementation generating Excel tree table headers. There is nothing to explain, so just take a look at the code process.

Source data format:

 String[] targetNames = { "Indicator Name", "Unit", "xx_yy1", "xx_yy2_zz1", "xx_yy2_zz2", "May 2017_May Business Income_Cumulative", "May 2017_May Business Income_Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Y "June 2017_Main Business Income_Year-Year", "June 2017_Main Business Income_This Month", "June 2017_Main Business Income_Moon-month", "June 2017_Profit_Cumulative", "June 2017_Profit_Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year

Generate the following Excel:

The first line does not belong to the tree header.

Code

SplitCell:

 package com.zzj.excel;public class SplitCell {private String key;private String parentKey;private String value;private int columnIndex;private int rowIndex;public SplitCell() {}public SplitCell(String key, String value) {this.key = key;this.value = value;}public SplitCell(String key, String parentKey, String value, int columnIndex, int rowIndex) {this.key = key; this.parentKey = parentKey; this.value = value; this.columnIndex = columnIndex; this.rowIndex = rowIndex;}public String getKey() {return key;}public void setKey(String key) {this.key = key;}public String getParentKey() {return parentKey;}public void setParentKey(String parentKey) {this.parentKey = parentKey;}public String getValue() {return value;}public void setValue(String value) {this.value = value;}public int getColumnIndex() {return columnIndex;}public void setColumnIndex(int columnIndex) {this.columnIndex = columnIndex;}public int getRowIndex() {return rowIndex;}public void setRowIndex(int rowIndex) {this.rowIndex = rowIndex;}@Override public String toString() {return "CellContent [key=" + key + ", parentKey=" + parentKey + ", value=" + value + ", columnIndex=" + columnIndex + ", rowIndex=" + rowIndex + "]";}}

MergedCell:

 package com.zzj.excel;public class MergedCell {private String key;private String parentKey;private String value;private int startC;private int endC;private int startR;private int endR;private Boolean leaf = true;// Default leaf node public String getKey() {return key;}public void setKey(String key) {this.key = key;}public String getParentKey() {return parentKey;}public void setParentKey(String parentKey) {this.parentKey = parentKey;}public String getValue() {return value;}public void setValue(String value) {this.value = value;}public int getStartC() {return startC;}public void setStartC(int startC) {this.startC = startC;}public int getEndC() {return endC;}public void setEndC(int endC) {this.endC = endC;}/** * Cell merge end column index self-increment*/public void incEndC(){this.endC++;}public int getStartR() {return startR;}public void setStartR(int startR) {this.startR = startR;}public int getEndR() {return endR;}public void setEndR(int endR) {this.endR = endR;}public Boolean isLeaf() {return leaf;}public void setLeaf(Boolean leaf) {this.leaf = leaf;}@Override public String toString() {return "CellInfo [key=" + key + ", parentKey=" + parentKey + ", value=" + value + ", startC=" + startC + ", endC=" + endC + ", startR=" + startR + ", endR=" + endR + ", leaf=" + leaf + "]";}}

CellTransformer:

 package com.zzj.excel;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;public class CellTransformer {private final List<SplitCell> cellContents;private final int firstRowIndex;private final int rowSize;private Map<String, MergedCell> cellInfoMap = new LinkedHashMap<String, MergedCell>();public CellTransformer(List<SplitCell> cellContents, int firstRowIndex, int rowSize) {this.cellContents = cellContents;this.firstRowIndex = firstRowIndex;this.rowSize = rowSize;}public Map<String, MergedCell> transform(){cellInfoMap.clear();for (SplitCell cellContent : cellContents) {MergedCell cellInfo = cellInfoMap.get(cellContent.getKey());if (cellInfo == null) {cellInfo = convertToCellInfo(cellContent);cellInfoMap.put(cellInfo.getKey(), cellInfo);} else {/* How many columns appear in the cell will merge */cellInfo.incEndC();// Column end index self-increase (used for column merging) cellInfo.setLeaf(false);// As long as it appears repeatedly, it is a non-leaf node}}// Row merge for (MergedCell cellInfo: cellInfoMap.values()) {if (cellInfo.isLeaf()) {// If it is a leaf node, it must be merged to the last row cellInfo.setEndR(firstRowIndex + rowSize - 1);}}return cellInfoMap;}private MergedCell convertToCellInfo(SplitCell cellContent){MergedCell cellInfo = new MergedCell();cellInfo.setKey(cellContent.getKey());cellInfo.setParentKey(cellContent.getParentKey());cellInfo.setValue(cellContent.getValue());cellInfo.setStartC(cellContent.getColumnIndex());// The end index defaults to the start index cellInfo.setEndC(cellContent.getColumnIndex());cellInfo.setStartR(cellContent.getRowIndex());// The end index defaults to the start index cellInfo.setEndR(cellContent.getRowIndex());return cellInfo;}}

test

 package com.zzj.excel;import java.io.File;import java.util.ArrayList;import java.util.List;import java.util.Map;import jxl.Workbook;import jxl.format.CellFormat;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;public class Main {private static final String SEPARATOR = "_";public static void main(String[] args) throws Exception {String[] targetNames = { "Indicator Name", "Unit", "xx_yy1", "xx_yy2_zz1", "xx_yy2_zz2", "May 2017_May Business Income_Cumulative", "May 2017_May Business Income_Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Y "May 2017_Profit_This Month", "May 2017_Profit_MoQ", "June 2017_Main Business Income_Cumulative", "June 2017_Main Business Income_Year-Year", "June 2017_Main Business Income_Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Year-Y extraNames.length; i++) {extraNames[i] = "extra" + i;}final int firstTreeRowIndex = 1;int rowSize = getRowSize(targetNames);List<SplitCell> cellContents = new ArrayList<>();for (int i = 0; i < targetNames.length; i++) {String[] values = targetNames[i].split(SEPARATOR);for (int j = 0; j < values.length; j++) {String value = values[j];String key = getKey(values, j);String parentKey = getParentKey(values, j); SplitCell cellContent = new SplitCell(key, parentKey, value, i, j + firstTreeRowIndex); cellContents.add(cellContent);}}WritableWorkbook workbook = Workbook.createWorkbook(new File("F://template.xls"));CellFormat cellFormat = getCellFormat();WritableSheet sheet = workbook.createSheet("template", 0);// First line for (int i = 0; i < extraNames.length; i++) {Label label = new Label(i, 0, extraNames[i], cellFormat);sheet.addCell(label);}// Tree header CellTransformer cellInfoManager = new CellTransformer(cellContents, firstTreeRowIndex, rowSize);Map<String, MergedCell> map = cellInfoManager.transform(); for (MergedCell cellInfo : map.values()) {Label label = new Label(cellInfo.getStartC(), cellInfo.getStartR(), cellInfo.getValue(), cellFormat);if (cellInfo.getStartC() != cellInfo.getEndC() || cellInfo.getStartR() != cellInfo.getEndR()) {sheet.mergeCells(cellInfo.getStartC(), cellInfo.getStartR(), cellInfo.getEndC(), cellInfo.getEndR());}sheet.addCell(label);}workbook.write();workbook.close();System.out.println("Exported successfully!");}private static CellFormat getCellFormat() throws Exception{WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);WritableCellFormat cellFormat = new WritableCellFormat();cellFormat.setFont(font);cellFormat.setAlignment(jxl.format.Alignment.CENTRE);cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);cellFormat.setWrap(false);return cellFormat;}private static int getRowSize(String[] targetNames) {int rowSize = 0;for (String t : targetNames) {rowSize = Math.max(rowSize, t.split(SEPARATOR).length);}return rowSize;}private static String getKey(String[] values, int index){StringBuffer sb = new StringBuffer();for (int i = 0; i < (index + 1); i++) {sb.append(values[i] + SEPARATOR);}sb.deleteCharAt(sb.length() - 1);return sb.toString();}private static String getParentKey(String[] values, int index){if (index == 0) {return null;}return getKey(values, index - 1);}}

Summarize

The above is all the content of this article about Java implementation generating a complete code example of Excel tree header. I hope it will be helpful to everyone. Interested friends can continue to refer to other related topics on this site. If there are any shortcomings, please leave a message to point it out. Thank you friends for your support for this site!