Reading Excel Spreadsheets with Python

Excel & Python

Reading an Excel Spreadsheet

In this section, we will look at a function that demonstrates reads the Excel sheet data and convert that data into XML file.

We Used the following libraries to do the above functionality

• Openpyxl : Openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files.
• xml.etree.ElementTree: To create the xml tree elements
• datetime: to conver the date time column values to specified format 

	from openpyxl import load_workbook
	import datetime
	import xml.etree.cElementTree as ET
	wb = load_workbook(filename = 'Sku Assignment 8.24 Furniture Content.xlsx')
	sheet = wb.get_sheet_by_name('Output Sheet')#sheet name
	root = ET.Element("skuProducts")
	i=0
	for rowOfCellObjects in sheet.rows:
	doc = ET.SubElement(root, "skuProduct"+str(i))
	j=0
	for cellObj in rowOfCellObjects:
	print(cellObj.coordinate, cellObj.value)
	print type(cellObj.value)
	datatype = type(cellObj.value)
	if( datatype is unicode):
	ET.SubElement(doc, cellObj.coordinate).text = cellObj.value
	elif( datatype is long):
	print int(cellObj.value)
	ET.SubElement(doc, cellObj.coordinate).text = str(int(cellObj.value))
	elif isinstance(cellObj.value, datetime.date):
	#print cellObj.value.strftime('%m/%d/%Y')
	#pass
	ET.SubElement(doc, cellObj.coordinate).text = str(cellObj.value.strftime('%m/%d/%Y'))
	else:
	print "pass"
	pass
	j=j+1
	i=i+1
	print('--- END OF ROW ---')
	tree = ET.ElementTree(root)
	tree.write("filename.xml")

view raw convert.py hosted with ❤ by GitHub