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

本文主要分享了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月_利润_环比" 
      };

生成如下excel：

第一行不属于树形表头。

代码

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;
	// 默认叶子节点 
	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 + "]";
	}
}

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 {
				/* 单元格出现多少次，则该单元格就合并多少列 */
				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);
	}
}

总结

以上就是本文关于java实现生成excel树形表头完整代码示例的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题，如有不足之处，欢迎留言指出。感谢朋友们对本站的支持！