This is due to a change in System.Package between .NET Framework and .NET Core. It's a known issue, I was able to cobble together a workaround (with limitations) based off of some suggestions from Microsoft. On GitHub they indicated that opening the Package in Write and not ReadWrite mode would allow for a large spreadsheet to be streamed with the SAX approach. Because of this approach, the order is important. The first thing you write out in Write mode has to be the large sheet because any other OpenXmlWriter instances that are opened will require ReadWrite or they'll throw an exception (thus the limitation).
Here's the steps I followed:
- Create a
FileStream (I used File.Create).
- Create a
Package, pass in the FileStream and use FileMode.Create and FileAccess.Write
- Create a
SpreadsheetDocument via SpreadsheetDocument.Create
- Write your large
WorksheetPart via an OpenXmlWriter
- Close and Dispose of the writer, the package, the file stream, etc.
- Create a
FileStream (open this time, File.Open with FileMode.Open, FileAccess.ReadWrite and FileShare.None)
- Create a
Package, pass in the FileStream and use FileMode.Open and FileAccess.ReadWrite
- Create a
SpreadsheetDocument via SpreadsheetDocument.Open
- Create an
OpenXmlWriter for the WorkbookPart and add the elements for Workbook and Sheets, then you'll associate the Sheet you added on the original create, close and dispose of those objects and done.
Now, some example code that should get you close. I'm writing an IDataReader out to the sheet here. There are a few string extensions I didn't include here but you can just remove or change to fit your needs.
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;
}
}
Obviously this isn't ideal but but it will get you one large sheet without getting the out of memory exception. I tested this .NET 5 but it should work with 3.1 as well.
I'm including the GitHub issue on the OpenXml library and also the dotnet runtime issue where this is discussed and I got the idea for the workaround.
