Hi All,

We're doing our first babysteps with embedded Python and IRIS with an interoperability solution. We want to convert a CSV file to an Excel xlt file.

As service we've got a EnsLib.File.PassthroughService which picks up a csv file

As operation we've got a EnsLib.File.PassthroughOperation which writes the Excel file.

In the middle:

We've created an Business Process with an %Stream.GlobalCharacter in the Request and a %Stream.GlobalCharacter  in the Response.

But how do we get the %Stream.GlobalCharacter in the Python ClassMethod? Ideally I don't would like to write the stream first to a temp file, give the temp file to Python and let Python write the XLT file and read that XLT file into a %Stream.GlobalCharacter.

Any ideas? See the code snipped below. This one works but will first create a string from the Stream but will fail when the csv is bigger then the Intersystems string limit

Class ZORG.BP.CsvToExcel Extends Ens.BusinessProcess
{

Method OnRequest(pRequest As ZORG.BP.CsvToExcel.CsvToExcelReq, Output pResponse As ZORG.BP.CsvToExcel.CsvToExcelResp) As %Status
{
    Set tsc = $$$OK
	; Create the response class
	Set pResponse = ##class(ZORG.BP.CsvToExcel.CsvToExcelResp).%New()
	Set pResponse.outputStream = ##class(%Stream.GlobalCharacter).%New()
	
	$$$TRACE("Size of Stream: "_pRequest.inputStream.SizeGet())
    Set XlsString=..convertCSVtoXLS(pRequest.inputStream.Read($$$MaxStringLength))
	Do pResponse.outputStream.Write(XlsString)
	
	return tsc
}

ClassMethod convertCSVtoXLS(csvFile As %String(MAXLEN="")) As %String [ Language = python ]
{

	"""
    This function takes a CSV string as input and converts it into an XLS string.
    
    Parameters:
    csv_string (str): The CSV string to be converted
    
    Returns:
    str: Th
	"""
	import pandas
	import xlwt
	import sys
	import csv
	import tempfile
	from io import StringIO
	
	# Read the CSVdata into a Pandas DataFrame
	
	df = pandas.read_csv(StringIO(csvFile), delimiter=";", decimal=",", dtype={'patno':str} )	
	# Create an instance of Workbook class from xlwt library	
	workbook = xlwt.Workbook()

    # Add a sheet to the workbook
	sheet = workbook.add_sheet('Sheet1')

    # Write the DataFrame values to the sheet
	for i, column in enumerate(df.columns):
		sheet.write(0, i, column)  # Write column headers
		for j, value in enumerate(df[column]):
			if ( str(value) != 'nan' ):       #catch empty cells
				sheet.write(j + 1, i, value)  # Write cell values

	# Write workbook to temporary file
	file = tempfile.TemporaryFile()
	
	# Save Workbook
	workbook.save(file)
	
	#Go to the beginning of the file
	file.seek(0)

	#Read from temporary File and return
	data = file.read()
	return(data)
}
}