Writing formulas with Excel with Python is a technique, that needs some practice, actually. Although XlsWriter has some good documentaion, not a lot is covered on the point, that concerns creating a file with a formula in it. First and probably most important point – formulas should be written in string in English, completely ignoring the local Excel language. Something like .Formula in VBA, actually.
Thus, the code below produces the scraped links from vitoshacademy.com into column A and their length, calculated by the formula =LEN(A1) in column B:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
import urllib.request from bs4 import BeautifulSoup import io import xlsxwriter from xlsxwriter.utility import xl_rowcol_to_cell def main(): resp = urllib.request.urlopen("https://www.vitoshacademy.com") soup = BeautifulSoup(resp, from_encoding=resp.info().get_param('charset'), features="html.parser") links = [] for link in soup.find_all('a', href=True): links.append(link.get('href')) wbk = xlsxwriter.Workbook("myExcel.xlsx") wks = wbk.add_worksheet() for i in range(len(links)): cell = xl_rowcol_to_cell(i, 0) cell2 = xl_rowcol_to_cell(i,1) print (links[i]) wks.write(cell, links[i]) wks.write(cell2, '=LEN('+ cell +')') wbk.close() if __name__== "__main__": main() |
The result looks like this:
Above, it is quite interesting, that the worksheet is automatically named “Sheet1” and not the default for Germany – “Blatt1”, although the formulas are the standard German ones.