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