none
Delete/hide empty columns from a pivot table C# RRS feed

  • Question

  • When I create a pivotTable in Excel, there are three extra empty columns inserted with the table.

    I want to delete (or hide) these  empty columns before inserting the table in the worksheet.

    this is my code: 

    public void createPivotTable()
            {
                try
                {
                    Range pivotData = null;
    
                    Range pivotDestination = null;
                    String pivotTableName = query.TabName;
                    PivotTable pivotTable = null;
                    Microsoft.Office.Interop.Excel.Worksheet pivotTableSheet = null;
    
                    if (dataRange == null)
                        return false;
    
                    if (currentCell != null)
                    {
                        firstLine = currentCell.Row + 1;
                        firstColumn = currentCell.Column - 1;
                    }
                    else
                    {
                        firstLine = 0;
                        firstColumn = 0;
                    }
    
                    pivotData = getPivotTableDataRange();
                    pivotTableSheet = (Microsoft.Office.Interop.Excel.Worksheet)this.myApp.ActiveWorkbook.ActiveSheet;
    
                    try
                    {
                        String rangInR1C2 = this.dataWorkSheet.Name + "!" + myApp.ConvertFormula(pivotData.Address, XlReferenceStyle.xlA1, XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing) as String;
                        pivotTable = this.myApp.ActiveWorkbook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, rangInR1C2, 4).CreatePivotTable(pivotDestination, pivotTableName);
                    }
                    catch (Exception e)
                    {
                        pivotTableSheet.PivotTableWizard(
                            XlPivotTableSourceType.xlDatabase,
                            pivotData,
                            pivotDestination,
                            pivotTableName,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing
                        );
                    }
    
                    pivotTable = (PivotTable)pivotTableSheet.PivotTables(pivotTableName);
                    pivotTable.Format(XlPivotFormatType.xlPTNone);
                    pivotTable.RowAxisLayout(XlLayoutRowType.xlTabularRow);
                    try
                    {
                        reformatPivotTable(pivotTable, query.Definition);
                    }
                    catch (Exception e)
                    {
                        Console.WriteLog(ELogLevel.ERROR, "ERROR CREATING PIVOT TABLE " + e.Message + " / " + e.StackTrace);
                    }
    
                  /*
                      here, I want to delete/ hide empty columns
                  */  
    
              applyFormatPivotTable(pivotTable.TableRange1);
                    pivotTable.TableRange1.Select();
                    pivotTable.SaveData = true;                
                }
                catch (Exception e)
                {
                    Console.Write(ELogLevel.ERROR, "ERROR CREATING PIVOT TABLE " + e.Message + " / " + e.StackTrace);
                }
    
            }

    Is there any suggestions to solve this problem?

    Thanks.


               
                
              
               

               

    Monday, October 27, 2014 3:36 PM

