unable to replace the date in the excel file using java

Question

Requirement

i need to open excel file. Then i need to check for the date(12/31/2014). If this exist in the file then i need replace with 11/28/2014. Actually excel file is containing the date. But in my code is never passing this condition if (df.format(DateUtil.getJavaDate(cell.getNumericCellValue())).equals(df.format(asOfDate))) {.

Here is the code:

package excelAsOfDate;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelAsOfDate {
    public static void main(String[] args) {
        try {
            DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
            Date asOfDate = df.parse("12/31/2014");
            Date newAsOfDate = df.parse("11/28/2014");

            System.out.println("date as of" + df.format(asOfDate));

            File directory = new File("C://Users//kondeti.venkatarao//Documents//Regresion_sheets//test");
            File[] files = directory.listFiles();

            for (File file : files) {
                if (file.getName().toLowerCase().endsWith(".xlsx")) {
                    FileInputStream fis = new FileInputStream(file.getAbsoluteFile());
                    // Create Workbook instance holding reference to .xlsx file


                    XSSFWorkbook workbook = new XSSFWorkbook(fis);


                    int i = 1;
                    while (i < workbook.getNumberOfSheets()) {

                        // Get first/desired sheet from the workbook
                        XSSFSheet sheet = workbook.getSheetAt(i);

                        // Iterate through each rows one by one
                        Iterator<Row> rowIterator = sheet.iterator();
                        while (rowIterator.hasNext()) {
                            Row row = rowIterator.next();
                            // For each row, iterate through all the columns
                            Iterator<Cell> cellIterator = row.cellIterator();
                            while (cellIterator.hasNext()) {
                                Cell cell = cellIterator.next();
                                // Check the cell type and format accordingly
                                switch (cell.getCellType()) {

                                case Cell.CELL_TYPE_NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        if (df.format(DateUtil.getJavaDate(cell.getNumericCellValue())).equals(df.format(asOfDate))) {
                                            // System.out.println(df.format(cell.getDateCellValue()));
                                            System.out.println(df.format(DateUtil.getJavaDate(cell.getNumericCellValue())));                                            

                                            CreationHelper createHelper = workbook.getCreationHelper();
                                            CellStyle cellStyle = workbook.createCellStyle();
                                            cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                                            cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                                            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));
                                            cell.setCellValue(newAsOfDate);
                                            cell.setCellStyle(cellStyle);
                                        }
                                    } 
                                    break;
                                }
                            }
                        }
                        i++;
                        fis.close();
                    }
                    //FileOutputStream fileOut = new FileOutputStream("C://Users//kondeti.venkatarao//Documents//Regresion_sheets//test//final//"+file.getName());
                    FileOutputStream fileOut = new FileOutputStream(file.getAbsoluteFile());
                    workbook.write(fileOut);
                    fileOut.close();
                    fis.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Answer 1

Logging is a key to find your answers.

As I look into the Cell class, I see there is a method named :

getDateCellCalue()

When we look at the setter of that :

void setCellValue(java.util.Date value)

Converts the supplied date to its equivalent Excel numeric value and sets that into the cell.

So you are not comparing correct.
Mine quick suggestion is :

if (df.format(cell.getDateCellValue()).equals(df.format(asOfDate)))

If this doesn't work, put logging on what the df.format(cell.getDateCellValue() produces as String.

Edit :

Did you also noticed this :

DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
Date asOfDate = df.parse("12/31/2014");

Your second date is MM/dd/yyyy