private void GroupCellsHorizontal(IXLRange range, int[] groupRowNumbers) { IDictionary <int, (object StartCellValue, int StartColNum)> previousCellValues = new Dictionary <int, (object, int)>(); int colsCount = range.Columns().Count(); for (int colNum = 1; colNum <= colsCount; colNum++) { IXLRangeColumn col = range.Column(colNum); foreach (int rowNum in groupRowNumbers) { object cellValue = col.Cell(rowNum).Value; if (previousCellValues.TryGetValue(rowNum, out var previousResult)) { if (!previousResult.StartCellValue.Equals(cellValue)) { range.Range(rowNum, previousResult.StartColNum, rowNum, colNum - 1).Merge(); previousCellValues[rowNum] = (cellValue, colNum); } else if (colNum == colsCount) { range.Range(rowNum, previousResult.StartColNum, rowNum, colNum).Merge(); } } else { previousCellValues[rowNum] = (cellValue, colNum); } } } }
public static void ReadFromXml(string filePath) { XLWorkbook wb = new XLWorkbook(filePath); IXLWorksheet ws = wb.Worksheets.First(); IXLTable table = ws.Range(ws.FirstCellUsed(), ws.LastCellUsed()).AsTable(); /*string s = ""; * table.DataRange.CellsUsed().Count(); * table.DataRange.Rows().ForEach(row => * { * s += "\n"; * row.Cells().ToList().ForEach(cell => * { * s += cell.GetString()+"\t|||\t"; * }); * }); * if (true) * { * * }*/ TimeTable orar = new TimeTable(); IXLCell grupaCell = null; #region grupa //cauta coloana care contine grupa IXLRangeColumn grupaColoana = table.DataRange.FindColumn(column => { grupaCell = column.Cells()?.FirstOrDefault(cell => cell.GetString().Equals("Grupa")); return(grupaCell != null); //return column.Cells().Any(cell => cell.GetString().Equals("Grupa")); }); int id = 1; //insereaza grupele care apar in excel in orar grupaColoana.CellsUsed().Where(cell => !cell.GetString().Equals("Grupa")).ForEach(cell => { orar.Groups.Add(new GroupDto(id++, cell.GetString())); }); #endregion table.Range(grupaCell.Address.RowNumber + 1, grupaCell.Address.ColumnNumber + 2, table.LastCellUsed().Address.RowNumber, table.LastCellUsed().Address.ColumnNumber) .CellsUsed().Where(cell => !table.Cell(cell.Address.RowNumber, 3).IsEmpty()).ForEach(cell => { string[] split = cell.GetString().Split(",".ToCharArray()); //Programare prog = new Programare(); string materie = split[0]; string tipMaterie = split[1]; string sala = split[2]; string profesor = split[3]; }); }
public void ColumnUsed() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.Worksheets.Add("Sheet1"); ws.Cell(2, 1).SetValue("Test"); ws.Cell(3, 1).SetValue("Test"); IXLRangeColumn fromColumn = ws.Column(1).ColumnUsed(); Assert.AreEqual("A2:A3", fromColumn.RangeAddress.ToStringRelative()); IXLRangeColumn fromRange = ws.Range("A1:A5").FirstColumn().ColumnUsed(); Assert.AreEqual("A2:A3", fromRange.RangeAddress.ToStringRelative()); }
public void TableRange() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.Worksheets.Add("Sheet1"); IXLRangeColumn rangeColumn = ws.Column(1).Column(1, 4); rangeColumn.Cell(1).Value = "FName"; rangeColumn.Cell(2).Value = "John"; rangeColumn.Cell(3).Value = "Hank"; rangeColumn.Cell(4).Value = "Dagny"; IXLTable table = rangeColumn.CreateTable(); wb.NamedRanges.Add("FNameColumn", String.Format("{0}[{1}]", table.Name, "FName")); IXLRange namedRange = wb.Range("FNameColumn"); Assert.AreEqual(3, namedRange.Cells().Count()); Assert.IsTrue( namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] { "John", "Hank", "Dagny" })); }
public void Add(IXLRangeColumn range) { _ranges.Add((XLRangeColumn)range); }