public void PopulateExcelWorkbook(List <Order> data)
{
Worksheet currentWorksheet = this.excelWorkbook.Worksheets.Add("WorkSheet1");
foreach (var cell in currentWorksheet.GetRegion("A1:D1"))
{
cell.CellFormat.Fill = CellFill.CreateSolidFill(Color.Gray);
cell.CellFormat.Font.ColorInfo = new WorkbookColorInfo(Color.White);
}
currentWorksheet.Rows[0].Cells[0].Value = "Order ID";
currentWorksheet.Rows[0].Cells[1].Value = "Contact Name";
currentWorksheet.Rows[0].Cells[2].Value = "Shipping Address";
currentWorksheet.Rows[0].Cells[3].Value = "Order Date";
currentWorksheet.Columns[0].Width = 3000;
currentWorksheet.Columns[0].CellFormat.Alignment = HorizontalCellAlignment.Left;
currentWorksheet.Columns[1].Width = 7100;
currentWorksheet.Columns[2].Width = 3000;
currentWorksheet.Columns[2].CellFormat.Alignment = HorizontalCellAlignment.Left;
currentWorksheet.Columns[3].Width = 6100;
int i = 1;
foreach (Order order in data)
{
currentWorksheet.Rows[i].Cells[0].Value = order.OrderID;
currentWorksheet.Rows[i].Cells[1].Value = order.ContactName;
currentWorksheet.Rows[i].Cells[2].Value = order.ShipAddress;
currentWorksheet.Rows[i].Cells[3].Value = order.OrderDate != null?string.Format("{0:d}", order.OrderDate) : "";
i++;
}
}
public override void Execute(object parameter)
{
if (parameter == null)
{
return;
}
var color = (Color)parameter;
if (color != null)
{
if (color.A == 0 && color != Colors.Transparent)
{
return;
}
if (color == Colors.Transparent)
{
this.Adapter.SpreadSheet.ActiveSelectionCellRangeFormat.Fill = CellFill.NoColor;
}
else
{
this.Adapter.SpreadSheet.ActiveSelectionCellRangeFormat.Fill = CellFill.CreateSolidFill(color);
}
}
}
public void Fills()
{
var whiteFill = CellFill.Solid(new Color("#fff")); //white fill
var blackFill = new CellFill { Pattern = FillPattern.Solid, Foreground = Color.Black }; //make sure you set the foreground color
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet("Chessboard");
var cells = sheet[1, 1, 8, 8];
cells.SetOutsideBorder(new BorderEdge { Color = Color.Black, Style = BorderStyle.Medium });
for (var i = 0; i < cells.Range.Width; i++)
for (var j = 0; j < cells.Range.Height; j++)
cells[j, i].Style.Fill = (i + j) % 2 == 0 ? whiteFill : blackFill;
var rows = cells.GetColumn(-1);
for (var i = 0; i < rows.Range.Height; i++)
{
rows[i].Value = 8 - i;
rows[i].Style.Alignment.Horizontal = HorizontalAlignment.Center;
rows[i].Style.Alignment.Vertical = VerticalAlignment.Center;
}
var columns = cells.GetRow(8);
for (var i = 0; i < columns.Range.Width; i++)
{
columns[i].Value = new string(new char[] { (Char)('A' + i) });
columns[i].Style.Alignment.Horizontal = HorizontalAlignment.Center;
columns[i].Style.Alignment.Vertical = VerticalAlignment.Center;
}
sheet.DefaultRowHeight = 40; //px
wb.Save("Fills.xlsx");
}
CT_Fill ConvertFill(CellFill fill)
{
return(new CT_Fill
{
Item = new CT_PatternFill
{
patternType = (ST_PatternType)fill.Pattern,
patternTypeSpecified = true,
fgColor = ConvertColor(fill.Foreground),
bgColor = ConvertColor(fill.Background)
}
});
}
public static void Fills(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("Fills");
worksheet.Columns[0].WidthCharacters = 20;
worksheet["A1"].Value = "Solid Blue";
worksheet["B1"].Fill = CellFill.CreateSolidFill(SpreadsheetColor.Blue);
worksheet["A3"].Value = "Green Red Dark Trellis";
worksheet["B3"].Fill = CellFill.CreatePattern(SpreadsheetColor.Green, SpreadsheetColor.Red, PatternType.DarkTrellis);
worksheet["A5"].Value = "Linear Gradient";
worksheet["B5"].Fill = CellFill.CreateGradient(GradientType.Linear, 180, SpreadsheetColor.Blue, SpreadsheetColor.Red);
}
internal static void CreateCellValueConditionalFormatting(Worksheet worksheet, string range, SpreadsheetColor color, ConditionalFormattingOperator conditionalFormattingOperator, params double[] values)
{
var formatting = worksheet.ConditionalFormattings.Add(range);
var rule = new CellIsFormattingRule();
rule.Fill = CellFill.CreateSolidFill(color);
rule.Operator = conditionalFormattingOperator;
rule.Formula1 = values[0].ToString(CultureInfo.InvariantCulture);
if (values.Length == 2)
{
rule.Formula2 = values[1].ToString(CultureInfo.InvariantCulture);
}
formatting.Rules.Add(rule);
}
private void CreateRowStyles()
{
cfpTitle = CellFill.CreateSolidFill(Colors.LightGray);
// create the fill pattern for the table header
cfpColHeader = CellFill.CreateSolidFill(Colors.AliceBlue);
// create the fill pattern for the table rows
// create the fill pattern for the table alternate rows
cfpGrandTotalRow = CellFillPattern.CreateSolidFill(Colors.LightGray);
cfpVoidRow = CellFillPattern.CreateSolidFill(Colors.Red);
wb = new Workbook();
ws = wb.Worksheets.Add("Crown");
ws.DisplayOptions.PanesAreFrozen = true;
ws.DisplayOptions.FrozenPaneSettings.FrozenRows = 3;
}
static public PatternFill GetFill(this CellFill cellFillAttribute)
{
PatternFill retFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
if (!string.IsNullOrEmpty(cellFillAttribute.HexColor))
{
retFill.ForegroundColor = new ForegroundColor()
{
Rgb = HexBinaryValue.FromString(cellFillAttribute.HexColor)
};
}
return(retFill);
}
private CellFill ConvertDxfCellFill(CT_Fill ct_fill)
{
CT_PatternFill pf;
var cellFill = new CellFill();
if (Util.TryCast(ct_fill.Item, out pf))
{
cellFill.Background = ConvertColor(pf.bgColor);
cellFill.Foreground = ConvertColor(pf.fgColor);
if (pf.patternType == ST_PatternType.none)
{
pf.patternType = ST_PatternType.solid;
}
cellFill.Pattern = (FillPattern)pf.patternType;
}
return(cellFill);
}
public void Fills()
{
var whiteFill = CellFill.Solid(new Color("#fff")); //white fill
var blackFill = new CellFill {
Pattern = FillPattern.Solid, Foreground = Color.Black
}; //make sure you set the foreground color
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet("Chessboard");
var cells = sheet[1, 1, 8, 8];
cells.SetOutsideBorder(new BorderEdge {
Color = Color.Black, Style = BorderStyle.Medium
});
for (var i = 0; i < cells.Range.Width; i++)
{
for (var j = 0; j < cells.Range.Height; j++)
{
cells[j, i].Style.Fill = (i + j) % 2 == 0 ? whiteFill : blackFill;
}
}
var rows = cells.GetColumn(-1);
for (var i = 0; i < rows.Range.Height; i++)
{
rows[i].Value = 8 - i;
rows[i].Style.Alignment.Horizontal = HorizontalAlignment.Center;
rows[i].Style.Alignment.Vertical = VerticalAlignment.Center;
}
var columns = cells.GetRow(8);
for (var i = 0; i < columns.Range.Width; i++)
{
columns[i].Value = new string(new char[] { (Char)('A' + i) });
columns[i].Style.Alignment.Horizontal = HorizontalAlignment.Center;
columns[i].Style.Alignment.Vertical = VerticalAlignment.Center;
}
sheet.DefaultRowHeight = 40; //px
wb.Save("Fills.xlsx");
}
public static void Run()
{
var workbook = Workbook.Load(@"C:\Work\Test.xlsx");
var sheet = workbook.Sheets[0];
sheet["A1"].Value = "Hello";
sheet["A1"].Style.Fill = new CellFill
{
Foreground = new Color(255, 255, 0, 0),
Pattern = FillPattern.Solid
};
sheet[0, 1].Value = "World";
sheet["A3"].Value = "This is a merged cell with border.";
sheet["A3"].Style.Alignment.WrapText = true;
sheet["A3", "B5"].Merge();
sheet["A3", "B5"].SetOutsideBorder(new BorderEdge
{
Style = BorderStyle.Thick,
Color = Color.Black
});
sheet["A6"].Value = "Formatted:";
var b6 = sheet["B6"];
b6.Value = 5;
b6.Style.Format = "#,#.00";
for (var c = 0; c < 5; c++)
{
var bgColor = new Color((byte)(100 + c * 30), 255, (byte)(100 + c * 30));
for (var r = 0; r < 50; r++)
{
sheet[r, c].Style.Fill = CellFill.Solid(bgColor);
}
}
workbook.Save(@"C:\Work\Xlio.xlsx", XlsxFileWriterOptions.AutoFit);
}
public static void CreateConditionalFormatting()
{
var workbook = new Workbook(); //creating workbook and adding sheet
var companiesSheet = new Sheet("CompaniesByRevenue");
workbook.Sheets.AddSheet(companiesSheet);
companiesSheet.Columns[0].Width = 5;
companiesSheet.Columns[1].Width = 30;
companiesSheet.Columns[2].Width = 25;
companiesSheet.Columns[3].Width = 25;
companiesSheet.Columns[4].Width = 15;
var headingStyle = new CellStyle(); //define bold and centered header style
headingStyle.Font.Bold = true;
headingStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var thousandsFormatStyle = new CellStyle(); //adding thousands separator format on Employees column
thousandsFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
thousandsFormatStyle.Format = "#,#0";
var currencyFormatStyle = new CellStyle(); //adding $ (dollar) sign on Revenue and Capitalization columns
currencyFormatStyle.Format = "$#,#0";
currencyFormatStyle.Alignment.Horizontal = HorizontalAlignment.Center;
var table = companiesSheet["A2", "E6"];
table.GetRow(-1).SetValues("Rank", "Company", "Revenue (USD billions)", "Capitalization (USD billions)", "Employees").SetStyle(headingStyle);
table.GetColumn(0).SetValues(1, 2, 3, 4, 5);
table.GetColumn(1).SetValues("Wal-Mart Stores, Inc. (USA)", "Royal Dutch Shell (NLD)", "Exxon Mobil Corporation (USA)", "China National Petroleum (CHN)", "Sinopec Group (CHN)");
table.GetColumn(2).SetValues(469, 467, 453, 425, 411).SetStyle(currencyFormatStyle);
table.GetColumn(3).SetValues(248, 132, 406, null, 81).SetStyle(currencyFormatStyle);
table.GetColumn(4).SetValues(2200000, 90000, 76900, 1668072, 401000).SetStyle(thousandsFormatStyle);
var originFormating = new ConditionalFormatting(Range.Parse("B2:B6")); //creating ConditionalFormatting for specified range
originFormating.AddContainsText("USA", new CellStyle {
Fill = CellFill.BackColor(Color.Red)
});
originFormating.AddEndsWithText("(NLD)", new CellStyle {
Fill = CellFill.BackColor(new Color("#0096FF"))
});
originFormating.AddContainsText("CHN", new CellStyle {
Fill = CellFill.BackColor(new Color("#FFFF96"))
});
var revenueFormatting = new ConditionalFormatting();
revenueFormatting.AddRange("C2:C6"); //multiple ranges or cells can be added
revenueFormatting.AddDefaultIconSet(IconSetType.Item5Arrows); //add IconSet with 5 arrows
revenueFormatting.AddGreaterThan("460", new CellStyle {
Font = new CellFont {
Underline = FontUnderline.Single
}
});
var capitalizationFormatting = new ConditionalFormatting(Range.Parse("D1:D6")); //specifying range in constructor
capitalizationFormatting.AddColorScale(Color.Yellow, Color.Orange);
capitalizationFormatting.AddDefaultIconSet();
capitalizationFormatting.AddIsBlank(new CellStyle {
Fill = CellFill.BackColor(Color.Red)
}); //add style applied on blank cell
ConditionalFormatting employeesNumberFormating = new ConditionalFormatting(Range.Parse("E1:E6"));
employeesNumberFormating.AddDefaultDataBar(Color.Blue); //adding blue data bar conditional formatting rule
//adding different conditional formattings to sheet
companiesSheet.ConditionalFormatting.Add(originFormating);
companiesSheet.ConditionalFormatting.Add(revenueFormatting);
companiesSheet.ConditionalFormatting.Add(capitalizationFormatting);
companiesSheet.ConditionalFormatting.Add(employeesNumberFormating);
workbook.Save(@"ConditionalFormatting.xlsx");
}
private void FillAttemptHistorySheet(Sheet sheet, IRun run)
{
var attemptIdColumn = 0;
var startedColumn = 1;
var endedColumn = 2;
var timeColumn = 3;
var header = sheet.Data.Rows[0];
header[attemptIdColumn].Value = "Attempt ID";
header[attemptIdColumn].Style.Font.Bold = true;
header[attemptIdColumn].Style.Font.Color = Color.White;
header[attemptIdColumn].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[attemptIdColumn].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[attemptIdColumn].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
header[startedColumn].Value = "Started";
header[startedColumn].Style.Font.Bold = true;
header[startedColumn].Style.Font.Color = Color.White;
header[startedColumn].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[startedColumn].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[startedColumn].Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
header[startedColumn].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
header[endedColumn].Value = "Ended";
header[endedColumn].Style.Font.Bold = true;
header[endedColumn].Style.Font.Color = Color.White;
header[endedColumn].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[endedColumn].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[endedColumn].Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
header[endedColumn].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
header[timeColumn].Value = "Time";
header[timeColumn].Style.Font.Bold = true;
header[timeColumn].Style.Font.Color = Color.White;
header[timeColumn].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[timeColumn].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[timeColumn].Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
header[timeColumn].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
var rowIndex = 1;
var bestTime = TimeSpan.MaxValue;
foreach (var attempt in run.AttemptHistory)
{
var row = sheet.Data.Rows[rowIndex];
row[attemptIdColumn].Value = attempt.Index;
row[attemptIdColumn].Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
var startedCell = row[startedColumn];
startedCell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
startedCell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
startedCell.Style.Format = "dd mmm yy hh:mm:ss";
startedCell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
if (attempt.Started.HasValue)
{
startedCell.Value = attempt.Started.Value;
}
var endedCell = row[endedColumn];
endedCell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
endedCell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
endedCell.Style.Format = "dd mmm yy hh:mm:ss";
endedCell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
if (attempt.Ended.HasValue)
{
endedCell.Value = attempt.Ended.Value;
}
var timeCell = row[timeColumn];
timeCell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
var time = attempt.Time.RealTime;
if (time.HasValue)
{
timeCell.Value = time.Value.TotalDays;
if (time.Value < bestTime)
{
bestTime = time.Value;
timeCell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(201, 231, 201)
: new Color(218, 248, 218));
}
}
timeCell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
timeCell.Style.Format = "[HH]:MM:SS.00";
timeCell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
++rowIndex;
}
sheet.AutoFilter = sheet[0, 0, rowIndex - 1, 3];
}
private static void FillSplitTimesSheet(Sheet sheet, IRun run)
{
var header = sheet.Data.Rows[0];
header[0].Value = "Segment";
header[0].Style.Font.Bold = true;
header[0].Style.Font.Color = Color.White;
header[0].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[0].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[0].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
var columnIndex = 1;
foreach (var comparisonName in run.Comparisons.Where(x => x != NoneComparisonGenerator.ComparisonName))
{
var cell = header[columnIndex];
cell.Value = comparisonName;
cell.Style.Font.Bold = true;
cell.Style.Font.Color = Color.White;
cell.Style.Alignment.Horizontal = HorizontalAlignment.Center;
cell.Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
cell.Style.Fill = CellFill.Solid(new Color(128, 128, 128));
columnIndex++;
}
var lastTime = TimeSpan.Zero;
var rowIndex = 1;
foreach (var segment in run)
{
var row = sheet.Data.Rows[rowIndex];
row[0].Value = segment.Name;
row[0].Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
columnIndex = 1;
foreach (var comparisonName in run.Comparisons.Where(x => x != NoneComparisonGenerator.ComparisonName))
{
var cell = row[columnIndex];
var time = segment.Comparisons[comparisonName].RealTime;
if (time.HasValue)
{
cell.Value = time.Value.TotalDays;
}
cell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
cell.Style.Format = "[HH]:MM:SS.00";
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
if (comparisonName == Run.PersonalBestComparisonName && time.HasValue && segment.BestSegmentTime.RealTime == (time.Value - lastTime))
{
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(241, 231, 181)
: new Color(255, 245, 198));
}
else
{
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
}
if (comparisonName == Run.PersonalBestComparisonName && time.HasValue)
{
lastTime = time.Value;
}
columnIndex++;
}
++rowIndex;
}
sheet.AutoFilter = sheet[0, 0, rowIndex - 1, columnIndex - 1];
}
private static void FillSegmentHistorySheet(Sheet sheet, IRun run)
{
var header = sheet.Data.Rows[0];
header[0].Value = "Attempt ID";
header[0].Style.Font.Bold = true;
header[0].Style.Font.Color = Color.White;
header[0].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[0].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[0].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
var columnIndex = 1;
foreach (var segment in run)
{
var cell = header[columnIndex];
cell.Value = segment.Name;
cell.Style.Font.Bold = true;
cell.Style.Font.Color = Color.White;
cell.Style.Alignment.Horizontal = HorizontalAlignment.Center;
cell.Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
cell.Style.Fill = CellFill.Solid(new Color(128, 128, 128));
columnIndex++;
}
var rowIndex = 1;
foreach (var attempt in run.AttemptHistory)
{
var row = sheet.Data.Rows[rowIndex];
row[0].Value = attempt.Index;
row[0].Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
columnIndex = 1;
foreach (var segment in run)
{
var cell = row[columnIndex];
var segmentHistoryElement = segment.SegmentHistory.FirstOrDefault(x => x.Index == attempt.Index);
if (segmentHistoryElement != null)
{
var time = segmentHistoryElement.Time.RealTime;
if (time.HasValue)
{
cell.Value = time.Value.TotalDays;
}
}
cell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
cell.Style.Format = "[HH]:MM:SS.00";
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
columnIndex++;
}
++rowIndex;
}
sheet.AutoFilter = sheet[0, 0, rowIndex - 1, columnIndex - 1];
}
private void LoadStyles(string path)
{
var styles = ReadFile <CT_Stylesheet>(path);
indexedColors = new List <Color>();
if (styles.colors != null && styles.colors.indexedColors != null)
{
foreach (var color in styles.colors.indexedColors)
{
indexedColors.Add(new Color(color.rgb));
}
}
borders = new List <CellBorder>();
if (styles.borders != null && styles.borders.border != null)
{
foreach (var border in styles.borders.border)
{
CellBorder b = new CellBorder
{
Bottom = ConvertBorderEdge(border.bottom),
Right = ConvertBorderEdge(border.right),
Left = ConvertBorderEdge(border.left),
Top = ConvertBorderEdge(border.top),
Diagonal = ConvertBorderEdge(border.diagonal),
DiagonalDown = border.diagonalDown,
DiagonalUp = border.diagonalUp,
Vertical = ConvertBorderEdge(border.vertical),
Horizontal = ConvertBorderEdge(border.horizontal),
Outline = border.outline
};
borders.Add(b);
}
}
fills = new List <CellFill>();
if (styles.fills != null && styles.fills.fill != null)
{
foreach (var fill in styles.fills.fill)
{
CT_PatternFill pf;
CellFill f = new CellFill();
if (Util.TryCast(fill.Item, out pf))
{
f.Background = ConvertColor(pf.bgColor);
f.Foreground = ConvertColor(pf.fgColor);
f.Pattern = (FillPattern)pf.patternType;
}
fills.Add(f);
}
}
fonts = new List <CellFont>();
if (styles.fonts != null && styles.fonts.font != null)
{
foreach (var font in styles.fonts.font)
{
CellFont f = null;
if (font.ItemsElementName != null && font.Items != null)
{
f = new CellFont();
for (int i = 0; i < font.ItemsElementName.Length; i++)
{
var item = font.Items[i];
CT_BooleanProperty bp;
switch (font.ItemsElementName[i])
{
case ItemsChoiceType3.name:
CT_FontName name;
if (Util.TryCast(item, out name))
{
f.Name = name.val;
}
break;
case ItemsChoiceType3.sz:
CT_FontSize size;
if (Util.TryCast(item, out size))
{
f.Size = size.val;
}
break;
case ItemsChoiceType3.b:
if (Util.TryCast(item, out bp))
{
f.Bold = bp.val;
}
break;
case ItemsChoiceType3.strike:
if (Util.TryCast(item, out bp))
{
f.Strike = bp.val;
}
break;
case ItemsChoiceType3.shadow:
if (Util.TryCast(item, out bp))
{
f.Shadow = bp.val;
}
break;
case ItemsChoiceType3.outline:
if (Util.TryCast(item, out bp))
{
f.Outline = bp.val;
}
break;
case ItemsChoiceType3.i:
if (Util.TryCast(item, out bp))
{
f.Italic = bp.val;
}
break;
case ItemsChoiceType3.u:
CT_UnderlineProperty up;
if (Util.TryCast(item, out up))
{
f.Underline = (FontUnderline)up.val;
}
break;
case ItemsChoiceType3.vertAlign:
CT_VerticalAlignFontProperty vafp;
if (Util.TryCast(item, out vafp))
{
f.Script = (FontScript)vafp.val;
}
break;
case ItemsChoiceType3.color:
CT_Color1 color;
if (Util.TryCast(item, out color))
{
f.Color = ConvertColor(color);
}
break;
}
}
}
fonts.Add(f);
}
}
if (fonts.Count > 0)
{
workbook.DefaultFont = fonts[0];
}
numFmts = new Dictionary <uint, string>();
if (styles.numFmts != null && styles.numFmts.numFmt != null)
{
foreach (var nfmt in styles.numFmts.numFmt)
{
numFmts[nfmt.numFmtId] = nfmt.formatCode;
}
}
xfs = new List <CellStyle>();
if (styles.cellXfs != null && styles.cellXfs.xf != null)
{
foreach (var xf in styles.cellXfs.xf)
{
var entry = new CellStyle();
if (xf.applyBorderSpecified && xf.applyBorder && xf.borderIdSpecified)
{
entry.Border = GetBorder((int)xf.borderId);
}
if (xf.applyFillSpecified && xf.applyFill && xf.fillIdSpecified)
{
entry.Fill = GetFill((int)xf.fillId);
}
if (xf.applyFontSpecified && xf.applyFont && xf.fontIdSpecified)
{
entry.Font = GetFont((int)xf.fontId);
}
if (xf.applyNumberFormatSpecified && xf.applyNumberFormat && xf.numFmtIdSpecified)
{
entry.Format = GetNumFmt(xf.numFmtId);
}
if (xf.applyAlignmentSpecified && xf.applyAlignment && xf.alignment != null)
{
entry.Alignment = ConvertAlignment(xf.alignment);
}
xfs.Add(entry);
}
}
dxfs = new List <CellStyle>();
if (styles.dxfs != null && styles.dxfs.dxf != null)
{
foreach (CT_Dxf dxf in styles.dxfs.dxf)
{
var entry = new CellStyle();
if (dxf.font != null)
{
entry.Font = ConvertDxfFont(dxf.font);
}
if (dxf.numFmt != null)
{
entry.format = GetNumFmt(dxf.numFmt.numFmtId);
}
if (dxf.fill != null)
{
entry.fill = ConvertDxfCellFill(dxf.fill);
}
if (dxf.alignment != null)
{
entry.alignment = ConvertAlignment(dxf.alignment);
}
if (dxf.border != null)
{
entry.border = ConvertDxfBorder(dxf.border);
}
dxfs.Add(entry);
}
}
}
private void FillRunHistorySheet(Sheet sheet, IRun run)
{
var header = sheet.Data.Rows[0];
header[0].Value = "Run ID";
header[0].Style.Font.Bold = true;
header[0].Style.Font.Color = Color.White;
header[0].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[0].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[0].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
header[1].Value = "Time";
header[1].Style.Font.Bold = true;
header[1].Style.Font.Color = Color.White;
header[1].Style.Alignment.Horizontal = HorizontalAlignment.Center;
header[1].Style.Border.Bottom = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.White
};
header[1].Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
header[1].Style.Fill = CellFill.Solid(new Color(128, 128, 128));
var rowIndex = 1;
var bestTime = TimeSpan.MaxValue;
foreach (var runHistoryElement in run.RunHistory)
{
var row = sheet.Data.Rows[rowIndex];
row[0].Value = runHistoryElement.Index;
row[0].Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
var cell = row[1];
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(221, 221, 221)
: new Color(238, 238, 238));
var time = runHistoryElement.Time.RealTime;
if (time.HasValue)
{
cell.Value = time.Value.TotalDays;
if (time.Value < bestTime)
{
bestTime = time.Value;
cell.Style.Fill = CellFill.Solid(
((rowIndex & 1) == 1)
? new Color(201, 231, 201)
: new Color(218, 248, 218));
}
}
cell.Style.Alignment.Horizontal = HorizontalAlignment.Right;
cell.Style.Format = "[HH]:MM:SS.00";
cell.Style.Border.Left = new BorderEdge {
Style = BorderStyle.Thin, Color = Color.White
};
++rowIndex;
}
sheet.AutoFilter = sheet[0, 0, rowIndex - 1, 1];
}
private void LoadStyles(string path)
{
var styles = ReadFile<CT_Stylesheet>(path);
indexedColors = new List<Color>();
if (styles.colors != null && styles.colors.indexedColors != null)
foreach (var color in styles.colors.indexedColors)
indexedColors.Add(new Color(color.rgb));
borders = new List<CellBorder>();
if (styles.borders!=null && styles.borders.border!=null)
foreach (var border in styles.borders.border)
{
CellBorder b = new CellBorder
{
Bottom = ConvertBorderEdge(border.bottom),
Right = ConvertBorderEdge(border.right),
Left = ConvertBorderEdge(border.left),
Top = ConvertBorderEdge(border.top),
Diagonal = ConvertBorderEdge(border.diagonal),
DiagonalDown = border.diagonalDown,
DiagonalUp = border.diagonalUp,
Vertical = ConvertBorderEdge(border.vertical),
Horizontal = ConvertBorderEdge(border.horizontal),
Outline = border.outline
};
borders.Add(b);
}
fills = new List<CellFill>();
if (styles.fills != null && styles.fills.fill != null)
foreach (var fill in styles.fills.fill)
{
CT_PatternFill pf;
CellFill f = new CellFill();
if (Util.TryCast(fill.Item, out pf))
{
f.Background = ConvertColor(pf.bgColor);
f.Foreground = ConvertColor(pf.fgColor);
f.Pattern = (FillPattern)pf.patternType;
}
fills.Add(f);
}
fonts = new List<CellFont>();
if (styles.fonts!=null && styles.fonts.font!=null)
foreach (var font in styles.fonts.font)
{
CellFont f = null;
if (font.ItemsElementName != null && font.Items != null)
{
f = new CellFont();
for (int i = 0; i < font.ItemsElementName.Length; i++)
{
var item = font.Items[i];
CT_BooleanProperty bp;
switch (font.ItemsElementName[i])
{
case ItemsChoiceType3.name:
CT_FontName name;
if (Util.TryCast(item, out name))
f.Name = name.val;
break;
case ItemsChoiceType3.sz:
CT_FontSize size;
if (Util.TryCast(item, out size))
f.Size = size.val;
break;
case ItemsChoiceType3.b:
if (Util.TryCast(item, out bp))
f.Bold = bp.val;
break;
case ItemsChoiceType3.strike:
if (Util.TryCast(item, out bp))
f.Strike = bp.val;
break;
case ItemsChoiceType3.shadow:
if (Util.TryCast(item, out bp))
f.Shadow = bp.val;
break;
case ItemsChoiceType3.outline:
if (Util.TryCast(item, out bp))
f.Outline = bp.val;
break;
case ItemsChoiceType3.i:
if (Util.TryCast(item, out bp))
f.Italic = bp.val;
break;
case ItemsChoiceType3.u:
CT_UnderlineProperty up;
if (Util.TryCast(item, out up))
f.Underline = (FontUnderline)up.val;
break;
case ItemsChoiceType3.vertAlign:
CT_VerticalAlignFontProperty vafp;
if (Util.TryCast(item, out vafp))
f.Script = (FontScript)vafp.val;
break;
case ItemsChoiceType3.color:
CT_Color1 color;
if (Util.TryCast(item, out color))
f.Color = ConvertColor(color);
break;
}
}
}
fonts.Add(f);
}
if (fonts.Count > 0)
workbook.DefaultFont = fonts[0];
numFmts = new Dictionary<uint, string>();
if (styles.numFmts!=null && styles.numFmts.numFmt!=null)
foreach (var nfmt in styles.numFmts.numFmt)
{
numFmts[nfmt.numFmtId] = nfmt.formatCode;
}
xfs = new List<CellStyle>();
if (styles.cellXfs != null && styles.cellXfs.xf!=null)
foreach (var xf in styles.cellXfs.xf)
{
var entry = new CellStyle();
if (xf.applyBorderSpecified && xf.applyBorder && xf.borderIdSpecified)
entry.Border = GetBorder((int)xf.borderId);
if (xf.applyFillSpecified && xf.applyFill && xf.fillIdSpecified)
entry.Fill = GetFill((int)xf.fillId);
if (xf.applyFontSpecified && xf.applyFont && xf.fontIdSpecified)
entry.Font = GetFont((int)xf.fontId);
if (xf.applyNumberFormatSpecified && xf.applyNumberFormat && xf.numFmtIdSpecified)
entry.Format = GetNumFmt(xf.numFmtId);
if (xf.applyAlignmentSpecified && xf.applyAlignment && xf.alignment != null)
entry.Alignment = ConvertAlignment(xf.alignment);
xfs.Add(entry);
}
dxfs = new List<CellStyle>();
if (styles.dxfs != null && styles.dxfs.dxf != null)
{
foreach (CT_Dxf dxf in styles.dxfs.dxf)
{
var entry = new CellStyle();
if (dxf.font != null)
entry.Font = ConvertDxfFont(dxf.font);
if (dxf.numFmt != null)
entry.format = GetNumFmt(dxf.numFmt.numFmtId);
if (dxf.fill != null)
entry.fill = ConvertDxfCellFill(dxf.fill);
if (dxf.alignment != null)
entry.alignment = ConvertAlignment(dxf.alignment);
if (dxf.border != null)
entry.border = ConvertDxfBorder(dxf.border);
dxfs.Add(entry);
}
}
}
private CellFill ConvertDxfCellFill(CT_Fill ct_fill)
{
CT_PatternFill pf;
var cellFill = new CellFill();
if (Util.TryCast(ct_fill.Item, out pf))
{
cellFill.Background = ConvertColor(pf.bgColor);
cellFill.Foreground = ConvertColor(pf.fgColor);
if (pf.patternType == ST_PatternType.none)
pf.patternType = ST_PatternType.solid;
cellFill.Pattern = (FillPattern)pf.patternType;
}
return cellFill;
}
public RowData ToRow(uint rowNo)
{
if (rowNo == 0)
{
throw new ArgumentException("rowNo must be greater than 0.");
}
var retRowData = new RowData();
retRowData.Row.RowIndex = rowNo;
var dataRowType = GetType();
var dataCellProperties = dataRowType.GetProperties().Where(prop => prop.IsDefined(typeof(CellDataAttribute), false));
foreach (var dataCell in dataCellProperties)
{
if (dataCell == null)
{
continue;
}
var target = dataCell.GetValue(this);
if (target == null)
{
continue;
}
var dataCellAttr = dataCell.GetCustomAttributes(false).Where(atr => atr is CellDataAttribute).FirstOrDefault() as CellDataAttribute;
if (dataCellAttr == null)
{
continue;
}
var cellColumnName = dataCellAttr.ColumnName;
if (_propertyMappings.ContainsKey(dataCell.Name))
{
cellColumnName = _propertyMappings[dataCell.Name];
}
var cellDataType = ExcelHelper.ResolveCellType(target.GetType());
var cellDataValue = target.ToString();
var cellReference = cellColumnName + rowNo;
var cell = new Cell()
{
CellReference = cellReference
};
cell.CellValue = new CellValue(cellDataValue);
cell.DataType = new EnumValue <CellValues>(cellDataType);
var excelCell = new ExcelCell();
excelCell.Cell = cell;
var cellFillAttr = dataCell.GetCustomAttributes(false).Where(atr => atr is CellFillAttribute).FirstOrDefault() as CellFillAttribute;
var cellBorderAttr = dataCell.GetCustomAttributes(false).Where(atr => atr is CellBorderAttribute).FirstOrDefault() as CellBorderAttribute;
var cellFontAttr = dataCell.GetCustomAttributes(false).Where(atr => atr is CellTextAttribute).FirstOrDefault() as CellTextAttribute;
var cellStyleMappings = _propertyStylMappings.ContainsKey(dataCell.Name) ? _propertyStylMappings[dataCell.Name] : null;
CellFill cellFillMap = null;
CellBorder cellBorderMap = null;
CellText cellFontMap = null;
if (cellStyleMappings != null)
{
cellFillMap = cellStyleMappings.Where(x => x is CellFill).FirstOrDefault() as CellFill;
cellBorderMap = cellStyleMappings.Where(x => x is CellBorder).FirstOrDefault() as CellBorder;
cellFontMap = cellStyleMappings.Where(x => x is CellText).FirstOrDefault() as CellText;
}
if (cellFillMap == null)
{
if (cellFillAttr != null)
{
excelCell.Styles.Add(cellFillAttr.GetFill());
}
}
else
{
excelCell.Styles.Add(cellFillMap.GetFill());
}
if (cellBorderMap == null)
{
if (cellBorderAttr != null)
{
excelCell.Styles.Add(cellBorderAttr.GetBorder());
}
}
else
{
excelCell.Styles.Add(cellBorderMap.GetBorder());
}
if (cellFontMap == null)
{
if (cellFontAttr != null)
{
excelCell.Styles.Add(cellFontAttr.GetFont());
}
}
else
{
excelCell.Styles.Add(cellFontMap.GetFont());
}
retRowData.Cells.Add(excelCell);
}
return(retRowData);
}