All replies

  • Hello Said,

    I don't know why you got 3 empty columns in the created pivot table because I can't debug your code, your code is only a part of your project, some variables and methods are not available.

    If you follow the instructions in this document:

    http://msdn.microsoft.com/en-us/library/dd891207(v=office.12).aspx

    you'll successfully create a pivot table with no empty columns. If the sample code in this document doesn't help, please post your complete code so that others can reproduce your problem and give you some valuable information.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 28, 2014 9:01 AM
    Moderator
  • When I create a pivotTable in Excel, there are three extra empty columns inserted in the right of the table.

    I want to delete (or hide) these  empty columns in my c# code: 

    public void createPivotTable()
            {
                try
                {
                    Range pivotData = null;
    
                    Range pivotDestination = null;
                    String pivotTableName = query.TabName;
                    PivotTable pivotTable = null;
                    Microsoft.Office.Interop.Excel.Worksheet pivotTableSheet = null;
    
                    if (dataRange == null)
                        return false;
    
                    if (currentCell != null)
                    {
                        firstLine = currentCell.Row + 1;
                        firstColumn = currentCell.Column - 1;
                    }
                    else
                    {
                        firstLine = 0;
                        firstColumn = 0;
                    }
    
                    pivotData = getPivotTableDataRange();
                    pivotTableSheet = (Microsoft.Office.Interop.Excel.Worksheet)this.myApp.ActiveWorkbook.ActiveSheet;
    
                    try
                    {
                        String rangInR1C2 = this.dataWorkSheet.Name + "!" + myApp.ConvertFormula(pivotData.Address, XlReferenceStyle.xlA1, XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing) as String;
                        pivotTable = this.myApp.ActiveWorkbook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, rangInR1C2, 4).CreatePivotTable(pivotDestination, pivotTableName);
                    }
                    catch (Exception e)
                    {
                        pivotTableSheet.PivotTableWizard(
                            XlPivotTableSourceType.xlDatabase,
                            pivotData,
                            pivotDestination,
                            pivotTableName,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing,
                            Type.Missing
                        );
                    }
    
                    pivotTable = (PivotTable)pivotTableSheet.PivotTables(pivotTableName);
                    pivotTable.Format(XlPivotFormatType.xlPTNone);
                    pivotTable.RowAxisLayout(XlLayoutRowType.xlTabularRow);
                    try
                    {
                        reformatPivotTable(pivotTable, query.Definition);
                    }
                    catch (Exception e)
                    {
                        Console.WriteLog(ELogLevel.ERROR, "ERROR CREATING PIVOT TABLE " + e.Message + " / " + e.StackTrace);
                    }
    
                  /*
                      here, I want to delete/ hide empty columns
                  */  
    
              applyFormatPivotTable(pivotTable.TableRange1);
                    pivotTable.TableRange1.Select();
                    pivotTable.SaveData = true;                
                }
                catch (Exception e)
                {
                    Console.Write(ELogLevel.ERROR, "ERROR CREATING PIVOT TABLE " + e.Message + " / " + e.StackTrace);
                }
    
            }

    Any quick solution for getting these empty colunms and delete (or hide) them?


    said

    Tuesday, October 28, 2014 4:11 PM
  • Hello Deric,

    I have tried this:

    1. Loop over pivot table columns
    2. test if the current column is empty 
    3. if the column is blank i delete it

    This is the code:

     Range Columns = pivotTable.TableRange1.Columns;
     Range currentColumn;
     int currentColumnIndex = Columns.Count;
     bool contentfound = false;
     while (!contentfound && currentColumnIndex > 0)
           {
               if (IsBlankCol(currentSheet, currentColumnIndex))
                  {
                     currentColumn =(Range)Columns[currentColumnIndex];
                     currentColumn.Delete();
                     currentColumnIndex--;
                   }
                   else
                   {
                     contentfound = true;
                   }
            }
    private static bool IsBlankCol(Worksheet sheet, int col) 
            {
                for (int i = 0; i < sheet.UsedRange.Rows.Count; i++) 
                {
                    if (null != ((Range)sheet.Cells[i + 1, col]).Value) 
                    {
                        return false;
                    }
                }
                return true;
            }

    However I get the following exception : System.Runtime.InteropServices.COMException with the message "Impossible to hide this selection".

    So please, is there a problem with my code?

    Just for addition; when I tried to delete the empty columns manually from excel I have got the same message.


    said

    Wednesday, October 29, 2014 2:41 PM
  • Did you try Delete overload?

    currentColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);

    Please check out this: https://social.msdn.microsoft.com/Forums/vstudio/en-US/920180bf-1c84-40f7-b547-ba9532e309cd/deleting-a-row-in-excel-sheet-using-c

    I tried it and works:

    Microsoft.Office.Interop.Excel.Range range = excelApp.get_Range("A7:A7", Type.Missing);
                    range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
    

    Wednesday, October 29, 2014 3:46 PM
  • I tried :

    currentColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);

    But I get always at this line the same exception and the same message : System.Runtime.InteropServices.COMException with the message "Impossible to hide this selection". So how can I avoid this exception?

    I was also thinking about getting the entire column and hide it, but I didn't find any Range method for hiding. Any suggestion please?


    said


    • Edited by saidOuarrich Thursday, October 30, 2014 11:01 AM
    Thursday, October 30, 2014 11:01 AM