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