comet.CExcelBook
*******************************************************************************

.. py:class:: comet.CExcelBook

This section describes all functions for the comet.CExcelBook class.

.. seealso::

	Module :py:mod:`comet.excel`
		Accessor and utility functions for excel files.


CExcelBook represents an opened Excel file in memory. This class can be used to query the Excel sheets for information and content.

.. container:: priint-block

	**Important**: When querying cell attributed like border styles etc..., you have to fetch them from the actual indices and not the anchor cell.\n
	e.g. When trying to determine the right border of a cell merged from column 0-1, you have to query the right border color at column 1.

Methods
===============================================================================

.. automethod:: comet.CExcelBook.getSheetCount
.. automethod:: comet.CExcelBook.getSheetName
.. automethod:: comet.CExcelBook.getFirstRow
.. automethod:: comet.CExcelBook.getLastRow
.. automethod:: comet.CExcelBook.getFirstColumn
.. automethod:: comet.CExcelBook.getLastColumn
.. automethod:: comet.CExcelBook.getCellType
.. automethod:: comet.CExcelBook.getCellFormula
.. automethod:: comet.CExcelBook.getCellFormat
.. automethod:: comet.CExcelBook.getCellFormatTags
.. automethod:: comet.CExcelBook.getCellArea
.. automethod:: comet.CExcelBook.getRowHeight
.. automethod:: comet.CExcelBook.getColumnWidth
.. automethod:: comet.CExcelBook.getColor
.. automethod:: comet.CExcelBook.getBorderStyle
.. automethod:: comet.CExcelBook.getFontFamily
.. automethod:: comet.CExcelBook.getFontFace
.. automethod:: comet.CExcelBook.getFontSize
.. automethod:: comet.CExcelBook.getFontColor
.. automethod:: comet.CExcelBook.getFontStrikeThrough
.. automethod:: comet.CExcelBook.getFontUnderline
.. automethod:: comet.CExcelBook.getFontPosition
.. automethod:: comet.CExcelBook.getFontWeight
.. automethod:: comet.CExcelBook.getFontItalic
.. automethod:: comet.CExcelBook.getCellContent
.. automethod:: comet.CExcelBook.setCellContent
.. automethod:: comet.CExcelBook.save
.. automethod:: comet.CExcelBook.close

Special methods
===============================================================================

.. automethod:: comet.CExcelBook.__repr__
	
Examples
===============================================================================

.. code-block:: python
	:caption: Open an excel file and fetch all cell data as HTML

	#!py
	#pragma plain

	import comet
	import datetime
	import time

	def getCellStyle(book: comet.CExcelBook, sheet, column, row):
		#Get the cell style information as HTML by querying cell format
		#attributes and translating them to CSS tags
		openTag = '<span style="'
		closeTag = ''

		#Retrieve font properties from the cell
		fontFamily = book.getFontFamily(sheet, column, row)
		fontSize = book.getFontSize(sheet, column, row)
		fontColor = book.getFontColor(sheet, column, row)
		fontStrikeThrough = book.getFontStrikeThrough(sheet, column, row)
		fontUnderline = book.getFontUnderline(sheet, column, row)
		fontPosition = book.getFontPosition(sheet, column, row)
		fontWeight = book.getFontWeight(sheet, column, row)
		fontItalic = book.getFontItalic(sheet, column, row)

		fontColorValues = fontColor.getValues()

		#Apply styles based on retrieved properties
		if fontFamily:
			openTag += f'font-family: {fontFamily};'

		openTag += f'font-size: {fontSize}pt;'
		openTag += f'color: rgb({fontColorValues[0]}, {fontColorValues[1]}, {fontColorValues[2]});'

		if fontStrikeThrough:
			openTag += 'text-decoration: line-through;'

		if fontUnderline:
			openTag += 'text-decoration: underline;'

		if fontPosition == 2:
			openTag += 'vertical-align: super;'

		if fontPosition == 3:
			openTag += 'vertical-align: sub;'

		if fontWeight >= 700:
			openTag += '<b>'
			closeTag += '</b>'

		if fontItalic:
			openTag += '<i>'
			closeTag += '</i>'

		openTag += '>'
		closeTag += '</span>'

		return [openTag, closeTag]

	def main():
		#Prompt user to select an Excel file
		inFile = comet.selectFile('Select an Excel file', suggestion = '$DESKTOP')
		if not inFile:
			return 0

		#Open the selected Excel file
		ebook = comet.CExcelBook.open(inFile)
		sheet = ''
		firstRow = ebook.getFirstRow('')
		lastRow = ebook.getLastRow('')
		firstColumn = ebook.getFirstColumn('')
		lastColumn = ebook.getLastColumn('')

		print(f'First row: {firstRow}')
		print(f'Last row: {lastRow}')
		print(f'First column: {firstColumn}')
		print(f'Last column: {lastColumn}')

		#Prepare the data output
		comet.gFrame.replace('')
		completeData = '%!TT_html_'

		for row in range(firstRow, lastRow):
			for column in range(firstColumn, lastColumn):
				colStr = comet.CExcelBook.indexToColumn(column)
				cellValue = f'[{colStr}{row + 1}] : '
				openTag, closeTag = getCellStyle(ebook, sheet, column, row)

				cellValue += openTag

				try:
					value = ebook.read(sheet, column, row)
					cellType = ebook.getCellType(sheet, column, row)

					#Handle datetime values
					if cellType == comet.excel.kCellTypeDateTime:
						comet.wlog(f'Read cell datetime content [{row + 1}, {colStr}]: {value}')

						tempTime = datetime.datetime.now()

						try:
							tempTime = datetime.datetime.strptime(value, '%Y%m%d%H%M%S')
						except:
							tempTime = datetime.datetime(*(time.strptime(value, '%Y%m%d%H%M%S')[0:6]))

						value = tempTime.strftime('%m.%d.%Y, %H:%M:%S')
					else:
						comet.wlog(f'Read cell content [{row + 1}, {colStr}]: {value}')

					cellValue += str(value)

				except BaseException as e:
					comet.wlog(f'Exception at [{row + 1}, {colStr}]: {str(e)}')

				#Retrieve cell format
				try:
					formatID = 0
					formatStr = ''
					formatID, formatStr = ebook.getCellFormat(sheet, column, row)
					if formatID > 0:
						cellValue += f' / Format {formatID}'
					
					if formatStr:
						cellValue += f' <span class="format">{formatStr}</span>'
				except:
					pass

				#Retrieve cell formula if available
				try:
					formula = ebook.getCellFormula(sheet, column, row)
					if formula:
						cellValue += f' <span class="formula">{formula}</span>'
				except:
					pass

				cellValue += closeTag
				completeData += cellValue
				completeData += '<br/>'

			completeData += '<br/>'

		#Log and update the output frame
		comet.wlog(completeData)
		comet.gFrame.replace(completeData)

		#Close the Excel file
		ebook.close()

		return 0