Hello,
What I would like to do is to be able to make a script that allows me to read in the Excel files, and make a graph based on the data within them.
This is my situation:
I have 70 tables, in 70 different Excel files.
For this question, I will simplify it, so let's say I have 2 small tables in 2 different Excel files.
Let's say the tables are like this:
FileA.xlsx :
JAN FEB
2015 2 3
2016 5 7
FileB.xlsx :
JAN FEB
2015 90 40
2016 60 80
All the tables have the same headings (JAN, FEB, 2015, 2016), located in the same cells of excel (same rows and columns).
What I would like to do is a dispersion graph with these tables, relating JAN 2015, FEB 2015, JAN 2016 and FEB 2016 of Tables A and B, being that Table A would be the x axis, and Table B would be the y axis.
So the resulting graph would have 4 points: (2,90), (3,40), (5,60), (7,80).
In total, there are 70 files, and I would like to see the relationship between different files. Sometimes its necessary for me to relate File 1 and File 3, sometimes File 1 and File 50, sometimes File 27 and File 33, etc.
In this way, it would be interesting to make a script that allows me to relate the files, where I can just change the File names inputs to see the graphs.
What programming language could allow me to do so? (the easier, most straightforward, the better!)
The files are .xlsx, but I would have no problem to save it in a different format, if it makes it easier.
Hope it's not impossible and I hope I could make myself clear.
Any help is appreciated.
Thanks in advance.
I don't think the language itself has much to do with it. It would be possible using any language, really. What's more important is which high quality libraries are available and which languages are they usable from. Python comes to mind, because of it's vast ecosystem and clear, simple programming style.
Nishant Agrwal
I've used C# to work with excel table.
A good option was to use the Microsoft.Office.Interop.Excel library, this works very well, but it requires you to have Microsoft Excel installed. If you don't want to be dependent on Excel, then you can use NPOI library.
Any of the JVM languages can do this, Java, Kotlin, Scala, Clojure, Groovy, etc. Apache POI is very powerful.
Cristiano
Thats great, many thanks for the replies.
They were very useful to get started and I managed to do what I wanted.
Although I was tempted to try Python as well, I ended up using Octave actually, but I got there thanks to your replies.
Thanks again.