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