本代码可以对Excel生成自定义的交叉表头,可以制作自定义的交叉报表。
代码使用的是Owc11库,要先安装这个库,再将其引用到项目中。
对excel主要的操作有:
1.新建excel进程
2.获得操作范围
3.对单元格进行纵向横向合并
4.设置样式
5.导出
6.以网面形式显示
private
void
button1_Click(
object
sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
...
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
FillInLeft(1, xlsheet, sList, xLevel);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
try
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
//导出
xlsheet.Export("../../TestOWC.htm", Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportHTML);
}
catch
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
MessageBox.Show("Export Error!");
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
string strFileName = @"D:TestOWC.htm";
Object refmissing = System.Reflection.Missing.Value;
axWebBrowser1.Navigate(strFileName, ref refmissing, ref refmissing, ref refmissing, ref refmissing);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
private
void
FillInLeft(
int
beginRow, Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet, Stack sList,
int
xlevel)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
...
{//xml中的节点写入Excel的顺序是:由上往上,由左往右
//beginRow:从第几行开始写入 xlsheet:目的Excel文件 sList:存入节点的堆栈 xlevle:深度
int row = beginRow; //int row = ((node)sList.Peek()).i;
while (sList.Count > 0)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
node node1 = (node)sList.Pop();
if (node1.i > 1)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{//有子节点且子节点在2个以上,需纵向合并
xlsheet.get_Range(xlsheet.Cells[row, node1.j], xlsheet.Cells[row + node1.i - 1, node1.j]).set_MergeCells(true);
}
if (node1.i == 0)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
if (node1.j < xlevel)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{//叶节点不在最下层,则横向合并
xlsheet.get_Range(xlsheet.Cells[row, node1.j], xlsheet.Cells[row, xlevel]).set_MergeCells(true);
}
}
xlsheet.Cells[row, node1.j] = node1.n.Attributes.GetNamedItem("name").Value;
if (node1.i == 0) //是叶节点,则下一个节点填在下一行
row += 1;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
Microsoft.Office.Interop.Owc11.Range range = xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[xlsheet.ActiveSheet.UsedRange.Rows.Count, xlsheet.ActiveSheet.UsedRange.Columns.Count]);
//单元格文本水平居中对齐
range.set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
//单元格文本垂直居中对齐
range.set_VerticalAlignment(Microsoft.Office.Interop.Owc11.XlVAlign.xlVAlignCenter);
//设置表头列宽
range.set_ColumnWidth(14);
//设置边框
range.Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
}