1
##########################
#working with xlsx files #
##########################

import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
import os
import datetime

my_cellstatus_headders = ['Server Name','Cell Name','Role','Current Status','Expected Status','Health']
log_path = 'C:\\Users\\686559\\Desktop\\TESTPERL\\TESTPYTHON\\Create_excel\\bin\\Infra_Health_Status.xlsx'

thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
alignment = Alignment(horizontal='center',vertical='bottom',text_rotation=0,wrap_text=False,shrink_to_fit=False,indent=0)

try:
    mywb = openpyxl.Workbook()
except:
    print ("Couldn't create the file ", log_path)
sheet = mywb.active
sheet.title = "Cell_Status"

for i in range (1,7):
    sheet.cell(row=1,column=i).value = my_cellstatus_headders[i-1]
    sheet.cell(row=1,column=i).font = Font(bold=True)
    sheet.cell(row=1,column=i).fill = PatternFill(fgColor="D8D8D8", fill_type = "solid")
    sheet.cell(row=1,column=i).border = thin_border
    sheet.cell(row=1,column=i).alignment = alignment


columns = sheet.columns
#help (columns)
for col in columns:
    max_length = 0
    #print (col)
    for cell in col:
       try:
          if len(str(cell.value)) > max_length:
                max_length = int(len(cell.value))
       except:
            pass
       adjusted_width = max_length
       sheet.column_dimensions['col'].width = adjusted_width
try:
    mywb.save(log_path)
except:
    print ("Could not save the file ",log_path)

but the excel sheet which is getting created is not getting the width of the column set to the max characters in the cell. Any help or idea is appreciated.

current output : enter image description here

expected output : enter image description here

kaushik km
  • 39
  • 8
  • Does this answer your question? [openpyxl - adjust column width size](https://stackoverflow.com/questions/13197574/openpyxl-adjust-column-width-size) – APhillips Jan 02 '20 at 16:09
  • Hi @APhillips, i am quite new to scripting, Could you please help me modify the above code ? or Could you please explain the importance of each line the correct marked answer in the link you provided. thanks for help again. – kaushik km Jan 02 '20 at 16:12
  • Could you please explain the correct answer from the link : https://stackoverflow.com/questions/13197574/openpyxl-adjust-column-width-size – kaushik km Jan 02 '20 at 17:42
  • 1
    You do not set the column width anywhere. – Charlie Clark Jan 02 '20 at 18:56
  • Hi @CharlieClark, right, after i get the max width in the variable "adjusted_width", how to set it to that particular column ? – kaushik km Jan 02 '20 at 19:45
  • `sheet.column_dimensions['col'] = adjusted_width` i added the above line after the `adjusted_width` variable line, but the excel sheet is getting corrupted. – kaushik km Jan 02 '20 at 20:06
  • Getting the below error after adding the line: Could not save the file C:\Users\686559\Desktop\TESTPERL\TESTPYTHON\Create_excel\bin\Infra_Health_Status.xlsx Error in atexit._run_exitfuncs: Traceback (most recent call last): File "C:\Users\686559\AppData\Roaming\Python\Python38\site-packages\openpyxl\worksheet\_writer.py", line 32, in _openpyxl_shutdown os.remove(path) PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: 'C:\\Users\\686559\\AppData\\Local\\Temp\\openpyxl.4y_yxyry' – kaushik km Jan 02 '20 at 20:11
  • Make sure the excel file you're editing is closed. You cannot edit it with Python while it's open. – APhillips Jan 03 '20 at 17:20
  • @APhillips got that thanks, i modified the code in the below answer, please let me know if using regular expression to extract the column name is good ? if not what is the better way ? – kaushik km Jan 03 '20 at 22:17

1 Answers1

0
##########################
#working with xlsx files #
##########################

import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
import os
import datetime
import re

my_cellstatus_headders = ['Server Name','Cell Name','Role','Current Status','Expected Status','Health']
log_path = 'C:\\Users\\686559\\Desktop\\TESTPERL\\TESTPYTHON\\Create_excel\\bin\\Infra_Health_Status.xlsx'

thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
alignment = Alignment(horizontal='center',vertical='bottom',text_rotation=0,wrap_text=False,shrink_to_fit=False,indent=0)

try:
    mywb = openpyxl.Workbook()
except:
    print ("Couldn't create the file ", log_path)
sheet = mywb.active
sheet.title = "Cell_Status"

for i in range (1,7):
    sheet.cell(row=1,column=i).value = my_cellstatus_headders[i-1]
    sheet.cell(row=1,column=i).font = Font(bold=True)
    sheet.cell(row=1,column=i).fill = PatternFill(fgColor="D8D8D8", fill_type = "solid")
    sheet.cell(row=1,column=i).border = thin_border
    sheet.cell(row=1,column=i).alignment = alignment


columns = sheet.columns
#help (columns)
for col in columns:
    max_length = 0
    #print (col)
    for cell in col:
       try:
          if len(str(cell.value)) > max_length:
                max_length = int(len(cell.value))
       except:
            pass
       adjusted_width = max_length
       #print (col[0]) #<Cell 'Cell_Status'.A1>
       #https://stackoverflow.com/questions/13197574/openpyxl-adjust-column-width-size
       col_name = re.findall('\w\d', str(col[0])) #pull the last letter+digit
       col_name = col_name[0]
       col_name = re.findall('\w', str(col_name))[0]
       #print (col_name)
    sheet.column_dimensions[col_name].width = adjusted_width+2
try:
    mywb.save(log_path)
except:
    print ("Could not save the file ",log_path)

this worked for me, Is there any way to make it better ? Please suggest. thanks

kaushik km
  • 39
  • 8