private Workbook AggregateDocuments(SortedDictionary <string, List <Document> > DMDocs) { const int VALUE_COLUMN_WIDTH = 12; Aspose.Cells.Workbook AggregateBook = null; const string AggregateFilePrefix = "Aggregate_"; string ErrorMessage = string.Empty; List <byte[]> FilesToAggregate = new List <byte[]>(); byte[] AggregateFile; bool AggregateTemplateFound = false; #region Get the document containing the aggregate filename foreach (string dm in DMDocs.Keys) { foreach (IDnsPersistentDocument doc in DMDocs[dm]) { Stream contentStream = doc.ReadStream(); //doc is a zipped file. Dictionary <string, byte[]> ExtractedFiles = ExtractZipFile(contentStream, doc.Name); if (ExtractedFiles.Count() <= 0) { ExtractedFiles.Add(doc.Name, GetBytesFromStream(contentStream, doc.BodySize)); } foreach (string key in ExtractedFiles.Keys) { if (key.StartsWith(AggregateFilePrefix)) { if (!AggregateTemplateFound) { AggregateFile = ExtractedFiles[key]; AggregateTemplateFound = true; //Load the aggregate file. MemoryStream ms = new MemoryStream(); ms.Write(AggregateFile, 0, AggregateFile.Length); ms.Position = 0; try { AggregateBook = new Aspose.Cells.Workbook(ms); } catch (Exception) { AggregateBook = null; } ms.Close(); AggregateFile = null; if (AggregateBook != null) { //SAS exported Aggregate template contains formula fields but suppressed by apostrophs(') in the beginning. //Remove apostrophs from the aggregate template so that suppressed formulae are restored. foreach (Aspose.Cells.Worksheet ws in AggregateBook.Worksheets) { foreach (Aspose.Cells.Cell cl in ws.Cells) { if (!cl.IsFormula && !string.IsNullOrEmpty(cl.StringValue) && cl.StringValue.StartsWith("=")) { cl.Formula = cl.StringValue; } } } AggregateTemplateFound = true; } } } else { FilesToAggregate.Add(ExtractedFiles[key]); } } } } #endregion if (AggregateTemplateFound) { //Iterate through each datamart. Aggregate the files from each datamart into the aggregate book. foreach (byte[] bytes in FilesToAggregate) { //SKIP the Aggregate Template File which are uploaded by each DataMart. //Create a Source Workbook from Byte Arrary obtained from Database. MemoryStream ms = new MemoryStream(); ms.Write(bytes, 0, bytes.Length); ms.Position = 0; //If Memorystream cannot be read as Aspose Cells, then ignore and move on to next memory stream. Aspose.Cells.Workbook FileToAggregate = null; try { FileToAggregate = new Aspose.Cells.Workbook(ms); } catch (Exception) { FileToAggregate = null; } ms.Close(); if (FileToAggregate != null) { //Copy the worksheets from Source Book (ws) to the corresponding worksheet in the Aggregate Book. foreach (Aspose.Cells.Worksheet ws in FileToAggregate.Worksheets) { Aspose.Cells.Worksheet targetsheet = AggregateBook.Worksheets[ws.Name]; if (targetsheet != null) { foreach (Aspose.Cells.Cell wcell in ws.Cells) { Aspose.Cells.Cell tcell = targetsheet.Cells[wcell.Row, wcell.Column]; if (tcell != null) { tcell.Copy(wcell); } } } } } } /* Set the formula field width = 12 characters */ foreach (Aspose.Cells.Worksheet ws in AggregateBook.Worksheets) { foreach (Aspose.Cells.Column col in ws.Cells.Columns) { foreach (Aspose.Cells.Cell fld in ws.Cells) { if (col.Index == fld.Column && (fld.IsFormula || fld.Type == Aspose.Cells.CellValueType.IsNumeric)) { col.Width = VALUE_COLUMN_WIDTH; break; } } } } } else { ErrorMessage = "Cannot locate the Aggregate Template File."; throw (new Exception(ErrorMessage)); } return(AggregateBook); }