使用c＃.net在MS excel 2010中使用数据透视表格式化数据 [英] Format data using pivot table in MS excel 2010 using c#.net

查看：78
本文介绍了使用c＃.net在MS excel 2010中使用数据透视表格式化数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Hello Everyone，



我有一张包含太多原始数据的Excel工作表。现在我想使用数据透视表工具在同一工作簿中的另一个Excel工作表中更改特定格式的数据表示。



到目前为止表示所需的数据格式我在Excel中做了一些手动步骤，这些步骤在下面提到，因为这些步骤为我提供了客户所需的格式：



Hello Everyone,

I have a excel sheet containing too much raw data. Now I want to change the representation of the data in specific format in another Excel sheet in same workbook using Pivot Table facility.

As of now to represent the data in required format I am doing few manual steps in Excel, which are mentioned below as such steps provide me resultant Format required by Client:

1. Select the sheet
2. Insert Tab --> Pivot Table
3.Create Pivot Table Dialog Appear:Select a Table from the source sheet (Choose the data you want to analyze = Sheet1!$A:$I)
4. Select New Sheet where we want the pivot table report to be placed.
5. Select fields to add to reports- Field 1,Field 2,Field 3,Field 4,Field 5,Field 6,Field 7,Field 8,Field 9- As Row Labels
6. Pivot Table tools in Ribbon--> Options--> Deselect '+/- Buttons'
7. Pivot Table tools in ribbon--> Design --> Report Layout --> Show in tabular form
8. Pivot table tools in ribbon--> Design -->SubTotatls--> Do not show Subtotals
9. Pivot table tools in ribbon--> Design --> Grand Totals--> Off for Rows and Columns









excel表的源格式如下所述







the Source Format of excel sheet is mentioned below

Role	Class	ES	ClassAccess	Property/Relation	RelES	RelClass	Access	Sort
ECE	Cabinet	ES1	UPDATE	Equip Class	-	-	r	1001
ECE	Cabinet	ES1	UPDATE	Name	-	-	r	2001
ECE	Cabinet	ES1	UPDATE	KKS0	-	-	r	2002
ECE	Cabinet	ES1	UPDATE	KKS1a	-	-	r	2003
ECE	Cabinet	ES1	UPDATE	KKS1b	-	-	r	2004
ECE	Cabinet	ES1	UPDATE	KKS2a	-	-	r	2006
ECE	Cabinet	ES1	UPDATE	KKS2b	-	-	r	2007
ECE	Cabinet	ES1	UPDATE	Class of Equipment	-	-	r	4003
ECE	Cabinet	ES1	UPDATE	Design By	-	-	r	4004
ECE	Cabinet	ES1	UPDATE	Engineering Scenario	-	-	r	4005
ECE	Cabinet	ES1	UPDATE	Origin Key	-	-	r	4006
ECE	Cabinet	ES1	UPDATE	Description	-	-	r	4011
ECE	Cabinet	ES1	UPDATE	Black Box System	-	-	r	4023
ECE	Cabinet	ES1	UPDATE	Black Box Identifier	-	-	r	4024
ECE	Cabinet	ES1	UPDATE	Drawing Number	-	-	r	4028
ECE	Cabinet	ES1	UPDATE	to Location	ES5	Location	w	4031
ECE	Cabinet	ES1	UPDATE	to Location	ES5.1	Location	w	4031
ECE	Cabinet	ES1	UPDATE	Function Area	-	-	r	12002
ECE	Cabinet	ES1	UPDATE	DesignType	-	-	r	29001
ECE	Cabinet	ES1	UPDATE	Ordering Department	-	-	r	29011
ECE	Cabinet	ES1	UPDATE	Manufacturer	-	-	r	29012
ECE	Cabinet	ES1	UPDATE	Manufacturer Type	-	-	r	29013
ECE	Cabinet	ES1	UPDATE	Degree of Protection	-	-	r	30821
ECE	Cabinet	ES1	UPDATE	Hazardous Areas	-	-	w	34001
ECE	Cabinet	ES1	UPDATE	Skid Identifier	-	-	r	94001
ECE	Cabinet	ES1	UPDATE	Remark	-	-	r	97001
ECE	Cabinet	ES1	UPDATE	Input Originator	-	-	r	97011
EEI	Cabinet	ES1	UPDATE	Equip Class	-	-	r	1001
EEI	Cabinet	ES1	UPDATE	Name	-	-	r	2001
EEI	Cabinet	ES1	UPDATE	KKS0	-	-	r	2002
EEI	Cabinet	ES1	UPDATE	KKS1a	-	-	r	2003
EEI	Cabinet	ES1	UPDATE	KKS1b	-	-	r	2004









