Error on saving multiple sheets

[bh-schmidt]

Can we save multiple worksheets in the same memory stream?

I am receiving the following exception:

NPOI.OpenXml4Net.Exceptions.OpenXML4NetRuntimeException: Fail to save: an error occurs while saving the package : Must support writing (Parameter 'baseOutputStream')
 ---> System.ArgumentException: Must support writing (Parameter 'baseOutputStream')
   at ICSharpCode.SharpZipLib.Zip.Compression.Streams.DeflaterOutputStream..ctor(Stream baseOutputStream, Deflater deflater, Int32 bufferSize)
   at ICSharpCode.SharpZipLib.Zip.Compression.Streams.DeflaterOutputStream..ctor(Stream baseOutputStream, Deflater deflater)
   at ICSharpCode.SharpZipLib.Zip.ZipOutputStream..ctor(Stream baseOutputStream)
   at NPOI.OpenXml4Net.OPC.ZipPackage.SaveImpl(Stream outputStream)
   --- End of inner exception stack trace ---
   at NPOI.OpenXml4Net.OPC.ZipPackage.SaveImpl(Stream outputStream)
   at NPOI.OpenXml4Net.OPC.OPCPackage.Save(Stream outputStream)
   at NPOI.POIXMLDocument.Write(Stream stream)
   at Ganss.Excel.ExcelMapper.Save[T](Stream stream, IEnumerable`1 objects, String sheetName, Boolean xlsx, Func`3 valueConverter)

Code:

var versions= new List<Version>() { new Version { MyProperty = 1 } };
var portfolios = new List<Portfolio>() { new Portfolio { MyProperty = 1 } };

var mapper = new ExcelMapper();
var stream = new MemoryStream();

mapper.Save(stream, versions, "Versions");
mapper.Save(stream, portfolios, "Portfolios");

var bytes = stream.ToArray();

The exception is thrown at

mapper.Save(stream, portfolios, "Portfolios");

[mganss]

Try writing to Stream.Null on all calls to Save() except the last one. The internal workbook object will be updated on each call but only the last call to Save() will actually write the whole workbook to the target stream.

[bh-schmidt]

Nice, it worked! Thanks for the help!
I would like to implement a method AddSheet(IEnumerable objects, string sheetName) that does the same, just to be easier to use, do you agree with that @mganss?

[mganss]

@bh-schmidt Generally, yes. But I wouldn't use the Stream.Null hack but rather refactor the core Save() method to not call Workbook.Write() at the end:

ExcelMapper/ExcelMapper/ExcelMapper.cs

Lines 1026 to 1069 in 2d36c9b

	void Save<T>(Stream stream, ISheet sheet, IEnumerable<T> objects, Func<string, object, object> valueConverter = null)
	{
	var firstObject = objects.FirstOrDefault();
	var typeMapper = firstObject is ExpandoObject ? TypeMapperFactory.Create(firstObject) : TypeMapperFactory.Create(typeof(T));
	var columnsByIndex = typeMapper.ColumnsByIndex;
	var i = MinRowNumber;
	columnsByIndex = columnsByIndex.Where(kvp => !kvp.Value.All(ci => ci.Directions == MappingDirections.ExcelToObject))
	.ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
	GetColumns(sheet, typeMapper, columnsByIndex);
	SetColumnStyles(sheet, columnsByIndex);
	foreach (var o in objects)
	{
	if (i > MaxRowNumber) break;
	if (HeaderRow && i == HeaderRowNumber)
	i++;
	var row = sheet.GetRow(i);
	if (row == null) row = sheet.CreateRow(i);
	SetCells(typeMapper, columnsByIndex, o, row, valueConverter);
	i++;
	}
	if (SkipBlankRows)
	{
	while (i <= sheet.LastRowNum && i <= MaxRowNumber)
	{
	var row = sheet.GetRow(i);
	while (row.Cells.Any())
	row.RemoveCell(row.GetCell(row.FirstCellNum));
	i++;
	}
	}
	Saving?.Invoke(this, new SavingEventArgs(sheet));
	Workbook.Write(stream);
	}