#!/usr/bin/python3

"""
XLSX spreadsheet converter
Opens a source XLSX workbook and saves the data in a csv file.
Requires Python >= 3.9, and the xlrd and openpyxl module versions as listed in requirements.txt
See cases 8299, 12536, 12537, 12634 for history
See Phabricator project/tag "xlsx2csv" for current work
"""

import os
import sys
import xlrd # For .xls files
import openpyxl # For .xlsx files
import csv
import datetime
import logging
import re
import codecs
import argparse # Yeah, finally
import time
import zipfile

def buildDBName(fileName, sheetName):
	fRoot, fNameRoot = os.path.split(fileName)
	fName, fExt = os.path.splitext(fNameRoot)
	
	logging.info(f'Creating filename based on sheet name: {sheetName}')
	
	unnamedSheet = re.compile(r'^Sheet[\d+]$')
	invalidSheetname = re.compile(r'^COM[\d*]$')
	if(unnamedSheet.match(sheetName)):
		return fName + '_' + sheetName[5:] + '.csv'
	elif invalidSheetname.match(sheetName):
		logging.warning('Sheet '+sheetName+' has an invalid name for a file. Prefixing with \'TABLENAME-\'...')
		return 'TABLENAME-'+sheetName+'.csv'
	else:
		return sheetName + '.csv'
	
