Format data using pivot table in MS excel 2010 using c#.net

See more:

C#3.5

MS-Excel

pivot-table

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

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

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.