Creating XLSX file taking more time for 100000 records and taking max RAM memory

Question

I am creating XLSX file with customized code with out Open XML SDK. it is working fine for 50000 records with 200 columns and taking max 13 GB RAM.

But when i was trying with 100000 rows and 200 columns taking max 16 GB RAM and never created the XLSX file and keep on increasing and decreasing RAM memory and also increasing and decreasing the CPU Usage.

I am writing 100000 rows and 200 columns into Stream and copying the stream to Package Part Stream at a same time with out splitting the XML file. That XML file size is 3 GB.

Can you please give solution for this with out using Open XML SDK.

When I tried with Open XML that is working with 100000 records with 200 columns for singe user. but at at time creating 100000 records with 200 columns for two users server is hanging.

My Customized code is taking more RAM but not hanging.

In below code "CreateOpenXMLComWorkSheet_XMLWriter" Method is taking more RAM Size.

I am using below code for your reference. Please let me know if any changes are required.

//Package method

    Package package = null;
    using (package = ZipPackage.Open(path, FileMode.Create))
    {

        packgPart = package.CreatePart(new Uri(relativePaths[relIndex], UriKind.Relative), contentTypes[6], CompressionOption.Maximum);
        XmlWriter xmlWriter;
        Stream stream = CreateOpenXMLComWorkSheet_XMLWriter(data, "", out xmlWriter);
        CopyStream(stream, packgPart.GetStream());
        xmlWriter.Flush();
        xmlWriter.Close();
        xmlWriter = null;
        package.Flush();
        packgPart = null;
        stream.Close();
        stream.Dispose();
        stream = null;
        relIndex++;
        GC.Collect();
        package.Close();

    }

    // CreateOpenXMLComWorkSheet method
    // Define other methods and classes here
private static Stream CreateOpenXMLComWorkSheet_XMLWriter(List<StringBuilder> rows, string sheet,out XmlWriter xmlWriter)

{

    string[]  cols;

    XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();
             xmlWriterSettings.NewLineHandling = NewLineHandling.None;
             xmlWriterSettings.Indent = false;
             xmlWriter = null;
             MemoryStream stream = new MemoryStream();
             string nameSpace = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
             xmlWriter = XmlWriter.Create(stream,xmlWriterSettings);

    xmlWriter.WriteStartElement("x","worksheet",nameSpace);
    xmlWriter.WriteStartElement("x","sheetData",nameSpace);


    for (m = 0; m < rows.Count; m++)
    {

        xmlWriter.WriteStartElement("x","row",nameSpace);

        cols = rows[m].ToString().Split(new string[] { univDelimiter }, StringSplitOptions.None);


        for (int i = 1; i <= cols.Length; i++)
         {
             cellValue = cols[i - 1];
             if (double.TryParse(cellValue,out dVal))
             {
                 dataType = "n";
             }
             else
             {
                 dataType = "str";
             }
             xmlWriter.WriteStartElement("x","c",nameSpace);
             xmlWriter.WriteAttributeString("s", "13");
             xmlWriter.WriteAttributeString("t", dataType);
             xmlWriter.WriteStartElement("x", "v",nameSpace);
             xmlWriter.WriteValue(cellValue);
             xmlWriter.WriteEndElement();
             xmlWriter.WriteEndElement();
         }

        xmlWriter.WriteEndElement();

        rows[m] = null;

    }
    xmlWriter.WriteEndElement();
    xmlWriter.WriteEndElement();
    xmlWriter.Flush();
    stream.Position = 0;
    return stream;
}

//CopyStream method
private static void CopyStream(Stream source, Stream target)
{
     const int bufSize = 0x1000;
     byte[] buf = new byte[bufSize];
     int bytesRead = 0;
     while ((bytesRead = source.Read(buf, 0, bufSize)) > 0)
        target.Write(buf, 0, bytesRead);
}

Answer 1

It seems you are taking wrong approach in writing files, open xml sdk is good enough tool to create excels with large amount of data. i think you need to take SAX-Like Approach which uses combination of xmlreader and writer without running out of memory.

have a look at this wonderful blog which fits your specific requirements.

https://blogs.msdn.microsoft.com/brian_jones/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk/

Answer 2

To reduce memory pressure consider not using MemoryStream in your XmlWriter. If you used a disk based stream then this would reduce memory pressure dramatically.