def convertFile(f, folder, options):
	convertedDBs = 0
	
	floatRound = re.compile(r'^[0-9]+\.0$')
	
	# Previously we were assuming file extensions are the Word of God like any good Windows user
	ex = os.path.splitext(f)[-1].lower()
	eo = ex.lstrip(".")
	# However, some people save .xlsx files as .xlsx, so we need to truly check
	ff = xlrd.inspect_format(f)
	if eo != ff:
		logging.warning(f"File extension {ex} does not match detected filetype {ff}, trusting detection instead")
		ex = "."+ff
	OPENPYXL_SUPPORTED_FORMATS = ('.xlsx', '.xlsm', '.xltx', '.xltm')
	
	if ex == ".xls":
		logging.info(f"Opening {f} using xlrd.open_workbook()")
		openstart = time.time()
		wb = xlrd.open_workbook(f)
		openend = time.time()
		openelapsed = round(openend - openstart)
		logging.info(f"Opening workbook took ~{openelapsed} seconds")

		for sheetName in wb.sheet_names():
			sheet = wb.sheet_by_name(sheetName)
			logging.info("Processing sheet:" + sheetName + "[" + str(sheet.nrows) + "," + str(sheet.ncols) + "]")

			# Read the data from each sheet into sheetData
			sheetData = []
			for row in range(0, sheet.nrows):
				rowData = []
				for cell in range(0, sheet.ncols):
					c = sheet.cell(row, cell)
					# First check for XL_CELL_ERROR and append nothing if so and complain about it; see http://xlrd.readthedocs.io/en/latest/api.html#xlrd.sheet.Cell
					if c.ctype == 5: # error
						logging.error("Error on cell: "+xlrd.error_text_from_code[c.value])
						rowData.append("")
					# Then for some other types try and handle them
					elif c.ctype == 3: # date
						try:
							y, m, d, hour, min, sec = xlrd.xldate_as_tuple(c.value, wb.datemode)
							x = datetime.datetime(y, m, d, hour, min, sec)
							rowData.append(x.strftime('%Y-%m-%d %H:%M:%S'))
						except Exception:
							rowData.append("Invalid Date")
					elif c.ctype == 2: # float
						val = str(c.value)
						if floatRound.match(val):
							val = val[:-2]
						rowData.append(val)
					else:
						v = c.value
						if row == 0:
							try:
								v = v.upper()
							except Exception:
								logging.error("Attempted to uppercase cell under assumption that it was a column header, but failed!")
						rowData.append(v)
				# If every cell is empty, skip this row; see T1934. Maybe better done before but xlrd confuses me a bit sometimes tbqh. Reworked further in T1983 to fix issues.
				if all(parsedCell is None for parsedCell in rowData):
					logging.debug("Skipping blank row")
					continue
				sheetData.append(rowData)
			#logging.debug(sheetData[0:5])
			# CSV output file
			outFile = buildDBName(f, sheetName)
			if(not outFile):
				logging.critical("Somehow was unable to determine an output filename? Will not write out data!")
				continue
			outFile = os.path.join(folder, outFile)

			# Output the data to CSV
			if len(sheetData)>0:
				convertedDBs += 1
				logging.info('Will write '+str(len(sheetData))+' records to '+outFile)
				# see https://docs.python.org/3/library/csv.html#id3 for "newline=''" rationale
				with open(outFile, "w", encoding="utf-16", newline='') as outFileHandle:
					writer = csv.writer(outFileHandle)
					writer.writerows(sheetData)
					logging.info(f"Wrote data, encoded as utf-16 (apologies)")

	elif ex in OPENPYXL_SUPPORTED_FORMATS: # If it wasn't .xls, check against the same list of file extensions openpyxl does, and run if so
		logging.info(f"Using openpyxl for presumably-xlsx file {f}")
		# Open file as an object to trick openpyxl into actually trusting us rather than following its faith in file extensions; see T1995. Also works around openpyxl's need to open files with read_only=False to be able to detect cell formats.
		logging.info(f"Opening file read-only in Python open()")
		fo = open(f, mode="rb")
		
		# If we're forcing (or theoretically forcing off) what Keith is calling "chunk mode", no need to parse further
		if options["openpyxl-chunk-mode"] is None:
			# openpyxl needs to use write mode to see if there are links, but we don't want to use it due to huge RAM usage if there aren't links, so we have to test it *before* passing to openpyxl
			# As of this writing, openpyxl does not support xlsb, so any file we're parsing here should be just a zip of XML files in folders, we'll use that
			with zipfile.ZipFile(fo) as xlsxzip:
				xlsx_file_list = xlsxzip.namelist()
				logging.debug(xlsx_file_list)
				if any(path.startswith('xl/worksheets/_rels') for path in xlsx_file_list):
					chunkmode = False
					logging.warning("Found directory inside XLSX file theorized to contain links, forcing off openpyxl read-only chunk mode")
				else:
					chunkmode = True
					logging.info("Did not find directory inside XLSX file theorized to contain links, using potentialy slower but much RAM-lighter chunk mode")
		else:
			chunkmode = options["openpyxl-chunk-mode"]
			logging.info(f"Respecting supplied override setting of chunkmode: {chunkmode}")
		
		
		logging.info(f"Opening workbook with openpyxl using read_only={chunkmode}")
		openstart = time.time()
		wb = openpyxl.load_workbook(fo, read_only=chunkmode)
		openend = time.time()
		openelapsed = round(openend - openstart)
		logging.info(f"Opening workbook with took ~{openelapsed} seconds")

		for sheetName in wb.sheetnames:
			sheet = wb[sheetName]
			logging.info("Processing sheet: " + sheetName)

			# Read the data from each sheet into sheetData
			sheetData = []
			for row in sheet.iter_rows():
				logging.debug(row)
				# If every cell is empty, skip this row; see T1934
				if all(cell.value is None for cell in row):
					logging.info("Skipping blank row")
					continue
				rowData = []
				for cell in row:
					logging.debug(cell)
					# Should maybe check for errors? Not doing so yet though.
					if isinstance(cell, openpyxl.cell.cell.MergedCell):
						logging.debug("Skipping MergedCell, a type of cell that is only for displays and has no actual value (in a literal sense)")
					elif cell.is_date:
						x = cell.value
						logging.debug(x)
						try:
							rowData.append(x.strftime('%Y-%m-%d %H:%M:%S'))
						except:
							logging.warning(f"Row {cell.row} Column {cell.column} claims to be a date, but the value resisted date reformatting. Raw value used instead: {x}")
							rowData.append(x)
					elif (not chunkmode) and cell.hyperlink:
						# Format using what I think is the Excel standard formatting for hyperlinks
						logging.info("it's a hyperlink")
						rowData.append('=HYPERLINK("{}", "{}")'.format(cell.hyperlink.target, cell.value))
					# There are other data types, but it wasn't immediately obvious to keith how to check for them
					else:
						v = cell.value
						if type(v) is str:
							v = v.strip()
						rowData.append(v)
				sheetData.append(rowData)
			try:
				sheetData[0] = [v.upper() for v in sheetData[0]]
			except Exception:
				logging.error("Attempted to uppercase first row under assumption that it is a header row, but failed!")
			# CSV output file
			outFile = buildDBName(f, sheetName)
			if(not outFile):
				continue
			outFile = os.path.join(folder, outFile)
			#logging.debug(outFile)

			# Output the data to CSV
			if len(sheetData)>0:
				convertedDBs += 1
				logging.info('Will write '+str(len(sheetData))+' records to '+outFile)
				# see https://docs.python.org/3/library/csv.html#id3 for "newline=''" rationale
				with open(outFile, "w", encoding="utf-16", newline='') as outFileHandle:
					writer = csv.writer(outFileHandle)
					writer.writerows(sheetData)
					logging.info(f"Wrote data, encoded as utf-16 (apologies)")
		wb.close()
		logging.info("Closed openpyxl workbook")
		fo.close()
		logging.info("Closed file")

	else:
		logging.error(f"Did not know how to handle {os,path(f)}, detected filetype {ff}, sorry!")
	
	return convertedDBs


