ExcelLargeDataSet.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.IO.Packaging;
using System.Linq;
using System.Reflection;

public class ExcelDoc
{
    /// <summary>
    /// Creates a single sheet spreadsheet from an <see cref="IDataReader"/> that is capable of writing large
    /// quantities of data with a low memory footprint on .NET Core.
    /// </summary>
    /// <param name="dr"></param>
    /// <param name="workSheetName"></param>
    public static void ToFile(string outputFileName, IDataReader dr, string workSheetName)
    {
        string worksheetPartId;

        // Create a file with write access.  To write the large dataset it must first thing written
        // to the writer, any subsequent OpenXmlWriter's seem to require a read.  Because of this, it
        // limits us to one large dataset on one sheet.
        using (var fs = File.Create(outputFileName))
        {
            using (var package = Package.Open(fs, FileMode.Create, FileAccess.Write))
            {
                using (var excel = SpreadsheetDocument.Create(package, SpreadsheetDocumentType.Workbook))
                {
                    // Create the Workbook for the spreadsheet
                    excel.AddWorkbookPart();

                    // Create the writer that we're going to use.. it will write data into the parts of the spreadsheet
                    // which we will then write into the Spreadsheet.
                    List<OpenXmlAttribute> oxa;

                    var wsp = excel.WorkbookPart.AddNewPart<WorksheetPart>();
                    var oxw = OpenXmlWriter.Create(wsp);

                    // We need to get the part ID that we'll larger use to associate the sheet we create to this data.
                    worksheetPartId = excel.WorkbookPart.GetIdOfPart(wsp);

                    oxw.WriteStartElement(new Worksheet());
                    oxw.WriteStartElement(new SheetData());

                    // Header Row
                    int index = 1;

                    oxa = new List<OpenXmlAttribute>();
                    // this is the row index
                    oxa.Add(new OpenXmlAttribute("r", null, index.ToString()));

                    // This is for the row
                    oxw.WriteStartElement(new Row(), oxa);

                    for (int x = 0; x <= dr.FieldCount - 1; x++)
                    {
                        var cell = GetCell(typeof(string), dr.GetName(x));
                        oxa = new List<OpenXmlAttribute>();
                        oxa.Add(new OpenXmlAttribute("t", null, "str"));
                        oxw.WriteElement(cell);
                    }

                    // This is for the row
                    oxw.WriteEndElement();

                    // Add a row for each data item.
                    while (dr.Read())
                    {
                        index += 1;

                        oxa = new List<OpenXmlAttribute>();

                        // this is the row index
                        oxa.Add(new OpenXmlAttribute("r", null, index.ToString()));

                        // This is for the row
                        oxw.WriteStartElement(new Row(), oxa);

                        // Add value for each field in the DataReader.
                        for (int x = 0; x <= dr.FieldCount - 1; x++)
                        {
                            var cell = GetCell(dr[x].GetType(), dr[x].ToString());
                            oxa = new List<OpenXmlAttribute>();
                            oxa.Add(new OpenXmlAttribute("t", null, "str"));
                            oxw.WriteElement(cell);
                        }

                        // this is for Row
                        oxw.WriteEndElement();
                    }

                    // this is for SheetData
                    oxw.WriteEndElement();

                    // this is for Worksheet
                    oxw.WriteEndElement();
                    oxw.Close();
                    oxw.Dispose();
                }
            }
        }

        // Phase 2, we've already written our large dataset, now we need to add the workbook, the sheets and
        // associate the dataset to a sheet.  This requires ReadWrite, it won't be a memory issue because this
        // part doesn't take much memory.
        using (var fs = File.Open(outputFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.None))
        {
            using (var package = Package.Open(fs, FileMode.Open, FileAccess.ReadWrite))
            {
                using (var excel = SpreadsheetDocument.Open(package))
                {
                    // Create the writer that will handle the outer portion of the spreadsheet, it will need to have
                    // these tags closed out when the spreadsheet is closed.
                    var oxw = OpenXmlWriter.Create(excel.WorkbookPart);
                    oxw.WriteStartElement(new Workbook());
                    oxw.WriteStartElement(new Sheets());

                    // Writer this into the global Writer we have open.
                    oxw.WriteElement(new Sheet()
                    {
                        Name = $"{workSheetName}",
                        SheetId = 1,
                        Id = worksheetPartId
                    });

                    // this is for Sheets
                    oxw.WriteEndElement();

                    // this is for Workbook
                    oxw.WriteEndElement();
                    oxw.Close();
                    oxw.Dispose();
                }
            }
        }
    }

    /// <summary>
    /// Returns a spreadsheet <see cref="Cell"/> with its type set according to the .NET type of the data.
    /// </summary>
    /// <param name="type"></param>
    /// <param name="value">The CellValue for the returned <see cref="Cell"/></param>
    private static Cell GetCell(Type type, string value)
    {
        var cell = new Cell();

        if (type.ToString() == "System.RuntimeType")
        {
            cell.DataType = CellValues.String;
            cell.CellValue = new CellValue(value.SafeLeft(32767));
            return cell;
        }

        if (type.ToString() == "System.Guid")
        {
            Guid guidResult;
            Guid.TryParse(value, out guidResult);
            cell.DataType = CellValues.String;
            cell.CellValue = new CellValue(guidResult.ToString());
            return cell;
        }

        // Make sure the value isn't null before putting it into the cell.
        // If it is null, put a blank in the cell.
        if (value == null || Convert.IsDBNull(value))
        {
            cell.DataType = CellValues.String;
            cell.CellValue = new CellValue("");
            return cell;
        }

        var typeCode = Type.GetTypeCode(type);

        switch (typeCode)
        {
            case TypeCode.String:
                cell.DataType = CellValues.String;

                // `ToValidXmlAsciiCharacters` will remove any invalid XML characters falling in the ascii code range of 0-32
                cell.CellValue = new CellValue(value.SafeLeft(32767).ToValidXmlAsciiCharacters());
                break;

            case TypeCode.Int16:
            case TypeCode.Int32:
            case TypeCode.Int64:
            case TypeCode.Double:
            case TypeCode.Decimal:
            case TypeCode.Single:
            case TypeCode.UInt16:
            case TypeCode.UInt32:
            case TypeCode.UInt64:
                // Second most common cases
                cell.DataType = CellValues.Number;
                cell.CellValue = new CellValue(value);
                break;

            case TypeCode.DateTime:
                var dt = Convert.ToDateTime(value).Date;
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue($"{dt.Year}/{dt.MonthTwoCharacters()}/{dt.DayTwoCharacters()}");
                break;
            default:
                // Everything else
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(value);
                break;
        }

        return cell;
    }
}