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