private Columns CreateColumnsTransposed()
{
Columns columnsElement = new Columns();
columnsElement.Append(new Column {
Min = 1, Max = 1, Width = 16, CustomWidth = true
});
columnsElement.Append(new Column {
Min = 2, Max = 10, Width = 30, CustomWidth = true
});
return(columnsElement);
}
private void SetColumnWidth(Worksheet worksheet, uint Index, DoubleValue dwidth)
{
DocumentFormat.OpenXml.Spreadsheet.Columns cs = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Columns>();
if (cs != null)
{
IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Column> ic = cs.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(r => r.Min == Index).Where(r => r.Max == Index);
if (ic.Count() > 0)
{
DocumentFormat.OpenXml.Spreadsheet.Column c = ic.First();
c.Width = dwidth;
}
else
{
DocumentFormat.OpenXml.Spreadsheet.Column c = new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = Index, Max = Index, Width = dwidth, CustomWidth = true
};
cs.Append(c);
}
}
else
{
cs = new DocumentFormat.OpenXml.Spreadsheet.Columns();
DocumentFormat.OpenXml.Spreadsheet.Column c = new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = Index, Max = Index, Width = dwidth, CustomWidth = true
};
cs.Append(c);
worksheet.InsertAfter(cs, worksheet.GetFirstChild <SheetFormatProperties>());
}
}
private Columns CreateColumns(IReadOnlyList <ColumnContext> columns)
{
Columns columnsElement = new Columns();
for (int index = 0; index < columns.Count; index++)
{
var columnContext = columns[index];
uint colNumber = (uint)(index + 1);
int columnWidth = ExcelMetadata.GetFirstDefinedValue(
ExcelMetadata.ColumnWidth,
columnContext.ColumnMetadata,
columnContext.SheetMetadata,
columnContext.DocumentMetadata);
columnContext.Column = new Column {
Min = colNumber, Max = colNumber, Width = columnWidth, CustomWidth = true
};
// External customization
var customizeFunc = columnContext.ColumnMetadata?.GetValue(ExcelColumnMetadata.ConfigureColumn);
customizeFunc?.Invoke(columnContext);
if (columnContext.Column != null)
{
columnsElement.Append(columnContext.Column);
}
}
return(columnsElement);
}
static void SaveColumn(Excel.Columns exportedColumns, Column column)
{
var exportedColumn = new Excel.Column()
{
Min = ColumnIndexNum(column), Max = ColumnIndexNum(column)
};
if (column._hidden)
{
exportedColumn.Hidden = true;
}
exportedColumn.Width = 5.0;
exportedColumns.Append(exportedColumn);
}
private void SetColumns(SpreadsheetDocument doc, WorksheetPart worksheetPart, int maxRow, int maxCol)
{
var worksheet = worksheetPart.Worksheet;
var columns = new DocumentFormat.OpenXml.Spreadsheet.Columns();
var col = new DocumentFormat.OpenXml.Spreadsheet.Column();
col.Min = 1;
col.Max = Convert.ToUInt32(maxCol);
col.BestFit = true;
columns.Append(col);
worksheet.Append(columns);
worksheet.Save();
}
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
}
}
private void SetColumns(SpreadsheetDocument doc, WorksheetPart worksheetPart, int maxRow, int maxCol)
{
var worksheet = worksheetPart.Worksheet;
var columns = new DocumentFormat.OpenXml.Spreadsheet.Columns();
var col = new DocumentFormat.OpenXml.Spreadsheet.Column();
col.Min = 1;
col.Max = Convert.ToUInt32(maxCol);
col.BestFit = true;
columns.Append(col);
worksheet.Append(columns);
worksheet.Save();
}