public static void AllocateSpaceForNextRange(IXLRange copiedRange, Direction direction, ShiftType type = ShiftType.Cells) { switch (direction) { case Direction.Bottom: if (type == ShiftType.Cells) { copiedRange.InsertRowsBelow(copiedRange.RowCount(), false); } else if (type == ShiftType.Row) { copiedRange.Worksheet.Row(copiedRange.LastRow().RowNumber()).InsertRowsBelow(copiedRange.RowCount()); } break; case Direction.Right: if (type == ShiftType.Cells) { copiedRange.InsertColumnsAfter(copiedRange.ColumnCount(), false); } else if (type == ShiftType.Row) { copiedRange.Worksheet.Column(copiedRange.LastColumn().ColumnNumber()).InsertColumnsAfter(copiedRange.ColumnCount()); } break; case Direction.Top: if (type == ShiftType.Cells) { copiedRange.InsertRowsAbove(copiedRange.RowCount(), false); } else if (type == ShiftType.Row) { copiedRange.Worksheet.Row(copiedRange.FirstRow().RowNumber()).InsertRowsAbove(copiedRange.RowCount()); } break; case Direction.Left: if (type == ShiftType.Cells) { copiedRange.InsertColumnsBefore(copiedRange.ColumnCount(), false); } else if (type == ShiftType.Row) { copiedRange.Worksheet.Column(copiedRange.FirstColumn().ColumnNumber()).InsertColumnsBefore(copiedRange.ColumnCount()); } break; } }
public static object[] GetSheetIntoObjectUsingClosedXML(string fileDetails, string sheetName) { object[] main = null; using (XLWorkbook book = new XLWorkbook(fileDetails)) { IXLWorksheet xlSheet = book.Worksheet(sheetName); IXLRange xlRange = xlSheet.RangeUsed(); int rowCount = xlRange.RowCount(); int ColCount = xlRange.ColumnCount(); Console.WriteLine(rowCount); Console.WriteLine(ColCount); main = new object[rowCount - 1]; //number of rows (Test case) for (int i = 2; i <= rowCount; i++) { object[] temp = new object[ColCount]; //number of parameter for (int j = 1; j <= ColCount; j++) { string value = xlRange.Cell(i, j).GetString(); Console.WriteLine(value); temp[j - 1] = value; } main[i - 2] = temp; } book.Dispose(); } return(main); }
/// <summary> /// Возвращает адресс нижней правой ячейки пары /// </summary> /// <param name="worksheet"></param> /// <param name="range"></param> /// <param name="column"></param> /// <returns></returns> private IXLRange SearchForLessonRange(IXLWorksheet worksheet, IXLRange range, int startRow, int startColumn) { var column = startColumn; var startCell = range.Cell(startRow, column).Address; var row = startRow; var lessonPrimaryBorderStyle = XLBorderStyleValues.Medium; var lessonSecondaryBorderStyle = XLBorderStyleValues.Thin; while ((range.Cell(row, column).Style.Border.RightBorder != lessonPrimaryBorderStyle) && (range.Cell(row, column + 1).Style.Border.RightBorder != lessonPrimaryBorderStyle) && (column < range.ColumnCount())) { column++; } while ((range.Cell(row, column).Style.Border.BottomBorder != lessonPrimaryBorderStyle) && (range.Cell(row, column).Style.Border.BottomBorder != lessonSecondaryBorderStyle) && (range.Cell(row + 1, column).Style.Border.TopBorder != lessonPrimaryBorderStyle) && (range.Cell(row + 1, column).Style.Border.TopBorder != lessonSecondaryBorderStyle) && (row < range.RowCount())) { row++; } return(worksheet.Range(startCell, range.Cell(row, column).Address)); //возвращает на 1 столбец меньше военки }
public IXLRange CopyTo(IXLRange range) { var tempRng = _sheet.Range(_sheet.Cell(1, 1), _sheet.LastCellUsed()); //_sheet.Cell(_prevrow, _prevclmn)); range.InsertRowsBelow(tempRng.RowCount() - range.RowCount(), true); range.InsertColumnsAfter(tempRng.ColumnCount() - range.ColumnCount(), true); tempRng.CopyTo(range.FirstCell()); var tgtSheet = range.Worksheet; var tgtStartRow = range.RangeAddress.FirstAddress.RowNumber; using (var srcRows = _sheet.Rows(tempRng.RangeAddress.FirstAddress.RowNumber, tempRng.RangeAddress.LastAddress.RowNumber)) foreach (var row in srcRows) { var xlRow = tgtSheet.Row(row.RowNumber() + tgtStartRow - 1); xlRow.OutlineLevel = row.OutlineLevel; if (row.IsHidden) { xlRow.Collapse(); } else { xlRow.Expand(); } } return(range); }
private void Process(IXLRange root, GroupTag[] groups, bool summaryAbove, SubtotalSummaryFunc[] funcs, bool disableGrandTotal) { var groupRow = root.LastRow(); // DoGroups var level = 0; var r = root.Offset(0, 0, root.RowCount() - 1, root.ColumnCount()); using (var subtotal = new Subtotal(r, summaryAbove)) { if (TotalLabel != null) { subtotal.TotalLabel = TotalLabel; } if (GrandLabel != null) { subtotal.GrandLabel = GrandLabel; } if (!disableGrandTotal) { var total = subtotal.AddGrandTotal(funcs); total.SummaryRow.Cell(2).Value = total.SummaryRow.Cell(1).Value; total.SummaryRow.Cell(1).Value = null; level++; } foreach (var g in groups.OrderBy(x => x.Column)) { Func <string, string> labFormat = null; if (!string.IsNullOrEmpty(g.LabelFormat)) { labFormat = title => string.Format(LabelFormat, title); } subtotal.GroupBy(g.Column, g.DisableSubtotals ? new SubtotalSummaryFunc[0] : funcs, g.PageBreaks, labFormat); g.Level = ++level; } foreach (var g in groups.Where(x => x.IsWithHeader).OrderBy(x => x.Column)) { subtotal.AddHeaders(g.Column); } var gr = groups.Union(new[] { new GroupTag { Column = 1, Level = 1 } }) .ToDictionary(x => x.Level, x => x); foreach (var subGroup in subtotal.Groups.OrderBy(x => x.Column).Reverse()) { var groupTag = gr[subGroup.Level]; FormatHeaderFooter(subGroup, groupRow); GroupRender(subGroup, groupTag); } } // Rem DoDeleteSpecialRow root.LastRow().Delete(XLShiftDeletedCells.ShiftCellsUp); }
private XLMarker(IXLRange rangeCell, Point offset) { if (rangeCell.RowCount() != 1 || rangeCell.ColumnCount() != 1) { throw new ArgumentException("Range should contain only one cell.", nameof(rangeCell)); } this.rangeCell = rangeCell; this.Offset = offset; }
/// <summary> /// ''' TODO AGGIUNGERE PARAMETRO ROUNDING OPPURE CELLFORMAT /// ''' </summary> /// ''' <param name="sheetName"></param> /// ''' <param name="labelCell"></param> /// ''' <param name="labelCellValue"></param> /// ''' <param name="excludeHeaders"></param> /// ''' <returns></returns> public ExcelHelper AddRowTotal(string sheetName, string labelCell = "A", string labelCellValue = "TOT.", bool excludeHeaders = true) { IXLRange range = this.GetRangeUsed(sheetName); if (range == null) { //SD.Log("range not found in " + sheetName, SD.LogLevel.Error); return(this); } int rowsNumber = range.RowCount(); IXLCell lastCellUsed = range.LastCellUsed(); IXLColumn lastColUsed = lastCellUsed.WorksheetColumn(); IXLRow lastRowUsed = lastCellUsed.WorksheetRow(); string lastColLetter = lastColUsed.ColumnLetter(); int lastRowNumber = lastRowUsed.RowNumber(); IXLRows rows = lastRowUsed.InsertRowsBelow(1); //IXLRow newRow = rows.Last(); var ws = this._workbook.Worksheets.Worksheet(sheetName); IXLRow newRow = ws.LastRowUsed().RowBelow(); if (labelCell.Trim() != "") { newRow.Cell(labelCell).Value = labelCellValue; newRow.Cell(labelCell).Style.Font.Bold = true; } var firstTotalCellAddress = newRow.FirstCell().CellRight().Address; var lastTotalCellAddress = newRow.Cell(lastColLetter).Address; IXLRange rangeTotal = this.GetRangeUsed(sheetName, firstTotalCellAddress, lastTotalCellAddress); //int i = rangeTotal.Cells().Count() + 1; int i = rangeTotal.ColumnCount() + 1; int firstDataRowIndex = 0; // escludo la riga delle intestazioni if (excludeHeaders) { firstDataRowIndex = 2; } for (int k = 1; k <= i; k++) { XLDataType colDataType = newRow.Cell(k).CellAbove(1).DataType; if (colDataType == XLDataType.Number) { string colLetter = newRow.Cell(k).Address.ColumnLetter; string formula = "=SUM(" + colLetter + firstDataRowIndex.ToString() + ":" + colLetter + rowsNumber.ToString() + ")"; this.AddFormula(sheetName, newRow.Cell(k).Address, formula); } } newRow.AsRange().RangeUsed().Style.Border.TopBorder = XLBorderStyleValues.Thick; return(this); }
public IXLRange ApplyHeaderFormat(IXLRange range, HeaderType axis, bool bIsOpenAspect = false) { if (range.ColumnCount() > 1 || range.RowCount() > 1) { range.Merge(); } if (axis == HeaderType.XAxis && range.ColumnCount() > 1) { range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; } else if (axis == HeaderType.YAxis && range.RowCount() > 1) { range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; } range.Style.Alignment.WrapText = axis != HeaderType.YAxis; range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; range.Style.Fill.BackgroundColor = bIsOpenAspect ? XLColor.FromTheme(XLThemeColor.Background2, 0.4) : XLColor.FromTheme(XLThemeColor.Background2); range.Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Text1); range.Style.NumberFormat.NumberFormatId = 49; // @ range.DataType = XLDataType.Text; return(range); }
/// <summary> /// This method migrates the excel sheet containing data. /// </summary> /// <param name="filePath">file path of the datasheet excel</param> /// <returns>The ID of the created TestSheet</returns> protected override int ParseTestDataSheetFile(string filePath) { //ClosedXML has been used here to read the excel files. The library can be found in Tosca Installation Directory at '%TRICENTIS_HOME%\ToscaCommander'. //Alternatively, Microsoft.Office.Interop.Excel library or other third-party library can also be used. XLWorkbook workbook = new XLWorkbook(filePath); IXLRange sheet = workbook.Worksheet(1).RangeUsed(); int testSheetId = 0; int tcInstanceCollectionId = 0; Dictionary <string, int> attributeList = new Dictionary <string, int>(); for (int row = 2; row <= sheet.RowCount(); row++) { int tcInstanceId = 0; for (int column = 1; column <= sheet.ColumnCount(); column++) { string cellValue = sheet.Row(row).Cell(column).Value.ToString(); if (string.IsNullOrEmpty(cellValue)) { continue; } switch (sheet.Row(1).Cell(column).Value.ToString()) { case "TC_Name": //Creates TestSheet testSheetId = Builder.CreateTestSheet(cellValue, Definition.TestCaseDesignFolderId); tcInstanceCollectionId = Builder.CreateInstanceCollection(testSheetId); attributeList.Clear(); break; case "Iteration": //Creates Instance of the TestSheet tcInstanceId = Builder.CreateInstance("TC_" + cellValue, tcInstanceCollectionId); break; default: if (!attributeList.ContainsKey(sheet.Row(1).Cell(column).Value.ToString())) { //Creates the attribute of the TestSheet int attributeId = Builder.CreateTDAttribute(sheet.Row(1).Cell(column).Value.ToString(), testSheetId); attributeList.Add(sheet.Row(1).Cell(column).Value.ToString(), attributeId); } //Sets the attribute value for the corresponding Instance. Builder.SetAttributeValue(cellValue, attributeList[sheet.Row(1).Cell(column).Value.ToString()], tcInstanceId); break; } } } return(testSheetId); }
public IXLRange ApplyTitleFormat(IXLRange range) { if (range.ColumnCount() > 1) { range.Merge(); } range.Style.Alignment.WrapText = true; range.Style.Border.OutsideBorder = XLBorderStyleValues.None; range.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1); range.Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Text1); range.Style.Font.Bold = true; range.Style.NumberFormat.NumberFormatId = 49; // @ range.DataType = XLDataType.Text; return(range); }
/// <summary> /// 角色相关查询 /// </summary> public void CharacterRangeSelect() { CharacterRange = book.Worksheet("角色索引").RangeUsed(); for (int i = 1; i < CharacterRange.ColumnCount(); i++) { Elements[i - 1] = CharacterRange.Cell(1, i).Value; } try { Character_Elem.Items.AddRange(Elements); } catch (ArgumentNullException) { } }
/// <summary> /// This method handles the migration of Manual TestCase /// </summary> /// <param name="filePath">File Path of the ManualTestCase Excel sheet.</param> public void ProcessManualTestCaseFile(string filePath) { //ClosedXML has been used here to read the excel files. The library can be found in Tosca Installation Directory at '%TRICENTIS_HOME%\ToscaCommander'. //Alternatively, Microsoft.Office.Interop.Excel library or other third-party library can also be used. XLWorkbook workBook = new XLWorkbook(filePath); foreach (IXLWorksheet sheet in workBook.Worksheets) { IXLRange usedRange = sheet.RangeUsed(); int testCaseId = 0; int testStepId = 0; for (int row = 2; row <= usedRange.RowCount(); row++) { for (int column = 1; column <= usedRange.ColumnCount(); column++) { string cellValue = usedRange.Row(row).Cell(column).Value.ToString(); if (!string.IsNullOrEmpty(cellValue)) { switch (usedRange.Row(1).Cell(column).Value.ToString()) { case "TestCase": //Creates TestCase testCaseId = Builder.CreateTestCase(cellValue, Definition.TestCasesFolderId); break; case "Action": //Creates ManualTestStep testStepId = Builder.CreateManualTestStep(cellValue, testCaseId, null); break; case "Input Parameter": string value = string.IsNullOrEmpty(usedRange.Row(row).Cell(column + 1).Value.ToString()) ? "" : usedRange.Row(row).Cell(column + 1).Value.ToString(); //Creates ManualTestStepValue with ActionMode as Input Builder.CreateManualTestStepValue(cellValue, testStepId, value, ActionMode.Input.ToString(), null); break; case "Expected Result": //Creates ManualTestStepValue with ActionMode as Verify Builder.CreateManualTestStepValue(cellValue, testStepId, "", ActionMode.Verify.ToString(), null); break; } } } } } }
private void prepDashboardSheets(IXLWorksheet sheet) { /*. * can use htis for both types of sheets. * Regular: List.Count -1 (end of list: should be Month) List.Count -2 (last value: Should be provider name) * Diabetes: List.Count -1 (end of list: Month) List.Count -3 (blank space between provider name an dmonth on this one) */ _worksheet = sheet; //use this one to keep track of provider name and row number. then send it to metrics to dashboard and do accordignly Dictionary<String, IXLRangeRow> providerRows = new Dictionary<String, IXLRangeRow>(); if (_worksheet != null) { var firstCell = _worksheet.FirstCellUsed(); var lastCell = _worksheet.LastCellUsed(); _worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address); if (_worksheet_range != null) { int nRows = _worksheet_range.RowCount(); int nCols = _worksheet_range.ColumnCount(); for (int i = 1; i < nRows + 1; i++) { var row = _worksheet_range.Row(i); var newRow = _worksheet_range.Row(i + 1); string value = row.Cell(1).Value as string; if (value != null) { foreach (string provider in providers) { if (value.Contains(provider)) { if (_worksheet == _dashboard.Worksheet(2))//add a new row for the depression sheets { newRow = _worksheet_range.Row(i + 3); newRow.InsertRowsBelow(1); var blankRow = _worksheet_range.Row(i + 4); blankRow.Style.NumberFormat.NumberFormatId = 0; providerRows.Add(value, blankRow); } else //add a new row for every other sheet in the dashboard: Asthma, Diabetes, Cardiovascular, Preventive { newRow = _worksheet_range.Row(i + 2);//this gets us int he right area and then insert the row above newRow.InsertRowsBelow(1); //try to insert rows after we have metrics and tehn insert metrics into cells then insert row var blankRow = _worksheet_range.Row(i + 3); blankRow.Style.NumberFormat.NumberFormatId = 0; providerRows.Add(value, blankRow); } break; //break out of the foreach provider loop, we already found one, we wont find another match, time to go to the next row instead } } } } MetricsToDashboard(providerRows, _worksheet);//figure out what we need to send to this method , worksheet, provider / row dict etc. } } }
/// <summary> /// This method migrates the TestCase excel files. /// </summary> /// <param name="filePath">File path of the TestCase excel.</param> protected override void ParseTestScriptFile(string filePath) { //ClosedXML has been used here to read the excel files. The library can be found in Tosca Installation Directory at '%TRICENTIS_HOME%\ToscaCommander'. //Alternatively, Microsoft.Office.Interop.Excel library or other third-party library can also be used. XLWorkbook workBook = new XLWorkbook(filePath); foreach (IXLWorksheet sheet in workBook.Worksheets) { IXLRange usedRange = sheet.RangeUsed(); //Builder object provides all the methods to create Tosca Business Objects. Here we create a TestCase. Also, Definition object provide the root folder IDs for import. int testCaseId = Builder.CreateTestCase(sheet.Name, Definition.TestCasesFolderId); string objectType = null; string browser = null, keyword = null, data = null; string browserName = null, pageName = null, controlName = null; int moduleId = 0, testStepId = 0, testSheetId = 0; bool isTestCaseTemplate = false; string objectrepositoryFolderPath = Definition.MigrationFolderPath + "Framework\\ObjectRepository"; for (int row = 2; row <= usedRange.RowCount(); row++) { Dictionary <string, string> technicalIdParam = new Dictionary <string, string>(); for (int column = 1; column <= usedRange.ColumnCount(); column++) { string cellValue = usedRange.Row(row).Cell(column).Value.ToString(); if (!string.IsNullOrEmpty(cellValue)) { switch (usedRange.Row(1).Cell(column).Value.ToString()) { case "Browser": browser = cellValue; break; case "Page": string[] page = cellValue.Split(new[] { ":=" }, StringSplitOptions.RemoveEmptyEntries); //Creates XModule. if (cellValue.Contains(":=")) { moduleId = Builder.CreateXModule(page[1], Definition.ModulesFolderId, new Dictionary <string, string> { { "Title", page[1] } }); } else { browserName = browser; pageName = page[page.Length - 1]; //If the Page properties are referred to Object Repository, it looks into the Object Repository XML for fetching the value. moduleId = Builder.CreateXModule(page[(page.Length - 1)], Definition.ModulesFolderId, ObjectRepository.GetModuleProperties(objectrepositoryFolderPath, browserName, pageName)); } //Creates XTestStep from XModule. testStepId = Builder.CreateXTestStepFromXModule(page[page.Length - 1], moduleId, testCaseId); break; case "Object": //ObjectMap.GetBusinessType - This method returns the corresponding Tosca Business Type for UFT object classes. objectType = ObjectMap.GetBusinessType(cellValue); break; case "Identifier": string[] objectIdentifier = cellValue.Split(new[] { ":=" }, StringSplitOptions.RemoveEmptyEntries); controlName = objectIdentifier[(objectIdentifier.Length - 1)]; //ObjectMap.GetTechnicalId - This method returns the corresponding Tosca Technical ID Type for UFT object attributes. if (cellValue.Contains(":=")) { //Control properties are defined in the excel file technicalIdParam.Add(ObjectMap.GetTechnicalId(objectIdentifier[0]), objectIdentifier[1]); } else { //Control properties referres to the Object Repository XML technicalIdParam = ObjectRepository.GetModuleAttributeProperties(objectrepositoryFolderPath, browserName, pageName, usedRange.Row(row).Cell(3).Value.ToString(), cellValue); } break; case "Keyword": keyword = cellValue; break; case "Value": data = cellValue; if (data.Contains("DT_") && !isTestCaseTemplate) { if (testSheetId == 0) { //The datasheet folder structure of the archive can be modified from here. testSheetId = ParseTestDataSheetFile(Directory.GetFiles(Definition.MigrationFolderPath + "Framework\\DataSheet")[0]); } //Converts TestCase to TestCaseTemplate Builder.ConvertTestCaseToTemplate(testCaseId, testSheetId); isTestCaseTemplate = true; } if (isTestCaseTemplate && data.Contains("DT_")) { string[] dataParts = data.Split(new[] { ':' }, StringSplitOptions.RemoveEmptyEntries); if (dataParts.Length == 3) { data = dataParts[0] + ":" + dataParts[1] + ":{XL[" + dataParts[2].Replace("DT_", "") + "]}"; } else if (dataParts.Length == 2) { data = dataParts[0] + ":{XL[" + dataParts[1].Replace("DT_", "") + "]}"; } else { data = "{XL[" + data.Replace("DT_", "") + "]}"; } } break; } } } int moduleAttributeId; if (keyword == "Open") { //Creates SpecialExecutionTask (The 'TBox Open URL' module from Standard Subset is reused here). moduleId = Builder.CreateSpecialExecutionTask("Open URL", Definition.ModulesFolderId, "Framework", "OpenUrl"); testStepId = Builder.CreateXTestStepFromXModule("Open URL", moduleId, testCaseId); moduleAttributeId = Builder.CreateSpecialExecutionTaskAttribute("Url", moduleId); Builder.SetXTestStepValue(data, testStepId, moduleAttributeId, null); } if (keyword == "CloseWindow") { //Creates SpecialExecutionTask (The 'TBox Window Operation' module from Standard Subset is reused here). moduleId = Builder.CreateSpecialExecutionTask("TBox Window Operation", Definition.ModulesFolderId, "Framework", "WindowOperation"); testStepId = Builder.CreateXTestStepFromXModule("Close Window", moduleId, testCaseId); moduleAttributeId = Builder.CreateSpecialExecutionTaskAttribute("Caption", moduleId); Builder.SetXTestStepValue(data + "*", testStepId, moduleAttributeId, null); moduleAttributeId = Builder.CreateSpecialExecutionTaskAttribute("Operation", moduleId); Builder.SetXTestStepValue("Close", testStepId, moduleAttributeId, null); } if (technicalIdParam.Count == 0) { continue; } if (objectType == "Table") { //Creates XModuleAttribute as a Table control (it adds the <Row>, <Col> and <Cell> attributes internally). moduleAttributeId = Builder.CreateXModuleAttributeAsTable(controlName, moduleId, technicalIdParam, "", ""); } else { //Creates XModuleAttribute (Page Controls) moduleAttributeId = Builder.CreateXModuleAttribute(controlName, objectType, ActionMode.Input.ToString(), moduleId, technicalIdParam); } switch (keyword) { //Sets the XTestStepValue accroding to the Keyword. case "Click": Builder.SetXTestStepValue("{Click}", testStepId, moduleAttributeId, null); break; case "SetValue": Builder.SetXTestStepValue(data, testStepId, moduleAttributeId, null); break; case "SelectValue": Builder.SetXTestStepValue(data, testStepId, moduleAttributeId, null); break; case "GetValue": Builder.SetXTestStepValue(data, testStepId, moduleAttributeId, ActionMode.Buffer.ToString()); break; case "VerifyProperty": string[] dataParts = data.Split(new[] { ':' }, StringSplitOptions.RemoveEmptyEntries); Builder.SetXTestStepValue(dataParts[0] + "=" + dataParts[1], testStepId, moduleAttributeId, ActionMode.Verify.ToString()); break; case "VerifyProperty_Table": dataParts = data.Split(new[] { ':' }, StringSplitOptions.RemoveEmptyEntries); Builder.SetXTestStepValueAsTableCell(dataParts[2], testStepId, moduleAttributeId, true, "#" + dataParts[0], "#" + dataParts[1], ActionMode.Verify.ToString()); break; } } } }
private DataTable ConvertWorksheetToDataTable(IXLWorksheet worksheet) { var dataTable = new DataTable(); List <int> formattedColumnNumbers = new List <int>(); IXLRange range = worksheet.Range(worksheet.FirstCellUsed(), worksheet.LastCellUsed()); int columnCount = range.ColumnCount(); dataTable.Clear(); for (int i = 1; i <= columnCount; i++) { IXLCell cell = worksheet.Cell(1, i); Type dataTableColumnType = GetDataType(range, cell.Address.ColumnNumber); dataTable.Columns.Add(cell.Value.ToString(), dataTableColumnType); } int firstHeadRow = 0; foreach (var row in range.Rows()) { if (firstHeadRow != 0) { var array = new object[columnCount]; for (int y = 1; y <= columnCount; y++) { if (!row.Cell(y).HasFormula) { if (row.Cell(y).Value == null || string.IsNullOrEmpty(row.Cell(y).Value.ToString())) { array[y - 1] = null; } else if (formattedColumnNumbers.Contains(row.Cell(y).Address.ColumnNumber)) { array[y - 1] = row.Cell(y).GetFormattedString(); } else if (row.Cell(y).DataType == XLDataType.DateTime) { DateTime dateTime = row.Cell(y).GetDateTime(); array[y - 1] = dateTime.TimeOfDay.Ticks == 0 ? dateTime.ToString("MM/dd/yyyy") : dateTime.ToString(); } else { row.Cell(y).SetDataType(XLDataType.Text); array[y - 1] = row.Cell(y).Value; } } } dataTable.Rows.Add(array); } firstHeadRow++; } return(dataTable); }
public IXLRange CopyTo(IXLRange range) { var firstCell = _sheet.Cell(1, 1); var lastCell = _sheet.Cell(_prevrow, _prevclmn); var tempRng = _sheet.Range(firstCell, lastCell); var rowDiff = tempRng.RowCount() - range.RowCount(); if (rowDiff > 0) { range.LastRow().RowAbove().InsertRowsBelow(rowDiff, true); } else if (rowDiff < 0) { range.Worksheet.Range( range.LastRow().RowNumber() + rowDiff + 1, range.FirstColumn().ColumnNumber(), range.LastRow().RowNumber(), range.LastColumn().ColumnNumber()) .Delete(XLShiftDeletedCells.ShiftCellsUp); } range.Worksheet.ConditionalFormats.Remove(c => c.Range.Intersects(range)); var columnDiff = tempRng.ColumnCount() - range.ColumnCount(); if (columnDiff > 0) { range.InsertColumnsAfter(columnDiff, true); } else if (columnDiff < 0) { range.Worksheet.Range( range.FirstRow().RowNumber(), range.LastColumn().ColumnNumber() + columnDiff + 1, range.LastRow().RowNumber(), range.LastColumn().ColumnNumber()) .Delete(XLShiftDeletedCells.ShiftCellsLeft); } tempRng.CopyTo(range.FirstCell()); var tgtSheet = range.Worksheet; var tgtStartRow = range.RangeAddress.FirstAddress.RowNumber; var srcRows = _sheet.Rows(tempRng.RangeAddress.FirstAddress.RowNumber, tempRng.RangeAddress.LastAddress.RowNumber); foreach (var row in srcRows) { var xlRow = tgtSheet.Row(row.RowNumber() + tgtStartRow - 1); xlRow.OutlineLevel = row.OutlineLevel; if (row.IsHidden) { xlRow.Collapse(); } else { xlRow.Expand(); } } return(range); }
private static RangeTemplate Parse(string name, IXLRange range, TempSheetBuffer buff, TemplateErrors errors, IDictionary <string, object> globalVariables) { var result = new RangeTemplate(name, range, buff, range.RowCount(), range.ColumnCount(), errors, globalVariables); var innerRanges = GetInnerRanges(range).ToArray(); var sheet = range.Worksheet; for (int iRow = 1; iRow <= result._rowCnt; iRow++) { for (int iColumn = 1; iColumn <= result._colCnt; iColumn++) { var xlCell = range.Cell(iRow, iColumn); if (innerRanges.Any(x => x.Ranges.Cells().Contains(xlCell))) { xlCell = null; } result._cells.Add(iRow, iColumn, xlCell); } if (iRow != result._rowCnt) { result._cells.AddNewRow(); } } result._mergedRanges = sheet.MergedRanges.Where(x => range.Contains(x) && !innerRanges.Any(nr => nr.Ranges.Any(r => r.Contains(x)))).ToArray(); sheet.MergedRanges.RemoveAll(result._mergedRanges.Contains); result.ParseTags(range); if (result._rowCnt > 1 && !result.IsHorizontal) { // Exclude special row result._rowCnt--; result._rowRange = range.Offset(0, 0, result._rowCnt, result._colCnt); result._optionsRow = range.LastRow(); result._optionsRowIsEmpty = !result._optionsRow.CellsUsed(XLCellsUsedOptions.AllContents | XLCellsUsedOptions.MergedRanges).Any(); } result._subranges = innerRanges.SelectMany(nrng => nrng.Ranges, (nr, rng) => { var tpl = Parse(nr.Name, rng, buff, errors, globalVariables); tpl._buff = result._buff; tpl._isSubrange = true; tpl._globalVariables = globalVariables; return(tpl); }).ToArray(); if (result._rangeOption != null) { var source = result._rangeOption.GetParameter("source"); if (!string.IsNullOrEmpty(source)) { result.Source = source; } } return(result); }
/// <summary> /// This method handles the migration of Manual TestCase /// </summary> /// <param name="filePath">File Path of the ManualTestCase Excel sheet.</param> public void ProcessManualTestCaseFile(string filePath, TCAddOnTaskContext taskContext) { //ClosedXML has been used here to read the excel files. The library can be found in Tosca Installation Directory at '%TRICENTIS_HOME%\ToscaCommander'. //Alternatively, Microsoft.Office.Interop.Excel library or other third-party library can also be used. XLWorkbook workBook = new XLWorkbook(filePath); int workSheetNumber = 0; foreach (IXLWorksheet sheet in workBook.Worksheets) { workSheetNumber++; // track worksheet number taskContext.ShowProgressInfo(workBook.Worksheets.Count, workSheetNumber, "Process worksheet: " + sheet.Name); IXLRange usedRange = sheet.RangeUsed(); int testCaseFolderId = 0; int testCaseId = 0; int testStepId = 0; string testFolderName = "not resolved"; string testCaseName = "not resolved"; string testCaseDescription = "not resolved"; string testStepName = "not resolved"; // exported hp sheet will start in row = 5 // example in row = 5 // properties in row = 4 // we only have to import "Import_Tests_QC" if (sheet.Name.Equals("Import_Tests_QC") || (workBook.Worksheets.Count == 1)) { for (int row = 1; row <= usedRange.RowCount(); row++) { //taskContext.ShowProgressInfo(usedRange.RowCount(), row, "Processed rows (Max: " + usedRange.RowCount() + ")"); // exported hp sheet has data starting at column = 2 for (int column = -5; column <= usedRange.ColumnCount(); column++) { string cellValue = usedRange.Row(row).Cell(column).Value.ToString(); //taskContext.ShowStatusInfo("Actual cell (Max: " + usedRange.ColumnCount() + ")" + cellValue + "( with subject: " + usedRange.Row(3).Cell(column).Value.ToString() + ")"); if (!string.IsNullOrEmpty(cellValue)) { switch (usedRange.Row(3).Cell(column).Value.ToString()) { case "Subject": if (testFolderName == "not resolved" || !testFolderName.Equals(cellValue)) { testFolderName = cellValue.Trim(); //Creates TestFolder testCaseFolderId = Builder.CreateFolder(testFolderName, FolderType.TestCases, Definition.TestCasesFolderId); } break; case "Test Name": if (!testCaseName.Equals(cellValue)) { testCaseName = cellValue.Trim(); //Creates TestCase testCaseId = Builder.CreateTestCase(testCaseName, testCaseDescription, testCaseFolderId); } break; case "Test Description": testCaseDescription = cellValue.Trim(); break; case "Step Name": testStepName = cellValue.Trim(); //Creates ManualTestStep testStepId = Builder.CreateManualTestStep(testStepName, testCaseId, null); break; case "Step Description": //Creates ManualTestStepValue with ActionMode as Input Builder.CreateManualTestStepValue(cellValue, testStepId, "DATA", ActionMode.Input.ToString(), null); break; case "Expected result": //Creates ManualTestStepValue with ActionMode as Verify Builder.CreateManualTestStepValue(cellValue, testStepId, "", ActionMode.Verify.ToString(), null); break; } } } } } else { taskContext.ShowStatusInfo("Not in scope: " + sheet.Name); } } }
public IXLRange CopyTo(IXLRange range) { // LastCellUsed may produce the wrong result, see https://github.com/ClosedXML/ClosedXML/issues/339 var lastCell = _sheet.Cell( _sheet.LastRowUsed(true)?.RowNumber() ?? 1, _sheet.LastColumnUsed(true)?.ColumnNumber() ?? 1); var tempRng = _sheet.Range(_sheet.Cell(1, 1), lastCell); var rowDiff = tempRng.RowCount() - range.RowCount(); if (rowDiff > 0) { range.InsertRowsBelow(rowDiff, true); } else if (rowDiff < 0) { range.Worksheet.Range( range.LastRow().RowNumber() + rowDiff + 1, range.FirstColumn().ColumnNumber(), range.LastRow().RowNumber(), range.LastColumn().ColumnNumber()) .Delete(XLShiftDeletedCells.ShiftCellsUp); } range.Worksheet.ConditionalFormats.Remove(c => c.Range.Intersects(range)); var columnDiff = tempRng.ColumnCount() - range.ColumnCount(); if (columnDiff > 0) { range.InsertColumnsAfter(columnDiff, true); } else if (columnDiff < 0) { range.Worksheet.Range( range.FirstRow().RowNumber(), range.LastColumn().ColumnNumber() + columnDiff + 1, range.LastRow().RowNumber(), range.LastColumn().ColumnNumber()) .Delete(XLShiftDeletedCells.ShiftCellsLeft); } tempRng.CopyTo(range.FirstCell()); var tgtSheet = range.Worksheet; var tgtStartRow = range.RangeAddress.FirstAddress.RowNumber; using (var srcRows = _sheet.Rows(tempRng.RangeAddress.FirstAddress.RowNumber, tempRng.RangeAddress.LastAddress.RowNumber)) foreach (var row in srcRows) { var xlRow = tgtSheet.Row(row.RowNumber() + tgtStartRow - 1); xlRow.OutlineLevel = row.OutlineLevel; if (row.IsHidden) { xlRow.Collapse(); } else { xlRow.Expand(); } } return(range); }
public static IXLRange Offset(this IXLRange range, int rowsOffset, int columnOffset) { return(Offset(range, rowsOffset, columnOffset, range.RowCount(), range.ColumnCount())); }
public static IXLRange CopyRange(IXLRange range, IXLCell cell) { IXLCell newRangeFirstCell = cell; IXLCell newRangeLastCell = ShiftCell(newRangeFirstCell, new AddressShift(range.RowCount() - 1, range.ColumnCount() - 1)); IXLRange newRange = range.Worksheet.Range(newRangeFirstCell, newRangeLastCell); if (!IsCellInsideRange(cell, range)) { newRange.Clear(); range.CopyTo(newRange); } else { // If the cell which the copy occurs to is inside the range then the copy will be wrong (copying is performed by cells, // copied cells appear in the first range immediately and start copying again) // That's why, copy through the auxiliary sheet IXLWorksheet tempWs = null; try { tempWs = AddTempWorksheet(range.Worksheet.Workbook); IXLRange tempRange = range.CopyTo(tempWs.FirstCell()); newRange.Clear(); tempRange.CopyTo(newRange); } finally { tempWs?.Delete(); } } return(newRange); }
public bool isFormatValid() { bool res = true; if (sheets != wb.Worksheets.Count) { addError("Cantidad de Hojas", "Se envio un archivo con " + wb.Worksheets.Count + " hojas, se esperaba tener " + sheets + ", solo se revisó la" + (sheets > 1 ? "s" : "") + " primera" + (sheets > 1 ? "s" : "") + " hoja" + (sheets > 1 ? "s" : "")); res = false; } for (int l = 1; l <= sheets; l++) { var sheet = wb.Worksheet(l); IXLRange UsedRange = sheet.RangeUsed(); /*if (UsedRange==null) * { * addError("Archivo Sin Datos", "No se encontró datos en el archivo subido."); * return false; * }*/ if (UsedRange.ColumnCount() != columns.Length) { addError("Cantidad de Columnas", "Se esperaba tener " + columns.Length + "columnas en la hoja: " + sheet.Name + " se encontró " + UsedRange.ColumnCount()); res = false; } if (UsedRange.LastRow().RowNumber() <= headerin) { addError("Archivo Sin Datos", "No se encontró datos en el archivo subido."); res = false; } for (int i = 1; i <= columns.Length; i++) { var comp = String.Compare( Regex.Replace(sheet.Cell(headerin, i).Value.ToString().Trim().ToUpper().Replace("_", " "), @"\t|\n|\r", " "), columns[i - 1].headers.Trim().ToUpper(), CultureInfo.CurrentCulture, CompareOptions.IgnoreNonSpace); if (comp != 0) { res = false; addError("Nombre de columna", "La columna " + i + "deberia llamarse: " + columns[i - 1].headers, false); paintXY(i, headerin, XLColor.Red, "Esta Columna deberia llamarse: " + columns[i - 1].headers); } bool tipocol = true; if (columns[i - 1].typeofcol != typeof(string)) { for (int j = headerin + 1; j < UsedRange.LastRow().RowNumber(); j++) { if (sheet.Cell(j, i).Value.GetType() != columns[i - 1].typeofcol) { res = false; var xx = sheet.Cell(j, i).Value; paintXY(i, j, XLColor.Red, "Esta Celda deberia ser tipo: " + columns[i - 1].typeofcol.Name); if (tipocol) { addError("Tipo de valor de columna", "La columna " + i + " deberia ser tipo: " + columns[i - 1].typeofcol.Name, false); tipocol = false; } } } } } } valid = valid && res; return(res); }
private List<String> ReturnMetricsRow(IXLWorksheet sheet) { List<String> metricNames = new List<string>(); _worksheet = sheet; if (_worksheet != null) { var firstCell = _worksheet.FirstCellUsed(); var lastCell = _worksheet.LastCellUsed(); _worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address); if (_worksheet_range != null) { int nRows = _worksheet_range.RowCount(); int nCols = _worksheet_range.ColumnCount(); for (int i = 1; i < nRows + 1; i++) { var row = _worksheet_range.Row(i); var newRow = _worksheet_range.Row(i + 1); string value = row.Cell(1).Value as string; if (value == "Month") { var metricRow = _worksheet_range.Row(i); for (int x = 1; x <= metricRow.CellCount(); x++) { metricNames.Add(metricRow.Cell(x).Value.ToString()); } break; } } } } return metricNames; }