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 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);
}
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 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);
}
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;
}
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 async Task <string> GenerateExcelReport(string jsonFilePath)
{
var options = new JsonSerializerOptions
{
AllowTrailingCommas = true
};
var jsonContent = await ReadHotelRatesJson(_jsonFilePath);
var hotelRates = JsonSerializer.Deserialize <HotelRates>(jsonContent, options);
var hotelName = hotelRates.hotel.name;
var fontColor = "#486891";
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.AddWorksheet(hotelName);
worksheet.Row(1).Style.Font.FontColor = XLColor.FromHtml(fontColor);
worksheet.Row(1).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
worksheet.Row(1).Style.Border.BottomBorderColor = XLColor.FromHtml(fontColor);
worksheet.Row(1).Cell(1).SetValue("ARRIVAL_DATE");
worksheet.Row(1).Cell(2).SetValue("DEPARTURE_DATE");
worksheet.Row(1).Cell(3).SetValue("PRICE");
worksheet.Row(1).Cell(4).SetValue("CURRENCY");
worksheet.Row(1).Cell(5).SetValue("RATENAME");
worksheet.Row(1).Cell(6).SetValue("ADULTS");
worksheet.Row(1).Cell(7).SetValue("BREAKFAST_INCLUDED");
int i = 2;
foreach (var rate in hotelRates.hotelRates)
{
var bgColor = i % 2 == 0 ? "#DCE6F0" : "#FFFFFF";
worksheet.Row(i).Style.Fill.BackgroundColor = XLColor.FromHtml(bgColor);
worksheet.Row(i).Style.Font.FontColor = XLColor.FromHtml(fontColor);
worksheet.Row(i).Cell(1).SetValue($"{rate.targetDay:dd.MM.yyyy}");
worksheet.Row(i).Cell(1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
worksheet.Row(i).Cell(2).SetValue($"{rate.targetDay.AddDays(rate.los):dd.MM.yyyy}");
worksheet.Row(i).Cell(2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
worksheet.Row(i).Cell(3).SetValue($"{rate.price.numericFloat:N2}");
worksheet.Row(i).Cell(4).SetValue(rate.price.currency);
worksheet.Row(i).Cell(5).SetValue(rate.rateName);
worksheet.Row(i).Cell(6).SetValue(rate.adults);
worksheet.Row(i).Cell(7).SetValue(rate.rateTags[0].shape ? 1 : 0);
i++;
}
worksheet.RangeUsed().SetAutoFilter(true);
worksheet.Columns().AdjustToContents();
workbook.SaveAs($"./output/report_{hotelRates.hotel.hotelID}.xlsx");
}
return("");
}
public static IXLCell SetGreenIfEqualToPrevious(this IXLCell cell)
{
cell.CellLeft(1);
var formula = $"={cell.Address}={cell.CellLeft(1).Address}";
cell.AddConditionalFormat().WhenIsTrue(formula)
.Fill.SetBackgroundColor(XLColor.FromHtml("#B4FFC8"));
return(cell);
}
public static IXLCell SetRedIfFalse(this IXLCell cell)
{
cell.CellLeft(1);
var formula = $"={cell.Address}=False";
cell.AddConditionalFormat().WhenIsTrue(formula)
.Fill.SetBackgroundColor(XLColor.FromHtml("#FFC1B4"));
return(cell);
}
private void WriteColumns(CremaDataTable dataTable, IXLWorksheet sheet)
{
var index = 1;
var rowIndex = 1;
if (this.settings.OmitAttribute == false)
{
sheet.Cell(rowIndex, index++).Value = CremaSchema.Tags;
sheet.Cell(rowIndex, index++).Value = CremaSchema.Enable;
}
foreach (var item in this.GetSortedColumn(dataTable))
{
var column = sheet.Column(index);
var cell = sheet.Cell(rowIndex, index++);
cell.Value = item.ColumnName;
if (item.Comment != string.Empty)
{
cell.Comment.AddText(item.Comment);
}
if (item.IsKey == true)
{
cell.Style.Font.Bold = true;
}
if (item.Unique == true)
{
cell.Style.Font.Italic = true;
}
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
if (item.DerivedTags.Color != null)
{
column.Style.Fill.BackgroundColor = XLColor.FromHtml(item.DerivedTags.Color);
}
}
if (dataTable.Parent != null)
{
var column = sheet.Column(index);
var cell = sheet.Cell(rowIndex, index++);
cell.Value = CremaSchema.RelationID;
cell.Comment.AddText("부모 자식 테이블의 관계값을 나타냅니다. 이 열의 값은 부모 시트의 행 번호를 나타냅니다.");
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
column.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
column.Style.Border.RightBorder = XLBorderStyleValues.Thin;
}
if (this.settings.OmitSignatureDate == false)
{
sheet.Cell(rowIndex, index++).Value = CremaSchema.Creator;
sheet.Cell(rowIndex, index++).Value = CremaSchema.CreatedDateTime;
sheet.Cell(rowIndex, index++).Value = CremaSchema.Modifier;
sheet.Cell(rowIndex, index++).Value = CremaSchema.ModifiedDateTime;
}
}
private void FormatSymbolColumns()
{
var columnsLetters = ColumnsToProcess.Split(',');
var nonemptyDataRows = Worksheet.RowsUsed();
foreach (var dataRow in nonemptyDataRows)
{
foreach (var column in columnsLetters)
{
var value = dataRow.Cell(column).GetValue <string>().Trim();
if (value.Equals("A"))
{
dataRow.Cell(column).Style.Fill.BackgroundColor = XLColor.FromHtml("#ffbf47");
dataRow.Cell(column).Value = "!";
dataRow.Cell(column).Style.Font.FontName = "Times New Roman";
dataRow.Cell(column).Style.Font.FontSize = 22;
dataRow.Cell(column).Style.Font.Bold = true;
}
else if (value.Equals("G"))
{
dataRow.Cell(column).Value = "ü";
dataRow.Cell(column).Style.Fill.BackgroundColor = XLColor.FromHtml("#85994b");
dataRow.Cell(column).Style.Font.FontName = "Wingdings";
dataRow.Cell(column).Style.Font.FontSize = 24;
}
else if (value.Equals("R"))
{
dataRow.Cell(column).Value = "û";
dataRow.Cell(column).Style.Fill.BackgroundColor = XLColor.FromHtml("#b10e1e");
dataRow.Cell(column).Style.Font.FontColor = XLColor.White;
dataRow.Cell(column).Style.Font.FontName = "Wingdings";
dataRow.Cell(column).Style.Font.FontSize = 24;
}
if (value.Equals("G") || value.Equals("A") || value.Equals("R"))
{
dataRow.Cell(column).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
dataRow.Cell(column).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
}
}
dataRow.Cells().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
//Column F has both numbers and text stored.
var columnValue = dataRow.Cell("F").GetValue <string>().Trim();
if (dataRow.RowNumber() > 1 && !dataRow.IsEmpty() && !columnValue.Equals("N/A"))
{
int overLimitCount;
if (int.TryParse(columnValue, out overLimitCount))
{
dataRow.Cell("F").Style.NumberFormat.NumberFormatId = 3;
dataRow.Cell("F").Value = overLimitCount;
}
}
}
}
public void SetHeaders()
{
#region Add Data
var _ws = _wb.Worksheets.Add("Reporte de Horas");
GetWeek(_date, new CultureInfo("es-ES"), out _begining, out _end);
_ws.Cell("A1").Value = "Mes de " + _date.ToString("MMMM");
_ws.Cell("A2").Value = "Semana de " + _begining.ToString("dd/MM/yyyy") + " al " + _end.AddDays(-1).ToString("dd/MM/yyyy");
_ws.Cell("A4").Value = "Actividad";
_ws.Cell("A5").Value = "Programación";
foreach (string column in _cells)
{
if (!column.Equals("A"))
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("es-ES");
_ws.Cell(column + "4").Value = _begining.ToString("dddd") + " " + _begining.Day;
_ws.Cell(column + "5").Value = _workHours;
_ws.Cell(column + "6").Value = _workHours;
_begining = _begining.AddDays(1);
}
}
#endregion
#region set Styles
_ws.Style.Font.SetFontName("Calibri");
_ws.Cell("A1").Style.Font.SetFontSize(22.0);
_ws.Cell("A1").Style.Font.SetBold(true);
_ws.Cell("A2").Style.Font.SetFontSize(16.0);
_ws.Cell("A2").Style.Font.SetBold(true);
foreach (string column in _cells)
{
_ws.Cell(column + "4").Style.Font.SetFontSize(18.0);
_ws.Cell(column + "4").Style.Font.SetBold(true);
_ws.Cell(column + "4").Style.Font.SetUnderline();
_ws.Cell(column + "4").Style.Fill.BackgroundColor = XLColor.FromHtml("5b9bd5");
_ws.Column("A").Width = 14;
if (!column.Equals("A"))
{
_ws.Column(column).AdjustToContents();
_ws.Cell(column + "6").Style.Fill.BackgroundColor = XLColor.FromHtml("ffc000");
}
}
_ws.Row(1).AdjustToContents();
_ws.Row(2).AdjustToContents();
_ws.Row(3).AdjustToContents();
_ws.Row(4).AdjustToContents();
_ws.Row(5).AdjustToContents();
_ws.Row(6).AdjustToContents();
#endregion
}
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 void TMSEmployeeExportExcel(string searchprm, int PageSize)
{
EMPTMSPara ObjPara = JsonConvert.DeserializeObject <EMPTMSPara>(searchprm,
new JsonSerializerSettings
{
NullValueHandling = NullValueHandling.Ignore,
DateFormatString = "dd/MM/yyyy"
});
ObjPara.OrderBy = "EmployeeNo";
ObjPara.OrderDirection = "Desc";
ObjPara.Page = 1;
ObjPara.PageSize = PageSize;
DataTable dt = new DataTable();
dt = ReportManager.EmployeeTMSSummaryReport(ObjPara);
string fileName = "TMS_Employee_Summary_" + SystemConfig.CurrentDate.ToString("MM-dd-yyyy");
string[] RemoveColumn = { "CompanyID", "TargetDisplayID", "ReturnDisplay", "TotalRecord", "CreatedUser", "CreatedDate" };
for (int i = 0; i < RemoveColumn.Length; i++)
{
if (dt.Columns.Contains(RemoveColumn[i]))
{
dt.Columns.Remove(RemoveColumn[i]);
}
}
List <ExcelCellModel> listd = new List <ExcelCellModel>();
ExcelCellModel ex = new ExcelCellModel();
DateTime mytime = ObjPara.FromDate.Value;
List <int> columnIndex = new List <int>();
int b = 9;
for (DateTime day = ObjPara.FromDate.Value; day < ObjPara.ToDate.Value; day = day.AddDays(1.0))
{
if (BDatetime.isWeekDay(day))
{
columnIndex.Add(b);
}
b = b + 1;
}
ex.BackgroundColorInfo.ColunmIndex = columnIndex;
ex.BackgroundColorInfo.BackgorundColor = XLColor.FromHtml("#FFFFCC");
ex.BackgroundColorInfo.FontColor = XLColor.Black;
//ex. = XLColor.Black;// CellValue(0,PageSize)
listd.Add(ex);
FileInputHelper.ExportExcel(dt, fileName, "TMS Employee Summary", true, listd);
// return fileName;
}
private void WriteType(XLWorkbook workbook, CremaDataType item)
{
var worksheet = workbook.AddWorksheet(this.settings.NameEllipsis($"${item.Name}"));
if (item.Tags.Color != null)
{
worksheet.SetTabColor(XLColor.FromHtml(item.Tags.Color));
}
this.WriteMembers(item, worksheet);
this.AdjustMembers(item, worksheet);
worksheet.SheetView.Freeze(1, 1);
}
/// <summary>
///Overwrite the excel styles for "Evaluation Report Initial and Final result" to match the template
/// </summary>
private static void ChangeStyleForEvaluationReport(IXLWorksheet wsReport)
{
//Remove border which crosses the logo from first table
wsReport.Range("A1:Q1").Style.Border.BottomBorder = XLBorderStyleValues.None;
wsReport.Range("A2:Q2").Style.Border.TopBorder = XLBorderStyleValues.None;
// var sdlGreen = XLColor.FromHtml("#00A89F");
//wsReport.Style.Border.SetOutsideBorderColor(sdlGreen);
//Dummy collor used later to replace with gradient
wsReport.Cell("D33").Style.Fill.SetBackgroundColor(XLColor.FromHtml("#B6B6B6")); // use some unique color
wsReport.Cell("G33").Style.Fill.SetBackgroundColor(XLColor.FromHtml("#B6B6B7")); // use some unique color
wsReport.Cell("D33").Style.Border.SetRightBorderColor(XLColor.White);
}
public async Task <byte[]> CreateExcelFileStream(Case Case)
{
string path = Path.Combine(appEnvironment.ContentRootPath + "/wwwroot", "Template.xlsx");
XLWorkbook workbook;
try
{
workbook = new XLWorkbook(path);
}
catch
{
await telegramClient.SendTextMessageAsync(Case.chatId, "Не найден Excel шаблон");
return(null);
}
var worksheet = workbook.Worksheet(1);
var rowCounter = 2;
foreach (var asset in Case.Assets)
{
worksheet.Cell($"A{rowCounter}").Value = $"{asset.Symbol}/{asset.Pair}";
worksheet.Cell($"B{rowCounter}").Value = RoundNum(asset.AvrPrice);
worksheet.Cell($"C{rowCounter}").Value = asset.Pair;
worksheet.Cell($"D{rowCounter}").Value = RoundNum(asset.Quantity);
worksheet.Cell($"E{rowCounter}").Value = RoundNum(asset.CurPrice);
worksheet.Cell($"F{rowCounter}").Value = asset.Pair;
worksheet.Cell($"G{rowCounter}").Value = RoundNum(asset.CurValue);
worksheet.Cell($"H{rowCounter}").Value = asset.Pair;
worksheet.Cell($"I{rowCounter}").Value = RoundNum(asset.Profit);
worksheet.Cell($"J{rowCounter}").Value = asset.Pair;
worksheet.Cell($"K{rowCounter}").Value = asset.PercentProfit.ToString("P");
worksheet.Cell($"L{rowCounter}").Value = asset.Share.ToString("P");
IXLRangeAddress address = worksheet.Range(worksheet.Cell($"I{rowCounter}").Address, worksheet.Cell($"K{rowCounter}").Address).RangeAddress;
worksheet.Range(address).Style.Font.FontColor = asset.Profit > 0 ? XLColor.FromHtml("#1D8348") : XLColor.FromHtml("#943126");
rowCounter++;
}
worksheet.Columns().AdjustToContents();
string savePath = Path.Combine(appEnvironment.ContentRootPath + "/wwwroot", $"{Guid.NewGuid().ToString()}.xlsx");
workbook.SaveAs(savePath);
var bytes = System.IO.File.ReadAllBytes(savePath);
System.IO.File.Delete(savePath);
return(bytes);
}
public void CanChangeSparklineStyle()
{
var ws = new XLWorkbook().AddWorksheet("Sheet 1");
var group = ws.SparklineGroups.Add("A1", "B1:Z1");
group.Style = XLSparklineTheme.Colorful1;
Assert.AreEqual(XLColor.FromHtml("FF5F5F5F"), group.Style.SeriesColor);
Assert.AreEqual(XLColor.FromHtml("FFFFB620"), group.Style.NegativeColor);
Assert.AreEqual(XLColor.FromHtml("FFD70077"), group.Style.MarkersColor);
Assert.AreEqual(XLColor.FromHtml("FF56BE79"), group.Style.HighMarkerColor);
Assert.AreEqual(XLColor.FromHtml("FFFF5055"), group.Style.LowMarkerColor);
Assert.AreEqual(XLColor.FromHtml("FF5687C2"), group.Style.FirstMarkerColor);
Assert.AreEqual(XLColor.FromHtml("FF359CEB"), group.Style.LastMarkerColor);
}
private void WriteTable(XLWorkbook workbook, CremaDataTable dataTable)
{
var worksheet = workbook.AddWorksheet(this.settings.NameEllipsis(dataTable.Name));
if (dataTable.Tags.Color != null)
{
worksheet.SetTabColor(XLColor.FromHtml(dataTable.Tags.Color));
}
this.WriteColumns(dataTable, worksheet);
this.WriteRows(dataTable, worksheet);
this.AdjustColumns(dataTable, worksheet);
worksheet.SheetView.Freeze(1, dataTable.PrimaryKey.Length);
}
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);
}
}
public void Create(String filePath)
{
var workbook = new XLWorkbook();
var ws = workbook.AddWorksheet("Sheet1");
ws.FirstCell().SetValue(1)
.CellBelow().SetValue(1)
.CellBelow().SetValue(2)
.CellBelow().SetValue(3);
ws.RangeUsed().AddConditionalFormat().ColorScale()
.LowestValue(XLColor.FromHtml("#FFFF7128"))
.HighestValue(XLColor.FromHtml("#FFFFEF9C"));
workbook.SaveAs(filePath);
}
private static void SetCssStyle <T>(this IXLFormattedText <T> richText, XmlElement element)
{
var xmlStyle = element.GetAttribute("style");
if (string.IsNullOrEmpty(xmlStyle) != true)
{
var cssStyles = xmlStyle.Split(';').Select(str => {
var pair = str.Split(':');
pair[0] = pair[0].Trim();
pair[1] = pair[1].Trim();
return(Tuple.Create(pair[0], pair[1]));
});
var cssColor = cssStyles.FirstOrDefault(pair => pair.Item1 == "color");
if (cssColor != null)
{
richText.SetFontColor(XLColor.FromHtml(cssColor.Item2));
}
}
}
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);
}
private void FormatStoreHeader(string storeName, ref IXLWorksheet ws, ref int row)
{
int startRow = row;
ws.Range(row, 1, row, 17).Merge().Value = storeName;
ws.Range(row, 1, row++, 17).Style.Fill.BackgroundColor = XLColor.FromHtml("#c6efce");
ws.Range(row, 1, row + 1, 1).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Ingredient Code"));
ws.Range(row, 2, row + 1, 2).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Ingredient Name"));
ws.Range(row, 3, row + 1, 3).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Base UOM"));
ws.Range(row, 4, row + 1, 4).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Type"));
ws.Range(row, 5, row + 1, 5).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Open Balance"));
ws.Range(row, 6, row, 7).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Stock In"));
ws.Cell(row + 1, 6).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Received"));
ws.Cell(row + 1, 7).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Transfer In"));
ws.Range(row, 8, row, 13).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Stock Out"));
ws.Cell(row + 1, 8).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Return"));
ws.Cell(row + 1, 9).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Transfer Out"));
ws.Cell(row + 1, 10).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Damage"));
ws.Cell(row + 1, 11).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Wastage"));
ws.Cell(row + 1, 12).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Others"));
ws.Cell(row + 1, 13).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Used for Self Made Ingredient"));
ws.Cell(row + 1, 13).Style.Alignment.WrapText = true;
ws.Range(row, 14, row + 1, 14).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Sale"));
ws.Range(row, 15, row + 1, 15).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Close Balance"));
ws.Range(row, 16, row + 1, 16).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Manual Close Balance"));
ws.Range(row, 17, row + 1, 17).Merge().SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Adjust"));
ws.Range(startRow + 1, 1, startRow + 2, 17).Style.Fill.BackgroundColor = XLColor.FromHtml("#d9d9d9");
ws.Range(startRow + 1, 1, startRow + 2, 17).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(startRow + 1, 1, startRow + 2, 17).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Range(startRow, 1, startRow + 2, 17).Style.Font.Bold = true;
ws.Range(startRow, 1, startRow + 2, 17).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, startRow + 2, 17).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Range(startRow, 1, startRow + 2, 17).Style.Border.InsideBorderColor = XLColor.FromHtml("#000000");
ws.Range(startRow, 1, startRow + 2, 17).Style.Border.OutsideBorderColor = XLColor.FromHtml("#000000");
row++;
}
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);
}
private void AddItemsInExsel(ICollection <Account> accounts)
{
foreach (var account in accounts)
{
worksheet.Cell(p.Y, p.X++).Value = account.name;
worksheet.Cell(p.Y, p.X++).Value = account.debitStart;
worksheet.Cell(p.Y, p.X++).Value = account.creditStart;
worksheet.Cell(p.Y, p.X++).Value = account.debitPeroid;
worksheet.Cell(p.Y, p.X++).Value = account.creditPeriod;
worksheet.Cell(p.Y, p.X++).Value = account.debitEnd;
worksheet.Cell(p.Y, p.X++).Value = account.creditEnd;
p.X = 1;
p.Y++;
}
worksheet.Columns("A", "H").Width = 20;
worksheet.Columns("A", "H").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
worksheet.Range("A1:G2").Style.Fill.BackgroundColor = XLColor.FromHtml("#A8E88D");
Stream fs = new MemoryStream();
worksheet.Range("A1:G2").Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Range("A1:G2").Style.Border.BottomBorderColor = XLColor.FromHtml("#DBCB90");
workbook.SaveAs(@"C:\Odz\Hello.xlsx");
}
private void WriteSheets(XLWorkbook workbook, IProgress progress)
{
var step = new StepProgress(progress);
step.Begin(this.dataSet.Tables.Count);
foreach (var item in this.dataSet.Tables)
{
var worksheet = workbook.AddWorksheet(this.settings.NameEllipsis(item.Name));
if (item.Tags.Color != null)
{
worksheet.SetTabColor(XLColor.FromHtml(item.Tags.Color));
}
this.WriteColumns(item, worksheet);
this.WriteRows(item, worksheet);
this.AdjustColumns(item, worksheet);
worksheet.SheetView.Freeze(1, item.PrimaryKey.Length);
step.Next("write {0} : {1}", ConsoleProgress.GetProgressString(step.Step + 1, this.dataSet.Tables.Count), item.Name);
}
step.Complete();
}
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 CreateReportHeaderNewFilterTime(IXLWorksheet ws, int totalCols, DateTime fromDate, DateTime toDate, int fromTime, int toTime, 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;
// Time
ws.Range(3, 1, 3, totalCols).Merge();
ws.Cell(3, 1).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time: From") + " " + fromTime.ToString("0#") + ":00 " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("To") + " " + toTime.ToString("0#") + ":00 ";
ws.Row(3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
// Date
ws.Range(4, 1, 4, totalCols).Merge();
ws.Cell(4, 1).Value = _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date: From") + " " + fromDate.ToString("MM/dd/yyyy") + " " + _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("To") + " " + toDate.ToString("MM/dd/yyyy");
ws.Row(4).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);
ws.Row(4).Style.Font.SetBold(true);
// Format header report
ws.Range(1, 1, 4, totalCols).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 4, totalCols).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
private void CreateColumns(IXLWorksheet worksheet)
{
var rowAdress = 5;
foreach (Columns column in Enum.GetValues(typeof(Columns)))
{
worksheet.Cell(rowAdress, (int)column).Value = column.ToString();
if (column == Columns.勤務時間 || column == Columns.労働時間 || column == Columns.休憩時間)
{
worksheet.Column((int)column).Width = 12;
}
if (column == Columns.詳細)
{
worksheet.Column((int)column).Width = 6;
}
}
var columncells = worksheet.Row(rowAdress).CellsUsed();
columncells.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
columncells.Style.Fill.BackgroundColor = XLColor.FromHtml("#f0ffff");
SetBorder(columncells);
}
