App_Code/CellStylesDemo.cs
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <summary>
/// Shows the parts of a cell style: solid, pattern and gradient fills, text alignment and rotation, fonts and
/// borders. A style can be a named workbook style shared by many cells or the style of a single range.
/// </summary>
public static class CellStylesDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "CellStyles";
/// <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.</param>
public static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
{
ExcelWorkbook workbook = new ExcelWorkbook(format);
// workbook.LicenseKey = "your license key";
workbook.DocumentProperties.Subject = "Cell styles";
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";
Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
try
{
ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);
bool xlsx = format == ExcelWorkbookFormat.Xlsx_2007;
ExcelWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Cell Styles";
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Landscape);
DemoLayout.WriteTitle(worksheet, "A2:G3", "Cell Styles Demo", titleStyle);
worksheet["A1:G1"].ColumnWidthInChars = 15;
// A NAMED STYLE: every cell that uses it changes when the style changes
DemoLayout.WriteSection(worksheet, "A5:C5", "A named workbook style", sectionStyle);
ExcelCellStyle customStyle = workbook.Styles.AddStyle("CustomCellStyle");
customStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
customStyle.Font.Size = 14;
customStyle.Font.Bold = true;
// each border of a cell can have its own color and line style
customStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Medium;
customStyle.Borders[ExcelCellBorderIndex.Top].Color = Color.Green;
customStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Medium;
customStyle.Borders[ExcelCellBorderIndex.Bottom].Color = Color.Blue;
customStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Medium;
customStyle.Borders[ExcelCellBorderIndex.Left].Color = Color.Red;
customStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Medium;
customStyle.Borders[ExcelCellBorderIndex.Right].Color = Color.Orange;
customStyle.Borders[ExcelCellBorderIndex.DiagonalDown].LineStyle = ExcelCellLineStyle.Thin;
customStyle.Borders[ExcelCellBorderIndex.DiagonalDown].Color = Color.Orange;
if (xlsx)
{
customStyle.Fill.FillType = ExcelCellFillType.GradientFill;
customStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204);
customStyle.Fill.GradientFillOptions.Color2 = Color.Orange;
}
else
{
customStyle.Fill.FillType = ExcelCellFillType.SolidFill;
customStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
}
worksheet["A7:G7"].Style = customStyle;
worksheet["A7:G7"].Value = "Styled";
// FILLS
DemoLayout.WriteSection(worksheet, "A9:C9", "Solid fill", sectionStyle);
worksheet["A11:G11"].Style.Fill.FillType = ExcelCellFillType.SolidFill;
worksheet["A11:G11"].Style.Fill.SolidFillOptions.BackColor = Color.Orange;
DemoLayout.WriteSection(worksheet, "A13:C13", "Pattern fills", sectionStyle);
ExcelCellFillPatternType[] patterns = { ExcelCellFillPatternType.Gray_12_5_Percent,
ExcelCellFillPatternType.DiagonalCrosshatch, ExcelCellFillPatternType.HorizontalStripe };
for (int i = 0; i < patterns.Length; i++)
{
ExcelRange patternRow = worksheet[15 + i, 1, 15 + i, 7];
patternRow.Style.Fill.FillType = ExcelCellFillType.PatternFill;
patternRow.Style.Fill.PatternFillOptions.PatternType = patterns[i];
patternRow.Style.Fill.PatternFillOptions.BackColor = Color.FromArgb(255, 255, 204);
patternRow.Style.Fill.PatternFillOptions.PatternColor = Color.Orange;
worksheet[15 + i, 1].Value = patterns[i].ToString();
}
// ALIGNMENT AND ROTATION
DemoLayout.WriteSection(worksheet, "A19:C19", "Text alignment and rotation", sectionStyle);
ExcelRange alignmentRow = worksheet["A21:G21"];
alignmentRow.RowHeightInPoints = 75;
alignmentRow.Style.Borders.LineStyle = ExcelCellLineStyle.Medium;
alignmentRow.Style.Borders.Color = Color.DarkBlue;
alignmentRow.Style.Font.Bold = true;
if (xlsx)
{
alignmentRow.Style.Fill.FillType = ExcelCellFillType.GradientFill;
alignmentRow.Style.Fill.GradientFillOptions.Direction = ExcelGradientDirection.DiagonlDown;
alignmentRow.Style.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204);
alignmentRow.Style.Fill.GradientFillOptions.Color2 = Color.Orange;
}
else
{
alignmentRow.Style.Fill.FillType = ExcelCellFillType.SolidFill;
alignmentRow.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
}
SetAlignedText(worksheet["A21"], "Top Left", ExcelCellHorizontalAlignmentType.Left, ExcelCellVerticalAlignmentType.Top, 0, Color.Red);
SetAlignedText(worksheet["B21"], "Bottom Right", ExcelCellHorizontalAlignmentType.Right, ExcelCellVerticalAlignmentType.Bottom, 0, Color.Green);
SetAlignedText(worksheet["C21"], "Center", ExcelCellHorizontalAlignmentType.Center, ExcelCellVerticalAlignmentType.Center, 0, Color.Blue);
// the orientation is the text rotation in degrees, from -90 to 90
SetAlignedText(worksheet["D21"], "Up 90", ExcelCellHorizontalAlignmentType.Center, ExcelCellVerticalAlignmentType.Center, 90, Color.Orange);
SetAlignedText(worksheet["E21"], "Down 90", ExcelCellHorizontalAlignmentType.Center, ExcelCellVerticalAlignmentType.Top, -90, Color.Red);
SetAlignedText(worksheet["F21"], "Up 45", ExcelCellHorizontalAlignmentType.Center, ExcelCellVerticalAlignmentType.Center, 45, Color.Green);
worksheet["G21"].Style.Alignment.WrapText = true;
SetAlignedText(worksheet["G21"], "A longer text wrapped in the cell", ExcelCellHorizontalAlignmentType.Left, ExcelCellVerticalAlignmentType.Top, 0, Color.Blue);
// FONTS
DemoLayout.WriteSection(worksheet, "A23:C23", "Fonts", sectionStyle);
worksheet["A25:G25"].RowHeightInPoints = 30;
worksheet["A25:G25"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
worksheet["A25:G25"].Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
worksheet["A25:G25"].Value = "Hello";
worksheet["A25"].Style.Font.Size = 14;
worksheet["A25"].Style.Font.Color = Color.Green;
worksheet["B25"].Style.Font.Name = "Verdana";
worksheet["B25"].Style.Font.Size = 16;
worksheet["B25"].Style.Font.Color = Color.Blue;
worksheet["C25"].Style.Font.Name = "Times New Roman";
worksheet["C25"].Style.Font.Italic = true;
worksheet["C25"].Style.Font.Size = 16;
worksheet["C25"].Style.Font.Color = Color.Red;
worksheet["D25"].Style.Font.Bold = true;
worksheet["D25"].Style.Font.UnderlineType = ExcelCellUnderlineType.Double;
worksheet["E25"].Style.Font.UnderlineType = ExcelCellUnderlineType.DoubleAccounting;
worksheet["F25"].Style.Font.IsStrikethrough = true;
worksheet["F25"].Style.Font.Color = Color.Orange;
worksheet["G25"].Value = "x2";
worksheet["G25"].Style.Font.IsSuperscript = true;
// BORDERS: one line style for all the borders, or a line style for each border
DemoLayout.WriteSection(worksheet, "A27:C27", "Borders", sectionStyle);
worksheet["A29:G29"].RowHeightInPoints = 40;
ExcelCellLineStyle[] lineStyles = { ExcelCellLineStyle.Thin, ExcelCellLineStyle.Medium,
ExcelCellLineStyle.Thick, ExcelCellLineStyle.Double, ExcelCellLineStyle.Dot,
ExcelCellLineStyle.DashDot, ExcelCellLineStyle.MediumDashDot };
Color[] lineColors = { Color.Green, Color.DarkBlue, Color.Orange, Color.Green, Color.Blue, Color.Red, Color.Red };
for (int i = 0; i < lineStyles.Length; i++)
{
ExcelRange cell = worksheet[29, 1 + i];
cell.Style.Borders.LineStyle = lineStyles[i];
cell.Style.Borders.Color = lineColors[i];
cell.Value = lineStyles[i].ToString();
cell.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
cell.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
}
worksheet["E29"].Style.Borders[ExcelCellBorderIndex.DiagonalUp].LineStyle = ExcelCellLineStyle.Dot;
worksheet["E29"].Style.Borders[ExcelCellBorderIndex.DiagonalUp].Color = Color.Blue;
workbook.Save(output);
}
finally
{
workbook.Close();
logo.Dispose();
}
}
private static void SetAlignedText(ExcelRange cell, string text, ExcelCellHorizontalAlignmentType horizontal,
ExcelCellVerticalAlignmentType vertical, int orientation, Color color)
{
cell.Value = text;
cell.Style.Alignment.HorizontalAlignment = horizontal;
cell.Style.Alignment.VerticalAlignment = vertical;
cell.Style.Alignment.Orientation = orientation;
cell.Style.Font.Color = color;
}
}
}