Skip to content

Instantly share code, notes, and snippets.

@catlion
Created August 16, 2011 12:40
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save catlion/1148977 to your computer and use it in GitHub Desktop.
Save catlion/1148977 to your computer and use it in GitHub Desktop.
ClosedXML OutOfMemoryException
private static byte[] ConvertReportToExcel(List<Dictionary<string, object>> report)
{
var file = new XLWorkbook();
var sheet = file.Worksheets.Add("Report");
if (report.Count() == 0) {
sheet.Cell("A1").SetValue("Report is empty");
} else {
// First row filled with keys
var firstRow = report.ElementAt(0);
for (int j = 0; j < firstRow.Count; j++) {
var cell = sheet.Row(1).Cell(j + 1);
cell.SetValue(firstRow.Keys.ElementAt(j));
sheet.Row(1).Style.Fill.BackgroundColor = XLColor.LightGray;
sheet.Row(1).Style.Font.Bold = true;
sheet.Row(1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
// Values
for (int i = 0; i < report.Count(); i++) {
var row = report.ElementAt(i);
for (int j = 0; j < row.Count; j++) {
var cell = sheet.Row(i + 2).Cell(j + 1);
var cellVal = row.Values.ElementAt(j);
cell.SetValue(cellVal);
}
}
sheet.SheetView.FreezeRows(1);
sheet.Range(sheet.Row(1).Cell(1).Address, sheet.LastCellUsed().Address).CreateTable();
}
using (var stream = new MemoryStream()) {
file.SaveAs(stream);
return stream.ToArray();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment