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