using System; using System.Data; using System.Drawing; using GemBox.Spreadsheet; namespace Samples { /// <summary> /// Feature samples. /// </summary> class FeatureSamplesCS { [STAThread] static void Main(string[] args) { // TODO: If using GemBox.Spreadsheet Professional, put your serial key below. // Otherwise, if you are using GemBox.Spreadsheet Free, comment out the // following line (Free version doesn't have SetLicense method). // SpreadsheetInfo.SetLicense("YOUR-SERIAL-KEY-HERE"); ExcelFile excelFile = new ExcelFile(); ExcelWorksheetCollection worksheets = excelFile.Worksheets; // Each static method modifies provided worksheet with one category of features. ValuesSample(worksheets.Add("Values")); StylesSample(worksheets.Add("Styles")); ReferencingAndGroupsSample(worksheets.Add("ReferencingAndGroups")); worksheets.ActiveWorksheet = worksheets["Styles"]; string fileName = "FeatureSamples.xls"; excelFile.SaveXls(fileName); // Uncomment if you want to export in XLSX. //string fileName = "FeatureSamples.xlsx"; //excelFile.SaveXlsx(fileName); // Uncomment if you want to export in ODS. //string fileName = "FeatureSamples.ods"; //excelFile.SaveOds(fileName); TryToDisplayGeneratedFile(fileName); } static void ValuesSample(ExcelWorksheet ws) { ws.Cells[0, 0].Value = "Cell value examples:"; // Column width of 25 and 40 characters. ws.Columns[0].Width = 25 * 256; ws.Columns[1].Width = 40 * 256; int row = 1; ws.Cells[++row, 0].Value = "Type"; ws.Cells[row, 1].Value = "Value"; ws.Cells[++row, 0].Value = "System.DBNull:"; ws.Cells[row, 1].Value = System.DBNull.Value; ws.Cells[++row, 0].Value = "System.Byte:"; ws.Cells[row, 1].Value = byte.MaxValue; ws.Cells[++row, 0].Value = "System.SByte:"; ws.Cells[row, 1].Value = sbyte.MinValue; ws.Cells[++row, 0].Value = "System.Int16:"; ws.Cells[row, 1].Value = short.MinValue; ws.Cells[++row, 0].Value = "System.UInt16:"; ws.Cells[row, 1].Value = ushort.MaxValue; ws.Cells[++row, 0].Value = "System.Int64:"; ws.Cells[row, 1].Value = long.MinValue; ws.Cells[++row, 0].Value = "System.UInt64:"; ws.Cells[row, 1].Value = ulong.MaxValue; ws.Cells[++row, 0].Value = "System.UInt32:"; ws.Cells[row, 1].Value = (uint)1234; ws.Cells[++row, 0].Value = "System.Int32:"; ws.Cells[row, 1].Value = (int)-5678; ws.Cells[++row, 0].Value = "System.Single:"; ws.Cells[row, 1].Value = float.MaxValue; ws.Cells[++row, 0].Value = "System.Double:"; ws.Cells[row, 1].Value = double.MaxValue; ws.Cells[++row, 0].Value = "System.Boolean:"; ws.Cells[row, 1].Value = true; ws.Cells[++row, 0].Value = "System.Char:"; ws.Cells[row, 1].Value = 'a'; ws.Cells[++row, 0].Value = "System.Text.StringBuilder:"; ws.Cells[row, 1].Value = new System.Text.StringBuilder("StringBuilder text."); ws.Cells[++row, 0].Value = "System.Decimal:"; ws.Cells[row, 1].Value = new decimal(50000); ws.Cells[++row, 0].Value = "System.DateTime:"; ws.Cells[row, 1].Value = DateTime.Now; ws.Cells[++row, 0].Value = "System.String:"; ws.Cells[row++, 1].Value = "Microsoft Excel is a spreadsheet program written and distributed by Microsoft" + " for computers using the Microsoft Windows operating system and Apple Macintosh" + " computers. It is overwhelmingly the dominant spreadsheet application available" + " for these platforms and has been so since version 5 1993 and its bundling as" + " part of Microsoft Office.\n\nFor more information see:" + " http://en.wikipedia.org/wiki/Microsoft_Excel"; ws.Cells[++row, 0].Value = "DataTable insert example:"; ws.InsertDataTable(FeatureSamplesCS.CreateData(), row, 2, true); } static DataTable CreateData() { DataTable dt = new DataTable(); dt.Columns.Add("ID", typeof(int)); dt.Columns.Add("FirstName", typeof(string)); dt.Columns.Add("LastName", typeof(string)); dt.Rows.Add(new object[] { 100, "John", "Doe" }); dt.Rows.Add(new object[] { 101, "Fred", "Nurk" }); dt.Rows.Add(new object[] { 103, "Hans", "Meier" }); dt.Rows.Add(new object[] { 104, "Ivan", "Horvat" }); dt.Rows.Add(new object[] { 105, "Jean", "Dupont" }); dt.Rows.Add(new object[] { 106, "Mario", "Rossi" }); return dt; } static void StylesSample(ExcelWorksheet ws) { ws.Cells[0, 0].Value = "Cell style examples:"; int row = 0; // Column width of 4, 30 and 35 characters. ws.Columns[0].Width = 4 * 256; ws.Columns[1].Width = 30 * 256; ws.Columns[2].Width = 35 * 256; ws.Cells[row += 2, 1].Value = ".Style.Borders.SetBorders(...)"; ws.Cells[row, 2].Style.Borders.SetBorders(MultipleBorders.All, Color.FromArgb(252, 1, 1), LineStyle.Thin); ws.Cells[row += 2, 1].Value = ".Style.FillPattern.SetPattern(...)"; ws.Cells[row, 2].Style.FillPattern.SetPattern(FillPatternStyle.ThinHorizontalCrosshatch, Color.Green, Color.Yellow); ws.Cells[row += 2, 1].Value = ".Style.Font.Color ="; ws.Cells[row, 2].Value = "Color.Blue"; ws.Cells[row, 2].Style.Font.Color = Color.Blue; ws.Cells[row += 2, 1].Value = ".Style.Font.Italic ="; ws.Cells[row, 2].Value = "true"; ws.Cells[row, 2].Style.Font.Italic = true; ws.Cells[row += 2, 1].Value = ".Style.Font.Name ="; ws.Cells[row, 2].Value = "Comic Sans MS"; ws.Cells[row, 2].Style.Font.Name = "Comic Sans MS"; ws.Cells[row += 2, 1].Value = ".Style.Font.ScriptPosition ="; ws.Cells[row, 2].Value = "ScriptPosition.Superscript"; ws.Cells[row, 2].Style.Font.ScriptPosition = ScriptPosition.Superscript; ws.Cells[row += 2, 1].Value = ".Style.Font.Size ="; ws.Cells[row, 2].Value = "18 * 20"; ws.Cells[row, 2].Style.Font.Size = 18 * 20; ws.Cells[row += 2, 1].Value = ".Style.Font.Strikeout ="; ws.Cells[row, 2].Value = "true"; ws.Cells[row, 2].Style.Font.Strikeout = true; ws.Cells[row += 2, 1].Value = ".Style.Font.UnderlineStyle ="; ws.Cells[row, 2].Value = "UnderlineStyle.Double"; ws.Cells[row, 2].Style.Font.UnderlineStyle = UnderlineStyle.Double; ws.Cells[row += 2, 1].Value = ".Style.Font.Weight ="; ws.Cells[row, 2].Value = "ExcelFont.BoldWeight"; ws.Cells[row, 2].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells[row += 2, 1].Value = ".Style.HorizontalAlignment ="; ws.Cells[row, 2].Value = "HorizontalAlignmentStyle.Center"; ws.Cells[row, 2].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells[row += 2, 1].Value = ".Style.Indent"; ws.Cells[row, 2].Value = "five"; ws.Cells[row, 2].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left; ws.Cells[row, 2].Style.Indent = 5; ws.Cells[row += 2, 1].Value = ".Style.IsTextVertical = "; ws.Cells[row, 2].Value = "true"; // Set row height to 50 points. ws.Rows[row].Height = 50 * 20; ws.Cells[row, 2].Style.IsTextVertical = true; ws.Cells[row += 2, 1].Value = ".Style.NumberFormat"; ws.Cells[row, 2].Value = 1234; ws.Cells[row, 2].Style.NumberFormat = "#.##0,00 [$Krakozhian Money Units]"; ws.Cells[row += 2, 1].Value = ".Style.Rotation"; ws.Cells[row, 2].Value = "35 degrees up"; ws.Cells[row, 2].Style.Rotation = 35; ws.Cells[row += 2, 1].Value = ".Style.ShrinkToFit"; ws.Cells[row, 2].Value = "This property is set to true so this text appears shrunk."; ws.Cells[row, 2].Style.ShrinkToFit = true; ws.Cells[row += 2, 1].Value = ".Style.VerticalAlignment ="; ws.Cells[row, 2].Value = "VerticalAlignmentStyle.Top"; // Set row height to 30 points. ws.Rows[row].Height = 30 * 20; ws.Cells[row, 2].Style.VerticalAlignment = VerticalAlignmentStyle.Top; ws.Cells[row += 2, 1].Value = ".Style.WrapText"; ws.Cells[row, 2].Value = "This property is set to true so this text appears broken into multiple lines."; ws.Cells[row, 2].Style.WrapText = true; } static void ReferencingAndGroupsSample(ExcelWorksheet ws) { ws.Cells[0].Value = "Cell referencing and grouping examples:"; ws.Cells["B2"].Value = "Cell B2."; ws.Cells[6, 0].Value = "Cell in row 7 and column A."; ws.Rows[2].Cells[0].Value = "Cell in row 3 and column A."; ws.Rows["4"].Cells["B"].Value = "Cell in row 4 and column B."; ws.Columns[2].Cells[4].Value = "Cell in column C and row 5."; ws.Columns["AA"].Cells["6"].Value = "Cell in AA column and row 6."; CellRange cr = ws.Rows[7].Cells; cr[0].Value = cr.IndexingMode; cr[3].Value = "D8"; cr["B"].Value = "B8"; cr = ws.Columns[7].Cells; cr[0].Value = cr.IndexingMode; cr[2].Value = "H3"; cr["5"].Value = "H5"; cr = ws.Cells.GetSubrange("I2", "L8"); cr.SetBorders(MultipleBorders.Outside, Color.Navy, LineStyle.Dashed); cr["J7"].Value = cr.IndexingMode; cr[0, 0].Value = "I2"; cr["J3"].Value = "J3"; cr[4].Value = "I3"; // Cell range width is 4 (I J K L). // Vertical grouping. ws.Cells[12, 0].Value = "GroupA Start"; ws.Rows[12].OutlineLevel = 1; ws.Cells[13, 0].Value = "A"; ws.Rows[13].OutlineLevel = 1; ws.Cells[14, 1].Value = "GroupB Start"; ws.Rows[14].OutlineLevel = 2; ws.Cells[15, 1].Value = "B"; ws.Rows[15].OutlineLevel = 2; ws.Cells[16, 1].Value = "GroupB End"; ws.Rows[16].OutlineLevel = 2; ws.Cells[17, 0].Value = "GroupA End"; ws.Rows[17].OutlineLevel = 1; // Put outline row buttons above groups. ws.ViewOptions.OutlineRowButtonsBelow = false; // Horizontal grouping (collapsed). ws.Cells["E12"].Value = "Gr.C Start"; ws.Columns["E"].OutlineLevel = 1; ws.Columns["E"].Hidden = true; ws.Cells["F12"].Value = "C"; ws.Columns["F"].OutlineLevel = 1; ws.Columns["F"].Hidden = true; ws.Cells["G12"].Value = "Gr.C End"; ws.Columns["G"].OutlineLevel = 1; ws.Columns["G"].Hidden = true; ws.Columns["H"].Collapsed = true; } static void TryToDisplayGeneratedFile(string fileName) { try { System.Diagnostics.Process.Start(fileName); } catch (Exception) { Console.WriteLine(fileName + " created in application folder."); } } } }