static void CreateColumns(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
#region #CreateColumns
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create the column A and set its width to 100 pixels.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
}
// Hide the column B in the worksheet.
using (IXlColumn column = sheet.CreateColumn()) {
column.IsHidden = true;
}
// Create the column D and set its width to 24.5 characters.
using (IXlColumn column = sheet.CreateColumn(3)) {
column.WidthInCharacters = 24.5f;
}
}
#endregion #CreateColumns
}
}
static void PictureHyperlinkClick(Stream stream, XlDocumentFormat documentFormat)
{
#region #HyperlinkClick
// 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())
{
// Load a picture from a file and add a hyperlink to it.
using (IXlPicture picture = sheet.CreatePicture())
{
picture.Image = Image.FromFile(Path.Combine(imagesPath, "DevExpress.png"));
picture.HyperlinkClick.TargetUri = "http://www.devexpress.com";
picture.HyperlinkClick.Tooltip = "Developer Express Inc.";
picture.SetTwoCellAnchor(new XlAnchorPoint(1, 1, 0, 0), new XlAnchorPoint(10, 5, 2, 15), XlAnchorType.TwoCell);
}
}
}
#endregion #HyperlinkClick
}
void GenerateDocument(IXlDocument document)
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Employees";
// Specify print settings for the worksheet.
SetupPageParameters(sheet);
// Generate worksheet columns.
GenerateColumns(sheet);
// Add the title to the documents exported to the XLSX and XLS formats.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
GenerateTitle(sheet);
}
// Create the header row.
GenerateHeaderRow(sheet);
int firstDataRowIndex = sheet.CurrentRowIndex;
// Create the data rows.
for (int i = 0; i < employees.Count; i++)
{
GenerateDataRow(sheet, employees[i], (i + 1) == employees.Count);
}
// Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange;
// Create data validation criteria for the documents exported to the XLSX and XLS formats.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
GenerateDataValidations(sheet, firstDataRowIndex);
}
}
// Create the hidden worksheet containing source data for the data validation drop-down list.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
using (IXlSheet sheet = document.CreateSheet()) {
sheet.Name = "Departments";
sheet.VisibleState = XlSheetVisibleState.Hidden;
foreach (string department in departments)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell())
cell.Value = department;
}
}
}
}
}
static void CreateRows(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
#region #CreateRows
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create the first row and set its height to 40 pixels.
using (IXlRow row = sheet.CreateRow()) {
row.HeightInPixels = 40;
}
// Hide the third row in the worksheet.
using (IXlRow row = sheet.CreateRow(2)) {
row.IsHidden = true;
}
}
#endregion #CreateRows
}
}
static void AddTable(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 #AddTable
IXlTable table;
// Specify an array containing column headings for a table.
string[] columnNames = new string[] { "Product", "Category", "Amount" };
// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
// Start generating the table with a header row displayed.
table = row.BeginTable(columnNames, true);
// Specify the total row label.
table.Columns[0].TotalRowLabel = "Total";
// Specify the function to calculate the total.
table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
// Specify the number format for the "Amount" column and its total cell.
XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
table.Columns[2].DataFormatting = accounting;
table.Columns[2].TotalRowFormatting = accounting;
}
// 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);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
row.EndTable(table, true);
#endregion #AddTable
}
}
}
void GenerateDocument(IXlDocument document)
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Specify options for exporting the document in CSV format.
CsvDataAwareExporterOptions csvOptions = document.Options as CsvDataAwareExporterOptions;
if (csvOptions != null)
{
csvOptions.WritePreamble = true;
csvOptions.UseCellNumberFormat = false;
csvOptions.NewlineAfterLastRow = true;
}
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Annual Sales";
// Specify page settings.
sheet.PageSetup = new XlPageSetup();
// Scale the print area to fit to one page wide.
sheet.PageSetup.FitToPage = true;
sheet.PageSetup.FitToWidth = 1;
sheet.PageSetup.FitToHeight = 0;
// Generate worksheet columns.
GenerateColumns(sheet);
// Add the title to the documents exported to the XLSX and XLS formats.
if (document.Options.DocumentFormat != XlDocumentFormat.Csv)
{
GenerateTitle(sheet);
}
// Create the header row.
GenerateHeaderRow(sheet);
int firstDataRowIndex = sheet.CurrentRowIndex;
// Create the data rows.
for (int i = 0; i < sales.Count; i++)
{
GenerateDataRow(sheet, sales[i], (i + 1) == sales.Count);
}
// Create the total row.
GenerateTotalRow(sheet, firstDataRowIndex);
// Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange;
// Create conditional formatting rules to be applied to worksheet data.
GenerateConditionalFormatting(sheet, firstDataRowIndex);
}
}
static void SimpleFormula(Stream stream, XlDocumentFormat documentFormat)
{
#region #SimpleFormula
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat, new XlFormulaParser());
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create worksheet columns and set their widths.
for (int i = 0; i < 4; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 80;
}
}
// Generate data for the document.
string[] header = new string[] { "Description", "QTY", "Price", "Amount" };
string[] product = new string[] { "Camembert", "Gorgonzola", "Mascarpone", "Mozzarella" };
int[] qty = new int[] { 12, 15, 25, 10 };
double[] price = new double[] { 23.25, 15.50, 12.99, 8.95 };
double discount = 0.2;
// Create the header row.
using (IXlRow row = sheet.CreateRow()) {
for (int i = 0; i < 4; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = header[i];
}
}
}
// Create data rows using string formulas.
for (int i = 0; i < 4; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = product[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = qty[i];
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = price[i];
}
using (IXlCell cell = row.CreateCell()) {
// Set the formula to calculate the amount
// applying 20% quantity discount on orders more than 15 items.
cell.SetFormula(String.Format("=IF(B{0}>15,C{0}*B{0}*(1-{1}),C{0}*B{0})", i + 2, discount));
}
}
}
}
}
#endregion #SimpleFormula
}
static void Hyperlinks(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 #Hyperlinks
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 300;
}
// Create a hyperlink to a cell in the current workbook.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Local link";
cell.Formatting = XlCellFormatting.Hyperlink;
XlHyperlink hyperlink = new XlHyperlink();
hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex));
hyperlink.TargetUri = "#Sheet1!C5";
sheet.Hyperlinks.Add(hyperlink);
}
}
// Create a hyperlink to a cell located in the external workbook.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "External file link";
cell.Formatting = XlCellFormatting.Hyperlink;
XlHyperlink hyperlink = new XlHyperlink();
hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex));
hyperlink.TargetUri = "linked.xlsx#Sheet1!C5";
sheet.Hyperlinks.Add(hyperlink);
}
}
// Create a hyperlink to a web page.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "External URI";
cell.Formatting = XlCellFormatting.Hyperlink;
XlHyperlink hyperlink = new XlHyperlink();
hyperlink.Reference = new XlCellRange(new XlCellPosition(cell.ColumnIndex, cell.RowIndex));
hyperlink.TargetUri = "http://www.devexpress.com";
sheet.Hyperlinks.Add(hyperlink);
}
}
}
#endregion #Hyperlinks
}
}
static void ColorScale(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()) {
// Generate data for the document.
for (int i = 0; i < 11; i++)
{
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = cell.RowIndex + 1;
}
}
}
}
#region #ColorScaleRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (A1:A11 and C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the default three-color scale rule to differentiate low, medium and high values in cell ranges.
XlCondFmtRuleColorScale rule = new XlCondFmtRuleColorScale();
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (B1:B11 and D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10));
// Create the two-color scale rule to differentiate low and high values in cell ranges.
rule = new XlCondFmtRuleColorScale();
rule.ColorScaleType = XlCondFmtColorScaleType.ColorScale2;
// Set a color corresponding to the minimum value in the cell range.
rule.MinColor = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
// Set a color corresponding to the maximum value in the cell range.
rule.MaxColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.5);
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #ColorScaleRule
}
}
}
static void MergeCells(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 the first row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Create a cell.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Merged cells A1 to E1";
// Align the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
}
// Create the second row in the worksheet.
using (IXlRow row = sheet.CreateRow()) {
// Create a cell.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Merged cells A2 to A5";
// Align the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
// Wrap the text within the cell.
cell.Formatting.Alignment.WrapText = true;
}
// Create a cell.
using (IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Merged cells B2 to E5";
// Align the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
}
#region #MergeCells
// Merge cells contained in the range A1:E1.
sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 0, 4, 0));
// Merge cells contained in the range A2:A5.
sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 1, 0, 4));
// Merge cells contained in the range B2:E5.
sheet.MergedCells.Add(XlCellRange.FromLTRB(1, 1, 4, 4));
#endregion #MergeCells
}
}
}
static void TableStyle(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 #TableStyle
IXlTable table;
// Specify an array containing column headings for a table.
string[] columnNames = new string[] { "Product", "Category", "Amount" };
// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
// Start generating the table with a header row displayed.
table = row.BeginTable(columnNames, true);
// Apply the table style.
table.Style.Name = XlBuiltInTableStyleId.Dark7;
}
// 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 #TableStyle
}
}
}
public void ExportToExcel()
{
SaveFileDialogService.Filter = "Excel files (*.xlsx)|*.xlsx";
if (SaveFileDialogService.ShowDialog())
{
IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xlsx);
string[] columnNames = new string[] { "Word", "Definition" };
string fileName = SaveFileDialogService.GetFullFileName();
using (FileStream stream = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite))
{
using (IXlDocument document = exporter.CreateDocument(stream))
{
using (IXlSheet sheet = document.CreateSheet())
{
sheet.Name = "Dictionary";
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInCharacters = 25;
column.Formatting = new XlCellFormatting();
column.Formatting.Alignment = new XlCellAlignment();
column.Formatting.Alignment.VerticalAlignment = XlVerticalAlignment.Center;
}
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInCharacters = 100;
column.Formatting = new XlCellFormatting();
column.Formatting.Alignment = new XlCellAlignment();
column.Formatting.Alignment.WrapText = true;
}
IXlTable table;
using (IXlRow row = sheet.CreateRow())
table = row.BeginTable(columnNames, true);
foreach (DictionaryBookmark bookmark in DictionaryBookmarksView)
{
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new string[] { bookmark.Word, bookmark.Definition }, null);
}
using (IXlRow row = sheet.CreateRow())
row.EndTable(table, false);
table.Style.ShowFirstColumn = true;
}
}
}
Process.Start(fileName);
}
}
static void TimePeriod(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()) {
// Generate data for the document.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
column.ApplyFormatting(XlNumberFormat.ShortDate);
}
for (int i = 0; i < 10; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = DateTime.Now.AddDays(row.RowIndex - 5);
}
}
}
#region #TimePeriodRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9));
// Create the rule to highlight yesterday's dates in the cell range.
XlCondFmtRuleTimePeriod rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Yesterday;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to highlight today's dates in the cell range.
rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Today;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Create the rule to highlight tomorrows's dates in the cell range.
rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Tomorrow;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Neutral;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #TimePeriodRule
}
}
}
static void CreateDocument(Stream stream, XlDocumentFormat documentFormat)
{
#region #CreateDocument
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document and write it to the specified stream.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
}
#endregion #CreateDocument
}
void GenerateDocument(IXlDocument document)
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Sales Report";
// Specify print settings for the worksheet.
SetupPageParameters(sheet);
// Specify the summary row and summary column location for the grouped data.
sheet.OutlineProperties.SummaryBelow = true;
sheet.OutlineProperties.SummaryRight = true;
// Generate worksheet columns.
GenerateColumns(sheet);
// Add the document title.
GenerateTitle(sheet);
// Begin to group worksheet rows (create the outer group of rows).
sheet.BeginGroup(false);
// Create the query expression to retrieve data from the sales list and group data by the State.
// Query variable is an IEnumerable<IGrouping<string, SalesData>>.
var statesQuery = from data in sales
group data by data.State into dataGroup
orderby dataGroup.Key
select dataGroup.Key;
// Create data rows to display sales for each state.
foreach (string state in statesQuery)
{
GenerateData(sheet, state);
}
// Finalize the group creation.
sheet.EndGroup();
// Create the grand total row.
GenerateGrandTotalRow(sheet);
// Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange;
}
}
void GenerateDocument(IXlDocument document)
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Add a new worksheet to the document.
using (IXlSheet sheet = document.CreateSheet()) {
// Specify the worksheet name.
sheet.Name = "Invoice";
// Specify page settings.
sheet.PageSetup = new XlPageSetup();
// Scale the print area to fit to one page wide.
sheet.PageSetup.FitToPage = true;
sheet.PageSetup.FitToWidth = 1;
sheet.PageSetup.FitToHeight = 0;
// Generate worksheet columns.
GenerateColumns(sheet);
// Generate data rows containing the invoice heading.
GenerateInvoiceTitle(sheet);
// Generate data rows containing the recipient's contact information.
GenerateInvoiceBillTo(sheet);
// Generate the header row for the table of purchased products.
GenerateHeaderRow(sheet);
int firstDataRowIndex = sheet.CurrentRowIndex;
// Generate the data row for each product in the invoice list and provide the product information: its description, quantity, unit price and so on.
for (int i = 0; i < invoice.Items.Count; i++)
{
GenerateDataRow(sheet, invoice.Items[i], (i + 1) == invoice.Items.Count);
}
// Generate the total row.
GenerateTotalRow(sheet, firstDataRowIndex);
// Generate data rows containing additional information.
GenerateInfoRow(sheet, "Make all checks payable to Vader Enterprises");
GenerateInfoRow(sheet, "If you have any questions concerning this invoice, contact Darth Vader, (111)111-1111, [email protected]");
// Specify the data range to be printed.
sheet.PrintArea = sheet.DataRange;
}
}
static void Blanks(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())
{
// Generate data for the document.
for (int i = 0; i < 10; i++)
{
using (IXlRow row = sheet.CreateRow())
{
using (IXlCell cell = row.CreateCell())
{
if ((i % 2) == 0)
{
cell.Value = i + 1;
}
}
}
}
#region #BlanksRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9));
// Create the rule to highlight blank cells in the range.
XlCondFmtRuleBlanks rule = new XlCondFmtRuleBlanks(true);
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to highlight non-blank cells in the range.
rule = new XlCondFmtRuleBlanks(false);
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #BlanksRule
}
}
}
MemoryStream CreateBIFF8DataStream()
{
IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xls);
MemoryStream dataStream = new MemoryStream();
using (IXlDocument document = exporter.CreateDocument(dataStream)) {
using (IXlSheet sheet = document.CreateSheet()) {
ExportColumns(sheet);
ExportRows(sheet);
this.sheetName = sheet.Name;
this.dataRange = sheet.DataRange;
}
}
dataStream.Position = 0;
return(dataStream);
}
static void Duplicates(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()) {
// Generate data for the document.
for (int i = 0; i < 11; i++)
{
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = cell.ColumnIndex * cell.RowIndex + cell.RowIndex + 1;
}
}
}
}
#region #DuplicatesRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 10));
// Create the rule to identify duplicate values in the cell range.
formatting.Rules.Add(new XlCondFmtRuleDuplicates()
{
Formatting = XlCellFormatting.Bad
});
// Create the rule to identify unique values in the cell range.
formatting.Rules.Add(new XlCondFmtRuleUnique()
{
Formatting = XlCellFormatting.Good
});
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #DuplicatesRule
}
}
}
static void Top10(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()) {
// Generate data for the document.
for (int i = 0; i < 10; i++)
{
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
using (IXlCell cell = row.CreateCell()) {
cell.Value = cell.ColumnIndex * 4 + cell.RowIndex + 1;
}
}
}
}
#region #TopAndBottomRules
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:D10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 9));
// Create the rule to identify bottom 10 values in the cell range.
XlCondFmtRuleTop10 rule = new XlCondFmtRuleTop10();
rule.Bottom = true;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to identify top 10 values in the cell range.
rule = new XlCondFmtRuleTop10();
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #TopAndBottomRules
}
}
}
static void CreateSheet(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
#region #CreateSheet
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a new worksheet under the specified name.
using (IXlSheet sheet = document.CreateSheet()) {
sheet.Name = "Sales report";
}
}
#endregion #CreateSheet
}
static void Fill(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 #Fill
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the predefined color.
cell.ApplyFormatting(XlFill.SolidFill(Color.Beige));
}
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the custom RGB color.
cell.ApplyFormatting(XlFill.SolidFill(Color.FromArgb(0xff, 0x99, 0x66)));
}
using (IXlCell cell = row.CreateCell()) {
// Fill the cell background using the theme color.
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent3, 0.4)));
}
}
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using predefined colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkDown, Color.Red, Color.White));
}
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using custom RGB colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.DarkTrellis, Color.FromArgb(0xff, 0xff, 0x66), Color.FromArgb(0x66, 0x99, 0xff)));
}
using (IXlCell cell = row.CreateCell()) {
// Specify the cell background pattern using theme colors.
cell.ApplyFormatting(XlFill.PatternFill(XlPatternType.LightHorizontal, XlColor.FromTheme(XlThemeColor.Accent1, 0.2), XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
#endregion #Fill
}
}
bool ExportToFile(string fileName, XlDocumentFormat documentFormat)
{
try {
using (FileStream stream = new FileStream(fileName, FileMode.Create)) {
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document and begin to write it to the specified stream.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Generate the document content.
GenerateDocument(document);
}
}
return(true);
}
catch (Exception ex) {
MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
static void EncryptDocument(Stream stream, XlDocumentFormat documentFormat)
{
#region #EncryptDocument
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Specify encryption options.
// A workbook will be encrypted using the default encryption mechanism
// (agile encryption for XLSX files, and RC4 encryption for XLS files).
EncryptionOptions encryptionOptions = new EncryptionOptions();
// Specify the encryption password.
encryptionOptions.Password = "******";
// Create a new document and encrypt its contents.
using (IXlDocument document = exporter.CreateDocument(stream, encryptionOptions))
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
}
#endregion #EncryptDocument
}
static void HideGridlines(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream))
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
#region #HideGridlines
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Hide gridlines on the worksheet.
sheet.ViewOptions.ShowGridLines = false;
}
#endregion #HideGridlines
}
}
static void HideHeaders(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream))
{
// Specify the document culture.
document.Options.Culture = CultureInfo.CurrentCulture;
#region #HideHeaders
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Hide row and column headers in the worksheet.
sheet.ViewOptions.ShowRowColumnHeaders = false;
}
#endregion #HideHeaders
}
}
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 RichTextFormatting(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 #RichTextFormatting
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Create the first column and set its width.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 180;
}
// Create the first row.
using (IXlRow row = sheet.CreateRow())
{
// Create the cell A1.
using (IXlCell cell = row.CreateCell())
{
// Create an XlRichTextString instance.
XlRichTextString richText = new XlRichTextString();
// Add three text runs to the collection.
richText.Runs.Add(new XlRichTextRun("Formatted ", XlFont.CustomFont("Arial", 14.0, XlColor.FromArgb(0x53, 0xbb, 0xf4))));
richText.Runs.Add(new XlRichTextRun("cell ", XlFont.CustomFont("Century Gothic", 14.0, XlColor.FromArgb(0xf1, 0x77, 0x00))));
richText.Runs.Add(new XlRichTextRun("text", XlFont.CustomFont("Consolas", 14.0, XlColor.FromArgb(0xe3, 0x2c, 0x2e))));
// Add the rich formatted text to the cell.
cell.SetRichText(richText);
}
}
}
#endregion #RichTextFormatting
}
}
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
}
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
}
}