现在我想以编程方式完成所有这些手动步骤使用C＃.net。最终结果应该是以下格式：







Now I want to do all these manual steps programmatically using C#.net. the end result should be in Following format:

Role	Class	ES	ClassAccess	Property/Relation	RelES	RelClass	Access
EBE	Cabinet	ES2	CREATE	Black Box Identifier	-	-	w
				Black Box System	-	-	w
				Class of Equipment	-	-	w
				Degree of Protection	-	-	w
				Description	-	-	w
				Design By	-	-	w
				DesignType	-	-	w
				Drawing Number	-	-	w
				Engineering Scenario	-	-	w
				Equip Class	-	-	w
				Function Area	-	-	w
				Hazardous Areas	-	-	w
				Input Originator	-	-	w
				KKS0	-	-	w
				KKS1a	-	-	w
				KKS1b	-	-	w
				KKS2a	-	-	w
				KKS2b	-	-	w
				Manufacturer	-	-	w
				Manufacturer Type	-	-	w
				Name	-	-	w
				Ordering Department	-	-	w
				Origin Key	-	-	w
				Remark	-	-	w
				Skid Identifier	-	-	w
				to Location	ES5	Location	w
					ES5.1	Location	w
		ES2.1	UPDATE	Black Box Identifier	-	-	r
				Black Box System	-	-	r
				Class of Equipment	-	-	r
				Degree of Protection	-	-	r
				Description	-	-	r
				Design By	-	-	r
				DesignType	-	-	r







请帮帮我，急需（请提供相同的源代码）



我尝试过的事情：



做了研究但没找到相关的东西。




Please help me, Urgent requirement (Please mention Source code to do the same)

What I have tried:

Did research but not found related stuff.

推荐答案

A：


I）
4.选择我们希望放置数据透视表报告的新工作表。
5.选择要添加到报告的字段 - 字段1，字段2，字段3，字段4，字段5，字段6，字段7，字段8，字段9-作为行标签
6.透视功能区中的表格工具 - >选项 - >取消选择'+/- Buttons'
7.功能区中的数据透视表工具 - >设计 - >报告布局 - >以表格形式显示
8.功能区中的数据透视表工具 - >设计 - > SubTotatls - >不要显示Subtotals
9.功能区中的数据透视表工具 - >设计 - > Grand Totals - >关闭行和列
I) 4. Select New Sheet where we want the pivot table report to be placed. 5. Select fields to add to reports- Field 1,Field 2,Field 3,Field 4,Field 5,Field 6,Field 7,Field 8,Field 9- As Row Labels 6. Pivot Table tools in Ribbon--> Options--> Deselect '+/- Buttons' 7. Pivot Table tools in ribbon--> Design --> Report Layout --> Show in tabular form 8. Pivot table tools in ribbon--> Design -->SubTotatls--> Do not show Subtotals 9. Pivot table tools in ribbon--> Design --> Grand Totals--> Off for Rows and Columns









excel表的源格式如下所述







the Source Format of excel sheet is mentioned below

