ExpertXls - Full Code Sample - Generate Excel Spreadsheets from scratch using a C#/VB.NET Library |
The code below is the Getting Started demo of the samples (Features\GettingStartedDemo.cs and GettingStartedDemo.vb). It creates a workbook from scratch in the Excel 97-2003 or Excel 2007 or later format: named cell styles, page setup with a header and footer, merged cells, pictures, a date value with a number format, a second worksheet with a table, formulas and a chart, a named range, a hyperlink to that range and a hyperlink to a web page. The workbook is saved to a stream: a file, a memory stream or an HTTP response.
Call it with the workbook format, the folder that contains the Images folder (logo.jpg and the flag images) and the output stream:
using System; using System.Drawing; using System.IO; using ExpertXls.ExcelLib; namespace ExcelLibraryDemos { /// <summary> /// Getting started: creates a workbook from scratch with named styles, page setup, merged cells, pictures, a /// formula, a chart, a named range and hyperlinks. This demo writes everything inline; the other demos reuse the /// page setup and title code from DemoLayout. /// </summary> public static class GettingStartedDemo { /// <summary>The name of the generated file, without extension.</summary> public const string OutputName = "GettingStarted"; // the greetings shown in the first worksheet, with the flag image of each language and the text color private static readonly string[] Greetings = { "Hello World !!!", "Bonjour tout le monde !!!", "Hallo Welt", "Hola Mundo", "Привет мир", "Ciao a tutti", "Hallo Wereld", "Olá Mundo" }; private static readonly string[] Languages = { "English", "French", "German", "Spanish", "Russian", "Italian", "Dutch", "Portuguese" }; private static readonly string[] FlagImages = { "us.png", "fr.png", "de.png", "es.png", "ru.png", "it.png", "nl.png", "pt.png" }; private static readonly Color[] TextColors = { Color.Black, Color.Blue, Color.Red, Color.Orange, Color.Indigo, Color.Green, Color.Blue, Color.Red }; /// <summary> /// Creates the workbook in the given format and saves it to the output stream. /// </summary> /// <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param> /// <param name="inputFolder">The folder that contains the Images folder of the sample.</param> /// <param name="output">The stream that receives the workbook: a file, a memory stream or an HTTP response.</param> public static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output) { string imagesFolder = Path.Combine(inputFolder, "Images"); // a new workbook has one empty worksheet ExcelWorkbook workbook = new ExcelWorkbook(format); // set the license key before you save the workbook, otherwise it is created in evaluation mode // workbook.LicenseKey = "your license key"; // the document properties are shown by Excel in File > Info workbook.DocumentProperties.Title = "Getting started"; workbook.DocumentProperties.Subject = "Getting started with ExpertXls"; workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET"; // the images must stay alive until the workbook is saved Image logo = Image.FromFile(Path.Combine(imagesFolder, "logo.jpg")); Image[] flags = new Image[FlagImages.Length]; try { // NAMED STYLES: defined once, applied to many cells ExcelCellStyle titleStyle = workbook.Styles.AddStyle("WorksheetTitleStyle"); titleStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center; titleStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; titleStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Medium; titleStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Medium; titleStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Medium; titleStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Medium; titleStyle.Borders.Color = Color.Green; titleStyle.Font.Size = 14; titleStyle.Font.Bold = true; titleStyle.Font.UnderlineType = ExcelCellUnderlineType.Single; if (format == ExcelWorkbookFormat.Xls_2003) { // the Excel 97-2003 format has no gradient cell fills titleStyle.Fill.FillType = ExcelCellFillType.SolidFill; titleStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204); } else { titleStyle.Fill.FillType = ExcelCellFillType.GradientFill; titleStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204); titleStyle.Fill.GradientFillOptions.Color2 = Color.White; } ExcelCellStyle indexStyle = workbook.Styles.AddStyle("IndexColumnStyle"); indexStyle.Font.Size = 12; indexStyle.Fill.FillType = ExcelCellFillType.SolidFill; indexStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204); indexStyle.Borders.LineStyle = ExcelCellLineStyle.Thin; ExcelCellStyle languageStyle = workbook.Styles.AddStyle("LanguageNameStyle"); languageStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; languageStyle.Font.Size = 12; languageStyle.Font.Bold = true; languageStyle.Borders.LineStyle = ExcelCellLineStyle.Dot; languageStyle.Fill.FillType = ExcelCellFillType.PatternFill; languageStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_12_5_Percent; languageStyle.Fill.PatternFillOptions.BackColor = Color.White; languageStyle.Fill.PatternFillOptions.PatternColor = Color.Green; ExcelCellStyle greetingStyle = workbook.Styles.AddStyle("GreetingStyle"); greetingStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; greetingStyle.Font.Size = 14; greetingStyle.Font.Bold = true; greetingStyle.Borders.LineStyle = ExcelCellLineStyle.Hair; if (format == ExcelWorkbookFormat.Xls_2003) { greetingStyle.Fill.FillType = ExcelCellFillType.PatternFill; greetingStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent; greetingStyle.Fill.PatternFillOptions.BackColor = Color.White; greetingStyle.Fill.PatternFillOptions.PatternColor = Color.Orange; } else { greetingStyle.Fill.FillType = ExcelCellFillType.GradientFill; greetingStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.Vertical; greetingStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 204, 153); greetingStyle.Fill.GradientFillOptions.Color2 = Color.White; } // FIRST WORKSHEET: "Hello World" in several languages ExcelWorksheet worksheet = workbook.Worksheets[0]; worksheet.Name = "Hello World"; // the page setup is used when the worksheet is printed; the header and footer use the Excel codes // &G picture, &A sheet name, &F file name, &P page number, &D date worksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4; worksheet.PageSetup.Orientation = ExcelPageOrientation.Portrait; worksheet.PageSetup.LeftMargin = 1; worksheet.PageSetup.RightMargin = 1; worksheet.PageSetup.TopMargin = 1; worksheet.PageSetup.BottomMargin = 1; worksheet.PageSetup.LeftHeaderFormat = "&G"; worksheet.PageSetup.LeftHeaderPicture = logo; worksheet.PageSetup.RightHeaderFormat = "&A"; worksheet.PageSetup.LeftFooterFormat = "&F"; worksheet.PageSetup.CenterFooterFormat = "&P"; worksheet.PageSetup.RightFooterFormat = "&D"; // a merged range shows one value, stored in its top left cell ExcelRange titleRange = worksheet["A2:G3"]; titleRange.Merge(); worksheet["A2"].Text = "Say 'Hello World' in Different Languages"; titleRange.RowHeightInPoints = 18; titleRange.Style = titleStyle; // narrow separator column B and flag column F; the column indexes are one based worksheet.SetColumnWidthInChars(2, 3); worksheet.SetColumnWidthInChars(6, 5); for (int i = 0; i < Greetings.Length; i++) { int row = 5 + 2 * i; worksheet[row, 1].Value = i + 1; worksheet[row, 1].Style = indexStyle; worksheet[row, 3].Text = Greetings[i]; worksheet[row, 3].Style = greetingStyle; // changing the style of one cell does not change the named style used by the other cells worksheet[row, 3].Style.Font.Color = TextColors[i]; worksheet[row, 3].RowHeightInPoints = 19.5; // the picture is placed with its top left corner in column F of this row flags[i] = Image.FromFile(Path.Combine(imagesFolder, FlagImages[i])); worksheet.Pictures.AddPicture(6, row, flags[i]); worksheet[row, 6, row, 7].StyleName = "LanguageNameStyle"; worksheet[row, 7].Text = Languages[i]; } // fit the columns to their content worksheet.AutofitColumn(1); worksheet.AutofitColumn(3); worksheet.AutofitColumn(7); // a date value is shown with the number format of its cell worksheet["A28:C28"].Merge(); worksheet["A28"].Text = "Workbook Creation Date & Time:"; worksheet["A28"].Style.Font.Bold = true; worksheet["D28:G28"].Merge(); worksheet["D28"].Value = DateTime.Now; worksheet["D28:G28"].Style.Number.NumberFormatString = "mm/dd/yyyy hh:mm:ss"; worksheet["D28:G28"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left; worksheet["D28:G28"].Style.Font.Color = Color.DarkBlue; worksheet["D28:G28"].Style.Font.Bold = true; // SECOND WORKSHEET: a table of values, a formula and a chart ExcelWorksheet chartWorksheet = workbook.Worksheets.AddWorksheet("Simple Chart"); chartWorksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4; chartWorksheet.PageSetup.Orientation = ExcelPageOrientation.Landscape; chartWorksheet.PageSetup.LeftHeaderFormat = "&G"; chartWorksheet.PageSetup.LeftHeaderPicture = logo; chartWorksheet.PageSetup.RightHeaderFormat = "&A"; ExcelRange chartTitleRange = chartWorksheet["A2:G3"]; chartTitleRange.Merge(); chartWorksheet["A2"].Text = "Simple Chart Demo"; chartTitleRange.RowHeightInPoints = 18; chartTitleRange.Style = titleStyle; // the first row of the table holds the category names and the first column the series names string[] products = { "HTML to PDF", "PDF Merge", "PDF Security", "Web Chart", "Excel Library" }; string[] quarters = { "First Quarter", "Second Quarter", "Third Quarter", "Fourth Quarter" }; int[,] unitsSold = { { 1000, 500, 200, 400, 800 }, { 850, 680, 350, 230, 640 }, { 950, 450, 175, 350, 520 }, { 500, 700, 250, 460, 320 } }; for (int p = 0; p < products.Length; p++) { chartWorksheet[7, 3 + p].Text = products[p]; for (int q = 0; q < quarters.Length; q++) chartWorksheet[8 + q, 3 + p].Value = unitsSold[q, p]; // the formula is saved in the workbook and calculated by Excel when the file is opened char column = (char)('C' + p); chartWorksheet[12, 3 + p].Formula = String.Format("=SUM({0}8:{0}11)", column); } for (int q = 0; q < quarters.Length; q++) chartWorksheet[8 + q, 2].Text = quarters[q]; chartWorksheet["B12"].Text = "Yearly Total"; chartWorksheet["B7:G7"].Style.Font.Bold = true; chartWorksheet["B7:G7"].ColumnWidthInChars = 16; chartWorksheet["B7:G7"].Style.Fill.FillType = ExcelCellFillType.SolidFill; chartWorksheet["B7:G7"].Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153); chartWorksheet["B8:B12"].Style.Font.Bold = true; chartWorksheet["C12:G12"].Style.Font.Bold = true; chartWorksheet["C12:G12"].Style.Font.Color = Color.Blue; // the series of the chart are created from the rows of the data source range ExcelChart chart = chartWorksheet.Charts.AddChart(ExcelChartType.ColumnClustered, chartWorksheet["B7:G12"], true, 2, 15, 8, 32); chart.Title.Text = "Product Units Sold per Quarter"; chart.Title.Font.Size = 12; chart.Title.Font.Color = Color.DarkBlue; chart.ValueAxis.Title.Text = "Units sold"; chart.CategoryAxis.Title.Text = "Analyzed products"; if (format == ExcelWorkbookFormat.Xlsx_2007) { // preset gradients for shapes are an Excel 2007+ feature chart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill; chart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset; chart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat; } // show the value of each column of the "Yearly Total" series chart.Series["Yearly Total"].DataPoints.All.Label.ContainsValue = true; // NAMED RANGE AND HYPERLINKS // a hyperlink to a place in the workbook targets a named range workbook.NamedRanges.AddNamedRange(chartWorksheet["A1"], "ChartWorksheet"); worksheet["A23:C23"].Merge(); ExcelHyperlink sheetLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Range, worksheet["A23"], "ChartWorksheet"); sheetLink.Text = "Go to the chart worksheet"; sheetLink.ToolTip = "Go to the chart worksheet"; worksheet["A25:C25"].Merge(); ExcelHyperlink webLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, worksheet["A25"], "https://www.html-to-pdf.info/excel-library.aspx"); webLink.Text = "Visit the ExpertXls website"; webLink.ToolTip = "https://www.html-to-pdf.info/excel-library.aspx"; // SAVE: the format of the output is the format the workbook was created with workbook.Save(output); } finally { // Close releases the resources of the workbook; the images can be disposed after the save workbook.Close(); logo.Dispose(); foreach (Image flag in flags) { if (flag != null) flag.Dispose(); } } } } }