void GenerateDataValidations(IXlSheet sheet, int firstDataRowIndex) { // Restrict data entry in the "Employee ID" column using criteria calculated by a worksheet formula (Employee ID must be a 5-digit number). XlDataValidation validation = new XlDataValidation(); validation.Ranges.Add(XlCellRange.FromLTRB(0, firstDataRowIndex, 0, sheet.CurrentRowIndex - 1)); validation.Type = XlDataValidationType.Custom; validation.Criteria1 = string.Format("=AND(ISNUMBER(A{0}),LEN(A{0})=5)", firstDataRowIndex + 1); validation.InputPrompt = "Please enter a 5-digit number."; validation.PromptTitle = "Employee ID"; sheet.DataValidations.Add(validation); // Restrict data entry in the "Salary" column to a whole number from 600 to 2000. validation = new XlDataValidation(); validation.Ranges.Add(XlCellRange.FromLTRB(2, firstDataRowIndex, 2, sheet.CurrentRowIndex - 1)); validation.Type = XlDataValidationType.Whole; validation.Operator = XlDataValidationOperator.Between; validation.Criteria1 = 600; validation.Criteria2 = 2000; // Specify the error message. validation.ErrorMessage = "Salary must be greater than $600 and less than $2000."; validation.ErrorTitle = "Warning"; validation.ErrorStyle = XlDataValidationErrorStyle.Warning; // Specify the input message. validation.InputPrompt = "Please enter a whole number in the range 600...2000."; validation.PromptTitle = "Salary"; validation.ShowErrorMessage = true; validation.ShowInputMessage = true; sheet.DataValidations.Add(validation); // Restrict data entry in the "Bonus" column to a decimal number within the specified limits (bonus cannot be greater than 10% of the salary.) validation = new XlDataValidation(); validation.Ranges.Add(XlCellRange.FromLTRB(3, firstDataRowIndex, 3, sheet.CurrentRowIndex - 1)); validation.Type = XlDataValidationType.Whole; validation.Operator = XlDataValidationOperator.Between; validation.Criteria1 = 0; validation.Criteria2 = string.Format("=C{0}*0.1", firstDataRowIndex + 1); // Specify the error message. validation.ErrorMessage = "Bonus cannot be greater than 10% of the salary."; validation.ErrorTitle = "Information"; validation.ErrorStyle = XlDataValidationErrorStyle.Information; validation.ShowErrorMessage = true; sheet.DataValidations.Add(validation); // Restrict data entry in the "Department" column to values in a drop-down list obtained from the cell range in the hidden "Departments" worksheet. validation = new XlDataValidation(); validation.Ranges.Add(XlCellRange.FromLTRB(4, firstDataRowIndex, 4, sheet.CurrentRowIndex - 1)); validation.Type = XlDataValidationType.List; XlCellRange sourceRange = XlCellRange.FromLTRB(0, 0, 0, departments.Count - 1).AsAbsolute(); sourceRange.SheetName = "Departments"; validation.Criteria1 = sourceRange; sheet.DataValidations.Add(validation); }
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); } } }