void renderReportObject(Style style, IXLCell cell)
{
if (style.Borders.Top != null && style.Borders.Top.Visible)
{
cell.Style.Border.TopBorder = XLBorderStyleValues.Thin;
cell.Style.Border.TopBorderColor = XLColor.FromName(style.Borders.Top.Color);
}
if (style.Borders.Right != null && style.Borders.Right.Visible)
{
cell.Style.Border.RightBorder = XLBorderStyleValues.Thin;
cell.Style.Border.RightBorderColor = XLColor.FromName(style.Borders.Right.Color);
}
if (style.Borders.Bottom != null && style.Borders.Bottom.Visible)
{
cell.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
cell.Style.Border.BottomBorderColor = XLColor.FromName(style.Borders.Bottom.Color);
}
if (style.Borders.Left != null && style.Borders.Left.Visible)
{
cell.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
cell.Style.Border.LeftBorderColor = XLColor.FromName(style.Borders.Left.Color);
}
if (!string.IsNullOrEmpty(style.BackColor))
{
cell.Style.Fill.BackgroundColor = XLColor.FromHtml(style.BackColor);
}
}
private void CreateHeader()
{
this.worksheet.Cell("A1").Value = "Wyniki - Szkolna liga szachowa";
this.worksheet.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
this.worksheet.Cell("A1").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
this.worksheet.Cell("A1").Style.Font.FontSize = 16;
this.worksheet.Cell("A1").Style.Fill.BackgroundColor = XLColor.FromName("PowderBlue");
this.worksheet.Range("A1:D4").Merge();
this.worksheet.Column("A").Width = 30;
this.worksheet.Column("B").Width = 30;
this.worksheet.Column("C").Width = 30;
this.worksheet.Column("D").Width = 30;
this.worksheet.Cell("A5").Value = "Zawodnik #1";
this.worksheet.Cell("A5").Style.Font.Bold = true;
this.worksheet.Cell("A5").Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
this.worksheet.Cell("B5").Value = "Zawodnik #2";
this.worksheet.Cell("B5").Style.Font.Bold = true;
this.worksheet.Cell("B5").Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
this.worksheet.Cell("C5").Value = "Wynik";
this.worksheet.Cell("C5").Style.Font.Bold = true;
this.worksheet.Cell("C5").Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
this.worksheet.Cell("D5").Value = "Notatka";
this.worksheet.Cell("D5").Style.Font.Bold = true;
this.worksheet.Cell("D5").Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
}
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);
}
void renderObject(ReportObject obj, IXLCell cell)
{
if (obj is TextBase)
{
var textbase = obj as TextBase;
renderReportObject(textbase.Style, cell);
cell.Style.Font.FontName = textbase.Style.FontFamily;
cell.Style.Font.FontSize = textbase.Style.FontSize;
cell.Style.Font.FontColor = XLColor.FromName(textbase.Style.Color);
cell.Style.Font.Bold = (obj as TextBase).Style.Bold;
cell.Style.Font.Italic = (obj as TextBase).Style.Italic;
cell.Style.Alignment.WrapText = true;
cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
switch ((obj as TextBase).Style.Alignment)
{
case System.Drawing.StringAlignment.Center:
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
case System.Drawing.StringAlignment.Far:
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case System.Drawing.StringAlignment.Near:
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
break;
default:
break;
}
var value = "";
if (obj is Label)
{
value = ((Label)obj).Value;
}
cell.Value = value;
}
}
/// <summary>
/// 1行分のデータをセルに出力
/// </summary>
/// <param name="worksheet">シート</param>
/// <param name="row">行番号</param>
/// <param name="col">列番号</param>
/// <param name="line">行データ</param>
/// <param name="isBold">太字にするか</param>
/// <param name="bgColor">背景色</param>
/// <param name="hasBoeder">外枠の罫線をつけるか</param>
private void PasteLine(
IXLWorksheet worksheet, int row, int col,
IReadOnlyCollection <string> line, bool isBold, string bgColor, bool hasBoeder)
{
foreach (var item in line)
{
col++;
var cell = worksheet.Cell(row, col);
cell.Value = item;
var style = cell.Style;
// HACK:事前チェック
style.Fill.BackgroundColor = XLColor.FromName(bgColor);
style.Font.Bold = isBold;
if (hasBoeder)
{
style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
}
}
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 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 static string BuildReport(string reportName, string reportTitle, DataSet dataSet, List <string> columnsToSum)
{
try
{
if (Directory.Exists(System.IO.Path.GetTempPath() + "\\TMS\\") == false)
{
Directory.CreateDirectory(System.IO.Path.GetTempPath() + "\\TMS\\");
}
string fileName = System.IO.Path.GetTempPath() + "\\TMS\\" + reportName + "_" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".xlsx";
XLWorkbook xLWorkbook = new XLWorkbook();
int k = 1;
foreach (DataTable dt in dataSet.Tables)
{
object sumObject;
Dictionary <string, object> sume = new Dictionary <string, object>();
foreach (string c in columnsToSum)
{
sume.Add(c, sumObject = dt.Compute("Sum(" + c + ")", string.Empty));
}
//dt.TableName = reportName+"_"+k.ToString();
var ws = xLWorkbook.Worksheets.Add(reportName);
ws.Cell("A1").Value = reportTitle;
ws.Cell("A1").Style.Font.Bold = true;
ws.Cell("A1").Style.Font.FontSize = 16;
ws.Cell("A3").InsertTable(dt);
ws.Range("A1:D1").Merge();
//update hyperlinks
//var ws = xLWorkbook.Worksheet(1);
int rowcount = ws.RangeUsed().RowCount();
int columncount = ws.RangeUsed().ColumnCount();
for (int i = 0; i < columncount; i++)
{
ws.Column(i + 1).AdjustToContents();
string colName = ws.Cell(1, i + 1).Value.ToString();
if (colName.Contains("_Wx"))
{
string columnName = colName.Substring(0, colName.LastIndexOf("_Wx"));
double width = double.Parse(colName.Substring(colName.LastIndexOf("_Wx") + 3));
ws.Column(i + 1).Width = width;
}
}
for (int i = 0; i < columncount; i++)
{
var is_color_col = ws.Cell(1, i + 1).Value.ToString().Contains("_color");
if (is_color_col)
{
string columnName = ws.Cell(1, i + 1).Value.ToString().Substring(0, ws.Cell(1, i + 1).Value.ToString().IndexOf('_'));
var column = dt.Columns[columnName].Ordinal;
for (int j = 0; j < rowcount; j++)
{
if (ws.Cell(j + 1, i + 1).Value.ToString() != "" && ws.Cell(j + 1, column + 1).Value.ToString() != "")
{
ws.Cell(j + 1, column + 1).Style.Fill.BackgroundColor = XLColor.FromName(ws.Cell(j + 1, i + 1).Value.ToString());
}
}
ws.Column(i + 1).Hide();
}
}
for (int i = 0; i < columncount; i++)
{
var is_url_col = ws.Cell(1, i + 1).Value.ToString().Contains("_url");
if (is_url_col)
{
string columnName = ws.Cell(1, i + 1).Value.ToString().Substring(0, ws.Cell(1, i + 1).Value.ToString().IndexOf('_'));
var column = dt.Columns[columnName].Ordinal;
for (int j = 0; j < rowcount; j++)
{
if (ws.Cell(j + 1, i + 1).Value.ToString() != "" && ws.Cell(j + 1, column + 1).Value.ToString() != "")
{
ws.Cell(j + 1, column + 1).Hyperlink = new XLHyperlink(ws.Cell(j + 1, i + 1).Value.ToString());
}
}
ws.Column(i + 1).Hide();
}
}
k++;
foreach (KeyValuePair <string, object> kvp in sume)
{
int column = dt.Columns[kvp.Key].Ordinal + 1;
int row = dt.Rows.Count + 4;
ws.Cell(row, column).Value = kvp.Value;
ws.Cell(row, column).Style.Font.Bold = true;
}
}
MemoryStream ms = new MemoryStream();
xLWorkbook.SaveAs(ms);
FileStream file = new FileStream(fileName, FileMode.Create, FileAccess.Write);
ms.WriteTo(file);
file.Close();
ms.Close();
return(fileName);
}
catch (Exception e)
{
//Logger.Exception(e);
return(null);
}
}
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 ApplyStyle(IXLColumn column)
{
switch (Type)
{
case "percent":
column.Style.NumberFormat.Format = "0%";
column.Width = 7;
break;
case "integer":
column.Style.NumberFormat.Format = "# ##0;-# ##0;0";
column.Width = 10;
break;
case "decimal":
column.Style.NumberFormat.Format = "# ##0.0##;_-# ##0.0##;0";
column.Width = 10;
break;
case "datetime":
case "datetime+utc":
column.Style.DateFormat.Format = "dd.mm.yyyy hh:mm:ss";
column.Width = 20;
break;
case "date":
case "date+utc":
column.Style.DateFormat.Format = "dd.mm.yyyy";
column.Width = 10;
break;
case "time":
case "time+utc":
column.Style.DateFormat.Format = "hh:mm:ss";
column.Width = 10;
break;
}
if (Width > 0)
{
column.Width = Width;
}
if (!string.IsNullOrWhiteSpace(FontColor))
{
column.Style.Font.FontColor = FontColor.StartsWith("#") ? XLColor.FromHtml(FontColor) : XLColor.FromName(FontColor);
}
}
private void GenerateClash(string[][] data)
{
int lineCount = 6, que = 1;
for (int i = 0; i < data.Length; i++)
{
if (i % (this.membersCount / 2) == 0)
{
this.worksheet.Cell("A" + lineCount).Value = "Kolejka: " + que;
this.worksheet.Cell("A" + lineCount).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
this.worksheet.Cell("A" + lineCount).Style.Fill.BackgroundColor = XLColor.FromName("yellow");
lineCount++;
que++;
}
this.worksheet.Cell("A" + lineCount).Value = data[i][0];
this.worksheet.Cell("A" + lineCount).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
this.worksheet.Cell("B" + lineCount).Value = data[i][1];
this.worksheet.Cell("B" + lineCount).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
lineCount++;
}
}
