Today we will do some mathematical graphing and load the cells of excel with data obtained externally from dos files, from web pages and from a perl output screen. This is a very important feature that will save you a lot of work if you take time to learn the process.
Lets start by writting a program in Perl that will calculate a set of order pairs for the equation y=x^2. Remember though that using ^ as a power operator is correct in Excel but it is not correct in Perl! Use x*x for x^2. OK start by opening up notepad++ and lets write a program that prints a set of ordered pairs for x=0,1,2,..10. Use a while statement that will print out these orderpairs in the format shown below.
First we must copy the data at the left. Remember how to do this? Click the icon in the upper left hand corner of the window, then select edit, then mark the data as shown below. Now do an icon, edit then copy to get the data into the clipboard.
Now open excel and paste this data into the cell A1. What is the problem?? Well how do we put the second number into the B column. Its easy. Just click on the Data tab and take note of the Text to Columns icon within Data Tools. Hmmm. Double click it. Leave the Delimited set and select Next>. Click the space Delimiter. Do it again, and again. See what happens? Its puts it into two columns and back into one. Leave it on two and click Finish. We should have the data in two columns as desired.
We are now at the graphing phase. Select the cells A1 to B10 and then Insert, then scatter, then scatter with smooth lines. Hover your mouse over the icons and Excel will tell you what that icon does. Add a title to the graph and delete the legend. Your final result should be something like the following.
Now getting th data this way is of course a little cumbersome. There are better ways if the data is stored in a file and not only within the dos output window. Well how do we get the data in a file? Perl as well as all programming languages that I have ever seen allow you to open, read or write to files and then close them. We will use the following command
at the top of your program and then when you do a print, do the command print FILE “$x $y\n”; instead. The program below will print the numbers 1 to 10, one per line to the file data.txt.
SO. If your now run the program and send the ordered pairs to the file data.txt we have a better option when when load it into Excel. Lets assume that the data is in data.txt and that it has the above format. Click on Data tab and then select the cell A1. Within the Get Extern Data section directly above you see From Text. Click it Pick the appropriate file, then click import and you see exactly what we saw earlier. Hit next> click Space and finish. You should see the Import Data window. Say OK and the data is now loaded and is in two column. There you go.