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
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
for col in worksheet.columns: max_length = 0 column = col[0].column_letter # Get the column name for cell in col: try: # Necessary to avoid error on empty cells if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = (max_length + 2) * 1.2 worksheet.column_dimensions[column].width = adjusted_width This could probably be made neater but it … Read more
openpyxl 2.4 comes with a utility for converting Pandas Dataframes into something that openpyxl can work with directly. Code would look a bit like this: from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(df) for r_idx, row in enumerate(rows, 1): for c_idx, value in enumerate(row, 1): ws.cell(row=r_idx, column=c_idx, value=value) You can adjust the start of the enumeration … Read more
The following inserts an image in cell A1. Adjust the image location to your needs or handle the creation of the PIL image yourself and hand that to Image() import openpyxl wb = openpyxl.Workbook() ws = wb.worksheets[0] img = openpyxl.drawing.image.Image(‘test.jpg’) img.anchor=”A1″ ws.add_image(img) wb.save(‘out.xlsx’) In older versions of openpyxl the following works: import openpyxl wb = … Read more
openpyxl support either the formula or the value of the formula. You can select which using the data_only flag when opening a workbook. However, openpyxl does not and will not calculate the result of a formula. There are libraries out there like pycel which purport to do this.
You can do this by changing the title attribute: import openpyxl ss = openpyxl.load_workbook(“file.xlsx”) #printing the sheet names ss_sheet = ss[‘Sheet’] ss_sheet.title=”Fruit” ss.save(“file.xlsx”)
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
wb = openpyxl.load_workbook(filename, data_only=True) The data_only flag helps.
yes, there is a way to do this with openpyxl: from openpyxl.styles import Alignment currentCell = ws.cell(‘A1’) #or currentCell = ws[‘A1’] currentCell.alignment = Alignment(horizontal=”center”) hope this will help you