Graph excel values and draw calculated lines


New member
Feb 18, 2016
Programming Experience
Hello all,
I have given up on trying to change vb6 code to .net and instead am going to try to rebuild in .net. What would be the best basis of code for the user to select an excel file, process the file to populate a graph from the data in specified columns, and then draw in lines of best fit to the data. I have attached an example of what the data format is consistently. As you click on the forward button it goes to the next row of data and redraws the corresponding graph. Things I am trying to figure out how to do:

1) Open two csv's and read the data (x-axis by columns and y-axis by rows)
2) Take the csv with increasing values on column E and plot on the right side of the y-axis
3) Take the csv with decreasing values and plot on the left (in line with the matching value from column E)
4) Have the title of each graph be the value in column E.

What do you think would be the best form components to achieve this basic initial step and how do you use .NET to create a graphic of your data plotted with additional fit lines? Thank you in advance for your time.

View attachment Please

This is an example of some code I wrote in excel VBA that was part of another procedure which might help clarify the layout.

Sub Convert2Output()
Dim WS As Worksheet
Dim A As Long, B As Long
Dim LastRow As Long
Dim LastCol As Long
Dim FF As Integer
Dim MyPath As String
Dim Offset As String
Dim Layer As String
Dim Pin As String

Set WS = Worksheets(1)
'Change as required.
Pin = InputBox("What is the project pin?", _
        "File Location by Pin Number", "12345")
MyPath = "C:\pin\" & Pin
Layer = InputBox("What do you want the layer to be named?", _
        "Surface Layer", "Existing Asphalt Surface")
With WS
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    FF = FreeFile
    Open MyPath & "_ARAN_SURFACE_Output.txt" For Output As #FF
    For A = 2 To LastRow 'label rows as A, A=1 at row 2
        For B = 8 To LastCol 'label columns as B, B=1 at column H
            If .Cells(2, 5) < .Cells(LastRow, 5) Then
            Offset = .Cells(1, B) - .Cells(1, 8)
            Offset = .Cells(1, 8) - .Cells(1, B)
            End If
            'Above If statement sets which side of the line to plot surface
    Close #FF
End With
MsgBox ("Your file was saved in " & MyPath & "_Output.txt")
End Sub
Top Bottom