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);
}
}
}
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);
}
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);
}
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;
}