public void Complete()
{
//save all parts
ExcelStyleSheet.Save();
ExcelSharedStringsTable.SharedStringTable.Save();
ExcelWorkSheetPart.Worksheet.Save();
if (ExcelDocument != null)
{
ExcelDocument.Close();
}
}
public async Task <IActionResult> GetCompliancBySystemExport(string id, string filter, bool pii, string majorcontrol = "")
{
if (!string.IsNullOrEmpty(id))
{
try {
_logger.LogInformation("Calling GetCompliancBySystemExport({0}, {1}, {2})", id, filter, pii.ToString());
// verify system information
SystemGroup sg = NATSClient.GetSystemGroup(id);
if (sg == null)
{
_logger.LogInformation("Called GetCompliancBySystemExport({0}, {1}, {2}) invalid System Group", id, filter, pii.ToString());
return(NotFound());
}
var result = ComplianceGenerator.GetSystemControls(id, filter, pii, majorcontrol);
if (result != null && result.Result != null && result.Result.Count > 0)
{
_logger.LogInformation("Called GetCompliancBySystemExport({0}, {1}, {2}) successfully. Putting into XLSX.", id, filter, pii.ToString());
// starting row
uint rowNumber = 7;
// create the XLSX in memory and send it out
var memory = new MemoryStream();
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(memory, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadSheet.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// add styles to workbook
WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// add stylesheet to use cell formats 1 - 4
wbsp.Stylesheet = ExcelStyleSheet.GenerateStylesheet();
DocumentFormat.OpenXml.Spreadsheet.Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Columns>();
if (lstColumns == null) // generate the column listings we need with custom widths
{
lstColumns = new DocumentFormat.OpenXml.Spreadsheet.Columns();
lstColumns.Append(new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = 1, Max = 1, Width = 20, CustomWidth = true
}); // col A
lstColumns.Append(new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = 2, Max = 2, Width = 60, CustomWidth = true
});
lstColumns.Append(new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = 3, Max = 3, Width = 50, CustomWidth = true
});
lstColumns.Append(new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = 4, Max = 4, Width = 25, CustomWidth = true
});
worksheetPart.Worksheet.InsertAt(lstColumns, 0);
}
// Add Sheets to the Workbook.
Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadSheet.WorkbookPart.
GetIdOfPart(worksheetPart), SheetId = 1, Name = "System-Compliance"
};
sheets.Append(sheet);
// Get the sheetData cell table.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = null;
DocumentFormat.OpenXml.Spreadsheet.Row row = MakeTitleRow("OpenRMF by Cingulara and Tutela");
sheetData.Append(row);
row = MakeXLSXInfoRow("System Name", sg.title, 2);
sheetData.Append(row);
row = MakeXLSXInfoRow("Generated", DateTime.Now.ToString("MM/dd/yy hh:mm tt"), 7);
sheetData.Append(row);
row = MakeComplianceHeaderRows(rowNumber);
sheetData.Append(row);
uint styleIndex = 0; // use this for 4, 5, 6, or 7 for status
_logger.LogInformation("GetCompliancBySystemExport() cycling through all the vulnerabilities");
foreach (NISTCompliance nist in result.Result)
{
if (nist.complianceRecords.Count > 0)
{
foreach (ComplianceRecord rec in nist.complianceRecords)
{
rowNumber++;
styleIndex = GetVulnerabilityStatus(rec.status, "high");
// make a new row for this set of items
row = MakeDataRow(rowNumber, "A", nist.control, styleIndex);
// now cycle through the rest of the items
newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "B" + rowNumber.ToString()
};
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue(nist.title);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = styleIndex;
newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "C" + rowNumber.ToString()
};
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue(rec.title);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = styleIndex;
newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "D" + rowNumber.ToString()
};
row.InsertBefore(newCell, refCell);
// print out status, if N/A or NAF then just NAF
if (rec.status.ToLower() == "open")
{
newCell.CellValue = new CellValue("Open");
}
else if (rec.status.ToLower() == "not_reviewed")
{
newCell.CellValue = new CellValue("Not Reviewed");
}
else
{
newCell.CellValue = new CellValue("Not a Finding");
}
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = styleIndex;
sheetData.Append(row);
}
}
else
{
rowNumber++;
styleIndex = 0;
// make a new row for this set of items
row = MakeDataRow(rowNumber, "A", nist.control, styleIndex);
// now cycle through the rest of the items
newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "B" + rowNumber.ToString()
};
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue(nist.title);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = styleIndex;
sheetData.Append(row);
}
}
// Save the new worksheet.
workbookpart.Workbook.Save();
// Close the document.
spreadSheet.Close();
// set the filename
string filename = sg.title;
if (!string.IsNullOrEmpty(sg.title) && sg.title.ToLower().Trim() == "none")
{
filename = sg.title.Trim() + "-" + filename; // add the system onto the front
}
// return the file
memory.Seek(0, SeekOrigin.Begin);
_logger.LogInformation("Called GetCompliancBySystemExport({0}, {1}, {2}) successfully", id, filter, pii.ToString());
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", CreateXLSXFilename(filename)));
} // end of using statement
}
else
{
_logger.LogWarning("Called GetCompliancBySystemExport({0}, {1}, {2}) but had no returned data", id, filter, pii.ToString());
return(NotFound()); // bad system reference
}
}
catch (Exception ex) {
_logger.LogError(ex, "GetCompliancBySystemExport() Error exporting Compliance for system {0}", id);
return(BadRequest());
}
}
else
{
_logger.LogWarning("Called GetCompliancBySystemExport() but with an invalid or empty system group Id", id);
return(BadRequest()); // no term entered
}
}