Add more than one value column to a pivot table

[mba91000]

Hello,
I am using Spire.xls to create a pivot table.
The problem is that when I add more than two value columns, the resulting file cannot be opened, there is a bug and the pivot table is not created.
I have no problem when the pivot table has a single column value (sum)

Code: Select all

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

            Dim workbook As New Workbook()
            Dim sheet As Worksheet = workbook.Worksheets(0)
            Dim sFileName As String = String.Empty

            ' Set the value to the cells
            sheet.Range("A1").Value = "Product"
            sheet.Range("B1").Value = "Month"
            sheet.Range("C1").Value = "DAY"
            sheet.Range("D1").Value = "Nombre"
            sheet.Range("E1").Value = "Facture"
            sheet.Range("F1").Value = "Divers"

            sheet.Range("A2").Value = "SpireDoc"
            sheet.Range("A3").Value = "SpireDoc"
            sheet.Range("A4").Value = "SpireXls"
            sheet.Range("A5").Value = "SpireDoc"
            sheet.Range("A6").Value = "SpireXls"
            sheet.Range("A7").Value = "SpireXls"

            sheet.Range("B2").Value = "January"
            sheet.Range("B3").Value = "February"
            sheet.Range("B4").Value = "January"
            sheet.Range("B5").Value = "January"
            sheet.Range("B6").Value = "February"
            sheet.Range("B7").Value = "February"

            sheet.Range("C2").Value = "Lundi"
            sheet.Range("C3").Value = "Mardi"
            sheet.Range("C4").Value = "Mercredi"
            sheet.Range("C5").Value = "Jeudi"
            sheet.Range("C6").Value = "Vendredi"
            sheet.Range("C7").Value = "Samedi"

            sheet.Range("D2").Value = "20"
            sheet.Range("D3").Value = "15"
            sheet.Range("D4").Value = "19"
            sheet.Range("D5").Value = "27"
            sheet.Range("D6").Value = "8"
            sheet.Range("D7").Value = "40"

            sheet.Range("E2").Value = "120"
            sheet.Range("E3").Value = "115"
            sheet.Range("E4").Value = "119"
            sheet.Range("E5").Value = "127"
            sheet.Range("E6").Value = "18"
            sheet.Range("E7").Value = "140"

            sheet.Range("F2").Value = "200"
            sheet.Range("F3").Value = "105"
            sheet.Range("F4").Value = "109"
            sheet.Range("F5").Value = "207"
            sheet.Range("F6").Value = "58"
            sheet.Range("F7").Value = "400"


            'Add a PivotTable to the worksheet
            Dim dataRange As CellRange = sheet.Range("A1:F7")
            Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange)

            Dim wbPivot As Worksheet = workbook.CreateEmptySheet("Test Pivot")
            Dim pt As PivotTable = wbPivot.PivotTables.Add("Pivot Table", sheet.Range("A1"), cache)

            'Drag the fields to the row area.
            Dim pf As PivotField = TryCast(pt.PivotFields("Product"), PivotField)
            pf.Axis = AxisTypes.Row
            Dim pf2 As PivotField = TryCast(pt.PivotFields("Month"), PivotField)
            pf2.Axis = AxisTypes.Row
            Dim pf3 As PivotField = TryCast(pt.PivotFields("DAY"), PivotField)
            pf3.Axis = AxisTypes.Row

            'Drag the field to the data area.
            pt.DataFields.Add(pt.PivotFields("Nombre"), "SUM of Nombre", SubtotalTypes.Sum)
            pt.DataFields.Add(pt.PivotFields("Facture"), "SUM of Facture", SubtotalTypes.Sum)
            pt.DataFields.Add(pt.PivotFields("Divers"), "SUM of Divers", SubtotalTypes.Sum)

            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

            sFileName = Page.MapPath("./Data/Test.xlsx")
            workbook.SaveToFile(sFileName, ExcelVersion.Version2010)


        End Sub


[rachel.lei]

Hello,

Thanks for your inquiry.
Please refer to the following modified code. If there is any other question, please do no hesitate to contact us.

