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
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.=
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