private void TableHeaderStyle(IXLRange range, IXLRow row) { range.Style.Font.Bold = true; range.Style.Fill.BackgroundColor = XLColor.FromArgb(0xBFBFBF); range.Cells().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; range.Cells().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; row.Height = 33; }
private void CellBorder(IXLRange cells) { foreach (var item in cells.Cells()) { item.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; } }
protected virtual void SetBorderAndBackground(IXLRange range) { range.Style.Fill.BackgroundColor = XLColor.LightGray; foreach (IXLCell c in range.Cells()) { SetBorder(c); } }
private void SetBorderTable() { if (this.tableRange != null) { tableRange.Cells().Style.Alignment.SetWrapText(this._wrapText); this.tableRange.Style.Border.TopBorder = this.boderStyle; this.tableRange.Style.Border.BottomBorder = this.boderStyle; this.tableRange.Style.Border.LeftBorder = this.boderStyle; this.tableRange.Style.Border.RightBorder = this.boderStyle; } }
private void SetCellsDisplayFormat(IXLRange range, IList <ExcelDynamicColumn> columns) { for (int i = 0; i < columns.Count; i++) { ExcelDynamicColumn column = columns[i]; if (string.IsNullOrWhiteSpace(column.DisplayFormat) || column.DataType == null) { continue; } if (column.DataType.IsNumeric()) { range.Cells().ElementAt(i).Style.NumberFormat.Format = column.DisplayFormat; } else if (column.DataType == typeof(DateTime) || column.DataType == typeof(DateTime?)) { range.Cells().ElementAt(i).Style.DateFormat.Format = column.DisplayFormat; } } }
protected virtual void SetBorderTable() { if (this.tableRange != null) { tableRange.Cells().Style.Alignment.SetWrapText(this.setting.WrapText); this.tableRange.Style.Border.TopBorder = this.setting.BoderStyle; this.tableRange.Style.Border.BottomBorder = this.setting.BoderStyle; this.tableRange.Style.Border.LeftBorder = this.setting.BoderStyle; this.tableRange.Style.Border.RightBorder = this.setting.BoderStyle; } }
public void Range3() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1).AddToNamed("Result"); IXLRange range = wb.Range("Sheet1!Result"); Assert.IsNotNull(range); Assert.AreEqual(1, range.Cells().Count()); Assert.AreEqual(1, range.FirstCell().GetValue <Int32>()); }
public void TestCellsWithoutFormulas() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Test"); IXLRange range = ws.Range(1, 1, 10, 10); range.Cell(1, 1).Value = "Value"; range.Cell(2, 2).Value = "Value2"; range.Cell(2, 2).Active = true; range.Cell(3, 3).FormulaA1 = "=ROW()"; range.Cell(4, 4).FormulaR1C1 = "=COLUMN()"; range.Cell(5, 5).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; range.Cell(6, 6).Style.Border.OutsideBorder = XLBorderStyleValues.Thin; range.Cell(6, 6).FormulaA1 = "=A1+B2"; Assert.AreEqual(100, range.Cells().Count()); Assert.AreEqual(97, range.CellsWithoutFormulas().Count()); Assert.AreEqual(3, range.Cells(c => c.Active || c.Style.Border.TopBorder == XLBorderStyleValues.Thin).Count()); Assert.AreEqual(2, range.CellsWithoutFormulas(c => c.Active || c.Style.Border.TopBorder == XLBorderStyleValues.Thin).Count()); }
public static void AreWorksheetsContentEquals(IXLWorksheet expected, IXLWorksheet actual) { if (expected == actual) { return; } Assert.AreEqual(expected.CellsUsed(XLCellsUsedOptions.All).Count(), actual.CellsUsed(XLCellsUsedOptions.All).Count(), "Cells used count failed"); IXLCell expectedFirstCellUsed = expected.FirstCellUsed(XLCellsUsedOptions.All); IXLCell actualFirstCellUsed = actual.FirstCellUsed(XLCellsUsedOptions.All); Assert.AreEqual(expectedFirstCellUsed.Address, actualFirstCellUsed.Address, "First cell used failed"); IXLCell expectedLastCellUsed = expected.LastCellUsed(XLCellsUsedOptions.All); IXLCell actualLastCellUsed = actual.LastCellUsed(XLCellsUsedOptions.All); Assert.AreEqual(expectedLastCellUsed.Address, actualLastCellUsed.Address, "Last cell used failed"); IXLRange range = expected.Range(expectedFirstCellUsed, expectedLastCellUsed); foreach (IXLCell expectedCell in range.Cells()) { IXLCell actualCell = actual.Cell(expectedCell.Address); if (expectedCell.HasFormula) { Assert.AreEqual(expectedCell.FormulaA1, actualCell.FormulaA1, $"Cell {expectedCell.Address} FormulaA1 failed."); //// For some reason sometimes the formulas "FormulaR1C1" are different although the formulas "FormulaA1" are match //Assert.AreEqual(expectedCell.FormulaR1C1, actualCell.FormulaR1C1, $"Cell {expectedCell.Address} FormulaR1C1 failed."); Assert.AreEqual(expectedCell.FormulaReference, actualCell.FormulaReference, $"Cell {expectedCell.Address} FormulaReference failed."); } else { Assert.AreEqual(expectedCell.Value, actualCell.Value, $"Cell {expectedCell.Address} Value failed."); } Assert.AreEqual(expectedCell.DataType, actualCell.DataType, $"Cell {expectedCell.Address} DataType failed."); Assert.AreEqual(expectedCell.Active, actualCell.Active, $"Cell {expectedCell.Address} Active failed."); AreColumnsEquals(expectedCell.WorksheetColumn(), actualCell.WorksheetColumn(), $"Column {actualCell.WorksheetColumn().RangeAddress} {{0}} failed."); AreRowEquals(expectedCell.WorksheetRow(), actualCell.WorksheetRow(), $"Row {actualCell.WorksheetRow().RangeAddress} {{0}} failed."); AreCellsStyleEquals(expectedCell.Style, actualCell.Style, $"Cell {expectedCell.Address} Style {{0}} failed."); AreCellsCommentEquals(expectedCell.Comment, actualCell.Comment, $"Cell {expectedCell.Address} Comment {{0}} failed."); } AreMergedRangesEquals(expected.MergedRanges, actual.MergedRanges); AreNamedRangesEquals(expected.NamedRanges, actual.NamedRanges); ArePageSetupEquals(expected.PageSetup, actual.PageSetup, "PageSetup {0} failed."); }
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 static bool IsCellInsideRange(IXLCell cell, IXLRange range) { return(range.Cells().Contains(cell)); }
public void CreateExcel(bool a) { var workbook = new XLWorkbook(); workbook.AddWorksheet("Wochenbericht"); var ws = workbook.Worksheet("Wochenbericht"); int row = 1; ws.Cell("A" + row.ToString()).Value = "Проект"; ws.Cell("B" + row.ToString()).Value = "Кількість чохлів"; ws.Cell("C" + row.ToString()).Value = "Загальний час"; ws.Cell("D" + row.ToString()).Value = "Час на одну штуку"; ws.Cell("E" + row.ToString()).Value = "Час на салон"; ws.Cell("F" + row.ToString()).Value = "Кількість салонів"; ws.Cell("G" + row.ToString()).Value = "Середній час на одну штуку"; ws.Cell("H" + row.ToString()).Value = "Коефіцієнт/кількість компонентів"; StringBuilder str = new StringBuilder(); str.Append("Кількість компонент помножено"); str.AppendLine(); str.Append("на середній на одну штуку"); ws.Cell("I" + row.ToString()).Value = str.ToString(); ws.Cell("J" + row.ToString()).Value = "Prod. sets planned"; ws.Cell("K" + row.ToString()).Value = "Кількість бригад"; ws.Cell("L" + row.ToString()).Value = "Кількість днів"; ws.Cell("M" + row.ToString()).Value = "Кількість бригад soll"; ws.Cell("N" + row.ToString()).Value = "Кількість бригад ist"; ws.Cell("O" + row.ToString()).Value = "Коефіцієнт"; ws.Cell("P" + row.ToString()).Value = "дні"; var rngTable = ws.Range("A1:P1"); rngTable.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; rngTable.Style.Font.Bold = true; rngTable.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 255, 0); row = 2; foreach (DataGridViewRow item in dataGridView1.Rows) { if (item.Cells[0].Value.ToString() == "Підсумок") { continue; } else { ws.Cell("A" + row.ToString()).Value = item.Cells[0].Value.ToString(); ws.Cell("B" + row.ToString()).Value = item.Cells[1].Value.ToString(); ws.Cell("C" + row.ToString()).Value = item.Cells[2].Value.ToString(); ws.Cell("D" + row.ToString()).Value = item.Cells[3].Value.ToString(); ws.Cell("E" + row.ToString()).Value = item.Cells[4].Value.ToString().Replace(',', '.');; ws.Cell("F" + row.ToString()).Value = item.Cells[5].Value.ToString().Replace(',', '.');; ws.Cell("G" + row.ToString()).Value = item.Cells[6].Value.ToString().Replace(',', '.');; ws.Cell("H" + row.ToString()).Value = item.Cells[7].Value.ToString().Replace(',', '.'); ws.Cell("I" + row.ToString()).Value = item.Cells[8].Value.ToString().Replace(',', '.');; ws.Cell("J" + row.ToString()).Value = item.Cells[9].Value.ToString().Replace(',', '.');; ws.Cell("K" + row.ToString()).Value = item.Cells[10].Value.ToString().Replace(',', '.');; ws.Cell("L" + row.ToString()).Value = item.Cells[11].Value.ToString().Replace(',', '.');; ws.Cell("M" + row.ToString()).Value = item.Cells[12].Value.ToString().Replace(',', '.');; ws.Cell("N" + row.ToString()).Value = item.Cells[13].Value.ToString().Replace(',', '.');; ws.Cell("O" + row.ToString()).Value = item.Cells[14].Value.ToString().Replace(',', '.');; ws.Cell("P" + row.ToString()).Value = item.Cells[15].Value.ToString().Replace(',', '.');; row++; } } ws.Cell("A" + row.ToString()).Value = "Підсумок"; ws.Cell("F" + row.ToString()).FormulaA1 = "=SUM(F2:F8)"; ws.Cell("F" + row.ToString()).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 255, 0); ws.Cell("N" + row.ToString()).FormulaA1 = "=SUM(N2:N8)"; ws.Cell("N" + row.ToString()).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 255, 0); ws.Cell("K" + row.ToString()).FormulaA1 = "=SUM(K2:K8)"; ws.Cell("K" + row.ToString()).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 255, 0); ws.Cell("P" + row.ToString()).FormulaA1 = "=(P2*K2+P5*K5+P6*K6+P7*K7+P8*K8)/K9"; ws.Cell("P" + row.ToString()).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 255, 0); ws.Cell("J" + row.ToString()).FormulaA1 = "=(N2*J2+J5*N5+J6*N6+J7*N7+J8*N8)/N9"; ws.Cell("J" + row.ToString()).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 255, 0); ws.RangeUsed().Style.Border.OutsideBorder = XLBorderStyleValues.Thick; ws.Columns().AdjustToContents(); ws.Columns().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; IXLRange titleRange = ws.Range("A1:P9"); titleRange.Cells().Style .Alignment.SetWrapText(true); // Its single statement titleRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; titleRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; ws.Columns().Width = 11; ws.Rows().Height = 30; ws.Row(1).Height = 83; titleRange.Cells().Style.Border.OutsideBorder = XLBorderStyleValues.Thin; foreach (Saloon car in Cars) { if (car.ProjectName == "G11" || car.ProjectName == "G3") { continue; } workbook.AddWorksheet("Бригади " + car.ProjectName); ws = workbook.Worksheet("Бригади " + car.ProjectName); int row2 = 1; ws.Cell("A" + row2.ToString()).Value = "День"; ws.Cell("B" + row2.ToString()).Value = "Бригада"; ws.Cell("A" + row2.ToString()).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 255, 0); ws.Cell("B" + row2.ToString()).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 255, 0); row2 = 2; car.LD.Sort(); foreach (LineDay ld in car.LD) { ws.Cell("A" + row2.ToString()).Value = ld.Date; ws.Cell("B" + row2.ToString()).Value = ld.Name; row2++; } ws.Columns().Width = 15; ws.Cells().Style.Border.OutsideBorder = XLBorderStyleValues.Thin; ws.Cells().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; } if (a == false) { workbook.SaveAs(@"C:/test/productivity.xlsx"); } else { saveFileDialog1.Filter = "*.xlsx|"; _ = saveFileDialog1.FileName; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { workbook.SaveAs(saveFileDialog1.FileName + ".xlsx"); } } }
// ** IEnumerable public IEnumerator GetEnumerator() { return(_range.Cells().Select(GetValue).GetEnumerator()); }