Code: Select all

        Dim workbook As New Workbook()
        Dim sheet As Worksheet = workbook.Worksheets(0)
        Dim sFileName As String = String.Empty

        ' Set the value to the cells
        sheet.Range("A1").Value = "Product"
        sheet.Range("B1").Value = "Month"
        sheet.Range("C1").Value = "DAY"
        sheet.Range("D1").Value = "Nombre"
        sheet.Range("E1").Value = "Facture"
        sheet.Range("F1").Value = "Divers"

        sheet.Range("A2").Value = "SpireDoc"
        sheet.Range("A3").Value = "SpireDoc"
        sheet.Range("A4").Value = "SpireXls"
        sheet.Range("A5").Value = "SpireDoc"
        sheet.Range("A6").Value = "SpireXls"
        sheet.Range("A7").Value = "SpireXls"

        sheet.Range("B2").Value = "January"
        sheet.Range("B3").Value = "February"
        sheet.Range("B4").Value = "January"
        sheet.Range("B5").Value = "January"
        sheet.Range("B6").Value = "February"
        sheet.Range("B7").Value = "February"

        sheet.Range("C2").Value = "Lundi"
        sheet.Range("C3").Value = "Mardi"
        sheet.Range("C4").Value = "Mercredi"
        sheet.Range("C5").Value = "Jeudi"
        sheet.Range("C6").Value = "Vendredi"
        sheet.Range("C7").Value = "Samedi"

        sheet.Range("D2").Value = "20"
        sheet.Range("D3").Value = "15"
        sheet.Range("D4").Value = "19"
        sheet.Range("D5").Value = "27"
        sheet.Range("D6").Value = "8"
        sheet.Range("D7").Value = "40"

        sheet.Range("E2").Value = "120"
        sheet.Range("E3").Value = "115"
        sheet.Range("E4").Value = "119"
        sheet.Range("E5").Value = "127"
        sheet.Range("E6").Value = "18"
        sheet.Range("E7").Value = "140"

        sheet.Range("F2").Value = "200"
        sheet.Range("F3").Value = "105"
        sheet.Range("F4").Value = "109"
        sheet.Range("F5").Value = "207"
        sheet.Range("F6").Value = "58"
        sheet.Range("F7").Value = "400"


        'Add a PivotTable to the worksheet
        Dim dataRange As CellRange = sheet.Range("A1:F7")
        Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange)

        Dim wbPivot As Worksheet = workbook.CreateEmptySheet("Test Pivot")
        Dim pt As PivotTable = wbPivot.PivotTables.Add("Pivot Table", wbPivot.Range("A1"), cache)

        'Drag the fields to the row area.
        Dim pf As PivotField = TryCast(pt.PivotFields("Product"), PivotField)
        pf.Axis = AxisTypes.Row
        Dim pf2 As PivotField = TryCast(pt.PivotFields("Month"), PivotField)
        pf2.Axis = AxisTypes.Row
        Dim pf3 As PivotField = TryCast(pt.PivotFields("DAY"), PivotField)
        pf3.Axis = AxisTypes.Row

        'Drag the field to the data area.
        pt.DataFields.Add(pt.PivotFields("Nombre"), "SUM of Nombre", SubtotalTypes.Sum)
        pt.DataFields.Add(pt.PivotFields("Facture"), "SUM of Facture", SubtotalTypes.Sum)
        pt.DataFields.Add(pt.PivotFields("Divers"), "SUM of Divers", SubtotalTypes.Sum)

        pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

        '!!!
        'Calculate data
        pt.CalculateData()
        'Autofit columns generated by the pivotTable
        wbPivot.AutoFitColumn(1)
        wbPivot.AutoFitColumn(2)
        wbPivot.AutoFitColumn(3)
        wbPivot.AutoFitColumn(4)

        sFileName = "Test.xlsx"
        workbook.SaveToFile(sFileName, ExcelVersion.Version2010)

Sincerely,
Rachel
E-iceblue support team

[mba91000]

Hi Racgel,
Thanks , but it doesn't work.

Best regards

[rachel.lei]

Hello,

Thanks for your response.
This code works fine on my side. The following is my test project, which uses the latest Spire.XLS Pack(Hotfix) Version:10.7.2. Please run it directly on your side.
http://www.e-iceblue.com/downloads/demo/22204Demo.zip

If the issue persists, to help us further investigate it, please provide your test environment information, such as your OS information (E.g. Windows 7, 64 bit) and region setting (E.g. China, Chinese).

Sincerely,
Rachel
E-iceblue support team

[mba91000]

Hello and thank you Rachel,
the problem may indeed be related to my development environment.
WIN 7 pro, Service pack 1,
System : 64 bits
Visual studio 2015.

I will come back for more.
Thanks again Rachel

[rachel.lei]

Hello,

Thanks for your response.
I tested the code in an environment similar to yours but still didn’t encounter any issue. I suggest you test your case on another computer to see if the issue still occurs.
I'll wait for your further feedback.

Sincerely,
Rachel
E-iceblue support team

[mba91000]

Hi Rachel,
I come back to you as promised.
I created a pivottable that works well (but in a WinForm project, not ASP.Net as I would have liked)
I still have questions:
-I want to format the columns as [h]: mm , the initial data are minutes
(Example : 72 minutes => 72 / (24x60) => 0.05 => 1:12)
-Is it possible to order a pivot table in relation to a column?
Thank you

Here is my code

