C# code of the GettingStarted demo
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();
}
}
}
}
}
Create, edit and read Excel workbooks in a Blazor application: the workbooks are created on the server and downloaded by the browser. The code of each workbook is in the Features folder.