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);
}
/// <summary>
/// Add cell width styles.
/// </summary>
/// <param name="minCol">Minimum column index.</param>
/// <param name="maxCol">Maximum column index.</param>
/// <param name="maxWidth">Maximum column width.</param>
/// <param name="spreadSheet">Spread sheet.</param>
/// <param name="workSheetPart">Work sheet.</param>
private static void AddCellWidthStyles(uint minCol, uint maxCol, int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
{
var cols = new DocumentFormat.OpenXml.Spreadsheet.Columns(new Column {
CustomWidth = true, Min = minCol, Max = maxCol, Width = maxWidth, BestFit = false
});
workSheetPart.Worksheet.InsertBefore(cols, workSheetPart.Worksheet.GetFirstChild <SheetData>());
}
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);
}
/// <summary>
/// Removes <see cref="OpenXmlSpreadsheet.Columns"/> collection, all <see cref="OpenXmlSpreadsheet.Column"/>s, all <see cref="OpenXmlSpreadsheet.MergeCells"/>
/// and all <see cref="OpenXmlSpreadsheet.Row"/s> from the supplied <see cref="WorksheetPart"/>
/// </summary>
/// <param name="worksheetPart"></param>
public void EmptyWorksheet()
{
OpenXmlSpreadsheet.Columns columns = this.worksheet.GetFirstChild <OpenXmlSpreadsheet.Columns>();
// Clear all contents of the sheet if this is an existing sheet.
this.worksheet.RemoveAllChildren <OpenXmlSpreadsheet.MergeCells>();
this.sheetData.RemoveAllChildren <OpenXmlSpreadsheet.Row>();
if (columns != null)
{
columns.RemoveAllChildren <OpenXmlSpreadsheet.Column>();
}
this.worksheet.RemoveAllChildren <OpenXmlSpreadsheet.Columns>();
}
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();
}
/*
* static Excel.SharedStringTable SaveSharedStringTable()
* {
* var exportedSharedStringTable = new Excel.SharedStringTable();
*
* return exportedSharedStringTable;
* }
*/
static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId)
{
var exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>();
string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart);
var exportedWorksheet = new Excel.Worksheet();
exportedWorksheetPart.Worksheet = exportedWorksheet;
var exportedColumns = new Excel.Columns();
exportedWorksheet.Append(exportedColumns);
var exportedSheetData = new Excel.SheetData();
exportedWorksheet.Append(exportedSheetData);
var exportedSheet = new Excel.Sheet()
{
Name = sheet.Name, Id = relId, SheetId = sheetId
};
if (sheet.Hidden)
{
exportedSheet.State = Excel.SheetStateValues.Hidden;
}
exportedSheets.Append(exportedSheet);
foreach (var column in sheet.Columns.OrderBy(r => r.Index))
{
SaveColumn(exportedColumns, column);
}
foreach (var row in sheet.Rows.OrderBy(r => r.Index))
{
SaveRow(exportedSheetData, styleSheet, cellFormatList, row);
}
exportedWorksheetPart.Worksheet.Save();
}
/// <summary>
/// Set column width in characters
/// </summary>
/// <param name="width">column width in characters</param>
/// <returns></returns>
public Column SetWidth(double width)
{
var columns = Worksheet.GetFirstChild <x.Columns>();
if (columns == null)
{
columns = new x.Columns();
Worksheet.Insert(columns).AfterOneOf(typeof(x.Dimension), typeof(x.SheetViews), typeof(x.SheetFormatProperties));
}
var columnProp = columns.Descendants <x.Column>().Where(c => c.Min.HasValue && c.Min.Value <= ColumnNum && c.Max.HasValue && c.Max.Value >= ColumnNum).FirstOrDefault();
if (columnProp == null)
{
columnProp = new x.Column()
{
Min = (uint)ColumnNum,
Max = (uint)ColumnNum
};
columns.Append(columnProp);
}
columnProp.CustomWidth = true;
columnProp.Width = width;
return(this);
}
public static void CreateExcelDoc(string filepath, List <StudentData> data, string header)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
DocumentFormat.OpenXml.Spreadsheet.Columns columns = new DocumentFormat.OpenXml.Spreadsheet.Columns(
new DocumentFormat.OpenXml.Spreadsheet.Column
{
Min = 1,
Max = 1,
Width = 4,
CustomWidth = true
},
new DocumentFormat.OpenXml.Spreadsheet.Column
{
Min = 2,
Max = 2,
Width = 45,
CustomWidth = true
},
new DocumentFormat.OpenXml.Spreadsheet.Column
{
Min = 3,
Max = 3,
Width = 12,
CustomWidth = true
},
new DocumentFormat.OpenXml.Spreadsheet.Column
{
Min = 4,
Max = 4,
Width = 9,
CustomWidth = true
},
new DocumentFormat.OpenXml.Spreadsheet.Column
{
Min = 5,
Max = 5,
Width = 18,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = header
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
Row row = new Row();
row.Append(
ConstructCell("№", CellValues.String, 2),
ConstructCell("П.I.Б. студента", CellValues.String, 2),
ConstructCell("Група", CellValues.String, 2),
ConstructCell("Рейтинг", CellValues.String, 2),
ConstructCell("Дод. iнформацiя", CellValues.String, 2));
sheetData.AppendChild(row);
for (int i = 0; i < data.Count; i++)
{
row = new Row();
row.Append(
ConstructCell((i + 1).ToString(), CellValues.Number, 1),
ConstructCell(data[i].Name, CellValues.String, 1),
ConstructCell(data[i].Group, CellValues.String, 1),
ConstructRatingCell(data[i].Rating),
ConstructCell(data[i].Info, CellValues.String, 1));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
}
}
/// <summary>
/// Preenche a Ficha de Inspeção Especial em Excel e disponibiliza para download
/// </summary>
/// <param name="ord_id">Id da O.S pertinente ao objeto</param>
/// <param name="origem">Tela que chamou o evento</param>
/// <returns>string</returns>
public string FichaInspecaoEspecialAnomalias_ExportarXLS(int ord_id, string origem)
{
string arquivo_modelo_caminhoFull = System.Web.HttpContext.Current.Server.MapPath("~/Reports/Ficha_Cadastramento_Anomalias.xlsx");
string arquivo_saida = "Ficha_Cadastramento_Anomalias_" + DateTime.Now.ToString().Replace(" ", "").Replace(":", "").Replace("/", "") + ".xlsx";
string arquivo_saida_caminhoFull = System.Web.HttpContext.Current.Server.MapPath("~/temp/") + "/" + arquivo_saida;
string arquivo_saida_caminho_virtual = HttpContext.Current.Request.Url.Host + "/temp/" + arquivo_saida;
string saida = "";
List <string> Headers = new List <string>();
try
{
Gerais ger = new Gerais();
limpaArquivosAntigos();
File.Copy(arquivo_modelo_caminhoFull, arquivo_saida_caminhoFull);
// faz busca os DADOS no banco
List <InspecaoAnomalia> lstDADOS = new InspecaoDAO().InspecaoAnomalias_Valores_ListAll(ord_id);
// Abre a planilha para edicao
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(arquivo_saida_caminhoFull, true))
{
// LEITURA DA PLANILHA
Worksheet worksheet = ger.GetWorksheet(doc, "Ficha_Cadastramento_Anomalias");
Worksheet worksheetRodape = ger.GetWorksheet(doc, "Rodape");
// ======= PREENCHE OS DADOS ===============================================
if (lstDADOS.Count > 0)
{
if (origem == "OrdemServico") // remove cabecalho e oculta coluna A
{
// remove cabecalhos
Cell cell_A1 = ger.InsertCellInWorksheet("A", 1, worksheet);
string letra = "A";
for (int t = 82; t < 88; t++)
{
if (t > 82)
{
letra = ((char)t).ToString();
}
for (int m = 5; m <= 7; m++)
{
ger.copyCell(doc, worksheet, cell_A1, letra, Convert.ToUInt32(m));
}
}
DocumentFormat.OpenXml.Spreadsheet.Columns columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
DocumentFormat.OpenXml.Spreadsheet.Column col = columns.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Column>();
//col.Width = 0;
col.Hidden = DocumentFormat.OpenXml.BooleanValue.FromBoolean(true);
}
for (int li = 0; li < lstDADOS.Count; li++)
{
for (int col = 65; col < 87; col++) // VARRE as COLUNAS A até V
{
if ((origem == "OrdemServico") && (col >= 83))
{
break;
}
Cell cell = ger.InsertCellInWorksheet(((char)col).ToString(), Convert.ToUInt32(li + 9), worksheet);
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.StyleIndex = cell_estilo(lstDADOS[li].clo_id, false, ref worksheetRodape);
switch (col)
{
case 65: if (origem == "Inspecao")
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].obj_codigo));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 66: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].item)); break;
case 67: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].obj_descricao)); break;
}
if (lstDADOS[li].ian_id > 0)
{
cell.StyleIndex = cell_estilo(lstDADOS[li].clo_id, true, ref worksheetRodape);
switch (col)
{
case 68: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_localizacao_especifica)); break;
case 69: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_numero)); break;
case 70: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].leg_codigo)); break;
case 71: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].atp_codigo)); break;
case 72: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ale_codigo)); break;
case 73:
if (ger.IsNumeric(lstDADOS[li].ian_quantidade))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_quantidade)));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 74:
if (ger.IsNumeric(lstDADOS[li].ian_espacamento))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_espacamento)));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 75:
if (ger.IsNumeric(lstDADOS[li].ian_largura))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_largura)));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 76:
if (ger.IsNumeric(lstDADOS[li].ian_comprimento))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_comprimento)));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 77:
if (ger.IsNumeric(lstDADOS[li].ian_abertura_minima))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_abertura_minima)));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 78:
if (ger.IsNumeric(lstDADOS[li].ian_abertura_maxima))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_abertura_maxima)));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 79: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].aca_codigo)); break;
case 80: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_fotografia)); break;
case 81: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_croqui)); break;
case 82: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_observacoes)); break;
case 83:
if (origem == "Inspecao")
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].rpt_id_sugerido_codigo));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 84:
if (origem == "Inspecao")
{
if (lstDADOS[li].ian_quantidade_sugerida > 0)
{
//cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
//cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_quantidade_sugerida)));
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[li].ian_quantidade_sugerida.ToString() + " " + lstDADOS[li].rpt_id_sugerido_unidade);
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 85:
if (origem == "Inspecao")
{
if ((ger.IsNumeric(lstDADOS[li].rpt_id_adotado)) && (Convert.ToDecimal(lstDADOS[li].rpt_id_adotado) > 0))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].rpt_id_adotado)));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 86:
if (origem == "Inspecao")
{
if (lstDADOS[li].ian_quantidade_adotada > 0)
{
// cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
// cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_quantidade_adotada)));
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[li].ian_quantidade_adotada.ToString() + " " + lstDADOS[li].rpt_id_adotado_unidade);
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
case 87:
if (origem == "Inspecao")
{
if (lstDADOS[li].apt_id > 0)
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].apt_id)));
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("");
}
break;
}
}
} // for col
} // for li
// ============ coloca o rodape ================================================
uint LinhaDestino = (uint)lstDADOS.Count + 8;
for (int li = 1; li <= 3; li++)
{
for (int col = 65; col < 87; col++) // VARRE as COLUNAS A até V
{
// copia o Quadro A da planilha "Rodape" para o rodape dos dados
Cell cellOrigem = ger.InsertCellInWorksheet(((char)col).ToString(), Convert.ToUInt32(li), worksheetRodape);
ger.copyCell(doc, worksheet, cellOrigem, ((char)col).ToString(), Convert.ToUInt32(li + LinhaDestino));
}
// mescla as celulas
if (li == 1)
{
ger.MergeCells(worksheet, "B" + (li + LinhaDestino).ToString(), "V" + (li + LinhaDestino).ToString());
}
else
{
ger.MergeCells(worksheet, "A" + (li + LinhaDestino).ToString(), "E" + (li + LinhaDestino).ToString());
ger.MergeCells(worksheet, "F" + (li + LinhaDestino).ToString(), "V" + (li + LinhaDestino).ToString());
}
}
// preenche os valores
Cell cell2 = ger.InsertCellInWorksheet("C", 2, worksheet);
cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].obj_codigo_TipoOAE);
cell2 = ger.InsertCellInWorksheet("R", 2, worksheet);
cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].ins_anom_data);
cell2 = ger.InsertCellInWorksheet("C", 3, worksheet);
cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].ins_anom_Responsavel);
cell2 = ger.InsertCellInWorksheet("G", (LinhaDestino + 2), worksheet);
cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].ins_anom_quadroA_1);
// cell2.StyleIndex = cell_Modelo4.StyleIndex;
cell2 = ger.InsertCellInWorksheet("G", (LinhaDestino + 3), worksheet);
cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].ins_anom_quadroA_2);
// cell2.StyleIndex = cell_Modelo4.StyleIndex;
// ============ coloca o rodape Observacoes ================================================
int li2 = 4;
for (int col = 65; col < 87; col++) // VARRE as COLUNAS A até V
{
// copia Obs da planilha "Rodape" para o rodape dos dados
Cell cellOrigem = ger.InsertCellInWorksheet(((char)col).ToString(), Convert.ToUInt32(li2), worksheetRodape);
ger.copyCell(doc, worksheet, cellOrigem, ((char)col).ToString(), Convert.ToUInt32(li2 + LinhaDestino));
}
// mescla as celulas
ger.MergeCells(worksheet, "A" + (li2 + LinhaDestino).ToString(), "V" + (li2 + LinhaDestino + 6).ToString());
// ajusta a altura da linha
Row linhaObs = ger.GetRow(worksheet, 4 + LinhaDestino);
}
// fecha o arquivo e retorna
doc.Save();
doc.Close();
} // using
return(arquivo_saida);
} // try
catch (Exception ex)
{
saida = ex.ToString();
}
return("");
}
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();
}
private void AddSheet(SheetContext sheetContext)
{
WorkbookPart workbookPart = sheetContext.DocumentContext.WorkbookPart;
WorksheetPart worksheetPart = sheetContext.WorksheetPart;
uint sheetCount = workbookPart.GetSheetCount();
Worksheet worksheet = new Worksheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac xr xr2 xr3"
}
};
worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
worksheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
worksheet.AddNamespaceDeclaration("xr2", "http://schemas.microsoft.com/office/spreadsheetml/2015/revision2");
worksheet.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
worksheetPart.Worksheet = worksheet;
SheetViews sheetViews = worksheet.GetOrCreateSheetViews();
SheetView sheetView = new SheetView {
WorkbookViewId = (UInt32Value)0U
};
if (sheetCount == 0)
{
sheetView.TabSelected = true;
}
sheetViews.AppendChild(sheetView);
SheetFormatProperties sheetFormatProperties = new SheetFormatProperties
{
DefaultRowHeight = 15D,
DyDescent = 0.25D,
};
ColumnContext CreateColumnContext(IPropertyRenderer renderer) =>
new ColumnContext(
sheetContext,
renderer.GetMetadata <ExcelColumnMetadata>() ?? _defaultColumnMetadata,
renderer);
sheetContext.Columns = sheetContext
.ReportProvider
.Renderers
.Select(CreateColumnContext)
.ToList();
Columns columns = sheetContext.IsNotTransposed ? CreateColumns(sheetContext.Columns) : CreateColumnsTransposed();
SheetData sheetData = new SheetData();
//workSheet.Append(sheetDimension);
worksheet.Append(sheetViews);
worksheet.Append(sheetFormatProperties);
if (columns != null)
{
worksheet.Append(columns);
}
worksheet.Append(sheetData);
//workSheet.Append(pageMargins);
// Append a new worksheet and associate it with the workbook.
Sheets sheets = workbookPart.Workbook.Sheets;
Sheet sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetCount + 1,
Name = sheetContext.ReportProvider.ReportName,
};
sheets.Append(sheet);
bool freezeTopRow = ExcelMetadata.GetFirstDefinedValue(
ExcelMetadata.FreezeTopRow,
sheetContext.SheetMetadata,
sheetContext.DocumentMetadata);
if (freezeTopRow)
{
worksheet.FreezeTopRow(rowNum: 1);
}
sheetContext.SheetData = sheetData;
sheetContext.Sheet = sheet;
}
private void SetColumns(Worksheet worksheet)
{
var cols = new Columns();
cols.AppendChild(new Column {
Min = 1, Max = 1, Width = 65,
CustomWidth = true
});
cols.AppendChild(new Column {
Min = 2, Max = 2, Width = 20,
CustomWidth = true
});
cols.AppendChild(new Column {
Min = 3, Max = 3, Width = 20,
CustomWidth = true
});
cols.AppendChild(new Column {
Min = 4, Max = 4, Width = 20,
CustomWidth = true
});
cols.AppendChild(new Column {
Min = 5, Max = 5, Width = 20,
CustomWidth = true
});
worksheet.InsertBefore(cols, worksheet.First(x => x.LocalName == "sheetData"));
worksheet.Save();
}
private static async Task <MemoryStream> DataToExcelStreamAsync(WriteRowsDelegate writeRows, IList <String> headers, string sheetName, List <int> columnWidths = null)
{
var xmlStream = ReportingHelper.GetResourceStream("Shesha.Web.DataTable.Excel.template.xlsx", typeof(ExcelUtility).Assembly);
using (var document = SpreadsheetDocument.Open(xmlStream, true))
{
var workbookPart = document.WorkbookPart;
var worksheetPart = workbookPart.WorksheetParts.First();
var originalSheetId = workbookPart.GetIdOfPart(worksheetPart);
var replacementPart = workbookPart.AddNewPart <WorksheetPart>();
var replacementPartId = workbookPart.GetIdOfPart(replacementPart);
// Configure the spreadsheet
SetSheetName(sheetName, document);
SetStyleSheet(document);
// Fit to page
var sp = new SheetProperties(new PageSetupProperties());
var ws = worksheetPart.Worksheet;
ws.SheetProperties = sp;
// Set the FitToPage property to true
ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true);
var pgOr = new PageSetup
{
// Page size A4 landscape
PaperSize = 9,
Orientation = OrientationValues.Landscape,
// Scale to fit to page width
FitToWidth = 1,
FitToHeight = 0
};
ws.AppendChild(pgOr);
var maxWidth = 0;
if (columnWidths != null)
{
var idx = 1;
var columns =
columnWidths
.Select(
w => new Column
{
CustomWidth = true,
Min = Convert.ToUInt32(idx),
Max = Convert.ToUInt32(idx++),
Width = w,
BestFit = false
})
.ToList();
var cols = new DocumentFormat.OpenXml.Spreadsheet.Columns(columns);
worksheetPart.Worksheet.InsertBefore(cols, worksheetPart.Worksheet.GetFirstChild <SheetData>());
}
else
{
maxWidth = headers.Select(h => h.Length).Max();
AddCellWidthStyles(Convert.ToUInt32(1), Convert.ToUInt32(headers.Count), maxWidth, document, worksheetPart);
}
worksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();
using (var xmlReader = OpenXmlReader.Create(worksheetPart))
{
using (var xmlWriter = OpenXmlWriter.Create(replacementPart))
{
while (xmlReader.Read())
{
if (xmlReader.ElementType == typeof(SheetData))
{
if (xmlReader.IsEndElement)
{
continue;
}
xmlWriter.WriteStartElement(new SheetData());
var headerCell = new Cell(new CellValue());
headerCell.DataType = new EnumValue <CellValues>(CellValues.String);
// write headers
xmlWriter.WriteStartElement(new Row());
SetHeaderStyle(document, headerCell);
foreach (var header in headers)
{
headerCell.CellValue.Text = header;
xmlWriter.WriteElement(headerCell);
}
xmlWriter.WriteEndElement();
await writeRows.Invoke(xmlWriter);
xmlWriter.WriteEndElement();
}
else
{
if (xmlReader.IsStartElement)
{
xmlWriter.WriteStartElement(xmlReader);
}
else if (xmlReader.IsEndElement)
{
xmlWriter.WriteEndElement();
}
}
}
}
}
var sheet = workbookPart.Workbook.Descendants <Sheet>().First(s => s.Id.Value.Equals(originalSheetId));
sheet.Id.Value = replacementPartId;
workbookPart.DeletePart(worksheetPart);
}
return(xmlStream);
}