Role	Class	ES	ClassAccess	Property/Relation	RelES	RelClass	Access	Sort
ECE	Cabinet	ES1	UPDATE	Equip Class	-	-	r	1001
ECE	Cabinet	ES1	UPDATE	Name	-	-	r	2001
ECE	Cabinet	ES1	UPDATE	KKS0	-	-	r	2002
ECE	Cabinet	ES1	UPDATE	KKS1a	-	-	r	2003
ECE	Cabinet	ES1	UPDATE	KKS1b	-	-	r	2004
ECE	Cabinet	ES1	UPDATE	KKS2a	-	-	r	2006
ECE	Cabinet	ES1	UPDATE	KKS2b	-	-	r	2007
ECE	Cabinet	ES1	UPDATE	Class of Equipment	-	-	r	4003
ECE	Cabinet	ES1	UPDATE	Design By	-	-	r	4004
ECE	Cabinet	ES1	UPDATE	Engineering Scenario	-	-	r	4005
ECE	Cabinet	ES1	UPDATE	Origin Key	-	-	r	4006
ECE	Cabinet	ES1	UPDATE	Description	-	-	r	4011
ECE	Cabinet	ES1	UPDATE	Black Box System	-	-	r	4023
ECE	Cabinet	ES1	UPDATE	Black Box Identifier	-	-	r	4024
ECE	Cabinet	ES1	UPDATE	Drawing Number	-	-	r	4028
ECE	Cabinet	ES1	UPDATE	to Location	ES5	Location	w	4031
ECE	Cabinet	ES1	UPDATE	to Location	ES5.1	Location	w	4031
ECE	Cabinet	ES1	UPDATE	Function Area	-	-	r	12002
ECE	Cabinet	ES1	UPDATE	DesignType	-	-	r	29001
ECE	Cabinet	ES1	UPDATE	Ordering Department	-	-	r	29011
ECE	Cabinet	ES1	UPDATE	Manufacturer	-	-	r	29012
ECE	Cabinet	ES1	UPDATE	Manufacturer Type	-	-	r	29013
ECE	Cabinet	ES1	UPDATE	Degree of Protection	-	-	r	30821
ECE	Cabinet	ES1	UPDATE	Hazardous Areas	-	-	w	34001
ECE	Cabinet	ES1	UPDATE	Skid Identifier	-	-	r	94001
ECE	Cabinet	ES1	UPDATE	Remark	-	-	r	97001
ECE	Cabinet	ES1	UPDATE	Input Originator	-	-	r	97011
EEI	Cabinet	ES1	UPDATE	Equip Class	-	-	r	1001
EEI	Cabinet	ES1	UPDATE	Name	-	-	r	2001
EEI	Cabinet	ES1	UPDATE	KKS0	-	-	r	2002
EEI	Cabinet	ES1	UPDATE	KKS1a	-	-	r	2003
EEI	Cabinet	ES1	UPDATE	KKS1b	-	-	r	2004









现在我想以编程方式完成所有这些手动步骤使用C＃.net。最终结果应该是以下格式：







Now I want to do all these manual steps programmatically using C#.net. the end result should be in Following format:

Role	Class	ES	ClassAccess	Property/Relation	RelES	RelClass	Access
EBE	Cabinet	ES2	CREATE	Black Box Identifier	-	-	w
				Black Box System	-	-	w
				Class of Equipment	-	-	w
				Degree of Protection	-	-	w
				Description	-	-	w
				Design By	-	-	w
				DesignType	-	-	w
				Drawing Number	-	-	w
				Engineering Scenario	-	-	w
				Equip Class	-	-	w
				Function Area	-	-	w
				Hazardous Areas	-	-	w
				Input Originator	-	-	w
				KKS0	-	-	w
				KKS1a	-	-	w
				KKS1b	-	-	w
				KKS2a	-	-	w
				KKS2b	-	-	w
				Manufacturer	-	-	w
				Manufacturer Type	-	-	w
				Name	-	-	w
				Ordering Department	-	-	w
				Origin Key	-	-	w
				Remark	-	-	w
				Skid Identifier	-	-	w
				to Location	ES5	Location	w
					ES5.1	Location	w
		ES2.1	UPDATE	Black Box Identifier	-	-	r
				Black Box System	-	-	r
				Class of Equipment	-	-	r
				Degree of Protection	-	-	r
				Description	-	-	r
				Design By	-	-	r
				DesignType	-	-	r







请帮帮我，急需（请提供相同的源代码）



我尝试过的事情：



做了研究，但找不到相关的东西。




Please help me, Urgent requirement (Please mention Source code to do the same)

What I have tried:

Did research but not found related stuff.


这篇关于使用c＃.net在MS excel 2010中使用数据透视表格式化数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