mini-software
System.IO.IOException: Stream was too long.
Excel Type
- [x] 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) ?
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)
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)
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;
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.
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
- https://github.com/dotnet/runtime/issues/1543
- https://github.com/dotnet/runtime/issues/1544
- https://github.com/dotnet/runtime/issues/49149
- https://github.com/dotnet/runtime/pull/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
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.
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 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)https://github.com/mini-software/MiniExcel/issues/793#issuecomment-2945074880
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 GitHubhttps://github.com/mini-software/MiniExcel/issues/793#issuecomment-2945074880, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AD5IUX5PZCMZ5QPLO3V34I33CBRVNAVCNFSM6AAAAAB6PB7KTGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDSNBVGA3TIOBYGA. You are receiving this because you were mentioned.Message ID: @.***>
@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.