void WriteExcel(Stream output, IEnumerable <QueryResultGrouping> responseResults, bool includeDataMartName) { using (MemoryStream ms = new MemoryStream()) { SpreadsheetDocument doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook); //write the results for each datamart to a separate tab, each spreadsheet will represent a query WorkbookPart workbookPart = doc.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); doc.WorkbookPart.Workbook.Sheets = new Sheets(); Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>(); if (responseResults.Any()) { var excelValidator = new Lpp.Utilities.Excel.ExcelEx(); for (uint sheetID = 1; sheetID <= responseResults.Count(); sheetID++) { var response = responseResults.ElementAt((int)sheetID - 1); string responseSourceName = response.DataMart; if (string.IsNullOrWhiteSpace(responseSourceName)) { var aggregationDefinition = response.Query.Aggregation; if (aggregationDefinition != null && !string.IsNullOrWhiteSpace(aggregationDefinition.Name)) { responseSourceName = aggregationDefinition.Name; } } string tabName = excelValidator.ValidateTabName(includeDataMartName ? (string.IsNullOrWhiteSpace(response.DataMartAcronym) ? (string.IsNullOrEmpty(responseSourceName) ? "Sheet " + sheetID : responseSourceName): response.DataMartAcronym) : responseResults.ElementAt(0).QueryName); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); Sheet sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheetID, Name = tabName }; sheets.Append(sheet); SheetData sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); int totalResultSets = response.Query.Results.Count(); int resultSetIndex = 0; foreach (var table in response.Query.Results) { //foreach resultset create a header row, each set of results for a datamart/grouping will be on the same sheet Row headerRow = new Row(); if (!string.IsNullOrEmpty(responseSourceName) && includeDataMartName) { headerRow.AppendChild(new Cell { DataType = CellValues.String, CellValue = new CellValue("DataMart") }); } foreach (var property in response.Query.Properties.Where(p => !string.Equals(p.As, "LowThreshold", StringComparison.OrdinalIgnoreCase))) { headerRow.AppendChild(new Cell { DataType = CellValues.String, CellValue = new CellValue(property.As) }); } sheetData.AppendChild(headerRow); Row dataRow; if (table.Count() > 0) { foreach (var row in table) { dataRow = new Row(); if (!string.IsNullOrEmpty(responseSourceName) && includeDataMartName) { dataRow.AppendChild(new Cell { DataType = CellValues.String, CellValue = new CellValue(responseSourceName) }); } foreach (var column in row) { if (!column.Key.Equals("LowThreshold", StringComparison.OrdinalIgnoreCase)) { dataRow.AppendChild(new Cell { DataType = CellValues.String, CellValue = new CellValue(column.Value.ToStringEx()) }); } } sheetData.AppendChild(dataRow); } resultSetIndex++; } else if (!string.IsNullOrEmpty(responseSourceName) && includeDataMartName) { dataRow = new Row(); dataRow.AppendChild(new Cell { DataType = CellValues.String, CellValue = new CellValue(responseSourceName) }); for (int k = 1; k < headerRow.ChildElements.Count; k++) { dataRow.AppendChild(new Cell { DataType = CellValues.String, CellValue = new CellValue("") }); } sheetData.AppendChild(dataRow); } if (resultSetIndex < totalResultSets) { //add an empty row between resultsets var emptyRow = new Row(); emptyRow.AppendChild(new Cell { DataType = CellValues.String, CellValue = new CellValue("") }); sheetData.AppendChild(emptyRow); } } worksheetPart.Worksheet.Save(); } } else { WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); Sheet sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet 1" }; sheets.Append(sheet); SheetData sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); worksheetPart.Worksheet.Save(); } workbookPart.Workbook.Save(); doc.Close(); ms.Flush(); ms.Position = 0; ms.CopyTo(output); output.Flush(); } }
public string ExportDetails(DataSet dataset, ExportFormat formatType, string filename) { if (dataset.Tables.Count < 2 && formatType == ExportFormat.CSV) { using (var writer = File.CreateText(filename)) { WriteCSV(dataset.Tables[0], writer); writer.Flush(); } return(filename); } if (formatType == ExportFormat.CSV) { //need to write each query result to a separate file and zip up if (System.IO.Path.GetExtension(filename) != "zip") { filename = Path.ChangeExtension(filename, "zip"); } using (var filestream = new FileStream(filename, FileMode.Create)) using (var zipstream = new ICSharpCode.SharpZipLib.Zip.ZipOutputStream(filestream)) { for (int i = 0; i < dataset.Tables.Count; i++) { var table = dataset.Tables[i]; string zipEntryFilename = Path.ChangeExtension(table.TableName, "csv"); var zipEntry = new ICSharpCode.SharpZipLib.Zip.ZipEntry(zipEntryFilename); zipEntry.DateTime = DateTime.Now; zipstream.PutNextEntry(zipEntry); using (var writer = new StreamWriter(zipstream, Encoding.Default, 1024, true)) { WriteCSV(table, writer); writer.Flush(); } } } return(filename); } else if (formatType == ExportFormat.Excel) { //need to write each query result to a separate tab, with the tab name the queryname using (var filestream = File.Create(filename, 1024)) using (SpreadsheetDocument doc = SpreadsheetDocument.Create(filestream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = doc.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); doc.WorkbookPart.Workbook.Sheets = new Sheets(); Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>(); var excelValidator = new Lpp.Utilities.Excel.ExcelEx(); for (uint sheetID = 1; sheetID <= dataset.Tables.Count; sheetID++) { var table = dataset.Tables[((int)sheetID) - 1]; WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); Sheet sheet = new Sheet { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheetID, Name = excelValidator.ValidateTabName(table.TableName) }; sheets.Append(sheet); SheetData sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); Row headerRow = new Row(); for (int i = 0; i < table.Columns.Count; i++) { headerRow.AppendChild(new Cell { DataType = CellValues.String, CellValue = new CellValue(table.Columns[i].ColumnName) }); } sheetData.AppendChild(headerRow); Row dataRow; for (int j = 0; j < table.Rows.Count; j++) { dataRow = new Row(); var row = table.Rows[j]; for (int k = 0; k < row.ItemArray.Length; k++) { dataRow.AppendChild(new Cell { DataType = CellValues.String, CellValue = new CellValue(row.ItemArray[k].ToStringEx()) }); } sheetData.AppendChild(dataRow); } worksheetPart.Worksheet.Save(); } } return(filename); } else { throw new ArgumentOutOfRangeException(nameof(formatType), "formatType", "Invalid export format type value: " + formatType.ToString()); } }