streamingXlsx does not seem to be streaming

[segueRanger]

Hello folks,
I am trying to use this tool to do a streamingXlsx, but it just keeps eating up the heap and bombing or timing out, and I can't see that setting window size or anything else is having any effect - it just seems the same as trying to write normally. Am I missing some intermediate step to flush these rows out of memory and have them written to disk?
Thanks for any help anyone can provide!

[segueRanger]

I think I figured out that the streaming part only happens when you use addRows() and not if you're looping through addRow(). If there's a way around that, would love to know because I was trying to format the row before it got written away out of the windowSize.

[cfsimplicity]

The trade-off with streaming is that once the rows are flushed to disk they are no longer accessible. That means you can't format or otherwise manipulate added rows once the streamingWindowSize has been reached.

A workaround is to add the rows in batches, formatting as you go before they get flushed. Here's a basic example:

spreadsheet = New spreadsheet.Spreadsheet();
testFilePath = ExpandPath( "streamingWithFormatting.xlsx" );
// generate a simple 1 column array of data
numberOfRows = 10000;
data = [];
cfloop( from=1, to=numberOfRows, index="i" ){
	row = [ "row" & i ];
	data.Append( row );
}
dataLength = data.Len();
// create an empty streaming workbook
streamingWindowSize = 100;
workbook = spreadsheet.newStreamingXlsx( streamingWindowSize=streamingWindowSize );
//add the data in batches matching the streaming window size
dataBatch = [];
cfloop( from=1, to=dataLength, index="currentRow" ){
	dataBatch.Append( data[ currentRow ] );
	// when the window size is reached add them to the spreadsheet and format them before they are flushed
	if( ( currentRow % streamingWindowSize ) == 0 ){
		startRow = ( ( currentRow - streamingWindowSize ) +1 );
		endRow = ( currentRow + streamingWindowSize );
		spreadsheet.addRows( workbook, dataBatch );
		spreadsheet.formatRows( workbook, { bold: true }, "#startRow#-#endRow#" );
		//reset batch
		dataBatch = [];
	}
}
spreadsheet.write( workbook, testFilePath, true );

[segueRanger]

I appreciate the response and the suggestion. That may work. Unfortunately, because we are trying to format a bunch of different tabs with the same formats up at the top with summary data, then have all the real meat of the output starting at row 4 on every tab, I think this means I would need to use a window size of 3 so that I could go through each tab and create those top sections first. I will play around with this idea and see what we can do. Thanks again!

[cfsimplicity]

Let me know how your experiments go, but my simple attempt to replicate your use case seems to work using the default 100 row window.

spreadsheet = New spreadsheet.Spreadsheet();
testFilePath = ExpandPath( "streamingWithFormatting.xlsx" );
// generate a simple 1 column array of data
numberOfRows = 1000;
data = [];
cfloop( from=1, to=numberOfRows, index="i" ){
	row = [ "row" & i ];
	data.Append( row );
}
//define functions
function createHeading( required workbook ){
	heading = [ [ "Heading on row 1" ], [ "Heading on row 2" ], [ "Heading on row 3" ] ];
	spreadsheet
		.addRows( arguments.workbook, heading )
		.formatRows( arguments.workbook, { italic: true }, "1-3" );	
}
function addAndFormatDataInBatches( required workbook, required data, required streamingWindowSize ){
	dataLength = arguments.data.Len();
	dataBatch = [];
	cfloop( from=1, to=dataLength, index="currentRow" ){
		dataBatch.Append( arguments.data[ currentRow ] );
		// when the window size is reached add them to the spreadsheet and format them before they are flushed
		if( ( currentRow % arguments.streamingWindowSize ) == 0 ){
			startRow = ( ( currentRow - arguments.streamingWindowSize ) +1 );
			endRow = ( currentRow + arguments.streamingWindowSize );
			spreadsheet
				.addRows( arguments.workbook, dataBatch )
				.formatRows( arguments.workbook, { bold: true }, "#startRow#-#endRow#" );
			//reset
			dataBatch = [];
		}
	}	
}
// create an empty streaming workbook...
streamingWindowSize = 100;
workbook = spreadsheet.newStreamingXlsx( streamingWindowSize=streamingWindowSize );
//...with 2 sheets
spreadsheet.createSheet( workbook );
//create heading on sheet1
createHeading( workbook );
// and sheet2
spreadsheet.setActiveSheetNumber( workbook, 2 );
createHeading( workbook );
//add the data to sheet1
spreadsheet.setActiveSheetNumber( workbook, 1 );
addAndFormatDataInBatches( workbook, data, streamingWindowSize );
//add the data to sheet2
spreadsheet.setActiveSheetNumber( workbook, 2 );
addAndFormatDataInBatches( workbook, data, streamingWindowSize );
spreadsheet.write( workbook, testFilePath, true );

[segueRanger]

thanks for the suggestions. I wound up just using the cf version because it produced what I needed very quickly, and for whatever reason, I couldn't get this one to work at all, it would just time out. I believe there was an error in the background that I wasn't able to see because of our framework, but I do appreciate your help and I will give this another shot when I'm not in such a time crunch. :-)