Message-ID: <303552000.8643.1711653272692.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_8642_1507613679.1711653272692" ------=_Part_8642_1507613679.1711653272692 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Data Validation

Data Validation

Intro

Define and apply data val= idation rules to cells with ExcelApplication.

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

Code

=20
  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 =3D "Sample J.=
 Borrower";
        private string loanStartDate =3D "6/19/201=
3";
        private int Downpayment =3D 20;
        private double InterestRate =3D .05;
        private int PurchasePrice =3D  200000;
        private int LoanPeriod =3D 15;

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

            // Create an instance of ExcelApp=
lication,
            open the correct Workbook templat=
e based on loan period,
            and create one Worksheet. 
            xlw =3D new ExcelApplication();
            wb =3D xlw.Open(@"..\..\Exce=
lTemplateFiles\FirstYearPayment.xlsx");
            ws =3D wb.Worksheets[0];

            // Insert some data into the temp=
late 
            ws.Name =3D borrowerName;
            ws["A2"].Value =3D &quo=
t;Prepared for " + borrowerName;
            ws["A3"].Value =3D &quo=
t;Generated on " + System.DateTime.Now.ToShortDateString();
            ws["C5"].Value =3D Conv=
ert.ToInt32(PurchasePrice);
            ws["C6"].Value =3D Conv=
ert.ToInt32(Downpayment) //.01;
            ws["C10"].Value =3D Con=
vert.ToDouble(InterestRate);
            ws["C11"].Value =3D loa=
nStartDate;
            ws["C9"].Value =3D Conv=
ert.ToInt32(LoanPeriod) //12;

            // Add data validation.
            //Define the type of validation y=
ou want (in this case, List), 
            //and specify the valid values in=
 an array.
            
            int[] purchasePriceValues =3D { 2=
00000, 250000, 300000, 350000, 400000 };
            SoftArtisans.OfficeWriter.ExcelWr=
iter.DataValidation dvPurchasePrice =3D
                wb.CreateDataValida=
tion(SoftArtisans.OfficeWriter.ExcelWriter.DataValidation.ValidationType.Li=
st, purchasePriceValues);

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

            // Define data validation rules f=
or percentages 
            string[] downPaymentValues =3D { =
"20.00%", "25.00%", "30.00%", "35.00%&qu=
ot;, "40.00%" };
            DataValidation dvDownpayment =3D
                wb.CreateDataValida=
tion(DataValidation.ValidationType.List, downPaymentValues);
            ws["C6"].DataValidation=
 =3D dvDownpayment;

            string[] intRateValues =3D { &quo=
t;5.00%", "6.00%", "7.00%", "8.00%", &qu=
ot;9.00%", "10.00%" };
            DataValidation dvInterestRate =3D
                wb.CreateDataValida=
tion(DataValidation.ValidationType.List, intRateValues);
            ws["C10"].DataValidatio=
n =3D dvInterestRate;

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

            DataValidation dvFirstPayment =3D
                wb.CreateDataValida=
tion(DataValidation.ValidationType.Date,
                    DataV=
alidation.ComparisonType.GreaterThanOrEqualTo, System.DateTime.Now);

            ws["C11"].DataValidatio=
n =3D dvFirstPayment;

            // Save the report by streaming i=
t
            to the client's browser 
            xlw.Save(wb, @"..\..\ExcelOu=
tputFiles\DataValidation_output.xlsx");

    }
}

=20

Downloads

------=_Part_8642_1507613679.1711653272692--