Write time format (hh:mm:ss) without date in Java

Question

I am trying to convert an Excel (.xls) file having multiple worksheets into a .csv. The code works fine but I notice the datatype for certain columns is getting changed from time datatype to double datatype.

Example: If my input is 00:45:20, I am getting output like 0.006168981481481482. Each worksheet has columns using time datatype.

Note: My input do not have date part. Only time component is there. I have seen few posts related to this and tried the same. But the java code is printing only default date and excluded the time part.

I feel something has to be modified in case statement to populate time datatype. I would like to have a generic program so that whenever there is time datatype I have to write it in same format. The code I used:

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

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class exceltst 
{
    static void xls(File inputFile, File outputFile,int sheet_num) 
    {
        // For storing data into CSV files
        StringBuffer data = new StringBuffer();
        try 
        {
        FileOutputStream fos = new FileOutputStream(outputFile);

        // Get the workbook object for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
        // Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(sheet_num);
        Cell cell; 
        Row row;

        // 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:
                                data.append(cell.getNumericCellValue() + ",");
                                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('\n');
        }

        fos.write(data.toString().getBytes());
        fos.close();
        }
        catch (FileNotFoundException e) 
        {
                e.printStackTrace();
        }
        catch (IOException e) 
        {
                e.printStackTrace();
        }
        }

        public static void main(String[] args) 
        {
                File inputFile = new File("C:\\Call_Center_20150323.xls");
                File outputFile1 = new File("C:\\live_person.csv");
                xls(inputFile, outputFile1,3);
        }
 }

Could you please help how to populate the time datatype (hh:mm:ss) without date instead of double in the output file?

@Andreas: I have seen that post as well. But the difference here is, I will not have date part in my input file. Only time part is there. Hence when I apply that code it is populating default date and neglected the time part. — Vivek, Nov 02 '15 at 12:52
If your cell has a time format, then applying the format should return a string in `HH:MM:SS` format, of course assuming that is the format in question. That string then goes straight to your .csv file. No date or time logic involved. — Andreas, Nov 02 '15 at 16:45

Sedat Polat · Answer 1 · 2015-11-02T13:10:13.090

You should create a CellStyle at the first, then set this style for your time cell. Also for cvs file, you cannot create a CellStyle, you should work on excel file for using cell styles.

For Excel:

CellStyle style = workBook.createCellStyle();
style.setDataFormat(workBook.createDataFormat().getFormat("hh:mm:ss"));
cell.setCellStyle(style);
cell.setCellValue("16:15:11");

For cvs file, you should set value of your Cell as String:

data.append("16:15:11" + ",");

score 0 · Answer 2 · answered Nov 02 '15 at 12:05

Try

if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
        if (DateUtil.isCellDateFormatted(cell)) {
            System.out.println(cell.getDateCellValue());
        } else {
            System.out.println(cell.getNumericCellValue());
        }
}

For details you can refer here

Write time format (hh:mm:ss) without date in Java

2 Answers2