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