void GenerateHeaderRow(IXlSheet sheet, string nameOfState) { // Create the header row for sales data in the specific state. using (IXlRow row = sheet.CreateRow()) { // Skip the first cell in the row. row.SkipCells(1); // Create the cell that displays the state name and specify its format settings. using (IXlCell cell = row.CreateCell()) { cell.Value = nameOfState; cell.ApplyFormatting(headerRowFormatting); cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0))); cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.General, XlVerticalAlignment.Bottom)); } // Create four successive cells with values "Q1", "Q2", "Q3" and "Q4". // Apply specific formatting settings to the created cells. for (int i = 0; i < 4; i++) { using (IXlCell cell = row.CreateCell()) { cell.Value = string.Format("Q{0}", i + 1); cell.ApplyFormatting(headerRowFormatting); } } // Create the "Yearly total" cell and specify its format settings. using (IXlCell cell = row.CreateCell()) { cell.Value = "Yearly total"; cell.ApplyFormatting(headerRowFormatting); } } }
void InitializeFormatting() { // Specify formatting settings for the even rows. evenRowFormatting = new XlCellFormatting(); evenRowFormatting.Font = new XlFont(); evenRowFormatting.Font.Name = "Century Gothic"; evenRowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None; evenRowFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.General, XlVerticalAlignment.Center); // Specify formatting settings for the odd rows. oddRowFormatting = new XlCellFormatting(); oddRowFormatting.CopyFrom(evenRowFormatting); oddRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, -0.15)); // Specify formatting settings for the header row. headerRowFormatting = new XlCellFormatting(); headerRowFormatting.CopyFrom(evenRowFormatting); headerRowFormatting.Font.Bold = true; headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0); headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0)); headerRowFormatting.Border = new XlBorder(); headerRowFormatting.Border.TopColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0); headerRowFormatting.Border.TopLineStyle = XlBorderLineStyle.Medium; headerRowFormatting.Border.BottomColor = XlColor.FromTheme(XlThemeColor.Dark1, 0.0); headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Medium; // Specify formatting settings for the total row. totalRowFormatting = new XlCellFormatting(); totalRowFormatting.CopyFrom(evenRowFormatting); totalRowFormatting.Font.Bold = true; }
void InitializeFormatting() { // Specify formatting settings for the header rows. headerRowFormatting = new XlCellFormatting(); headerRowFormatting.Font = XlFont.BodyFont(); headerRowFormatting.Font.Bold = true; headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0); headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0)); headerRowFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom); // Specify formatting settings for the data rows. dataRowFormatting = new XlCellFormatting(); dataRowFormatting.Font = XlFont.BodyFont(); dataRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, 0.0)); // Specify formatting settings for the total rows. totalRowFormatting = new XlCellFormatting(); totalRowFormatting.Font = XlFont.BodyFont(); totalRowFormatting.Font.Bold = true; totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)); // Specify formatting settings for the grand total row. grandTotalRowFormatting = new XlCellFormatting(); grandTotalRowFormatting.Font = XlFont.BodyFont(); grandTotalRowFormatting.Font.Bold = true; grandTotalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, -0.2)); }
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 } } }
void ExportCell(IXlRow row, int gridRowHandle, GridColumn gridColumn) { using (IXlCell cell = row.CreateCell()) { // Set cell value cell.Value = XlVariantValue.FromObject(this.view.GetRowCellValue(gridRowHandle, gridColumn)); // Get cell appearance AppearanceObject appearance = GetCellAppearance(gridRowHandle, gridColumn); // Apply alignment XlCellAlignment alignment = new XlCellAlignment() { WrapText = appearance.TextOptions.WordWrap.HasFlag(WordWrap.Wrap), VerticalAlignment = ConvertAlignment(appearance.TextOptions.VAlignment), HorizontalAlignment = ConvertAlignment(appearance.TextOptions.HAlignment) }; cell.ApplyFormatting(alignment); // Apply borders Color borderColor = appearance.GetBorderColor(); if (!DXColor.IsTransparentOrEmpty(borderColor)) { cell.ApplyFormatting(XlBorder.OutlineBorders(borderColor)); } // Apply fill if (appearance.Options.UseBackColor) { cell.ApplyFormatting(XlFill.SolidFill(appearance.BackColor)); } // Apply font Font appearanceFont = appearance.Font; XlFont font = XlFont.CustomFont(appearanceFont.Name); font.Size = appearanceFont.SizeInPoints; font.Bold = appearanceFont.Bold; font.Italic = appearanceFont.Italic; font.StrikeThrough = appearanceFont.Strikeout; font.Underline = appearanceFont.Underline ? XlUnderlineType.Single : XlUnderlineType.None; if (appearance.Options.UseForeColor) { font.Color = appearance.ForeColor; } cell.ApplyFormatting(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 Subtotals(Stream stream, XlDocumentFormat documentFormat) { // Declare a variable that indicates the start of the data rows to calculate grand totals. int startDataRowForGrandTotal; // 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 column "A" and set its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 200; } // Create five successive columns and set the specific number format for their cells. for (int i = 0; i < 5; i++) { using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 100; column.Formatting = new XlCellFormatting(); column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"; } } // Specify formatting settings for cells containing data. XlCellFormatting rowFormatting = new XlCellFormatting(); rowFormatting.Font = XlFont.BodyFont(); rowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, 0.0)); // Specify formatting settings for the header row. XlCellFormatting headerRowFormatting = new XlCellFormatting(); headerRowFormatting.Font = XlFont.BodyFont(); headerRowFormatting.Font.Bold = true; headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0); headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0)); // Specify formatting settings for the total row. XlCellFormatting totalRowFormatting = new XlCellFormatting(); totalRowFormatting.Font = XlFont.BodyFont(); totalRowFormatting.Font.Bold = true; totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)); // Generate data for the document. Random random = new Random(); string[] productsDairy = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" }; string[] productsCereals = new string[] { "Gnocchi di nonna Alice", "Gustaf's Knäckebröd", "Ravioli Angelo", "Singaporean Hokkien Fried Mee" }; // Create the header row. using (IXlRow row = sheet.CreateRow()) { startDataRowForGrandTotal = row.RowIndex + 1; using (IXlCell cell = row.CreateCell()) { cell.Value = "Product"; cell.ApplyFormatting(headerRowFormatting); cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0)); } for (int i = 0; i < 4; i++) { using (IXlCell cell = row.CreateCell()) { cell.Value = string.Format("Q{0}", i + 1); cell.ApplyFormatting(headerRowFormatting); cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom)); } } using (IXlCell cell = row.CreateCell()) { cell.Value = "Yearly total"; cell.ApplyFormatting(headerRowFormatting); cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom)); } } // Create data rows for Dairy products. for (int i = 0; i < 4; i++) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = productsDairy[i]; cell.ApplyFormatting(rowFormatting); cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8)); } for (int j = 0; j < 4; j++) { using (IXlCell cell = row.CreateCell()) { cell.Value = Math.Round(random.NextDouble() * 2000 + 3000); cell.ApplyFormatting(rowFormatting); } } using (IXlCell cell = row.CreateCell()) { // Use the SUM function to calculate annual sales for each product. cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(1, row.RowIndex, 4, row.RowIndex))); cell.ApplyFormatting(rowFormatting); cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0))); } } } // Create the total row for Dairies. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Subtotal"; cell.ApplyFormatting(totalRowFormatting); cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6)); } for (int j = 0; j < 5; j++) { using (IXlCell cell = row.CreateCell()) { // Use the SUBTOTAL function to calculate total sales for each quarter and the entire year. cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, row.RowIndex - 4, j + 1, row.RowIndex - 1), XlSummary.Sum, false)); cell.ApplyFormatting(totalRowFormatting); } } } // Create data rows for Cereals. for (int i = 0; i < 4; i++) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = productsCereals[i]; cell.ApplyFormatting(rowFormatting); cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8)); } for (int j = 0; j < 4; j++) { using (IXlCell cell = row.CreateCell()) { cell.Value = Math.Round(random.NextDouble() * 2000 + 3000); cell.ApplyFormatting(rowFormatting); } } using (IXlCell cell = row.CreateCell()) { // Use the SUM function to calculate annual sales for each product. cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(1, row.RowIndex, 4, row.RowIndex))); cell.ApplyFormatting(rowFormatting); cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0))); } } } // Create the total row for Cereals. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Subtotal"; cell.ApplyFormatting(totalRowFormatting); cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6)); } for (int j = 0; j < 5; j++) { using (IXlCell cell = row.CreateCell()) { // Use the SUBTOTAL function to calculate total sales for each quarter and the entire year. cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, row.RowIndex - 4, j + 1, row.RowIndex - 1), XlSummary.Sum, false)); cell.ApplyFormatting(totalRowFormatting); } } } #region #SubtotalFunction // Create the grand total row. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Grand Total"; cell.ApplyFormatting(totalRowFormatting); cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6)); } for (int j = 0; j < 5; j++) { using (IXlCell cell = row.CreateCell()) { // Use the SUBTOTAL function to calculate grand total sales for each quarter and the entire year. cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, startDataRowForGrandTotal, j + 1, row.RowIndex - 1), XlSummary.Sum, false)); cell.ApplyFormatting(totalRowFormatting); } } } #endregion #SubtotalFunction } } }
static void PrintArea(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 #PrintArea // Set the print area to cells A1:E5. sheet.PrintArea = XlCellRange.FromLTRB(0, 0, 4, 4); #endregion #PrintArea // Create worksheet columns and set their widths. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 110; column.Formatting = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom); } using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 190; } for (int i = 0; i < 2; i++) { using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 90; column.Formatting = new XlCellFormatting(); column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"; } } using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 130; } sheet.SkipColumns(1); using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 130; } // Specify formatting settings for cells containing data. XlCellFormatting rowFormatting = new XlCellFormatting(); rowFormatting.Font = new XlFont(); rowFormatting.Font.Name = "Century Gothic"; rowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None; // Specify formatting settings for the header row. XlCellFormatting headerRowFormatting = new XlCellFormatting(); headerRowFormatting.CopyFrom(rowFormatting); headerRowFormatting.Font.Bold = true; headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0); headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0)); // Generate the header row. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Employee ID"; cell.ApplyFormatting(headerRowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = "Employee name"; cell.ApplyFormatting(headerRowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = "Salary"; cell.ApplyFormatting(headerRowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = "Bonus"; cell.ApplyFormatting(headerRowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = "Department"; cell.ApplyFormatting(headerRowFormatting); } row.SkipCells(1); using (IXlCell cell = row.CreateCell()) { cell.Value = "Departments"; cell.ApplyFormatting(headerRowFormatting); } } // Generate data for the document. int[] id = new int[] { 10115, 10709, 10401, 10204 }; string[] name = new string[] { "Augusta Delono", "Chris Cadwell", "Frank Diamond", "Simon Newman" }; int[] salary = new int[] { 1100, 2000, 1750, 1250 }; int[] bonus = new int[] { 50, 180, 100, 80 }; int[] deptid = new int[] { 0, 2, 3, 3 }; string[] department = new string[] { "Accounting", "IT", "Management", "Manufacturing" }; for (int i = 0; i < 4; i++) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = id[i]; cell.ApplyFormatting(rowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = name[i]; cell.ApplyFormatting(rowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = salary[i]; cell.ApplyFormatting(rowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = bonus[i]; cell.ApplyFormatting(rowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = department[deptid[i]]; cell.ApplyFormatting(rowFormatting); } row.SkipCells(1); using (IXlCell cell = row.CreateCell()) { cell.Value = department[i]; cell.ApplyFormatting(rowFormatting); } } } // Restrict data entry in the cell range E2:E5 to values in a drop-down list obtained from the cells G2:G5. XlDataValidation validation = new XlDataValidation(); validation.Ranges.Add(XlCellRange.FromLTRB(4, 1, 4, 4)); validation.Type = XlDataValidationType.List; validation.Criteria1 = XlCellRange.FromLTRB(6, 1, 6, 4).AsAbsolute(); sheet.DataValidations.Add(validation); } } }
void InitializeFormatting() { // Specify formatting settings for the even rows. evenRowFormatting = new XlCellFormatting(); evenRowFormatting.Font = new XlFont(); evenRowFormatting.Font.Name = "Century Gothic"; evenRowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None; evenRowFormatting.Fill = XlFill.SolidFill(Color.White); // Specify formatting settings for the odd rows. oddRowFormatting = new XlCellFormatting(); oddRowFormatting.CopyFrom(evenRowFormatting); oddRowFormatting.Fill = XlFill.SolidFill(Color.FromArgb(242, 242, 242)); // Specify formatting settings for the header row. headerRowFormatting = new XlCellFormatting(); headerRowFormatting.CopyFrom(evenRowFormatting); headerRowFormatting.Font.Bold = true; headerRowFormatting.Font.Color = Color.White; headerRowFormatting.Fill = XlFill.SolidFill(Color.FromArgb(192, 0, 0)); // Set borders for the header row. headerRowFormatting.Border = new XlBorder(); // Specify the top border and set its color to white. headerRowFormatting.Border.TopColor = Color.White; // Specify the medium border line style. headerRowFormatting.Border.TopLineStyle = XlBorderLineStyle.Medium; // Specify the bottom border for the header row. // Set the bottom border color to dark gray. headerRowFormatting.Border.BottomColor = Color.FromArgb(89, 89, 89); // Specify the medium border line style. headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Medium; // Specify formatting settings for the invoice header. panelFont = new XlFont(); panelFont.Name = "Century Gothic"; panelFont.SchemeStyle = XlFontSchemeStyles.None; panelFont.Color = Color.White; // Set font attributes for the row displaying the invoice label and company name. titleFont = panelFont.Clone(); titleFont.Size = 26; // Specify formatting settings for the worksheet range containing the name and contact details of the seller (the "Vader Enterprises" panel). leftPanelFormatting = new XlCellFormatting(); // Set the cell background color to dark gray. leftPanelFormatting.Fill = XlFill.SolidFill(Color.FromArgb(89, 89, 89)); leftPanelFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom); leftPanelFormatting.NumberFormat = XlNumberFormat.General; // Set the right border for this range. leftPanelBorder = new XlBorder(); // Set the right border color to white. leftPanelBorder.RightColor = Color.White; // Specify the medium border line style. leftPanelBorder.RightLineStyle = XlBorderLineStyle.Medium; // Specify formatting settings for the worksheet range containing general information about the invoice: // its date, reference number and service description (the "Invoice" panel). rightPanelFormatting = new XlCellFormatting(); // Set the cell background color to dark red. rightPanelFormatting.Fill = XlFill.SolidFill(Color.FromArgb(192, 0, 0)); rightPanelFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom); rightPanelFormatting.NumberFormat = XlNumberFormat.General; // Specify formatting settings and font attributes for the worksheet range containing buyer's contact information (the "Bill To" panel). infoFormatting = new XlCellFormatting(); // Set the cell background color to light gray. infoFormatting.Fill = XlFill.SolidFill(Color.FromArgb(217, 217, 217)); infoFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom); infoFormatting.NumberFormat = XlNumberFormat.General; infoFont = panelFont.Clone(); infoFont.Color = Color.Black; }
void GenerateColumns(IXlSheet sheet) { XlNumberFormat currencyFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"; XlNumberFormat discountFormat = @"0.00%;[Red]-0.00%;;@"; // Create the column "A" and set its width. using (IXlColumn column = sheet.CreateColumn()) column.WidthInPixels = 21; // Create the column "B" and set its width. using (IXlColumn column = sheet.CreateColumn()) column.WidthInPixels = 21; // Create the column "C" containing the "Description" label in the header row and adjust its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 120; // Set the horizontal and vertical alignment of cell content. column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center)); } // Create the column "D" and adjust its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 263; // Set the horizontal and vertical alignment of cell content. column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center)); } // Create the column "E" containing the "QTY" label in the header row and adjust its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 102; // Set the horizontal and vertical alignment of cell content. column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center)); } // Create the column "F" containing the "Unit Price" label in the header row and adjust its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 150; // Set the horizontal and vertical alignment of cell content. column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Center)); // Apply the currency number format to the column. column.ApplyFormatting(currencyFormat); } // Create the column "G" containing the "Discount" label in the header row and adjust its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 134; // Set the horizontal and vertical alignment of cell content. column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center)); // Apply the custom number format to the column. column.ApplyFormatting(discountFormat); } // Create the column "H" containing the "Amount" label in the header row and adjust its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 174; // Set the horizontal and vertical alignment of cell content. column.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Center)); // Apply the currency number format to the column. column.ApplyFormatting(currencyFormat); } // Create the column "I" and set its width. using (IXlColumn column = sheet.CreateColumn()) column.WidthInPixels = 21; }
static void DocumentOptions(Stream stream, XlDocumentFormat documentFormat) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(documentFormat); #region #DocumentOptions // Create a new document. using (IXlDocument document = exporter.CreateDocument(stream)) { document.Options.Culture = CultureInfo.CurrentCulture; // Create a worksheet. using (IXlSheet sheet = document.CreateSheet()) { using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 200; } using (IXlColumn column = sheet.CreateColumn()) { column.Formatting = XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Bottom); } // Display the file format to which the document is exported. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Document format:"; } using (IXlCell cell = row.CreateCell()) { cell.Value = document.Options.DocumentFormat.ToString().ToUpper(); } } // Display the maximum number of columns allowed by the output file format. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Maximum number of columns:"; } using (IXlCell cell = row.CreateCell()) { cell.Value = document.Options.MaxColumnCount; } } // Display the maximum number of rows allowed by the output file format. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Maximum number of rows:"; } using (IXlCell cell = row.CreateCell()) { cell.Value = document.Options.MaxRowCount; } } // Display whether the document can contain multiple worksheets. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Supports document parts:"; } using (IXlCell cell = row.CreateCell()) { cell.Value = document.Options.SupportsDocumentParts; } } // Display whether the document can contain formulas. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Supports formulas:"; } using (IXlCell cell = row.CreateCell()) { cell.Value = document.Options.SupportsFormulas; } } // Display whether the document supports grouping functionality. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Supports outline/grouping:"; } using (IXlCell cell = row.CreateCell()) { cell.Value = document.Options.SupportsOutlineGrouping; } } } } #endregion #DocumentOptions }
void op_CustomizeCell(CustomizeCellEventArgs e) { long _mark = long.Parse((GVG.GetRowCellValue(e.RowHandle, "ID") ?? 0).ToString()); XlCellFormatting formatting = new XlCellFormatting(); formatting.Font = new XlFont(); formatting.Font.Bold = true; formatting.Font.Name = "Times New Roman"; if (_mark == 0) { formatting.Border = XlBorder.OutlineBorders(Color.FromArgb(216, 228, 188)); if (e.ColumnFieldName == "CongTy") { formatting.Border.RightColor = Color.Black; } formatting.Fill = XlFill.SolidFill(Color.FromArgb(216, 228, 188)); e.Formatting.FormatType = FormatType.None; e.Value = string.Empty; } else { XlCellAlignment alignment = new XlCellAlignment(); alignment.HorizontalAlignment = XlHorizontalAlignment.Center; alignment.VerticalAlignment = XlVerticalAlignment.Center; if (e.AreaType == SheetAreaType.Header) { e.Formatting.Alignment = alignment; formatting.Fill = XlFill.SolidFill(Color.DarkSeaGreen); formatting.Font.Size = 15; } else if (_mark == -1) { formatting.Border = XlBorder.OutlineBorders(Color.DarkSeaGreen); if (!"MaCho HanhTrinhDi MaHD SoVe".Contains(e.ColumnFieldName)) { formatting.Border.RightColor = Color.Black; } formatting.Border.BottomColor = Color.Black; formatting.Fill = XlFill.SolidFill(Color.DarkSeaGreen); formatting.Font.Size = 12; if (e.ColumnFieldName == "HanhTrinhVe") { e.Value = "Tổng"; } if ((e.Value ?? string.Empty).ToString() == "0") { e.Value = string.Empty; } } else { formatting.Fill = XlFill.SolidFill(Color.White); if (e.Value is string) { e.Formatting.Alignment = alignment; } } } e.Formatting.CopyFrom(formatting, FormatType.None); e.Handled = true; }
static void Main(string[] args) { // Create an exporter instance. IXlExporter exporter = XlExport.CreateExporter(XlDocumentFormat.Xlsx); // Create the FileStream object with the specified file path. using (FileStream stream = new FileStream("Document.xlsx", FileMode.Create, FileAccess.ReadWrite)) { // Create a new document and begin to write it to the specified stream. using (IXlDocument document = exporter.CreateDocument(stream)) { // Add a new worksheet to the document. using (IXlSheet sheet = document.CreateSheet()) { // Specify the worksheet name. sheet.Name = "Sales report"; // Create the first column and set its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 100; } // Create the second column and set its width. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 250; } // Create the third column and set the specific number format for its cells. using (IXlColumn column = sheet.CreateColumn()) { column.WidthInPixels = 100; column.Formatting = new XlCellFormatting(); column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"; } // Specify cell font attributes. XlCellFormatting cellFormatting = new XlCellFormatting(); cellFormatting.Font = new XlFont(); cellFormatting.Font.Name = "Century Gothic"; cellFormatting.Font.SchemeStyle = XlFontSchemeStyles.None; // Specify formatting settings for the header row. XlCellFormatting headerRowFormatting = new XlCellFormatting(); headerRowFormatting.CopyFrom(cellFormatting); headerRowFormatting.Font.Bold = true; headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0); headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0)); // Create the header row. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = "Region"; cell.ApplyFormatting(headerRowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = "Product"; cell.ApplyFormatting(headerRowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = "Sales"; cell.ApplyFormatting(headerRowFormatting); } } // Generate data for the sales report. string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" }; int[] amount = new int[] { 6750, 4500, 3550, 4250, 5500, 6250, 5325, 4235 }; for (int i = 0; i < 8; i++) { using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.Value = (i < 4) ? "East" : "West"; cell.ApplyFormatting(cellFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = products[i % 4]; cell.ApplyFormatting(cellFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = amount[i]; cell.ApplyFormatting(cellFormatting); } } } // Enable AutoFilter for the created cell range. sheet.AutoFilterRange = sheet.DataRange; // Specify formatting settings for the total row. XlCellFormatting totalRowFormatting = new XlCellFormatting(); totalRowFormatting.CopyFrom(cellFormatting); totalRowFormatting.Font.Bold = true; totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent5, 0.6)); // Create the total row. using (IXlRow row = sheet.CreateRow()) { using (IXlCell cell = row.CreateCell()) { cell.ApplyFormatting(totalRowFormatting); } using (IXlCell cell = row.CreateCell()) { cell.Value = "Total amount"; cell.ApplyFormatting(totalRowFormatting); cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom)); } using (IXlCell cell = row.CreateCell()) { // Add values in the cell range C2 through C9 using the SUBTOTAL function. cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(2, 1, 2, 8), XlSummary.Sum, true)); cell.ApplyFormatting(totalRowFormatting); } } } } } // Open the XLSX document using the default application. System.Diagnostics.Process.Start("Document.xlsx"); }