快捷導(dǎo)航

Java實(shí)現(xiàn)生成Excel樹(shù)形表頭完整代碼示例

更新時(shí)間：2017年12月07日 14:15:40 作者：zhangzeyuaaa

這篇文章主要介紹了Java實(shí)現(xiàn)生成Excel樹(shù)形表頭完整代碼示例，具有一定借鑒價(jià)值，需要的朋友可以參考下。

本文主要分享了Java實(shí)現(xiàn)生成Excel樹(shù)形表頭完整代碼示例，沒(méi)有什么好解釋的，直接看看代碼過(guò)程。

源數(shù)據(jù)格式：

String[] targetNames = { 
        "指標(biāo)名稱(chēng)",  
        "單位",  
        "xx_yy1",  
        "xx_yy2_zz1",  
        "xx_yy2_zz2", 
        "2017年5月_主營(yíng)業(yè)務(wù)收入_累計(jì)", "2017年5月_主營(yíng)業(yè)務(wù)收入_同比",  
        "2017年5月_主營(yíng)業(yè)務(wù)收入_本月", "2017年5月_主營(yíng)業(yè)務(wù)收入_環(huán)比",  
        "2017年5月_利潤(rùn)_累計(jì)", "2017年5月_利潤(rùn)_同比", "2017年5月_利潤(rùn)_本月", "2017年5月_利潤(rùn)_環(huán)比", 
        "2017年6月_主營(yíng)業(yè)務(wù)收入_累計(jì)", "2017年6月_主營(yíng)業(yè)務(wù)收入_同比",  
        "2017年6月_主營(yíng)業(yè)務(wù)收入_本月", "2017年6月_主營(yíng)業(yè)務(wù)收入_環(huán)比",  
        "2017年6月_利潤(rùn)_累計(jì)", "2017年6月_利潤(rùn)_同比", "2017年6月_利潤(rùn)_本月", "2017年6月_利潤(rùn)_環(huán)比" 
      };

生成如下Excel：

第一行不屬于樹(shù)形表頭。

代碼

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;
	// 默認(rèn)葉子節(jié)點(diǎn) 
	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;
	}
	/** 
   * 單元格合并結(jié)束列索引自增 
   */
	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 {
				/* 單元格出現(xiàn)多少次，則該單元格就合并多少列 */
				cellInfo.incEndC();
				// 列結(jié)束索引自增（用于列合并） 
				cellInfo.setLeaf(false);
				// 只要重復(fù)出現(xiàn)，則為非葉子節(jié)點(diǎn)
			}
		}
		// 行合并 
		for (MergedCell cellInfo : cellInfoMap.values()) {
			if (cellInfo.isLeaf()) {
				// 如果為葉子節(jié)點(diǎn)，則一定合并到最后一行 
				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());
		// 結(jié)束索引默認(rèn)為開(kāi)始索引 
		cellInfo.setEndC(cellContent.getColumnIndex());
		cellInfo.setStartR(cellContent.getRowIndex());
		// 結(jié)束索引默認(rèn)為開(kāi)始索引 
		cellInfo.setEndR(cellContent.getRowIndex());
		return cellInfo;
	}
}

測(cè)試

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 = { 
		        "指標(biāo)名稱(chēng)",  
		        "單位",  
		        "xx_yy1",  
		        "xx_yy2_zz1",  
		        "xx_yy2_zz2", 
		        "2017年5月_主營(yíng)業(yè)務(wù)收入_累計(jì)", "2017年5月_主營(yíng)業(yè)務(wù)收入_同比",  
		        "2017年5月_主營(yíng)業(yè)務(wù)收入_本月", "2017年5月_主營(yíng)業(yè)務(wù)收入_環(huán)比",  
		        "2017年5月_利潤(rùn)_累計(jì)", "2017年5月_利潤(rùn)_同比", "2017年5月_利潤(rùn)_本月", "2017年5月_利潤(rùn)_環(huán)比", 
		        "2017年6月_主營(yíng)業(yè)務(wù)收入_累計(jì)", "2017年6月_主營(yíng)業(yè)務(wù)收入_同比",  
		        "2017年6月_主營(yíng)業(yè)務(wù)收入_本月", "2017年6月_主營(yíng)業(yè)務(wù)收入_環(huán)比",  
		        "2017年6月_利潤(rùn)_累計(jì)", "2017年6月_利潤(rùn)_同比", "2017年6月_利潤(rùn)_本月", "2017年6月_利潤(rùn)_環(huán)比" 
		      };
		// 設(shè)第一行不屬于樹(shù)形表頭 
		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);
		}
		// 樹(shù)形表頭 
		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("導(dǎo)出成功！");
	}
	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);
	}
}

總結(jié)

以上就是本文關(guān)于Java實(shí)現(xiàn)生成Excel樹(shù)形表頭完整代碼示例的全部?jī)?nèi)容，希望對(duì)大家有所幫助。感興趣的朋友可以繼續(xù)參閱本站其他相關(guān)專(zhuān)題，如有不足之處，歡迎留言指出。感謝朋友們對(duì)本站的支持！

您可能感興趣的文章: