/// <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);
}
/// <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;
}
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);
}
}
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);
}
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);
}
/// <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);
}
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 SetCellFontColor(int row, string column, System.Drawing.Color color)
{
try
{
var cell = ActiveSheet.Cell(row, column);
cell.Style.Font.FontColor = XLColor.FromColor(color);
}
catch
{
throw new ArgumentException("Could not set color of given cell.");
}
}
public void FormatCellsAt(TableIndexRange range, ICellStyle style)
{
var cellsRange = _workbook.Worksheet(range.Name).Range
(
range.From.Row + 1,
range.From.Column + 1,
range.To.Row + 1,
range.To.Column + 1
);
var worksheet = _workbook.Worksheet(range.Name);
foreach (int columnIndex in range.EnumerateColumnsIndices())
{
worksheet.Column(columnIndex).Width = style.Width;
}
foreach (int rowIndex in range.EnumerateRowsIndices())
{
worksheet.Row(rowIndex).Height = style.Height;
}
cellsRange.Style.Fill.SetBackgroundColor(XLColor.FromColor(style.BackgroundColor));
foreach (var cellIndex in range)
{
worksheet.Cell(cellIndex.Row, cellIndex.Column).Hyperlink = new XLHyperlink(style.HyperLink);
}
cellsRange.Style.Border.SetLeftBorder(style.BorderStyle.Left.Style.ToExcelLineStyle());
cellsRange.Style.Border.SetLeftBorderColor(XLColor.FromColor(style.BorderStyle.Left.Color));
cellsRange.Style.Border.SetRightBorder(style.BorderStyle.Right.Style.ToExcelLineStyle());
cellsRange.Style.Border.SetRightBorderColor(XLColor.FromColor(style.BorderStyle.Right.Color));
cellsRange.Style.Border.SetTopBorder(style.BorderStyle.Top.Style.ToExcelLineStyle());
cellsRange.Style.Border.SetTopBorderColor(XLColor.FromColor(style.BorderStyle.Top.Color));
cellsRange.Style.Border.SetBottomBorder(style.BorderStyle.Bottom.Style.ToExcelLineStyle());
cellsRange.Style.Border.SetBottomBorderColor(XLColor.FromColor(style.BorderStyle.Bottom.Color));
cellsRange.Style.Alignment.SetHorizontal(style.HorizontalAlignment.ToExcelHorizontalAlignment());
cellsRange.Style.Alignment.SetVertical(style.VerticalAlignment.ToExcelVerticalAlignment());
cellsRange.Style.Font.SetFontName(style.FontName);
cellsRange.Style.Font.SetFontSize(style.FontSize);
cellsRange.Style.Font.SetFontColor(XLColor.FromColor(style.FontColor));
cellsRange.Style.Alignment.WrapText = style.TextWrapping.ToExcelTextWrapping();
}
/// <summary>
/// Creates line - Type Name Price Amount
/// And Total Amount with formula
/// </summary>
private static void CreateHeader(IXLWorksheet sheet, int row)
{
int column = 1;
sheet.Cell(row, column++).Value = "Type";
sheet.Cell(row, column++).Value = "Name";
sheet.Cell(row, column++).Value = "Price";
sheet.Cell(row, column++).Value = "Amount";
sheet.Cell(row, column++).Value = "Total price, P";
var range = sheet.Range($"A{row}:E{row}");
range.Style.Fill.BackgroundColor = XLColor.FromColor(Color.LightBlue);
range.Style.Font.Bold = true;
}
public IXLStyle GetStyle(IXLStyle Style)
{
Style.Alignment.Horizontal = this.Horizontal;
Style.Alignment.Vertical = this.Vertical;
Style.Border.BottomBorder = this.BottomBorder;
Style.Border.TopBorder = this.TopBorder;
Style.Border.LeftBorder = this.LeftBorder;
Style.Border.RightBorder = this.RightBorder;
Style.Fill.BackgroundColor = XLColor.FromColor(this.BackgroundColor);
Style.Font.Bold = this.Bold;
Style.Font.FontColor = XLColor.FromColor(this.FontColor);
Style.Font.FontName = this.FontName;
Style.Font.FontSize = this.FontSize;
Style.Font.Italic = this.Italic;
Style.Font.Strikethrough = this.Strikethrough;
Style.Font.Underline = this.Underline;
//Style.Font.VerticalAlignment = this.VerticalAlignment;
return(Style);
}
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);
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));
}
private static XLWorkbook GenerateExcelFromModel(WorkBook workBook)
{
//-------------------------------------------
// Create Workbook (integrated with using statement)
//-------------------------------------------
var xlWorkbook = new XLWorkbook
{
RightToLeft = workBook.WBProps.IsRightToLeft,
ColumnWidth = workBook.WBProps.DefaultColumnWidth,
RowHeight = workBook.WBProps.DefaultRowHeight
};
// Check sheet names are unique
var sheetNames = workBook.Sheets.Select(s => s.Name).ToList();
var uniqueSheetNames = sheetNames.Distinct().ToList();
if (sheetNames.Count != uniqueSheetNames.Count)
{
throw new Exception("Sheet names should be unique");
}
// Check any sheet available
if (workBook.Sheets.Count == 0)
{
throw new Exception("No sheet is available to create Excel workbook");
}
//-------------------------------------------
// Add Sheets one by one to ClosedXML Workbook instance
//-------------------------------------------
foreach (var sheet in workBook.Sheets)
{
// Set name
var xlSheet = xlWorkbook.Worksheets.Add(sheet.Name);
// Set protection level
var protection = xlSheet.Protect(sheet.ProtectionOptions.Password);
if (sheet.ProtectionOptions.Deletecolumns)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.DeleteColumns;
}
if (sheet.ProtectionOptions.Editobjects)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.EditObjects;
}
if (sheet.ProtectionOptions.Formatcells)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.FormatCells;
}
if (sheet.ProtectionOptions.Formatcolumns)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.FormatColumns;
}
if (sheet.ProtectionOptions.Formatrows)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.FormatRows;
}
if (sheet.ProtectionOptions.Insertcolumns)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.InsertColumns;
}
if (sheet.ProtectionOptions.Inserthyperlinks)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.InsertHyperlinks;
}
if (sheet.ProtectionOptions.Insertrows)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.InsertRows;
}
if (sheet.ProtectionOptions.Selectlockedcells)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.SelectLockedCells;
}
if (sheet.ProtectionOptions.Deleterows)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.DeleteRows;
}
if (sheet.ProtectionOptions.Editscenarios)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.EditScenarios;
}
if (sheet.ProtectionOptions.Selectunlockedcells)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.SelectUnlockedCells;
}
if (sheet.ProtectionOptions.Sort)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.Sort;
}
if (sheet.ProtectionOptions.UseAutoFilter)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.AutoFilter;
}
if (sheet.ProtectionOptions.UsePivotTablereports)
{
protection.Protect().AllowedElements = XLSheetProtectionElements.PivotTables;
}
// Set direction
if (sheet.WSProps.IsRightToLeft is not null)
{
xlSheet.RightToLeft = (bool)sheet.WSProps.IsRightToLeft;
}
// Set default column width
if (sheet.WSProps.DefaultColumnWidth is not null)
{
xlSheet.ColumnWidth = (double)sheet.WSProps.DefaultColumnWidth;
}
// Set default row height
if (sheet.WSProps.DefaultRowHeight is not null)
{
xlSheet.RowHeight = (double)sheet.WSProps.DefaultRowHeight;
}
// Set visibility
xlSheet.Visibility = sheet.WSProps.Visibility switch
{
SheetVisibility.Hidden => XLWorksheetVisibility.Hidden,
SheetVisibility.VeryHidden => XLWorksheetVisibility.VeryHidden,
_ => XLWorksheetVisibility.Visible
};
xlSheet.Columns().Style.Alignment.Horizontal = sheet.WSProps.DefaultTextAlign switch
{
TextAlign.Center => XLAlignmentHorizontalValues.Center,
TextAlign.Right => XLAlignmentHorizontalValues.Right,
TextAlign.Left => XLAlignmentHorizontalValues.Left,
TextAlign.Justify => XLAlignmentHorizontalValues.Justify,
_ => throw new ArgumentOutOfRangeException()
};
//-------------------------------------------
// Columns properties
//-------------------------------------------
foreach (var colProps in sheet.Columns)
{
// Infer XLAlignment from "ColumnProp"
var columnAlignmentHorizontalValue = colProps.TextAlign switch
{
TextAlign.Center => XLAlignmentHorizontalValues.Center,
TextAlign.Justify => XLAlignmentHorizontalValues.Justify,
TextAlign.Left => XLAlignmentHorizontalValues.Left,
TextAlign.Right => XLAlignmentHorizontalValues.Right,
_ => throw new ArgumentOutOfRangeException()
};
if (colProps.Width is not null)
{
if (colProps.Width.CalculateType == ColumnWidthCalculateType.AdjustToContents)
{
xlSheet.Column(colProps.ColumnNo).AdjustToContents();
}
else
{
xlSheet.Column(colProps.ColumnNo).Width = (double)colProps.Width.Value !;
}
}
if (colProps.AutoFit)
{
xlSheet.Column(colProps.ColumnNo).AdjustToContents();
}
if (colProps.IsHidden)
{
xlSheet.Column(colProps.ColumnNo).Hide();
}
xlSheet.Column(colProps.ColumnNo).Style.Alignment
.SetHorizontal(columnAlignmentHorizontalValue);
}
//-------------------------------------------
// Map Tables
//-------------------------------------------
foreach (var table in sheet.Tables)
{
foreach (var tableRow in table.Rows)
{
xlSheet.ConfigureRow(tableRow, sheet.Columns, sheet.IsLocked);
}
var tableRange = xlSheet.Range(table.StartLocation.Y, table.StartLocation.X,
table.EndLocation.Y, table.EndLocation.X);
// Config Outside-Border
XLBorderStyleValues?outsideBorder = GetXlBorderLineStyle(table.OutsideBorder.LineStyle);
if (outsideBorder is not null)
{
tableRange.Style.Border.SetOutsideBorder((XLBorderStyleValues)outsideBorder);
tableRange.Style.Border.SetOutsideBorderColor(XLColor.FromColor(table.OutsideBorder.Color));
}
// Config Inside-Border
XLBorderStyleValues?insideBorder = GetXlBorderLineStyle(table.InlineBorder.LineStyle);
if (insideBorder is not null)
{
tableRange.Style.Border.SetInsideBorder((XLBorderStyleValues)insideBorder);
tableRange.Style.Border.SetInsideBorderColor(XLColor.FromColor(table.InlineBorder.Color));
}
// Apply table merges here
foreach (var mergedCells in table.MergedCells)
{
xlSheet.Range(mergedCells).Merge();
}
}
//-------------------------------------------
// Map Rows
//-------------------------------------------
foreach (var row in sheet.Rows)
{
xlSheet.ConfigureRow(row, sheet.Columns, sheet.IsLocked);
}
//-------------------------------------------
// Map Cells
//-------------------------------------------
foreach (var cell in sheet.Cells)
{
if (cell.Visible is false)
{
continue;
}
xlSheet.ConfigureCell(cell, sheet.Columns, sheet.IsLocked);
}
// Apply sheet merges here
foreach (var mergedCells in sheet.MergedCells)
{
var rangeToMerge = xlSheet.Range(mergedCells).Cells();
var value = rangeToMerge.FirstOrDefault(r => r.IsEmpty() is false)?.Value;
rangeToMerge.First().SetValue(value);
xlSheet.Range(mergedCells).Merge();
}
}
return(xlWorkbook);
}
public void ExportToExcelWithFormatting(DataGridView dataGridView1)
{
string fileName;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "xls files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveFileDialog1.Title = "To Excel";
saveFileDialog1.FileName = this.Text + " (" + DateTime.Now.ToString("yyyy-MM-dd") + ")";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
fileName = saveFileDialog1.FileName;
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add(this.Text);
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
worksheet.Cell(1, i + 1).Value = dataGridView1.Columns[i].Name;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cell(i + 2, j + 1).Value = dataGridView1.Rows[i].Cells[j].Value.ToString();
if (worksheet.Cell(i + 2, j + 1).Value.ToString().Length > 0)
{
XLAlignmentHorizontalValues align;
switch (dataGridView1.Rows[i].Cells[j].Style.Alignment)
{
case DataGridViewContentAlignment.BottomRight:
align = XLAlignmentHorizontalValues.Right;
break;
case DataGridViewContentAlignment.MiddleRight:
align = XLAlignmentHorizontalValues.Right;
break;
case DataGridViewContentAlignment.TopRight:
align = XLAlignmentHorizontalValues.Right;
break;
case DataGridViewContentAlignment.BottomCenter:
align = XLAlignmentHorizontalValues.Center;
break;
case DataGridViewContentAlignment.MiddleCenter:
align = XLAlignmentHorizontalValues.Center;
break;
case DataGridViewContentAlignment.TopCenter:
align = XLAlignmentHorizontalValues.Center;
break;
default:
align = XLAlignmentHorizontalValues.Left;
break;
}
worksheet.Cell(i + 2, j + 1).Style.Alignment.Horizontal = align;
XLColor xlColor = XLColor.FromColor(dataGridView1.Rows[i].Cells[j].Style.SelectionBackColor);
worksheet.Cell(i + 2, j + 1).AddConditionalFormat().WhenLessThan(1).Fill.SetBackgroundColor(xlColor);
worksheet.Cell(i + 2, j + 1).Style.Font.FontName = dataGridView1.Font.Name;
worksheet.Cell(i + 2, j + 1).Style.Font.FontSize = dataGridView1.Font.Size;
}
}
}
worksheet.Columns().AdjustToContents();
workbook.SaveAs(fileName);
MessageBox.Show("Done");
}
}
private void SetCellBackgroundColor(int columnIndex, Color color)
{
var cell = ActiveWorksheet.Cell(CurrentRowIndex, columnIndex);
cell.Style.Fill.BackgroundColor = XLColor.FromColor(color);
}
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);
}
/// <summary>
/// Set caption style to excel cell
/// </summary>
/// <param name="cell">Excel cell</param>
public void SetCaptionStyle(IXLCell cell)
{
cell.Style.Fill.PatternType = XLFillPatternValues.Solid;
cell.Style.Fill.BackgroundColor = XLColor.FromColor(Color.FromArgb(184, 204, 228));
cell.Style.Font.Bold = true;
}
public void ExportWithFormatting(DataGridView dataGridView1)
{
var saveFileDialog1 = new SaveFileDialog
{
Filter = "xls files (*.xlsx)|*.xlsx|All files (*.*)|*.*|csv files (*.csv)|*.csv",
Title = "To Excel",
FileName = this.Text + " (" + DateTime.Now.ToString("yyyy-MM-dd") + ")"
};
if (saveFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
if (saveFileDialog1.FilterIndex == 3)
{
int columnCount = dgTransactions.ColumnCount;
string columnNames = "";
string[] output = new string[dgTransactions.RowCount + 1];
for (int i = 0; i < columnCount; i++)
{
columnNames += dgTransactions.Columns[i].Name.ToString() + ",";
}
output[0] += columnNames;
for (int i = 1; (i - 1) < dgTransactions.RowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
output[i] += dgTransactions.Rows[i - 1].Cells[j].Value.ToString() + ",";
}
}
System.IO.File.WriteAllLines(saveFileDialog1.FileName, output, System.Text.Encoding.UTF8);
}
else
{
string fileName = saveFileDialog1.FileName;
XLWorkbook workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add(this.Text);
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
worksheet.Cell(1, i + 1).Value = dataGridView1.Columns[i].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cell(i + 2, j + 1).Value = dataGridView1.Rows[i].Cells[j].Value.ToString();
if (worksheet.Cell(i + 2, j + 1).Value.ToString().Length > 0)
{
XLAlignmentHorizontalValues align;
switch (dataGridView1.Rows[i].Cells[j].Style.Alignment)
{
case DataGridViewContentAlignment.BottomRight:
align = XLAlignmentHorizontalValues.Right;
break;
case DataGridViewContentAlignment.MiddleRight:
align = XLAlignmentHorizontalValues.Right;
break;
case DataGridViewContentAlignment.TopRight:
align = XLAlignmentHorizontalValues.Right;
break;
case DataGridViewContentAlignment.BottomCenter:
align = XLAlignmentHorizontalValues.Center;
break;
case DataGridViewContentAlignment.MiddleCenter:
align = XLAlignmentHorizontalValues.Center;
break;
case DataGridViewContentAlignment.TopCenter:
align = XLAlignmentHorizontalValues.Center;
break;
default:
align = XLAlignmentHorizontalValues.Left;
break;
}
worksheet.Cell(i + 2, j + 1).Style.Alignment.Horizontal = align;
XLColor xlColor = XLColor.FromColor(dataGridView1.Rows[i].Cells[j].Style.SelectionBackColor);
worksheet.Cell(i + 2, j + 1).AddConditionalFormat().WhenLessThan(1).Fill.SetBackgroundColor(xlColor);
worksheet.Cell(i + 2, j + 1).Style.Font.FontName = dataGridView1.Font.Name;
worksheet.Cell(i + 2, j + 1).Style.Font.FontSize = dataGridView1.Font.Size;
}
}
}
worksheet.Columns().AdjustToContents();
workbook.SaveAs(fileName);
}
MessageBox.Show("Export Successful!");
}
public static void ExportToExcel(string ReportName, string feuille, string output, ObjectListView List, ObjectListView FooterList = null, double fontSize = 22, bool UseBackColor = false)
{
ListView.ColumnHeaderCollection properties = null;
int CurrentRow = 1;
int CurrentCol = 1;
if (List != null && List.Items.Count >= 1)
{
properties = (List.Columns);
}
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add(feuille);
using (workbook)
{
// Set Style for Report Title
using (var titleRange = worksheet.Range(CurrentRow, 1, CurrentRow, properties.Count))
{
titleRange.Merge();
var rangeStyle = titleRange.Style;
rangeStyle.Font.FontName = "Britannic Bold";
rangeStyle.Font.FontSize = fontSize;
rangeStyle.Font.Italic = true;
rangeStyle.Font.FontColor = XLColor.White;
rangeStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.CenterContinuous;
rangeStyle.Alignment.Vertical = XLAlignmentVerticalValues.Center;
rangeStyle.Alignment.WrapText = true;
rangeStyle.Fill.PatternType = XLFillPatternValues.Solid;
rangeStyle.Fill.BackgroundColor = XLColor.FromArgb(23, 55, 93);
}
// Set Report Title
worksheet.Cell(CurrentRow, 1).Value = ReportName;
worksheet.Rows(CurrentRow, 1).Height = 75;
// Fill next row = Header
CurrentRow++;
// Set Style for Report Header row
using (var headerRange = worksheet.Range(CurrentRow, 1, CurrentRow, properties.Count))
{
var rangeStyle = headerRange.Style;
rangeStyle.Font.Bold = true;
rangeStyle.Font.FontColor = XLColor.White;
rangeStyle.Fill.PatternType = XLFillPatternValues.Solid;
rangeStyle.Fill.BackgroundColor = XLColor.DarkBlue;
}
// Set Column titles
foreach (OLVColumn p in properties)
{
worksheet.Cell(CurrentRow, CurrentCol).Value = p.Text;
CurrentCol++;
}
// Fill data rows
CurrentRow++;
foreach (ListViewItem o in List.Items)
{
CurrentCol = 1;
foreach (OLVColumn p in properties)
{
worksheet.Cell(CurrentRow, CurrentCol).Value = o.SubItems[CurrentCol - 1].Text;
if (UseBackColor)
{
worksheet.Cell(CurrentRow, CurrentCol).AsRange().Style.Fill.BackgroundColor = XLColor.FromColor(o.SubItems[CurrentCol - 1].BackColor);
}
CurrentCol++;
}
CurrentRow++;
}
if (FooterList != null)
{
// Fill footer data rows
CurrentRow++;
foreach (ListViewItem o in FooterList.Items)
{
CurrentCol = 1;
foreach (OLVColumn p in properties)
{
worksheet.Cell(CurrentRow, CurrentCol).Value = o.SubItems[CurrentCol - 1].Text;
CurrentCol++;
}
CurrentRow++;
}
}
// Resize columns to fit contents
worksheet.Columns(1, properties.Count).AdjustToContents(2);
// Save Workbook
workbook.SaveAs(output);
}
}
private static ImageSize ReadFile(IXLWorksheet worksheet, string filename, int xPos, int yPos)
{
Console.WriteLine("Reading image " + filename);
var image = new Bitmap(filename);
var width = image.Width;
var height = image.Height;
for (int x = 0; x < width; x++)
{
for (int y = 0; y < height; y++)
{
var pixelColour = image.GetPixel(x, y);
if (pixelColour.A > 64)
{
worksheet.Cell(y + 1 + yPos, x + 1 + xPos).Style.Fill.BackgroundColor = XLColor.FromColor(Average(pixelColour));
}
}
// Console.WriteLine($"{filename} Progress: {Math.Round(((double)x / width) * 100, 2)}%");
}
return(new ImageSize {
Height = height, Width = width
});
}