自动合并单元格

最新推荐文章于 2021-09-12 17:48:28 发布

n_yan

最新推荐文章于 2021-09-12 17:48:28 发布

阅读量647

点赞数

分类专栏：小学文章标签： jquery excel 合并

本文链接：https://blog.csdn.net/n_yan/article/details/25116461

版权

小学专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一：使用jQuery合并table单元格

<script type="text/javascript" src="./js/jquery-1.7.2.js"></script>
<script type="text/javascript">
	jQuery.noConflict();
</script>
<style type="text/css">
td{
	text-align: center;
}
table.gridtable {
	font-family: verdana,arial,sans-serif;
	font-size:11px;
	color:#333333;
	border-width: 1px;
	border-color: #666666;
	border-collapse: collapse;
}
table.gridtable th {
	border-width: 1px;
	padding: 8px;
	border-style: solid;
	border-color: #666666;
	background-color: #dedede;
}
table.gridtable td {
	border-width: 1px;
	padding: 8px;
	border-style: solid;
	border-color: #666666;
	background-color: #ffffff;
}
</style>
</style>
</head>
<body>
	<table class="gridtable">
		<thead>
			<th>第一列</th>
			<th>第二列</th>
			<th>第三列</th>
			<th>第四列</th>
		</thead>
		<tbody class="sub">
			<tr>
				<td>a</td>
				<td>b</td>
				<td>e</td>
				<td>k</td>
			</tr>
			<tr>
				<td>a</td>
				<td>b</td>
				<td>e</td>
				<td>k</td>
			</tr>
			<tr>
				<td>a</td>
				<td>b</td>
				<td>e</td>
				<td>k</td>
			</tr>
			<tr>
				<td>a</td>
				<td>c</td>
				<td>e</td>
				<td>k</td>
			</tr>
			<tr>
				<td>a</td>
				<td>c</td>
				<td>f</td>
				<td>k</td>
			</tr>
			<tr>
				<td>a</td>
				<td>d</td>
				<td>g</td>
				<td>k</td>
			</tr>
			<tr>
				<td>a</td>
				<td>d</td>
				<td>h</td>
				<td>k</td>
			</tr>
		</tbody>
	</table>
	<div>
		<input type="button" value="合并单元格" οnclick="automerge()"/>
	</div>
	<script type="text/javascript">
	function automerge(){
		var size = jQuery("tbody.sub tr").length;
		merge(1,0,size);
	}
	function merge(col,first,size){
		if(col>3) return;//合并前三列
		var tdArr ;
		if(first == 0)
			tdArr = jQuery("tbody.sub tr:lt("+size+") td:nth-child("+col+")");
		else
			tdArr = jQuery("tbody.sub tr:gt("+first+"):lt("+size+") td:nth-child("+col+")");
		var start = 0;
		var str = jQuery(tdArr[0]).text();
		for(var i = 1 ; i < tdArr.length ; i++){
				if(jQuery(tdArr[i]).text() != str){
					jQuery(tdArr[start]).attr("rowspan",i-start);
					if(i-start > 1){
						if(start == 0)
							merge(col+1,first+start,i-start);
						else
							merge(col+1,first+start-1,i-start);
					}
					start = i;
					str = jQuery(tdArr[i]).text();
				}else if(i == size-1){
					jQuery(tdArr[start]).attr("rowspan",i-start+1);
					jQuery(tdArr[i]).css("display","none");
					if(i-start+1 > 1){
						if(start == 0)
							merge(col+1,first+start,i-start+1);
						else
							merge(col+1,first+start-1,i-start+1);
					}
				}else
					jQuery(tdArr[i]).css("display","none");
			}
		}
	</script>
</body>

效果图：

变为

二：自动合并excel单元格，需要用到jxl.jar包

public static void main(String[] args) {
		File file = new File("F:/my.xls");
		try {
			//修改excel文件
			Workbook workbook = Workbook.getWorkbook(file);
			WritableWorkbook wb = Workbook.createWorkbook(file, workbook);
			WritableSheet sheet = wb.getSheet(0);
			int rowCount = sheet.getRows();
			merge(sheet, 0, 1, rowCount-1);
			wb.write();
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**合并单元格*/
	private static void merge(WritableSheet sheet,int col,int startRow,int endRow) 
			throws RowsExceededException, WriteException{
		if(startRow >= endRow) return;
		if(col>2) return;//只合并前三列
		String tagStr = sheet.getCell(col, startRow).getContents();
		for(int i=startRow+1;i<=endRow;i++){
			String str = sheet.getCell(col, i).getContents();
			if(!tagStr.equals(str)){
				sheet.mergeCells(col, startRow, col, i-1);
				merge(sheet, col+1, startRow, i-1);
				startRow = i;
				tagStr = str;
			}else if(i==endRow){
				sheet.mergeCells(col, startRow, col, i);
				merge(sheet, col+1, startRow, i);
			}
		}
	}