Java实现生成Excel树形表头完整代码示例

String[] targetNames = {         "指标名称",          "单位",          "xx_yy1",          "xx_yy2_zz1",          "xx_yy2_zz2",         "2017年5月_主营业务收入_累计", "2017年5月_主营业务收入_同比",          "2017年5月_主营业务收入_本月", "2017年5月_主营业务收入_环比",          "2017年5月_利润_累计", "2017年5月_利润_同比", "2017年5月_利润_本月", "2017年5月_利润_环比",         "2017年6月_主营业务收入_累计", "2017年6月_主营业务收入_同比",          "2017年6月_主营业务收入_本月", "2017年6月_主营业务收入_环比",          "2017年6月_利润_累计", "2017年6月_利润_同比", "2017年6月_利润_本月", "2017年6月_利润_环比"       };

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 + "]";	}}

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;	// 默认叶子节点 	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;	}	/**    * 单元格合并结束列索引自增    */	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 + "]";	}}

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 {				/* 单元格出现多少次，则该单元格就合并多少列 */				cellInfo.incEndC();				// 列结束索引自增（用于列合并） 				cellInfo.setLeaf(false);				// 只要重复出现，则为非叶子节点			}		}		// 行合并 		for (MergedCell cellInfo : cellInfoMap.values()) {			if (cellInfo.isLeaf()) {				// 如果为叶子节点，则一定合并到最后一行 				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());		// 结束索引默认为开始索引 		cellInfo.setEndC(cellContent.getColumnIndex());		cellInfo.setStartR(cellContent.getRowIndex());		// 结束索引默认为开始索引 		cellInfo.setEndR(cellContent.getRowIndex());		return cellInfo;	}}

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 = { 		        "指标名称",  		        "单位",  		        "xx_yy1",  		        "xx_yy2_zz1",  		        "xx_yy2_zz2", 		        "2017年5月_主营业务收入_累计", "2017年5月_主营业务收入_同比",  		        "2017年5月_主营业务收入_本月", "2017年5月_主营业务收入_环比",  		        "2017年5月_利润_累计", "2017年5月_利润_同比", "2017年5月_利润_本月", "2017年5月_利润_环比", 		        "2017年6月_主营业务收入_累计", "2017年6月_主营业务收入_同比",  		        "2017年6月_主营业务收入_本月", "2017年6月_主营业务收入_环比",  		        "2017年6月_利润_累计", "2017年6月_利润_同比", "2017年6月_利润_本月", "2017年6月_利润_环比" 		      };		// 设第一行不属于树形表头 		String[] extraNames = new String[targetNames.length];		for (int i = 0; i < 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);		// 第一行 		for (int i = 0; i < extraNames.length; i++) {			Label label = new Label(i, 0, extraNames[i], cellFormat);			sheet.addCell(label);		}		// 树形表头 		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("导出成功！");	}	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);	}}