locked
EXCEL to use Tending Forcast to hit a vaule of 0 RRS feed

  • Question

  • Hello,

    I have written a power-shell scrip that includes excel to be able to show a trend line of a server data usage over several months.  I even have made it so that it can forcast in the future to show when the volume of the disk will hit 0.

    Everything works great. But i have to manually put in a value for the forecast filed.

    Is there a way that I can make it so that forcast will work out to show me when it hits 0 .

    The code below when I have the dates and data usage . In the code below i have to manually set the vaule for Forcast to 40 (that is when the volume of the server will hit 0 free space)

    $tmpentry = $sqlquery | select date,'free megabytes' 
    $tmpentry | Export-Excel -Path C:\scripts\tempxlsserverhdrive1.xlsx 

    ################################
    # Creating excel com object
    ################################

            $xl = new-object -ComObject Excel.Application   
            $fileName = "C:\scripts\tempxlsserverhdrive1.xlsx"
            $wb = $xl.Workbooks.Open($fileName)

    ################################
    #Open the first sheet of the excel 
    ################################
            $wsChart = $wb.WorkSheets.item(1) 
            
    ###############################################################
    #FORMATTING THE COLUMNS
    #################################################################
        
            $objRange = $wsChart.UsedRange
            [void]$objRange.EntireColumn.Autofit() 
            $wb.Save()
            
    ######################
    #CREATING THE GRAPH
    ######################

            $xlChart=[Microsoft.Office.Interop.Excel.XLChartType]


            ################################
            # Activating the Data sheet
            ################################
            $wsChart.activate() 
            # Adding the Chart
            $chart = $wsChart.Shapes.AddChart().Chart

            ################################
            #selecting the Range for X and Y
            ################################ 
            $range = $wsChart.range("a${xrow}:b$yrow") # sets the Data range we want to chart

            #########################################
            # Providing the chart type - Line chart
            #########################################
            $chart.ChartType = 4
            $chart.setSourceData($range)
            
            # Setting up the position of chart (Not required if the sheet has just one chart). It will create the chart at top left corner
            #$wsChart.shapes.item("Chart 1").top = 50
            #$wsChart.shapes.item("Chart 1").left = 200

            #Resize the Chart 
            $RngToCover = $wsChart.Range("D5:AA30") # This is where we want the chart

            $ChtOb = $chart.Parent # This selects the current Chart
            $ChtOb.Top = $RngToCover.Top # This moves it up to row 5
            $ChtOb.Left = $RngToCover.Left # and to column D 
            $ChtOb.Height = $RngToCover.Height # resize This sets the height of your chart to Rows 5 - 30
            $ChtOb.Width = $RngToCover.Width # resize This sets the width to Columns D - AA


            # Set it true if want to have chart Title and Legend
            $chart.HasTitle = $true
            $chart.HasLegend = $true
            

            # Providing the Title for the chart
            $chart.ChartTitle.Text = "$ServerName Future Free Disk Usuage 40 Days  - $ServerVolume"
        
     
            $tl=$chart.seriesCollection(1).trendlines().add()
            $tl.DisplayEquation = $flase
            $tl.DisplayRSquared = $flase
            $tl.Forward = 40
            $tl.Name = 'Future Trend'

              
          # Save the sheet
            $wb.Save()  
            # Closing the work book and xl
            $wb.close() 
            $xl.Quit()
            # Releasting the excel com object
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)


           
    ####################################################################################################
    #Reset the SQL SCRIPT back to default
    ####################################################################################################

    (Get-Content $SqlScriptLoc).replace($ServerName,$ServerDefault) | Set-Content $SqlScriptLoc
    (Get-Content $SqlScriptLoc).replace($ServerVolume,$ServerVolumeDefault) | Set-Content $SqlScriptLoc

    Wednesday, March 14, 2018 8:55 PM

All replies