public void ExcelColorTest()
{
ListSerializable <User> users = new UserList();
users.Add(new User("Toto", "Titi"));
users.Add(new User("Tata", "Roro"));
string currentDirectory = Directory.GetCurrentDirectory();
string ExcelFile = Path.Combine(currentDirectory, "test.xlsx");
ExcelWriter <User> writer = new ExcelWriter <User>("Users", new StringList {
"Name", "Firstname"
});
writer.Write <UserList>(users, ExcelFile);
ExcelColor color = null;
using (ExcelPackage excel = new ExcelPackage(new FileInfo(ExcelFile)))
{
var worksheet = excel.Workbook.Worksheets["Users"];
color = worksheet.Cells["A1:B2"].Style.Font.Color;
color.SetColor(System.Drawing.Color.White);
}
System.Windows.Media.Color colorTransform = Color.ToMediaColor(color);
Assert.AreEqual(colorTransform.ToDrawingColor().ToArgb(), System.Drawing.Color.White.ToArgb());
FileManager.Delete(ExcelFile);
}
/// <summary>
/// Format cells in the range into a table
/// </summary>
/// <param name="range">The table's cells</param>
/// <param name="color">Color palette of the table</param>
/// <param name="showColumnHeader">Defines if the table has column header</param>
/// <param name="showRowHeader">Defines if the table has row header</param>
public static void FormatAsTable(this ExcelRange range, ExcelColor color = ExcelColor.Primary, bool showColumnHeader = true, bool showRowHeader = true)
{
var ws = range.Worksheet;
var palette = PaletteStorage.GetPalette(color);
range.BorderEverywhere();
//First fill every cell as Light color from palette
range.Fill(palette.LightColor);
//Row header's color is main
if (showRowHeader)
{
//row header starts from first row first column, and ends at first row last column
var headerRange = ws.Cells[range.Start.Row, range.Start.Column, range.End.Row, range.Start.Column];
headerRange.Fill(palette.MainColor);
}
//Column header's color is main
if (showColumnHeader)
{
//Column header starts from first row first column, and ends at first row last column
var headerRange = ws.Cells[range.Start.Row, range.Start.Column, range.Start.Row, range.End.Column];
headerRange.Fill(palette.MainColor);
}
}
private static ExcelCell.Color GetFontColor(ExcelColor excelColor)
{
var color = new ExcelCell.Color()
{
rgb = string.IsNullOrEmpty(excelColor.Rgb) ? null : excelColor.Rgb,
theme = excelColor.Theme,
};
if (color.theme.HasValue)
{
color.tint = excelColor.Tint;
}
if (IsEmptyColor(color))
{
return(null);
}
// 通常色の場合は無視.
if (color.theme == eThemeSchemeColor.Text1 && color.tint == 0)
{
return(null);
}
return(color);
}
public ColorCacheItem(ExcelColor color)
{
Red = color.Red;
Green = color.Green;
Blue = color.Blue;
Alpha = color.Alpha;
}
// 把屬性設定到 Excel 上面
private void SetPerpertyToExcel(string strKey, string strColumn, ICell Cell, IWorkbook WorkBook)
{
string strKeyWord = GetKeyWord(strKey, strColumn);
// 沒有就不需要做設定
if (m_dictProperty.ContainsKey(strKeyWord) == false)
{
return;
}
// 把屬性一個一個設定
Dictionary <ExcelProperty, object> dictProperty = m_dictProperty[strKeyWord];
if (dictProperty.ContainsKey(ExcelProperty.Font_Color))
{
// 取得顏色
short sColor = ExcelColor.GetColor(dictProperty[ExcelProperty.Font_Color]);
// 如果同色就不需要改變
short sTargetColor = GetCellFontColor(WorkBook, Cell);
if (sColor == sTargetColor)
{
return;
}
// 找看看有沒有舊的可以用
ICellStyle CellStyle = GetCellStyleByFontColor(WorkBook, Cell, sColor);
Cell.CellStyle = CellStyle;
}
}
public ExcelColorInstance(ObjectInstance prototype, ExcelColor excelColor)
: this(prototype)
{
if (excelColor == null)
{
throw new ArgumentNullException("excelColor");
}
m_excelColor = excelColor;
}
internal static string ToHexCode(this ExcelColor excelColor)
{
if (excelColor != null && excelColor.Rgb != null && excelColor.Rgb.Length > 3)
{
return("#" + excelColor.Rgb.Substring(2));
}
else
{
return(null);
}
}
private string CreateColor(ExcelColor color)
{
var rgb = color.LookupColor(color);
if (!string.IsNullOrWhiteSpace(color.Rgb))
{
return($"#{color.Rgb.Substring(2)}");
}
else if (color.Indexed > 0 && color.Indexed < 64)
{
return($"#{rgb.Substring(3)}");
}
return(null);
}
public static HslColor RgbToHls(ExcelColor rgbColor)
{
var hslColor = new HslColor();
var r = (double)rgbColor.Red / 255;
var g = (double)rgbColor.Green / 255;
var b = (double)rgbColor.Blue / 255;
var a = (double)rgbColor.Alpha / 255;
var min = Math.Min(r, Math.Min(g, b));
var max = Math.Max(r, Math.Max(g, b));
var delta = max - min;
if (max == min)
{
hslColor.A = a;
hslColor.H = 0;
hslColor.S = 0;
hslColor.L = max;
return(hslColor);
}
hslColor.L = (min + max) / 2;
if (hslColor.L < 0.5)
{
hslColor.S = delta / (max + min);
}
else
{
hslColor.S = delta / (2.0 - max - min);
}
if (r == max)
{
hslColor.H = (g - b) / delta;
}
if (g == max)
{
hslColor.H = 2.0 + (b - r) / delta;
}
if (b == max)
{
hslColor.H = 4.0 + (r - g) / delta;
}
hslColor.H *= 60;
if (hslColor.H < 0)
{
hslColor.H += 360;
}
hslColor.A = a;
return(hslColor);
}
/// <summary>
/// Convert a excel color to hex color
/// </summary>
/// <param name="color"></param>
/// <returns>Hex color</returns>
public static string ExcelColorToHex(ExcelColor color)
{
if (color == null)
{
return("transparent");
}
byte a = (byte)(Convert.ToUInt32(color.Rgb.Substring(0, 2), 16));
byte r = (byte)(Convert.ToUInt32(color.Rgb.Substring(2, 2), 16));
byte g = (byte)(Convert.ToUInt32(color.Rgb.Substring(4, 2), 16));
byte b = (byte)(Convert.ToUInt32(color.Rgb.Substring(6, 2), 16));
var colorRgb = Color.FromArgb(a, r, g, b);
return("#" + colorRgb.R.ToString("X2") + colorRgb.G.ToString("X2") + colorRgb.B.ToString("X2"));
}
public CStyleApplier CellColor(IArgbColor foregroundColor, IArgbColor backgroundColor, FillPattern pattern)
{
FillForegroundColor = foregroundColor;
FillBackgroundColor = backgroundColor;
if (ExcelColor.GetIndex(foregroundColor) == ExcelColor.AutomaticIndex &&
ExcelColor.GetIndex(backgroundColor) == ExcelColor.AutomaticIndex)
{
FillPattern = FillPattern.NoFill;
}
else
{
FillPattern = pattern;
}
return(this);
}
public void Reset()
{
TopStyle = CellBorderStyle.NoLine;
TopColor = ExcelColor.WindowTextForPattern;
BottomStyle = CellBorderStyle.NoLine;
BottomColor = ExcelColor.WindowTextForPattern;
LeftStyle = CellBorderStyle.NoLine;
LeftColor = ExcelColor.WindowTextForPattern;
RightStyle = CellBorderStyle.NoLine;
RightColor = ExcelColor.WindowTextForPattern;
DiagonalUp = false;
DiagonalDown = false;
DiagonalStyle = CellBorderStyle.NoLine;
DiagonalColor = ExcelColor.WindowTextForPattern;
}
private static ExcelCell.Color GetBackgroundColor(ExcelColor excelColor)
{
var color = new ExcelCell.Color()
{
rgb = string.IsNullOrEmpty(excelColor.Rgb) ? null : excelColor.Rgb,
theme = excelColor.Theme,
};
if (color.theme.HasValue)
{
color.tint = excelColor.Tint;
}
if (IsEmptyColor(color))
{
return(null);
}
return(color);
}
private static void SetColor(ExcelColor excelColor, ExcelCell.Color color)
{
if (IsEmptyColor(color))
{
return;
}
if (color.theme.HasValue)
{
excelColor.SetColor(color.theme.Value);
if (color.tint.HasValue)
{
excelColor.Tint = color.tint.Value;
}
}
else
{
excelColor.SetColor(ColorTranslator.FromHtml("#" + color.rgb));
}
}
private IBaseEntity GetBaseEntity(IExcelWorksheetEntity excelWorksheet,
string data, int column, ExcelConfiguration excelConfiguration)
{
IBaseEntity baseEntity = new BaseEntity()
{
Value = data
};
if (column == excelConfiguration.DataColumn
.Datas.FirstOrDefault(p => p.ColumnType == (int)ColumnType.Section)?.Nomer ||
column == excelConfiguration.DataColumn.Datas
.FirstOrDefault(p => p.ColumnType == (int)ColumnType.Language)?.Nomer)
{
string name = excelConfiguration.NameColumnSection.MainLanguage;
ITranslationEntity entity = new TranslationEntity();
entity.Language = LanguageHolder.GetISOCodes(name, _dataNormalization);
entity.Value = data;
return(entity);
}
if (column == excelConfiguration.DataColumn.Datas
.FirstOrDefault(p => p.ColumnType == (int)ColumnType.Picture)?.Nomer)
{
IDataResult <string> colorNameResult =
_readExcelData.GetColorValue(excelWorksheet);
IExcelColor excelColor = new ExcelColor();
if (colorNameResult.Success)
{
Color color = ColorTranslator.FromHtml("#" + colorNameResult.Data);
excelColor.R = color.R;
excelColor.G = color.G;
excelColor.B = color.B;
}
return(excelColor);
}
return(baseEntity);
}
private static void CheckThatAllExpectedCellsAreColored(ExcelPackage excelPackage, HashSet <Tuple <int, int> > fieldsInvalidated)
{
ExcelWorksheet workbookWorksheet = excelPackage.Workbook.Worksheets[1];
int columnEnd = workbookWorksheet.Dimension.Columns;
int rowEnd = workbookWorksheet.Dimension.Rows;
for (int i = 1; i < rowEnd; i++)
{
for (int j = 1; j < columnEnd; j++)
{
ExcelColor cellColor = workbookWorksheet.Cells[i, j].Style.Fill.BackgroundColor;
if (fieldsInvalidated.Contains(new Tuple <int, int>(i, j)))
{
cellColor.Rgb
.Should().NotBeNull();
}
else
{
cellColor.Rgb
.Should().BeNull();
}
}
}
}
/// <summary>
/// Insert a table filled with data from an IEnumerable
/// </summary>
/// <typeparam name="T">Type of inserted objects</typeparam>
/// <param name="ws">Worksheet in wich the table is created</param>
/// <param name="x">Starting row of the table</param>
/// <param name="y">Starting column of the table</param>
/// <param name="data">IEnumerable in wich the data to insert is stored</param>
/// <param name="title">The table's title</param>
/// <param name="color">The table's color</param>
/// <param name="showHeader">Defines if the table has header</param>
public static void InsertTable <T>(this ExcelWorksheet ws, int x, int y, IEnumerable <T> data, string title = null, ExcelColor color = ExcelColor.Primary, bool showHeader = true)
{
//Get required Type data
var dataType = data.First().GetType();
var props = dataType.GetProperties();
//We use the data often, don't want to always iterate on it
var dataArray = data.ToArray();
//Get table's range
//If we show a header the height of the table is 1 bigger
var tableHeight = dataArray.Length + (showHeader ? 1 : 0);
var tableLength = props.Length;
var tableRange = ws.Cells[y, x, y + tableHeight - 1, x + tableLength - 1];
//Format table
//Get the starting row for inserting data
int startRow = tableRange.FormatAsTableWithTitle(title, color, showHeader, false);
//Refresh table range
tableRange = ws.Cells[startRow, x, startRow + tableHeight - 1, x + tableLength - 1];
//Write header
if (showHeader)
{
//If we show header then it contains the name of the properties
for (int i = 0; i < props.Length; i++)
{
var cell = ws.Cells[startRow, x + i];
//If the property has Display attribute "Name" then the header will contain that instead of the property's name from code
string name = ((DisplayAttribute)(props[i].GetCustomAttributes(typeof(DisplayAttribute), true)?.FirstOrDefault()))?.Name;
if (name != null)
{
cell.Value = name;
}
else
{
cell.Value = props[i].Name;
}
//Ha van description attribute (és még nincs más komment) akkor kommentben a cellára teszi
string description = ((DisplayAttribute)(props[i].GetCustomAttributes(typeof(DisplayAttribute), true)?.FirstOrDefault()))?.Description;
if (description != null)
{
if (cell.Comment == null)
{
cell.AddComment(description, "ExcelReportGenerator");
}
}
}
startRow += 1;
}
//Set numberformat for the table
for (int i = 0; i < props.Length; i++)
{
//If column's property has DisplayFormatAttribute set the table's columns format to that
string format = ((DisplayFormatAttribute)(props[i].GetCustomAttributes(typeof(DisplayFormatAttribute), true)?.FirstOrDefault()))?.DataFormatString;
if (format != null)
{
ws.Cells[tableRange.Start.Row, tableRange.Start.Column + i, tableRange.End.Row, tableRange.Start.Column + i].Style.Numberformat.Format = format;
}
//Ha a property dátum, akkor dátum formátumot kap
if (props[i].PropertyType == typeof(DateTime))
{
ws.Cells[tableRange.Start.Row, tableRange.Start.Column + i, tableRange.End.Row, tableRange.Start.Column + i].Style.Numberformat.Format = "yyyy.mm.dd.";
}
}
//Insert data
for (int i = 0; i < dataArray.Length; i++)
{
for (int j = 0; j < props.Length; j++)
{
Object obj = props[j].GetValue(dataArray[i]);
ws.Cells[startRow + i, x + j].Value = obj;
}
}
//Set columns to autofit for better output
tableRange.AutoFitColumns();
}
public void Reset()
{
Style = PatternStyle.NoPattern;
ForegroundColor = ExcelColor.WindowTextForPattern;
BackgroundColor = ExcelColor.WindowBackgroundForPattern;
}
public FontInfo(Font font, ExcelColor color)
{
this.Font = font;
this.Color = color;
}
public static MColor ToMediaColor(ExcelColor color)
{
return(ToMediaColor(ToDrawingColor(color)));
}
public static ReportColor GetColor(this ExcelColor excelColor)
{
return(ToReportColorByRgb(excelColor.Rgb));
}
private void WriteFontRecord(BinaryWriter writer, Font font, ExcelColor color)
{
ushort[] clData = { BIFF.FontRecord, 0, 0, 0, color.Index };
byte[] plainText = Encoding.ASCII.GetBytes(font.FontFamily.Name);
int iLen = plainText.Length;
clData[1] = (ushort)(7 + iLen);
clData[2] = (ushort)(font.SizeInPoints * 20);
int Flags = 0;
if (font.Bold)
Flags |= 1;
if (font.Italic)
Flags |= 2;
if (font.Underline)
Flags |= 4;
if (font.Strikeout)
Flags |= 8;
clData[3] = (ushort)Flags;
WriteUshortArray(writer, clData);
writer.Write((byte)iLen);
writer.Write(plainText);
}
public static DColor ToDrawingColor(ExcelColor color)
{
return((DColor) new ColorConverter().ConvertFromString("#" + color.Rgb));
}
/// <summary>
/// Insert table from key-value pairs
/// </summary>
/// <typeparam name="TKey"></typeparam>
/// <typeparam name="TValue"></typeparam>
/// <param name="ws">Worksheet in wich the table is created</param>
/// <param name="col">Starting column of the table</param>
/// <param name="row">Starting row of the table</param>
/// <param name="data">IEnumerable of key-value pairs in wich the data to insert is stored</param>
/// <param name="title">The table's title</param>
/// <param name="color">The table's color</param>
/// <param name="Name">If given, creates a named range, with this name</param>
/// <returns>Range of the inserted data</returns>
public static ExcelRange InsertDictionary <TKey, TValue>(this ExcelWorksheet ws, int row, int col, IEnumerable <KeyValuePair <TKey, TValue> > data, string title = null, ExcelColor color = ExcelColor.Primary, string Name = null)
{
//Get table's range
//If we show a header the height of the table is 1 bigger
int tableLength = 2;
int tableHeight = data.Count();
var tableRange = ws.Cells[row, col, row + tableHeight - 1, col + tableLength - 1];
//Format table
//Get the starting row for inserting data
int startRow = tableRange.FormatAsTableWithTitle(title, color, false, false);
//Write data
int i = 0;
foreach (var value in data)
{
ws.Cells[startRow + i, col].Value = value.Key;
ws.Cells[startRow + i, col + 1].Value = value.Value;
i++;
}
//Set columns to autofit for better output
tableRange = ws.Cells[startRow, col, startRow + tableHeight - 1, col + tableLength - 1];
//tableRange.AutoFitColumns();//ehhez gdi+ kell
//create NamedRange
if (Name != null)
{
ws.Names.Add(Name, tableRange);
}
return(tableRange);
}
/// <summary>
/// Insert table filled with data from a 2D array
/// </summary>
/// <param name="ws">Worksheet in wich the table is created</param>
/// <param name="col">Starting column of the table</param>
/// <param name="row">Starting row of the table</param>
/// <param name="data">2D array in wich the data to insert is stored (double values)</param>
/// <param name="title">The table's title</param>
/// <param name="color">The table's color</param>
/// <param name="hasRowHeader">Defines if the table has row header</param>
/// <param name="hasColumnHeader">Defines if the table has column header</param>
/// /// <param name="name">If given, creates a named range, with this name</param>
/// <returns>Range of the inserted data</returns>
public static ExcelRange InsertTable(this ExcelWorksheet ws, int row, int col, double[,] data, string title = null, ExcelColor color = ExcelColor.Primary, bool hasRowHeader = true, bool hasColumnHeader = true, string name = null)
{
//Get table's range
int tableLength = data.GetLength(0);
int tableHeight = data.GetLength(1);
var tableRange = ws.Cells[row, col, row + tableHeight - 1, col + tableLength - 1];
//Format table
//Get the starting row for inserting data
int startRow = tableRange.FormatAsTableWithTitle(title, color, hasColumnHeader, hasRowHeader);
//Fill table with data
for (int i = 0; i < tableHeight; i++)
{
for (int j = 0; j < tableLength; j++)
{
ws.Cells[startRow + i, col + j].Value = data[j, i];
}
}
//Set columns to autofit for better output
tableRange = ws.Cells[startRow, col, startRow + tableHeight - 1, col + tableLength - 1];
//tableRange.AutoFitColumns();//ehhez gdi+ kell
//create NamedRange
if (name != null)
{
ws.Names.Add(name, tableRange);
}
return(tableRange);
}
/// <summary>
/// Format cells in the range into a table with possible title
/// </summary>
/// <param name="range">The table's cells</param>
/// <param name="title">The table's title, if null, the table won't have title</param>
/// <param name="color">Color palette of the table</param>
/// <param name="showColumnHeader">Defines if the table has column header</param>
/// <param name="showRowHeader">Defines if the table has row header</param>
/// <returns></returns>
public static int FormatAsTableWithTitle(this ExcelRange range, string title, ExcelColor color = ExcelColor.Primary, bool showColumnHeader = true, bool showRowHeader = true)
{
var ws = range.Worksheet;
var palette = PaletteStorage.GetPalette(color);
ExcelRange tableRange = range;
var nextRow = range.Start.Row;
if (title != null)
{
//Get the title's cells and format title
var titleCells = ws.Cells[range.Start.Row, range.Start.Column, range.Start.Row, range.End.Column];
titleCells.Fill(palette.DarkColor);
titleCells.Merge();
titleCells.BorderAround();
titleCells.Value = title;
//if there's title then the range should be changed after (pushed down by one row)
tableRange = ws.Cells[range.Start.Row + 1, range.Start.Column, range.End.Row + 1, range.End.Column];
nextRow++;
}
//Format table under title
tableRange.FormatAsTable(color, showColumnHeader, showRowHeader);
return(nextRow);
}
/// <summary>
/// Get palette defined by ExcelColor
/// </summary>
/// <param name="excelColor">Main color of the desired palette</param>
/// <returns>Defined palette</returns>
public static Palette GetPalette(ExcelColor excelColor)
{
return(PaletteColors[excelColor]);
}
/// <summary>
/// Insert legend
/// </summary>
/// <param name="range">Range of the legend</param>
/// <param name="text">Text of the legend</param>
/// <param name="title">Title of the legend (can be null)</param>
/// <param name="color">Color of the legend</param>
public static void InsertLegend(this ExcelRange range, string text, string title = null, ExcelColor color = ExcelColor.Info)
{
var ws = range.Worksheet;
var palette = PaletteStorage.GetPalette(color);
//Set legends text range
var legendRange = range;
//If legend has title, format it
if (title != null)
{
var titleCells = ws.Cells[range.Start.Row, range.Start.Column, range.Start.Row, range.End.Column];
titleCells.Merge();
titleCells.Value = title;
titleCells.BorderAround();
titleCells.Fill(palette.MainColor);
//Alter text's range
legendRange = ws.Cells[range.Start.Row + 1, range.Start.Column, range.End.Row, range.End.Column];
}
//Format legends text
legendRange.Merge();
legendRange.Value = text;
legendRange.BorderAround();
legendRange.Fill(palette.LightColor);
legendRange.Style.WrapText = true;
legendRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
legendRange.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelCellStyleBorder"/> class.
/// </summary>
/// <param name="border">The border (top, bottom, etc).</param>
/// <param name="style">The style of the border.</param>
/// <param name="color">The color of the border.</param>
public ExcelCellStyleBorder(ExcelCellBorderEnum border, ExcelCellStyleBorderSizeEnum style = ExcelCellStyleBorderSizeEnum.Thin, ExcelColor color = null)
{
this.Border = border;
this.Style = style;
this.Color = color;
}
/// <summary>
/// Insert a hierarchical list in tree style into the worksheet
/// </summary>
/// <param name="ws">Worksheet in wich the list is inserted</param>
/// <param name="x">Starting row of the list</param>
/// <param name="y">Starting column of the list</param>
/// <param name="root">Root element of the list</param>
/// <param name="title">Title of the list</param>
/// <param name="color">color of the list</param>
public static void InsertHierarchicalList(this ExcelWorksheet ws, int x, int y, HierarchyElement root, string title = null, ExcelColor color = ExcelColor.Primary)
{
//Get hierarchy debth and the number of items in it
int depth = root.GetDepth();
int length = root.GetCount();
//Get table range
ExcelRange range = ws.Cells[y, x, y + length - 1, x + depth - 1];
//Format
var palette = PaletteStorage.GetPalette(color);
var startRow = y;
if (title != null)
{
//Get the title's cells and format title
var titleCells = ws.Cells[range.Start.Row, range.Start.Column, range.Start.Row, range.End.Column];
titleCells.Fill(palette.MainColor);
titleCells.Merge();
titleCells.BorderAround();
titleCells.Value = title;
//if there's title then the range should be changed after (pushed down by one row)
range = ws.Cells[range.Start.Row + 1, range.Start.Column, range.End.Row + 1, range.End.Column];
startRow++;
}
//Format table under title
range.BorderAround();
range.Fill(palette.LightColor);
//Insert data into formatted cells
ws.PrintHierarchicalList(x, startRow, root);
//Set columns to a low width
for (int i = x; i < (x + depth - 1); i++)
{
ws.Column(i).Width = 2;
}
//set last column to autosize
ws.Column(x + depth - 1).AutoFit();
}
/// <summary>
/// Insert table from key-value pairs
/// </summary>
/// <typeparam name="TKey"></typeparam>
/// <typeparam name="TValue"></typeparam>
/// <param name="ws">Worksheet in wich the table is created</param>
/// <param name="x">Starting row of the table</param>
/// <param name="y">Starting column of the table</param>
/// <param name="data">IEnumerable of key-value pairs in wich the data to insert is stored</param>
/// <param name="title">The table's title</param>
/// <param name="color">The table's color</param>
/// <param name="showHeader">Defines if the table has header</param>
public static void InsertTable <TKey, TValue>(this ExcelWorksheet ws, int x, int y, IEnumerable <KeyValuePair <TKey, TValue> > data, string title = null, ExcelColor color = ExcelColor.Primary)
{
//Get table's range
//If we show a header the height of the table is 1 bigger
int tableLength = 2;
int tableHeight = data.Count();
var tableRange = ws.Cells[y, x, y + tableHeight - 1, x + tableLength - 1];
//Format table
//Get the starting row for inserting data
int startRow = tableRange.FormatAsTableWithTitle(title, color, false, false);
//Write data
int i = 0;
foreach (var value in data)
{
ws.Cells[startRow + i, x].Value = value.Key;
ws.Cells[startRow + i, x + 1].Value = value.Value;
i++;
}
//Set columns to autofit for better output
tableRange = ws.Cells[startRow, x, startRow + tableHeight - 1, x + tableLength - 1];
tableRange.AutoFitColumns();
}
/// <summary>
/// Insert table filled with data from a 2D array
/// </summary>
/// <param name="ws">Worksheet in wich the table is created</param>
/// <param name="x">Starting row of the table</param>
/// <param name="y">Starting column of the table</param>
/// <param name="data">2D array in wich the data to insert is stored (double values)</param>
/// <param name="title">The table's title</param>
/// <param name="color">The table's color</param>
/// <param name="hasRowHeader">Defines if the table has row header</param>
/// <param name="hasColumnHeader">Defines if the table has column header</param>
public static void InsertTable(this ExcelWorksheet ws, int x, int y, double[,] data, string title = null, ExcelColor color = ExcelColor.Primary, bool hasRowHeader = true, bool hasColumnHeader = true)
{
//Get table's range
int tableLength = data.GetLength(1);
int tableHeight = data.GetLength(0);
var tableRange = ws.Cells[y, x, y + tableHeight - 1, x + tableLength - 1];
//Format table
//Get the starting row for inserting data
int startRow = tableRange.FormatAsTableWithTitle(title, color, hasColumnHeader, hasRowHeader);
//Fill table with data
for (int i = 0; i < tableHeight; i++)
{
for (int j = 0; j < tableLength; j++)
{
ws.Cells[startRow + i, x + j].Value = data[i, j];
}
}
//Set columns to autofit for better output
tableRange = ws.Cells[startRow, x, startRow + tableHeight - 1, x + tableLength - 1];
tableRange.AutoFitColumns();
}
