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')

Autofit the columns in excel using openpyxl,openpyxl part 5

 #autofit the columns

#planet_sheet = your current worksheet   
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

 

Source:https://stackoverflow.com/questions/13197574/openpyxl-adjust-column-width-size

Openpyxl part 4,writing to excel file using openpyxl

 #creating new workbook


work_book=openpyxl.Workbook()

#to see the active sheet
print(work_book.active)

#create a handle to the sheet

sheet=work_book['Sheet']

#write value in individual cells

sheet['A1']='Hello'
sheet['B1']='Excel'
sheet['C1']='Users!'

print(sheet['A1'].value)
print(sheet['B1'].value)
print(sheet['C1'].value)

#save the workbook

#work_book.save(r'C:\Users\allso\Desktop\new vba projects\openpyxl\example.xlsx')
import os
print("File location using os.getcwd():", os.getcwd())
work_book.save('example.xlsx')

#get cells in the sheet which contains data

print(sheet.calculate_dimension())


#add data after the dimension

sheet.append(['One','row','of','text'])


#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')

Openpyxl part 3,manipulating rows and columns

 import openpyxl
work_book=openpyxl.load_workbook(r'C:\Users\allso\Desktop\new vba projects\openpyxl\countries.xlsx')
print(work_book.sheetnames)
sheet_obj=work_book['Sheet1']
print(sheet_obj.title)
print(sheet_obj['A1'])
cell=sheet_obj['B1']
print(cell.value)
print(cell.row)
print(cell.column)
print(cell.number_format)
print(cell.coordinate)
print(cell.data_type)
print(sheet_obj['A2'].value+\
      '\'s capital  '+sheet_obj['B2'].value+\
      ' has a population of '+str(sheet_obj['C2'].value)\
      )
print(sheet_obj.cell(row=1,column=2))
print(sheet_obj.cell(row=1,column=2).value)
print(sheet_obj.max_row)
print(sheet_obj.max_column)
max_col=sheet_obj.max_column
for i in range(1,max_col+1):
    cell_obj=sheet_obj.cell(row=1,column=i)
    print(cell_obj.value)

max_row=sheet_obj.max_row
for i in range(1,max_row+1):
    cell_obj=sheet_obj.cell(row=i,column=1)
    print(cell_obj.value)

print(sheet_obj['A1':'C2'])
for rows in sheet_obj['A1':'C2']:
    for cell in rows:
        print(cell.coordinate,cell.value)
    print('--------------------------------')   
    
for row in sheet_obj.iter_rows(min_row=1,max_row=2,min_col=1,max_col=3):
    print(row)
    
for value in sheet_obj.iter_rows(min_row=1,max_row=2,min_col=1,max_col=3,values_only=True):
    print(value)

for colvalue in sheet_obj.iter_cols(min_row=1,max_row=3,min_col=1,max_col=3,values_only=True):
    print(colvalue)

import json

revenues={}
for rowvalue in sheet_obj.iter_rows(min_row=2,max_row=4,min_col=1,max_col=3,values_only=True):
    #print(value)
    rep=rowvalue[0]
    rev_details={"Country":rowvalue[1],"Revenue":rowvalue[2]}

    revenues[rep]=rev_details

print(json.dumps(revenues,indent=4,sort_keys=True))

Win32com python excel intro part 1

 import win32com.client as win32
excel=win32.gencache.EnsureDispatch('Excel.Application')
wb=excel.Workbooks.Open(r'C:\Users\allso\Desktop\new vba projects\openpyxl\countries.xlsx')
#get worksheet names        
sheet_names = [sheet.Name for sheet in wb.Sheets]
print(sheet_names)
sheet_obj=wb.Sheets('Sheet1')
print(sheet_obj)
print(sheet_obj.Name)
print(sheet_obj.Range("A1"))
cell=sheet_obj.Range("B1")
print(cell.Value2)
print(cell.Row)
print(cell.Column)
print(cell.NumberFormat)
print(cell.Address)
print(cell.AddressLocal)
print(sheet_obj.Range('A2').Value+\
      '\'s capital  '+sheet_obj.Range('B2').Value+\
      ' has a population of '+str(sheet_obj.Range('C2').Value)\
      )
print(sheet_obj.Cells(1,2))
print(sheet_obj.Cells(1,2).Value)
xlUp = -4162
print(sheet_obj.Cells(sheet_obj.Cells.Rows.Count, 1).End(xlUp).Row)
used = sheet_obj.UsedRange
nrows = used.Row + used.Rows.Count - 1
ncols = used.Column + used.Columns.Count - 1
print(nrows)
print(ncols)
for i in range(1,ncols+1):
    print(sheet_obj.Cells(1,i))
    #print(cell_obj.Value)
for i in range(1,nrows+1):
    print(sheet_obj.Cells(i,1))
    #print(cell_obj.value)
    
print(sheet_obj.Range('A1:C2'))
##for rows in sheet_obj.Range('A1:C2'):
##    
##    for cell in rows:
##        print(cell.Address,cell.Value)
##    print('--------------------------------')
for j in range(1,nrows+1):
    for i in range(1,ncols+1):
        print(sheet_obj.Cells(j,i))
    print('--------------------------------')    
wb.Close(True)
excel.Application.Quit()

Openpyxl part 2,accessing data in excel using python

 import openpyxl
work_book=openpyxl.load_workbook(r'C:\Users\allso\Desktop\new vba projects\openpyxl\countries.xlsx')
print(work_book.sheetnames)
sheet_obj=work_book['Sheet1']
print(sheet_obj.title)
print(sheet_obj['A1'])
cell=sheet_obj['B1']
print(cell.value)
print(cell.row)
print(cell.column)
print(cell.number_format)
print(cell.coordinate)
print(cell.data_type)
print(sheet_obj['A2'].value+\
      '\'s capital  '+sheet_obj['B2'].value+\
      ' has a population of '+str(sheet_obj['C2'].value)\
      )
print(sheet_obj.cell(row=1,column=2))
print(sheet_obj.cell(row=1,column=2).value)
print(sheet_obj.max_row)
print(sheet_obj.max_column)
max_col=sheet_obj.max_column
for i in range(1,max_col+1):
    cell_obj=sheet_obj.cell(row=1,column=i)
    print(cell_obj.value)

max_row=sheet_obj.max_row
for i in range(1,max_row+1):
    cell_obj=sheet_obj.cell(row=i,column=1)
    print(cell_obj.value)

print(sheet_obj['A1':'C2'])
for rows in sheet_obj['A1':'C2']:
    for cell in rows:
        print(cell.coordinate,cell.value)
    print('--------------------------------')   
    

Openpyxl intro,open an existing xlsx and print the number of sheets as well as the first cell's content in the first sheet

 import openpyxl
work_book=openpyxl.load_workbook(r'C:\Users\allso\Desktop\new vba projects\openpyxl\countries.xlsx')
print(work_book.sheetnames)
sheet_obj=work_book['Sheet1']
print(sheet_obj.title)
print(sheet_obj['A1'])
cell=sheet_obj['B1']
print(cell.value)
print(cell.row)
print(cell.column)