java excel to csv file convert

Question

I am trying to convert .xlsx file to .csv, convertion is happening but the data is not formatted properly. Please find code below and suggest changes to the code.

Here I am trying to read an .xlsx file and write it to a csv file i.e. converting xlsx to csv but I am not getting the .csv file in proper format all the data is displayed in a single but it must displayed like rows in Excel.

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;

class XlstoCSV 

{

   static void xls(File inputFile, File outputFile) 

{

    // 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(0);
    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 in`enter code here`putFile = new File("C:\test.xls");
            File outputFile = new File("C:\output.csv");
            xls(inputFile, outputFile);
    }
}

Please include a sample of the input, the desired output, and what you actually get. — user1907906
– user1907906, Commented Apr 9, 2014 at 9:35
rows in excel must appear similar in csv i,e if i have 10 rows in excel i should get the 10 rows in csv but the above code is adding all ten rows in a single i want it to be added in rows in csv also — sakthi
– sakthi, Commented Apr 9, 2014 at 9:37
We need to see the output file. You would be best using a proper CSV library for serialisation as it will handle escaping strings correctly. — tom
– tom, Commented Apr 9, 2014 at 9:37

user3442100 · Accepted Answer · 2014-04-09 09:38:59Z

Try Changing your code to this your new line char has to be appended after the one row read

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'); 
}

tom · Accepted Answer · 2014-04-09 09:38:55Z

1

The problem is you are appending a new line after every cell, here:

data.append('\n');

You need to do it after every row instead.

Note also that you would be best using a proper CSV library for serialisation as it will handle escaping strings correctly.

answered Apr 9, 2014 at 9:38

tom

2,71218 silver badges28 bronze badges

Comments

Akilan · Accepted Answer · 2016-11-30 03:47:45Z

Quite a old post but will help someone.. With Multiple Sheets.

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
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;

class XlStoCSV {
    static void xls(File inputFile) {
        // Get the workbook object for XLS file
        int count = 0;
        try {

            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
                    inputFile));
            for (int l = workbook.getNumberOfSheets() - 1; l >= 0; l--) {
                File outputFile = new File(System.getProperty("user.dir")
                        + "/output/"+inputFile.getName()+"-"+workbook.getSheetName(count) +".csv");

                // For storing data into CSV files
                StringBuffer data = new StringBuffer();
                FileOutputStream fos = new FileOutputStream(outputFile);
                HSSFSheet sheet = workbook.getSheetAt(count);
                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();
                    int columnNumber = 1;
                    while (cellIterator.hasNext()) {
                        cell = cellIterator.next();
                        if (columnNumber > 1)
                        {
                            data.append(",");
                        }

                        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);
                        }
                         ++columnNumber;
                    }
                    data.append('\n');
                }

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

    public static void main(String[] args) {
        File inputFile = new File(System.getProperty("user.dir") + "/assets/"
                + "test.xls");
        xls(inputFile);
    }
}

MK-rou · Accepted Answer · 2017-11-03 14:52:20Z

Try this :

public String convertRowContentToCSV(Row row) {
        Iterator<Cell> cellIterator = row.cellIterator();
        StringBuilder data = new StringBuilder();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            switch (cell.getCellTypeEnum()) {
                case BOOLEAN:
                    data.append(cell.getBooleanCellValue()).append(",");
                    break;

                case NUMERIC:
                    data.append(cell.getNumericCellValue()).append(",");
                    break;

                case STRING:
                    data.append(cell.getStringCellValue()).append(",");
                    break;

                case BLANK:
                    data.append(",");
                    break;

                case FORMULA:
                case _NONE:
                case ERROR:
                    break;

                default:
                    data.append(cell).append(",");
            }

        }
        return data.toString();
    }

Abdullah · Accepted Answer · 2019-07-15 09:47:29Z

Try this code:

import java.util.*;
import java.util.Map.Entry;
import java.util.concurrent.TimeoutException;
import java.util.logging.Logger;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;

public class App {

    public void convertExcelToCSV(Sheet sheet, String sheetName) {
        StringBuffer data = new StringBuffer();
        try {
            FileOutputStream fos = new FileOutputStream("C:\\Users\\" + sheetName + ".csv");
            Cell cell;
            Row row;

            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    cell = cellIterator.next();

                    CellType type = cell.getCellTypeEnum();
                    if (type == CellType.BOOLEAN) {
                        data.append(cell.getBooleanCellValue() + ",");
                    } else if (type == CellType.NUMERIC) {
                        data.append(cell.getNumericCellValue() + ",");
                    } else if (type == CellType.STRING) {
                        data.append(cell.getStringCellValue() + ",");
                    } else if (type == CellType.BLANK) {
                        data.append("" + ",");
                    } else {
                        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)
    {
        App app = new App();
        String path =  "C:\\Users\\myFile.xlsx";
        InputStream inp = null;
        try {
            inp = new FileInputStream(path);
            Workbook wb = WorkbookFactory.create(inp);

            for(int i=0;i<wb.getNumberOfSheets();i++) {
                System.out.println(wb.getSheetAt(i).getSheetName());
                app.convertExcelToCSV(wb.getSheetAt(i),wb.getSheetAt(i).getSheetName());
            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        } 
        finally {
            try {
                inp.close();
            } catch (Exception ex) {
                System.out.println(ex.getMessage());
            }
        }
    }
}

Collectives™ on Stack Overflow

java excel to csv file convert

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related