public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Load template file var fileStream = this.GetTemplateStream("Annual financial report.xlsx"); workbook.Open(fileStream); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { Stream stream = this.GetTemplateStream(); //Open csv file stream. workbook.Open(stream, OpenFileFormat.Csv); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Load template file Event budget.xlsx from resource var fileStream = this.GetTemplateStream("Event budget.xlsx"); workbook.Open(fileStream); var worksheet = workbook.ActiveSheet; //change range B2's font size. worksheet.Range["B2"].Font.Size = 22; //change range E4's font style to bold. worksheet.Range["E4"].Font.Bold = true; //change table style. worksheet.Tables["tblAdmissions"].TableStyle = workbook.TableStyles["TableStyleLight10"]; worksheet.Tables["tblAds"].TableStyle = workbook.TableStyles["TableStyleLight10"]; worksheet.Tables["tblVendors"].TableStyle = workbook.TableStyles["TableStyleLight10"]; worksheet.Tables["tblItems"].TableStyle = workbook.TableStyles["TableStyleLight10"]; //modify range F4:G5's cell style. worksheet.Range["F4:G5"].Interior.ThemeColor = ThemeColor.Light1; worksheet.Range["F4:G5"].Interior.TintAndShade = -0.15; worksheet.Range["F4:G5"].Font.ThemeFont = ThemeFont.Major; worksheet.Range["F4:G5"].Font.Size = 12; worksheet.Range["F4:G5"].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.None; worksheet.Range["F5:G5"].NumberFormat = "$#,##0.00"; //modify table columns' style. worksheet.Range["F8:G11, F15:G18, F22:G25, F29:G33"].Interior.ThemeColor = ThemeColor.Light1; worksheet.Range["F8:G11, F15:G18, F22:G25, F29:G33"].Interior.TintAndShade = -0.15; worksheet.Range["E8:G11, E15:G18, E22:G25, E29:G33"].NumberFormat = "$#,##0.00"; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Load template file var fileStream = this.GetTemplateStream("Simple personal budget.xlsx"); workbook.Open(fileStream); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Open an excel file var fileStream = this.GetTemplateStream("Employee absence schedule.xlsx"); workbook.Open(fileStream); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Load template file var fileStream = this.GetTemplateStream("Student calendar (Mon).xlsx"); workbook.Open(fileStream); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Load template file var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Change the path to the real file path when open. XlsxOpenOptions options = new XlsxOpenOptions(); options.Password = "******"; workbook.Open(this.CurrentDirectory + "source.xlsx", options); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream("PageSetup Print Title Columns.xlsx"); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set columns to repeat at left worksheet.PageSetup.PrintTitleColumns = "$D:$G"; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set page orientation, default is portrait. worksheet.PageSetup.Orientation = PageOrientation.Landscape; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set rows to repeat at top worksheet.PageSetup.PrintTitleRows = "$5:$10"; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set page order, default is DownThenOver. worksheet.PageSetup.Order = Order.OverThenDown; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Use XlsxOpenOptions.ImportFlags to control what you want to import from excel, ImportFlags.Data means only the data will be imported //Change the path to the real file path when open. XlsxOpenOptions options = new XlsxOpenOptions(); options.ImportFlags = ImportFlags.Data; workbook.Open(this.CurrentDirectory + "source.xlsx", options); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Open csv with more settings. CsvOpenOptions options = new CsvOpenOptions(); options.SeparatorString = "-"; //Change the path to the real file path when open. workbook.Open(this.CurrentDirectory + "source.csv", options); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream("PageSetup Demo.xlsx"); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set A4 paper size worksheet.PageSetup.PaperSize = PaperSize.A4; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Load template file Income statement.xlsx from resource var fileStream = this.GetResourceStream("xlsx\\Income statement.xlsx"); workbook.Open(fileStream); var worksheet = workbook.ActiveSheet; //set values object[,] RevData = new object[, ] { { 454597, 687802, 469902, 335846, 776667, 806250, 552036, 346734, 787205, 720496, 686963, 835688 }, { 821687, 679306, 568503, 715827, 627459, 822625, 271114, 797991, 823966, 304521, 758665, 322694 }, { 426510, 767295, 799314, 408533, 353862, 261403, 344075, 277433, 289814, 386539, 625447, 489178 }, }; object[,] CostData = new object[, ] { { 113053, 134668, 192784, 199522, 163169, 109389, 187568, 170346, 130920, 180119, 184725, 162780 }, { 42667, 14921, 42007, 10264, 48864, 10360, 42378, 31445, 15208, 34418, 37529, 22077 }, }; object[,] OpeData = new object[, ] { { 264290, 282301, 266021, 272333, 285359, 256072, 289430, 280307, 278656, 272674, 299784, 284062 }, { 29536, 28709, 27579, 27862, 26750, 26970, 27847, 27680, 28375, 25474, 27514, 26753 }, { 23473, 22902, 21680, 19232, 23077, 20839, 19461, 15504, 21117, 20102, 16322, 21160 }, { 11340, 11514, 11207, 9186, 9243, 10003, 11092, 10761, 9032, 10366, 9571, 11774 }, { 9562, 10787, 11809, 9949, 9559, 9753, 11761, 10702, 10545, 10347, 10593, 10719 }, { 9410, 9646, 9969, 10758, 9183, 10777, 11249, 9763, 9445, 11218, 10579, 10203 }, }; object[,] EarnData = new object[, ] { { 1038, 1430, 627, 1352, 765, 1190, 1082, 1261, 1240, 645, 552, 669 }, { 1227, 1372, 544, 1172, 1479, 821, 891, 1361, 1187, 760, 1126, 873 }, { 661, 1342, 1119, 1085, 887, 1274, 883, 582, 601, 845, 948, 817 }, }; worksheet.Range["E10:P12"].Value = RevData; worksheet.Range["E15:P16"].Value = CostData; worksheet.Range["E21:P26"].Value = OpeData; worksheet.Range["E31:P33"].Value = EarnData; //set formulars worksheet.Range["E13:P13"].Formula = "=SUM(E10:E12)"; worksheet.Range["E17:P17"].Formula = "=SUM(E15:E16)"; worksheet.Range["E19:P19"].Formula = "=E13-E17"; worksheet.Range["E27:P27"].Formula = "=SUM(E21:E26)"; worksheet.Range["E29:P29"].Formula = "=E19-E27"; worksheet.Range["E34:P34"].Formula = "=E29-E31+E32-E33"; worksheet.Range["E36:P36"].Formula = "=E34*0.17"; worksheet.Range["E38:P38"].Formula = "=E34-E36"; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set center, default is false. worksheet.PageSetup.CenterHorizontally = true; worksheet.PageSetup.CenterVertically = true; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //When XlsxOpenOptions.DoNotRecalculateAfterOpened means GrapeCity Documents for Excel will just read all the cached values without calculating again after //opening an Excel file. //Change the path to the real file path when open. XlsxOpenOptions options = new XlsxOpenOptions(); options.DoNotRecalculateAfterOpened = true; workbook.Open(this.CurrentDirectory + "source.xlsx", options); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.PageSetup.CenterFooter = "&P"; //Set first page number, default is p1. worksheet.PageSetup.FirstPageNumber = 3; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream("PageSetup Demo.xlsx"); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set margins, in points. worksheet.PageSetup.TopMargin = 36; worksheet.PageSetup.BottomMargin = 36; worksheet.PageSetup.RightMargin = 72; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream("PageSetup Print Area.xlsx"); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set print area & print titles worksheet.PageSetup.PrintArea = "$D$5:$G$10"; worksheet.PageSetup.PrintTitleRows = "$5:$10"; worksheet.PageSetup.PrintTitleColumns = "$D:$G"; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Load template file Simple invoice.xlsx from resource var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); var worksheet = workbook.ActiveSheet; // fill some new items worksheet.Range["E09:H09"].Value = new object[] { "DD1-001", "Item 3", 5.60, 12 }; worksheet.Range["E10:H10"].Value = new object[] { "DD2-001", "Item 3", 8.5, 14 }; worksheet.Range["E11:H11"].Value = new object[] { "DD3-001", "Item 3", 9.6, 16 }; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set sheet worksheet.PageSetup.PrintGridlines = true; worksheet.PageSetup.PrintHeadings = true; worksheet.PageSetup.BlackAndWhite = true; worksheet.PageSetup.PrintComments = PrintLocation.InPlace; worksheet.PageSetup.PrintErrors = PrintErrors.Dash; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream("PageSetup Demo.xlsx"); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set paper scaling //Method 1: Set percent scale worksheet.PageSetup.IsPercentScale = true; worksheet.PageSetup.Zoom = 150; //Or Method 2: Fit to page's wide & tall //worksheet.PageSetup.IsPercentScale = false; //worksheet.PageSetup.FitToPagesWide = 3; //worksheet.PageSetup.FitToPagesTall = 4; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream("PageSetup Demo.xlsx"); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set even page headerfooter worksheet.PageSetup.OddAndEvenPagesHeaderFooter = true; worksheet.PageSetup.EvenPage.CenterHeader.Text = "&T"; worksheet.PageSetup.EvenPage.RightFooter.Text = "&D"; //Set even page headerfooter's graphic worksheet.PageSetup.EvenPage.LeftFooter.Text = "&G"; System.IO.Stream stream = this.GetResourceStream("logo.png"); worksheet.PageSetup.EvenPage.LeftFooter.Picture.SetGraphicStream(stream, ImageType.PNG); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set page headerfooter worksheet.PageSetup.LeftHeader = "&\"Arial,Italic\"LeftHeader"; worksheet.PageSetup.CenterHeader = "&P"; //Set page headerfooter's graphic worksheet.PageSetup.CenterFooter = "&G"; System.IO.Stream stream = this.GetResourceStream("logo.png"); worksheet.PageSetup.CenterFooterPicture.SetGraphicStream(stream, Drawing.ImageType.PNG); //If you have picture resources locally, you can also set graphic in this way. //worksheet.PageSetup.CenterFooter = "&G"; //worksheet.PageSetup.CenterFooterPicture.Filename = @"C:\picture.png"; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; //Set first page headerfooter worksheet.PageSetup.DifferentFirstPageHeaderFooter = true; worksheet.PageSetup.FirstPage.CenterHeader.Text = "&T"; worksheet.PageSetup.FirstPage.RightFooter.Text = "&D"; //Set first page headerfooter's graphic worksheet.PageSetup.FirstPage.LeftFooter.Text = "&G"; System.IO.Stream stream = this.GetResourceStream("logo.png"); worksheet.PageSetup.FirstPage.LeftFooter.Picture.SetGraphicStream(stream, ImageType.PNG); worksheet.PageSetup.FirstPage.LeftFooter.Picture.Width = 100; worksheet.PageSetup.FirstPage.LeftFooter.Picture.Height = 13; }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //ToJson&FromJson can be used in combination with spread.sheets product:http://spread.grapecity.com/spreadjs/sheets/ //GrapeCity Documents for Excel import an excel file. //change the path to real source file path. string source = this.CurrentDirectory + "source.xlsx"; workbook.Open(source); //GrapeCity Documents for Excel export to a json string. var jsonstr = workbook.ToJson(); //use the json string to initialize spread.sheets product. //spread.sheets will show the excel file contents. //spread.sheets product export a json string. //GrapeCity Documents for Excel use the json string to initialize. workbook.FromJson(jsonstr); //GrapeCity Documents for Excel export workbook to an excel file. //change the path to real export file path. string export = this.CurrentDirectory + "export.xlsx"; workbook.Save(export); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Load template file Monthly business budget.xlsx from resource var fileStream = this.GetResourceStream("xlsx\\Blood sugar tracker.xlsx"); workbook.Open(fileStream); var worksheet = workbook.ActiveSheet; //insert 19 rows worksheet.Range["1:19"].Insert(); //Change the rows(2~5) RowHeight worksheet.Rows[1].RowHeight = 34.5; worksheet.Rows[2].RowHeight = 15.75; worksheet.Rows[3].RowHeight = 19.5; worksheet.Rows[4].RowHeight = 15.75; //Set values worksheet.Range["B2"].Value = "BLOOD SUGAR TRACKING"; worksheet.Range["B4"].Value = "CHARTED PROGRESS"; //Set Styles worksheet.Range["B2"].Font.ThemeFont = ThemeFont.Major; worksheet.Range["B2"].Font.Size = 26; worksheet.Range["B2"].Font.ThemeColor = ThemeColor.Dark1; worksheet.Range["B2"].Font.TintAndShade = 0.34998626667073579; worksheet.Range["B2:D2"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick; worksheet.Range["B2:D2"].Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent1; worksheet.Range["B4"].Font.ThemeFont = ThemeFont.Major; worksheet.Range["B4"].Font.Bold = true; worksheet.Range["B4"].Font.Size = 14; worksheet.Range["B4"].Font.ThemeColor = ThemeColor.Dark1; worksheet.Range["B4"].Font.TintAndShade = 0.34998626667073579; worksheet.Range["B4:D4"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick; worksheet.Range["B4:D4"].Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent2; //Add chart IShape shape = worksheet.Shapes.AddChart(ChartType.Line, 9.75, 100.5, 365, 203.25); shape.Name = "BloodSugarProgress"; //Add Series. ISeries series1 = shape.Chart.SeriesCollection.NewSeries(); series1.Formula = "=SERIES('BLOOD SUGAR DATA'!$C$23,'BLOOD SUGAR DATA'!$B$24:$B$45,'BLOOD SUGAR DATA'!$C$24:$C$45,1)"; series1.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent1; series1.Format.Line.Weight = 2.5; ISeries series2 = shape.Chart.SeriesCollection.NewSeries(); series2.Formula = "=SERIES('BLOOD SUGAR DATA'!$D$23,'BLOOD SUGAR DATA'!$B$24:$B$45,'BLOOD SUGAR DATA'!$D$24:$D$45,2)"; series2.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent2; series2.Format.Line.Weight = 2.5; //Hidden the chart title shape.Chart.HasTitle = false; //Hidden the category axis IAxis category_axis = shape.Chart.Axes.Item(AxisType.Category); category_axis.Visible = false; //Set value axis units IAxis value_axis = shape.Chart.Axes.Item(AxisType.Value); value_axis.MaximumScale = 140; value_axis.MinimumScale = 50; value_axis.MajorUnit = 10; value_axis.MinorUnit = 2; //Add a rectange shape IShape shape1 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 385, 84.75, 102, 218.25); 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 = "INFO:"; shape1_p1.Runs.Add(" Blood sugar levels will vary from person-to-person. There are many factors to keeping it within your normal range and isn't based on sugar alone. Consult a physician for additional information or follow-up."); ITextRange shape1_p2 = shape1.TextFrame.TextRange.Paragraphs.Add(""); ITextRange shape1_p3 = shape1.TextFrame.TextRange.Paragraphs.Add("More info can be found here:"); ITextRange shape1_p4 = shape1.TextFrame.TextRange.Paragraphs.Add("http://diabetes.webmd.com/blood-glucose"); 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_p1.Runs[0].Font.Bold = true; shape1_p3.Runs[0].Font.Bold = true; //Do table filter var table = worksheet.Tables[0]; table.Range.AutoFilter(1, ">=102"); }
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { //Load template file Financial KPIs.xlsx from resource var fileStream = this.GetTemplateStream(); workbook.Open(fileStream); var worksheet = workbook.ActiveSheet; //set values object[,] ProData = new object[, ] { { 1483550, 121386 }, { 0.4336, 0.32 }, { 0.1236, -0.0438 }, { 12.36, -0.0438 } }; object[] ProData1 = new object[] { 0.14, 0.0029, 0.0361, 0.0361 }; worksheet.Range["E7:F10"].Value = ProData; worksheet.Range["I7:I10"].Value = ProData1; object[,] ActiveData = new object[, ] { { "4.32 item", "2.00 item" }, { "72 days", "45 days" }, { "53 days", "55 days" } }; object[] ActiveData1 = new object[] { "0.45 items", "-5 days", "-6 days" }; worksheet.Range["E12:F14"].Value = ActiveData; worksheet.Range["I12:I14"].Value = ActiveData1; object[,] EffData = new object[, ] { { 0.3446, 0.25 }, { 0.5335, 0.11 } }; object[] EffData1 = new object[] { 0.1245, 0.1946 }; worksheet.Range["E16:F17"].Value = EffData; worksheet.Range["I16:I17"].Value = EffData1; object[,] LinData = new object[, ] { { "0.91:1", "'2:1" }, { "0.58:1", "'1:1" } }; object[] LinData1 = new object[] { "'0.02:1", "'0.03:1" }; worksheet.Range["E19:F20"].Value = LinData; worksheet.Range["I19:I20"].Value = LinData1; object[,] GeaData = new object[, ] { { -9.60, 0.85 }, { 0.68, 0.5 } }; object[] GeaData1 = new object[] { 6.65, 0.0282 }; worksheet.Range["E22:F23"].Value = GeaData; worksheet.Range["I22:I23"].Value = GeaData1; object[,] CasData = new object[, ] { { 0.0735, 1.2 }, { 0.1442, 0.1442 } }; object[] CasData1 = new object[] { -0.0046, 0.023 }; worksheet.Range["E25:F26"].Value = CasData; worksheet.Range["I25:I26"].Value = CasData1; }