Convert .xls to csv file using apache poi

Question

I have convert .xls to csv file.my code is woring fine.but one issue,I was facing.like some cell value has (xxx,Md) these cell value should consider one value but take two column.how to rectify the problem here ,I have attached my code.

try
{
    FileOutputStream fos = new FileOutputStream(outputFile);
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
    workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
    HSSFSheet sheet = workbook.getSheetAt(0);

    for(int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++)
    {
        Cell cell=null;
        Row row = null;

        int previousCell = -1;
        int currentCell = 0;
        row = sheet.getRow(rowIndex);

        for(int colIndex=row.getFirstCellNum(); colIndex < row.getLastCellNum(); colIndex++)
        {
            cell = row.getCell(colIndex);
            currentCell = cell.getColumnIndex();

            /* Cell processing starts here*/
            System.out.println("coll"+colIndex);

            switch (cell.getCellType())
            {
                case Cell.CELL_TYPE_BOOLEAN:
                    cellDData.append(cell.getBooleanCellValue() + ",");
                    System.out.println("boo"+ cell.getBooleanCellValue());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell))
                    {
                        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
                        String  strCellValue = dateFormat.format(cell.getDateCellValue());
                        cellDData.append(strCellValue +",");
                    }
                    else 
                    {
                        System.out.println(cell.getNumericCellValue());
                        Double value = cell.getNumericCellValue();
                        Long longValue = value.longValue();
                        String strCellValue1 = new String(longValue.toString());
                        cellDData.append(strCellValue1 +",");
                    }
                    break;

                case Cell.CELL_TYPE_STRING:
                    String out=cell.getRichStringCellValue().getString();
                    cellDData.append(cell.getRichStringCellValue().getString() + ",");
                    System.out.println("string"+cell.getStringCellValue());
                    break;

                case Cell.CELL_TYPE_BLANK:
                    cellDData.append("" +",");
                    System.out.print("THIS IS BLANK");
                    break;

                default:
                    break;
            }
        }
    }
}

Check this answer http://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file — luanjot, May 21 '14 at 11:26

Hirak · Answer 1 · 2014-05-21T11:46:38.070

1

I could think of the following two options:

Change the separator from COMMA to something else (PIPE maybe ?)
Wrap your strings into inverted commas ("")

Sample code ...

case Cell.CELL_TYPE_STRING:
                String out=cell.getRichStringCellValue().getString();
                if(out.contains(",")) {
                    out = "\""+out+"\"";
                }
                cellDData.append(out + ",");
                System.out.println("string"+out);
                break;

P.S: You may use Strnigbuilder instead of string concatenation

edited May 21 '14 at 11:46

answered May 21 '14 at 11:31

Hirak

3,601
1
22
33

how to do that can you please elobrate – sakthi May 21 '14 at 11:33
Which option are you unsure about? – Hirak May 21 '14 at 11:35
string type only face the problem – sakthi May 21 '14 at 11:39

score 1 · Answer 2 · edited Oct 07 '21 at 06:16

1

You can write your own code, but I would advise to use a library like SuperCSV or OpenCSV

For CSV itself, there is RFC-4180. This RFC defines what 'proper' CSV should look like; it tells you how and what to quote and escape

edited Oct 07 '21 at 06:16

Community

1
1

answered May 21 '14 at 11:48

Rob Audenaerde

19,195
10
76
121

score 0 · Answer 3 · edited Oct 07 '21 at 13:31

0

According to RFC-4180, Each field may or may not be enclosed in double quotes. So, if you enclose, each cell value within double quotes, comma's in between will not be considered as delimiters. If you are following this approach, make sure to escape any double quotes appearing inside a field by preceding it with another double quote.

edited Oct 07 '21 at 13:31

Community

1
1

answered Feb 21 '19 at 17:00

guru shankar

1

Convert .xls to csv file using apache poi

3 Answers3

Linked