VSTO(C#)操作Excel数据透视表

VSTO(C#)控制Excel操作透视表

1.首先新建一个 VSTO工作簿。

2.创建一个winform窗体

2.选择相应的统计要素，就可以在数据透视表呈现相应的统计效果，单位和维修人用","隔开表示查询多个（数据是从Sql server读出来的）
在操作的时候，顺便生成sql语句。

DateTime beginDate = dtpBeginDate.Value.AddDays(-(dtpBeginDate.Value.Day - 1));   //日期重置为1号
            DateTime endDate = dtpEndDate.Value.AddDays(-(dtpEndDate.Value.Day - 1));
            int betMons = (endDate - beginDate).Days;
			if (betMons < 0)
			{
				MessageBox.Show("结束年月小于开始年月", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
				return;
			}
			Excel.Worksheet sht = Globals.ThisWorkbook.Sheets["自定义报表"];
			Excel.PivotTable pivt = sht.PivotTables(1);
			sht.Application.DisplayAlerts = false;
			try
			{
				pivt.ClearTable();   //先把数据透视表清除
			}
			finally
			{
				sht.Application.DisplayAlerts = true;
			}
			List<string> condiction = new List<string>();
			Dictionary<string, object> dict = new Dictionary<string, object>();     //存储查询键值对
			dict.Add("@beginDate", beginDate.ToString("yyyy/M/1"));
			dict.Add("@endDate", endDate.ToString("yyyy/M/1"));
			if (betMons != 0)
			{
				Excel.PivotField pivtf = pivt.PivotFields("年月");
				pivtf.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
			}
			if (ckbCatagory.Checked)   //类别
			{
				Excel.PivotField pivtf = pivt.PivotFields("类别");
				pivtf.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
				if (cbbCatagory.Text != "全部")
				{
					condiction.Add("catagory = @catagory");
					dict.Add("@catagory", cbbCatagory.Text);
				}
			}
			if (ckbDepartment.Checked)   //单位
			{
				Excel.PivotField pivtf = pivt.PivotFields("单位");
				pivtf.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
				if (txbDepartment.Text != "")
				{
					List<string> orCdt = new List<string>();
					string[] deparments = txbDepartment.Text.Trim().Split(',');
					for (int i = 0; i < deparments.Length; i++)
					{
						orCdt.Add("department = @department" + i);
						dict.Add("@department" + i, deparments[i]);
					}
					condiction.Add("(" + string.Join(" or ", orCdt) + ")");
				}
			}
			if (ckbClerk.Checked)   //维修人
			{
				Excel.PivotField pivtf = pivt.PivotFields("维修人");
				pivtf.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
				if (cbbCatagory.Text != "")
				{
					List<string> orCdt = new List<string>();
					string[] clerkNames = txbClerk.Text.Trim().Split(',');
					for (int i = 0; i < clerkNames.Length; i++)
					{
						orCdt.Add("clerk_name = @clerk_name" + i);
						dict.Add("@clerk_name" + i, clerkNames[i]);
					}
					condiction.Add("(" + string.Join(" or ", orCdt) + ")");
				}
			}

			if (ckbExpense.Checked)
				pivt.AddDataField(pivt.PivotFields("人工费"), "人工费用", Excel.XlConsolidationFunction.xlSum);
			if (ckbCertificate.Checked)
				pivt.AddDataField(pivt.PivotFields("维修单数量"), "单据数量", Excel.XlConsolidationFunction.xlSum);
			pivt.DataPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

			string cdtStr = string.Join(" and ", condiction);
			string sql = "select id,t1.long_id as 编号, department as 单位, catagory as 类别, expense_date as 年月, clerk_name as 维修人, human_expense as 人工费, "
						+ "materials_expense as 材料费, certificate_count as 维修单数量 from tb_totalInfo as t1 inner join tb_human as t2"
						+ " on t1.long_id = t2.long_id where (expense_date between @beginDate and @endDate) and " + cdtStr;
			DataTable dt = SqlDataBaseOperator.GetDataSet(sql, dict).Tables[0];
			Globals.Sheet2.IsUpdate = false;
			try
			{
				Globals.Sheet2.listObject1.DataSource = dt;
				DataRefresh.FillColor(Globals.Sheet2.listObject1, 2);
			}
			finally
			{
				Globals.Sheet2.IsUpdate = true;
			}

			pivt.PivotCache().Refresh();
			sht.Visible = Excel.XlSheetVisibility.xlSheetVisible;
			sht.Activate();

			toolStripStatusLabel1.Text = "查询完成";
		}

效果：
查询技师以及职院在2018年11月到2018年12月水电类别的统计

查询郭靖以及黄蓉在技师2018年11月到2018年12月全部类别的统计