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());
}
}