System.IO.IOException: Stream was too long.

[rferraton]

Excel Type

• XLSX
• XLSM
• CSV
• OTHER

Description

Problem when trying to insert many data 900k rows x 72 columns

Receive the following message :

System.IO.IOException: Stream was too long.
   at System.IO.MemoryStream.Write(ReadOnlySpan`1 buffer)
   at System.IO.StreamWriter.Flush(Boolean flushStream, Boolean flushEncoder)
   at System.IO.StreamWriter.Write(String value)
   at MiniExcelLibs.OpenXml.ExcelOpenXmlSheetWriter.WriteCell(MiniExcelStreamWriter writer, Int32 rowIndex, Int32 cellIndex, Object value, ExcelColumnInfo columnInfo, ExcelWidthCollection widthCollection)
   at MiniExcelLibs.OpenXml.ExcelOpenXmlSheetWriter.WriteValues(MiniExcelStreamWriter writer, Object values)
   at MiniExcelLibs.OpenXml.ExcelOpenXmlSheetWriter.CreateSheetXml(Object values, String sheetPath)
   at MiniExcelLibs.OpenXml.ExcelOpenXmlSheetWriter.Insert(Boolean overwriteSheet)
   at MiniExcelLibs.MiniExcel.Insert(String path, Object value, String sheetName, ExcelType excelType, IConfiguration configuration, Boolean printHeader, Boolean overwriteSheet)
   at FastBCP.Transfer.DataToExcel(ParsingArguments p, TypeCode[] typeCodes, String connectionType, String _connectionString, String presql, String inputsql, String filePath, String fileName, Encoding encode, NumberFormatInfo nfi, String dateformat, Boolean useQuoteIdentifier, String delimiter, String boolformat, String headerline)

Would it be possible to use a stream buffer (let's say 10MB) ?

[michelebastione]

@rferraton where you inserting from an IDataReader?

[rferraton]

@michelebastione : yes

[michelebastione]

Are you running your application in a 32 bit process? If so would it be feasible for you to switch to a 64 bit process and let us know whether the same error occurs? If so or if you're already running in a 64 bit process could you provide a sample/mock of your data? One row should suffice. By the way what version of .NET are you using?

Edit: I forgot to mention the trivial answer but I assume you've already tried it, anyways you can increase the stream buffer by using the configuration property BufferSize (the size is in byte)

[rferraton]

We use 64bits process.

I can provide a link to a mssql backup if you want ?

if you prefer DDL (MSSQL) :

CREATE TABLE TEST_71_1M_12m(
	NIV_AGREGAT char(32) NULL,
	ID_SOCIETE int NULL,
	DT_PERIODE datetime NULL,
	TYPE_CA char(1) NULL,
	ID_DIR_VENTES_CS int NULL,
	ID_DIR_REGION_CS int NULL,
	ID_CHEF_SECTEUR int NULL,
	ID_DIR_REGION_PROMOT int NULL,
	ID_PROMOTEUR int NULL,
	ID_ARTICLE int NULL,
	ID_TYPE_ARTICLE int NULL,
	ID_SUPER_MARQUE int NULL,
	ID_MARQUE int NULL,
	ID_SEGMENT int NULL,
	ID_SUPER_FAMILLE int NULL,
	ID_FAMILLE int NULL,
	ID_SOUS_FAMILLE int NULL,
	ID_FORMAT int NULL,
	ID_TYPE_FORMAT int NULL,
	ID_GAMME int NULL,
	ID_SUPER_CENTRALE int NULL,
	ID_CENTRALE int NULL,
	ID_ENSEIGNE int NULL,
	ID_TYPOLOGIE int NULL,
	ID_CLIENT int NULL,
	ID_PAYS int NULL,
	ID_DIVISION int NULL,
	ID_SOUS_DIVISION int NULL,
	ID_PROFESSION int NULL,
	ID_TYPE_CLIENT int NULL,
	ID_TYPE_MAGASIN int NULL,
	ID_DECOUPE_GEO int NULL,
	ID_DEC_REG_ENS int NULL,
	ID_NATION_EXPORT int NULL,
	CA_MM0_CNS decimal(16, 4) NULL,
	CA_MM0_SOC decimal(16, 4) NULL,
	CA_MM1_CNS decimal(16, 4) NULL,
	CA_MM1_SOC decimal(16, 4) NULL,
	CA_MM2_CNS decimal(16, 4) NULL,
	CA_MM2_SOC decimal(16, 4) NULL,
	QTL_MM0 decimal(16, 4) NULL,
	QTL_MM1 decimal(16, 4) NULL,
	QTL_MM2 decimal(16, 4) NULL,
	QTF_MM0 decimal(16, 4) NULL,
	QTF_MM1 decimal(16, 4) NULL,
	QTF_MM2 decimal(16, 4) NULL,
	VLL_MM0 decimal(16, 4) NULL,
	VLL_MM1 decimal(16, 4) NULL,
	VLL_MM2 decimal(16, 4) NULL,
	VLF_MM0 decimal(16, 4) NULL,
	VLF_MM1 decimal(16, 4) NULL,
	VLF_MM2 decimal(16, 4) NULL,
	VC_MM0_CNS decimal(16, 4) NULL,
	VC_MM0_SOC decimal(16, 4) NULL,
	VC_MM1_CNS decimal(16, 4) NULL,
	VC_MM1_SOC decimal(16, 4) NULL,
	VC_MM2_CNS decimal(16, 4) NULL,
	VC_MM2_SOC decimal(16, 4) NULL,
	VR_MM0_CNS decimal(16, 4) NULL,
	VR_MM0_SOC decimal(16, 4) NULL,
	VR_MM1_CNS decimal(16, 4) NULL,
	VR_MM1_SOC decimal(16, 4) NULL,
	VR_MM2_CNS decimal(16, 4) NULL,
	VR_MM2_SOC decimal(16, 4) NULL,
	VB_MM0_CNS decimal(16, 4) NULL,
	VB_MM0_SOC decimal(16, 4) NULL,
	VB_MM1_CNS decimal(16, 4) NULL,
	VB_MM1_SOC decimal(16, 4) NULL,
	VB_MM2_CNS decimal(16, 4) NULL,
	VB_MM2_SOC decimal(16, 4) NULL,
	DT_CALCUL_AGREG datetime NULL,
	DT_REF_AGREG datetime NULL
) ;

And a 10 rows sample in CSV (My table is 1M rows)

FastExport_10rows.csv

Currently i set a BufferSize of 4MB`to avoid high memory usage. I was thinking the buffer is flushed when full and continue. Am i wrong ?

var config = new OpenXmlConfiguration();
config.BufferSize = 1024 * 1024 * 4;
config.FastMode = true;

[michelebastione]

I think the culprit is the FastMode, I'm not too familiar with its inner workings but I think it might be specifically avoiding to flush and wait until the very end of the process to write on disk in order to save as much time as possible. I will keep investingating, in the meantime if you disable FastMode the problem should go away.

[izanhzh]

It seems to be a problem with the ZipArchive Update mode. Maybe the Insert Sheet function is no longer suitable for large-scale data insertion. SaveAs can only be used unless other solutions are found

• System.IO.Compression: ZipArchive loads entire file in memory on .Dispose dotnet/runtime#1543
• System.IO.Compression: ZipArchiveEntry always stores uncompressed data in memory dotnet/runtime#1544
• Add support for adding new entries to large zip archives dotnet/runtime#49149
• Reduce memory usage when updating ZipArchives dotnet/runtime#102704

When ZipArchiveMode.Update is present, the size limit of an entry is limited to Int32.MaxValue. This limit is because update mode uses a MemoryStream internally to allow the seeking required when updating an archive, and MemoryStream has a maximum equal to the size of an int.

https://learn.microsoft.com/en-us/dotnet/api/system.io.compression.zipfileextensions.createentryfromfile?view=netcore-3.1#System_IO_Compression_ZipFileExtensions_CreateEntryFromFile_System_IO_Compression_ZipArchive_System_String_System_String_System_IO_Compression_CompressionLevel

[michelebastione]

Yeah I was reading about that yesterday and I was afraid it could be because of this issue. Silly me suggesting to disable FastMode when in order to use Insert it needs to be enabled, I don't know what I was thinking. Editing the original document becomes unfeasible in this case, at least until the runtime team decides to change this behavior. I wouldn't necessarily worry about it too much but we should come up with some sort of workaround for massive insertions like this one.

[rferraton]

We have another message when desactivating Fastmode Message : Auto width requires fast mode to be enabled When déactivating autowidth we have message : Message : Insert requires fast mode to be enabled Insert Mode is required for us because we don't want to overwrite existing data in an existing Excel file. So it seams we cannot disable FastMode. Can you provide another solution (disable autowidth is acceptable but disable insert is a problem) Nota 1 : we try to increase the buffersize to 512MB without success when having 1Mrows x 71c Nota 2 : it work when using 100k rows x 71 c (even with a 32M buffer size or less) May be the buffersize parameter is not used and a constant is use somewhere instead ? Edit : I just read the previous messages and understand that the [MemoryStream](https://learn.microsoft.com/en-us/dotnet/api/system.io.memorystream?view=netcore-3.1) int limit is the root cause. Maybe using a FileStream could be better if possible ??

…

________________________________ De : Michele Bastione ***@***.***> Envoyé : jeudi 5 juin 2025 17:52 À : mini-software/MiniExcel ***@***.***> Cc : Romain FERRATON ***@***.***>; Mention ***@***.***> Objet : Re: [mini-software/MiniExcel] System.IO.IOException: Stream was too long. (Issue #793) [https://avatars.githubusercontent.com/u/50582333?s=20&v=4]michelebastione left a comment (mini-software/MiniExcel#793)<#793 (comment)> Yeah I was reading about that yesterday and I was afraid it could be because of this issue. Silly me suggesting to disable FastMode when in order to use Insert it needs to be enabled, I don't know what I was thinking. Editing the original document becomes unfeasible in this case, at least until the runtime team decides to change this behavior. I wouldn't necessarily worry about it too much but we should come up with some sort of workaround for massive insertions like this one. — Reply to this email directly, view it on GitHub<#793 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AD5IUX5PZCMZ5QPLO3V34I33CBRVNAVCNFSM6AAAAAB6PB7KTGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDSNBVGA3TIOBYGA>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[michelebastione]

@rferraton We do use filestreams, the problem here is that if you try to open a zip archive entry in update mode, which is what we do to insert a new sheet, the runtime library System.IO.Compression overrides it and writes the modifications into a new MemoryStream before flushing on disk only at the very end, presumably to avoid file corruption. This is impossible to avoid directly but I do have an idea for a workaround, even though it's pretty tricky, so despite being quite confident enough that it should work it might take some time to implement and I cannot give any guarantees of success.

[rferraton]

OK, got it.
@michelebastione good luck !

[michelebastione]

Hello @rferraton. I apologize for not giving any updates for such a long time regarding this issue. As you might have noticed the project has been evolving a lot during the past year; we've been addressing many bugs, adding some new features and working on an upcoming v2.0 (which has been in preview for some time and is almost ready to be fully released).

I'm sorry if it frustrated you to see no progress being made here, but I promise I tried multiple times to tackle it. Unfortunately as it happens, every approach I experimented with ended up generating one or more side effect that would completely break some other part of the project, and for the longest time I couldn't figure out how to untangle the knot.

Finally, during the past week or so I put a huge amount of effort into finding a solution and I think I managed to do so, I tested it and I believe I covered all edge cases and closed all possible loose ends. I will soon make a PR that will close the issue, and the feature will be out in the next v2 preview release. I don't know if we'll be able to backport it to version 1.x given the amount of maintenance required compared to its general usefulness, but we'll look into it further down the road.

Apologies for the rambling, I don't even know if this is an issue that you're actually still interested in having resolved, but I was so relieved to finally being able to clear it from the backlog after it bugged me for so long I had to talk about it 😅.

If you could please, once the PR is merged, build the project and test it for your real world scenario to make sure everything is alright, it would be a big helping hand!

[rferraton]

@michelebastione : thanks a lot! i will test it.

Any sample with a IDataReader and a Stream ?

[michelebastione]

Sure:

await using var conn = GetConnection(); 
await conn.OpenAsync(); 

await using var cmd = conn.CreateCommand(); 
cmd.CommandText = yourSqlCommand; 
await using var reader = await cmd.ExecuteReaderAsync(); 

await MiniExcel.Exporters.GetOpenXmlExporter().CopyAndAddSheetAsync(yourInputStream, yourOuputStream, reader, "NewSheet");