While I was working as a data scientist at Facilis, there was once I needed to fill a formula for more than 100 companies. But these formulas need to be filled based on the company’s own positions on the Excel sheet, and it was a very wide excel sheet, how should I complete this task?
First way : Brute force
https://medium.com/media/0ee4f078f57c15c34d024a1884b1384e/href
Well…this is one way to solve this problem, by doing it manually. But after trying to fill a few cells, I became very dizzy. So it’s time to do it in a smarter way.
Second way : Fill with Python
This problem actually reminds me of a LeetCode problem, called Excel Sheet Column Title. Check out this problem here.
Excel Sheet Column Title – LeetCode
So basically, what I need to do is to turn numbers into the Excel column title.
For example, 1 → A, 3 →C, 26 →Z, etc. So in my case, I know the location of the cells I need, they are at the 58th rows, and I need to fill the formula like this:
For company 1, write formula “=E58”;
For company 2, write formula “=I58”;
For company 3, write formula “=M58”;
Trending Chatbot Articles:
1. Building a Discord Bot for ChatOps, Pentesting or Server Automation
2. 8 Best IT Workflow Automation Practices to follow for Chatbots
4. Adding a conversational interface to your app with Dialogflow
From E →I (F G H), from I →M (J K L), there is exactly the same number of letters between these pairs. So if I know the index of this column, (for Column A, the index is 1), and I know the index of the next needed column (current_index = previous_index + interval), the interval between these two indexes is consistent, what I need to do next is to write an algorithm to turn numbers into Excel column titles.
Here is my Algorithm.
def convertToTitle(n: int):
if n <= 0:
return ""
A = ord('A')
res = []
while n > 0:
if n > 26:
i = n % 26
if i == 0:
res.append('Z')
n -= 26
else:
res.append(chr(A + i - 1))
n //= 26
else:
res.append(chr(A + n - 1))
break
res.reverse()
return ''.join(res)
So if you are given number 28, you need to return “AB”, this is what this algorithm complete.
The next thing I’m gonna do is, I need to find a way to make sure after I output the excel from Jupyter Notebook, it can be recognized as a formula not as a String.
So in Jupyter, it looks like this: “=” is not read as an operator, but a string.
After I output the data frame to “xlsx”, and open it in Excel, I get this. “=” is recognized as an operator in Excel. Bingo!
This problem actually gave me a new thought about how to utilize the difference between different platforms, with smart handling, we can do more with limited time!
There should be other smarter ways to handle this kind of problem, but so far I have only come up with this method, welcome to any inspiration and comments.
Don’t forget to give us your 👏 !
https://medium.com/media/7078d8ad19192c4c53d3bf199468e4ab/href
One way to fill Excel formula with Python was originally published in Chatbots Life on Medium, where people are continuing the conversation by highlighting and responding to this story.