Code: Select all

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim workbook As New Workbook()
        Dim sheet As Worksheet = workbook.Worksheets(0)
        Dim sFileName As String = String.Empty
        Const sColumFormat As String = "[h]:mm"

        '1-Data source of the PivotTables
        ' Set the value to the cells
        sheet.Range("A1").Value = "Av"
        sheet.Range("B1").Value = "Numero"
        sheet.Range("C1").Value = "Titre"
        sheet.Range("D1").Value = "Temp1"
        sheet.Range("E1").Value = "Temp2"
        sheet.Range("F1").Value = "Temp3"
        sheet.Range("G1").Value = "Temp4"
        sheet.Range("H1").Value = "Ass"

        sheet.Range("A2").Value = "PS"
        sheet.Range("A3").Value = "PS"
        sheet.Range("A4").Value = "BP"
        sheet.Range("A5").Value = "SR"
        sheet.Range("A6").Value = "MN"
        sheet.Range("A7").Value = "TR"

        sheet.Range("B2").Value = "14256"
        sheet.Range("B3").Value = "12034"
        sheet.Range("B4").Value = "12545"
        sheet.Range("B5").Value = "12034"
        sheet.Range("B6").Value = "12034"
        sheet.Range("B7").Value = "14256"

        sheet.Range("C2").Value = "dos1"
        sheet.Range("C3").Value = "dos2"
        sheet.Range("C4").Value = "dos3"
        sheet.Range("C5").Value = "dos2"
        sheet.Range("C6").Value = "dos4"
        sheet.Range("C7").Value = "dos5"

        ' Minutes/(24*60) => Time format [h]:mm in Excel
        sheet.Range("D2").Value = 0.0659722222222222
        sheet.Range("D3").Value = 0.04
        sheet.Range("D4").Value = 0.011
        sheet.Range("D5").Value = 0.0633333333333333
        sheet.Range("D6").Value = 0.0733333333333333
        sheet.Range("D7").Value = 0.0933333333333333

        sheet.Range("E2").Value = 0.0416666666666667
        sheet.Range("E3").Value = 0.1875
        sheet.Range("E4").Value = 0.160138888888889
        sheet.Range("E5").Value = 0.190138888888889
        sheet.Range("E6").Value = 0.135
        sheet.Range("E7").Value = 0.125

        sheet.Range("F2").Value = 0.55333333333333
        sheet.Range("F3").Value = 0.045
        sheet.Range("F4").Value = 0.23333333333333
        sheet.Range("F5").Value = 0.0121
        sheet.Range("F6").Value = 0.0763888888888889
        sheet.Range("F7").Value = 0.0763888888888889

        sheet.Range("G2").Value = 0.0198888888888889
        sheet.Range("G3").Value = 0.145833333333333
        sheet.Range("G4").Value = 0.708333333333333
        sheet.Range("G5").Value = 0.0406666666666667
        sheet.Range("G6").Value = 0.0416666666666667
        sheet.Range("G7").Value = 0.0753888888888889

        sheet.Range("H2").Value = "SN"
        sheet.Range("H3").Value = "SN"
        sheet.Range("H4").Value = "BR"
        sheet.Range("H5").Value = "BR"
        sheet.Range("H6").Value = "MT"
        sheet.Range("H7").Value = "MT"

        sheet.Name = "DataSource"


        ''Format [h]:mm
        'sheet.Columns(3).NumberFormat = sColumFormat
        'sheet.Columns(4).NumberFormat = sColumFormat
        'sheet.Columns(5).NumberFormat = sColumFormat
        'sheet.Columns(6).NumberFormat = sColumFormat

        'Add a PivotTable to the worksheet
        Dim dataRange As CellRange = sheet.Range("A1:H7")
        Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange)

        '2-Pivot Ass
        Dim wsAss As Worksheet = workbook.Worksheets(1)
        Dim ptAss As PivotTable = wsAss.PivotTables.Add("Pivot Table AS", wsAss.Range("A1"), cache)

        'Drag the fields to the row area.
        Dim pf As PivotField = TryCast(ptAss.PivotFields("Ass"), PivotField)
        pf.Axis = AxisTypes.Row
        Dim pf2 As PivotField = TryCast(ptAss.PivotFields("Titre"), PivotField)
        pf2.Axis = AxisTypes.Row
        Dim pf3 As PivotField = TryCast(ptAss.PivotFields("Av"), PivotField)
        pf3.Axis = AxisTypes.Row

        'Drag the field to the data area.
        ptAss.DataFields.Add(ptAss.PivotFields("Temp1"), "SUM of Temp1", SubtotalTypes.Sum)
        ptAss.DataFields.Add(ptAss.PivotFields("Temp2"), "SUM of Temp2", SubtotalTypes.Sum)
        ptAss.DataFields.Add(ptAss.PivotFields("Temp3"), "SUM of Temp3", SubtotalTypes.Sum)
        ptAss.DataFields.Add(ptAss.PivotFields("Temp4"), "SUM of Temp4", SubtotalTypes.Sum)

        ptAss.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

        'Calculate data
        ptAss.CalculateData()
        'Autofit columns generated by the pivotTable
        wsAss.AutoFitColumn(1)
        wsAss.AutoFitColumn(2)
        wsAss.AutoFitColumn(3)
        wsAss.AutoFitColumn(4)

        ''Format [h]:mm
        'wsAss.Columns(3).NumberFormat = sColumFormat
        'wsAss.Columns(4).NumberFormat = sColumFormat
        'wsAss.Columns(5).NumberFormat = sColumFormat
        'wsAss.Columns(6).NumberFormat = sColumFormat

        wsAss.Name = "Temps Ass"

        '3-Pivot Av
        Dim wsAv As Worksheet = workbook.Worksheets(2)
        Dim ptAv = wsAv.PivotTables.Add("Pivot Table AV", wsAv.Range("A1"), cache)
        'Drag the fields to the row area.
        Dim pfAv As PivotField = TryCast(ptAv.PivotFields("Av"), PivotField)
        pfAv.Axis = AxisTypes.Row

        'Drag the field to the data area.
        ptAv.DataFields.Add(ptAv.PivotFields("Temp1"), "SUM of Temp1", SubtotalTypes.Sum)
        ptAv.DataFields.Add(ptAv.PivotFields("Temp2"), "SUM of Temp2", SubtotalTypes.Sum)
        ptAv.DataFields.Add(ptAv.PivotFields("Temp3"), "SUM of Temp3", SubtotalTypes.Sum)
        ptAv.DataFields.Add(ptAv.PivotFields("Temp4"), "SUM of Temp4", SubtotalTypes.Sum)

        ptAv.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

        'Calculate data
        ptAv.CalculateData()
        'Autofit columns generated by the pivotTable
        wsAv.AutoFitColumn(1)
        wsAv.AutoFitColumn(2)
        wsAv.AutoFitColumn(3)
        wsAv.AutoFitColumn(4)

        ''Format [h]:mm
        'wsAv.Columns(1).NumberFormat = sColumFormat
        'wsAv.Columns(2).NumberFormat = sColumFormat
        'wsAv.Columns(3).NumberFormat = sColumFormat
        'wsAv.Columns(4).NumberFormat = sColumFormat
        wsAv.Name = "Temps Av"

        sFileName = "Test_" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".xlsx"
        workbook.SaveToFile(sFileName, ExcelVersion.Version2010)
        Process.Start(sFileName)
    End Sub


