Manipulating data in excel,Openpyxl part 6

 
#get cells in the sheet which contains data

print(sheet.calculate_dimension())


#save the workbook

work_book.save(r'C:\Users\allso\Desktop\new vba projects\openpyxl\example.xlsx')

#inserting rows
#this will insert three rows after second row,that will change the dimension
sheet.insert_rows(idx=2,amount=3)

print(sheet.calculate_dimension())

#now we will add columns,we are adding columns to c column

sheet.insert_cols(idx=3)

print(sheet.calculate_dimension())

sheet.insert_cols(idx=3,amount=2)

print(sheet.calculate_dimension())

#save the workbook

work_book.save(r'C:\Users\allso\Desktop\new vba projects\openpyxl\example.xlsx')

#delete a column

sheet.delete_cols(3)

print(sheet.calculate_dimension())


#save the workbook

#delete multiple columns

sheet.delete_cols(5,2)


work_book.save(r'C:\Users\allso\Desktop\new vba projects\openpyxl\example.xlsx')


print(sheet.calculate_dimension())


#change the default sheetname

sheet.title="First Sheet"

print(work_book.active)


work_book.save(r'C:\Users\allso\Desktop\new vba projects\openpyxl\example.xlsx')

#Writing multiple rows using list of lists

data=[['Planet','Radius (km)','Distance from Sun (m km)'],
      ['Earth',6371,150],
      ['Mars',3389,228],
      ['Mercury',2440,58]]


#creating a new workbook

planet_wb=openpyxl.Workbook()
planet_sheet=planet_wb['Sheet']

planet_sheet.title='Planets'
for row in data:
    planet_sheet.append(row)

#autofit the columns
    
dims = {}
for row in planet_sheet.rows:
    for cell in row:
        if cell.value:
            #dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value))))
            dims[cell.column_letter] = max((dims.get(cell.column_letter, 0), len(str(cell.value))))
for col, value in dims.items():
    planet_sheet.column_dimensions[col].width = value

    
print(planet_sheet.calculate_dimension())
planet_wb.save(r'C:\Users\allso\Desktop\new vba projects\openpyxl\planets.xlsx')