java excel to csv file convert

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

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.

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

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

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