[rachel.lei]

Hello,

Thanks for your feedback.
Regarding setting the data format, below is the code for your reference.

Code: Select all

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim workbook As New Workbook()
        Dim sheet As Worksheet = workbook.Worksheets(0)
        Dim sFileName As String = String.Empty
        Const sColumFormat As String = "[h]:mm"

        '1-Data source of the PivotTables
        ' Set the value to the cells
        sheet.Range("A1").Value = "Av"
        sheet.Range("B1").Value = "Numero"
        sheet.Range("C1").Value = "Titre"
        sheet.Range("D1").Value = "Temp1"
        sheet.Range("E1").Value = "Temp2"
        sheet.Range("F1").Value = "Temp3"
        sheet.Range("G1").Value = "Temp4"
        sheet.Range("H1").Value = "Ass"

        sheet.Range("A2").Value = "PS"
        sheet.Range("A3").Value = "PS"
        sheet.Range("A4").Value = "BP"
        sheet.Range("A5").Value = "SR"
        sheet.Range("A6").Value = "MN"
        sheet.Range("A7").Value = "TR"

        sheet.Range("B2").Value = "14256"
        sheet.Range("B3").Value = "12034"
        sheet.Range("B4").Value = "12545"
        sheet.Range("B5").Value = "12034"
        sheet.Range("B6").Value = "12034"
        sheet.Range("B7").Value = "14256"

        sheet.Range("C2").Value = "dos1"
        sheet.Range("C3").Value = "dos2"
        sheet.Range("C4").Value = "dos3"
        sheet.Range("C5").Value = "dos2"
        sheet.Range("C6").Value = "dos4"
        sheet.Range("C7").Value = "dos5"

        ' Minutes/(24*60) => Time format [h]:mm in Excel
        sheet.Range("D2").Value = 0.0659722222222222
        sheet.Range("D3").Value = 0.04
        sheet.Range("D4").Value = 0.011
        sheet.Range("D5").Value = 0.0633333333333333
        sheet.Range("D6").Value = 0.0733333333333333
        sheet.Range("D7").Value = 0.0933333333333333

        sheet.Range("E2").Value = 0.0416666666666667
        sheet.Range("E3").Value = 0.1875
        sheet.Range("E4").Value = 0.160138888888889
        sheet.Range("E5").Value = 0.190138888888889
        sheet.Range("E6").Value = 0.135
        sheet.Range("E7").Value = 0.125

        sheet.Range("F2").Value = 0.55333333333333
        sheet.Range("F3").Value = 0.045
        sheet.Range("F4").Value = 0.23333333333333
        sheet.Range("F5").Value = 0.0121
        sheet.Range("F6").Value = 0.0763888888888889
        sheet.Range("F7").Value = 0.0763888888888889

        sheet.Range("G2").Value = 0.0198888888888889
        sheet.Range("G3").Value = 0.145833333333333
        sheet.Range("G4").Value = 0.708333333333333
        sheet.Range("G5").Value = 0.0406666666666667
        sheet.Range("G6").Value = 0.0416666666666667
        sheet.Range("G7").Value = 0.0753888888888889

        sheet.Range("H2").Value = "SN"
        sheet.Range("H3").Value = "SN"
        sheet.Range("H4").Value = "BR"
        sheet.Range("H5").Value = "BR"
        sheet.Range("H6").Value = "MT"
        sheet.Range("H7").Value = "MT"

        sheet.Name = "DataSource"

        'Add a PivotTable to the worksheet
        Dim dataRange As CellRange = sheet.Range("A1:H7")
        Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange)

        '2-Pivot Ass
        Dim wsAss As Worksheet = workbook.Worksheets(1)
        Dim ptAss As PivotTable = wsAss.PivotTables.Add("Pivot Table AS", wsAss.Range("A1"), cache)

        'Drag the fields to the row area.
        Dim pf As PivotField = TryCast(ptAss.PivotFields("Ass"), PivotField)
        pf.Axis = AxisTypes.Row
        Dim pf2 As PivotField = TryCast(ptAss.PivotFields("Titre"), PivotField)
        pf2.Axis = AxisTypes.Row
        Dim pf3 As PivotField = TryCast(ptAss.PivotFields("Av"), PivotField)
        pf3.Axis = AxisTypes.Row

        ''Format [h]:mm
        Dim p1 As PivotField = TryCast(ptAss.PivotFields("Temp1"), PivotField)
        p1.NumberFormat = sColumFormat
        p1 = TryCast(ptAss.PivotFields("Temp2"), PivotField)
        p1.NumberFormat = sColumFormat
        p1 = TryCast(ptAss.PivotFields("Temp3"), PivotField)
        p1.NumberFormat = sColumFormat
        p1 = TryCast(ptAss.PivotFields("Temp4"), PivotField)
        p1.NumberFormat = sColumFormat

        'Drag the field to the data area.
        ptAss.DataFields.Add(ptAss.PivotFields("Temp1"), "SUM of Temp1", SubtotalTypes.Sum)
        ptAss.DataFields.Add(ptAss.PivotFields("Temp2"), "SUM of Temp2", SubtotalTypes.Sum)
        ptAss.DataFields.Add(ptAss.PivotFields("Temp3"), "SUM of Temp3", SubtotalTypes.Sum)
        ptAss.DataFields.Add(ptAss.PivotFields("Temp4"), "SUM of Temp4", SubtotalTypes.Sum)

        ptAss.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

        'Calculate data
        ptAss.CalculateData()
        'Autofit columns generated by the pivotTable
        wsAss.AutoFitColumn(1)
        wsAss.AutoFitColumn(2)
        wsAss.AutoFitColumn(3)
        wsAss.AutoFitColumn(4)
        wsAss.Name = "Temps Ass"

        '3-Pivot Av
        Dim wsAv As Worksheet = workbook.Worksheets(2)
        Dim ptAv = wsAv.PivotTables.Add("Pivot Table AV", wsAv.Range("A1"), cache)
        'Drag the fields to the row area.
        Dim pfAv As PivotField = TryCast(ptAv.PivotFields("Av"), PivotField)
        pfAv.Axis = AxisTypes.Row

        ''Format [h]:mm
        Dim p2 As PivotField = TryCast(ptAv.PivotFields("Temp1"), PivotField)
        p2.NumberFormat = sColumFormat
        p2 = TryCast(ptAv.PivotFields("Temp2"), PivotField)
        p2.NumberFormat = sColumFormat
        p2 = TryCast(ptAv.PivotFields("Temp3"), PivotField)
        p2.NumberFormat = sColumFormat
        p2 = TryCast(ptAv.PivotFields("Temp4"), PivotField)
        p2.NumberFormat = sColumFormat

        'Drag the field to the data area.
        ptAv.DataFields.Add(ptAv.PivotFields("Temp1"), "SUM of Temp1", SubtotalTypes.Sum)
        ptAv.DataFields.Add(ptAv.PivotFields("Temp2"), "SUM of Temp2", SubtotalTypes.Sum)
        ptAv.DataFields.Add(ptAv.PivotFields("Temp3"), "SUM of Temp3", SubtotalTypes.Sum)
        ptAv.DataFields.Add(ptAv.PivotFields("Temp4"), "SUM of Temp4", SubtotalTypes.Sum)

        ptAv.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

        'Calculate data
        ptAv.CalculateData()
        'Autofit columns generated by the pivotTable
        wsAv.AutoFitColumn(1)
        wsAv.AutoFitColumn(2)
        wsAv.AutoFitColumn(3)
        wsAv.AutoFitColumn(4)

        ''Format [h]:mm
        sheet.Columns(3).NumberFormat = sColumFormat
        sheet.Columns(4).NumberFormat = sColumFormat
        sheet.Columns(5).NumberFormat = sColumFormat
        sheet.Columns(6).NumberFormat = sColumFormat
        wsAv.Name = "Temps Av"

        sFileName = "Test_" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".xlsx"
        workbook.SaveToFile(sFileName, ExcelVersion.Version2010)
        Process.Start(sFileName)
    End Sub

