set column format type as date or time using HSSFSheet

Question

I want to set my column Mobile_Time as time / Date time type column. I have tried

protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        DAO d0 = new DAO();
        Map<String, String> AllUsermap = new HashMap<String, String>();
        AllUsermap = d0.colleagueMap(Integer.parseInt(request.getParameter("lid")), request.getParameter("ltype"));
        String date = request.getParameter("date");

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment; filename=All Employees Tracking Report [Date]:" + date + ".xls");
        Workbook workbook = new HSSFWorkbook();

        Font font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        CellStyle HeaderStyle = workbook.createCellStyle();
        HeaderStyle.setFont(font);

        for (String userK : AllUsermap.keySet()) {
            String userinfo[] = userK.split("-");
            String userinfoval[] = AllUsermap.get(userK).split("-");

            if (userinfo.length == 2 && userinfoval.length == 3) {
                Sheet sheet = workbook.createSheet(userinfoval[0]);

                // header
                Row row = sheet.createRow(0);
                Cell serial = row.createCell(0);
                serial.setCellValue("#");
                serial.setCellStyle(HeaderStyle);

                 serial = row.createCell(1);
                 serial.setCellValue("LOCATION");
                 serial.setCellStyle(HeaderStyle);

                 serial = row.createCell(2);
                 serial.setCellValue("DISTANCE");
                 serial.setCellStyle(HeaderStyle);

                 serial = row.createCell(3);
                serial.setCellValue("BATTERY");
                serial.setCellStyle(HeaderStyle);

                 serial = row.createCell(4);
                serial.setCellValue("SPEED");
                serial.setCellStyle(HeaderStyle);

                 serial = row.createCell(5);
                serial.setCellValue("GPS_ACCURACY");
                serial.setCellStyle(HeaderStyle);

                 serial = row.createCell(6);
                serial.setCellValue("STATUS");
                serial.setCellStyle(HeaderStyle);

                 serial = row.createCell(7);
                 serial.setCellType(1);
                serial.setCellValue("MOBILE_TIME");
                serial.setCellStyle(HeaderStyle);

                 serial = row.createCell(8);
                serial.setCellValue("SPEED");
                serial.setCellStyle(HeaderStyle);

                // End Header

                List<DailyReport> adr = new ArrayList<DailyReport>();
                adr = d0.seletspecificdailyreport(Integer.parseInt(userinfo[0]), date, userinfo[1]);
                if (adr.size() > 0) {
                    List<DailyReportDetails> adlrd = new ArrayList<DailyReportDetails>();
                    adlrd = d0.seletdailyreportdetails(adr.get(0).getIdDailyReport());
                    int i = 1;
                    for (DailyReportDetails drd : adlrd) {
                        row = sheet.createRow(i);
                        Cell cell = row.createCell(0);
                        cell.setCellValue(i);

                        cell = row.createCell(1);
                        cell.setCellValue(drd.getLocation());

                        cell = row.createCell(2);
                        cell.setCellValue(drd.getDistance());

                        cell = row.createCell(3);
                        cell.setCellValue(drd.getBattery());

                        cell = row.createCell(4);
                        cell.setCellValue(drd.getSpeed());

                        cell = row.createCell(5);
                        cell.setCellValue(drd.getAccuracy());

                        cell = row.createCell(6);
                        cell.setCellValue(drd.getOffline() == 1 ? "ONLINE" : "OFFLINE");

                        cell = row.createCell(7);
                        cell.setCellValue(drd.getMobiletime());

                        cell = row.createCell(8);
                        cell.setCellValue(userinfoval[2]);
                        i++;
                    }
                }
            }
        }

        workbook.write(response.getOutputStream()); // Write workbook to
                                                    // response.
        workbook.close();

    }

The upper servelet writing xcel properly but Mobile time is not in time or date format in xcel

I also have tried to put line for mobile_time column serial .

serial = row.createCell(7);
                    serial.setCellType(HSSFCell.LAST_COLUMN_NUMBER);
                    serial.setCellValue("MOBILE_TIME");
                    serial.setCellStyle(HeaderStyle);

and also tried the below line at the of generating row

cell = row.createCell(7);

                    try {
                        cell.setCellValue(new SimpleDateFormat("HH:mm:ss").parse(drd.getMobiletime()));
                    } catch (ParseException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }

But still mobile_time column is in character format .What is the proper solution?

Answer 1

You can do something like this :

 Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(0);

// Create a cell and put a date value in it.  The first cell is not styled
// as a date.
Cell cell = row.createCell(0);
cell.setCellValue(new Date());

// we style the second cell as a date (and time).  It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but other cells.
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
    createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

//you can also set date as java.util.Calendar
cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();