Change format of date from Excel

Question

I have written a Java file Handling program which fetches the date and days from an excel file where no entry has been made.

The excel file stores the attendance records for some people. and if there has been no entry for a particular day then the program should check if the day was a weekend. If it is not a weekend then the porgram should output the name and the day and date.

However the current format of the output is not as I want it to be :


No entry found for Shiv on Wed Jul 27 00:00:00 IST 2022
No entry found for Shiv on Thu Jul 28 00:00:00 IST 2022
No entry found for Shiv on Fri Jul 29 00:00:00 IST 2022
No entry found for Shiv on Mon Aug 01 00:00:00 IST 2022
No entry found for Shiv on Wed Aug 03 00:00:00 IST 2022
No entry found for Shiv on Thu Aug 04 00:00:00 IST 2022

I want the output date to be in the style 01 August 2022, Monday

How do I achieve that?

Following is the code


import java.io.File;  
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.Scanner;
import java.util.Calendar;
import java.text.DateFormat;  
 
import java.util.Date;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.xssf.usermodel.XSSFSheet;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
public class ExcelReader {
    public static void main(String [] args) {
        try {
            File file=new File("./data/details.xlsx");
            FileInputStream fis= new FileInputStream(file);
            XSSFWorkbook wb=new XSSFWorkbook(fis);
            System.out.println("Enter the sheet you want to search in: ");
            Scanner sc= new Scanner(System.in);
            int n= sc.nextInt(); 
            XSSFSheet sheet=wb.getSheetAt(n-1);
            Iterator<Row> itr=sheet.iterator();
            
            
            while(itr.hasNext()) {
                
                
                
                
                Row row=itr.next();
                
                Iterator<Cell> cellIterator=row.cellIterator();
                while(cellIterator.hasNext()) {
                    Cell cell=cellIterator.next();
                    Cell name_cell=row.getCell(0);
                    String name=name_cell.getStringCellValue();
                    if (cell.getRowIndex()!=0 && cell.getStringCellValue().equals("")&& cell.getColumnIndex()!=0){
                        int idate=cell.getColumnIndex();
                        Row first_row=sheet.getRow(0); 
                        Cell date_cell=first_row.getCell(idate);
                        
                        Date sdate=date_cell.getDateCellValue();
                        
                        
                      
                            
                        if (ExcelReader.DayCheck(sdate)){
                            System.out.println("No entry found for "+name+" on "+sdate);
                            
                            
                        }
                        
                        
                        
                            
                    }
                    
                    
                    
                    
                    }
                
                }
                System.out.println("");
            }
            
        catch(Exception e)  
        {  
        e.printStackTrace();  
        }  
        }
    
    public static boolean DayCheck(Date date) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);

        int day = cal.get(Calendar.DAY_OF_WEEK);
        return (day != Calendar.SATURDAY && day != Calendar.SUNDAY);
        
    }

}

This question is not like the other questions where I simply need to change the format of date to dd-mm-yyyy I need to take the date containing the day date month time and locale info to extract the date in the format dd-mm-yyyy, Day

Have you looked at [Change date format in a Java string](https://stackoverflow.com/q/4772425/12567365), or [similar questions](https://www.google.com/search?q=how+to+format+a+Java+date+site:stackoverflow.com)? Do any of those help? See also the JavaDoc for [SimpleDateFormat](https://docs.oracle.com/en/java/javase/17/docs/api/java.base/java/text/SimpleDateFormat.html) — andrewJames, Oct 01 '22 at 12:14
not really here I have no control over the format of the date. Nomatter how I set the date format in excel it stays the same in java — lord foul, Oct 01 '22 at 12:17
Side note: Wherever possible, you should avoid Java's `Date` and `SimpleDateFormat` classes, and use [`java.time`](https://docs.oracle.com/en/java/javase/17/docs/api/java.base/java/time/package-summary.html) classes instead. But I don't know if POI supports those, for your needs. — andrewJames, Oct 01 '22 at 12:20
The format of the date in Excel (how it is displayed in the spreadsheet) is irrelevant. You have your Java `Date sdate` object. You want a formatted string of that Java date. So use `SimpleDateFormat` to get that. (Let me know if I am missing the point here.) — andrewJames, Oct 01 '22 at 12:24
@andrewJames POI will happily return a `LocalDateTime`, as well as the older `java.util.Date` - see https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Cell.html#getLocalDateTimeCellValue-- — Gagravarr, Oct 01 '22 at 12:58
Thank you @gagravarr. In that case, @lordfoul, use `LocalDateTime` and `DateTimeFormatter`. — andrewJames, Oct 01 '22 at 13:08
I strongly recommend you don’t use `Date`. That class is poorly designed and long outdated. Instead use `LocalDateTime` and `DateTimeFormatter`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Oct 01 '22 at 13:50
Use [`Cell.getLocalDateTimeCellValue()`](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Cell.html#getLocalDateTimeCellValue--) as in `date_cell.getLocalDateTimeCellValue().format(DateTimeFormatter.ofPattern("dd MMMM uuuu, EEEE", Locale.ENGLISH))`. — Ole V.V., Oct 01 '22 at 13:53
Thanks everyone for the help. DO let me know your thoughts on my solution. — lord foul, Oct 02 '22 at 08:52

score 0 · Answer 1 · answered Oct 02 '22 at 08:50

I introduced these two functions to create the date format I needed.


        public static String getDayStringOld(Date date, Locale locale){

        DateFormat formatter = new SimpleDateFormat("EEEE", locale);
        return formatter.format(date);}
    
    public static String getmonth(Date date) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int month= cal.get(Calendar.MONTH);
        
        String[] monthNames = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};
        return monthNames[month];
        
    }
}

Also, I changed the output print statemnt to print the date in the way I wanted.

        System.out.println("No entry found for "+name+" on "+ strDate.substring(8,10)+"-"+getmonth( sdate)+"-"+strDate.substring(24,28) +" "+ getDayStringOld(sdate,locale));

I strongly recommend you don’t use `Date`, `DateFormat`, `SimpleDateFormat` and `Calendar`. Those classes are troublesome and long outdated, nothing that you want to use. Also Java knows the names of the months (even in many languages), so don’t type them yourself. Use `LocalDateTime` and `DateTimeFormatter`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Oct 02 '22 at 16:32

Change format of date from Excel

1 Answers1