Linux World: Copy rows from one spreadsheet to new one based on columns in a spreadsheet

If we want to copy the rows from one spreadsheet to a new file based on values in another file, for example we have the files listed below.

File1 File2

If we want to create a new file which has the rows of File1 only for those ids which are present in file2 we can use the following pyoo script.

Steps for installation of pyoo has been given here.

Before executing the script, we have to ensure that libreoffice is listening to the pyoo by running the following command

In the script below, file1points to the file with source data ,file2 points to the file which has the reference values based on which we want to copy and copy points to the file which will be created. Update the paths respectively.

	#!/usr/bin/env python3

	"""
	Tested on python 3.6
	file1: File from where rows need to be copied
	file2: File having the reference values
	copy : The new file to be created with the required rows.
	Update the above three paths before executing the script.
	"""

	import pyoo
	file1=""
	file2=""
	copy=""
	desktop = pyoo.Desktop('localhost', 2002)
	try:
	doc1 = desktop.open_spreadsheet(file1)
	doc2 = desktop.open_spreadsheet(file2)
	doc3 = desktop.create_spreadsheet()
	except Exception as e :
	print(e)
	print("\nInvalid path for the files")
	quit()

	srcSheet = doc1.sheets[0] #Assuming the sheet0 has the data
	fromSheet= doc2.sheets[0]
	toSheet=doc3.sheets[0]


	fr=tr=1
	fcol=0
	scol=0
	while(srcSheet[0,scol].value != ''):
	toSheet[0,scol].value=srcSheet[0,scol].value
	toSheet[0,scol].background_color= srcSheet[0,scol].background_color
	toSheet[0,scol].text_color=srcSheet[0,scol].text_color
	toSheet[0,scol].font_weight=srcSheet[0,scol].font_weight
	toSheet[0,scol].border_width=srcSheet[0,scol].border_width
	toSheet[0,scol].border_color=srcSheet[0,scol].border_color
	scol=scol+1
	scol=1
	i=0

	while(fromSheet[fr,fcol].value != ''):
	sr=1
	while(srcSheet[sr,scol].value != ''):
	if(srcSheet[sr,scol].value == fromSheet[fr,fcol].value):
	toSheet[tr,:].values=srcSheet[sr,:].values
	tr=tr+1
	toSheet[tr,:].background_color= \
	fromSheet[fr,fcol].background_color
	toSheet[tr,:].text_color=srcSheet[sr,:].text_color
	toSheet[tr,:].font_weight=srcSheet[sr,:].font_weight
	toSheet[tr,:].border_width=srcSheet[sr,:].border_width
	toSheet[tr,:].border_color=srcSheet[sr,:].border_color
	i=i+1

	sr=sr+1
	fr=fr+1
	print("Copied " + str(i) + " rows")



	doc3.save(copy)
	doc1.close()
	doc2.close()
	doc3.close()