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
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)
}
}