(Dec-21-2016, 11:09 PM)birdieman Wrote: I am a moderately experienced VBA coder, and have only begun learning Python (second post on this forum) because I would like to take on a new project which takes an Excel program/spreadsheet and duplicates what it does using Python.
As you probably know, Excel is made up of rows and columns, and works/calculates left to right within a row, then moves down a row, and starts over, etc.. In addition, a value that is calculated in cell A1, for example, is typically used in cell A2, and so on down the row.
In my spreadsheet, assume I have 80 rows and 200 columns. Formulas (nothing fancy, just normal math) are copied downward in each column to all 80 rows. In other words, every formula in a particular column is the same thing (of course, the values used in those formulas are different).
My general questions:
1. Does Python even lend itself to calculating/behaving like Excel? If not, is there a better language?
2. If so, how should I think of duplicating a spreadsheet-like program in Python? Should I think of a row as a list? dictionary? etc? Is that hard to do with a big list? Maybe a make a bunch of smaller lists to duplicate a section of the spreadsheet? etc, etc.
I just need some general thoughts on whether to try, and if so, maybe how to approach it from a big picture standpoint?
thanks for looking
HI,
Python can certainly handle spreadsheet-like calculations and behavior, though it may not have the same visual interface as Excel. Python has several libraries and modules that allow for data analysis, manipulation, and calculations, such as Pandas, NumPy, and openpyxl.
In terms of duplicating a spreadsheet-like program in Python, you could think of each row as a list or a dictionary, where the keys represent the column headers and the values represent the cell values. Alternatively, you could use Pandas to create a DataFrame, which is essentially a table with rows and columns.
For example, you could create a DataFrame with 80 rows and 200 columns like this:
import pandas as pd
data = [[0 for j in range(200)] for i in range(80)] # initialize with 0
df = pd.DataFrame(data)
You can then perform calculations on this DataFrame, for example:
df.iloc[:,0] = 1 # set the first column to 1
df.iloc[:,1] = df.iloc[:,0] + 1 # set the second column to the first column + 1
In this example, the first column is set to 1 and the second column is set to the values of the first column plus 1.
You could also use loops to iterate over the rows and columns and perform calculations on each cell, similar to how you would do it in Excel.
Overall, the approach you take may depend on the specific requirements of your project and the complexity of the calculations involved. But with the right libraries and tools, Python can certainly handle spreadsheet-like calculations and behavior.
Please let me know if it works or not.
