Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Intro

Excerpt

Define and apply data validation rules to cells with ExcelApplication.

Excel Data Validation (in Excel the menu, Data > Validation...) allows you to control what kind of data can be entered into cells by creating validation rules. You can define and apply these rules with ExcelApplication.

Code

Code Block

  public class DataValidationDemo
    {

        // Declare an ExcelApplication object, workbook and a worksheet. The document created
        will only have one worksheet. 
        private ExcelApplication xlw;
        private Workbook wb;
        private Worksheet ws;
        // Previously selected by user in a webform
        private string borrowerName = "Sample J. Borrower";
        private string loanStartDate = "6/19/2013";
        private int Downpayment = 20;
        private double InterestRate = .05;
        private int PurchasePrice =  200000;
        private int LoanPeriod = 15;

        /// 
        /// Build the report with ExcelApplication
        /// 
        public void GenerateReport()
        {
            // Get path to template. 
            //string templatePath ="Templates/FirstYearPayment.xlsx";

            // Create an instance of ExcelApplication,
            open the correct Workbook template based on loan period,
            and create one Worksheet. 
            xlw = new ExcelApplication();
            wb = xlw.Open(@"..\..\ExcelTemplateFiles\FirstYearPayment.xlsx");
            ws = wb.Worksheets[0];

            // Insert some data into the template 
            ws.Name = borrowerName;
            ws["A2"].Value = "Prepared for " + borrowerName;
            ws["A3"].Value = "Generated on " + System.DateTime.Now.ToShortDateString();
            ws["C5"].Value = Convert.ToInt32(PurchasePrice);
            ws["C6"].Value = Convert.ToInt32(Downpayment) //.01;
            ws["C10"].Value = Convert.ToDouble(InterestRate);
            ws["C11"].Value = loanStartDate;
            ws["C9"].Value = Convert.ToInt32(LoanPeriod) //12;

            // Add data validation.
            //Define the type of validation you want (in this case, List), 
            //and specify the valid values in an array.
            
            int[] purchasePriceValues = { 200000, 250000, 300000, 350000, 400000 };
            SoftArtisans.OfficeWriter.ExcelWriter.DataValidation dvPurchasePrice =
                wb.CreateDataValidation(SoftArtisans.OfficeWriter.ExcelWriter.DataValidation.ValidationType.List, purchasePriceValues);

            // After you define your data validation rule, apply it to a Cell,
             //Area, or Range
             
            ws["C5"].DataValidation = dvPurchasePrice;

            // Define data validation rules for percentages 
            string[] downPaymentValues = { "20.00%", "25.00%", "30.00%", "35.00%", "40.00%" };
            DataValidation dvDownpayment =
                wb.CreateDataValidation(DataValidation.ValidationType.List, downPaymentValues);
            ws["C6"].DataValidation = dvDownpayment;

            string[] intRateValues = { "5.00%", "6.00%", "7.00%", "8.00%", "9.00%", "10.00%" };
            DataValidation dvInterestRate =
                wb.CreateDataValidation(DataValidation.ValidationType.List, intRateValues);
            ws["C10"].DataValidation = dvInterestRate;

            // Define a date-based comparison rule.  The entered date must be on or after 
             //the current date.
             

            DataValidation dvFirstPayment =
                wb.CreateDataValidation(DataValidation.ValidationType.Date,
                    DataValidation.ComparisonType.GreaterThanOrEqualTo, System.DateTime.Now);

            ws["C11"].DataValidation = dvFirstPayment;

            // Save the report by streaming it
            to the client's browser 
            xlw.Save(wb, @"..\..\ExcelOutputFiles\DataValidation_output.xlsx");

    }
}

...