Npoi 1.2.4 Tutorial – Date function

Excel has a very rich date processing function, which is also well supported in Npoi. Such as:

usingNpoi. HSSF. Usermodel;usingNpoi. HPSF;usingNpoi. Poifs. FileSystem;usingNpoi. Ss. Usermodel; Hssfworkbook Workbook=NewHssfworkbook ();//Create a tableIsheet Sheet1 = workbook. Createsheet ("Sheet1");//Create a sheetIRow Row1 = Sheet1. CreateRow (0);//Create a rowIRow Row2 = Sheet1. CreateRow (1);//Create two more rows//Sheet1. CreateRow (0). Createcell (0). Setcellvalue ("B");//Create and set values directly in the first column of the first rowRow1. Createcell (0). Setcellvalue ("name");//Create a column and add content in the first columnRow1. Createcell (1). Setcellvalue ("participation in working hours"); Row1. Createcell (2). Setcellvalue ("Current Date"); Row1. Createcell (3). Setcellvalue ("Working years"); Icell Cel1= Row2. Createcell (0);//Create a column in the second rowIcell Cel2 = Row2. Createcell (1); Icell cel3= Row2. Createcell (2); Icell Cel4= Row2. Createcell (3); Cel1. Setcellvalue ("Tom");//add content in the first column of the second rowCel2. Setcellvalue (NewDateTime (2004,7,1));//Add DateCel3. Cellformula ="today ()";//Add current dateCel4. Cellformula ="concatenate (Datedif (B2,today (), \ "y\"), \ "year \", Datedif (B2,today (), \ "ym\"), \ "month \")";//The date in the POI is represented by a double type, so formatting//Set Date formatIcellstyle CellStyle =workbook. Createcellstyle (); IDataFormat format=workbook. Createdataformat (); Cellstyle.dataformat= format. GetFormat ("yyyy-m-d"); Cel2. CellStyle=Cellstyle;cel3. CellStyle=CellStyle;using(FileStream file =NewFileStream (@"C:\tes.xls", FileMode.Create)) {Workbook. Write (file);//write to the output stream}

Here are some explanations for the main functions used in the previous example:

Today (): Gets the current date;

Datedif (B2,today (), "Y"): The time interval that gets the date of the B2 cell and the previous date in years. ("Y": Represents the unit in years, "M" for the month; " D "is expressed in days);

Concatenate (STR1,STR2,...) : Connection string.

Also attached is the list of date functions commonly used in Excel, just make the appropriate changes to this sentence code:

Cel4. Cellformula = "Concatenate" (Datedif (B2,today (), \ "y\"), \ "year \", Datedif (B2,today (), \ "ym\"), \ "month \") ";

Name of function	function Description	Grammar
DATE	Returns the number of series that represents a specific date.	DATE (Year,month,day)
Datedif	Calculates the number of days, months, or years between two dates.	Datedif (Start_date,end_date,unit)
DATEVALUE	The main function of the function DATEVALUE is to convert the date in text representation to a series number.	DATEVALUE (Date_text)
Day	Returns the number of days of a date expressed as a series, expressed as integers 1 through 31.	Day (Serial_number)
DAYS360	Returns the number of days between two days, based on an algorithm that is 360 days a year (30 days per month for a total of 12 months a year).	DAYS360 (Start_date,end_date,method)
EDATE	Returns the number of date series for the specified number of months before or after the specified date (start_date). Use the function EDATE to calculate the date of the expiration date of the same day as the release date in January.	EDATE (start_date,months)
EOMONTH	Returns the number of series for the last day of the specified month before or after Start-date. Use the function EOMONTH to calculate the number of time series for the last day of a particular month for the expiry date of a security.	EOMONTH (start_date,months)
HOUR	Returns the number of hours of time value. That is, an integer between 0 (a.m.) and at (p.m.).	HOUR (Serial_number)
MINUTE	Returns the Minutes in a time value. An integer between 0 and 59.	MINUTE (Serial_number)
MONTH	Returns the month of a date in the series number. The month is an integer between 1 (January) and 12 (December).	MONTH (Serial_number)
Networkdays	Returns the full working day value between the parameters Start-data and End-data. Weekdays do not include weekends and specially designated holidays	Networkdays (Start_date,end_date,holidays)
Now	Returns the number of series that corresponds to the current date and time.	Now ()
SECOND	Returns the number of seconds of the time value. The number of seconds returned is an integer between 0 and 59.	SECOND (Serial_number)
Time	Returns the decimal value for a specific time, which is the value from 0 to 0.99999999, representing the time between 0:00:00 (12:00:00 a.m.) and 23:59:59 (11:59:59 p.m.).	Time (Hour,minute,second)
TimeValue	Returns the decimal value of the time represented by the text string. The decimal value is a number from 0 to 0.999999999, representing the time between 0:00:00 (12:00:00 AM) and 23:59:59 (11:59:59 PM).	TimeValue (Time_text)
TODAY	Returns the series number of the current date, which is the date-time code that Microsoft Excel uses for date and time calculations.	Today ()
WEEKDAY	Returns a date for the day of the week. By default, the value is an integer between 1 (Sunday) and 7 (Saturday).	WEEKDAY (Serial_number,return_type)
WEEKNUM	Returns a number that represents the week ordinal of a year.	WEEKNUM (Serial_num,return_type)
WORKDAY	Returns a date value that is a date (from the start date) before or after a specified weekday. Weekdays do not include weekends and specially designated holidays.	WORKDAY (Start_date,days,holidays)
Year	Returns the year of a date. The return value is an integer from 1900 to 9999.	Year (Serial_number)
YEARFRAC	Returns the number of days between start_date and end_date as a percentage of the year.	YEARFRAC (start_date,end_date,basis)

Npoi 1.2.4 Tutorial – Date function