Set Conditional Formating for "Access" Column if Cell of the Access Column Contain W then Set Background color Light Green/ If R then Set Misty Rose Cell's Back Ground ColorĮxcel.FormatCondition SetBgColorForAccessW = sheet2.get_Range("H:H", Type.Missing).FormatConditions.Add(, Type.Missing, Type.Missing, Type.Missing, "w",, Type.Missing, Type.Missing) () // Used to Autoset the column width according to data Pvt.ShowDrillIndicators = false // Used to remove the Expand/ Collapse Button from each cellĮxcel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("ColumnA")) // Create a Pivot Field in Pivot tableįld.Orientation = // Add the pivot field as Row Fieldįld.set_Subtotals(1, false) //Remove Subtotals for each row and columnįld = ((Excel.PivotField)pvt.PivotFields("ColumnB")) įld.Orientation = įld = ((Excel.PivotField)pvt.PivotFields("ColumnC")) įld = ((Excel.PivotField)pvt.PivotFields("ColumnD")) įld = ((Excel.PivotField)pvt.PivotFields("ColumnE")) įld = ((Excel.PivotField)pvt.PivotFields("ColumnF")) įld = ((Excel.PivotField)pvt.PivotFields("ColumnG")) įld = ((Excel.PivotField)pvt.PivotFields("ColumnH")) įld = ((Excel.PivotField)pvt.PivotFields("ColumnI")) įld.Orientation = // Sort column set as datafield to show the Pivot table as per requirement- It will show the total count of data and not needed so later on we will hide this Column Pch.Add(.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(sheet2.Cells, "PivTbl_1", Type.Missing, Type.Missing) // Create Pivot tableĮxcel.PivotTable pvt = sheet2.PivotTables("PivTbl_1") as Excel.PivotTable Sheet2.Name = "Pivot Table" // Assigned sheet NameĮxcel.Range oRange = excelworksheet.UsedRange Įxcel.PivotCache oPivotCache = (Excel.PivotCache)excelWorkBook.PivotCaches().Add(, oRange) // Set the Source data range from First sheetĮxcel.Range oRange2 = sheet2.Cells Įxcel.PivotCaches pch = excelWorkBook.PivotCaches() Please mention source code to do the same.Įxcel.Application excelApp = new Excel.Application() Įxcel.Workbook excelWorkBook = ("c:\\Users\\username\\Desktop\\Test.xlsx") Įxcel.Worksheet excelworksheet = excelWorkBook.ActiveSheet Įxcel.Worksheet sheet2 = () // Added new sheet to create Pivot Table the end result should be in Following format: Now I want to do all these manual steps programmatically using C#.net. The Source Format of excel sheet is mentioned below Pivot table tools in ribbon-> Design -> Grand Totals-> Off for Rows and Columns.Pivot table tools in ribbon-> Design ->SubTotatls-> Do not show Subtotals.Pivot Table tools in ribbon-> Design -> Report Layout -> Show in tabular form.Pivot Table tools in Ribbon-> Options-> Deselect '+/- Buttons'.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |