XLS - Revision history

Hdridder: /* Pandas */

2020-06-29T13:50:18Z

Pandas

← Older revision		Revision as of 13:50, 29 June 2020
Line 100:		Line 100:
	==Pandas==		==Pandas==

	[[Pandas]] ~~has an excel import module read_excel~~.		There is a [[Pandas#Reading_Data\|pandas function to read excel files ]] you can use to avoid all this.

	Using the routine above you can try:		Using the routine above you can try to convert it to a pandas dataframe too:
	;df = pd.DataFrame(xlsx(xlsfile,sheetname))		;df = pd.DataFrame(xlsx(xlsfile,sheetname))
	:Put the sheet into a pandas dataframe		:Put the sheet into a pandas dataframe

Hdridder: /* Pandas */

2020-01-23T12:21:59Z

Pandas

← Older revision		Revision as of 12:21, 23 January 2020
Line 98:		Line 98:
	</syntaxhighlight>		</syntaxhighlight>

	==[[Pandas]]==		==Pandas==

	Using the routine above you can:		[[Pandas]] has an excel import module read_excel.

			Using the routine above you can try:
	;df = pd.DataFrame(xlsx(xlsfile,sheetname))		;df = pd.DataFrame(xlsx(xlsfile,sheetname))
	:Put the sheet into a pandas dataframe		:Put the sheet into a pandas dataframe

Hdridder at 11:20, 23 January 2020

2020-01-23T11:20:18Z

← Older revision		Revision as of 11:20, 23 January 2020
Line 97:		Line 97:
	return rows		return rows
	</syntaxhighlight>		</syntaxhighlight>

			==[[Pandas]]==

			Using the routine above you can:
			;df = pd.DataFrame(xlsx(xlsfile,sheetname))
			:Put the sheet into a pandas dataframe

			;df.columns = df.iloc[1]
			:Set the column names as the first row in the sheet.

Hdridder at 10:45, 23 January 2020

2020-01-23T10:45:21Z

@@ Line 64: / Line 64: @@
                         in iterparse(z.open('xl/sharedStrings.xml'))
                         if element.tag.endswith('}t')]
      sheets = { element.attrib['name']:element.attrib['sheetId'] for event,element in iterparse(z.open('xl/workbook.xml'))
                                            if element.tag.endswith('}sheet') }
      rows = []
      row = {}
@@ Line 89: / Line 95: @@
                  row = {}
      return rows
 </syntaxhighlight>

Hdridder at 13:44, 29 October 2018

2018-10-29T13:44:32Z

← Older revision		Revision as of 13:44, 29 October 2018
Line 72:		Line 72:
	if sheet in sheets:		if sheet in sheets:
	sheetfile = 'xl/worksheets/sheet'+sheets[sheet]+'.xml'		sheetfile = 'xl/worksheets/sheet'+sheets[sheet]+'.xml'
			#print(sheet,sheetfile)
	for event, element in iterparse(z.open(sheetfile)):		for event, element in iterparse(z.open(sheetfile)):
	# get value or index to shared strings		# get value or index to shared strings
	if element.tag.endswith('}v'):		if element.tag.endswith('}v') or element.tag.endswith('}t'):
	value = element.text		value = element.text
	# If value is a shared string, use value as an index		# If value is a shared string, use value as an index
	if element.tag.endswith('}c'):		if element.tag.endswith('}c'):
	if element.attrib.get('t') == 's':		if element.attrib.get('t') == 's':
	value = strings[int(value)]		value = strings[int(value)]
	# split the row/col information so that the row leter(s) can be separate		# split the row/col information so that the row leter(s) can be separate
	letter = re.sub('\D','',element.attrib['r'])		letter = re.sub('\d','',element.attrib['r'])
	row[letter] = value		row[letter] = value
	value = ''		value = ''
	if element.tag.endswith('}row'):		if element.tag.endswith('}row'):
	rows.append(row)		rows.append(row)
	row = {}		row = {}

	return rows		return rows
	</syntaxhighlight>		</syntaxhighlight>

Hdridder at 08:36, 29 October 2018

2018-10-29T08:36:49Z

← Older revision		Revision as of 08:36, 29 October 2018
Line 64:		Line 64:
	in iterparse(z.open('xl/sharedStrings.xml'))		in iterparse(z.open('xl/sharedStrings.xml'))
	if element.tag.endswith('}t')]		if element.tag.endswith('}t')]
	~~sheetdict~~ = { element.attrib['name']:element.attrib['sheetId'] for event,element in iterparse(z.open('xl/workbook.xml'))		sheets = { element.attrib['name']:element.attrib['sheetId'] for event,element in iterparse(z.open('xl/workbook.xml'))
	if element.tag.endswith('}sheet') }		if element.tag.endswith('}sheet') }
	rows = []		rows = []
Line 70:		Line 70:
	value = ''		value = ''

	if sheet in ~~sheetdict~~:		if sheet in sheets:
	sheetfile = 'xl/worksheets/sheet'+~~sheetdict~~[sheet]+'.xml'		sheetfile = 'xl/worksheets/sheet'+sheets[sheet]+'.xml'
	for event, element in iterparse(z.open(sheetfile)):		for event, element in iterparse(z.open(sheetfile)):
	# get value or index to shared strings		# get value or index to shared strings

Hdridder at 11:05, 28 October 2018

2018-10-28T11:05:02Z

← Older revision		Revision as of 11:05, 28 October 2018
Line 47:		Line 47:
	\|}		\|}

	Found this on [https://stackoverflow.com/questions/4371163/reading-xlsx-files-using-python ~~stackoverflow~~]. It is announced as very very basic but it works very well.		Found this on [https://stackoverflow.com/ stackoverflow] where it has been removed [https://stackoverflow.com/questions/4371163/reading-xlsx-files-using-python]. It was announced as very very basic but it works very well and it only uses modules installed by default (at least in [https://www.ubuntu.com/ Ubuntu] 18.04). You can find the original solution at the end of this page.

			The routine returns a list of dicts like { <columname> : <cellvalue> [, .. ] }

			Our solution supporting workbooks with and without sharedstrings and fetches sheet content by the sheet name. Using 're' to find the column, this may be slower than the original approach but we have not found that in the test-files used.

	~~This routine returns a list of dicts like { <columname> : <cellvalue> [, .. ] }~~
	~~The original, with sharedStrings:~~
	<syntaxhighlight lang=python>		<syntaxhighlight lang=python>
	def xlsx(fname):		def xlsx(fname,sheet):
	import zipfile		import zipfile
	from xml.etree.ElementTree import iterparse		from xml.etree.ElementTree import iterparse
			import re
	z = zipfile.ZipFile(fname)		z = zipfile.ZipFile(fname)
	# Get shared strings		if 'xl/sharedStrings.xml' in z.namelist():
	strings = [ element.text		# Get shared strings
	for event,element in iterparse(z.open('xl/sharedStrings.xml'))		strings = [element.text for event, element
	if element.tag.endswith('}t') ]		in iterparse(z.open('xl/sharedStrings.xml'))
	~~# Get sheetnames and index~~		if element.tag.endswith('}t')]
	sheetdict = { element.attrib['name']:element.attrib['sheetId']		sheetdict = { element.attrib['name']:element.attrib['sheetId'] for event,element in iterparse(z.open('xl/workbook.xml'))
	for event,element in iterparse(z.open('xl/workbook.xml'))		if element.tag.endswith('}sheet') }
	if element.tag.endswith('}sheet') }

	rows = []		rows = []
	row = {}		row = {}
Line 80:		Line 81:
	value = strings[int(value)]		value = strings[int(value)]
	# split the row/col information so that the row leter(s) can be separate		# split the row/col information so that the row leter(s) can be separate
	letter = element.attrib['r']		letter = re.sub('\D','',element.attrib['r'])
	~~while letter[-1].isdigit(~~):
	~~letter = letter[:-1]~~
	row[letter] = value		row[letter] = value
	value = ''		value = ''
Line 91:		Line 90:
	</syntaxhighlight>		</syntaxhighlight>

	~~Adapted for sheets without sharedStrings~~:		The original as found on stackoverflow:
	<syntaxhighlight lang=python>		<syntaxhighlight lang=python>
	def xlsx(fname):		def xlsx(fname):
Line 97:		Line 96:
	from xml.etree.ElementTree import iterparse		from xml.etree.ElementTree import iterparse
	z = zipfile.ZipFile(fname)		z = zipfile.ZipFile(fname)
	~~# Get shared strings~~		strings = [el.text for e, el in iterparse(z.open('xl/sharedStrings.xml')) if el.tag.endswith('}t')]
	#strings = [el.text for e, el in iterparse(z.open('xl/sharedStrings.xml'))
	# if el.tag.endswith('}t')]
	rows = []		rows = []
	row = {}		row = {}
	value = ''		value = ''
	for e, el in iterparse(z.open('xl/worksheets/~~sheet7~~.xml')):		for e, el in iterparse(z.open('xl/worksheets/sheet1.xml')):
	~~# get value or index to shared strings~~		if el.tag.endswith('}v'): # <v>84</v>
	if el.tag.endswith('}v'): # <v>84</v>
	~~value = el.text~~
	~~if el.tag.endswith('}t'): # <t>String</t~~>
	value = el.text		value = el.text
	~~# If value is a shared string, use value as an index~~		if el.tag.endswith('}c'): # <c r="A3" t="s"><v>84</v></c>
	if el.tag.endswith('}c'): # <c r="A3" t="s"><v>84</v></c>
	if el.attrib.get('t') == 's':		if el.attrib.get('t') == 's':
	value = strings[int(value)]		value = strings[int(value)]
	~~# split the row/col information so that the row leter(s) can be separate~~		letter = el.attrib['r'] # AZ22
	letter = el.attrib['r'] # AZ22
	while letter[-1].isdigit():		while letter[-1].isdigit():
	letter = letter[:-1]		letter = letter[:-1]

Hdridder at 20:33, 27 October 2018

2018-10-27T20:33:38Z

← Older revision		Revision as of 20:33, 27 October 2018
Line 57:		Line 57:
	z = zipfile.ZipFile(fname)		z = zipfile.ZipFile(fname)
	# Get shared strings		# Get shared strings
	strings = [el.text for e, el		strings = [ element.text
	in iterparse(z.open('xl/sharedStrings.xml'))		for event,element in iterparse(z.open('xl/sharedStrings.xml'))
	if el.tag.endswith('}t')]		if element.tag.endswith('}t') ]
			# Get sheetnames and index
			sheetdict = { element.attrib['name']:element.attrib['sheetId']
			for event,element in iterparse(z.open('xl/workbook.xml'))
			if element.tag.endswith('}sheet') }

	rows = []		rows = []
	row = {}		row = {}
	value = ''		value = ''
	for e, el in iterparse(z.open(~~'xl/worksheets/sheet1.xml'~~)):
	# get value or index to shared strings		if sheet in sheetdict:
	if el.tag.endswith('}v'): ~~# <v>84</v>~~		sheetfile = 'xl/worksheets/sheet'+sheetdict[sheet]+'.xml'
	value = el.text		for event, element in iterparse(z.open(sheetfile)):
	# If value is a shared string, use value as an index		# get value or index to shared strings
	if el.tag.endswith('}c'): ~~# <c r="A3" t="s"><v>84</v></c>~~		if element.tag.endswith('}v'):
	if el.attrib.get('t') == 's':		value = element.text
	value = strings[int(value)]		# If value is a shared string, use value as an index
	# split the row/col information so that the row leter(s) can be separate		if element.tag.endswith('}c'):
	letter = el.attrib['r'] ~~# AZ22~~		if element.attrib.get('t') == 's':
	while letter[-1].isdigit():		value = strings[int(value)]
	letter = letter[:-1]		# split the row/col information so that the row leter(s) can be separate
	row[letter] = value		letter = element.attrib['r']
	value = ''		while letter[-1].isdigit():
	if el.tag.endswith('}row'):		letter = letter[:-1]
	rows.append(row)		row[letter] = value
	row = {}		value = ''
			if element.tag.endswith('}row'):
			rows.append(row)
			row = {}
	return rows		return rows
	</syntaxhighlight>		</syntaxhighlight>

Hdridder at 18:27, 27 October 2018

2018-10-27T18:27:10Z

← Older revision		Revision as of 18:27, 27 October 2018
Line 5:		Line 5:
	A simple workbook with 3 sheets having the same content like this:		A simple workbook with 3 sheets having the same content like this:

	[[File:~~test_xls~~.png]]		[[File:Sample_xls.png]]

	Consists of the following .xml files:		Consists of the following .xml files:

Hdridder at 16:49, 27 October 2018

2018-10-27T16:49:07Z

← Older revision		Revision as of 16:49, 27 October 2018
Line 3:		Line 3:
	Python XLS parser using standard modules. xlsx files are basically just a set of compressed (zipped) .[[XML]] pages.		Python XLS parser using standard modules. xlsx files are basically just a set of compressed (zipped) .[[XML]] pages.

			A simple workbook with 3 sheets having the same content like this:

			[[File:test_xls.png]]

			Consists of the following .xml files:

			{\| class="wikitable"
			!Filename
			!Remarks
			\|-
			\| style="width:28ch \|./[Content_Types].xml
			\|
			\|-
			\| ./docProps/core.xml
			\|
			\|-
			\| ./docProps/app.xml
			\|
			\|-
			\| ./xl/workbook.xml
			\| Holds per sheet a node like '<sheet name="Test2" sheetId="2" state="visible" r:id="rId3"/>'
			\|-
			\| ./xl/sharedStrings.xml
			\| Strings that appear in more sheets, references from the sheet by indexnumber.
			\|-
			\| ./xl/styles.xml
			\|
			\|-
			\| ./xl/worksheets/sheet2.xml
			\| Content of sheet2
			\|-
			\| ./xl/worksheets/sheet1.xml
			\| Content of sheet1
			\|-
			\| ./xl/worksheets/sheet3.xml
			\| Content of sheet3
			\|-
			\| ./xl/_rels/workbook.xml.rels
			\|
			\|-
			\| ./_rels/.rels
			\|
			\|}

	Found this on [https://stackoverflow.com/questions/4371163/reading-xlsx-files-using-python stackoverflow]. It is announced as very very basic but it works very well.		Found this on [https://stackoverflow.com/questions/4371163/reading-xlsx-files-using-python stackoverflow]. It is announced as very very basic but it works very well.