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.