Padding zero in the last digit

Question

How to add a zero in the last digit of excel file ?

I print below value by code below

DecimalFormat df = new DecimalFormat("#.00"); 
System.out.println(s.getNetAmount());
System.out.println(df.format(s.getNetAmount()));

and the result was

691.200
691.20

But when I write it into excel file, I expect to get 691.20, but I get 691.2.

This is how I write to excel file

      public void write(List<? extends List> list) throws Exception {

            if (list != null) {
                try {
                    writeFile(header(), detail(list.get(0)));
                } catch (BatchRunException ex) {
                    throw new BatchRunException(" Fail..." + ex);
                }
            }
        }

 private void writeFile(String header, List<String> detail) throws IOException, BatchRunException {

        String fullPath = outputPath + fileNameFormat;

        File file = new File(fullPath);
        File path = new File(outputPath);

        if (!path.exists()) {
            path.mkdirs();
        }

        if (!file.exists()) {
            file.createNewFile();
        }

        try (BufferedWriter bw = new BufferedWriter(new FileWriter(file))) {
            bw.write(header);
            bw.write(NEW_LINE);

            for (String s : detail) {

                bw.write(s);
                bw.write(NEW_LINE);
            }


        } catch (IOException ex) {
            throw new BatchRunException(" Fail..." + ex);
        }
    }

    private String header() {
        StringBuilder bf = new StringBuilder();

        bf.append("Employee Name").append(SEPERATOR);
        bf.append("Amount").append(SEPERATOR);
        return bf.toString();
    }

    private List<String> detail(List<SettlementList> dList) {
        List<String> list = new ArrayList();
        BigDecimal a = new BigDecimal("0");

        for (SettlementList s : dList) {
            StringBuilder bf = new StringBuilder();

            bf.append(s.Name()).append(SEPERATOR);
            bf.append(s.getNetAmount()).append(SEPERATOR); // here the error
            list.add(bf.toString());    
        }

        return list;
    }

Excel uses binary representation of decimal values, similar to Java's `double`. But you could add numeric format to the cell. — Usagi Miyamoto, Jul 17 '17 at 08:47
@AI. you shouldn't get rid of it, you should select number/currency formatting — Philipp Sander, Jul 17 '17 at 08:48
Possible duplicate of [Keep decimal places when open CSV](https://stackoverflow.com/questions/33771112/keep-decimal-places-when-open-csv) — Alastair McCormack, Jul 17 '17 at 11:09
@AlastairMcCormack I want it display 691.20 instead of 691.2. The link you gave not what I want... — AI., Jul 17 '17 at 11:11
The program will be run by someone else, but not in my PC.... — AI., Jul 17 '17 at 11:12
Your problem is that you're generating a CSV-like file not a Excel file, hence the link. There is no way to pre-format your data in a CSV file. You will have to use a proper Excel file format writing library, as implied by @Darshan Mehta's presumptive answer — Alastair McCormack, Jul 17 '17 at 11:15
I advise you to research the difference between CSV and the various Excel formats. Then you'll understand why the answer is no — Alastair McCormack, Jul 17 '17 at 11:28

Darshan Mehta · Accepted Answer · 2017-07-17T12:54:09.287

2

It's because of Cell style and not DecimalFormat. Try setting the cell style, e.g.:

HSSFCell cell = row.createCell(0);
HSSFCellStyle style = workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
cell.setCellValue(s.getNetAmount().doubleValue());

Update

It looks like you are writing into csv file and trying to open it with xls. In this case, Excel will format the cells based on the data. So, for rows with values like 691.200, Excel will interpret these as numeric values and format it accordingly (making it 691.2). There are two ways to solve it:

Write to xls instead of csv with Apache POI library (here is an example) and apply the cell format as per the code snippet above
Write to csv with these numbers as Strings, open it in excel and apply Text formatting for corresponding columns.

edited Jul 17 '17 at 12:54

answered Jul 17 '17 at 08:57

Darshan Mehta

30,102
11
68
102

the s.getNetAmount is BigDecimal – AI. Jul 17 '17 at 09:04
These are just the example names, can you share the code that writes to xls? I will update the answer accordingly. – Darshan Mehta Jul 17 '17 at 09:19
@DarshanMehta you need to explain that the OP needs to use HSSF library to achieve what they want – Alastair McCormack Jul 17 '17 at 11:18
@AlastairMcCormack can I just format a specific cell – AI. Jul 17 '17 at 11:21
@AI. have a look at the update and explanation above – Darshan Mehta Jul 17 '17 at 12:54

score 0 · Answer 2 · answered Jul 17 '17 at 09:03

0

select your cell and right click on it , then click on format cell and do this configuration then clik ok

answered Jul 17 '17 at 09:03

Saurabh Kumar

59
1

The program will be run by someone else, not in my PC. – AI. Jul 17 '17 at 11:13

Padding zero in the last digit

2 Answers2