private static async Task AddColors(IXLWorksheet ws, int rowCounter, string domain)
{
try
{
Console.WriteLine($"Getting Color for {domain}");
var colorRoot = await GetColor(domain);
var colors = colorRoot.response.filtered;
ws.Cell(rowCounter, VibrantColumn).Style.Fill.BackgroundColor = XLColor.FromHtml(colors.vibrant);
ws.Cell(rowCounter, DarkColumn).Style.Fill.BackgroundColor = XLColor.FromHtml(colors.dark);
ws.Cell(rowCounter, LightColumn).Style.Fill.BackgroundColor = XLColor.FromHtml(colors.light);
}
catch (HttpRequestException e)
{
Console.WriteLine($"Error getting color for {domain}: {e.Message}");
}
catch (NotFoundException e)
{
Console.WriteLine($"Error getting color for {domain}: {e.Message}");
}
catch (Exception e)
{
Console.WriteLine($"Error getting color for {domain}: {e.Message}");
}
}
public static void SetFormatCell(IXLWorksheet ws,
int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn,
bool isFontBold = false, int fontSize = 10, bool isMergeCell = false,
bool isSetColorHeader = false, bool isSetBorder = true,
XLAlignmentHorizontalValues HorizontalAlgn = XLAlignmentHorizontalValues.Center,
XLAlignmentVerticalValues VerticalAlgn = XLAlignmentVerticalValues.Center)
{
// Defining ranges
// From worksheet
var rangeTable = ws.Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn);
rangeTable.Style.Alignment.Horizontal = HorizontalAlgn;
rangeTable.Style.Alignment.Vertical = VerticalAlgn;
rangeTable.Style.Font.Bold = isFontBold;
rangeTable.Style.Font.FontSize = fontSize;
if (isSetColorHeader)
{
rangeTable.Style.Fill.BackgroundColor = XLColor.FromArgb(184, 184, 184);
}
if (isMergeCell)
{
// Merge title cells
//rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge()
rangeTable.Merge();
}
if (isSetBorder)
{
rangeTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
rangeTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
}
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;
}
/// <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);
}
public void CreateReportHeader(IXLWorksheet ws, int totalCols, DateTime fromDate, DateTime toDate, string reportName)
{
string merchantName = Commons.MerchantName;
// Merchant Name
ws.Cell(1, 1).Value = merchantName;
ws.Range(1, 1, 1, totalCols).Merge();
ws.Range(1, 1, 1, totalCols).Style.Fill.SetBackgroundColor(XLColor.FromHtml(Commons.BgColorHeader));
// Report Name
//ws.Cell(2, 1).Value = string.Format("{0} from {1} to {2}", reportName, fromDate.ToString("MM/dd/yyyy"), toDate.ToString("MM/dd/yyyy"));
ws.Cell(2, 1).Value = string.Format("{0} " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("From").ToLower() + " {1} " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("To").ToLower() + " {2}", reportName, fromDate.ToString("MM/dd/yyyy"), toDate.ToString("MM/dd/yyyy"));
ws.Range(2, 1, 2, totalCols).Merge();
ws.Range(2, 1, 2, totalCols).Style.Fill.SetBackgroundColor(XLColor.FromHtml(Commons.BgColorHeader));
ws.Row(2).Style.Font.FontSize = 16;
ws.Row(2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Row(2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Row(2).Height = 40;
// Current Date
ws.Range(3, 1, 3, totalCols).Merge();
ws.Cell(3, 1).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Generated on") + ": " + DateTime.Now.ToString();
ws.Row(3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Row(1).Style.Font.SetBold(true);
ws.Row(2).Style.Font.SetBold(true);
ws.Row(3).Style.Font.SetBold(true);
}
public XLWorkbook Convert(Bitmap bitmap)
{
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sheet 1");
for (int x = 0; x < bitmap.Width; x++)
{
string col = ToBase26(x + 1);
for (int y = 0; y < bitmap.Height; y++)
{
var pixel = bitmap.GetPixel(x, y);
int rowRed = y * 3 + 1;
int rowGreen = y * 3 + 2;
int rowBlue = y * 3 + 3;
worksheet.Cell($"{col}{rowRed}").Value = pixel.R;
worksheet.Cell($"{col}{rowRed}").Style.Fill.BackgroundColor = XLColor.FromArgb(pixel.R, 0, 0);
worksheet.Cell($"{col}{rowGreen}").Value = pixel.G;
worksheet.Cell($"{col}{rowGreen}").Style.Fill.BackgroundColor = XLColor.FromArgb(0, pixel.G, 0);
worksheet.Cell($"{col}{rowBlue}").Value = pixel.B;
worksheet.Cell($"{col}{rowBlue}").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 0, pixel.B);
}
}
return(workbook);
}
void painting()
{
button.Enabled = false;
button.Text = "initalizing...";
button.Update();
inputFileName = dataPath + number.ToString() + ".png";
outputFileName = dataPath + dataName[number];
int wid, hig, r, g, bb, i = 0;
//string r, g, bb;
try
{
Bitmap img = new Bitmap(inputFileName);
wid = img.Width;
hig = img.Height;
BitmapData data = img.LockBits(new Rectangle(0, 0, wid, hig), ImageLockMode.ReadWrite, PixelFormat.Format32bppArgb);
byte[] buf = new byte[wid * hig * 4];
Marshal.Copy(data.Scan0, buf, 0, buf.Length);
using (var book = new XLWorkbook("testafter.xlsx"))
{
button.Text = "processing...";
button.Update();
var sheet1 = book.Worksheet(1);
for (int a = 0; a < hig; a++)
{
for (int b = 0; b < wid; b++)
{
var cell = sheet1.Cell(a + 1, b + 1);
bb = buf[i++];
g = buf[i++];
r = buf[i++];
cell.Style.Fill.BackgroundColor = XLColor.FromArgb(r, g, bb);
i++;
//textbox2.AppendText(b.ToString() + "," + a.ToString() + "," + number.ToString() + "," + progress.ToString() + "%" + Environment.NewLine);
//bar.Value = (int)prog;
//label2.Text = b.ToString() + "," + a.ToString() + "," + number.ToString() + "," + prog.ToString() + "%";
//label2.Update();
}
}
sheet1.Dispose();
book.Dispose();
img.Dispose();
button.Text = "finalizing...";
button.Update();
book.SaveAs(outputFileName);
}
number++;
if (File.Exists(dataPath + number.ToString() + ".png"))
{
painting();
}
}
catch (System.ArgumentException) { MessageBox.Show("ファイルパスが異常です", "", MessageBoxButtons.OK, MessageBoxIcon.Error); }
finally
{
button.Text = "開始";
button.Enabled = true;
button.Update();
}
}
private static void OutDate(IXLWorksheet sheet, ref int currentRow, Purchase purchase)
{
var dateCell = sheet.Cell(currentRow++, 1);
dateCell.Value = purchase.Date.ToShortDateString();
dateCell.Style.Fill.BackgroundColor = XLColor.FromColor(Color.LightGreen);
}
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);
}
public void CreateReportHeaderForTopSale(IXLWorksheet ws, int totalCols, DateTime date, string reportName)
{
string merchantName = Commons.MerchantName;
// Merchant Name
ws.Cell(1, 1).Value = merchantName;
ws.Range(1, 1, 1, totalCols).Merge();
ws.Range(1, 1, 1, totalCols).Style.Fill.SetBackgroundColor(XLColor.FromHtml(Commons.BgColorHeader));
// Report Name
ws.Cell(2, 1).Value = string.Format("{0}", reportName);
ws.Range(2, 1, 2, totalCols).Merge();
ws.Range(2, 1, 2, totalCols).Style.Fill.SetBackgroundColor(XLColor.FromHtml(Commons.BgColorHeader));
ws.Row(2).Style.Font.FontSize = 16;
ws.Row(2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Row(2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Row(2).Height = 40;
// Date
ws.Range(3, 1, 3, totalCols).Merge();
ws.Cell(3, 1).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("As of this date") + ": " + date.ToString("MM/dd/yyyy");
ws.Row(3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Row(1).Style.Font.SetBold(true);
ws.Row(2).Style.Font.SetBold(true);
ws.Row(3).Style.Font.SetBold(true);
}
private static void ConfigureRow(this IXLWorksheet xlSheet, Row row, List <ColumnProps> columnProps, bool isSheetLocked)
{
foreach (var rowCell in row.Cells)
{
if (rowCell.Visible is false)
{
continue;
}
xlSheet.ConfigureCell(rowCell, columnProps, isSheetLocked);
}
// Configure merged cells in the row
foreach (var cellsToMerge in row.MergedCellsList)
{
// CellsToMerge example is "B2:D2"
xlSheet.Range(cellsToMerge).Row(1).Merge();
}
if (row.Cells.Count != 0)
{
if (row.StartLocation is not null && row.EndLocation is not null)
{
var xlRow = xlSheet.Row(row.Cells.First().Location.Y);
if (row.Height is not null)
{
xlRow.Height = (double)row.Height;
}
var xlRowRange = xlSheet.Range(row.StartLocation.Y, row.StartLocation.X, row.EndLocation.Y,
row.EndLocation.X);
xlRowRange.Style.Font.SetFontColor(XLColor.FromColor(row.ForeColor));
xlRowRange.Style.Fill.SetBackgroundColor(XLColor.FromColor(row.BackColor));
XLBorderStyleValues?outsideBorder = GetXlBorderLineStyle(row.OutsideBorder.LineStyle);
if (outsideBorder is not null)
{
xlRowRange.Style.Border.SetOutsideBorder((XLBorderStyleValues)outsideBorder);
xlRowRange.Style.Border.SetOutsideBorderColor(
XLColor.FromColor(row.OutsideBorder.Color));
}
// TODO: For Inside border, the row should be considered as Ranged (like Table). I persume it is not important for this phase
}
else
{
var xlRow = xlSheet.Row(row.Cells.First().Location.Y);
if (row.Height is not null)
{
xlRow.Height = (double)row.Height;
}
xlRow.Style.Font.SetFontColor(XLColor.FromColor(row.ForeColor));
xlRow.Style.Fill.SetBackgroundColor(XLColor.FromColor(row.BackColor));
xlRow.Style.Border.SetOutsideBorder(XLBorderStyleValues.Dotted);
xlRow.Style.Border.SetInsideBorder(XLBorderStyleValues.Thick);
xlRow.Style.Border.SetTopBorder(XLBorderStyleValues.Thick);
xlRow.Style.Border.SetRightBorder(XLBorderStyleValues.DashDotDot);
}
}
/// <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 static void SetHeaderCell(IXLWorksheet sheet, int row, int column, int color, string text)
{
sheet.Cell(row, column).Value = text;
sheet.Cell(row, column).Style.Fill.BackgroundColor = XLColor.FromArgb(color);
ActivateRuledLine(sheet.Cell(row, column).Style);
sheet.Cell(row, column).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
public void GetCellText(IXLWorksheet RowsHead, double nWidthColumn, string StringCell, string NumCell, string sValue, XLAlignmentHorizontalValues Align, string index, string sCC)
{
//RowsHead.Range(StringCell + NumCell+":")
var temColumn = RowsHead.Column(StringCell);
//temColumn.Style.Fill.BackgroundColor = XLColor.DarkOrange;
temColumn.Width = nWidthColumn;
var itemCell = RowsHead.Cell(StringCell + NumCell);
itemCell.Value = sValue;
itemCell.Style.Alignment.Horizontal = Align;
itemCell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
//itemCell.Style.Font.Bold = true;
itemCell.Style.Font.FontName = "Cordia New";
itemCell.Style.Font.FontSize = 12;
//itemCell.Style.Font.Underline = true;
itemCell.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
itemCell.Style.Border.RightBorder = XLBorderStyleValues.Thin;
itemCell.Style.Border.TopBorder = XLBorderStyleValues.Thin;
itemCell.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
itemCell.Style.Fill.BackgroundColor = XLColor.FromHtml(sCC);
itemCell.Style.Border.LeftBorderColor = XLColor.Black;
itemCell.Style.Border.RightBorderColor = XLColor.Black;
itemCell.Style.Border.TopBorderColor = XLColor.Black;
itemCell.Style.Border.BottomBorderColor = XLColor.Black;
itemCell.Style.Alignment.WrapText = true;
}
/// <summary>
/// Copies the report style to an XL Style.
/// </summary>
/// <param name="reportStyle">The report style.</param>
/// <param name="xlStyle">The xl style.</param>
public static void CopyToXlStyle(this ReportStyle reportStyle, IXLStyle xlStyle)
{
if (reportStyle.BackColor != Color.White)
{
xlStyle.Fill.BackgroundColor = XLColor.FromColor(reportStyle.BackColor);
}
if (reportStyle.ForeColor != Color.Black)
{
xlStyle.Font.SetFontColor(XLColor.FromColor(reportStyle.ForeColor));
}
xlStyle.Font.SetBold(reportStyle.Bold);
xlStyle.Font.SetFontSize(reportStyle.FontSize);
xlStyle.Font.SetItalic(reportStyle.Italic);
xlStyle.Font.SetUnderline(reportStyle.Underline ? XLFontUnderlineValues.Single : XLFontUnderlineValues.None);
xlStyle.Alignment.Horizontal =
reportStyle.HorizontalAlignment == HorizontalAlignment.Center ? XLAlignmentHorizontalValues.Center :
reportStyle.HorizontalAlignment == HorizontalAlignment.Left ? XLAlignmentHorizontalValues.Left :
XLAlignmentHorizontalValues.Right;
xlStyle.Alignment.Vertical =
reportStyle.VerticalAlignment == VerticalAlignment.Bottom ? XLAlignmentVerticalValues.Bottom :
reportStyle.VerticalAlignment == VerticalAlignment.Middle ? XLAlignmentVerticalValues.Center :
XLAlignmentVerticalValues.Top;
xlStyle.Alignment.TextRotation = reportStyle.TextRotation;
xlStyle.Alignment.WrapText = reportStyle.WrapText;
}
/// <summary>
/// Creates the headers for the table that will hold the artists information
/// from the report. The headers are: Posición, Nombre del Artista, Género
/// Musical, Calificación Fanáticos MyConcert, Nota promedio Algoritmo Chef.
/// </summary>
/// <param name="pHeaderColor">Table header color. Must be of type XLColor from ClosedXML lib.</param>
public void addTableHeaders(XLColor pHeaderColor)
{
_worksheet.Cell("A11").Value = "Posición";
_worksheet.Cell("A11").Style.Fill.BackgroundColor = pHeaderColor;
addAllBordersToCell("A11", XLColor.Black);
_worksheet.Cell("B11").Value = "Nombre del Artista";
_worksheet.Cell("B11").Style.Fill.BackgroundColor = pHeaderColor;
_worksheet.Range("B11:E11").Row(1).Merge();
addAllBordersToCell("B11", XLColor.Black);
addAllBordersToCell("C11", XLColor.Black);
addAllBordersToCell("D11", XLColor.Black);
addAllBordersToCell("E11", XLColor.Black);
_worksheet.Cell("F11").Value = "Género Musical";
_worksheet.Cell("F11").Style.Fill.BackgroundColor = pHeaderColor;
_worksheet.Range("F11:H11").Row(1).Merge();
addAllBordersToCell("F11", XLColor.Black);
addAllBordersToCell("G11", XLColor.Black);
addAllBordersToCell("H11", XLColor.Black);
_worksheet.Cell("I11").Value = "Cal. MyConcert";
_worksheet.Cell("I11").Style.Fill.BackgroundColor = pHeaderColor;
_worksheet.Range("I11:J11").Row(1).Merge();
addAllBordersToCell("I11", XLColor.Black);
addAllBordersToCell("J11", XLColor.Black);
_worksheet.Cell("K11").Value = "Not. Alg.";
_worksheet.Cell("K11").Style.Fill.BackgroundColor = pHeaderColor;
addAllBordersToCell("K11", XLColor.Black);
_currentRow = 12;
}
private static void OutPurchaseSum(IXLWorksheet sheet, ref int currentRow, int rowDataBegin)
{
var cell = sheet.Cell(currentRow++, 5);
cell.Value = sheet.Evaluate($"=SUM($E{rowDataBegin}:$E{currentRow - 2})");
cell.Style.Fill.BackgroundColor = XLColor.FromColor(Color.LightPink);
}
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);
}
}
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);
}
private void InitializeWorksheet(IXLWorksheet worksheet)
{
void SetColumn(string columnIndex, string columnName, float columnWidth)
{
worksheet.Cell(columnIndex).Value = columnName;
worksheet.Column(columnIndex[0] - 'A' + 1).Width = columnWidth; // 'A' -> 1 ; 'C' -> 3
}
SetColumn("A1", "Poster", 8.11f);
SetColumn("B1", "AnimeName", 36.8f);
SetColumn("C1", "Rating", 8.11f);
SetColumn("D1", "StudioName", 16.5f);
SetColumn("E1", "Status", 12.8f);
SetColumn("F1", "AgeRating", 40.7f);
SetColumn("G1", "Type", 25f);
SetColumn("H1", "Description", 19.2f);
SetColumn("I1", "Source", 10.8f);
SetColumn("J1", "Season", 10.1f);
SetColumn("K1", "Genres", 10.4f);
worksheet.Row(1).Style.Font.Bold = true;
worksheet.Columns(1, 11).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Column(8).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
worksheet.Column(11).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
worksheet.Range("A1", "K1").Style.Fill.BackgroundColor = XLColor.FromArgb(112, 173, 71);
}
public void SetInsideBorderPreservesOutsideBorders()
{
using (var wb = new XLWorkbook())
{
var ws = wb.AddWorksheet();
ws.Cells("B2:C2").Style
.Border.SetOutsideBorder(XLBorderStyleValues.Thin)
.Border.SetOutsideBorderColor(XLColor.FromTheme(XLThemeColor.Accent1, 0.5));
//Check pre-conditions
Assert.AreEqual(XLBorderStyleValues.Thin, ws.Cell("B2").Style.Border.LeftBorder);
Assert.AreEqual(XLBorderStyleValues.Thin, ws.Cell("B2").Style.Border.RightBorder);
Assert.AreEqual(XLThemeColor.Accent1, ws.Cell("B2").Style.Border.LeftBorderColor.ThemeColor);
Assert.AreEqual(XLThemeColor.Accent1, ws.Cell("B2").Style.Border.RightBorderColor.ThemeColor);
ws.Range("B2:C2").Style.Border.SetInsideBorder(XLBorderStyleValues.None);
Assert.AreEqual(XLBorderStyleValues.Thin, ws.Cell("B2").Style.Border.LeftBorder);
Assert.AreEqual(XLBorderStyleValues.None, ws.Cell("B2").Style.Border.RightBorder);
Assert.AreEqual(XLBorderStyleValues.None, ws.Cell("C2").Style.Border.LeftBorder);
Assert.AreEqual(XLBorderStyleValues.Thin, ws.Cell("C2").Style.Border.RightBorder);
Assert.AreEqual(XLThemeColor.Accent1, ws.Cell("B2").Style.Border.LeftBorderColor.ThemeColor);
Assert.AreEqual(XLThemeColor.Accent1, ws.Cell("C2").Style.Border.RightBorderColor.ThemeColor);
}
}
public static void ToExcelSummary(List <Activity> activities, string folderPath)
{
string pathFile = Path.Combine(folderPath, "Export_To_EXCEL_SUMMARY_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx");
var wb = new XLWorkbook();
// Sheet name:
var ws = wb.Worksheets.Add("Summary");
// Title:
var title = ws.Cell(1, 1);
title.Value = "UiPath is Funny";
title.Style.Font.Bold = true;
title.Style.Font.FontColor = XLColor.White;
title.Style.Fill.BackgroundColor = XLColor.FromArgb(0, 133, 202);
ws.Range(1, 1, 1, 4).Merge().AddToNamed("Title");
// Columns:
Type typeOfMyObject = activities[0].GetType();
PropertyInfo[] properties = typeOfMyObject.GetProperties();
for (int i = 0; i < properties.Length; i++)
{
var column = ws.Cell(3, i + 1);
column.Value = properties[i].Name.ToUpper();
column.Style.Font.Bold = true;
column.Style.Font.FontColor = XLColor.Black;
column.Style.Fill.BackgroundColor = XLColor.Orange;
column.Style.Border.RightBorder = XLBorderStyleValues.Thin;
column.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
column.Style.Border.TopBorder = XLBorderStyleValues.Thin;
column.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
column.Style.Border.RightBorderColor = XLColor.Black;
column.Style.Border.LeftBorderColor = XLColor.Black;
column.Style.Border.TopBorderColor = XLColor.Black;
column.Style.Border.BottomBorderColor = XLColor.Black;
}
// Rows:
var row = ws.Cell(4, 1);
row.InsertData(activities);
var cl = ws.Range(4, 1, 3 + activities.Count, 4);
cl.Style.Border.RightBorder = XLBorderStyleValues.Thin;
cl.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
cl.Style.Border.TopBorder = XLBorderStyleValues.Thin;
cl.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
cl.Style.Border.RightBorderColor = XLColor.Black;
cl.Style.Border.LeftBorderColor = XLColor.Black;
cl.Style.Border.TopBorderColor = XLColor.Black;
cl.Style.Border.BottomBorderColor = XLColor.Black;
wb.SaveAs(pathFile);
wb.Dispose();
}
private string GetColor(string address, string type)
{
IXLCell cell = closedWorksheet.Cell(address);
XLColor cellColor = null;
if (type == "background-color")
{
cellColor = cell.Style.Fill.BackgroundColor;
}
else if (type == "color")
{
cellColor = cell.Style.Font.FontColor;
}
else if (type == "border-top")
{
cellColor = cell.Style.Border.TopBorderColor;
}
else if (type == "border-left")
{
cellColor = cell.Style.Border.LeftBorderColor;
}
else if (type == "border-right")
{
cellColor = cell.Style.Border.RightBorderColor;
}
else if (type == "border-bottom")
{
cellColor = cell.Style.Border.BottomBorderColor;
}
else
{
return(String.Empty);
}
if (cellColor.ColorType == XLColorType.Color)
{
return("#" + cellColor.Color.ToHex().Remove(0, 2));
}
else if (cellColor.ColorType == XLColorType.Indexed)
{
if (cellColor.Color.Name != "Transparent")
{
return("#" + cellColor.Color.ToHex().Remove(0, 2));
}
}
else //(cell.Style.Fill.BackgroundColor.ColorType == XLColorType.Theme)
{
string value = "";
if (Theme.TryGetValue(cellColor.ThemeColor.ToString(), out value))
{
return("#" + value.Remove(0, 2));
}
else
{
Console.WriteLine("Theme not found {2} cell:{0}{1}", cell.Address.ColumnLetter, cell.Address.RowNumber, cellColor.ThemeColor);
}
}
return(string.Empty);
}
internal static void SetConditionalFormatting(IXLCell cell)
{
cell.CellLeft(1);
var formula = $"={cell.Address}<>{cell.CellLeft(1).Address}";
cell.AddConditionalFormat().WhenIsTrue(formula)
.Fill.SetBackgroundColor(XLColor.FromHtml("#FFC1B4"));
}
public void setFontColorAndBg(IXLWorksheet sheet, int row, int column, XLColor font, XLColor bg)
{
sheet.Cell(row, column).Style.Font.Bold = true; //文字-粗體
sheet.Cell(row, column).Style.Font.FontColor = font; //文字-顏色
sheet.Cell(row, column).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
sheet.Cell(row, column).Style.Fill.BackgroundColor = bg; //背景顏色
sheet.Cell(row, column).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //文字置中
}
private static void SetupHeaderRow(IXLWorksheet worksheet, XLColor color)
{
var headerRow = worksheet.FirstRow();
headerRow.Style.Font.Bold = true;
headerRow.Style.Fill.BackgroundColor = color;
worksheet.SheetView.FreezeRows(1);
}
/// <summary>
/// 绘制指定行与列的颜色
/// </summary>
/// <param name="row">指定列</param>
/// <param name="column">指定行</param>
private void PaintCellColor(IXLWorksheet mSheet, int row, int column, XLColor color)
{
//找到指定位置的单元格
IXLCell cell = mSheet.Row(row).Cell(column);
//绘色
cell.Style.Fill.BackgroundColor = color;
}
/// <summary>
/// Convert color in ClosedXML representation to specified OpenXML type.
/// </summary>
/// <typeparam name="T">The descendant of <see cref="X14.ColorType"/>.</typeparam>
/// <param name="openXMLColor">The existing instance of ColorType.</param>
/// <param name="xlColor">Color in ClosedXML format.</param>
/// <param name="isDifferential">Flag specifiying that the color should be saved in
/// differential format (affects the transparent color processing).</param>
/// <returns>The original color in OpenXML format.</returns>
public static T FromClosedXMLColor <T>(this X14.ColorType openXMLColor, XLColor xlColor, bool isDifferential = false)
where T : X14.ColorType
{
var adapter = new X14ColorTypeAdapter(openXMLColor);
FillFromClosedXMLColor(adapter, xlColor, isDifferential);
return((T)adapter.ColorType);
}
public IXLRange ApplyDataInvalidCellFormat(IXLRange range)
{
range.Style.Border.DiagonalBorder = XLBorderStyleValues.Thin;
range.Style.Border.DiagonalUp = true;
range.Style.Border.DiagonalDown = true;
range.Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Text2);
return(range);
}
private void TableHeaderStyle(IXLRange range, IXLRow row)
{
range.Style.Font.Bold = true;
range.Style.Fill.BackgroundColor = XLColor.FromArgb(0xBFBFBF);
range.Cells().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
range.Cells().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
row.Height = 33;
}
