How to convert the cell output in POI into a text file in java

Question

I am reading a excel file which has contains some informations in it, i am able to read that excel file using apache POI. And i am able to print the cell values to the console.But my requirement is that i have to convert the output to a text file. I am giving the code which i have done so far ,

public class TestReadExcel {

public static void main(String[] args) throws IOException {
    Writer writer = null;
    try {

    String excelFilePath = "D:\\resources\\myExcel.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet firstSheet = workbook.getSheetAt(0);
    File file = new File("D:\\writedatasamp.txt");  
    writer = new BufferedWriter(new FileWriter(file));
    Iterator<Row> iterator = firstSheet.iterator();

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

            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue());
                    writer.write(cell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    writer.write(String.valueOf(cell.getBooleanCellValue()));
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue());
                    writer.write(String.valueOf(cell.getNumericCellValue()));
                    break;
            }
            System.out.print(" - ");
        }
        System.out.println();
    }

    workbook.close();
    inputStream.close();

 }catch(Exception e) {
     e.printStackTrace();
 }finally {
     writer.close();
 }
}

  }

The output is coming to the console , but i have to generate a text file of the output how i can do that ? please help

There are many examples of writing to a file, such as [this](https://stackoverflow.com/questions/2885173/how-do-i-create-a-file-and-write-to-it-in-java). So instead of (or in addition to) using `println()`, write the cell's value to the file. — Andrew S, Feb 07 '18 at 15:45
@AndrewS i have also tried with your option but i am getting blank file — Mandrek, Feb 07 '18 at 16:00
Why not use or extend one of the example Excel to CSV programs that Apache POI ships with? — Gagravarr, Feb 07 '18 at 16:06
@Gagravarr i have edited my question , the problem is i am getting data in the text file but i am getting missing data like the headers are missing ,But i am getting complete data in the console — Mandrek, Feb 07 '18 at 16:13
Can anyone tell why the data is coming file in the console but not in the text file ?? — Mandrek, Feb 07 '18 at 16:48
You have two System.out.print() statements which are not followed by writer.write() statements. If you want the exact same text to appear in the file as appears in the console, you will need to have equivalent write statements for every proint statement. — Wee Shetland, Feb 08 '18 at 14:39

score 1 · Answer 1 · answered Feb 08 '18 at 18:25

I have modified your code a bit. Cell data which you read now gets appended to StringBuilder, and at the end, string which we built, gets written into text file in only two lines of code.

public class TextFileWriter {

public static void main(String[] args){
    StringBuilder sb = new StringBuilder();
    try {

    String excelFilePath = "D:\\resources\\myExcel.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet firstSheet = workbook.getSheetAt(0);
    File file = new File("D:\\writedatasamp.txt");  
    Iterator<Row> iterator = firstSheet.iterator();

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellType()) {
                case STRING:
                    System.out.print(cell.getStringCellValue());
                    sb.append(cell.getStringCellValue() + ", ");
                    break;
                case BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    sb.append(String.valueOf(cell.getBooleanCellValue() + ", "));
                    break;
                case NUMERIC:
                    System.out.print(cell.getNumericCellValue());
                    sb.append(String.valueOf(cell.getNumericCellValue() + ", "));
                    break;
                case FORMULA:
                    System.out.print(cell.getCellFormula());
                    sb.append(String.valueOf(cell.getCellFormula() + ", "));
                    break;
                default :
                    System.out.print(cell.getStringCellValue());
                    sb.append(cell.getStringCellValue() + ", ");
            }
            System.out.print(" - ");
        }
        System.out.println();
        sb.append("\n");
    }
    workbook.close();
    inputStream.close();
    Path path = Paths.get("D:\\writedatasamp.txt");
    Files.write(path, Arrays.asList(sb.toString()));

 }catch(Exception e) {
     e.printStackTrace();
 }
}

}

How to convert the cell output in POI into a text file in java

1 Answers1