C#导出excel复杂表格(单元各合并)

一、效果展示

二、代码实现

引用dll

using Aspose.Cells;

DataTable数据保存到Excel

/// <summary>
/// DataTable数据表保存至Excel （合并行报表）
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
/// <param name="excelParm"></param>
public static void ToExcel1(DataTable dt, string filePath, ExcelParm excelParm)
{
	string subTitle = string.Empty;




	//新建工作簿  
	Workbook wb = new Workbook();
	//新建工作表  
	Worksheet ws = wb.Worksheets[0];


	ws.Name = dt.TableName;


	int rowIndex = 3;
	int colIndex = 0;
	int colCount = dt.Columns.Count;
	int rowCount = dt.Rows.Count;


	ws.Cells.SetRowHeight(rowIndex, 25);//设置行高


	//创建样式
	Style style = wb.Styles[wb.Styles.Add()];//新增样式  
	style.HorizontalAlignment = TextAlignmentType.Center; //单元格内容的水平对齐方式文字居中
	style.Font.Name = "宋体"; //字体
	style.Font.IsBold = true; //设置粗体
	//style.Font.Color = Color.White;//设置字体颜色
	style.Font.Size = 10; //设置字体大小
	//style.ForegroundColor = Color.FromArgb(0, 196, 180); //背景色
	style.Pattern = BackgroundType.Solid;


	style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
	style.Borders[BorderType.TopBorder].Color = Color.Black;
	style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
	style.Borders[BorderType.BottomBorder].Color = Color.Black;
	style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
	style.Borders[BorderType.LeftBorder].Color = Color.Black;
	style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
	style.Borders[BorderType.RightBorder].Color = Color.Black;




	//列名的处理
	for (int i = 0; i < colCount; i++)
	{
		ws.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
		ws.Cells[rowIndex, colIndex].SetStyle(style);//给单元格关联样式
		colIndex++;
	}




	Style style2 = wb.Styles[wb.Styles.Add()];//新增样式
	style2.Font.Name = "宋体";//文字字体
	style2.Font.Size = 10;//文字大小 
	style2.ShrinkToFit = true;
	style2.VerticalAlignment = TextAlignmentType.Center;


	style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
	style2.Borders[BorderType.TopBorder].Color = Color.Black;
	style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
	style2.Borders[BorderType.BottomBorder].Color = Color.Black;
	style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
	style2.Borders[BorderType.LeftBorder].Color = Color.Black;
	style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
	style2.Borders[BorderType.RightBorder].Color = Color.Black;


	Style style3 = wb.Styles[wb.Styles.Add()];//组织标题样式
	style3.Font.Name = "宋体";//文字字体
	style3.Font.Size = 16;//文字大小 
	style3.HorizontalAlignment = TextAlignmentType.Center;
	ws.Cells.SetRowHeight(0, 25);//设置行高
	ws.Cells.Merge(0, 0, 1, dt.Columns.Count);
	ws.Cells[0, 0].PutValue(excelParm.OrganName);
	ws.Cells[0, 0].SetStyle(style3);


	Style style5 = wb.Styles[wb.Styles.Add()];//标题样式
	style5.Font.Name = "宋体";//文字字体
	style5.Font.IsBold = true; //设置粗体
	style5.Font.Size = 14;//文字大小 
	style5.HorizontalAlignment = TextAlignmentType.Center;
	ws.Cells.SetRowHeight(1, 25);//设置行高
	ws.Cells.Merge(1, 0, 1, dt.Columns.Count);
	ws.Cells[1, 0].PutValue(dt.TableName);
	ws.Cells[1, 0].SetStyle(style5);


	Style style4 = wb.Styles[wb.Styles.Add()];//新增查询条件标题样式
	style4.Font.Name = "宋体";//文字字体
	style4.Font.IsBold = true; //设置粗体
	style4.Font.Size = 10;//文字大小 
	ws.Cells.SetRowHeight(2, 25);//设置行高
	ws.Cells.Merge(2, 0, 1, dt.Columns.Count);
	ws.Cells[2, 0].PutValue(subTitle);
	ws.Cells[2, 0].SetStyle(style4);


	rowIndex++;


	for (int i = 0; i < rowCount; i++)
	{
		ws.Cells.SetRowHeight(rowIndex, 25);//设置行高
		colIndex = 0;
		for (int j = 0; j < colCount; j++)
		{
			ws.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString() == "" ? null : dt.Rows[i][j].ToString());
			style2.ForegroundColor = Color.White;
			style2.Pattern = BackgroundType.Solid;
			ws.Cells[rowIndex, colIndex].SetStyle(style2);//给单元格关联样式
			colIndex++;
		}
		rowIndex++;
	}


	//设置所有列为自适应列宽 
	ws.AutoFitColumns();


	for (int col = 0; col < colCount; col++)
	{
		ws.Cells.SetColumnWidthPixel(col, ws.Cells.GetColumnWidthPixel(col) + 20);
	}




	#region 合并单元格
	int mergeDateStart = 4;
	int mergeDate = 1;


	int mergeAreaStart = 4;
	int mergeArea = 1;
	for (var i = 0; i < dt.Rows.Count; i++)
	{
		if (i + 1 < dt.Rows.Count)
		{
			//日期
			if (dt.Rows[i]["日期"].ToString() != dt.Rows[i + 1]["日期"].ToString())
			{
				ws.Cells.Merge(mergeDateStart, 0, mergeDate, 1);
				mergeDateStart += mergeDate;
				mergeDate = 1;
			}
			else
			{
				mergeDate++;
			}


			//区域
			if (dt.Rows[i]["区域"].ToString() != dt.Rows[i + 1]["区域"].ToString())
			{
				ws.Cells.Merge(mergeAreaStart, 1, mergeArea, 1);
				mergeAreaStart += mergeArea;
				mergeArea = 1;
			}
			else
			{
				mergeArea++;
			}
		}
		else
		{
			//日期
			ws.Cells.Merge(mergeDateStart, 0, mergeDate, 1);
			//区域
			ws.Cells.Merge(mergeAreaStart, 1, mergeArea, 1);
		}
	}
	#endregion




	string fullUpLoadPath = HttpContext.Current.Server.MapPath("~/UpLoad/Excel/");
	//检查本地上传的物理路径是否存在，不存在则创建
	if (!System.IO.Directory.Exists(fullUpLoadPath))
	{
		System.IO.Directory.CreateDirectory(fullUpLoadPath);
	}


	filePath = GetMapPath(filePath);
	if (System.IO.File.Exists(filePath))
		System.IO.File.Delete(filePath);
	System.IO.FileStream fs = System.IO.File.Create(filePath);
	fs.Close();
	wb.Save(filePath);
}

以上就完成了，快去试试吧。

• C#实现西门子S7-1200、200 SMART PLC之间通信
• C#连接FTP实现文件上传下载
• C#实现MQTT通讯
• C#实现串口通讯
• C#实现WebSocket服务和通讯
• C#实现UDP通讯
• C#实现TCP通讯
• C#调用WebApi请求常用的两种方式
• C# .NET MVC实现图片、视频上传