openpyxl - w3toppers.com

Get cell color from .xlsx

Looks like the sheet is using the built-on colour index. The mapping for these is in the source of openpyxl.styles.color COLOR_INDEX = ( ‘00000000’, ’00FFFFFF’, ’00FF0000′, ‘0000FF00’, ‘000000FF’, #0-4 ’00FFFF00′, ’00FF00FF’, ‘0000FFFF’, ‘00000000’, ’00FFFFFF’, #5-9 ’00FF0000′, ‘0000FF00’, ‘000000FF’, ’00FFFF00′, ’00FF00FF’, #10-14 ‘0000FFFF’, ‘00800000’, ‘00008000’, ‘00000080’, ‘00808000’, #15-19 ‘00800080’, ‘00008080’, ’00C0C0C0′, ‘00808080’, ‘009999FF’, #20-24 ‘00993366’, ’00FFFFCC’, … Read more

xlsx and xlsm files return badzipfile: file is not a zip file

The same problem occurred to me, and then I noticed the following: When I created the .xlsx file from file manager, by creating new document with .xlsx format, I had the same error. But when I created the file with some spreadsheet software (libreoffice-calc) in my case, it just worked fine. I hope this may … Read more

getting the row and column numbers from coordinate value in openpyxl

What you want is openpyxl.utils.coordinate_from_string() and openpyxl.utils.column_index_from_string() from openpyxl.utils.cell import coordinate_from_string, column_index_from_string xy = coordinate_from_string(‘A4’) # returns (‘A’,4) col = column_index_from_string(xy[0]) # returns 1 row = xy[1]

openpyxl load_workbook() on a legit .xlsx file leads to a zipfile.BadZipFile error

I was able to replicate the problem. It is pandas related. Everything works just fine up to pandas 1.1.5 In pandas 1.2.0 they did some changes At the time when you instantiate pd.ExcelWriter with writer = pd.ExcelWriter(report_path, engine=”openpyxl”)` it creates empty file with size 0 bytes and overwrites the existing file and then you get … Read more

Insert row into Excel spreadsheet using openpyxl in Python

== Updated to a fully functional version, based on feedback here: groups.google.com/forum/#!topic/openpyxl-users/wHGecdQg3Iw. == As the others have pointed out, openpyxl does not provide this functionality, but I have extended the Worksheet class as follows to implement inserting rows. Hope this proves useful to others. def insert_rows(self, row_idx, cnt, above=False, copy_style=True, fill_formulae=True): “””Inserts new (empty) rows … Read more

Python openpyxl data_only=True returning None

It depends upon the provenance of the file. data_only=True depends upon the value of the formula being cached by an application like Excel. If, however, the file was created by openpyxl or a similar library, then it’s probable that the formula was never evaluated and, thus, no cached value is available and openpyxl will report … Read more

openpyxl – adjust column width size

You could estimate (or use a mono width font) to achieve this. Let’s assume data is a nested array like [[‘a1′,’a2’],[‘b1′,’b2’]] We can get the max characters in each column. Then set the width to that. Width is exactly the width of a monospace font (if not changing other styles at least). Even if you … Read more

how to write to a new cell in python using openpyxl

Try this: import openpyxl wb = load_workbook(filename=”xxxx.xlsx”) ws = wb.worksheets[0] ws[‘A1’] = 1 ws.cell(row=2, column=2).value = 2 This will set Cells A1 and B2 to 1 and 2 respectively (two different ways of setting cell values in a worksheet). The second method (specifying row and column) is most useful for your situation: import openpyxl wb … Read more

Fill cells with colors using openpyxl?

I believe the issue is that you’re trying to assign a fill object to a style. ws[‘A1’].fill = redFill should work fine.

python: why does replace not work?

String.replace(substr) does not happen in place, change it to: string = string.replace(“http://”,””)