while converting excel to the .csv file i am not able to converting date and save it in the csv file?

Question

I'm using Apache POI 3.6, i am trying to read data from excel and convert the excel data to the .CVS file . My Excel data will be in this format

1. 1st column will be :date(8/6/2009)
2. 2nd column will be :time(12:00:01 AM)
3. 3rd column will be :value 1 (30)
4. 4th column will be :value 2 (400.99).

i need to convert this data in this format (8-6-2009) and store that in the CVS file ERROR : I can store the data in the csv file but i m not able to convert the date and time in it. its taking some values in date column i m getting 41426, and in the time column i am getting this 3456.0.

i am not getting how to convert this above: please anyone help me out.. :(

my code is here:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 public class Excel {

static void xlsx(File inputFile, File outputFile) {
    // For storing data into CSV files
    StringBuffer data = new StringBuffer();

    try {
        FileOutputStream fos = new FileOutputStream(outputFile);
        FileInputStream fio = new FileInputStream(inputFile);
        // Get the workbook object for XLSX file
        XSSFWorkbook wBook = new XSSFWorkbook(fio);
        // Get first sheet from the workbook
        XSSFSheet sheet = wBook.getSheetAt(0);
        Row row;
        Cell cell;
        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {

                cell = cellIterator.next();

                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        data.append(cell.getBooleanCellValue() + ",");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (row.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC){
                            data.append(cell.getNumericCellValue() + ",");
                            }
                        if (DateUtil.isCellDateFormatted(row.getCell(0))) {
                            System.out.println ("Row No.: " + row.getRowNum ()+ " " + 
                                row.getCell(0).getDateCellValue());
                            data.append(row.getCell(0).getDateCellValue() + ",");
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        data.append(cell.getStringCellValue() + ",");
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        data.append("" + ",");
                        break;
                    default:
                        data.append(cell + ",");

                }
            }data.append("\r\n");
        }//System.out.println();
        //System.out.println(data);
        fos.write(data.toString().getBytes());
        fos.close();

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}
//testing the application 

public static void main(String[] args) {
    //reading file from desktop
    File inputFile = new File("D:\\project-work\\sampleData.xlsx");
    //writing excel data to csv 
    File outputFile = new File("D:\\project-work\\Data.csv");
    xlsx(inputFile, outputFile);
}

Answer 1

If the Excel has the cell set correctly as Date/Time, you should be able to get the actual date via

Date date = cell.getDateCellValue()

Then you can use SimpleDateFormatter or similar to format it in any way you need to in your CSV.