public void CanConvertXlColorToX14ColorType()
{
var xlColor1 = XLColor.Red;
var xlColor2 = XLColor.FromIndex(20);
var xlColor3 = XLColor.FromTheme(XLThemeColor.Accent1);
var xlColor4 = XLColor.FromTheme(XLThemeColor.Accent2, 0.4);
var color1 = new X14.AxisColor().FromClosedXMLColor <X14.AxisColor>(xlColor1);
var color2 = new X14.BorderColor().FromClosedXMLColor <X14.BorderColor>(xlColor2);
var color3 = new X14.FillColor().FromClosedXMLColor <X14.FillColor>(xlColor3);
var color4 = new X14.HighMarkerColor().FromClosedXMLColor <X14.HighMarkerColor>(xlColor4);
Assert.AreEqual("FFFF0000", color1.Rgb.Value);
Assert.IsNull(color1.Indexed);
Assert.IsNull(color1.Theme);
Assert.IsNull(color1.Tint);
Assert.IsNull(color2.Rgb);
Assert.AreEqual(20, color2.Indexed.Value);
Assert.IsNull(color2.Theme);
Assert.IsNull(color2.Tint);
Assert.IsNull(color3.Rgb);
Assert.IsNull(color3.Indexed);
Assert.AreEqual(4, color3.Theme.Value);
Assert.IsNull(color3.Tint);
Assert.IsNull(color4.Rgb);
Assert.IsNull(color4.Indexed);
Assert.AreEqual(5, color4.Theme.Value);
Assert.AreEqual(0.4, color4.Tint.Value);
}
/// <summary>
/// Here we perform the actual convertion from OpenXML color to ClosedXML color.
/// </summary>
/// <param name="openXMLColor">OpenXML color. Must be either <see cref="ColorType"/> or <see cref="X14.ColorType"/>.
/// Since these types do not implement a common interface we use dynamic.</param>
/// <param name="colorCache">The dictionary containing parsed colors to optimize performance.</param>
/// <returns>The color in ClosedXML format.</returns>
private static XLColor ConvertToClosedXMLColor(dynamic openXMLColor, IDictionary <string, Drawing.Color> colorCache)
{
XLColor retVal = null;
if (openXMLColor != null)
{
if (openXMLColor.Rgb != null)
{
String htmlColor = "#" + openXMLColor.Rgb.Value;
Drawing.Color thisColor;
if (colorCache?.ContainsKey(htmlColor) ?? false)
{
thisColor = colorCache[htmlColor];
}
else
{
thisColor = ColorStringParser.ParseFromHtml(htmlColor);
colorCache?.Add(htmlColor, thisColor);
}
retVal = XLColor.FromColor(thisColor);
}
else if (openXMLColor.Indexed != null && openXMLColor.Indexed <= 64)
{
retVal = XLColor.FromIndex((Int32)openXMLColor.Indexed.Value);
}
else if (openXMLColor.Theme != null)
{
retVal = openXMLColor.Tint != null
? XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value, openXMLColor.Tint.Value)
: XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value);
}
}
return(retVal ?? XLColor.NoColor);
}
public void CanConvertXLColorToColorType()
{
var xlColor1 = XLColor.Red;
var xlColor2 = XLColor.FromIndex(20);
var xlColor3 = XLColor.FromTheme(XLThemeColor.Accent1);
var xlColor4 = XLColor.FromTheme(XLThemeColor.Accent2, 0.4);
var color1 = new ForegroundColor().FromClosedXMLColor <ForegroundColor>(xlColor1);
var color2 = new ForegroundColor().FromClosedXMLColor <ForegroundColor>(xlColor2);
var color3 = new BackgroundColor().FromClosedXMLColor <BackgroundColor>(xlColor3);
var color4 = new BackgroundColor().FromClosedXMLColor <BackgroundColor>(xlColor4);
Assert.AreEqual("FFFF0000", color1.Rgb.Value);
Assert.IsNull(color1.Indexed);
Assert.IsNull(color1.Theme);
Assert.IsNull(color1.Tint);
Assert.IsNull(color2.Rgb);
Assert.AreEqual(20, color2.Indexed.Value);
Assert.IsNull(color2.Theme);
Assert.IsNull(color2.Tint);
Assert.IsNull(color3.Rgb);
Assert.IsNull(color3.Indexed);
Assert.AreEqual(4, color3.Theme.Value);
Assert.IsNull(color3.Tint);
Assert.IsNull(color4.Rgb);
Assert.IsNull(color4.Indexed);
Assert.AreEqual(5, color4.Theme.Value);
Assert.AreEqual(0.4, color4.Tint.Value);
}
/// <summary>
/// Here we perform the actual convertion from OpenXML color to ClosedXML color.
/// </summary>
/// <param name="openXMLColor">OpenXML color. Must be either <see cref="ColorType"/> or <see cref="X14.ColorType"/>.
/// Since these types do not implement a common interface we use dynamic.</param>
/// <param name="colorCache">The dictionary containing parsed colors to optimize performance.</param>
/// <returns>The color in ClosedXML format.</returns>
private static XLColor ConvertToClosedXMLColor(IColorTypeAdapter openXMLColor, IDictionary <string, Drawing.Color> colorCache)
{
XLColor retVal = null;
if (openXMLColor != null)
{
if (openXMLColor.Rgb != null)
{
String htmlColor = "#" + openXMLColor.Rgb.Value;
if (colorCache == null || !colorCache.TryGetValue(htmlColor, out Drawing.Color thisColor))
{
thisColor = ColorStringParser.ParseFromHtml(htmlColor);
colorCache?.Add(htmlColor, thisColor);
}
retVal = XLColor.FromColor(thisColor);
}
else if (openXMLColor.Indexed != null && openXMLColor.Indexed <= 64)
{
retVal = XLColor.FromIndex((Int32)openXMLColor.Indexed.Value);
}
else if (openXMLColor.Theme != null)
{
retVal = openXMLColor.Tint != null
? XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value, openXMLColor.Tint.Value)
: XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value);
}
}
return(retVal ?? XLColor.NoColor);
}
private static void FillRow(IXLRow row1)
{
row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromColor(Color.MediumSeaGreen));
row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
row1.Cell(2).AddConditionalFormat().WhenEquals("=" + row1.FirstCell().CellRight(6).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue);
}
// Public
// Private
#endregion
#region Properties
// Public
// Private
// Override
#endregion
#region Events
// Public
// Private
// Override
#endregion
#region Methods
// Public
public void Create(String filePath)
{
var wb = new XLWorkbook();
var wsRed = wb.Worksheets.Add("Red").SetTabColor(XLColor.Red);
var wsAccent3 = wb.Worksheets.Add("Accent3").SetTabColor(XLColor.FromTheme(XLThemeColor.Accent3));
var wsIndexed = wb.Worksheets.Add("Indexed");
wsIndexed.TabColor = XLColor.FromIndex(24);
var wsArgb = wb.Worksheets.Add("Argb");
wsArgb.TabColor = XLColor.FromArgb(23, 23, 23);
wb.SaveAs(filePath);
}
/// <summary>
/// https://github.com/ClosedXML/ClosedXML/wiki/Tab-Colors
/// </summary>
/// <returns></returns>
public ActionResult TabColors()
{
GetInstance("Red", out XLWorkbook wb, out IXLWorksheet ws);
ws.SetTabColor(XLColor.Red);
var wsAccent3 = wb.Worksheets.Add("Accent3");
wsAccent3.SetTabColor(XLColor.FromTheme(XLThemeColor.Accent3));
var wsIndexed = wb.Worksheets.Add("Indexed");
wsIndexed.TabColor = XLColor.FromIndex(24);
var wsArgb = wb.Worksheets.Add("Argb");
wsArgb.TabColor = XLColor.FromArgb(23, 23, 23);
return(ExportExcel(wb, "TabColors"));
}
public void CopyingColumns()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLColumn column1 = ws.Column(1);
column1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
column1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
column1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
column1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
column1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromColor(Color.MediumSeaGreen));
column1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
column1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
ws.Cell(1, 2).Value = column1;
ws.Cell(1, 3).Value = column1.Column(1, 7);
IXLColumn column2 = ws.Column(2);
Assert.AreEqual(XLColor.Red, column2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), column2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), column2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), column2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen),
column2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), column2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), column2.Cell(7).Style.Fill.BackgroundColor);
IXLColumn column3 = ws.Column(3);
Assert.AreEqual(XLColor.Red, column3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), column3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), column3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), column3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen),
column3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), column3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), column3.Cell(7).Style.Fill.BackgroundColor);
}
public void FillsWithTransparentColorEqual()
{
var fill1 = new XLFill {
BackgroundColor = XLColor.ElectricUltramarine, PatternType = XLFillPatternValues.None
};
var fill2 = new XLFill {
BackgroundColor = XLColor.EtonBlue, PatternType = XLFillPatternValues.None
};
var fill3 = new XLFill {
BackgroundColor = XLColor.FromIndex(64)
};
var fill4 = new XLFill {
BackgroundColor = XLColor.NoColor
};
Assert.IsTrue(fill1.Equals(fill2));
Assert.IsTrue(fill1.Equals(fill3));
Assert.IsTrue(fill1.Equals(fill4));
Assert.AreEqual(fill1.GetHashCode(), fill2.GetHashCode());
Assert.AreEqual(fill1.GetHashCode(), fill3.GetHashCode());
Assert.AreEqual(fill1.GetHashCode(), fill4.GetHashCode());
}
public void CopyingRows()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
ws.Cell(2, 1).Value = row1;
ws.Cell(3, 1).Value = row1.Row(1, 7);
IXLRow row2 = ws.Row(2);
Assert.AreEqual(XLColor.Red, row2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row2.Cell(7).Style.Fill.BackgroundColor);
IXLRow row3 = ws.Row(3);
Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
}
public void CopyingRows()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLRow row1 = ws.Row(1);
FillRow(row1);
ws.Cell(2, 1).Value = row1;
ws.Cell(3, 1).Value = row1.Row(1, 7);
IXLRow row2 = ws.Row(2);
Assert.AreEqual(XLColor.Red, row2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen), row2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row2.Cell(7).Style.Fill.BackgroundColor);
IXLRow row3 = ws.Row(3);
Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), row3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
Assert.AreEqual(3, ws.ConditionalFormats.Count());
Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B1:B1").Values.Any(v => v.Value.Value == "G1" && v.Value.IsFormula));
Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B2:B2").Values.Any(v => v.Value.Value == "G2" && v.Value.IsFormula));
Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B3:B3").Values.Any(v => v.Value.Value == "G3" && v.Value.IsFormula));
}
public void Create(String filePath)
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Using Colors");
Int32 ro = 0;
// From Known color
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.Red;
ws.Cell(ro, 2).Value = "XLColor.Red";
// From Color not so known
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.Byzantine;
ws.Cell(ro, 2).Value = "XLColor.Byzantine";
ro++;
// FromArgb(Int32 argb) using Hex notation
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0xFF00FF);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(0xFF00FF)";
// FromArgb(Int32 argb) using an integer (you need to convert the hex value to an int)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(16711935);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(16711935)";
// FromArgb(Int32 r, Int32 g, Int32 b)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(255, 0, 255);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(255, 0, 255)";
// FromArgb(Int32 a, Int32 r, Int32 g, Int32 b)
// Note: Excel ignores the alpha value
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 255, 0, 255);
ws.Cell(ro, 2).Value = "XLColor.FromArgb(0, 255, 0, 255)";
ro++;
// FromColor(Color color)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromColor(Color.Red);
ws.Cell(ro, 2).Value = "XLColor.FromColor(Color.Red)";
ro++;
// FromHtml(String htmlColor)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromHtml("#FF996515");
ws.Cell(ro, 2).Value = "XLColor.FromHtml(\"#FF996515\")";
ro++;
// FromIndex(Int32 indexedColor)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromIndex(25);
ws.Cell(ro, 2).Value = "XLColor.FromIndex(25)";
ro++;
// FromKnownColor(KnownColor knownColor)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromKnownColor(KnownColor.Plum);
ws.Cell(ro, 2).Value = "XLColor.FromKnownColor(KnownColor.Plum)";
ro++;
// FromName(String colorName)
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromName("PowderBlue");
ws.Cell(ro, 2).Value = "XLColor.FromName(\"PowderBlue\")";
ro++;
// From Theme color
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1);
ws.Cell(ro, 2).Value = "XLColor.FromTheme(XLThemeColor.Accent1)";
// From Theme color with tint
ws.Cell(++ro, 1).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
ws.Cell(ro, 2).Value = "XLColor.FromTheme(XLThemeColor.Accent1, 0.5)";
ws.Columns().AdjustToContents();
wb.SaveAs(filePath);
}
public void ExportToExcel()
{
Dictionary <string, int> dct;
Type keyType;
if (ch == 0)
{
dct = filtered.Select((x, i) => new { i, x.NameOfGroup })
.ToDictionary(k => k.NameOfGroup, e => e.i);
keyType = typeof(Group);
}
else if (ch == -1)
{
dct = filteredTeacher.Select((x, i) => new { i, x.FIO })
.ToDictionary(k => k.FIO, e => e.i);
keyType = typeof(Teacher);
}
else
{
dct = filteredClassroom.Select((x, i) => new { i, x.NumberOfClassroom })
.ToDictionary(k => k.NumberOfClassroom, e => e.i);
keyType = typeof(ClassRoom);
}
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Лист1");
for (int r = 0; r < maxpair; r++)
{
worksheet.Cell(r + 2, 1).Style.Alignment.TextRotation = 90;
worksheet.Cell(r + 2, 1).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(r + 2, 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(r + 2, 1).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(r + 2, 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(r + 2, 1).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(r + 2, 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(r + 2, 1).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(r + 2, 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(r + 2, 1).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Row(r + 2).Height = 25;
worksheet.Cell(r + 2, 1).Style.Alignment.WrapText = true;
worksheet.Cell(r + 2, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(r + 2, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(r + 2, 1).RichText.FontSize = 20;
worksheet.Cell(r + 2, 1).RichText.FontColor = XLColor.Black;
worksheet.Cell(r + 2, 1).RichText.FontName = "Broadway";
string str = "";
if (r / 6 < 1)
{
str = "Понедельник";
worksheet.Cell(r + 2, 1).Value = str;
worksheet.Range("A2:A7").Column(1).Merge();
}
else
if ((r / 6 < 2) && (r / 6 >= 1))
{
str = "Вторник";
worksheet.Cell(r + 2, 1).Value = str;
worksheet.Range("A8:A13").Column(1).Merge();
}
else
if ((r / 6 < 3) && (r / 6 >= 2))
{
str = "Среда";
worksheet.Cell(r + 2, 1).Value = str;
worksheet.Range("A14:A19").Column(1).Merge();
}
else
if ((r / 6 < 4) && (r / 6 >= 3))
{
str = "Четверг";
worksheet.Cell(r + 2, 1).Value = str;
worksheet.Range("A20:A25").Column(1).Merge();
}
else
if ((r / 6 < 5) && (r / 6 >= 4))
{
str = "Пятница";
worksheet.Cell(r + 2, 1).Value = str;
worksheet.Range("A26:A31").Column(1).Merge();
}
else
{
str = "Суббота";
worksheet.Cell(r + 2, 1).Value = str;
worksheet.Range("A32:A34").Column(1).Merge();
}
}
string[] strPair = { "I 8:30 - 10:05", "II 10:20 - 11:55", "III 12:10 - 13:45", "IV 14:15 - 15:50", "V 16:05 - 17:40", "VI 17:50 - 19:25" };
for (int r = 1; r <= maxpair; r++)
{
worksheet.Cell(r + 1, 2).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(r + 1, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(r + 1, 2).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(r + 1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(r + 1, 2).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(r + 1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(r + 1, 2).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(r + 1, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(r + 1, 2).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(r + 1, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(r + 1, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Row(r + 1).Height = 25;
worksheet.Column(2).Width = 20;
worksheet.Cell(r + 1, 2).Value = strPair[(r - 1) % strPair.Length];
}
if (ch == 0)
{
for (int c = 0; c < filtered.Length; c++)
{
worksheet.Column(3 + c).Width = 40;
worksheet.Cell(1, 3 + c).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(1, 3 + c).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(1, 3 + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(1, 3 + c).Value = filtered[c].NameOfGroup;
}
}
else if (ch == -1)
{
for (int c = 0; c < filteredTeacher.Length; c++)
{
worksheet.Column(3 + c).Width = 40;
worksheet.Cell(1, 3 + c).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(1, 3 + c).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(1, 3 + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(1, 3 + c).Value = filteredTeacher[c].FIO;
}
}
else
{
for (int c = 0; c < filteredClassroom.Length; c++)
{
worksheet.Column(3 + c).Width = 40;
worksheet.Cell(1, 3 + c).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(1, 3 + c).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(1, 3 + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(1, 3 + c).Value = filteredClassroom[c].NumberOfClassroom;
}
}
var temp = Data.Select(x => x.ToArray()).ToArray();
for (int i = 0; i < temp.Length; i++)
{
for (int j = 0; j < temp[0].Length; j++)
{
if (ch == 0)
{
int cind;
if (temp[i][j].Item.Group != null)
{
if (dct.TryGetValue(temp[i][j].Item.Group, out cind))
{
Data[i][cind].Item = temp[i][j].Item;
worksheet.Cell(i + 2, 2 + cind + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(i + 2, 2 + cind + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(i + 2, 2 + cind + 1).Value = Data[i][cind].Item.NumberOfClassroom + " " + Data[i][cind].Item.Subject + " " + Data[i][cind].Item.Teacher;
}
}
}
else if (ch == -1)
{
int cind;
if (temp[i][j].Item.Teacher != null)
{
if (dct.TryGetValue(temp[i][j].Item.Teacher, out cind))
{
Data[i][cind].Item = temp[i][j].Item;
worksheet.Cell(i + 2, 2 + cind + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(i + 2, 2 + cind + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(i + 2, 2 + cind + 1).Value = Data[i][cind].Item.NumberOfClassroom + " " + Data[i][cind].Item.Subject + " " + Data[i][cind].Item.Group;
}
}
}
else
{
int cind;
if (temp[i][j].Item.NumberOfClassroom != null)
{
if (dct.TryGetValue(temp[i][j].Item.NumberOfClassroom, out cind))
{
Data[i][cind].Item = temp[i][j].Item;
worksheet.Cell(i + 2, 2 + cind + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(i + 2, 2 + cind + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(i + 2, 2 + cind + 1).Value = Data[i][cind].Item.Subject + " " + Data[i][cind].Item.Group + " " + Data[i][cind].Item.Teacher;
}
}
}
}
}
if (IsValidate() == true)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Книга Excel (*.xlsx)|*.xlsx";
string path = "";
if (saveFileDialog.ShowDialog() == true)
{
if (!string.IsNullOrEmpty(saveFileDialog.FileName))
{
path = saveFileDialog.FileName;
workbook.SaveAs(path);
MessageBox.Show("Сохранено");
}
}
}
}
public void SendExcelFile()
{
string mailLogin = XMLConfig.ReadMailLoginValue(System.IO.Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName) + "XMLConfig.xml");
string mailPassword = XMLConfig.ReadMailPasswordValue(System.IO.Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName) + "XMLConfig.xml");
SmtpClient smtp = new SmtpClient("smtp.gmail.com", 587);
smtp.EnableSsl = true;
smtp.Credentials = new NetworkCredential(mailLogin, mailPassword);
MailAddress from = new MailAddress(mailLogin);
for (int c = 0; c < columns.Count; c++)
{
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Лист1");
for (int r = 1; r <= SheduleSettings.WeekDayMaxCount; r++)
{
worksheet.Cell(12 * r - 10, 1).Style.Alignment.TextRotation = 90;
worksheet.Cell(12 * r - 10, 1).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(12 * r - 10, 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Row(12 * r - 10).Height = 25;
worksheet.Cell(12 * r - 10, 1).Style.Alignment.WrapText = true;
worksheet.Cell(12 * r - 10, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(12 * r - 10, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(12 * r - 10, 1).RichText.FontSize = 20;
worksheet.Cell(12 * r - 10, 1).RichText.FontColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).RichText.FontName = "Broadway";
string str = "";
if (r == 1)
{
str = "Понедельник";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 2)
{
str = "Вторник";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 3)
{
str = "Среда";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 4)
{
str = "Четверг";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 5)
{
str = "Пятница";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else
{
str = "Суббота";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
if (r < SheduleSettings.WeekDayMaxCount)
{
worksheet.Range(12 * r - 10, 1, 12 * r - 10 + 11, 1).Merge();
}
else
{
worksheet.Range(12 * r - 10, 1, 12 * r - 10 + 5, 1).Merge();
}
}
for (int r = 1; r <= maxpair; r++)
{
worksheet.Cell(2 * r, 2).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(2 * r, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(2 * r + 1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r + 1, 2).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(2 * r + 1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r + 1, 2).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(2 * r + 1, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r + 1, 2).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(2 * r, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Row(2 * r).Height = 20;
worksheet.Column(2).Width = 20;
worksheet.Cell(2 * r, 2).Value = strPair[(r - 1) % strPair.Length];
worksheet.Range(2 * r, 2, 2 * r + 1, 2).Merge();
}
worksheet.Column(3).Width = 40;
worksheet.Cell(1, 3).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(1, 3).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(1, 3).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(1, 3).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(1, 3).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(1, 3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(1, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(1, 3).Value = columns[c];
for (int i = 0; i < filtered.Count; i++)
{
if (filtered[i][c].Item.Teacher != null || filtered[i][c].ItemTwo.Teacher != null)
{
worksheet.Cell(2 * i + 2, 3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(2 * i + 2, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(2 * i + 3, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
if (filtered[i][c].State == 0)
{
worksheet.Cell(2 * i + 2, 3).Value = filtered[i][c].Item.Subject + " " + filtered[i][c].Item.Specifics + " " + filtered[i][c].Item.NumberOfClassroom + " " + string.Join(" ", filtered[i][c].Item.Group);
worksheet.Range(2 * i + 2, 3, 2 * i + 3, 3).Merge();
}
else
{
worksheet.Cell(2 * i + 2, 3).Value = filtered[i][c].Item.Subject + " " + filtered[i][c].Item.Specifics + " " + filtered[i][c].Item.NumberOfClassroom + " " + string.Join(" ", filtered[i][c].Item.Group);
worksheet.Cell(2 * i + 3, 3).Value = filtered[i][c].ItemTwo.Subject + " " + filtered[i][c].ItemTwo.Specifics + " " + filtered[i][c].ItemTwo.NumberOfClassroom + " " + string.Join(" ", filtered[i][c].ItemTwo.Group);
}
}
}
string fileName = "Расписание " + filtered[0][c].Key + ".xlsx";
workbook.SaveAs(fileName);
//MailAddress to = new MailAddress(filtered[0][c].Item.Teacher.Mail);
//MailMessage m = new MailMessage(from, to);
//m.Subject = "Тест";
//m.Body = "Письмо-тест работы отправки сообщения";
//m.Attachments.Add(new Attachment(fileName));
//smtp.Send(m);
System.IO.File.Delete(fileName);
}
MessageBox.Show("Расписание отправленно преподавателям", "Отправка расписания");
}
public void ExportToExcel()
{
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Лист1");
for (int r = 1; r <= SheduleSettings.WeekDayMaxCount; r++)
{
worksheet.Cell(12 * r - 10, 1).Style.Alignment.TextRotation = 90;
worksheet.Cell(12 * r - 10, 1).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(12 * r - 10, 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Row(12 * r - 10).Height = 25;
worksheet.Cell(12 * r - 10, 1).Style.Alignment.WrapText = true;
worksheet.Cell(12 * r - 10, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(12 * r - 10, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(12 * r - 10, 1).RichText.FontSize = 20;
worksheet.Cell(12 * r - 10, 1).RichText.FontColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).RichText.FontName = "Broadway";
string str = "";
if (r == 1)
{
str = "Понедельник";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 2)
{
str = "Вторник";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 3)
{
str = "Среда";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 4)
{
str = "Четверг";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 5)
{
str = "Пятница";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else
{
str = "Суббота";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
if (r < SheduleSettings.WeekDayMaxCount)
{
worksheet.Range(12 * r - 10, 1, 12 * r - 10 + 11, 1).Merge();
}
else
{
worksheet.Range(12 * r - 10, 1, 12 * r - 10 + 5, 1).Merge();
}
}
for (int r = 1; r <= maxpair; r++)
{
worksheet.Cell(2 * r, 2).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(2 * r, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(2 * r + 1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r + 1, 2).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(2 * r + 1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r + 1, 2).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(2 * r + 1, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r + 1, 2).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(2 * r, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Row(2 * r).Height = 20;
worksheet.Column(2).Width = 20;
worksheet.Cell(2 * r, 2).Value = strPair[(r - 1) % strPair.Length];
worksheet.Range(2 * r, 2, 2 * r + 1, 2).Merge();
}
for (int c = 0; c < columns.Count; c++)
{
worksheet.Column(3 + c).Width = 40;
worksheet.Cell(1, 3 + c).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(1, 3 + c).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3 + c).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(1, 3 + c).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(1, 3 + c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(1, 3 + c).Value = columns[c];
}
for (int i = 0; i < filtered.Count; i++)
{
for (int j = 0; j < filtered[i].Count; j++)
{
worksheet.Cell(2 * i + 2, 3 + j).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(2 * i + 2, 3 + j).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(2 * i + 3, 3 + j).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
if (ch == 0)
{
if (filtered[i][j].Item.Group != null)
{
if (filtered[i][j].State == 0)
{
worksheet.Cell(2 * i + 2, 3 + j).Value = filtered[i][j].Item.Subject + " " + filtered[i][j].Item.Specifics + " " + filtered[i][j].Item.NumberOfClassroom + " " + filtered[i][j].Item.Teacher;
worksheet.Range(2 * i + 2, 3 + j, 2 * i + 3, 3 + j).Merge();
}
else
{
worksheet.Cell(2 * i + 2, 3 + j).Value = filtered[i][j].Item.Subject + " " + filtered[i][j].Item.Specifics + " " + filtered[i][j].Item.NumberOfClassroom + " " + filtered[i][j].Item.Teacher;
worksheet.Cell(2 * i + 3, 3 + j).Value = filtered[i][j].ItemTwo.Subject + " " + filtered[i][j].ItemTwo.Specifics + " " + filtered[i][j].ItemTwo.NumberOfClassroom + " " + filtered[i][j].ItemTwo.Teacher;
}
}
}
else if (ch == -1)
{
if (filtered[i][j].Item.Teacher != null)
{
if (filtered[i][j].State == 0)
{
worksheet.Cell(2 * i + 2, 3 + j).Value = filtered[i][j].Item.Subject + " " + filtered[i][j].Item.Specifics + " " + filtered[i][j].Item.NumberOfClassroom + " " + string.Join("+", filtered[i][j].Item.Group.Select(gr => gr.NameOfGroup));
worksheet.Range(2 * i + 2, 3 + j, 2 * i + 3, 3 + j).Merge();
}
else
{
worksheet.Cell(2 * i + 2, 3 + j).Value = filtered[i][j].Item.Subject + " " + filtered[i][j].Item.Specifics + " " + filtered[i][j].Item.NumberOfClassroom + " " + string.Join("+", filtered[i][j].Item.Group.Select(gr => gr.NameOfGroup));
worksheet.Cell(2 * i + 3, 3 + j).Value = filtered[i][j].ItemTwo.Subject + " " + filtered[i][j].ItemTwo.Specifics + " " + filtered[i][j].ItemTwo.NumberOfClassroom + " " + string.Join("+", filtered[i][j].ItemTwo.Group.Select(gr => gr.NameOfGroup));
}
}
}
//else
//{
// if (filtered[i][j].Item.NumberOfClassroom != null)
// {
// if (filtered[i][j].State == 0)
// {
// worksheet.Cell(2 * i + 2, 3 + j).Value = filtered[i][j].Item.Teacher + " " + filtered[i][j].Item.Subject + " " + filtered[i][j].Item.Specifics + " " + string.Join("+", filtered[i][j].Item.Group.Select(gr => gr.NameOfGroup));
// worksheet.Range(2 * i + 2, 3 + j, 2 * i + 3, 3 + j).Merge();
// }
// else
// {
// worksheet.Cell(2 * i + 2, 3 + j).Value = filtered[i][j].Item.Teacher + " " + filtered[i][j].Item.Subject + " " + filtered[i][j].Item.Specifics + " " + string.Join("+", filtered[i][j].Item.Group.Select(gr => gr.NameOfGroup));
// worksheet.Cell(2 * i + 3, 3 + j).Value = filtered[i][j].ItemTwo.Teacher + " " + filtered[i][j].ItemTwo.Subject + " " + filtered[i][j].ItemTwo.Specifics + " " + string.Join("+", filtered[i][j].ItemTwo.Group.Select(gr => gr.NameOfGroup));
// }
// }
//}
}
}
if (IsValidate())
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Книга Excel (*.xlsx)|*.xlsx";
string path = "";
if (saveFileDialog.ShowDialog() == true)
{
if (!string.IsNullOrEmpty(saveFileDialog.FileName))
{
path = saveFileDialog.FileName;
workbook.SaveAs(path);
MessageBox.Show("Сохранено", "Сохранение");
}
}
}
}
public void ExportToExcelSeparately()
{
string path = "";
WinForms.FolderBrowserDialog fdb = new WinForms.FolderBrowserDialog();
if (fdb.ShowDialog() == WinForms.DialogResult.OK)
{
if (!string.IsNullOrEmpty(fdb.SelectedPath))
{
path = fdb.SelectedPath;
}
}
Console.WriteLine(path);
for (int c = 0; c < columns.Count; c++)
{
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Лист1");
for (int r = 1; r <= SheduleSettings.WeekDayMaxCount; r++)
{
worksheet.Cell(12 * r - 10, 1).Style.Alignment.TextRotation = 90;
worksheet.Cell(12 * r - 10, 1).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(12 * r - 10, 1).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(12 * r - 10, 1).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Row(12 * r - 10).Height = 25;
worksheet.Cell(12 * r - 10, 1).Style.Alignment.WrapText = true;
worksheet.Cell(12 * r - 10, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(12 * r - 10, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(12 * r - 10, 1).RichText.FontSize = 20;
worksheet.Cell(12 * r - 10, 1).RichText.FontColor = XLColor.Black;
worksheet.Cell(12 * r - 10, 1).RichText.FontName = "Broadway";
string str = "";
if (r == 1)
{
str = "Понедельник";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 2)
{
str = "Вторник";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 3)
{
str = "Среда";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 4)
{
str = "Четверг";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else if (r == 5)
{
str = "Пятница";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
else
{
str = "Суббота";
worksheet.Cell(12 * r - 10, 1).Value = str;
}
if (r < SheduleSettings.WeekDayMaxCount)
{
worksheet.Range(12 * r - 10, 1, 12 * r - 10 + 11, 1).Merge();
}
else
{
worksheet.Range(12 * r - 10, 1, 12 * r - 10 + 5, 1).Merge();
}
}
for (int r = 1; r <= maxpair; r++)
{
worksheet.Cell(2 * r, 2).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(2 * r, 2).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(2 * r + 1, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r + 1, 2).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(2 * r + 1, 2).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r + 1, 2).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r, 2).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(2 * r + 1, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2 * r + 1, 2).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(2 * r, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(2 * r, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Row(2 * r).Height = 20;
worksheet.Column(2).Width = 20;
worksheet.Cell(2 * r, 2).Value = strPair[(r - 1) % strPair.Length];
worksheet.Range(2 * r, 2, 2 * r + 1, 2).Merge();
}
worksheet.Column(3).Width = 40;
worksheet.Cell(1, 3).Style.Fill.BackgroundColor = XLColor.FromIndex(22);
worksheet.Cell(1, 3).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3).Style.Border.TopBorderColor = XLColor.Black;
worksheet.Cell(1, 3).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3).Style.Border.RightBorderColor = XLColor.Black;
worksheet.Cell(1, 3).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3).Style.Border.LeftBorderColor = XLColor.Black;
worksheet.Cell(1, 3).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, 3).Style.Border.BottomBorderColor = XLColor.Black;
worksheet.Cell(1, 3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(1, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(1, 3).Value = columns[c];
for (int i = 0; i < filtered.Count; i++)
{
if (ch == 1)
{
if (filtered[i][c].Item.NumberOfClassroom != null || filtered[i][c].ItemTwo.NumberOfClassroom != null)
{
worksheet.Cell(2 * i + 2, 3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(2 * i + 2, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(2 * i + 3, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
if (filtered[i][c].State == 0)
{
worksheet.Cell(2 * i + 2, 3).Value = filtered[i][c].Item.Subject + " " + filtered[i][c].Item.Specifics + " " + filtered[i][c].Item.Teacher + " " + string.Join(" ", filtered[i][c].Item.Group);
worksheet.Range(2 * i + 2, 3, 2 * i + 3, 3).Merge();
}
else
{
worksheet.Cell(2 * i + 2, 3).Value = filtered[i][c].Item.Subject + " " + filtered[i][c].Item.Specifics + " " + filtered[i][c].Item.Teacher + " " + string.Join(" ", filtered[i][c].Item.Group);
worksheet.Cell(2 * i + 3, 3).Value = filtered[i][c].ItemTwo.Subject + " " + filtered[i][c].ItemTwo.Specifics + " " + filtered[i][c].ItemTwo.Teacher + " " + string.Join(" ", filtered[i][c].ItemTwo.Group);
}
}
}
else if (ch == -1)
{
if (filtered[i][c].Item.Teacher != null || filtered[i][c].ItemTwo.Teacher != null)
{
worksheet.Cell(2 * i + 2, 3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(2 * i + 2, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(2 * i + 3, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
if (filtered[i][c].State == 0)
{
worksheet.Cell(2 * i + 2, 3).Value = filtered[i][c].Item.Subject + " " + filtered[i][c].Item.Specifics + " " + filtered[i][c].Item.NumberOfClassroom + " " + string.Join(" ", filtered[i][c].Item.Group);
worksheet.Range(2 * i + 2, 3, 2 * i + 3, 3).Merge();
}
else
{
worksheet.Cell(2 * i + 2, 3).Value = filtered[i][c].Item.Subject + " " + filtered[i][c].Item.Specifics + " " + filtered[i][c].Item.NumberOfClassroom + " " + string.Join(" ", filtered[i][c].Item.Group);
worksheet.Cell(2 * i + 3, 3).Value = filtered[i][c].ItemTwo.Subject + " " + filtered[i][c].ItemTwo.Specifics + " " + filtered[i][c].ItemTwo.NumberOfClassroom + " " + string.Join(" ", filtered[i][c].ItemTwo.Group);
}
}
}
}
string fileName = CreateName(filtered[0][c].Key);
workbook.SaveAs(path + @"\" + fileName);
}
MessageBox.Show("Сохранено", "Сохранение");
}