/// <summary>
/// The generate export metadata report.
/// </summary>
private MemoryStreamResult GenerateExportMetadataBasedReport()
{
this.logger.Log(LogType.Trace, this.GetAssemblyName(), "{0}.GenerateExportMetadataReport", this.GetType().Name);
var resourcesPackage = ExcelTemplatePackage.Open(Path.Combine(this.sourceFolder, this.templatePackageName));
var exportMetadata = ExportMetadataPackage.Open(Path.Combine(this.sourceFolder, this.metadataPackageName)).ExportMetadata;
var generator = new ExportGenerator();
var exportParams = new ExportParameters();
ExportToMemoryStreamResult result = generator.ExportToExcelMemoryStream(this.dataParts,
exportMetadata,
resourcesPackage,
exportParams);
if (result.Error != null)
{
this.logger.Log(LogType.Fatal, this.GetAssemblyName(), "{0}.GenerateExportMetadataReport", result.Error);
}
return(new MemoryStreamResult
{
MemoryStream = result.MemoryStream,
Status = result.Error == null ? MemoryStreamResultStatus.Success : MemoryStreamResultStatus.Failure,
ErrorMessage = result.Error == null ? null : result.Error.Message,
});
}
/// <summary>
/// The generate document metadata based report.
/// </summary>
private MemoryStreamResult GenerateDocumentMetadataBasedReport(bool isPdf)
{
this.logger.Log(LogType.Trace, this.GetAssemblyName(), "{0}.GenerateDocumentMetadataBasedReport", this.GetType().Name);
var resourcesPackage = ResourcePackage.Open(Path.Combine(this.sourceFolder, this.templatePackageName));
var documentMetadataPackage = DocumentMetadataPackage.Open(Path.Combine(this.sourceFolder, this.metadataPackageName)).DocumentMetadata;
var generator = new ExportGenerator();
var exportParams = new ExportParameters {
ConvertOutputToPdf = isPdf
};
ExportToMemoryStreamResult result = generator.GenerateDocument(this.dataParts,
documentMetadataPackage,
resourcesPackage,
exportParams);
if (result.Error != null)
{
this.logger.Log(LogType.Fatal, this.GetAssemblyName(), "{0}.GenerateDocumentMetadataBasedReport", result.Error);
}
return(new MemoryStreamResult
{
MemoryStream = result.MemoryStream,
Status = result.Error == null ? MemoryStreamResultStatus.Success : MemoryStreamResultStatus.Failure,
ErrorMessage = result.Error == null ? null : result.Error.Message,
});
}
/// <summary>
/// Appends the sheet to excel stream.
/// </summary>
/// <param name="inputStream">The input stream.</param>
/// <param name="workbookPath">The workbook path.</param>
/// <param name="worksheetName">Name of the worksheet.</param>
private static void InsertAppendSheetToExcelStream(InsertAppend insertAppend, MemoryStream inputStream, string workbookPath, string worksheetName)
{
if (inputStream == null)
{
throw new ArgumentNullException("inputStream");
}
if (string.IsNullOrEmpty(workbookPath))
{
throw new ArgumentNullException("workbookPath");
}
if (string.IsNullOrEmpty(worksheetName))
{
throw new ArgumentNullException("worksheetName");
}
var fi = new FileInfo(workbookPath);
if (!fi.Exists)
{
throw new ExportException(string.Format("Supplied workbook path does not exist <{0}>", workbookPath));
}
// start the append process by opening the inputstream as a autosave spreadsheet document
using (SpreadsheetDocument target = SpreadsheetDocument.Open(inputStream, true, new OpenSettings {
AutoSave = true
}))
{
// next open the workbook containing the sheet to append
using (var source = SpreadsheetDocument.Open(workbookPath, false))
{
// get sheet from the source
var wsp = source.GetWorksheetPart(worksheetName);
if (wsp == null)
{
throw new ExportException(string.Format("Unable to clone worksheet <{0}> from workbook path <{1}>", worksheetName, workbookPath));
}
// and insert a clone into the target
if (insertAppend == InsertAppend.Append)
{
var clone = target.WorkbookPart.InsertClonedWorksheetPart(wsp, SheetStateValues.Visible, worksheetName);
// call the resource and style merger, this merges the index driven styles
// between source and target stream
ExportGenerator.MergeResourcesAndStyles(clone, source, target);
}
else
{
var clone = target.WorkbookPart.InsertClonedWorksheetPartAtFirst(wsp, SheetStateValues.Visible, worksheetName);
// call the resource and style merger, this merges the index driven styles
// between source and target stream
ExportGenerator.MergeResourcesAndStyles(clone, source, target);
}
}
}
}
/// <summary>
/// Takes many streams of excel and merges into one
/// </summary>
/// <param name="inputStreams">The input streams.</param>
/// <returns>The merged output stream</returns>
public static MemoryStream MergeExcelStreams(List <MemoryStream> inputStreams)
{
// no streams return null
if (inputStreams == null || inputStreams.Count == 0)
{
return(null);
}
// only 1 just return it, dont bother to merge
if (inputStreams.Count == 1)
{
return(inputStreams[0]);
}
// start the merge process, using the 1st stream of the starting point
using (SpreadsheetDocument target = SpreadsheetDocument.Open(inputStreams[0], true, new OpenSettings {
AutoSave = true
}))
{
// use this dictionary as a map to keep track of styles created during the merge process
// for example style 1 in source may be style 2 in the target
var mergedStyles = new Dictionary <uint, uint>();
// make sure defined names is there, its always used so may as well do now
if (target.WorkbookPart.Workbook.DefinedNames == null)
{
target.WorkbookPart.Workbook.DefinedNames = new Spreadsheet.DefinedNames();
}
bool first = true;
foreach (var stream in inputStreams)
{
// skip the 1st, its the start point
if (first)
{
first = false;
continue;
}
// start moving through the rest of the stream, creating a spreadsheet doc for each
using (SpreadsheetDocument source = SpreadsheetDocument.Open(stream, true, new OpenSettings {
AutoSave = true
}))
{
// use this to keep track of sheet id changes during copy of sheets from source to target
var sheetIdMap = new Dictionary <int, int>();
// work through each sheet in the workbook
foreach (var sheet in source.WorkbookPart.Workbook.Descendants <Spreadsheet.Sheet>())
{
// very hidden(?!) sheets are not cloned, they seem to contain things like macros
if (sheet.State != null && sheet.State.HasValue && sheet.State.Value == SheetStateValues.VeryHidden)
{
continue;
}
// get sheet from the source
var wsp = (WorksheetPart)source.WorkbookPart.GetPartById(sheet.Id);
// and insert a clone into the target
var clone = target.WorkbookPart.InsertClonedWorksheetPart(wsp, sheet.State, sheet.Name.HasValue ? sheet.Name.Value : null);
int sheetIndex = wsp.GetSheetIndex();
int cloneSheetIndex = clone.GetSheetIndex();
// add the original and cloned sheet index to the map
if (cloneSheetIndex != -1 && sheetIndex != -1 && !sheetIdMap.ContainsKey(sheetIndex))
{
sheetIdMap.Add(sheetIndex, cloneSheetIndex);
}
// clear style for the next sheet, might be worth trying this foreach stream
mergedStyles.Clear();
// call the resource and style merger, this merges the index driven styles
// between source and target stream
ExportGenerator.MergeResourcesAndStyles(clone, source, target, mergedStyles);
}
foreach (var dn in source.WorkbookPart.Workbook.Descendants <Spreadsheet.DefinedName>())
{
var clonedDefinedName = (Spreadsheet.DefinedName)dn.CloneNode(true);
if (clonedDefinedName == null)
{
continue;
}
if (clonedDefinedName.LocalSheetId != null)
{
int clonedSheetIndex = (int)clonedDefinedName.LocalSheetId.Value;
if (sheetIdMap.ContainsKey(clonedSheetIndex))
{
clonedDefinedName.LocalSheetId = new UInt32Value((uint)sheetIdMap[clonedSheetIndex]);
}
}
target.WorkbookPart.Workbook.DefinedNames.AppendChild <Spreadsheet.DefinedName>(clonedDefinedName);
}
}
}
}
return(inputStreams[0]);
}
