Process from xls to xlsx change date format

1

I have a problem converting xls files to xlsx and it is that when reading the cells where the dates have the data type in the xls cell, it appears as 13/04/2016 (The data type in the document says custom) but when I generate the new document in xlsx I take it as a number for example 14690 and formatting it gives me an erroneous date, how can I do it so that in reading that cell I take it as a simple string and it does not convert it to int? will I leave the code that does the conversion from xls to xlsx?

public class xls2xlsx {

/**
 * @param archivo
 * @param salida
 * @throws InvalidFormatException
 * @throws IOException
 */
public String convertirLibro(String archivo,String salida) throws InvalidFormatException,
        IOException {

    String inpFn = archivo;
    String outFn = salida;

    InputStream in = new BufferedInputStream(new FileInputStream(inpFn));
    try {
        Workbook wbIn = new HSSFWorkbook(in);
        File outF = new File(outFn);
        if (outF.exists())
            outF.delete();

        Workbook wbOut = new XSSFWorkbook();
        int sheetCnt = wbIn.getNumberOfSheets();
        for (int i = 0; i < sheetCnt; i++) {
            Sheet sIn = wbIn.getSheetAt(0);
            Sheet sOut = wbOut.createSheet(sIn.getSheetName());
            Iterator<Row> rowIt = sIn.rowIterator();
            while (rowIt.hasNext()) {
                Row rowIn = rowIt.next();
                Row rowOut = sOut.createRow(rowIn.getRowNum());

                Iterator<Cell> cellIt = rowIn.cellIterator();
                while (cellIt.hasNext()) {
                    Cell cellIn = cellIt.next();

                    Cell cellOut = rowOut.createCell(
                            cellIn.getColumnIndex(), cellIn.getCellType());

                    switch (cellIn.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;

                    case Cell.CELL_TYPE_BOOLEAN:
                        cellOut.setCellValue(cellIn.getBooleanCellValue());
                        break;

                    case Cell.CELL_TYPE_ERROR:
                        cellOut.setCellValue(cellIn.getErrorCellValue());
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        cellOut.setCellFormula(cellIn.getCellFormula());
                        break;

                    case Cell.CELL_TYPE_NUMERIC:
                        cellOut.setCellValue(cellIn.getNumericCellValue());

                        break;


                    case Cell.CELL_TYPE_STRING:
                        cellOut.setCellValue(cellIn.getStringCellValue());
                        break;
                    }

                    {
                      CellStyle styleIn = cellIn.getCellStyle();
                      CellStyle styleOut = cellOut.getCellStyle();
                      styleOut.setDataFormat(styleIn.getDataFormat());
                    }
                    cellOut.setCellComment(cellIn.getCellComment());

                    // HSSFCellStyle cannot be cast to XSSFCellStyle
                    // cellOut.setCellStyle(cellIn.getCellStyle());
                }
            }
        }
        OutputStream out = new BufferedOutputStream(new FileOutputStream(
                outF));
        try {
            wbOut.write(out);

        } finally {
            out.close();

        }
    } finally {
        in.close();
    }
    return outFn;
}

}

    
asked by Felipe Inostroza 15.12.2016 в 21:27
source

2 answers

1

I recommend that when you verify the numeric type you add a validation if the cell is a number or is a date. You can use this code:

case Cell.CELL_TYPE_NUMERIC:
    if (DateUtil.isCellDateFormatted(cellIn)) {
        //cambiar esto al formato que deseas de fecha
        CellStyle cellStyle = wbOut.createCellStyle();
        CreationHelper createHelper = wb.getCreationHelper();
        cellStyle.setDataFormat(
            createHelper.createDataFormat().getFormat("d/m/yyyy h:mm"));
        cellOut.setCellValue(cellIn.getDateCellValue());
        cellOut.setCellStyle(cellStyle);
    } else {
        cellOut.setCellValue(cellIn.getNumericCellValue());
    }
    break;

Adapted from: Issue reading in a cell from Excel with Apache POI and POI - How do I set cell value to Date and apply default Excel date format?

    
answered by 16.12.2016 / 17:44
source
0

You can use DateUtil.isCellDateFormatted Take a look at this thread as well

    
answered by 16.12.2016 в 08:45