Excel does not display zero decimal when opening an exported CSV file

Question

I am trying to export a CSV file with a wrong content type so it gets opened in Excel. When the value is 2.0 from object, in Excel sheet it displays only 2. It just omits the decimal value. It works fine when the value is 2.1, 2.2, etc.

The problem comes when it is 2.0, 3.0.

    StringBuffer sb= new StringBuffer();
        sb.append("2.0");

        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext()
                .getResponse();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=Test.csv");
        response.getOutputStream().write(sb.toString().getBytes(Charset.forName("UTF-8")));
        response.getOutputStream().flush();
        response.getOutputStream().close();
        FacesContext.getCurrentInstance().responseComplete();

How is this caused and how can I solve it?

Answer 1

You can use this code:

   sb.append("\"2.0\"");

I often use even more verbose, to force text format:

   sb.append("=\"2.0\"");

(see https://stackoverflow.com/a/165052/476791 )

To verify, that this solution works open notepad, enter ="2.0", save it as Test.csv on your Desktop and double click it.

Answer 2

The problem has to do with rendering in excel rather than java.

On your excel document,

Right click on the cell > Format Cells > Select 'Number' from category

You'll see the number in decimal format.