Error on saving multiple sheets

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");

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.

@mganss

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?

@bh-schmidt

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

Navigation Menu

Repository navigation

Error on saving multiple sheets #209

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

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

Error on saving multiple sheets #209

Description

Activity

mganss commented on Aug 17, 2022

bh-schmidt commented on Aug 17, 2022

mganss commented on Aug 18, 2022

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions