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++; } }