private void setBorderAndBg(IXLWorksheet ws, string Range, XLBorderStyleValues v, XLColor bgcolor, bool fontBold, bool wrapText)
{
try
{
setBorderAndBg(ws, Range, v, bgcolor, fontBold);
ws.Range(Range).Style.Alignment.WrapText = wrapText;
}
catch { }
}
private void setBorderAndBg(IXLWorksheet ws, string Range, XLBorderStyleValues v, XLColor bgcolor, bool fontBold, bool wrapText, double fontSize)
{
try
{
setBorderAndBg(ws, Range, v, bgcolor, fontBold, wrapText);
ws.Cell(Range).WorksheetColumn().Width = fontSize;
}
catch { }
}
private void setBorderAndBg(IXLWorksheet ws, string Range, XLBorderStyleValues v, XLColor bgcolor, bool fontBold)
{
try
{
setBorderAndBg(ws, Range, v, bgcolor);
ws.Range(Range).Style.Font.Bold = fontBold;
}
catch { }
}
private void setBorderAndBg(IXLWorksheet ws, string Range, XLBorderStyleValues v, XLColor bgcolor)
{
try
{
setBorderAndBg(ws, Range, v);
ws.Range(Range).Style.Fill.BackgroundColor = bgcolor;
}
catch { }
}
private bool AreEquivalent(
XLBorderStyleValues borderStyle1, XLColorKey color1,
XLBorderStyleValues borderStyle2, XLColorKey color2)
{
return((borderStyle1 == XLBorderStyleValues.None &&
borderStyle2 == XLBorderStyleValues.None) ||
borderStyle1 == borderStyle2 &&
color1 == color2);
}
private void addBottomBorder(IXLCell cell, XLBorderStyleValues border = XLBorderStyleValues.Thick, XLColor color = null)
{
if (color == null)
{
color = XLColor.Black;
}
cell.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
cell.Style.Border.BottomBorderColor = XLColor.Black;
}
private void addRightBottomBorder(IXLCell cell, XLBorderStyleValues border = XLBorderStyleValues.Thick, XLColor color = null)
{
if (color == null)
{
color = XLColor.Black;
}
addBottomBorder(cell, border, color);
addRightBorder(cell, border, color);
}
private void setBorderAndBg(IXLWorksheet ws, string Range, XLBorderStyleValues v)
{
try
{
ws.Range(Range).Style.Border.LeftBorder = v;
ws.Range(Range).Style.Border.RightBorder = v;
ws.Range(Range).Style.Border.TopBorder = v;
ws.Range(Range).Style.Border.BottomBorder = v;
}
catch { }
}
public XLBorder(IXLStylized container, IXLBorder defaultBorder, Boolean useDefaultModify = true)
{
_container = container;
if (defaultBorder == null)
{
return;
}
_leftBorder = defaultBorder.LeftBorder;
_leftBorderColor = new XLColor(defaultBorder.LeftBorderColor);
_rightBorder = defaultBorder.RightBorder;
_rightBorderColor = new XLColor(defaultBorder.RightBorderColor);
_topBorder = defaultBorder.TopBorder;
_topBorderColor = new XLColor(defaultBorder.TopBorderColor);
_bottomBorder = defaultBorder.BottomBorder;
_bottomBorderColor = new XLColor(defaultBorder.BottomBorderColor);
_diagonalBorder = defaultBorder.DiagonalBorder;
_diagonalBorderColor = new XLColor(defaultBorder.DiagonalBorderColor);
_diagonalUp = defaultBorder.DiagonalUp;
_diagonalDown = defaultBorder.DiagonalDown;
if (useDefaultModify)
{
var d = defaultBorder as XLBorder;
BottomBorderColorModified = d.BottomBorderColorModified;
BottomBorderModified = d.BottomBorderModified;
DiagonalBorderColorModified = d.DiagonalBorderColorModified;
DiagonalBorderModified = d.DiagonalBorderModified;
DiagonalDownModified = d.DiagonalDownModified;
DiagonalUpModified = d.DiagonalUpModified;
LeftBorderColorModified = d.LeftBorderColorModified;
LeftBorderModified = d.LeftBorderModified;
RightBorderColorModified = d.RightBorderColorModified;
RightBorderModified = d.RightBorderModified;
TopBorderColorModified = d.TopBorderColorModified;
TopBorderModified = d.TopBorderModified;
}
}
public IXLStyle SetTopBorder(XLBorderStyleValues value)
{
TopBorder = value;
return _container.Style;
}
public IXLStyle SetRightBorder(XLBorderStyleValues value)
{
RightBorder = value;
return _container.Style;
}
public IXLStyle SetOutsideBorder(XLBorderStyleValues value)
{
OutsideBorder = value;
return _container.Style;
}
public IXLStyle SetDiagonalBorder(XLBorderStyleValues value)
{
DiagonalBorder = value;
return _container.Style;
}
public IXLStyle SetBottomBorder(XLBorderStyleValues value)
{
BottomBorder = value;
return _container.Style;
}
private void SetAllBorders(IXLRange range, XLBorderStyleValues borderStyle)
{
range.Style.Border.OutsideBorder = borderStyle;
range.Style.Border.InsideBorder = borderStyle;
}
internal async Task <IXLCell> SetStyleToCell(IXLCell cell, int fontSize = 12, string fontName = "Segoe UI", bool isFontBold = false,
XLColor backgroundColor = null, XLAlignmentHorizontalValues horizontalAlignment = XLAlignmentHorizontalValues.Center,
XLAlignmentVerticalValues verticalAlignment = XLAlignmentVerticalValues.Center, XLColor outsideBorderColor = null, XLBorderStyleValues outsideBorder = XLBorderStyleValues.Dashed)
{
cell.Style.Font.FontSize = fontSize;
cell.Style.Font.FontName = fontName;
cell.Style.Font.Bold = isFontBold;
cell.Style.Border.OutsideBorder = outsideBorder;
cell.Style.Border.OutsideBorderColor = outsideBorderColor ?? XLColor.LightSlateGray;
cell.Style.Alignment.SetHorizontal(horizontalAlignment);
cell.Style.Alignment.SetVertical(verticalAlignment);
cell.Style.Alignment.SetWrapText();
if (backgroundColor != null)
{
cell.Style.Fill.BackgroundColor = backgroundColor;
}
return(cell);
}
private void addBottomBorder(IXLCell cell, XLBorderStyleValues border = XLBorderStyleValues.Thick, XLColor color = null)
{
if (color == null)
{
color = XLColor.Black;
}
cell.Style.Border.BottomBorder = XLBorderStyleValues.Thick;
cell.Style.Border.BottomBorderColor = XLColor.Black;
}
public static XLWorkbook GetExcel(TableModel[] models)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet sheet = wb.Worksheets.Add("Sheet1");
// ЗАГОЛОВКИ
IXLRange range = sheet.Range(sheet.Cell(1, "A"), sheet.Cell(2, "I"));
// заливка
range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#fcecdc"));
// жирный шрифт
range.Style.Font.SetBold();
// выравнивание
range.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
range.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
// ширина заголовков
double factor = 1.145; // множитель (чтобы ширина была как у оригинального excel)
sheet.Column("A").Width = 24.13 * factor;
sheet.Column("B").Width = 33.63 * factor;
sheet.Column("C").Width = 11.88 * factor;
sheet.Column("D").Width = 9.25 * factor;
sheet.Column("E").Width = 10.5 * factor;
sheet.Column("F").Width = 52.75 * factor;
sheet.Column("G").Width = 8.75 * factor;
sheet.Column("H").Width = 8.75 * factor;
sheet.Column("I").Width = 33.25 * factor;
// подписи у заголовков и объединение ячеек для заголовков
sheet.Range(sheet.Cell(1, "A"), sheet.Cell(2, "A"))
.Merge().Value = "Component type";
sheet.Range(sheet.Cell(1, "B"), sheet.Cell(2, "B"))
.Merge().Value = "Name";
sheet.Range(sheet.Cell(1, "C"), sheet.Cell(2, "C"))
.Merge().Value = "Nominal";
sheet.Range(sheet.Cell(1, "D"), sheet.Cell(2, "D"))
.Merge().Value = "Deviation";
sheet.Range(sheet.Cell(1, "E"), sheet.Cell(2, "E"))
.Merge().Value = "Case type";
sheet.Range(sheet.Cell(1, "F"), sheet.Cell(2, "F"))
.Merge().Value = "Comment";
sheet.Range(sheet.Cell(1, "G"), sheet.Cell(1, "H"))
.Merge().Value = "quantity";
sheet.Cell(2, "G").Value = "00";
sheet.Cell(2, "H").Value = "01";
sheet.Range(sheet.Cell(1, "I"), sheet.Cell(2, "I"))
.Merge().Value = "Remark:";
// границы у заголовков
XLBorderStyleValues styleBorder = XLBorderStyleValues.Medium;
range.Style.Border.SetOutsideBorder(styleBorder);
range.Style.Border.SetInsideBorder(styleBorder);
#region ЛОГИКА ВЫВОДА ДАННЫХ
int currentRow = 3;
IXLCells cellRange;
for (int i = 0; i < models.Length; i++)
{
TableModel tm = models[i];
#region ЛОГИКА ОТДЕЛЕНИЯ ГРУПП КОМПОНЕНТОВ
// чтобы не выйти за пределы массива
if (i > 0 && tm.ComponentType != models[i - 1].ComponentType)
{
range = sheet.Range(sheet.Cell(currentRow, "A"), sheet.Cell(currentRow, "i")).Merge();
// границы
range.Style.Border.SetOutsideBorder(XLBorderStyleValues.Medium);
// заливка
range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#f8f4f4"));
currentRow++;
}
#endregion
cellRange = sheet.Row(currentRow).Cells("A", "I");
// выравнивание текста
cellRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
cellRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
// границы
styleBorder = XLBorderStyleValues.Thin;
cellRange.Style.Border.SetInsideBorder(styleBorder);
cellRange.Style.Border.SetOutsideBorder(styleBorder);
sheet.Cell(currentRow, "A").Value = tm.ComponentType;
sheet.Cell(currentRow, "B").Value = tm.Name;
sheet.Cell(currentRow, "C").Value = tm.Nominal;
sheet.Cell(currentRow, "D").Value = tm.Deviation;
sheet.Cell(currentRow, "E").SetValue(tm.CaseType);
sheet.Cell(currentRow, "F").Value = tm.Comment;
sheet.Cell(currentRow, "G").Value = tm.Quanity.ZeroZero;
sheet.Cell(currentRow, "H").Value = tm.Quanity.ZeroOne;
currentRow++;
}
#endregion
// СТИЛИ ДЛЯ ВСЕХ ЯЧЕЕК
cellRange = sheet.CellsUsed();
// шрифт
cellRange.Style.Font.SetFontName("Arial Cyr");
cellRange.Style.Font.SetFontSize(9);
return(wb);
}
public static IXLCell AddLeftBorder(this IXLCell cell, XLBorderStyleValues borderStyle = XLBorderStyleValues.Thin, XLColor color = null)
{
cell.AsRange().AddLeftBorder(borderStyle, color);
return(cell);
}
private void addRightBottomBorder(IXLCell cell, XLBorderStyleValues border = XLBorderStyleValues.Thick, XLColor color = null)
{
if (color == null)
{
color = XLColor.Black;
}
addBottomBorder(cell, border, color);
addRightBorder(cell, border, color);
}
public IXLStyle SetDiagonalBorder(XLBorderStyleValues value)
{
DiagonalBorder = value;
return(_container.Style);
}
public void DrawRightBorder(int row, int col, XLBorderStyleValues Style, XLColor Color)
{
ws.Cell(row, col).Style.Border.RightBorder = Style;
ws.Cell(row, col).Style.Border.RightBorderColor = Color;
}
public IXLStyle SetBottomBorder(XLBorderStyleValues value)
{
BottomBorder = value;
return(_container.Style);
}
public IXLStyle SetTopBorder(XLBorderStyleValues value)
{
TopBorder = value;
return(_container.Style);
}
public IXLStyle SetInsideBorder(XLBorderStyleValues value)
{
InsideBorder = value;
return(_container.Style);
}
public Row BorderLeft(XLBorderStyleValues style, XLColor color)
{
_row.Style
.Border.SetLeftBorder(style)
.Border.SetLeftBorderColor(color);
return this;
}
public XLBorder(IXLStylized container, IXLBorder defaultBorder, Boolean useDefaultModify = true)
{
_container = container;
if (defaultBorder == null) return;
_leftBorder = defaultBorder.LeftBorder;
_leftBorderColor = defaultBorder.LeftBorderColor;
_rightBorder = defaultBorder.RightBorder;
_rightBorderColor = defaultBorder.RightBorderColor;
_topBorder = defaultBorder.TopBorder;
_topBorderColor = defaultBorder.TopBorderColor;
_bottomBorder = defaultBorder.BottomBorder;
_bottomBorderColor = defaultBorder.BottomBorderColor;
_diagonalBorder = defaultBorder.DiagonalBorder;
_diagonalBorderColor = defaultBorder.DiagonalBorderColor;
_diagonalUp = defaultBorder.DiagonalUp;
_diagonalDown = defaultBorder.DiagonalDown;
if (useDefaultModify)
{
var d = defaultBorder as XLBorder;
BottomBorderColorModified = d.BottomBorderColorModified;
BottomBorderModified = d.BottomBorderModified;
DiagonalBorderColorModified = d.DiagonalBorderColorModified;
DiagonalBorderModified = d.DiagonalBorderModified;
DiagonalDownModified = d.DiagonalDownModified;
DiagonalUpModified = d.DiagonalUpModified;
LeftBorderColorModified = d.LeftBorderColorModified;
LeftBorderModified = d.LeftBorderModified;
RightBorderColorModified = d.RightBorderColorModified;
RightBorderModified = d.RightBorderModified;
TopBorderColorModified = d.TopBorderColorModified;
TopBorderModified = d.TopBorderModified;
}
}