As for sorting the pivot table, please refer to the following code snippet. If you have further questions, please feel free to contact us.

Code: Select all

        'Sort row fields
        CType(pt.RowFields(0), PivotField).Sort(True, pt.DataFields(0))
        'Sort column fields
        'CType(pt.ColumnFields(0), PivotField).Sort(false, pt.DataFields(1))
        pt.CalculateData()

Sincerely,
Rachel
E-iceblue support team

[mba91000]

Hi Rachel.
Thank you very much.

-Do you know how to make the decimal values considered by Excel as numbers and not as strings ?
The format [h]: mm does not work for the data source (sheet.Columns(indexColum).NumberFormat="[h]:mm")

-This code doesn't really sort.
CType(ptAv.RowFields(0), PivotField).Sort(True, ptAv.DataFields(0))

- Does the code works for you in an aspx page ?

Thank you again for your help.

My development environment.
WIN 7 pro, Service pack 1,
System : 64 bits
Visual studio 2015.
Country : France

Code: Select all

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim workbook As New Workbook()
        Dim sheet As Worksheet = workbook.Worksheets(0)
        Dim sFileName As String = String.Empty
        Const sColumFormat As String = "[h]:mm"

         '1-Data source of the PivotTables
        ' Set the value to the cells
        sheet.Range("A1").Value = "Av"
        sheet.Range("B1").Value = "Numero"
        sheet.Range("C1").Value = "Titre"
        sheet.Range("D1").Value = "Temp1"
        sheet.Range("E1").Value = "Temp2"
        sheet.Range("F1").Value = "Temp3"
        sheet.Range("G1").Value = "Temp4"
        sheet.Range("H1").Value = "Ass"

        sheet.Range("A2").Value = "PS"
        sheet.Range("A3").Value = "PS"
        sheet.Range("A4").Value = "BP"
        sheet.Range("A5").Value = "SR"
        sheet.Range("A6").Value = "MN"
        sheet.Range("A7").Value = "TR"

        sheet.Range("B2").Value = "14256"
        sheet.Range("B3").Value = "12034"
        sheet.Range("B4").Value = "12545"
        sheet.Range("B5").Value = "12034"
        sheet.Range("B6").Value = "12034"
        sheet.Range("B7").Value = "14256"

        sheet.Range("C2").Value = "dos1"
        sheet.Range("C3").Value = "dos2"
        sheet.Range("C4").Value = "dos3"
        sheet.Range("C5").Value = "dos2"
        sheet.Range("C6").Value = "dos4"
        sheet.Range("C7").Value = "dos5"

        ' Minutes/(24*60) => Time format [h]:mm in Excel
        sheet.Range("D2").Value = 0.0659722222222222
        sheet.Range("D3").Value = 0.04
        sheet.Range("D4").Value = 0.011
        sheet.Range("D5").Value = 0.0633333333333333
        sheet.Range("D6").Value = 0.0733333333333333
        sheet.Range("D7").Value = 0.0933333333333333

        sheet.Range("E2").Value = 0.0416666666666667
        sheet.Range("E3").Value = 0.1875
        sheet.Range("E4").Value = 0.160138888888889
        sheet.Range("E5").Value = 0.190138888888889
        sheet.Range("E6").Value = 0.135
        sheet.Range("E7").Value = 0.125

        sheet.Range("F2").Value = 0.55333333333333
        sheet.Range("F3").Value = 0.045
        sheet.Range("F4").Value = 0.23333333333333
        sheet.Range("F5").Value = 0.0121
        sheet.Range("F6").Value = 0.0763888888888889
        sheet.Range("F7").Value = 0.0763888888888889

        sheet.Range("G2").Value = 0.0198888888888889
        sheet.Range("G3").Value = 0.145833333333333
        sheet.Range("G4").Value = 0.708333333333333
        sheet.Range("G5").Value = 0.0406666666666667
        sheet.Range("G6").Value = 0.0416666666666667
        sheet.Range("G7").Value = 0.0753888888888889

        sheet.Range("H2").Value = "SN"
        sheet.Range("H3").Value = "SN"
        sheet.Range("H4").Value = "BR"
        sheet.Range("H5").Value = "BR"
        sheet.Range("H6").Value = "MT"
        sheet.Range("H7").Value = "MT"

        sheet.Name = "DataSource"

        'Add a PivotTable to the worksheet
        Dim dataRange As CellRange = sheet.Range("A1:H7")
        Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange)

        '2-Pivot Ass
        Dim wsAss As Worksheet = workbook.Worksheets(1)
        Dim ptAss As PivotTable = wsAss.PivotTables.Add("Pivot Table AS", wsAss.Range("A1"), cache)

        'Drag the fields to the row area.
        Dim pf As PivotField = TryCast(ptAss.PivotFields("Ass"), PivotField)
        pf.Axis = AxisTypes.Row
        Dim pf2 As PivotField = TryCast(ptAss.PivotFields("Titre"), PivotField)
        pf2.Axis = AxisTypes.Row
        Dim pf3 As PivotField = TryCast(ptAss.PivotFields("Av"), PivotField)
        pf3.Axis = AxisTypes.Row

        ''Format [h]:mm
        Dim p1 As PivotField = TryCast(ptAss.PivotFields("Temp1"), PivotField)
        p1.NumberFormat = sColumFormat
        p1 = TryCast(ptAss.PivotFields("Temp2"), PivotField)
        p1.NumberFormat = sColumFormat
        p1 = TryCast(ptAss.PivotFields("Temp3"), PivotField)
        p1.NumberFormat = sColumFormat
        p1 = TryCast(ptAss.PivotFields("Temp4"), PivotField)
        p1.NumberFormat = sColumFormat

        'Drag the field to the data area.
        ptAss.DataFields.Add(ptAss.PivotFields("Temp1"), "SUM of Temp1", SubtotalTypes.Sum)
        ptAss.DataFields.Add(ptAss.PivotFields("Temp2"), "SUM of Temp2", SubtotalTypes.Sum)
        ptAss.DataFields.Add(ptAss.PivotFields("Temp3"), "SUM of Temp3", SubtotalTypes.Sum)
        ptAss.DataFields.Add(ptAss.PivotFields("Temp4"), "SUM of Temp4", SubtotalTypes.Sum)

        ptAss.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

        'Sort row fields
        CType(ptAss.RowFields(0), PivotField).Sort(True, ptAss.DataFields(0))

        'Calculate data
        ptAss.CalculateData()
        'Autofit columns generated by the pivotTable
        wsAss.AutoFitColumn(1)
        wsAss.AutoFitColumn(2)
        wsAss.AutoFitColumn(3)
        wsAss.AutoFitColumn(4)
        wsAss.Name = "Temps Ass"

        '3-Pivot Av
        Dim wsAv As Worksheet = workbook.Worksheets(2)
        Dim ptAv = wsAv.PivotTables.Add("Pivot Table AV", wsAv.Range("A1"), cache)
        'Drag the fields to the row area.
        Dim pfAv As PivotField = TryCast(ptAv.PivotFields("Av"), PivotField)
        pfAv.Axis = AxisTypes.Row

        ''Format [h]:mm
        Dim p2 As PivotField = TryCast(ptAv.PivotFields("Temp1"), PivotField)
        p2.NumberFormat = sColumFormat
        p2 = TryCast(ptAv.PivotFields("Temp2"), PivotField)
        p2.NumberFormat = sColumFormat
        p2 = TryCast(ptAv.PivotFields("Temp3"), PivotField)
        p2.NumberFormat = sColumFormat
        p2 = TryCast(ptAv.PivotFields("Temp4"), PivotField)
        p2.NumberFormat = sColumFormat

        'Drag the field to the data area.
        ptAv.DataFields.Add(ptAv.PivotFields("Temp1"), "SUM of Temp1", SubtotalTypes.Sum)
        ptAv.DataFields.Add(ptAv.PivotFields("Temp2"), "SUM of Temp2", SubtotalTypes.Sum)
        ptAv.DataFields.Add(ptAv.PivotFields("Temp3"), "SUM of Temp3", SubtotalTypes.Sum)
        ptAv.DataFields.Add(ptAv.PivotFields("Temp4"), "SUM of Temp4", SubtotalTypes.Sum)

        ptAv.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

        CType(ptAv.RowFields(0), PivotField).Sort(True, ptAv.DataFields(0))

        'Calculate data
        ptAv.CalculateData()
        'Autofit columns generated by the pivotTable
        wsAv.AutoFitColumn(1)
        wsAv.AutoFitColumn(2)
        wsAv.AutoFitColumn(3)
        wsAv.AutoFitColumn(4)
        wsAv.Name = "Temps Av"

        ''Format [h]:mm
        'sheet.Columns(3).NumberFormat = sColumFormat
        'sheet.Columns(4).NumberFormat = sColumFormat
        'sheet.Columns(5).NumberFormat = sColumFormat
        'sheet.Columns(6).NumberFormat = sColumFormat

        sheet.Range("D1:D7").Style.NumberFormat = sColumFormat
        sheet.Range("E1:E7").Style.NumberFormat = sColumFormat
        sheet.Range("F1:F7").Style.NumberFormat = sColumFormat
        sheet.Range("G1:G7").Style.NumberFormat = sColumFormat

        sFileName = "Test_" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".xlsx"
        workbook.SaveToFile(sFileName, ExcelVersion.Version2010)
        Process.Start(sFileName)
    End Sub


