How would I set the alignment of cells in a pivot table created with EPPlus?

Question

At this time, this functionality has not been added to EPPlus. EPPlus is reviewing the addition of horizonal alignment to pivot table styles as a possible addition to future versions.

I have added a simple pivot table to an Excel Worksheet in Excel with EPPlus from a VB.NET application.

• The pivot table is created by the code below and the results are numerically correct.
• I would like to set the alignment of the cells in the body of the pivot table.

The code that I am using to create the pivot table is below.

   Private Sub AddPivotTable()

        ' Add a worksheet for the pivot table, get a cell range that contains the data to summarize.
        xlPivotsheet = xlPackage.Workbook.Worksheets.Add("Summary")

        Dim dataRange = xlWorksheet.Cells("A1:" + xlWorksheet.Dimension.End.Address)

        ' Create the Pivot Table on the "Summary" Worksheet.
        Dim pivotTable = xlPivotsheet.PivotTables.Add(xlPivotsheet.Cells("B4"),
                                                      dataRange,
                                                      "pivotTable")

        ' Set pivot table properties.
        pivotTable.MultipleFieldFilters = True
        pivotTable.RowGrandTotals = True
        pivotTable.ColumGrandTotals = False
        pivotTable.Compact = True
        pivotTable.CompactData = True
        pivotTable.DataOnRows = False
        pivotTable.GridDropZones = False
        pivotTable.Outline = False
        pivotTable.OutlineData = False
        pivotTable.ShowError = True
        pivotTable.ErrorCaption = "[error]"
        pivotTable.ShowHeaders = True
        pivotTable.UseAutoFormatting = True
        pivotTable.ApplyWidthHeightFormats = True
        pivotTable.ShowDrill = True
        pivotTable.FirstDataCol = 3
        pivotTable.RowHeaderCaption = "Provider"

        ' Set the Row Fields.
        Dim providerField = pivotTable.Fields("Provider")
        providerField.Sort = Table.PivotTable.eSortType.Ascending
        pivotTable.RowFields.Add(providerField)

        Dim serviceField = pivotTable.Fields("ServiceCode")
        pivotTable.RowFields.Add(serviceField)

        ' Set the Column Fields.
        Dim monthField = pivotTable.Fields("Month")
        monthField.Sort = Table.PivotTable.eSortType.Ascending
        pivotTable.ColumnFields.Add(monthField)

        ' Set the Count Fields.
        Dim countField = pivotTable.Fields("Provider")

       ' Format(?)
        xlPackage.Save()

        xlPivotsheet.Columns(2).AutoFit()

        If xlPivotsheet.Dimension IsNot Nothing Then

            Dim pivotColumns = xlPivotsheet.Dimension.Columns

            For col As Integer = 2 To 1 + pivotColumns
                xlPivotsheet.Columns(col).Width = 10
                xlPivotsheet.Columns(col).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center
            Next

        End If


End Sub

• The workbook is saved following the call to AddPivotTable() via xlPackage.Save.

Questions and What I have tried...

1. The pivot table results are not created until the user Enables the workbook for editing. (Email recipients will notice that no pivot table appears when they do a preview in Outlook). Is there a EPPlus function that aggregates and writes the pivot table?
2. I tried the xlPivotSheet.Cells... call at the bottom of the AddPivotTable procedure with the known coordinates of the pivot table for the test data. This has no effect on the horizontal alignment.
3. xlPackage.Save() is called after the call to AddPivotTable().
4. I also tried setting the alignment of the columns. This had no effect on the cell alignment of the columns. An example is below:

xlPivotsheet.Column(5).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center

5. Does anyone have an example of programmatically setting the alignment of cells in a pivot table?

6. I looked at the pivotTable.Fields.Styles but found no way to set the alignment of the ExcelPivotTableAreaStyle object returned by AddAllData().

7. Is there any way call to have the pivot table calculated and written to the worksheet so that the Worksheet Dimensions object is not nothing? I tried doing a xlPackage.Save(), but Dimensions is still null (or Nothing). This may require me to save, close, and re-open the package. Calculate had no effect and I do not see a refresh.

• Note: Dimension does not apply to a worksheet that only contains a pivot table.

9. Neither of the methods below affect the horizontal alignment.

• The first method tries to apply a center alignment to the cells in the data area of the pivot table.

            ' Create a range object to center-align the cells.
            Dim topLeftRow As Integer = 5
            Dim topLeftCol As Integer = 3
            Dim bottomRightRow As Integer = 5 + distinctProviders + mainTable.Rows.Count
            Dim bottomRightCol As Integer = 3 + distinctMonths

            Dim pivotRange As ExcelRange = xlPivotsheet.Cells(topLeftRow, topLeftCol, bottomRightRow, bottomRightCol)

            With pivotRange.Style
                .HorizontalAlignment = ExcelHorizontalAlignment.Center
            End With

• The second method used the AddAllData() to obtain theExcelPivotTableAreaStyle object for data area. There are no alignment properties in ExcelPivotTableAreaStyle returned by AddAllData().