Get Rendered LocalReport Values with OpenXml

[1 minute read]

How oh how is one to get XML from a local (not server) SSRS report?

As it turns out, via Excel. The LocalReport.Render() method only supports Excel, PDF, and Image formats, but since the Excel 2007+ file format is just compressed XML, it didn’t take too much finagling to get what I was really after. The only thing I needed was the Microsoft OpenXml SDK, easily acquired through nuget.

This code snippet shows how to get the rendered values from a LocalReport in memory:

void WriteRenderedValuesToConsole(LocalReport localReport)
{
  var byteArray = localReport.Render(format: "EXCELOPENXML");

	using (MemoryStream stream = new MemoryStream())
	{
		stream.Write(byteArray, 0, byteArray.Length);
		using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
		{
			var workbookPart = spreadsheetDoc.WorkbookPart;
			var sheet = workbookPart.Workbook.Descendants<Sheet>().Single();
			var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));

			foreach (var row in worksheetPart.Worksheet.Descendants<Row>())
			{
				foreach (var cell in row.Descendants<Cell>())
				{
					Console.Write(cell.InnerText);
					Console.Write(" ");
				}

				Console.WriteLine();
			}

			Console.ReadLine();
		}
	}
}