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))) {.

我需要打开excel文件。然后我需要检查一下日期(12/31/2014)。如果这个文件中存在，那么我需要替换为11/28/2014。实际上excel文件包含日期。但是在我的代码中，如果(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();
        }
    }
}

1 个解决方案

#1

Logging is a key to find your answers.

日志记录是找到答案的关键。

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

当我查看Cell类时，我看到有一个方法名为:

getDateCellCalue()

When we look at the setter of that :

当我们看它的setter时

void setCellValue(java.util.Date value)

空白setCellValue(java.util。日期值)

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

将提供的日期转换为等效的Excel数值，并将其设置为单元格。

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.

如果这不起作用，那么将日志记录放在df.format(cell.getDateCellValue()作为字符串生成的内容上。

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

你的第二次约会是MM/dd/yyyy

#1