Reading Data from an Excel Spreadsheet?

[GWats1]

I did read over the Sample F90 Autodice and it is way more complicated than my simple Fortran IV mind can get around. I don't need to output into Excel, I just want to read a few cells of real numbers to populate my data arrays.

I have an old Fortran program that I put in some static data and do some calculations. I thought it would be a little easier to read the data from a file that is editable by a user and I can do that but then I thought a simple Excel spreadsheet might be easier for a novice user to edit. 

So, my thought was to open a simple Dialog window and have the user select the BOLTS.XLSX file and read the data into my arrays for each column. I used to have 17 elements in each 1 dimension array for bolt diameter, area, etc. and the user might only want a few diameters to process.

If it is all to complicated to get Fortran to read data, I guess I could have the user edit the Excel rows and run a Macro to print out the data to a file and read that into my program, but I thought I would try to understand how Fortran could do it.

TIA 

[JohnNichols]

I had 20 spare minutes and I had a look at your FOR file design base plate.  it looks interesting, I made it into a F90 file, and added implicit none.  

There are two variables declared A and B, but they have no value and the RDIS is not declared or sized that I could see.  I could be wrong.  I made RDIS(5,400).

I added a driver function and guessed some numbers, I am a metric human bean so I just guessed.  

Nice code, the output is good.  

Good job. 

JMN

[GWats1]

Hi John;

I'm still mired deep in .FOR constructs and the purpose of line of code: 

RDIS(A,B)=SQRT(A*A + B*B)

is to define an inline function to get the resultant distance between 2 values. From my ancient 1967 FORTRAN IV class, you can define a function if it is the first line of code and use it within the subroutine. So if you have a value for any variable, you get the resultant. AFAIK, you can place a Function construct as the first line but I'm sure it may drive the Compiler crazy.    

So if you want the resultant of 2 vectors you would put:

qqq = RDIS(xxx,yyy)

In my routine, I have the x coordinate and y coordinate and want the radial distance to the bolt so I used RDIS(x,y). I explicitly defined a couple of other Functions for rounding up and converting degrees to radians and back, but it is sometimes handy to just use one line of code to define a Function.  Try it out and see if it works for you.   

FUNCTION RND ( YYY ,XXX )
!
! THIS FUNCTION ROUNDS OFF TO THE NEAREST WHOLE FRACTION XXX
!
ITK = INT (YYY/XXX +0.5)
RND=XXX*FLOAT(ITK)
RETURN
END
FUNCTION RNDUP ( YYY ,XXX )
!
! THIS FUNCTION ROUNDS OFF TO THE NEAREST WHOLE FRACTION XXX
!
ITK = INT (YYY/XXX +1.0 )
RNDUP=XXX*FLOAT(ITK)
RETURN
END
!
! THIS FUNCTION WILL CONVERT DEGREES TO RADIANS
!
!
FUNCTION DTR(XXX)
DTR=XXX*2.0*ASIN(1.0)/180.0
RETURN
END
FUNCTION RTD(XXX)
RTD=XXX*180.0/(2.0*ASIN(1.0))
RETURN
END

[mecej4]

The statement

     RDIS(A,B)=SQRT(A*A + B*B)

defines a "Statement Function". Such functions are sometimes convenient, but their usage can be error-prone, and the Fortran Standard labels them as "obsolescent".

Modern Fortran provides many more intrinsic functions than Fortran77 and earlier versions. There is an intrinsic function, hypot(x,y), that could be used instead of the statement function RDIS.

[JohnNichols]

 DATA BSPACE /2.25,  2.25,  2.5,  2.5,  3.0,  3.25,  3.50,  4.25, 5.75,  5.75, 5.75, 5.75,  6.25,  7.0,  7.5,  8.75,  10.0  /


    RDIS(A,B)=SQRT(A*A + B*B)

    WRITE(*,217) MX,MY,VLOAD,VX,VY,AF,THK,NSIDES
    write(10,706)
