As of openpyxl version 1.5.7, I have successfully applied the following worksheet style options… from openpyxl.reader.excel import load_workbook from openpyxl.workbook import Workbook from openpyxl.styles import Color, Fill from openpyxl.cell import Cell # Load the workbook… book = load_workbook(‘foo.xlsx’) # define ws here, in this case I pick the first worksheet in the workbook… # NOTE: … Read more
You should use wb[sheetname] from openpyxl import load_workbook wb2 = load_workbook(‘test.xlsx’) ws4 = wb2[“New Title”] PS: You should check if your sheet in sheet names wb.sheetnames print(wb2.sheetnames) [‘Sheet2’, ‘New Title’, ‘Sheet1’]
As noted in the bug report you linked to there’s a difference between a sheet’s reported dimensions and whether these include empty rows or columns. If max_row and max_column are not reporting what you want to see then you will need to write your own code to find the first completely empty. The most efficient … Read more
Openpyxl 1.7 contains several improvements for handling formulae so that they are preserved when reading. Use guess_types=False to prevent openpyxl from trying to guess the type for a cell and 1.8 includes the data_only=True option if you want the values but not the formula. Want to preserve charts in the 2.x series.
Figured it out by myself: #Prepare the excel we want to write to t=pd.date_range(‘2004-01-31′, freq=’M’, periods=4) first=pd.DataFrame({‘A’:[1,1,1,1], ‘B’:[2,2,2,2]}, index=t) first.index=first.index.strftime(‘%Y-%m-%d’) writer=pd.ExcelWriter(‘test.xlsx’) first.to_excel(writer, sheet_name=”Here”) first.to_excel(writer, sheet_name=”Keep”) #read the existing sheets so that openpyxl won’t create a new one later book = load_workbook(‘test.xlsx’) writer = pandas.ExcelWriter(‘test.xlsx’, engine=”openpyxl”) writer.book = book writer.sheets = dict((ws.title, ws) for ws in … Read more
To do something when cell is not empty add: if cell.value: which in python is the same as if cell value is not None (i.e.: if not cell.value == None:) Note to avoid checking empty cells you can use worksheet.get_highest_row() and worksheet.get_highest_column() Also I found it useful (although might not be a nice solution) if … Read more
Adding on to what Hubro said, apparently get_highest_row() has been deprecated. Using the max_row and max_column properties returns the row and column count. For example: wb = load_workbook(path, use_iterators=True) sheet = wb.worksheets[0] row_count = sheet.max_row column_count = sheet.max_column
If you don’t use conda, just use : pip install openpyxl If you use conda, I’d recommend : conda install -c anaconda openpyxl instead of simply conda install openpyxl Because there are issues right now with conda updating (see GitHub Issue #8842) ; this is being fixed and it should work again after the next … Read more
Answering my own question here. Thanks to JvdV for putting me on the right path. In this answer I found what solved my question. I added this line before saving the excel file in my example code: ws.formula_attributes[‘E9’] = {‘t’: ‘array’, ‘ref’: “E9:E9”} This essentially sets the formula in cell ‘E9’ to be read as … Read more
To assign a value to a cell, use =: cell.value = statN You also need to fix your loops. Notice that right now, for each element in lstStat, you are writing the entire range. Besides not being what you intended, it also is less flexible: What happens if lstStat has more or fewer elements? What … Read more