[rachel.lei]

Hello,

Thanks for your feedback. Below is my answer to your questions.
1) Please try to set the CurrentCulture to "InvariantCulture" as shown below. And attached is my output for your reference.

Code: Select all

            Dim cc As CultureInfo = Thread.CurrentThread.CurrentCulture
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture

            //Your code...

            Thread.CurrentThread.CurrentCulture = cc

2) Based on your goal file, please refer to the following code snippet to sort the pivot table.

Code: Select all

        '...
        Dim pf As PivotField = TryCast(ptAss.PivotFields("Ass"), PivotField)
        pf.Axis = AxisTypes.Row
        'Sort
        pf.SortType = PivotFieldSortType.Ascending

        '...

        Dim pfAv As PivotField = TryCast(ptAv.PivotFields("Av"), PivotField)
        pfAv.Axis = AxisTypes.Row
        pfAv.SortType = PivotFieldSortType.Ascending
        '...


3) Yes, the code I provided can work for me in an aspx page. Here I uploaded my test Web project, you can run it directly on your side. If there is any question, just feel free to write back.

Sincerely,
Rachel
E-iceblue support team

[mba91000]

Hello Rachel.
Thank you.
My aspx project works well. I changed the frameword and used the same version of spire.xls as you.

I still have questions:

