void GenerateTotalRow(IXlSheet sheet, int firstDataRowIndex)
{
// Skip one row before starting to generate the total row.
sheet.SkipRows(1);
// Create the total row.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height to 28 pixels.
row.HeightInPixels = 28;
// Set font characteristics for the row cells.
row.ApplyFormatting(infoFont.Clone());
row.Formatting.Font.Bold = true;
// Skip six successive cells in the total row.
row.SkipCells(6);
// Create the "Total" cell.
using (IXlCell cell = row.CreateCell())
cell.Value = "TOTAL";
// Create the cell that displays the total amount.
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the total amount.
cell.SetFormula(string.Format("SUM(H{0}:H{1})", firstDataRowIndex + 1, row.RowIndex - 1));
// Set the cell background color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(217, 217, 217)));
}
// Create the empty cell.
using (IXlCell cell = row.CreateCell())
// Set the cell background color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(217, 217, 217)));
}
}
void GenerateTitle(IXlSheet sheet)
{
// Specify formatting settings for the document title.
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
formatting.Font.Name = "Calibri Light";
formatting.Font.SchemeStyle = XlFontSchemeStyles.None;
formatting.Font.Size = 24;
formatting.Font.Color = XlColor.FromTheme(XlThemeColor.Dark1, 0.35);
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.35);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Add the document title.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "SALES 2014";
cell.Formatting = formatting;
}
// Create four empty cells with the title formatting applied.
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell())
cell.Formatting = formatting;
}
}
// Skip one row before starting to generate data rows.
sheet.SkipRows(1);
}
void GenerateInfoRow(IXlSheet sheet, string info)
{
// Skip one row before starting to generate the row with additional information.
sheet.SkipRows(1);
// Create the row.
using (IXlRow row = sheet.CreateRow()) {
// Skip the first cell in the row.
row.SkipCells(1);
// Create the cell that contains the invoice payment options and set its font attributes.
using (IXlCell cell = row.CreateCell()) {
cell.Value = info;
cell.ApplyFormatting(infoFont);
}
}
}
static void PageSetup(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
#region #PageSetup
// Specify page settings for the worksheet.
sheet.PageSetup = new XlPageSetup();
// Select the paper size.
sheet.PageSetup.PaperKind = System.Drawing.Printing.PaperKind.A4;
// Set the page orientation to Landscape.
sheet.PageSetup.PageOrientation = XlPageOrientation.Landscape;
// Scale the print area to fit to one page wide.
sheet.PageSetup.FitToPage = true;
sheet.PageSetup.FitToWidth = 1;
sheet.PageSetup.FitToHeight = 0;
// Print in black and white.
sheet.PageSetup.BlackAndWhite = true;
// Specify the number of copies.
sheet.PageSetup.Copies = 2;
#endregion #PageSetup
// Generate data for the document.
sheet.SkipRows(1);
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(1);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Invoke the Page Setup dialog to control page settings.";
}
}
}
}
}
static void Borders(Stream stream, XlDocumentFormat documentFormat)
{
#region #Borders
// Specify a two-dimensional array that stores possible line styles for a border.
XlBorderLineStyle[,] lineStyles = new XlBorderLineStyle[, ] {
{ XlBorderLineStyle.Thin, XlBorderLineStyle.Medium, XlBorderLineStyle.Thick, XlBorderLineStyle.Double },
{ XlBorderLineStyle.Dotted, XlBorderLineStyle.Dashed, XlBorderLineStyle.DashDot, XlBorderLineStyle.DashDotDot },
{ XlBorderLineStyle.SlantDashDot, XlBorderLineStyle.MediumDashed, XlBorderLineStyle.MediumDashDot, XlBorderLineStyle.MediumDashDotDot }
};
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
for (int i = 0; i < 3; i++)
{
sheet.SkipRows(1);
// Create a worksheet row.
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
row.SkipCells(1);
// Create a new cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set outside borders for the created cell using a particular line style from the lineStyles array.
cell.ApplyFormatting(XlBorder.OutlineBorders(Color.SeaGreen, lineStyles[i, j]));
}
}
}
}
}
}
#endregion #Borders
}
void GenerateTitle(IXlSheet sheet)
{
// Specify formatting settings for the document title.
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
formatting.Font.Name = "Calibri Light";
formatting.Font.SchemeStyle = XlFontSchemeStyles.None;
formatting.Font.Size = 24;
formatting.Font.Color = XlColor.FromTheme(XlThemeColor.Dark1, 0.5);
formatting.Border = new XlBorder();
formatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.5);
formatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Add the document title.
using (IXlRow row = sheet.CreateRow()) {
// Skip the cell "A1".
row.SkipCells(1);
// Create the cell "B1" containing the document title.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "SALES ANALYSIS 2014";
cell.Formatting = formatting;
}
// Create five empty cells with the title formatting.
row.BlankCells(5, formatting);
}
// Skip one row before starting to generate data rows.
sheet.SkipRows(1);
// Insert a picture from a file and anchor it to the cell "G1".
string startupPath = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName);
using (IXlPicture picture = sheet.CreatePicture()) {
picture.Image = Image.FromFile(Path.Combine(startupPath, "Logo.png"));
picture.SetOneCellAnchor(new XlAnchorPoint(6, 0, 8, 4), 105, 30);
}
}
static void FitPicture(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream))
{
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
sheet.SkipColumns(1);
// Create the column "B" and set its width.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 300;
}
sheet.SkipRows(1);
// Create the second row and set its height.
using (IXlRow row = sheet.CreateRow())
{
row.HeightInPixels = 154;
}
#region #FitPicture
// Insert a picture from a file to fit in the cell B2.
using (IXlPicture picture = sheet.CreatePicture())
{
picture.Image = Image.FromFile(Path.Combine(imagesPath, "image1.jpg"));
picture.FitToCell(new XlCellPosition(1, 1), 300, 154, true);
}
}
#endregion #FitPicture
}
}
static void DocumentProperties(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
#region #DocumentProperties
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Set the built-in document properties.
document.Properties.Title = "XL Export API: document properties example";
document.Properties.Subject = "XL Export API";
document.Properties.Keywords = "XL Export, document generation";
document.Properties.Description = "How to set document properties using the XL Export API";
document.Properties.Category = "Spreadsheet";
document.Properties.Company = "DevExpress Inc.";
// Set the custom document properties.
document.Properties.Custom["Product Suite"] = "XL Export Library";
document.Properties.Custom["Revision"] = 5;
document.Properties.Custom["Date Completed"] = DateTime.Now;
document.Properties.Custom["Published"] = true;
// Generate data for the document.
using (IXlSheet sheet = document.CreateSheet()) {
sheet.SkipRows(1);
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(1);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "You can view document properties using the File->Info->Properties->Advanced Properties dialog.";
}
}
}
}
#endregion #DocumentProperties
}
static void PageMargins(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
#region #PageMargins
sheet.PageMargins = new XlPageMargins();
// Set the unit of margin measurement.
sheet.PageMargins.PageUnits = XlPageUnits.Centimeters;
// Specify page margins.
sheet.PageMargins.Left = 2.0;
sheet.PageMargins.Right = 1.0;
sheet.PageMargins.Top = 1.25;
sheet.PageMargins.Bottom = 1.25;
// Specify header and footer margins.
sheet.PageMargins.Header = 0.7;
sheet.PageMargins.Footer = 0.7;
#endregion #PageMargins
// Generate data for the document.
sheet.SkipRows(1);
using (IXlRow row = sheet.CreateRow()) {
row.SkipCells(1);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Invoke the Page Setup dialog to control margin settings.";
}
}
}
}
}
static void NumberFormat(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Specify options for exporting the document to CSV format.
CsvDataAwareExporterOptions csvOptions = document.Options as CsvDataAwareExporterOptions;
if (csvOptions != null)
{
csvOptions.WritePreamble = true;
}
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create six successive columns and set their widths.
for (int i = 0; i < 6; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 180;
}
}
#region #ExcelNumberFormat
// Create the header row for the "Excel number formats" category.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Excel number formats";
// Apply the "Heading 4" predefined formatting to the cell.
cell.Formatting = XlCellFormatting.Heading4;
}
}
// Use the predefined Excel number formats to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Predefined formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 123.456 as 123.46.
cell.Value = 123.456;
cell.Formatting = XlNumberFormat.Number2;
}
using (IXlCell cell = row.CreateCell()) {
// Display 12345 as 12,345.
cell.Value = 12345;
cell.Formatting = XlNumberFormat.NumberWithThousandSeparator;
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.33 as 33%.
cell.Value = 0.33;
cell.Formatting = XlNumberFormat.Percentage;
}
using (IXlCell cell = row.CreateCell()) {
// Display the current date as "mm-dd-yy".
cell.Value = DateTime.Now;
cell.Formatting = XlNumberFormat.ShortDate;
}
using (IXlCell cell = row.CreateCell()) {
// Display the current time as "h:mm AM/PM".
cell.Value = DateTime.Now;
cell.Formatting = XlNumberFormat.ShortTime12;
}
}
// Use custom number formats to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Custom formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 4310.45 as $4,310.45.
cell.Value = 4310.45;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
using (IXlCell cell = row.CreateCell()) {
// Display 3426.75 as €3,426.75.
cell.Value = 3426.75;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = @"_-[$€-2] * #,##0.00_-;-[$€-2] * #,##0.00_-;_-[$€-2] * "" - ""??_-;_-@_-";
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.333 as 33.3%.
cell.Value = 0.333;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "0.0%";
}
using (IXlCell cell = row.CreateCell()) {
// Apply the custom number format to the date value.
// Display days as Sunday–Saturday, months as January–December, days as 1–31 and years as 1900–9999.
cell.Value = DateTime.Now;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "dddd, mmmm d, yyyy";
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.6234 as 341/547.
cell.Value = 0.6234;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "# ???/???";
}
}
#endregion #ExcelNumberFormat
sheet.SkipRows(1);
#region #NETNumberFormat
// Create the header row for the ".NET number formats" category.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = ".NET number formats";
// Apply the "Heading 4" predefined formatting to the cell.
cell.Formatting = XlCellFormatting.Heading4;
}
}
// Use the standard .NET-style format strings to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Standard formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 123.45 as 123.
cell.Value = 123.45;
cell.Formatting = XlCellFormatting.FromNetFormat("D", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 12345 as 1.234500E+004.
cell.Value = 12345;
cell.Formatting = XlCellFormatting.FromNetFormat("E", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.33 as 33.00%.
cell.Value = 0.33;
cell.Formatting = XlCellFormatting.FromNetFormat("P", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display the current date using the short date pattern.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("d", true);
}
using (IXlCell cell = row.CreateCell()) {
// Display the current time using the short time pattern.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("t", true);
}
}
// Use custom format strings to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Custom formats:";
}
using (IXlCell cell = row.CreateCell()) {
// Display 123.456 as 123.46.
cell.Value = 123.45;
cell.Formatting = XlCellFormatting.FromNetFormat("#0.00", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 12345 as 1.235E+04.
cell.Value = 12345;
cell.Formatting = XlCellFormatting.FromNetFormat("0.0##e+00", false);
}
using (IXlCell cell = row.CreateCell()) {
// Display 0.333 as Max=33.3%.
cell.Value = 0.333;
cell.Formatting = XlCellFormatting.FromNetFormat("Max={0:#.0%}", false);
}
using (IXlCell cell = row.CreateCell()) {
// Apply the custom format string to the current date.
// Display days as 01–31, months as 01-12 and years as a four-digit number.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("dd-MM-yyyy", true);
}
using (IXlCell cell = row.CreateCell()) {
// Apply the custom format string to the current time.
// Display hours as 01-12, minutes as 00-59, and add the AM/PM designator.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("hh:mm tt", true);
}
}
#endregion #NETNumberFormat
}
}
}
static void PredefinedFormatting(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
#region #PredefinedFormatting
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create six successive columns and set their widths.
for (int i = 0; i < 6; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
}
}
// Specify the "Good, Bad and Neutral" formatting category.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Good, Bad and Neutral";
}
}
using (IXlRow row = sheet.CreateRow()) {
// Create a cell with the default "Normal" formatting.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Normal";
}
// Create a cell and apply the "Bad" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Bad";
cell.Formatting = XlCellFormatting.Bad;
}
// Create a cell and apply the "Good" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Good";
cell.Formatting = XlCellFormatting.Good;
}
// Create a cell and apply the "Neutral" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Neutral";
cell.Formatting = XlCellFormatting.Neutral;
}
}
sheet.SkipRows(1);
// Specify the "Data and Model" formatting category.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Data and Model";
}
}
using (IXlRow row = sheet.CreateRow()) {
// Create a cell and apply the "Calculation" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Calculation";
cell.Formatting = XlCellFormatting.Calculation;
}
// Create a cell and apply the "Check Cell" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Check Cell";
cell.Formatting = XlCellFormatting.CheckCell;
}
// Create a cell and apply the "Explanatory..." predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Explanatory";
cell.Formatting = XlCellFormatting.Explanatory;
}
// Create a cell and apply the "Input" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Input";
cell.Formatting = XlCellFormatting.Input;
}
// Create a cell and apply the "Linked Cell" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Linked Cell";
cell.Formatting = XlCellFormatting.LinkedCell;
}
// Create a cell and apply the "Note" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Note";
cell.Formatting = XlCellFormatting.Note;
}
}
using (IXlRow row = sheet.CreateRow()) {
// Create a cell and apply the "Output" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Output";
cell.Formatting = XlCellFormatting.Output;
}
// Create a cell and apply the "Warning Text" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Warning Text";
cell.Formatting = XlCellFormatting.WarningText;
}
}
sheet.SkipRows(1);
// Specify the "Titles and Headings" formatting category.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Titles and Headings";
}
}
using (IXlRow row = sheet.CreateRow()) {
// Create a cell and apply the "Heading 1" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 1";
cell.Formatting = XlCellFormatting.Heading1;
}
// Create a cell and apply the "Heading 2" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 2";
cell.Formatting = XlCellFormatting.Heading2;
}
// Create a cell and apply the "Heading 3" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 3";
cell.Formatting = XlCellFormatting.Heading3;
}
// Create a cell and apply the "Heading 4" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 4";
cell.Formatting = XlCellFormatting.Heading4;
}
// Create a cell and apply the "Title" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Title";
cell.Formatting = XlCellFormatting.Title;
}
// Create a cell and apply the "Total" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total";
cell.Formatting = XlCellFormatting.Total;
}
}
}
#endregion #PredefinedFormatting
}
}
static void Font(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
#region #Font
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Create five successive columns and set their widths.
for (int i = 0; i < 5; i++)
{
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
}
}
// Create the first row.
using (IXlRow row = sheet.CreateRow())
{
// Create the cell A1.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Body font";
// Apply the theme body font to the cell content.
cell.ApplyFormatting(XlFont.BodyFont());
}
// Create the cell B1.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Headings font";
// Apply the theme heading font to the cell content.
cell.ApplyFormatting(XlFont.HeadingsFont());
}
// Create the cell C1.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Custom font";
// Specify the custom font attributes.
XlFont font = new XlFont();
font.Name = "Century Gothic";
font.SchemeStyle = XlFontSchemeStyles.None;
// Apply the custom font to the cell content.
cell.ApplyFormatting(font);
}
}
// Create an array that stores different values of font size.
int[] fontSizes = new int[] { 11, 14, 18, 24, 36 };
// Skip one row in the worksheet.
sheet.SkipRows(1);
// Create the third row.
using (IXlRow row = sheet.CreateRow())
{
// Create five successive cells (A3:E3) with different font sizes.
for (int i = 0; i < 5; i++)
{
using (IXlCell cell = row.CreateCell())
{
// Set the cell value that displays the applied font size.
cell.Value = string.Format("{0}pt", fontSizes[i]);
// Create a font instance of the specified size.
XlFont font = new XlFont();
font.Size = fontSizes[i];
// Apply font settings to the cell content.
cell.ApplyFormatting(font);
}
}
}
// Skip one row in the worksheet.
sheet.SkipRows(1);
// Create the fifth row.
using (IXlRow row = sheet.CreateRow())
{
// Create the cell A5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Red";
// Create a font instance and set its color.
XlFont font = new XlFont()
{
Color = Color.Red
};
// Apply the font color to the cell content.
cell.ApplyFormatting(font);
}
// Create the cell B5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Bold";
// Create a font instance and set its style to bold.
XlFont font = new XlFont()
{
Bold = true
};
// Apply the font style to the cell content.
cell.ApplyFormatting(font);
}
// Create the cell C5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Italic";
// Create a font instance and set its style to italic.
XlFont font = new XlFont()
{
Italic = true
};
// Italicize the cell text.
cell.ApplyFormatting(font);
}
// Create the cell D5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Underline";
// Create a font instance and set the underline type to double.
XlFont font = new XlFont()
{
Underline = XlUnderlineType.Double
};
// Underline the cell text.
cell.ApplyFormatting(font);
}
// Create the cell E5.
using (IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "StrikeThrough";
// Create a font instance and turn the strikethrough formatting on.
XlFont font = new XlFont()
{
StrikeThrough = true
};
// Strike the cell text through.
cell.ApplyFormatting(font);
}
}
}
#endregion #Font
}
}
static void Alignment(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
#region #Alignment
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create three successive columns and set their widths.
for (int i = 0; i < 3; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 130;
}
}
// Create the first row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Left and Top";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Top));
}
// Create the second cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Center and Top";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Top));
}
// Create the third cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Right and Top";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Top));
}
}
// Create the second row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Left and Center";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center));
}
// Create the second cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Center and Center";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
// Create the third cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Right and Center";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Center));
}
}
// Create the third row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Left and Bottom";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom));
}
// Create the second cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Center and Bottom";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Bottom));
}
// Create the third cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Right and Bottom";
// Specify the horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
sheet.SkipRows(1);
// Create the fifth row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Create the first cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "The WrapText property is applied to wrap the text within a cell";
// Wrap the text within the cell.
cell.Formatting = new XlCellAlignment()
{
WrapText = true
};
}
// Create the second cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Indented text";
// Set the indentation of the cell content.
cell.Formatting = new XlCellAlignment()
{
Indent = 2
};
}
// Create the third cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Rotated text";
// Rotate the text within the cell.
cell.Formatting = new XlCellAlignment()
{
TextRotation = 90
};
}
}
}
#endregion #Alignment
}
}
static void TableStyleOptions(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream))
{
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Create columns "A", "B" and "C" and set their widths.
int[] widths = new int[] { 165, 120, 100 };
for (int i = 0; i < 3; i++)
{
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = widths[i];
}
#region #TableStyleOptions
IXlTable table;
// Specify an array containing column headings for tables.
string[] columnNames = new string[] { "Product", "Category", "Amount" };
// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Disable banded rows" }, XlCellFormatting.Title);
sheet.SkipRows(1);
// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
table = row.BeginTable(columnNames, true);
// Disable banded row formatting for the table.
table.Style.ShowRowStripes = false;
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
// Create the last table row and finish the table.
// The total row is not displayed for the table.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
row.EndTable(table, false);
}
sheet.SkipRows(1);
// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Enable banded columns" }, XlCellFormatting.Title);
sheet.SkipRows(1);
// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
table = row.BeginTable(columnNames, true);
// Apply banded column formatting to the table.
table.Style.ShowRowStripes = false;
table.Style.ShowColumnStripes = true;
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
// Create the last table row and finish the table.
// The total row is not displayed for the table.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
row.EndTable(table, false);
}
sheet.SkipRows(1);
// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Highlight first column" }, XlCellFormatting.Title);
sheet.SkipRows(1);
// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
table = row.BeginTable(columnNames, true);
// Display special formatting for the first column of the table.
table.Style.ShowFirstColumn = true;
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
// Create the last table row and finish the table.
// The total row is not displayed for the table.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
row.EndTable(table, false);
}
sheet.SkipRows(1);
// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Highlight last column" }, XlCellFormatting.Title);
sheet.SkipRows(1);
// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
table = row.BeginTable(columnNames, true);
// Display special formatting for the last column of the table.
table.Style.ShowLastColumn = true;
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
// Create the last table row and finish the table.
// The total row is not displayed for the table.
using (IXlRow row = sheet.CreateRow())
{
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
row.EndTable(table, false);
}
#endregion #TableStyleOptions
}
}
}
