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();
            }
        }
Exemplo n.º 2
0
        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());
            }
        }