1-How to change or add a header to the pivot table ?

2-How to collapse all the lines (at least level 1) of the pivot table , without knowing the value in advance like here :

Code: Select all

TryCast(pivotTable.PivotFields("Vendor No"), Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("3501", True)


3-How to change the color of the pivot table (I prefer the color blue as in the attached file).
I tested several styles but I do not get the color blue

Best regards

[rachel.lei]

Hello,

Thanks for your feedback. Please find the answers below.
1) You can refer to the following code snippet to add a header. As for changing the default field captions, sorry our Spire.XLS doesn't support it. But you can set "PivotTable.DisplayFieldCaptions" to "False" to hide the field captions.

Code: Select all

        'Insert a new row before the first row of the pivot table
        wsAss.InsertRow(1)
        'Merge cell
        wsAss.Range(1, 1, 1, 5).Merge()
        'Set header text
        wsAss.Range("A1").Text = "header text"
        'Set horizontal alignment
        wsAss.Range("A1").Style.HorizontalAlignment = HorizontalAlignType.Center

2) Please refer to the following code to collapse the rows without specifying the value.

Code: Select all

    TryCast(ptAss.PivotFields("Ass"), Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideDetail(True)

3) Please try to set the BuiltInStyle to "PivotStyleLight20".

Code: Select all

    ptAss.BuiltInStyle = PivotBuiltInStyles.PivotStyleLight20


