Writing a list of lists in an excel file using openpyxl

import openpyxl
work_book=openpyxl.Workbook()
work_sheet=work_book.active
data=[["Champion","Year"],
    ['Uruguay',    1930],
    ['Italy',    1934],
    ['Italy',    1938],
    ['Uruguay',    1950],
    ['Germany',    1954],
    ['Brazil',    1958],
    ['Brazil',    1962],
    ['England',    1966],
    ['Brazil',    1970],
    ['Germany',    1974],
    ['Argentina',1978],
    ['Italy',    1982],
    ['Argentina',1986],
    ['Germany',    1990],
    ['Brazil',    1994],
    ['France',    1998],
    ['Brazil',    2002],
    ['Italy',    2006],
    ['Spain',    2010],
    ['Germany',    2014],
    ['France',    2018],
    ['Argentina',2022]]

for r in data:
    work_sheet.append(r)


#save the work book

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

Freeze top row using Openpyxl

import openpyxl

#freeze and unfreeze using openpyxl
#open zomato spreadsheet

work_book=openpyxl.load_workbook(r'C:\Users\allso\Desktop\new vba projects\openpyxl\zomato.xlsx')


#select active sheet

sheet=work_book.active

#freeze the top row
#all rows above the current row and all columns left to the current column will be frozen

sheet.freeze_panes="A2"

#save the work book

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

Convert csv to xlsx as openpyxl does not support .csv file format

import csv
from openpyxl import Workbook

wb = Workbook()
ws = wb.worksheets[0]
ws.title = "zomato"

with open(r'C:\Users\allso\Desktop\new vba projects\openpyxl\zomato.csv',encoding='cp437', errors='ignore') as f:
    reader = csv.reader(f)
    for row_index, row in enumerate(reader):
        for column_index, cell in enumerate(row):
            column_letter = column_index + 1
            cell_value = cell.replace('"', '')
            ws.cell(row = row_index + 1, column = column_letter).value = cell_value



wb.save(filename = r'C:\Users\allso\Desktop\new vba projects\openpyxl\zomato.xlsx')

vlsub not working solved

opensubtitles API has changed its request format from XML to JSON only. In adiction it also needs a session token to validate requests. So, in a few words, VLSub needs a re-work to replace the old function, to call the API with a valid token and process JSON data.

EDIT: But, there's a workarround for this. As the previous version of opensubtitles is still working, you can edit your system hosts file to route VLSub call as XML. The default path of the hosts file in Windows 10 appears to be C:\Windows\System32\drivers\etc and you have to open your text editor as admin to edit the file. Add a new line and type 104.25.132.104 api.opensubtitles.org Save hosts file and try VLSub again. NOTE: This is valid while XML service is active in the old API address.

Some common usages of cat command

1) To view a single file 
Command: 
 

$cat filename

Output 
 

It will show content of given filename

 

2) To view multiple files 
Command: 

 

 

$cat file1 file2

Output 
 

This will show the content of file1 and file2.

 

 

3) To view contents of a file preceding with line numbers. 
Command: 
 

$cat -n filename

Output 
 

It will show content with line number

example:-cat -n  geeks.txt

1)This is geeks

2)A unique array

 

4) Create a file 
Command: 
 

$ cat > newfile

Output 
 

Will create a file named newfile

 

5) Copy the contents of one file to another file. 
Command: 
 

$cat [filename-whose-contents-is-to-be-copied] > [destination-filename]

Output 
 

The content will be copied in destination file

 

6) Cat command can append the contents of one file to the end of another file. 
Command: 
 

$cat file1 >> file2

Output 
 

Will append the contents of one file to the end of another file

7) Cat command can display content in reverse order using tac command. 
Command: 
 

 $tac filename

Output 
 

Will display content in reverse order 

8) Cat command to merge the contents of multiple files. 
Command: 
 

$cat "filename1" "filename2" "filename3" > "merged_filename"

Output 
 

Will merge the contents of file in respective order and will insert that content in "merged_filename".

9) Cat command to display the content of all text files in the folder. 
Command: 
 

$cat *.txt

Output 
 

Will show the content of all text files present in the folder.

 

10) Cat command can suppress repeated empty lines in output 
Command: 
 

$cat -s geeks.txt

Output 

Will suppress repeated empty lines in output

Source:https://www.geeksforgeeks.org/cat-command-in-linux-with-examples/ 

 

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)