Exigence

J'ai besoin d'ouvrir le file excel. Ensuite, je dois vérifier la date (31/12/2014). Si cela existe dans le file, j'ai besoin de le replace par le 11/28/2014. En réalité, le file excel contient la date. Mais dans mon code ne passe jamais cette condition if (df.format(DateUtil.getJavaDate(cell.getNumericCellValue())).equals(df.format(asOfDate))) { .

Voici le 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(Ssortingng[] 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(); } } } i ++; 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(Ssortingng[] 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(); } } } 

• Obtenir dans un format différent le contenu des cellules de la date dans Apint POI
• Lecture / écriture avec POI d'Apache
• Apache POI setCellData ne définit pas datatables de la cellule
• POI d'Apache – Supprimer la ligne d'application dans Excel
• Erreur Apache Poi 64K dans le test Espresso

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

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