706 FORMAT(10x,'This Version was compiled on 15-Dec-2021 '/,&

The program compiles but it throws an forrtl: severe (408): fort: (11): Subscript #2 of the array RDIS has value -107374176 which is less than the lower bound of 1.

Just out of vague interest how do you code them?  

[mecej4]

JohnNichols wrote: "The program compiles".

Which program? There have been many programs posted in this thread.

You are not allowed to use the same name for a variable and a function in the same scope.

Here is a program that illustrates the use of an ASF (Arithmetic Statement Function), but don't use ASFs in new code. Use internal functions and subroutines instead.

program xasf
implicit none
real a,b,c, rdis
!
rdis(a,b) = sqrt(a*a+b*b)  !Arithmetic Statement Function
!
c = rdis(3.0,4.0)
print *,c
print *, rdis(5.0,12.0)
end

[DavidWhite]

This code with .EQ. for comparing two reals cannot guarantee that the correct option will be detected.

You should use something like ABS(DIAM(I)-2.25).LT.1E-4

.EQ. should only be relied on for discrete values like integers.

[GWats1]

Hi DavidWhite;

Yes, I believe you are correct and I vaguely remember the .EQ. was meant for integers to compare.

IIRC there was a computed go to statement something like GOTO (I) 10,20,30 that went to 10 if I was negative, 20 if I was 0, and 30 if I was positive. 

[andrew_4619]

qqq = norm2( [A, B] ) ! [] makes an array instrinsic norm2 opperates on arrays

[JohnNichols]

Hermite polynomials would make another useful intrinsic function.  They can be quite useful in Civil Engineering. 

[JohnNichols]

As I noted earlier in this message set, I took the bolt plate subroutine, changed it to F90 and turned on implicit none.  I uploaded the code to this forum in an earlier post. 

I was unaware of inline functions, never used them and do not remember ever reading about them, they are not in the MS Fortran manuals that I could find.  

In the latest compiler, I have noticed and reported in this forum that some undeclared variables are not being spotted by implicit none, so the RDIS(a,b) = ...  compiled quite nicely but then failed on execution.  

As pointed out, declaring the RDIS , A and B solves the problem, but they should have been flagged by the implicit none.  

In a manner, the inline function is an alternative to a lambda function, useful, but often a subroutine or function makes easier reading.  

[mecej4]

Arithmetic Statement Functions have existed in every full (i.e., not a subset such as Lahey's ELF90) version of Fortran from Fortran II. See, for example, the Fortran 66 standard, Section 8.1. They were present in Microsoft Fortran 80, and later versions.

This language feature is confusing to readers who do not understand how they work, and your faulty "conversion" to Fortran 90 proves the point.

Such an improperly converted program may be syntactically correct, in which case the compiler would not give any error messages, but it is likely to give garbage as output. Note the attempted use of an absurd subscript value which caused the converted program to abort.

Here are the three relevant lines in your converted F90 file (Design_base_PLATE-4811P.F90), contained in Console1.zip  :

99:    reaL rdis(5,400)
123:    !RDIS(A,B)=SQRT(A*A + B*B)
187:        SHRES=RDIS(VX,VY)

In this code a function definition (real function with two real arguments) was replaced with a two dimensional array declaration, and the code then referenced the array with real subscripts, with nothing in the code that set values into the array prior to that reference.

[GWats1]

Hi Guys;

I switched my RDIS function to the hypot(x,y) intrinsic I just learned from mecej4, recompiled and ran my test data and it runs just fine. I attached the revised .FOR file that works, the data file that I read, and a picture of a T-Line pole (for those that wonder what all the fuss is about). I forget how many bolts are on this pole but they are threaded #18 rebar bolts at 2.25" diameter. The foundation is around 14' diameter and probably 60' deep.  All the bolts are not plumb so the pole fabricator had to ream out the holes in the base plate so the bottom shaft would fit over the bolts. A crane is holding the section up and you can see the upper section ready to be stacked in the upper right. I think this pole is over 220' tall.

[mecej4]

Thanks for posting the photograph of the bolts and the pole base. The pole appears to be a 12-sided polygon in cross section, with 5 bolts used in the baseplate per side. Are the irregularly spaced studs located on the outer circle used for pre-stressing the foundation?

Here is a related link, showing several very tall flagpoles around the world. Some of the flags on those poles themselves weigh as much as half a ton.

[GWats1]

These are Transmission Line poles that support high voltage wires (345kV) that are about 1" diameter aluminum strands and we run them up to 200°C (this is why you don't see birds on them). I believe the pole in my picture was a 30° line angle double circuit pole and the base plate was about 6" thick. The anchor bolts are doubly symmetric in quadrants. The foundations are just a big drilled pier with a reinforcing cage outside the bolts which are #18 rebar and about 13' long. No pre-stressing but the plate sits on levelling nuts and is clamped with a top nut to be fairly rigid.

Most of the T-Line industry uses PLS-CADD to analyze poles and towers. My post-processor works with PLS-POLE to design base plates for the pole. The vendors/designers of the poles have their own inhouse design software for the poles and base plates but I wanted to be able to spit out some possible plates as a rough estimate. PLS-POLE checks the pole and base plate according to the ASCE 48-19 Standard so I used the equations from there.

I am the Chair of ASCE 113 and I believe one of our members was the designer of some of the tall flag poles in your link. The weight of the flags is a minor load and the wind on the flag is what drives the design of the pole.    In my part of South Texas, we design for a 140 mph hurricane to try to keep the power flowing. 

[mecej4]

Thanks for the additional details on the field application.