2.3.3 using npoi for Excel-date functions

Excel has rich date processing functions, which are also well supported in npoi. For example:
Similar to the preceding basic formula settings:

Hssfsheet sheet1 = Hssfworkbook. createsheet ( " Sheet1 " );
Hssfrow row1 = Sheet1.createrow ( 0 );
Hssfrow row2 = Sheet1.createrow ( 1 );
Row1.createcell ( 0 ). Setcellvalue ( " Name " );
Row1.createcell ( 1 ). Setcellvalue ( " Time of participation " );
Row1.createcell ( 2 ). Setcellvalue ( " Current date " );
Row1.createcell ( 3 ). Setcellvalue ( " Service Life " );

Hssfcell cel1=Row2.createcell (0);
Hssfcell cel2=Row2.createcell (1);
Hssfcell cel3=Row2.createcell (2);
Hssfcell cel4=Row2.createcell (3);

Cel1.setcellvalue ( " ATAO. Xiang " );
Cel2.setcellvalue ( New Datetime ( 2004 , 7 , 1 ));
Cel3.setcellformula ( " Today () " );
Cel4.setcellformula ( " Concatenate (datedif (B2, today (),\ " Y \ " ),\ " Year \ " , Datedif (B2, today (),\ " Ym \ " ),\ " Months \ " ) " );

// In poi, the date is represented by the double type, so you need to format
hssfcellstyle cellstyle = hssfworkbook. createcellstyle ();
hssfdataformat format = hssfworkbook. createdataformat ();
cellstyle. dataformat = format. getformat ( " yyyy-m-D " );

Cel2.cellstyle=Cellstyle;
Cel3.cellstyle=Cellstyle;

The following describes the main functions used in the previous example:
Today (): obtains the current date;
Datedif (B2, today (), "Y"): returns the interval between the date of cell B2 and the previous date in years. ("Y": indicates the unit of year, "M" indicates the unit of month; "D" indicates the unit of day );
Concatenate (str1, str2,...): connection string.

In addition, the list of commonly used date functions in Excel is attached.CodeMake the appropriate changes:

Cel4.setcellformula ( " Concatenate (datedif (B2, today (),\ " Y \ " ),\ " Year \ " , Datedif (B2, today (),\ " Ym \ " ),\ " Months \ " ) " );

Function Name	Function Description	Syntax
Date	Returns the number of series representing 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 datevalue function is to convert a date in text into a series number.	Datevalue (date_text)
Day	Returns the number of days of a date in series, represented by an integer of 1 to 31.	Day (serial_number)
Days360	Based onAlgorithm(30 days per month, 12 months in a year), returns the number of days of the difference between the two days.	Days360 (start_date, end_date, method)
Edate	Returns the number of date series specified before or after the specified date (start_date. You can use the edate function to calculate the date of the expiration date on the same day as the release date on January 1, January.	Edate (start_date, months)
Eomonth	Returns the number of series of the last day of the specified month before or after start-date. The eomonth function is used to calculate the number of time series of the last day of a specific month. It is used to calculate the expiration date of securities.	Eomonth (start_date, months)
Hour	Returns the hour of the time value. It is an integer between 0 (a.m.) and 23 (p.m.	Hour (serial_number)
Minute	Returns the minute in the time value. It is an integer between 0 and 59.	Minute (serial_number)
Month	Returns the month of the date in series. The month is an integer between 1 (January) and 12 (December.	Month (serial_number)
Networkdays	Returns the complete workday value between start-data and end-data. Workdays do not include weekends and specified holidays	Networkdays (start_date, end_date, holidays)
Now	Returns the number of series corresponding to the current date and time.	Now ()
Second	Returns the number of seconds of the time value. The returned number of seconds is an integer between 0 and 59.	Second (serial_number)
Time	Returns a small value of a specific time. The small value returned by the function time is a value ranging from 0 to 0.99999999, representing from 0:00:00 (12:00:00 A.M) to 23:59:59 (11:59:59 p. m.	Time (hour, minute, second)
Timevalue	Returns a small value of time represented by a text string. This small value is a value ranging from 0 to 0.999999999, representing the time from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 PM.	Timevalue (time_text)
Today	Returns the number of series of the current date, which is the date-time code used by Microsoft Excel for date and time calculation.	Today ()
Weekday	Returns the day of a week. By default, the value is an integer between 1 (Sunday) and 7 (Saturday.	Weekday (serial_number, return_type)
Weeknum	Returns a number representing the week of the year.	Weeknum (serial_num, return_type)
Workday	Returns the date value of a date before or after a specified business day. Workdays do not include weekends and specified holidays.	Workday (start_date, days, holidays)
Year	Returns the year of a date. The return value is an integer between 1900 and 9999.	Year (serial_number)
Yearfrac	Returns the percentage of days between start_date and end_date in the year.	Yearfrac (start_date, end_date, basis)

 

Returned directory