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