Here I attached my full code and output file for your better reference. If you need further assistance, just feel free to write back.

Sincerely,
Rachel
E-iceblue support team

[mba91000]

Thanks Rachel,

A small remark:
I notice that when I put the line :

Code: Select all

ptAv.DisplayFieldCaptions = False


I get the message “There’s already data here. Do you want to replace it?” when I open the created Excel file.

Thanks again Rachel, I will purchase the Pro version of Spire.xls.

[mba91000]

Hi Rachel,

The generated files are automatically sent to users by email, but strangely the headers of the pivot Tables are doubled (And the number formats are not respected) when you do not activate the file modifications.

Do you know if this is normal?

[rachel.lei]

Hello,

Thanks for your feedback.
Regarding your first question, please make sure you call it before calculating the pivot table data, like this:

Code: Select all

        'Hide field captions before calculating the pivot table data
        ptAss.DisplayFieldCaptions = False
        'Calculate data
        ptAss.CalculateData()

As for your another question, I learned from our Dev team that when opening an Excel file in Microsoft Excel, Microsoft Excel will first recalculate the data (such as pivot tables, formulas, etc) of the file, and then display it. And for network file, Microsoft Excel opens it in protected view by default. In this view, only after file modifications are enabled, Microsoft Excel will recalculate the data. Thus, to display the correct value for pivot table in Microsoft Excel, you may need to enable editing to recalculate the data. Hope you can understand.

Sincerely,
Rachel
E-iceblue support team