def main():
	start = time.time()
	codepage = os.environ.get("DBDOC_CODEPAGE", "utf-8")
	sys.stdout = codecs.getwriter(codepage)(sys.stdout.detach())
	
	helptxt = 'Script to convert an xlsx or xlsx file into csv files (one per sheet). Opened read-only, and then passed to a Python library to process (either xlrd for XLS, or openpyxl for XLSX files).'
	argparser = argparse.ArgumentParser(description=helptxt)
	argparser.add_argument("filename", help="Path to xls or xlsx file")
	argparser.add_argument("folder", help="Optional output folder", nargs='?', default='.')
	argparser.add_argument("--openpyxl-chunk-mode", help="Force-toggle use of openpyxl's read-only mode, which reads data in chunks. This will use FAR less memory when reading XLSX files, but will miss data Excel stores internally at the end of sheets (notably hyperlinks). Sans this option, an attempt will be made to check for links and set the mode accordingly.", action=argparse.BooleanOptionalAction)
	argparser.add_argument('--loglevel', choices=['DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL'], default="WARNING")
	args = argparser.parse_args()
	# TODO this more dynamically
	filename = args.filename
	folder = args.folder
	options = {"openpyxl-chunk-mode": args.openpyxl_chunk_mode}
	
	logFormatter = logging.Formatter("%(message)s")
	rootLogger = logging.getLogger()
	rootLogger.setLevel(logging.getLevelName(args.loglevel))
	fileHandler = logging.FileHandler("xlsx2csv.err", mode="w", encoding="utf-8")
	fileHandler.setFormatter(logFormatter)
	# fileHandler.setLevel(logging.ERROR)
	rootLogger.addHandler(fileHandler)
	consoleHandler = logging.StreamHandler(sys.stdout)
	consoleHandler.setFormatter(logFormatter)
	# consoleHandler.setLevel(logging.ERROR)
	rootLogger.addHandler(consoleHandler)
	
	argc = len(sys.argv)
	if argc<2:
		# Invalid number of arguments
		return -4
	if sys.argv[1] in ("--help", "-h"):
		# We asked for help, now declaring that a "Code 3"
		return -3

	try:
		logging.info('Processing '+filename)
		n = convertFile(filename, folder, options)
		end = time.time()
		elapsed = round(end - start)
		logging.info(f'Finished processing file! Took ~{elapsed} seconds.')
	except Exception as e:
		logging.critical('Converting Database Failed:')
		logging.critical('\t'+str(e))
		
		end = time.time()
		elapsed = round(end - start)
		logging.error(f'Failed after ~{elapsed} seconds.')
		
		logging.info("Using the following modules:")
		logging.info(xlrd.__file__)
		logging.info(openpyxl.__file__)
		
		logging.debug("Full trace:")
		exc_type, exc_obj, exc_tb = sys.exc_info()
		fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
		logging.debug(exc_type, fname, exc_tb.tb_lineno)
		
		return -1
	except KeyboardInterrupt:
		end = time.time()
		elapsed = round(end - start)
		logging.error(f'User cancelled after ~{elapsed} seconds.')
		return -2

if __name__ == '__main__':
	sys.exit(main())
