private ITableStyle CreateCustomStyle(IWorkbook workbook)
{
#region Custom table style
string tableStyleName = "Table Style 1";
ITableStyles tableStyles = workbook.TableStyles;
ITableStyle tableStyle = tableStyles.Add(tableStyleName);
ITableStyleElements tableStyleElements = tableStyle.TableStyleElements;
ITableStyleElement tableStyleElement = tableStyleElements.Add(ExcelTableStyleElementType.SecondColumnStripe);
tableStyleElement.BackColorRGB = Color.FromArgb(217, 225, 242);
ITableStyleElement tableStyleElement1 = tableStyleElements.Add(ExcelTableStyleElementType.FirstColumn);
tableStyleElement1.FontColorRGB = Color.FromArgb(128, 128, 128);
ITableStyleElement tableStyleElement2 = tableStyleElements.Add(ExcelTableStyleElementType.HeaderRow);
tableStyleElement2.FontColor = ExcelKnownColors.White;
tableStyleElement2.BackColorRGB = Color.FromArgb(0, 112, 192);
ITableStyleElement tableStyleElement3 = tableStyleElements.Add(ExcelTableStyleElementType.TotalRow);
tableStyleElement3.BackColorRGB = Color.FromArgb(0, 112, 192);
tableStyleElement3.FontColor = ExcelKnownColors.White;
#endregion
return(tableStyle);
}
public ExcelWriter(ITableStyle style, int xOffset, int yOffset, int emptyRowsAfterTitle)
{
Style = style;
XOffset = xOffset;
YOffset = yOffset;
EmptyRowsAfterTitle = emptyRowsAfterTitle;
}
public static bool IsBuiltinStyle(ITableStyle style)
{
if (style == null)
{
return(false);
}
return(Enums.GetNames <XSSFBuiltinTableStyleEnum>().Any(x => x == style.Name));
}
public ITableVisual SetStyle(ITableStyle style)
{
string id = style.Id;
this.GetTableStyleId(true).Text = id;
return(this);
}
private static ITableStyle CreateTableStyle(IPresentation presentation)
{
ITableStyle tableStyle = presentation.TableStyles.AddTableStyle("Custom Style");
tableStyle.WholeTablePart
.Text.SetFont(new OpenXmlFontRef(OpenXmlFontCollectionIndex.Minor, new OpenXmlPresetColor(PresetColorValues.Black)))
.Text.SetFontColor(OpenXmlColor.Dark1)
.Style.Border.Left.SetStrokeWidth(1)
.Style.Border.Left.SetStroke(OpenXmlColor.Light1)
.Style.Border.Right.SetStrokeWidth(1)
.Style.Border.Right.SetStroke(OpenXmlColor.Light1)
.Style.Border.Top.SetStrokeWidth(1)
.Style.Border.Top.SetStroke(OpenXmlColor.Light1)
.Style.Border.Bottom.SetStrokeWidth(1)
.Style.Border.Bottom.SetStroke(OpenXmlColor.Light1)
.Style.Border.InsideHorizontal.SetStrokeWidth(1)
.Style.Border.InsideHorizontal.SetStroke(OpenXmlColor.Light1)
.Style.Border.InsideVertical.SetStrokeWidth(1)
.Style.Border.InsideVertical.SetStroke(OpenXmlColor.Light1)
.Style.Fill.SetFill(OpenXmlColor.Accent1.WithTint(0.2));
tableStyle.Row
.Style.Fill.SetFill(OpenXmlColor.Accent1.WithTint(0.4));
tableStyle.Column
.Style.Fill.SetFill(OpenXmlColor.Accent1.WithTint(0.4));
tableStyle.LastColumn
.Text.SetIsBold(true)
.Text.SetFont(new OpenXmlFontRef(OpenXmlFontCollectionIndex.Minor, new OpenXmlPresetColor(PresetColorValues.Black)))
.Text.SetFontColor(OpenXmlColor.Light1)
.Style.Fill.SetFill(OpenXmlColor.Accent1);
tableStyle.FirstColumn
.Text.SetIsBold(true)
.Text.SetFont(new OpenXmlFontRef(OpenXmlFontCollectionIndex.Minor, new OpenXmlPresetColor(PresetColorValues.Black)))
.Text.SetFontColor(OpenXmlColor.Light1)
.Style.Fill.SetFill(OpenXmlColor.Accent1);
tableStyle.LastRow
.Text.SetIsBold(true)
.Text.SetFont(new OpenXmlFontRef(OpenXmlFontCollectionIndex.Minor, new OpenXmlPresetColor(PresetColorValues.Black)))
.Text.SetFontColor(OpenXmlColor.Light1)
.Style.Border.Top.SetStrokeWidth(3)
.Style.Border.Top.SetStroke(OpenXmlColor.Light1)
.Style.Fill.SetFill(OpenXmlColor.Accent1);
tableStyle.FirstRow
.Text.SetIsBold(true)
.Text.SetFont(new OpenXmlFontRef(OpenXmlFontCollectionIndex.Minor, new OpenXmlPresetColor(PresetColorValues.Black)))
.Text.SetFontColor(OpenXmlColor.Light1)
.Style.Border.Bottom.SetStrokeWidth(3)
.Style.Border.Bottom.SetStroke(OpenXmlColor.Light1)
.Style.Fill.SetFill(OpenXmlColor.Accent1);
return(tableStyle);
}
public Table(ITableStyle style)
{
if (style == null)
{
throw new ArgumentNullException(nameof(style));
}
Style = style;
}
public XSSFTableStyleInfo(StylesTable stylesTable, CT_TableStyleInfo tableStyleInfo)
{
this.columnStripes = tableStyleInfo.showColumnStripes;
this.rowStripes = tableStyleInfo.showRowStripes;
this.firstColumn = tableStyleInfo.showFirstColumn;
this.lastColumn = tableStyleInfo.showLastColumn;
this.style = stylesTable.GetTableStyle(tableStyleInfo.name);
this.stylesTable = stylesTable;
this.styleInfo = tableStyleInfo;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet sheet = workbook.Worksheets[0];
//Single cell border
sheet.Range["B2"].Borders.ThemeColor = ThemeColor.Accent1;
sheet.Range["B2"].Borders.LineStyle = BorderLineStyle.SlantDashDot;
sheet.Range["B2"].Borders[BordersIndex.DiagonalUp].ThemeColor = ThemeColor.Accent1;
sheet.Range["B2"].Borders[BordersIndex.DiagonalUp].LineStyle = BorderLineStyle.SlantDashDot;
sheet.Range["B2"].Borders[BordersIndex.DiagonalDown].ThemeColor = ThemeColor.Accent1;
sheet.Range["B2"].Borders[BordersIndex.DiagonalDown].LineStyle = BorderLineStyle.SlantDashDot;
//Range border
sheet.Range["D2:E3"].Borders.ThemeColor = ThemeColor.Accent1;
sheet.Range["D2:E3"].Borders.LineStyle = BorderLineStyle.DashDot;
sheet.Range["D2:E3"].Borders[BordersIndex.DiagonalDown].ThemeColor = ThemeColor.Accent1;
sheet.Range["D2:E3"].Borders[BordersIndex.DiagonalDown].LineStyle = BorderLineStyle.DashDot;
//Merge cell border
sheet.Range["B6:C7"].Merge();
sheet.Range["B6:C7"].Borders.ThemeColor = ThemeColor.Accent1;
sheet.Range["B6:C7"].Borders.LineStyle = BorderLineStyle.Double;
sheet.Range["B6:C7"].Borders[BordersIndex.DiagonalUp].ThemeColor = ThemeColor.Accent1;
sheet.Range["B6:C7"].Borders[BordersIndex.DiagonalUp].LineStyle = BorderLineStyle.Double;
//Border style on table
ITable table = sheet.Tables.Add(sheet.Range["B12:G22"], true);
//Create custom table style
ITableStyle customTableStyle = workbook.TableStyles["TableStyleMedium10"].Duplicate();
//Set outline border for "whole table" style
var wholeTableStyle = customTableStyle.TableStyleElements[TableStyleElementType.WholeTable];
wholeTableStyle.Borders[BordersIndex.EdgeTop].ThemeColor = ThemeColor.Accent1;
wholeTableStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thick;
wholeTableStyle.Borders[BordersIndex.EdgeRight].ThemeColor = ThemeColor.Accent1;
wholeTableStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thick;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent1;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
wholeTableStyle.Borders[BordersIndex.EdgeLeft].ThemeColor = ThemeColor.Accent1;
wholeTableStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thick;
//Set vertical border for "first row strip" style
var firstRowStripStyle = customTableStyle.TableStyleElements[TableStyleElementType.FirstRowStripe];
firstRowStripStyle.Borders[BordersIndex.InsideVertical].ThemeColor = ThemeColor.Accent6;
firstRowStripStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Dashed;
//Apply custom style to table
table.TableStyle = customTableStyle;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
object[,] sourceData = new object[, ] {
{ "Order ID", "Product", "Category", "Amount", "Date", "Country" },
{ 1, "Carrots", "Vegetables", 4270, new DateTime(2018, 1, 6), "United States" },
{ 2, "Broccoli", "Vegetables", 8239, new DateTime(2018, 1, 7), "United Kingdom" },
{ 3, "Banana", "Fruit", 617, new DateTime(2018, 1, 8), "United States" },
{ 4, "Banana", "Fruit", 8384, new DateTime(2018, 1, 10), "Canada" },
{ 5, "Beans", "Vegetables", 2626, new DateTime(2018, 1, 10), "Germany" },
{ 6, "Orange", "Fruit", 3610, new DateTime(2018, 1, 11), "United States" },
{ 7, "Broccoli", "Vegetables", 9062, new DateTime(2018, 1, 11), "Australia" },
{ 8, "Banana", "Fruit", 6906, new DateTime(2018, 1, 16), "New Zealand" },
{ 9, "Apple", "Fruit", 2417, new DateTime(2018, 1, 16), "France" },
{ 10, "Apple", "Fruit", 7431, new DateTime(2018, 1, 16), "Canada" },
{ 11, "Banana", "Fruit", 8250, new DateTime(2018, 1, 16), "Germany" },
{ 12, "Broccoli", "Vegetables", 7012, new DateTime(2018, 1, 18), "United States" },
{ 13, "Carrots", "Vegetables", 1903, new DateTime(2018, 1, 20), "Germany" },
{ 14, "Broccoli", "Vegetables", 2824, new DateTime(2018, 1, 22), "Canada" },
{ 15, "Apple", "Fruit", 6946, new DateTime(2018, 1, 24), "France" },
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A:F"].ColumnWidth = 15;
worksheet.Range["A1:F16"].Value = sourceData;
ITable table = worksheet.Tables.Add(worksheet.Range["A1:F16"], true);
table.Columns[3].DataBodyRange.NumberFormat = "$#,##0.00";
//create slicer cache for table.
ISlicerCache cache = workbook.SlicerCaches.Add(table, "Category", "categoryCache");
//add slicer
ISlicer slicer = cache.Slicers.Add(workbook.Worksheets["Sheet1"], "cate2", "Category", 30, 550, 100, 200);
//create custom slicer style.
ITableStyle slicerStyle = workbook.TableStyles.Add("test");
//set ShowAsAvailableSlicerStyle to true, the style will be treated as slicer style.
slicerStyle.ShowAsAvailableSlicerStyle = true;
slicerStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Name = "Arial";
slicerStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Bold = false;
slicerStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Italic = false;
slicerStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Color = Color.White;
slicerStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders.Color = Color.LightPink;
slicerStyle.TableStyleElements[TableStyleElementType.WholeTable].Interior.Color = Color.LightGreen;
//set slicer style to custom style.
slicer.Style = slicerStyle;
}
public void UpdateColors(Transform rootElement, ITableStyle style)
{
var counter = 0;
var colors = style.Colors;
foreach (Transform child in rootElement)
{
var image = child.GetComponent <Image>();
if (image != null)
{
image.color = colors[counter++ % colors.Length];
}
}
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
//add table.
IWorksheet worksheet = workbook.Worksheets[0];
ITable table = worksheet.Tables.Add(worksheet.Range["A1:F7"], true);
worksheet.Range["A:F"].ColumnWidth = 15;
//Add one custom table style.
ITableStyle style = workbook.TableStyles.Add("test");
//set custom table style for table.
table.TableStyle = style;
//Use table style name get one build in table style.
ITableStyle tableStyle = workbook.TableStyles["TableStyleMedium3"];
//set built-in table style for table.
table.TableStyle = tableStyle;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet sheet = workbook.Worksheets[0];
//Add Table
ITable table = sheet.Tables.Add(sheet.Range["B5:G16"], true);
table.ShowTotals = true;
//Set values
int[] data = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 };
sheet.Range["C6:C16"].Value = data;
sheet.Range["D6:D16"].Value = data;
//Set total functions
table.Columns[1].TotalsCalculation = TotalsCalculation.Average;
table.Columns[2].TotalsCalculation = TotalsCalculation.Sum;
//Create custom table style
ITableStyle customTableStyle = workbook.TableStyles["TableStyleMedium10"].Duplicate();
var wholeTableStyle = customTableStyle.TableStyleElements[TableStyleElementType.WholeTable];
wholeTableStyle.Font.Italic = true;
wholeTableStyle.Borders[BordersIndex.EdgeTop].ThemeColor = ThemeColor.Accent1;
wholeTableStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thick;
wholeTableStyle.Borders[BordersIndex.EdgeRight].ThemeColor = ThemeColor.Accent1;
wholeTableStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thick;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent1;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
wholeTableStyle.Borders[BordersIndex.EdgeLeft].ThemeColor = ThemeColor.Accent1;
wholeTableStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thick;
var firstRowStripStyle = customTableStyle.TableStyleElements[TableStyleElementType.FirstRowStripe];
firstRowStripStyle.Font.Bold = true;
//Apply custom style to table
table.TableStyle = customTableStyle;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
//Add one custom table style.
ITableStyle style = workbook.TableStyles.Add("test");
//Set WholeTable element style.
style.TableStyleElements[TableStyleElementType.WholeTable].Font.Italic = true;
style.TableStyleElements[TableStyleElementType.WholeTable].Font.Color = Color.White;
style.TableStyleElements[TableStyleElementType.WholeTable].Font.Strikethrough = true;
style.TableStyleElements[TableStyleElementType.WholeTable].Borders.LineStyle = BorderLineStyle.Dotted;
style.TableStyleElements[TableStyleElementType.WholeTable].Borders.Color = Color.FromRGB(0, 193, 213);
style.TableStyleElements[TableStyleElementType.WholeTable].Interior.Color = Color.FromRGB(59, 92, 170);
//Set FirstColumnStripe element style.
style.TableStyleElements[TableStyleElementType.FirstColumnStripe].Font.Bold = true;
style.TableStyleElements[TableStyleElementType.FirstColumnStripe].Font.Color = Color.FromRGB(255, 0, 0);
style.TableStyleElements[TableStyleElementType.FirstColumnStripe].Borders.LineStyle = BorderLineStyle.Thick;
style.TableStyleElements[TableStyleElementType.FirstColumnStripe].Borders.ThemeColor = ThemeColor.Accent5;
style.TableStyleElements[TableStyleElementType.FirstColumnStripe].Interior.Color = Color.FromRGB(255, 255, 0);
style.TableStyleElements[TableStyleElementType.FirstColumnStripe].StripeSize = 2;
//Set SecondColumnStripe element style.
style.TableStyleElements[TableStyleElementType.SecondColumnStripe].Font.Color = Color.FromRGB(255, 0, 255);
style.TableStyleElements[TableStyleElementType.SecondColumnStripe].Borders.LineStyle = BorderLineStyle.DashDot;
style.TableStyleElements[TableStyleElementType.SecondColumnStripe].Borders.Color = Color.FromRGB(42, 105, 162);
style.TableStyleElements[TableStyleElementType.SecondColumnStripe].Interior.Color = Color.FromRGB(204, 204, 255);
//add table.
IWorksheet worksheet = workbook.Worksheets[0];
ITable table = worksheet.Tables.Add(worksheet.Range["A1:F7"], true);
worksheet.Range["A:F"].ColumnWidth = 15;
//set custom table style to table.
table.TableStyle = style;
}
private Workbook GetAddressBook()
{
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
//***************************Set RowHeight & Width****************************
worksheet.StandardHeight = 30;
worksheet.Range["3:4"].RowHeight = 30;
worksheet.Range["1:1"].RowHeight = 103.50;
worksheet.Range["2:2"].RowHeight = 38.25;
worksheet.Range["A:A"].ColumnWidth = 2.625;
worksheet.Range["B:B"].ColumnWidth = 22.25;
worksheet.Range["C:E"].ColumnWidth = 17.25;
worksheet.Range["F:F"].ColumnWidth = 31.875;
worksheet.Range["G:G"].ColumnWidth = 22.625;
worksheet.Range["H:H"].ColumnWidth = 30;
worksheet.Range["I:I"].ColumnWidth = 20.25;
worksheet.Range["J:J"].ColumnWidth = 17.625;
worksheet.Range["K:K"].ColumnWidth = 12.625;
worksheet.Range["L:L"].ColumnWidth = 37.25;
worksheet.Range["M:M"].ColumnWidth = 2.625;
//*******************************Set Table Value & Formulas*************************************
ITable table = worksheet.Tables.Add(worksheet.Range["B2:L4"], true);
worksheet.Range["B2:L4"].Value = new object[, ]
{
{ "NAME", "WORK", "CELL", "HOME", "EMAIL", "BIRTHDAY", "ADDRESS", "CITY", "STATE", "ZIP", "NOTE" },
{ "Kim Abercrombie", 1235550123, 1235550123, 1235550123, "*****@*****.**", null, "123 N. Maple", "Cherryville", "WA", 98031, "" },
{ "John Smith", 3215550123, "", "", "*****@*****.**", null, "456 E. Aspen", "", "", "", "" },
};
worksheet.Range["B1"].Value = "ADDRESS BOOK";
worksheet.Range["G3"].Formula = "=TODAY()";
worksheet.Range["G4"].Formula = "=TODAY()+5";
//****************************Set Table Style********************************
ITableStyle tableStyle = workbook.TableStyles.Add("Personal Address Book");
workbook.DefaultTableStyle = "Personal Address Book";
//Set WholeTable element style.
var wholeTableStyle = tableStyle.TableStyleElements[TableStyleElementType.WholeTable];
wholeTableStyle.Borders.Color = Color.FromArgb(179, 35, 23);
wholeTableStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
//Set FirstColumn element style.
tableStyle.TableStyleElements[TableStyleElementType.FirstColumn].Font.Bold = true;
//Set SecondColumns element style.
var headerRowStyle = tableStyle.TableStyleElements[TableStyleElementType.HeaderRow];
headerRowStyle.Borders.Color = Color.FromArgb(179, 35, 23);
headerRowStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thick;
headerRowStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
//***********************************Set Named Styles*****************************
IStyle normalStyle = workbook.Styles["Normal"];
normalStyle.Font.Name = "Arial";
normalStyle.Font.Color = Color.FromArgb(179, 35, 23);
normalStyle.HorizontalAlignment = HorizontalAlignment.Left;
normalStyle.IndentLevel = 1;
normalStyle.VerticalAlignment = VerticalAlignment.Center;
normalStyle.WrapText = true;
IStyle titleStyle = workbook.Styles["Title"];
titleStyle.IncludeAlignment = true;
titleStyle.HorizontalAlignment = HorizontalAlignment.Left;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleStyle.Font.Name = "Arial";
titleStyle.Font.Bold = true;
titleStyle.Font.Size = 72;
titleStyle.Font.Color = Color.FromArgb(179, 35, 23);
IStyle heading1Style = workbook.Styles["Heading 1"];
heading1Style.IncludeBorder = false;
heading1Style.Font.Name = "Arial";
heading1Style.Font.Size = 18;
heading1Style.Font.Color = Color.FromArgb(179, 35, 23);
IStyle dataStyle = workbook.Styles.Add("Data");
dataStyle.IncludeNumber = true;
dataStyle.NumberFormat = "m/d/yyyy";
IStyle phoneStyle = workbook.Styles.Add("Phone");
phoneStyle.IncludeNumber = true;
phoneStyle.NumberFormat = "[<=9999999]###-####;(###) ###-####";
//****************************************Use NamedStyle**************************
worksheet.SheetView.DisplayGridlines = false;
worksheet.Range["B2:L2"].Interior.Color = Color.FromArgb(217, 217, 217);
worksheet.Range["B3:B4"].Font.Bold = true;
worksheet.Range["2:2"].HorizontalAlignment = HorizontalAlignment.Left;
table.TableStyle = tableStyle;
worksheet.Range["B1"].Style = titleStyle;
worksheet.Range["B2:L2"].Style = heading1Style;
worksheet.Range["C3:E4"].Style = phoneStyle;
worksheet.Range["G3:G4"].Style = dataStyle;
return(workbook);;
}
private Workbook GetBidTracker()
{
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
//**********************Set RowHeight & ColumnWidth**************
worksheet.StandardHeight = 30;
worksheet.Range["1:1"].RowHeight = 57.75;
worksheet.Range["2:9"].RowHeight = 30;
worksheet.Range["A:A"].ColumnWidth = 2.71;
worksheet.Range["B:B"].ColumnWidth = 11.71;
worksheet.Range["C:C"].ColumnWidth = 28;
worksheet.Range["D:D"].ColumnWidth = 22.425;
worksheet.Range["E:E"].ColumnWidth = 16.71;
worksheet.Range["F:F"].ColumnWidth = 28;
worksheet.Range["G:H"].ColumnWidth = 16.71;
worksheet.Range["I:I"].ColumnWidth = 2.71;
//*************************Set Table Value & Formulas********************
ITable table = worksheet.Tables.Add(worksheet.Range["B2:H9"], true);
worksheet.Range["B2:H9"].Value = new object[, ]
{
{ "BID #", "DESCRIPTION", "DATE RECEIVED", "AMOUNT", "PERCENT COMPLETE", "DEADLINE", "DAYS LEFT" },
{ 1, "Bid number 1", null, 2000, 0.5, null, null },
{ 2, "Bid number 2", null, 3500, 0.25, null, null },
{ 3, "Bid number 3", null, 5000, 0.3, null, null },
{ 4, "Bid number 4", null, 4000, 0.2, null, null },
{ 5, "Bid number 5", null, 4000, 0.75, null, null },
{ 6, "Bid number 6", null, 1500, 0.45, null, null },
{ 7, "Bid number 7", null, 5000, 0.65, null, null },
};
worksheet.Range["B1"].Value = "Bid Details";
worksheet.Range["D3"].Formula = "=TODAY()-10";
worksheet.Range["D4:D5"].Formula = "=TODAY()-20";
worksheet.Range["D6"].Formula = "=TODAY()-10";
worksheet.Range["D7"].Formula = "=TODAY()-28";
worksheet.Range["D8"].Formula = "=TODAY()-17";
worksheet.Range["D9"].Formula = "=TODAY()-15";
worksheet.Range["G3:G9"].Formula = "=[@[DATE RECEIVED]]+30";
worksheet.Range["H3:H9"].Formula = "=[@DEADLINE]-TODAY()";
//***************************Set Table Style*******************************
ITableStyle tableStyle = workbook.TableStyles.Add("Bid Tracker");
workbook.DefaultTableStyle = "Bid Tracker";
var wholeTableStyle = tableStyle.TableStyleElements[TableStyleElementType.WholeTable];
//Set WholeTable element style.
wholeTableStyle.Font.Color = Color.FromArgb(89, 89, 89);
wholeTableStyle.Borders.Color = Color.FromArgb(89, 89, 89);
wholeTableStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
var headerRowStyle = tableStyle.TableStyleElements[TableStyleElementType.HeaderRow];
//Set HeaderRow element style.
headerRowStyle.Borders.Color = Color.FromArgb(89, 89, 89);
headerRowStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Interior.Color = Color.FromArgb(131, 95, 1);
headerRowStyle.Interior.PatternColor = Color.FromArgb(254, 184, 10);
var totalRowStyle = tableStyle.TableStyleElements[TableStyleElementType.TotalRow];
//Set TotalRow element style.
totalRowStyle.Borders.Color = Color.White;
totalRowStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Interior.Color = Color.FromArgb(131, 95, 1);
//**********************************Set Named Styles****************************
IStyle titleStyle = workbook.Styles["Title"];
titleStyle.Font.Name = "Calibri";
titleStyle.Font.Size = 36;
titleStyle.Font.Color = Color.FromArgb(56, 145, 167);
titleStyle.IncludeAlignment = true;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
IStyle heading1Style = workbook.Styles["Heading 1"];
heading1Style.IncludeAlignment = true;
heading1Style.HorizontalAlignment = HorizontalAlignment.Left;
heading1Style.IndentLevel = 1;
heading1Style.VerticalAlignment = VerticalAlignment.Bottom;
heading1Style.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
heading1Style.Font.Size = 14;
heading1Style.Font.Color = Color.White;
heading1Style.Font.Bold = false;
heading1Style.IncludePatterns = true;
heading1Style.Interior.Color = Color.White;
IStyle dateStyle = workbook.Styles.Add("Date");
dateStyle.IncludeNumber = true;
dateStyle.NumberFormat = "m/d/yyyy";
dateStyle.IncludeAlignment = true;
dateStyle.HorizontalAlignment = HorizontalAlignment.Left;
dateStyle.IndentLevel = 1;
dateStyle.VerticalAlignment = VerticalAlignment.Center;
dateStyle.IncludeFont = false;
dateStyle.IncludeBorder = false;
dateStyle.IncludePatterns = false;
IStyle commaStyle = workbook.Styles["Comma"];
commaStyle.IncludeNumber = true;
commaStyle.NumberFormat = "#,##0_);(#,##0)";
commaStyle.IncludeAlignment = true;
commaStyle.HorizontalAlignment = HorizontalAlignment.Left;
commaStyle.IndentLevel = 1;
commaStyle.VerticalAlignment = VerticalAlignment.Center;
IStyle normalStyle = workbook.Styles["Normal"];
normalStyle.HorizontalAlignment = HorizontalAlignment.Left;
normalStyle.IndentLevel = 1;
normalStyle.VerticalAlignment = VerticalAlignment.Center;
normalStyle.WrapText = true;
normalStyle.Font.Color = Color.FromArgb(89, 89, 89);
IStyle currencyStyle = workbook.Styles["Currency"];
currencyStyle.NumberFormat = "$#,##0.00";
currencyStyle.IncludeAlignment = true;
currencyStyle.HorizontalAlignment = HorizontalAlignment.Right;
currencyStyle.IndentLevel = 1;
currencyStyle.VerticalAlignment = VerticalAlignment.Center;
IStyle percentStyle = workbook.Styles["Percent"];
percentStyle.IncludeAlignment = true;
percentStyle.HorizontalAlignment = HorizontalAlignment.Right;
percentStyle.VerticalAlignment = VerticalAlignment.Center;
percentStyle.IncludeFont = true;
percentStyle.Font.Name = "Calibri";
percentStyle.Font.Size = 20;
percentStyle.Font.Bold = true;
percentStyle.Font.Color = Color.FromArgb(89, 89, 89);
IStyle comma0Style = workbook.Styles["Comma [0]"];
comma0Style.NumberFormat = "#,##0_);(#,##0)";
comma0Style.IncludeAlignment = true;
comma0Style.HorizontalAlignment = HorizontalAlignment.Right;
comma0Style.IndentLevel = 3;
comma0Style.VerticalAlignment = VerticalAlignment.Center;
//***********************************Add Conditional Formatting***************
IDataBar dataBar = worksheet.Range["F3:F9"].FormatConditions.AddDatabar();
dataBar.MinPoint.Type = ConditionValueTypes.Number;
dataBar.MinPoint.Value = 1;
dataBar.MaxPoint.Type = ConditionValueTypes.Number;
dataBar.MaxPoint.Value = 0;
dataBar.BarFillType = DataBarFillType.Gradient;
dataBar.BarColor.Color = Color.FromArgb(126, 194, 211);
dataBar.Direction = DataBarDirection.Context;
dataBar.AxisColor.Color = Color.Black;
dataBar.AxisPosition = DataBarAxisPosition.Automatic;
dataBar.NegativeBarFormat.ColorType = DataBarNegativeColorType.Color;
dataBar.NegativeBarFormat.Color.Color = Color.Red;
dataBar.ShowValue = true;
//***************************************Use NamedStyle*************************
worksheet.SheetView.DisplayGridlines = false;
table.TableStyle = tableStyle;
worksheet.Range["B1"].Style = titleStyle;
worksheet.Range["B1"].WrapText = false;
worksheet.Range["B2:H2"].Style = heading1Style;
worksheet.Range["B3:B9"].Style = commaStyle;
worksheet.Range["C3:C9"].Style = normalStyle;
worksheet.Range["D3:D9"].Style = dateStyle;
worksheet.Range["E3:E9"].Style = currencyStyle;
worksheet.Range["F3:F9"].Style = percentStyle;
worksheet.Range["G3:G9"].Style = dateStyle;
worksheet.Range["H3:H9"].Style = comma0Style;
return(workbook);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
#region theme
//create a custom theme.
Themes themes = new Themes();
ITheme theme = themes.Add("testTheme");
theme.ThemeColorScheme[ThemeColor.Light1].RGB = Color.FromRGB(255, 255, 255);
theme.ThemeColorScheme[ThemeColor.Dark1].RGB = Color.FromRGB(0, 0, 0);
theme.ThemeColorScheme[ThemeColor.Light2].RGB = Color.FromRGB(222, 222, 212);
theme.ThemeColorScheme[ThemeColor.Dark2].RGB = Color.FromRGB(30, 46, 47);
theme.ThemeColorScheme[ThemeColor.Accent1].RGB = Color.FromRGB(233, 117, 90);
theme.ThemeColorScheme[ThemeColor.Accent2].RGB = Color.FromRGB(122, 182, 186);
theme.ThemeColorScheme[ThemeColor.Accent3].RGB = Color.FromRGB(125, 181, 135);
theme.ThemeColorScheme[ThemeColor.Accent4].RGB = Color.FromRGB(230, 191, 94);
theme.ThemeColorScheme[ThemeColor.Accent5].RGB = Color.FromRGB(230, 143, 77);
theme.ThemeColorScheme[ThemeColor.Accent6].RGB = Color.FromRGB(194, 107, 112);
theme.ThemeColorScheme[ThemeColor.Hyperlink].RGB = Color.FromRGB(122, 182, 186);
theme.ThemeColorScheme[ThemeColor.FollowedHyperlink].RGB = Color.FromRGB(166, 140, 177);
theme.ThemeFontScheme.Major[FontLanguageIndex.Latin].Name = "Gill Sans";
theme.ThemeFontScheme.Minor[FontLanguageIndex.Latin].Name = "Gill Sans";
//assign the custom theme for workbook.
workbook.Theme = theme;
#endregion
//does not show sheet gridlines.
worksheet.SheetView.DisplayGridlines = false;
#region RowHeightColumnWidth
//set row height and column width.
worksheet.StandardHeight = 12.75;
worksheet.StandardWidth = 8.43;
worksheet.Rows[1].RowHeight = 32.25;
worksheet.Rows[2].RowHeight = 13.5;
worksheet.Rows[3].RowHeight = 18.75;
worksheet.Rows[6].RowHeight = 18.75;
worksheet.Rows[9].RowHeight = 18.75;
worksheet.Rows[12].RowHeight = 18.75;
worksheet.Rows[15].RowHeight = 19.5;
worksheet.Rows[16].RowHeight = 13.5;
worksheet.Rows[33].RowHeight = 19.5;
worksheet.Rows[34].RowHeight = 13.5;
worksheet.Columns[0].ColumnWidth = 1.7109375;
worksheet.Columns[1].ColumnWidth = 12.140625;
worksheet.Columns[2].ColumnWidth = 12.140625;
worksheet.Columns[3].ColumnWidth = 12.140625;
worksheet.Columns[4].ColumnWidth = 11.85546875;
worksheet.Columns[5].ColumnWidth = 12.7109375;
worksheet.Columns[6].ColumnWidth = 13.85546875;
worksheet.Columns[7].ColumnWidth = 44.7109375;
#endregion
#region Values
//initialize worksheet's values.
worksheet.Name = "BLOOD PRESSURE DATA";
worksheet.Range["B2"].Value = "BLOOD PRESSURE TRACKER";
worksheet.Range["B4:F13"].Value = new object[, ] {
{ "NAME", null, null, null, null },
{ null, null, null, null, null },
{ null, null, null, "Systolic", "Diastolic" },
{ "TARGET BLOOD PRESSURE", null, null, 120, 80 },
{ null, null, null, null, null },
{ null, null, null, "Systolic", "Diastolic" },
{ "CALL PHYSICIAN IF ABOVE", null, null, 140, 90 },
{ null, null, null, null, null },
{ null, null, null, null, null },
{ "PHYSICIAN PHONE NUMBER", null, null, "[Phone Number]", null }
};
worksheet.Range["B16"].Value = "CHARTED PROGRESS";
worksheet.Range["B34"].Value = "DATA ENTRY";
#endregion
#region Table
//initialize table data.
worksheet.Range["B36:H44"].Value = new object[, ] {
{ "TIME", "DATE", "AM/PM", "SYSTOLIC", "DIASTOLIC", "HEART RATE", "NOTES" },
{ new TimeSpan(10, 00, 00), DateTime.Parse("7/1/2013 12:00:00 AM"), "AM", 129, 99, 72, null },
{ new TimeSpan(18, 00, 00), DateTime.Parse("7/1/2013 12:00:00 AM"), "PM", 133, 80, 75, null },
{ new TimeSpan(10, 30, 00), DateTime.Parse("7/2/2012 12:00:00 AM"), "AM", 142, 86, 70, null },
{ new TimeSpan(19, 00, 00), DateTime.Parse("7/2/2012 12:00:00 AM"), "PM", 141, 84, 68, null },
{ new TimeSpan(09, 00, 00), DateTime.Parse("7/3/2012 12:00:00 AM"), "AM", 137, 84, 70, null },
{ new TimeSpan(18, 30, 00), DateTime.Parse("7/3/2012 12:00:00 AM"), "PM", 139, 83, 72, null },
{ new TimeSpan(10, 00, 00), DateTime.Parse("7/4/2012 12:00:00 AM"), "AM", 140, 85, 78, null },
{ new TimeSpan(18, 00, 00), DateTime.Parse("7/4/2012 12:00:00 AM"), "PM", 138, 85, 69, null },
};
ITable table = worksheet.Tables.Add(worksheet.Range["B36:H44"], true);
table.ShowTotals = true;
//set total row formulas.
table.Columns[0].Total.Value = "Average";
table.Columns[3].TotalsCalculation = TotalsCalculation.Average;
table.Columns[4].TotalsCalculation = TotalsCalculation.Average;
table.Columns[5].TotalsCalculation = TotalsCalculation.Average;
table.Columns[6].TotalsCalculation = TotalsCalculation.None;
//config data body range and total range's number format.
table.Columns[0].DataBodyRange.NumberFormat = "h:mm;@";
table.Columns[1].DataBodyRange.NumberFormat = "m/d/yyyy";
table.Columns[3].DataBodyRange.NumberFormat = "0";
table.Columns[4].DataBodyRange.NumberFormat = "0";
table.Columns[5].DataBodyRange.NumberFormat = "0";
table.Columns[3].Total.NumberFormat = "0";
table.Columns[4].Total.NumberFormat = "0";
table.Columns[5].Total.NumberFormat = "0";
//config table range's alignment.
table.Range.HorizontalAlignment = HorizontalAlignment.Left;
table.Range.IndentLevel = 0;
table.Range.VerticalAlignment = VerticalAlignment.Center;
#endregion
#region TableStyle
//create a custom table style.
ITableStyle tablestyle = workbook.TableStyles.Add("testStyle");
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Font.ThemeColor = ThemeColor.Dark1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Font.TintAndShade = 0.25;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.FirstRowStripe].Interior.ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.FirstRowStripe].Interior.TintAndShade = 0.8;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.Bold = true;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.ThemeColor = ThemeColor.Dark1;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.TintAndShade = 0.25;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Interior.ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Font.Bold = true;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Font.ThemeColor = ThemeColor.Dark1;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Font.TintAndShade = 0.25;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Double;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeTop].ThemeColor = ThemeColor.Accent1;
//assign custom table style for table.
table.TableStyle = workbook.TableStyles["testStyle"];
#endregion
#region Style
//assign built-in styles for ranges.
worksheet.Range["B2:H2"].Style = workbook.Styles["Heading 1"];
worksheet.Range["B4:F4, B7:D7, B10:D10, B13:D13"].Style = workbook.Styles["Heading 2"];
worksheet.Range["B16:H16, B34:H34"].Style = workbook.Styles["Heading 3"];
//modify built-in styles.
IStyle style_Heading1 = workbook.Styles["Heading 1"];
style_Heading1.HorizontalAlignment = HorizontalAlignment.General;
style_Heading1.VerticalAlignment = VerticalAlignment.Center;
style_Heading1.Font.ThemeFont = ThemeFont.Major;
style_Heading1.Font.Size = 24;
style_Heading1.Font.Bold = true;
style_Heading1.Font.ThemeColor = ThemeColor.Accent1;
style_Heading1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
style_Heading1.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent1;
style_Heading1.IncludeAlignment = true;
style_Heading1.IncludeFont = true;
style_Heading1.IncludeBorder = true;
style_Heading1.IncludeNumber = false;
style_Heading1.IncludePatterns = false;
style_Heading1.IncludeProtection = false;
IStyle style_Heading2 = workbook.Styles["Heading 2"];
style_Heading2.HorizontalAlignment = HorizontalAlignment.General;
style_Heading2.VerticalAlignment = VerticalAlignment.Bottom;
style_Heading2.Font.ThemeFont = ThemeFont.Minor;
style_Heading2.Font.Size = 14;
style_Heading2.Font.ThemeColor = ThemeColor.Dark1;
style_Heading2.Font.TintAndShade = 0.25;
style_Heading2.Font.Bold = false;
style_Heading2.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Dotted;
style_Heading2.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Light1;
style_Heading2.Borders[BordersIndex.EdgeBottom].TintAndShade = -0.5;
style_Heading2.IncludeAlignment = true;
style_Heading2.IncludeFont = true;
style_Heading2.IncludeBorder = true;
style_Heading2.IncludeNumber = false;
style_Heading2.IncludePatterns = false;
style_Heading2.IncludeProtection = false;
IStyle style_Heading3 = workbook.Styles["Heading 3"];
style_Heading3.HorizontalAlignment = HorizontalAlignment.General;
style_Heading3.VerticalAlignment = VerticalAlignment.Center;
style_Heading3.Font.ThemeFont = ThemeFont.Minor;
style_Heading3.Font.Size = 14;
style_Heading3.Font.Bold = true;
style_Heading3.Font.ThemeColor = ThemeColor.Dark1;
style_Heading3.Font.TintAndShade = 0.25;
style_Heading3.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
style_Heading3.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent2;
style_Heading3.IncludeAlignment = true;
style_Heading3.IncludeFont = true;
style_Heading3.IncludeBorder = true;
style_Heading3.IncludeNumber = false;
style_Heading3.IncludePatterns = false;
style_Heading3.IncludeProtection = false;
IStyle style_Normal = workbook.Styles["Normal"];
style_Normal.NumberFormat = "General";
style_Normal.HorizontalAlignment = HorizontalAlignment.General;
style_Normal.VerticalAlignment = VerticalAlignment.Center;
style_Normal.Font.ThemeFont = ThemeFont.Minor;
style_Normal.Font.Size = 10;
style_Normal.Font.ThemeColor = ThemeColor.Dark1;
style_Normal.Font.TintAndShade = 0.25;
style_Normal.IncludeAlignment = true;
style_Normal.IncludeFont = true;
style_Normal.IncludeBorder = true;
style_Normal.IncludeNumber = true;
style_Normal.IncludePatterns = true;
style_Normal.IncludeProtection = true;
//modify cell styles.
worksheet.Range["B4"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
worksheet.Range["C4:F4"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
IRange range1 = worksheet.Range["E7:F7, E10:F10"];
range1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Dotted;
range1.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Light1;
range1.Borders[BordersIndex.EdgeBottom].TintAndShade = -0.5;
range1.Font.Bold = true;
IRange range2 = worksheet.Range["E7, E10"];
range2.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
range2.Borders[BordersIndex.EdgeRight].ThemeColor = ThemeColor.Light1;
range2.Borders[BordersIndex.EdgeRight].TintAndShade = -0.5;
#endregion
#region Chart
//create a new chart.
IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 8.99984251968504, 268.5, 627.750157480315, 184.5);
//create series for chart.
ISeries series_systolic = shape.Chart.SeriesCollection.NewSeries();
ISeries series_diatolic = shape.Chart.SeriesCollection.NewSeries();
ISeries series_HeartRate = shape.Chart.SeriesCollection.NewSeries();
//set series formulas.
series_systolic.Formula = "=SERIES('BLOOD PRESSURE DATA'!$E$36,'BLOOD PRESSURE DATA'!$C$37:$D$44,'BLOOD PRESSURE DATA'!$E$37:$E$44,1)";
series_diatolic.Formula = "=SERIES('BLOOD PRESSURE DATA'!$F$36,'BLOOD PRESSURE DATA'!$C$37:$D$44,'BLOOD PRESSURE DATA'!$F$37:$F$44,2)";
//set series plot on secondary axis, and change its chart type.
series_HeartRate.AxisGroup = AxisGroup.Secondary;
series_HeartRate.ChartType = ChartType.Line;
series_HeartRate.Formula = "=SERIES('BLOOD PRESSURE DATA'!$G$36,,'BLOOD PRESSURE DATA'!$G$37:$G$44,3)";
//set series fill to gradient fill.
series_systolic.Format.Fill.TwoColorGradient(GradientStyle.Horizontal, 1);
series_systolic.Format.Fill.GradientAngle = 270;
series_systolic.Format.Fill.GradientStops[0].Color.RGB = Color.FromRGB(255, 172, 175);
series_systolic.Format.Fill.GradientStops[1].Color.RGB = Color.FromRGB(255, 227, 228);
series_systolic.Format.Fill.GradientStops.Insert(0xFEC6C8, 0.35);
series_systolic.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent6;
series_diatolic.Format.Fill.TwoColorGradient(GradientStyle.Horizontal, 1);
series_diatolic.Format.Fill.GradientAngle = 270;
series_diatolic.Format.Fill.GradientStops[0].Color.RGB = Color.FromRGB(255, 192, 147);
series_diatolic.Format.Fill.GradientStops[1].Color.RGB = Color.FromRGB(255, 227, 212);
series_diatolic.Format.Fill.GradientStops.Insert(0xFFCBA9, 0.35);
series_diatolic.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent5;
//set series gap width and overlap.
shape.Chart.ColumnGroups[0].GapWidth = 150;
shape.Chart.ColumnGroups[0].Overlap = 0;
//set series line style.
series_HeartRate.Format.Line.BeginArrowheadLength = ArrowheadLength.Medium;
series_HeartRate.Format.Line.BeginArrowheadStyle = ArrowheadStyle.None;
series_HeartRate.Format.Line.BeginArrowheadWidth = ArrowheadWidth.Medium;
series_HeartRate.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent4;
series_HeartRate.Format.Line.Color.TintAndShade = 0;
series_HeartRate.Format.Line.DashStyle = LineDashStyle.Solid;
series_HeartRate.Format.Line.EndArrowheadLength = ArrowheadLength.Medium;
series_HeartRate.Format.Line.EndArrowheadStyle = ArrowheadStyle.None;
series_HeartRate.Format.Line.EndArrowheadWidth = ArrowheadWidth.Medium;
series_HeartRate.Format.Line.Style = LineStyle.Single;
series_HeartRate.Format.Line.Weight = 1.25;
IAxis primary_axis = shape.Chart.Axes.Item(AxisType.Value, AxisGroup.Primary);
primary_axis.HasTitle = true;
primary_axis.AxisTitle.Text = "BLOOD PRESSURE";
primary_axis.AxisTitle.IncludeInLayout = true;
IAxis secondary_axis = shape.Chart.Axes.Item(AxisType.Value, AxisGroup.Secondary);
secondary_axis.HasTitle = true;
secondary_axis.AxisTitle.Text = "HEART RATE";
secondary_axis.AxisTitle.IncludeInLayout = true;
IAxis category_axis = shape.Chart.Axes.Item(AxisType.Category, AxisGroup.Primary);
category_axis.HasTitle = true;
category_axis.CategoryType = CategoryType.CategoryScale;
category_axis.Format.Line.Color.ColorType = SolidColorType.None;
shape.Chart.HasTitle = false;
//set chart font style.
shape.Chart.ChartArea.Font.Size = 9;
shape.Chart.ChartArea.Font.Color.ObjectThemeColor = ThemeColor.Dark1;
shape.Chart.ChartArea.Font.Color.Brightness = 0.5;
#endregion
#region Shape
IShape shape1 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 402, 77.25, 234, 100);
shape1.Fill.Solid();
shape1.Fill.Color.ObjectThemeColor = ThemeColor.Accent1;
shape1.Fill.Color.Brightness = 0.6;
//set shape's border to no line.
shape1.Line.Color.ColorType = SolidColorType.None;
//set shape rich text.
ITextRange shape1_p1 = shape1.TextFrame.TextRange.Paragraphs[0];
shape1_p1.Text = "*";
shape1_p1.Runs.Add(" Blood pressures may vary dependent on many");
shape1_p1.Runs.Add(" factors. Always consult with a physician about what is normal for you. These numbers may vary slightly.");
ITextRange shape1_p2 = shape1.TextFrame.TextRange.Paragraphs.Add("");
ITextRange shape1_p3 = shape1.TextFrame.TextRange.Paragraphs.Add("Info from National Institute of Health:");
ITextRange shape1_p4 = shape1.TextFrame.TextRange.Paragraphs.Add("http://www.nhlbi.nih.gov/health/health-topics/topics/hbp/");
shape1.TextFrame.TextRange.Font.Size = 10;
shape1.TextFrame.TextRange.Font.ThemeFont = ThemeFont.Minor;
shape1.TextFrame.TextRange.Font.Color.ObjectThemeColor = ThemeColor.Dark1;
shape1.TextFrame.TextRange.Font.Color.Brightness = 0.25;
shape1_p3.Runs[0].Font.Bold = true;
IShape shape2 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 421.5, 546.75, 198, 50);
shape2.Fill.Solid();
shape2.Fill.Color.ObjectThemeColor = ThemeColor.Accent3;
shape2.Fill.Color.Brightness = 0.6;
//set shape's border to no line.
shape2.Line.Color.ColorType = SolidColorType.None;
ITextRange shape2_p1 = shape2.TextFrame.TextRange.Paragraphs[0];
shape2_p1.Text = "NOTE:";
shape2_p1.Runs.Add(" Any blood pressure readings over the indicated numbers (\"CALL PHYSICIAN IF ABOVE\") will be");
shape2_p1.Runs.Add(" highlighted.");
shape2.TextFrame.TextRange.Font.Size = 10;
shape2.TextFrame.TextRange.Font.ThemeFont = ThemeFont.Minor;
shape2.TextFrame.TextRange.Font.Color.ObjectThemeColor = ThemeColor.Dark1;
shape2.TextFrame.TextRange.Font.Color.Brightness = 0.25;
shape2_p1.Runs[0].Font.Bold = true;
#endregion
#region DefinedName
//create defined names for workbook.
workbook.Names.Add("MaxDiastolic", "='BLOOD PRESSURE DATA'!$F$10");
workbook.Names.Add("MaxSystolic", "='BLOOD PRESSURE DATA'!$E$10");
#endregion
#region ConditionalFormat
//create conditional format for ranges.
IFormatCondition condition1 = worksheet.Range["E37:E44"].FormatConditions.Add(FormatConditionType.Expression, formula1: "=E37>MaxSystolic") as IFormatCondition;
IFormatCondition condition2 = worksheet.Range["F37:F44"].FormatConditions.Add(FormatConditionType.Expression, formula1: "=F37>MaxDiastolic") as IFormatCondition;
condition1.Interior.Color = Color.Red;
condition2.Interior.Color = Color.Red;
#endregion
}
private Workbook GetToToList()
{
Workbook workbook = new Workbook();
object[,] data = new object[, ] {
{ "TASK", "PRIORITY", "STATUS", "START DATE", "DUE DATE", "% COMPLETE", "DONE?", "NOTES" },
{ "First Thing I Need To Do", "Normal", "Not Started", null, null, 0, null, null },
{ "Other Thing I Need To Finish", "High", "In Progress", null, null, 0.5, null, null },
{ "Something Else To Get Done", "Low", "Complete", null, null, 1, null, null },
{ "More Errands And Things", "Normal", "In Progress", null, null, 0.75, null, null },
{ "So Much To Get Done This Week", "High", "In Progress", null, null, 0.25, null, null }
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "To-Do List";
worksheet.TabColor = Color.FromArgb(148, 112, 135);
worksheet.SheetView.DisplayGridlines = false;
//Set Value.
worksheet.Range["B1"].Value = "To-Do List";
worksheet.Range["B2:I7"].Value = data;
//Set formula.
worksheet.Range["E3"].Formula = "=TODAY()";
worksheet.Range["E4"].Formula = "=TODAY()-30";
worksheet.Range["E5"].Formula = "=TODAY()-23";
worksheet.Range["E6"].Formula = "=TODAY()-15";
worksheet.Range["E7"].Formula = "=TODAY()-5";
//Change the range's RowHeight and ColumnWidth.
worksheet.StandardHeight = 30;
worksheet.StandardWidth = 8.88671875;
worksheet.Range["1:1"].RowHeight = 72.75;
worksheet.Range["2:2"].RowHeight = 33;
worksheet.Range["3:7"].RowHeight = 30;
worksheet.Range["A:A"].ColumnWidth = 2.77734375;
worksheet.Range["B:B"].ColumnWidth = 29.109375;
worksheet.Range["C:G"].ColumnWidth = 16.77734375;
worksheet.Range["H:H"].ColumnWidth = 10.77734375;
worksheet.Range["I:I"].ColumnWidth = 29.6640625;
worksheet.Range["J:J"].ColumnWidth = 2.77734375;
//Modify the build in name styles.
var nameStyle_Normal = workbook.Styles["Normal"];
nameStyle_Normal.VerticalAlignment = VerticalAlignment.Center;
nameStyle_Normal.WrapText = true;
nameStyle_Normal.Font.ThemeFont = ThemeFont.Minor;
nameStyle_Normal.Font.ThemeColor = ThemeColor.Dark1;
nameStyle_Normal.Font.TintAndShade = 0.25;
var nameStyle_Title = workbook.Styles["Title"];
nameStyle_Title.HorizontalAlignment = HorizontalAlignment.General;
nameStyle_Title.VerticalAlignment = VerticalAlignment.Bottom;
nameStyle_Title.Font.ThemeFont = ThemeFont.Minor;
nameStyle_Title.Font.Bold = true;
nameStyle_Title.Font.Size = 38;
nameStyle_Title.Font.ThemeColor = ThemeColor.Dark1;
nameStyle_Title.Font.TintAndShade = 0.249946592608417;
nameStyle_Title.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
nameStyle_Title.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Dark1;
nameStyle_Title.IncludeAlignment = true;
nameStyle_Title.IncludeBorder = true;
var nameStyle_Percent = workbook.Styles["Percent"];
nameStyle_Percent.HorizontalAlignment = HorizontalAlignment.Right;
nameStyle_Percent.IndentLevel = 1;
nameStyle_Percent.VerticalAlignment = VerticalAlignment.Center;
nameStyle_Percent.IncludeAlignment = true;
var nameStyle_Heading_1 = workbook.Styles["Heading 1"];
nameStyle_Heading_1.HorizontalAlignment = HorizontalAlignment.Left;
nameStyle_Heading_1.VerticalAlignment = VerticalAlignment.Bottom;
nameStyle_Heading_1.Font.ThemeFont = ThemeFont.Major;
nameStyle_Heading_1.Font.Bold = false;
nameStyle_Heading_1.Font.Size = 11;
nameStyle_Heading_1.Font.ThemeColor = ThemeColor.Dark1;
nameStyle_Heading_1.Font.TintAndShade = 0.249946592608417;
nameStyle_Heading_1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
nameStyle_Heading_1.IncludeNumber = true;
nameStyle_Heading_1.IncludeAlignment = true;
nameStyle_Heading_1.IncludeBorder = false;
var nameStyle_Heading_2 = workbook.Styles["Heading 2"];
nameStyle_Heading_2.HorizontalAlignment = HorizontalAlignment.Right;
nameStyle_Heading_2.IndentLevel = 2;
nameStyle_Heading_2.VerticalAlignment = VerticalAlignment.Bottom;
nameStyle_Heading_2.Font.ThemeFont = ThemeFont.Major;
nameStyle_Heading_2.Font.Bold = false;
nameStyle_Heading_2.Font.Size = 11;
nameStyle_Heading_2.Font.ThemeColor = ThemeColor.Dark2;
nameStyle_Heading_2.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
nameStyle_Heading_2.IncludeNumber = true;
nameStyle_Heading_2.IncludeAlignment = true;
//Create custom name styes.
IStyle nameStyle_Done = workbook.Styles.Add("Done");
nameStyle_Done.NumberFormat = "\"Done\";\"\";\"\"";
nameStyle_Done.HorizontalAlignment = HorizontalAlignment.Center;
nameStyle_Done.VerticalAlignment = VerticalAlignment.Center;
nameStyle_Done.Font.ThemeFont = ThemeFont.Minor;
nameStyle_Done.Font.ThemeColor = ThemeColor.Light1;
IStyle nameStyle_Date = workbook.Styles.Add("Date");
nameStyle_Date.NumberFormat = "yyyy/m/d";
nameStyle_Date.HorizontalAlignment = HorizontalAlignment.Right;
nameStyle_Date.VerticalAlignment = VerticalAlignment.Center;
nameStyle_Date.Font.ThemeFont = ThemeFont.Minor;
nameStyle_Date.Font.ThemeColor = ThemeColor.Dark1;
nameStyle_Date.Font.TintAndShade = 0.249946592608417;
nameStyle_Date.IncludeBorder = false;
nameStyle_Date.IncludePatterns = false;
//Apply the above name styles on ranges.
worksheet.Range["B1:I1"].Style = workbook.Styles["Title"];
worksheet.Range["B2:D2"].Style = workbook.Styles["Heading 1"];
worksheet.Range["E2:F2"].Style = workbook.Styles["Heading 2"];
worksheet.Range["G2"].Style = workbook.Styles["Heading 1"];
worksheet.Range["H2:H7"].Style = workbook.Styles["Done"];
worksheet.Range["I2"].Style = workbook.Styles["Heading 1"];
worksheet.Range["E3:F7"].Style = workbook.Styles["Date"];
worksheet.Range["G3:G7"].Style = workbook.Styles["Percent"];
//Add one custom table style.
ITableStyle style = workbook.TableStyles.Add("To-do List");
var wholeTableStyle = style.TableStyleElements[TableStyleElementType.WholeTable];
wholeTableStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Light1;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].TintAndShade = -0.14993743705557422;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].ThemeColor = ThemeColor.Light1;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].TintAndShade = -0.14993743705557422;
//Create a table and apply the above table style.
ITable table = worksheet.Tables.Add(worksheet.Range["B2:I7"], true);
table.Name = "ToDoList";
table.TableStyle = style;
//Use table formula in table range.
worksheet.Range["F3"].Formula = "=[@[START DATE]]+7";
worksheet.Range["F4"].Formula = "=[@[START DATE]]+35";
worksheet.Range["F5"].Formula = "=[@[START DATE]]+10";
worksheet.Range["F6"].Formula = "=[@[START DATE]]+36";
worksheet.Range["F7"].Formula = "=[@[START DATE]]+14";
worksheet.Range["H3:H7"].Formula = "=--([@[% COMPLETE]]>=1)";
//Add a expression rule.
IFormatCondition expression = worksheet.Range["B3:I7"].FormatConditions.Add(FormatConditionType.Expression, FormatConditionOperator.Between, "=AND($G3=0,$G3<>\"\")", null) as IFormatCondition;
expression.Interior.ThemeColor = ThemeColor.Light1;
expression.Interior.TintAndShade = -0.0499893185216834;
//Add a data bar rule.
IDataBar dataBar = worksheet.Range["G3:G7"].FormatConditions.AddDatabar();
dataBar.BarFillType = DataBarFillType.Solid;
dataBar.BarColor.ThemeColor = ThemeColor.Accent1;
dataBar.BarColor.TintAndShade = 0.39997558519241921;
//Add an icon set rule.
IIconSetCondition iconSet = worksheet.Range["H3:H7"].FormatConditions.AddIconSetCondition();
iconSet.IconSet = workbook.IconSets[IconSetType.Icon3Symbols];
iconSet.IconCriteria[0].Operator = FormatConditionOperator.GreaterEqual;
iconSet.IconCriteria[0].Value = 1;
iconSet.IconCriteria[0].Type = ConditionValueTypes.Number;
//Create list validations.
worksheet.Range["C3:C7"].Validation.Add(ValidationType.List, ValidationAlertStyle.Warning, ValidationOperator.Between, "Low, Normal, High", null);
IValidation validation = worksheet.Range["C3:C7"].Validation;
validation.ErrorMessage = "Select entry from the list. Select CANCEL, then press ALT+DOWN ARROW to navigate the list. Select ENTER to make selection";
worksheet.Range["D3:D7"].Validation.Add(ValidationType.List, ValidationAlertStyle.Warning, ValidationOperator.Between, "Not Started,In Progress, Deferred, Complete", null);
validation = worksheet.Range["D3:D7"].Validation;
validation.ErrorMessage = "Select entry from the list. Select CANCEL, then press ALT+DOWN ARROW to navigate the list. Select ENTER to make selection";
worksheet.Range["G3:G7"].Validation.Add(ValidationType.List, ValidationAlertStyle.Warning, ValidationOperator.Between, "0%,25%,50%,75%,100%", null);
validation = worksheet.Range["G3:G7"].Validation;
validation.ErrorMessage = "Select entry from the list. Select CANCEL, then press ALT+DOWN ARROW to navigate the list. Select ENTER to make selection";
//Create custom validation.
worksheet.Range["F3:F7"].Validation.Add(ValidationType.Custom, ValidationAlertStyle.Warning, ValidationOperator.Between, "=F3>=E3", null);
validation = worksheet.Range["F3:F7"].Validation;
validation.ErrorMessage = "The Due Date must be greater than or equal to the Start Date. Select YES to keep the value, NO to retry or CANCEL to clear the entry";
//Create none validations, set inputmessage.
worksheet.Range["B2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["B2"].Validation;
validation.InputMessage = "Enter Task in this column under this heading. Use heading filters to find specific entries";
worksheet.Range["C2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["C2"].Validation;
validation.InputMessage = "Select Priority in this column under this heading. Press ALT+DOWN ARROW to open the drop-down list, then ENTER to make selection";
worksheet.Range["D2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["D2"].Validation;
validation.InputMessage = "Select Status in this column under this heading. Press ALT+DOWN ARROW to open the drop-down list, then ENTER to make selection";
worksheet.Range["E2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["E2"].Validation;
validation.InputMessage = "Enter Start Date in this column under this heading";
worksheet.Range["F2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["F2"].Validation;
validation.InputMessage = "Enter Due Date in this column under this heading";
worksheet.Range["G2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["G2"].Validation;
validation.InputMessage = "Select % Complete in this column. Press ALT+DOWN ARROW to open the drop-down list, then ENTER to make selection. A status bar indicates progress toward completion";
worksheet.Range["H2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["H2"].Validation;
validation.InputMessage = "Icon indicator for task completion in this column under this heading is automatically updated as tasks complete";
worksheet.Range["I2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["I2"].Validation;
validation.InputMessage = "Enter Notes in this column under this heading";
//Create customize theme.
Themes themes = new Themes();
ITheme theme = themes.Add("test");
theme.ThemeColorScheme[ThemeColor.Dark1].RGB = Color.FromArgb(0, 0, 0);
theme.ThemeColorScheme[ThemeColor.Light1].RGB = Color.FromArgb(255, 255, 255);
theme.ThemeColorScheme[ThemeColor.Dark2].RGB = Color.FromArgb(37, 28, 34);
theme.ThemeColorScheme[ThemeColor.Light2].RGB = Color.FromArgb(240, 248, 246);
theme.ThemeColorScheme[ThemeColor.Accent1].RGB = Color.FromArgb(148, 112, 135);
theme.ThemeColorScheme[ThemeColor.Accent2].RGB = Color.FromArgb(71, 166, 181);
theme.ThemeColorScheme[ThemeColor.Accent3].RGB = Color.FromArgb(234, 194, 53);
theme.ThemeColorScheme[ThemeColor.Accent4].RGB = Color.FromArgb(107, 192, 129);
theme.ThemeColorScheme[ThemeColor.Accent5].RGB = Color.FromArgb(233, 115, 61);
theme.ThemeColorScheme[ThemeColor.Accent6].RGB = Color.FromArgb(251, 147, 59);
theme.ThemeColorScheme[ThemeColor.Hyperlink].RGB = Color.FromArgb(71, 166, 181);
theme.ThemeColorScheme[ThemeColor.FollowedHyperlink].RGB = Color.FromArgb(148, 112, 135);
theme.ThemeFontScheme.Major[FontLanguageIndex.Latin].Name = "Franklin Gothic Medium";
theme.ThemeFontScheme.Minor[FontLanguageIndex.Latin].Name = "Bookman Old Style";
//Apply the above custom theme.
workbook.Theme = theme;
//Set active cell.
worksheet.Range["G4"].Activate();
return(workbook);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
//-------------------------Set RowHeight & Width-----------------------------------
worksheet.StandardHeight = 30;
worksheet.StandardWidth = 8.43;
worksheet.Range["1:1"].RowHeight = 278.25;
worksheet.Range["2:4"].RowHeight = 30.25;
worksheet.Range["8:8"].RowHeight = 55.5;
worksheet.Range["9:30"].RowHeight = 30.25;
worksheet.Range["33:33"].RowHeight = 55.5;
worksheet.Range["34:44"].RowHeight = 43.5;
worksheet.Range["A:A"].ColumnWidth = 2.777;
worksheet.Range["B:B"].ColumnWidth = 32.887;
worksheet.Range["C:C"].ColumnWidth = 24.219;
worksheet.Range["D:D"].ColumnWidth = 10.109;
worksheet.Range["E:E"].ColumnWidth = 61.332;
worksheet.Range["F:F"].ColumnWidth = 2.777;
//-------------------------Set Table Value & Formulas-------------------------------
ITable assetsTable = worksheet.Tables.Add(worksheet.Range["B9:D30"], true);
assetsTable.Name = "Assets";
worksheet.Range["B8"].Value = "Assets";
worksheet.Range["B9:D30"].Value = new object[, ]
{
{ "Category", "Item", "Value" },
{ "Real Estate", "Home", 560000 },
{ "Real Estate", "Other", 255000 },
{ "Investments", "Retirement accounts", 98000 },
{ "Investments", "Stocks", 53000 },
{ "Investments", "Bonds", 25000 },
{ "Investments", "Mutual funds", 33000 },
{ "Investments", "CDs", 74000 },
{ "Investments", "Bullion", 20000 },
{ "Investments", "Trust funds", 250000 },
{ "Investments", "Health savings account", 18000 },
{ "Investments", "Face value of life insurance policy", 85000 },
{ "Investments", "Other", 20000 },
{ "Cash", "Checking accounts", 14500 },
{ "Cash", "Savings accounts", 5000 },
{ "Cash", "Other", 2000 },
{ "Personal Property", "Cars", 55000 },
{ "Personal Property", "Other vehicles", 85000 },
{ "Personal Property", "Furnishings", 100000 },
{ "Personal Property", "Collectibles", 50000 },
{ "Personal Property", "Jewelry", 60000 },
{ "Personal Property", "Other luxury goods", 40000 },
};
ITable debtsTable = worksheet.Tables.Add(worksheet.Range["B34:C44"], true);
debtsTable.Name = "Debts";
worksheet.Range["B33"].Value = "Debts";
worksheet.Range["B34:C44"].Value = new object[, ]
{
{ "Category", "Value" },
{ "Mortgages", 400000 },
{ "Home equity loans", 50000 },
{ "Car loans", 30000 },
{ "Personal loans", 0 },
{ "Credit cards", 0 },
{ "Student loans", 10000 },
{ "Loans against investments", 20000 },
{ "Life insurance loans", 5000 },
{ "Other installment loans", 10000 },
{ "Other debts", 50000 },
};
worksheet.Range["B1:C1"].Merge();
worksheet.Range["B1"].Value = "Personal\r\nNet\r\nWorth";
worksheet.Range["B2"].Formula = "=\"Total \"&TotalAssetsLabel";
worksheet.Range["B3"].Formula = "=\"Total \"&TotalDebtsLabel";
worksheet.Range["B4"].Formula = "=NetWorthLabel";
worksheet.Range["C2"].Formula = "=TotalAssets";
worksheet.Range["C3"].Formula = "=TotalDebts";
worksheet.Range["C4"].Formula = "=NetWorth";
worksheet.Names.Add("TotalAssets", "=SUM(Assets[Value])");
worksheet.Names.Add("TotalDebts", "=SUM(Debts[Value])");
worksheet.Names.Add("NetWorth", "=TotalAssets-TotalDebts");
worksheet.Names.Add("TotalAssetsLabel", "=Sheet1!$B$8");
worksheet.Names.Add("TotalDebtsLabel", "=Sheet1!$B$33");
worksheet.Names.Add("NetWorthLabel", "=\"Net Worth\"");
//---------------------------Set Table Style---------------------------
ITableStyle assetsTableStyle = workbook.TableStyles.Add("Assets");
workbook.DefaultTableStyle = "Assets";
assetsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Color = Color.FromArgb(64, 64, 64);
assetsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders.Color = Color.FromArgb(128, 128, 128);
assetsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Dotted;
assetsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
assetsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.None;
assetsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.None;
assetsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.None;
assetsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.None;
assetsTableStyle.TableStyleElements[TableStyleElementType.SecondRowStripe].Interior.Color = Color.White;
assetsTableStyle.TableStyleElements[TableStyleElementType.SecondRowStripe].StripeSize = 1;
assetsTableStyle.TableStyleElements[TableStyleElementType.LastColumn].Font.Bold = true;
assetsTableStyle.TableStyleElements[TableStyleElementType.LastColumn].Font.Color = Color.FromArgb(61, 125, 137);
assetsTableStyle.TableStyleElements[TableStyleElementType.LastColumn].Interior.Color = Color.White;
assetsTableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Interior.Color = Color.FromArgb(61, 125, 137);
ITableStyle debtsTableStyle = workbook.TableStyles.Add("Debts");
debtsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Font.Color = Color.FromArgb(64, 64, 64);
debtsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders.Color = Color.FromArgb(128, 128, 128);
debtsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Dotted;
debtsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
debtsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.None;
debtsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.None;
debtsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.None;
debtsTableStyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.None;
debtsTableStyle.TableStyleElements[TableStyleElementType.SecondRowStripe].Interior.Color = Color.White;
debtsTableStyle.TableStyleElements[TableStyleElementType.SecondRowStripe].StripeSize = 1;
debtsTableStyle.TableStyleElements[TableStyleElementType.LastColumn].Font.Bold = true;
debtsTableStyle.TableStyleElements[TableStyleElementType.LastColumn].Font.Color = Color.FromArgb(146, 75, 12);
debtsTableStyle.TableStyleElements[TableStyleElementType.LastColumn].Interior.Color = Color.White;
debtsTableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Interior.Color = Color.FromArgb(218, 113, 18);
//----------------------------Set Named Styles-------------------------
IStyle normalStyle = workbook.Styles["Normal"];
normalStyle.Font.Name = "Century Gothic";
normalStyle.Font.Size = 12;
normalStyle.Font.Color = Color.FromArgb(64, 64, 64);
normalStyle.Interior.Color = Color.FromArgb(243, 243, 236);
normalStyle.Interior.PatternColor = Color.FromArgb(243, 243, 236);
normalStyle.HorizontalAlignment = HorizontalAlignment.Left;
normalStyle.IndentLevel = 1;
normalStyle.VerticalAlignment = VerticalAlignment.Center;
normalStyle.WrapText = true;
IStyle titleStyle = workbook.Styles["Title"];
titleStyle.IncludeAlignment = true;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleStyle.WrapText = true;
titleStyle.Font.Name = "Century Gothic";
titleStyle.Font.Size = 66;
titleStyle.Font.Color = Color.FromArgb(64, 64, 64);
titleStyle.IncludePatterns = true;
titleStyle.Interior.Color = Color.FromArgb(243, 243, 236);
IStyle heading1Style = workbook.Styles["Heading 1"];
heading1Style.IncludeAlignment = true;
heading1Style.HorizontalAlignment = HorizontalAlignment.Left;
heading1Style.IndentLevel = 4;
heading1Style.VerticalAlignment = VerticalAlignment.Center;
heading1Style.Font.Name = "Century Gothic";
heading1Style.Font.Bold = false;
heading1Style.Font.Size = 16;
heading1Style.Font.Color = Color.FromArgb(64, 64, 64);
heading1Style.IncludeBorder = false;
heading1Style.IncludePatterns = true;
heading1Style.Interior.Color = Color.FromArgb(243, 243, 236);
IStyle heading2Style = workbook.Styles["Heading 2"];
heading2Style.IncludeNumber = true;
heading2Style.NumberFormat = "$#,##0";
heading2Style.IncludeAlignment = true;
heading2Style.HorizontalAlignment = HorizontalAlignment.Right;
heading2Style.IndentLevel = 2;
heading2Style.VerticalAlignment = VerticalAlignment.Center;
heading2Style.Font.Name = "Century Gothic";
heading2Style.Font.Size = 16;
heading2Style.Font.Color = Color.FromArgb(64, 64, 64);
heading2Style.IncludeBorder = false;
heading2Style.IncludePatterns = true;
heading2Style.Interior.Color = Color.FromArgb(243, 243, 236);
IStyle heading3Style = workbook.Styles["Heading 3"];
heading3Style.IncludeAlignment = true;
heading3Style.HorizontalAlignment = HorizontalAlignment.Left;
heading3Style.VerticalAlignment = VerticalAlignment.Bottom;
heading3Style.IncludeBorder = false;
heading3Style.Font.Name = "Century Gothic";
heading3Style.Font.Bold = false;
heading3Style.Font.Size = 27;
heading3Style.Font.Color = Color.FromArgb(64, 64, 64);
heading3Style.IncludePatterns = true;
heading3Style.Interior.Color = Color.FromArgb(243, 243, 236);
IStyle heading4Style = workbook.Styles["Heading 4"];
heading4Style.Font.Name = "Century Gothic";
heading4Style.Font.Size = 16;
heading4Style.Font.Color = Color.White;
heading4Style.Font.Bold = false;
IStyle currencyStyle = workbook.Styles["Currency"];
currencyStyle.NumberFormat = "$#,##0";
currencyStyle.IncludeAlignment = true;
currencyStyle.HorizontalAlignment = HorizontalAlignment.Right;
currencyStyle.IndentLevel = 1;
currencyStyle.VerticalAlignment = VerticalAlignment.Center;
currencyStyle.IncludeFont = true;
currencyStyle.Font.Bold = true;
currencyStyle.Font.Name = "Century Gothic";
currencyStyle.Font.Size = 12;
//----------------------------------Use Style---------------------------
assetsTable.TableStyle = assetsTableStyle;
debtsTable.TableStyle = debtsTableStyle;
worksheet.SheetView.DisplayGridlines = false;
worksheet.Range["B2:B4"].Style = heading1Style;
worksheet.Range["C2:C4"].Style = heading2Style;
worksheet.Range["B9:D9"].Style = heading4Style;
worksheet.Range["D10:D30"].Style = currencyStyle;
worksheet.Range["D10:D30"].Font.Color = Color.FromArgb(61, 125, 137);
worksheet.Range["B34:C34"].Style = heading4Style;
worksheet.Range["C35:C44"].Style = currencyStyle;
worksheet.Range["C35:C44"].Font.Color = Color.FromArgb(218, 113, 18);
worksheet.Range["B1"].Style = titleStyle;
worksheet.Range["B8"].Style = heading3Style;
worksheet.Range["B33"].Style = heading3Style;
worksheet.Range["B3:C3"].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Hair;
worksheet.Range["B3:C3"].Borders[BordersIndex.EdgeTop].Color = Color.FromArgb(128, 128, 128);
worksheet.Range["B3:C3"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Hair;
worksheet.Range["B3:C3"].Borders[BordersIndex.EdgeBottom].Color = Color.FromArgb(128, 128, 128);
//--------------------------------Add Shape--------------------------------
IShape recShape1 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 17.81, 282.75, 20.963, 21.75);
recShape1.Line.Color.ColorType = SolidColorType.None;
recShape1.Fill.Color.RGB = Color.FromArgb(60, 126, 138);
IShape recShape2 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 17.81, 312.75, 20.963, 21.75);
recShape2.Line.Color.ColorType = SolidColorType.None;
recShape2.Fill.Color.RGB = Color.FromArgb(218, 118, 13);
IShape recShape3 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 17.81, 342.75, 20.963, 21.75);
recShape3.Line.Color.ColorType = SolidColorType.None;
recShape3.Fill.Color.RGB = Color.FromArgb(84, 138, 57);
IShape pieShape = worksheet.Shapes.AddChart(ChartType.Pie, 442.5, 26.25, 346, 350.25);
pieShape.Chart.HasLegend = false;
pieShape.Chart.HasTitle = false;
pieShape.Chart.ChartGroups[0].FirstSliceAngle = 180;
pieShape.Placement = Placement.Move;
IChartArea chartArea = pieShape.Chart.ChartArea;
chartArea.Format.Fill.Transparency = 1;
chartArea.Format.Line.Transparency = 1;
ISeries chartSeries = pieShape.Chart.SeriesCollection.NewSeries();
chartSeries.Formula = "=SERIES('Sheet1'!$B$2:$B$4,,'Sheet1'!$C$2:$C$4,1)";
chartSeries.HasDataLabels = true;
chartSeries.DataLabels.Font.Name = "Century Gothic";
chartSeries.DataLabels.Font.Size = 20;
chartSeries.DataLabels.Font.Bold = true;
chartSeries.DataLabels.Font.Color.RGB = Color.White;
chartSeries.DataLabels.ShowValue = false;
chartSeries.DataLabels.ShowPercentage = true;
chartSeries.DataLabels.Position = DataLabelPosition.Center;
chartSeries.Points[0].Format.Fill.Color.RGB = Color.FromArgb(60, 126, 138);
chartSeries.Points[1].Format.Fill.Color.RGB = Color.FromArgb(218, 118, 13);
chartSeries.Points[2].Format.Fill.Color.RGB = Color.FromArgb(84, 138, 57);
chartSeries.Explosion = 1;
}
private static void GeneratePresentation(string[] args)
{
string destination;
if (args.Length > 0)
{
destination = args[0];
}
else
{
Console.WriteLine("destination not specified");
return;
}
IPresentation presentation = null;
using (Stream source = typeof(Program).Assembly.GetManifestResourceStream("BinaryMesh.OpenXml.Explorer.Assets.ExamplePresentation.pptx"))
{
presentation = PresentationFactory.CreatePresentation(source);
}
using (presentation)
{
ISlide titleSlide = presentation.InsertSlide(presentation.SlideMasters[0].SlideLayouts[0]);
(titleSlide.ShapeTree.Visuals["Titel 1"] as IShapeVisual).Text.SetText("Automated Presentation Documents made easy");
(titleSlide.ShapeTree.Visuals["Untertitel 2"] as IShapeVisual).Text.SetText("BinaryMesh.OpenXml is an open-source library to easily and intuitively create OpenXml documents");
(titleSlide.ShapeTree.Visuals["Datumsplatzhalter 3"] as IShapeVisual).Text.SetText("10.10.2020");
ISlide chartSlide = presentation.InsertSlide(presentation.SlideMasters[0].SlideLayouts[6]);
IChartVisual pieChartVisual = chartSlide.ShapeTree.AppendChartVisual("Chart 1")
.Transform.SetOffset(2032000, 719666)
.Transform.SetExtents(8128000, 5418667);
IChartSpace pieChartSpace = pieChartVisual.ChartSpace;
using (ISpreadsheetDocument spreadsheet = pieChartSpace.OpenSpreadsheetDocument())
{
IWorkbook workbook = spreadsheet.Workbook;
IWorksheet sheet = workbook.AppendWorksheet("Sheet1");
string reference = sheet.Cells[0, 1].SetValue("Costs").Reference;
sheet.Cells[1, 0].SetValue("1. Quarter");
sheet.Cells[2, 0].SetValue("2. Quarter");
sheet.Cells[3, 0].SetValue("3. Quarter");
sheet.Cells[4, 0].SetValue("4. Quarter");
sheet.Cells[1, 1].SetValue(152306);
sheet.Cells[2, 1].SetValue(128742);
sheet.Cells[3, 1].SetValue(218737);
sheet.Cells[4, 1].SetValue(187025);
IPieChart pieChart = pieChartSpace.InsertPieChart()
.SetFirstSliceAngle(Math.PI * 0.5)
.SetExplosion(0.8)
.SetHoleSize(0.5);
pieChart.Series
.SetText(workbook.GetRange("Sheet1!$A$2"))
.SetCategoryAxis(workbook.GetRange("Sheet1!$B$1:$E$1"))
.SetValueAxis(workbook.GetRange("Sheet1!B$2:$E$2"))
.DataLabel.SetShowValue(true)
.DataLabel.SetShowSeriesName(false)
.DataLabel.SetShowCategoryName(false)
.DataLabel.SetShowLegendKey(false)
.DataLabel.SetShowPercent(false)
.DataLabel.Text.SetFontColor(OpenXmlColor.Rgb(0xFFFFFF));
pieChart.Series.Values[0].Style.SetFill(OpenXmlColor.Rgb(0x00FFFF));
pieChart.Series.Values[1].Style.SetFill(OpenXmlColor.Rgb(0xFFFFFF));
pieChart.Series.Values[2].Style.SetFill(OpenXmlColor.Rgb(0xFFFF00));
pieChart.Series.Values[3].Style.SetFill(OpenXmlColor.Rgb(0xFF0000));
}
IChartVisual barChartVisual = chartSlide.ShapeTree.AppendChartVisual("Chart 2")
.Transform.SetOffset(2032000, 719666)
.Transform.SetExtents(8128000, 5418667);
IChartSpace barChartSpace = barChartVisual.ChartSpace;
using (ISpreadsheetDocument spreadsheet = barChartSpace.OpenSpreadsheetDocument())
{
IWorkbook workbook = spreadsheet.Workbook;
IWorksheet sheet = workbook.AppendWorksheet("Sheet1");
string reference = sheet.Cells[0, 1].SetValue("Costs").Reference;
sheet.Cells["A2"].SetValue("Kategorie 1");
sheet.Cells["A3"].SetValue("Kategorie 2");
sheet.Cells["A4"].SetValue("Kategorie 3");
sheet.Cells["A5"].SetValue("Kategorie 4");
sheet.Cells["B1"].SetValue("Label 1");
sheet.Cells["C1"].SetValue("Label 2");
sheet.Cells["D1"].SetValue("Label 3");
sheet.Cells["B2"].SetValue(106);
sheet.Cells["B3"].SetValue(18742);
sheet.Cells["B4"].SetValue(237);
sheet.Cells["B5"].SetValue(1025);
sheet.Cells["C2"].SetValue(12306);
sheet.Cells["C3"].SetValue(3441);
sheet.Cells["C4"].SetValue(325234);
sheet.Cells["C5"].SetValue(123);
sheet.Cells["D2"].SetValue(25241);
sheet.Cells["D3"].SetValue(8345);
sheet.Cells["D4"].SetValue(132523);
sheet.Cells["D5"].SetValue(12345);
CartesianAxes axes = barChartSpace.AppendCartesianAxes();
axes.CategoryAxis.SetVisibility(false);
ILineChart lineChart = barChartSpace.InsertLineChart(axes)
.InitializeFromRange(sheet.GetRange("B1:D1"), sheet.GetRange("A2:A5"));
IBarChart barChart = barChartSpace.InsertBarChart(axes)
.SetDirection(BarChartDirection.Column)
.SetGrouping(BarChartGrouping.Clustered)
.InitializeFromRange(sheet.GetRange("B1:D1"), sheet.GetRange("A2:A5"));
barChart.Series[0]
.DataLabel.SetShowValue(true)
.DataLabel.SetShowSeriesName(false)
.DataLabel.SetShowCategoryName(false)
.DataLabel.SetShowLegendKey(false)
.DataLabel.Style.SetFill(OpenXmlColor.Rgb(0x000000).WithAlpha(0.3))
.DataLabel.Text.SetFontColor(OpenXmlColor.Rgb(0x00000));
barChart.Series[0].Values[0]
.DataLabel.SetDelete(true);
barChart.Series[0].Values[1]
.DataLabel.Text.SetFontColor(OpenXmlColor.Rgb(0xFF0000))
.DataLabel.Style.SetFill(OpenXmlColor.Accent4);
barChart.Series[1]
.DataLabel.SetShowValue(true)
.DataLabel.SetShowSeriesName(false)
.DataLabel.SetShowCategoryName(false)
.DataLabel.SetShowLegendKey(false)
.DataLabel.Style.SetFill(OpenXmlColor.Accent6.WithLuminanceModulation(0.75))
.DataLabel.Text.SetFontColor(OpenXmlColor.Rgb(0xFFFFFF));
barChart.Series[0].Values[1].Style.SetFill(OpenXmlColor.Accent2);
barChart.Series[1].Values[1].Style.SetFill(OpenXmlColor.Accent2);
barChart.Series[2].Values[1].Style.SetFill(OpenXmlColor.Accent2);
barChartSpace.CategoryAxes[0]
.Text.SetFontSize(8)
.MajorGridlines.Style.SetStroke(OpenXmlColor.Accent3)
.MajorGridlines.Style.SetStrokeWidth(2);
barChartSpace.ValueAxes[0]
.Text.SetFontSize(8)
.MajorGridlines.Remove();
}
chartSlide.ShapeTree.AppendShapeVisual("Shape 8")
.Transform.SetOffset(OpenXmlUnit.Cm(3), OpenXmlUnit.Cm(3))
.Transform.SetExtents(OpenXmlUnit.Cm(10), OpenXmlUnit.Cm(10))
.Text.SetText("TEST 2")
.Text.SetFontSize(8)
.Text.SetTextAlign(TextAlignmentTypeValues.Center)
.Text.SetTextAnchor(TextAnchoringTypeValues.Center)
.Text.SetIsBold(true)
.Style.SetFill(OpenXmlColor.Accent6.WithLuminanceModulation(0.75))
.Style.SetStroke(OpenXmlColor.Rgb(0, 0, 255))
.Style.SetStrokeWidth(0.5)
.Style.SetStrokeDash(PresetLineDashValues.LargeDash)
.Style.SetPresetGeometry(OpenXmlPresetGeometry.BuildChevron(28868));
ISlide burndownSlide = presentation.InsertSlide(presentation.SlideMasters[0].SlideLayouts[6]);
IChartVisual burndownChartVisual = burndownSlide.ShapeTree.AppendChartVisual("Burndown Chart 1")
.Transform.SetOffset(2032000, 719666)
.Transform.SetExtents(8128000, 5418667);
/*IChartSpace burndownChartSpace = burndownChartVisual.ChartSpace;
* burndownChartSpace.BuildBurndownChart(new BurndownChartData(
* new BurndownChartCategory(
* "In Akquise",
* new BurndownChartSeries("erfolgversprechend", 15, s => s.Style.SetFill(OpenXmlColor.Accent1)),
* new BurndownChartSeries("unwahrscheinlich", 28, s => s.Style.SetFill(OpenXmlColor.Accent2))
* ),
* new BurndownChartCategory(
* "Projektphase",
* new BurndownChartSeries("in Arbeit", 32, s => s.Style.SetFill(OpenXmlColor.Accent3))
* ),
* new BurndownChartCategory(
* "Abgeschlossen",
* new BurndownChartSeries("erfolgreich", 86, s => s.Style.SetFill(OpenXmlColor.Accent4)),
* new BurndownChartSeries("abgebrochen", 3, s => s.Style.SetFill(OpenXmlColor.Accent5))
* )
* ))
* .ConfigureConnector(c => c.Style.SetStroke(OpenXmlColor.Text1).Style.SetStrokeWidth(0.5))
* .ConfigureTotal(t => t.Style.SetFill(OpenXmlColor.Accent5))
* .ConfigureSeries(0, 0, s => s.Style.SetFill(OpenXmlColor.Rgb(0x00FF00)).DataLabel.SetShowValue(true).DataLabel.Text.SetFontColor(OpenXmlColor.Text1).DataLabel.Style.SetFill(OpenXmlColor.Light1))
* .ConfigureSeries(0, 1, s => s.Style.SetFill(OpenXmlColor.Rgb(0xFFFF00)))
* .ConfigureSeries(1, 0, s => s.Style.SetFill(OpenXmlColor.Rgb(0x0000FF)))
* .ConfigureSeries(2, 0, s => s.Style.SetFill(OpenXmlColor.Rgb(0x00FF00)))
* .ConfigureSeries(2, 1, s => s.Style.SetFill(OpenXmlColor.Rgb(0xFF0000)));*/
new BurndownChartConfig()
.AddCategory("In Akquise")
.AddValue("erfolgsversprechend", 15)
.WithStyle(s => s.Style.SetFill(OpenXmlColor.Rgb(0x00FF00)).DataLabel.SetShowValue(true).DataLabel.Text.SetFontColor(OpenXmlColor.Text1).DataLabel.Style.SetFill(OpenXmlColor.Light1))
.AddValue("unwahrscheinlich", 28)
.WithStyle(s => s.Style.SetFill(OpenXmlColor.Rgb(0xFFFF00)))
.AddCategory("Projektphase")
.AddValue("in Arbeit", 32)
.AddCategory("Abgeschlossen")
.WithCustomOffset(0)
.AddValue("in Arbeit", 86)
.AddCategory("Abgeschlossen - erfolgreich")
.WithCustomOffset(0)
.AddValue("in Arbeit", 56)
.AddCategory("Abgeschlossen - abgebrochen")
.WithCustomOffset(86)
.AddValue("in Arbeit", 63)
.WithConnectorStyle(c => c.Style.SetStroke(OpenXmlColor.Text1).Style.SetStrokeWidth(0.5))
.WithTotal("Gesamt")
.WithTotalStyle(t => t.Style.SetFill(OpenXmlColor.Accent5))
.Apply(burndownChartVisual.ChartSpace);
ISlide tableSlide = presentation.InsertSlide(presentation.SlideMasters[0].SlideLayouts[6]);
ITableStyle tableStyle = CreateTableStyle(presentation);
ITableVisual table = tableSlide.ShapeTree.AppendTableVisual("Table 1")
.Transform.SetOffset(OpenXmlUnit.Cm(5), OpenXmlUnit.Cm(5))
.SetStyle(tableStyle)
.SetHasFirstRow(true)
.SetHasBandRow(true);
table.AppendColumn(OpenXmlUnit.Inch(1));
table.AppendColumn(OpenXmlUnit.Cm(5));
table.AppendRow(OpenXmlUnit.Cm(0));
table.AppendRow(OpenXmlUnit.Cm(0));
table.Cells[0, 0].Text.SetText("Hello World") /*.Text.SetFontSize(28).Text.SetFont("Arial")*/;
table.Cells[1, 0].Text.SetText("World") /*.Text.SetIsBold(true).Text.SetFont("Comic Sans MS")*/;
table.Cells[0, 1].Text.SetText("ABC") /*.Text.SetFontColor(OpenXmlColor.Accent2)*/;
table.Cells[1, 1].Text.SetText("123") /*.Text.SetFontColor(OpenXmlColor.Rgb(25, 240, 120))*/;
OpenXmlSize rowSize = table.Rows[0].Measure();
OpenXmlSize columnSize = table.Columns[0].Measure();
OpenXmlSize tableSize = table.Measure();
using (Stream stream = new FileStream(destination, FileMode.Create, FileAccess.ReadWrite))
{
presentation.Close(stream);
}
}
new Process
{
StartInfo = new ProcessStartInfo(destination)
{
UseShellExecute = true
}
}.Start();
}
public Table(ITableStyle style)
{
Style = style;
}
/**
* @param builtIn
* @param style
*/
internal XSSFBuiltinTypeStyleStyle(XSSFBuiltinTableStyleEnum builtIn, ITableStyle style)
{
this.builtIn = builtIn;
this.style = style;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
//------------------Set RowHeight & ColumnWidth----------------
worksheet.StandardHeight = 43.5;
worksheet.StandardWidth = 8.43;
worksheet.Range["1:1"].RowHeight = 171;
worksheet.Range["2:2"].RowHeight = 12.75;
worksheet.Range["3:3"].RowHeight = 22.5;
worksheet.Range["4:7"].RowHeight = 43.75;
worksheet.Range["A:A"].ColumnWidth = 2.887;
worksheet.Range["B:B"].ColumnWidth = 8.441;
worksheet.Range["C:C"].ColumnWidth = 12.777;
worksheet.Range["D:D"].ColumnWidth = 25.109;
worksheet.Range["E:E"].ColumnWidth = 12.109;
worksheet.Range["F:F"].ColumnWidth = 41.664;
worksheet.Range["G:G"].ColumnWidth = 18.555;
worksheet.Range["H:H"].ColumnWidth = 11;
worksheet.Range["I:I"].ColumnWidth = 13.664;
worksheet.Range["J:J"].ColumnWidth = 15.109;
worksheet.Range["K:K"].ColumnWidth = 38.887;
worksheet.Range["L:L"].ColumnWidth = 2.887;
//------------------------Set Table Values-------------------
ITable table = worksheet.Tables.Add(worksheet.Range["B3:K7"], true);
worksheet.Range["B3:K7"].Value = new object[, ]
{
{ "NO.", "YEAR", "TITLE", "REVIEW", "STARRING ACTORS", "DIRECTOR", "GENRE", "RATING", "FORMAT", "COMMENTS" },
{ 1, 1994, "Forrest Gump", "5 Stars", "Tom Hanks, Robin Wright, Gary Sinise", "Robert Zemeckis", "Drama", "PG-13", "DVD", "Based on the 1986 novel of the same name by Winston Groom" },
{ 2, 1946, "It’s a Wonderful Life", "2 Stars", "James Stewart, Donna Reed, Lionel Barrymore ", "Frank Capra", "Drama", "G", "VHS", "Colorized version" },
{ 3, 1988, "Big", "4 Stars", "Tom Hanks, Elizabeth Perkins, Robert Loggia ", "Penny Marshall", "Comedy", "PG", "DVD", "" },
{ 4, 1954, "Rear Window", "3 Stars", "James Stewart, Grace Kelly, Wendell Corey ", "Alfred Hitchcock", "Suspense", "PG", "Blu-ray", "" },
};
//-----------------------Set Table style--------------------
ITableStyle tableStyle = workbook.TableStyles.Add("Movie List");
workbook.DefaultTableStyle = "Movie List";
tableStyle.TableStyleElements[TableStyleElementType.WholeTable].Interior.Color = Color.White;
tableStyle.TableStyleElements[TableStyleElementType.FirstRowStripe].Interior.Color = Color.FromRGB(38, 38, 38);
tableStyle.TableStyleElements[TableStyleElementType.SecondRowStripe].Interior.Color = Color.Black;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.Color = Color.Black;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders.Color = Color.FromRGB(38, 38, 38);
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Interior.Color = Color.FromRGB(68, 217, 255);
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thick;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.None;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.None;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.None;
tableStyle.TableStyleElements[TableStyleElementType.HeaderRow].Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.None;
//--------------------------------Set Named Styles---------------------
IStyle movieListBorderStyle = workbook.Styles.Add("Movie list border");
movieListBorderStyle.IncludeNumber = true;
movieListBorderStyle.IncludeAlignment = true;
movieListBorderStyle.VerticalAlignment = VerticalAlignment.Center;
movieListBorderStyle.WrapText = true;
movieListBorderStyle.IncludeFont = true;
movieListBorderStyle.Font.Name = "Helvetica";
movieListBorderStyle.Font.Size = 11;
movieListBorderStyle.Font.Color = Color.White;
movieListBorderStyle.IncludeBorder = true;
movieListBorderStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
movieListBorderStyle.Borders[BordersIndex.EdgeBottom].Color = Color.FromRGB(38, 38, 38);
movieListBorderStyle.IncludePatterns = true;
movieListBorderStyle.Interior.Color = Color.FromRGB(238, 219, 78);
IStyle nOStyle = workbook.Styles.Add("NO.");
nOStyle.IncludeNumber = true;
nOStyle.IncludeAlignment = true;
nOStyle.HorizontalAlignment = HorizontalAlignment.Left;
nOStyle.VerticalAlignment = VerticalAlignment.Center;
nOStyle.IncludeFont = true;
nOStyle.Font.Name = "Helvetica";
nOStyle.Font.Size = 11;
nOStyle.Font.Color = Color.White;
nOStyle.IncludeBorder = true;
nOStyle.IncludePatterns = true;
nOStyle.Interior.Color = Color.FromRGB(38, 38, 38);
IStyle reviewStyle = workbook.Styles.Add("Review");
reviewStyle.IncludeNumber = true;
reviewStyle.IncludeAlignment = true;
reviewStyle.VerticalAlignment = VerticalAlignment.Center;
reviewStyle.IncludeFont = true;
reviewStyle.Font.Name = "Helvetica";
reviewStyle.Font.Size = 11;
reviewStyle.Font.Color = Color.White;
reviewStyle.IncludeBorder = true;
reviewStyle.IncludePatterns = true;
reviewStyle.Interior.Color = Color.FromRGB(38, 38, 38);
IStyle yearStyle = workbook.Styles.Add("Year");
yearStyle.IncludeNumber = true;
yearStyle.IncludeAlignment = true;
yearStyle.HorizontalAlignment = HorizontalAlignment.Left;
yearStyle.VerticalAlignment = VerticalAlignment.Center;
yearStyle.IncludeFont = true;
yearStyle.Font.Name = "Helvetica";
yearStyle.Font.Size = 11;
yearStyle.Font.Color = Color.White;
yearStyle.IncludeBorder = true;
yearStyle.IncludePatterns = true;
yearStyle.Interior.Color = Color.FromRGB(38, 38, 38);
IStyle heading1Style = workbook.Styles["Heading 1"];
heading1Style.IncludeAlignment = true;
heading1Style.VerticalAlignment = VerticalAlignment.Bottom;
heading1Style.IncludeBorder = true;
heading1Style.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
heading1Style.Borders[BordersIndex.EdgeBottom].Color = Color.FromRGB(68, 217, 255);
heading1Style.IncludeFont = true;
heading1Style.Font.Name = "Helvetica";
heading1Style.Font.Bold = false;
heading1Style.Font.Size = 12;
heading1Style.Font.Color = Color.Black;
IStyle normalStyle = workbook.Styles["Normal"];
normalStyle.IncludeNumber = true;
normalStyle.IncludeAlignment = true;
normalStyle.VerticalAlignment = VerticalAlignment.Center;
normalStyle.WrapText = true;
normalStyle.IncludeFont = true;
normalStyle.Font.Name = "Helvetica";
normalStyle.Font.Size = 11;
normalStyle.Font.Color = Color.White;
normalStyle.IncludePatterns = true;
normalStyle.Interior.Color = Color.FromRGB(38, 38, 38);
//-----------------------------Use NamedStyle--------------------------
worksheet.SheetView.DisplayGridlines = false;
worksheet.TabColor = Color.FromRGB(38, 38, 38);
table.TableStyle = tableStyle;
worksheet.Range["A2:L2"].Style = movieListBorderStyle;
worksheet.Range["B3:K3"].Style = heading1Style;
worksheet.Range["B4:B7"].Style = nOStyle;
worksheet.Range["C4:C7"].Style = yearStyle;
worksheet.Range["E4:E7"].Style = reviewStyle;
worksheet.Range["F4:F7"].IndentLevel = 1;
worksheet.Range["F4:F7"].HorizontalAlignment = HorizontalAlignment.Left;
//-----------------------------Add Shapes------------------------------
//Movie picture
System.IO.Stream stream = this.GetResourceStream("movie.png");
IShape pictureShape = worksheet.Shapes.AddPicture(stream, ImageType.PNG, 0, 1, worksheet.Range["A:L"].Width, worksheet.Range["1:1"].Height - 1.5);
pictureShape.Placement = Placement.Move;
//Movie list picture
System.IO.Stream stream2 = this.GetResourceStream("list.png");
IShape pictureShape2 = worksheet.Shapes.AddPicture(stream2, ImageType.PNG, 1, 0.8, 325.572, 85.51);
pictureShape2.Placement = Placement.Move;
//Rounded Rectangular Callout 7
IShape roundedRectangular = worksheet.Shapes.AddShape(AutoShapeType.RoundedRectangularCallout, 437.5, 22.75, 342, 143);
roundedRectangular.Name = "Rounded Rectangular Callout 7";
roundedRectangular.Placement = Placement.Move;
roundedRectangular.TextFrame.TextRange.Font.Name = "Helvetica";
roundedRectangular.TextFrame.TextRange.Font.Color.RGB = Color.FromRGB(38, 38, 38);
roundedRectangular.Fill.Solid();
roundedRectangular.Fill.Color.RGB = Color.FromRGB(68, 217, 255);
roundedRectangular.Fill.Transparency = 0;
roundedRectangular.Line.Solid();
roundedRectangular.Line.Color.RGB = Color.FromRGB(0, 129, 162);
roundedRectangular.Line.Weight = 2;
roundedRectangular.Line.Transparency = 0;
ITextRange roundedRectangular_p0 = roundedRectangular.TextFrame.TextRange.Paragraphs[0];
roundedRectangular_p0.Runs.Font.Bold = true;
roundedRectangular_p0.Runs.Add("TABLE");
roundedRectangular_p0.Runs.Add(" TIP");
roundedRectangular.TextFrame.TextRange.Paragraphs.Add("");
ITextRange roundedRectangular_p2 = roundedRectangular.TextFrame.TextRange.Paragraphs.Add();
roundedRectangular_p2.Runs.Add("Use the drop down arrows in the table headings to quickly filter your movie list. " +
"For multiple entry fields, such as Starring Actors, select the drop down arrow next to the field and enter text in the Search box. " +
"For example, type Tom Hanks or James Stewart, and then select OK.");
roundedRectangular.TextFrame.TextRange.Paragraphs.Add("");
ITextRange roundedRectangular_p4 = roundedRectangular.TextFrame.TextRange.Paragraphs.Add();
roundedRectangular_p4.Runs.Add("To delete this note, click the edge to select it and then press ");
roundedRectangular_p4.Runs.Add("Delete");
roundedRectangular_p4.Runs.Add(".");
roundedRectangular_p4.Runs[2].Font.Bold = true;
roundedRectangular.TextFrame.TextRange.Paragraphs.Add("");
//Add Stright Line Shape
IShape lineShape = worksheet.Shapes.AddConnector(ConnectorType.Straight, 455.228f, 57.35f, 756.228f, 57.35f);
lineShape.Line.Solid();
lineShape.Line.Weight = 3;
lineShape.Line.Color.RGB = Color.FromRGB(38, 38, 38);
lineShape.Line.DashStyle = LineDashStyle.SysDot;
}
