CSV generation possible with Apache POI?

Question

I need to generate csv files and I stumbled on a module in our project itself which uses Apache POI to generate excel sheets aleady. So I thought I could use the same to generate csv. So I asked google brother, but he couldnt find anything for sure that says Apache POI can be used for CSV file generation. I was checking on the following api too and it only talks about xls sheets and not csv anywhere. Any ideas?

http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Workbook.html

Answer 1

Apache Poi will not output to CSV for you. However, you have a couple good options, depending on what kind of data you are writing into the csv.

If you know that none of your cells will contain csv markers such as commas, quotes, line endings, then you can loop through your data rows and copy the text into a StringBuffer and send that to regular java IO. Here is an example of writing an sql query to csv along those lines: Poi Mailing List: writing CSV

Otherwise, rather than figure out how to escape the special characters yourself, you should check out the opencsv project

Answer 2

If you check official web site Apache POI, you can find lots of example there. There is also an example that shows how you can have csv formatted output by using apache POI.

ToCSV example

Answer 3

Basic strategy:

1) Apache Commons CSV is the standard library for writing CSV values.

2) But we need to loop through the Workbook ourselves, and then call Commons CSV's Printer on each cell value, with a newline at the end of each row. Unfortunately this is custom code, it's not automatically available in XSSF. But it's easy:

        // In this example we construct CSVPrinter on a File, can also do an OutputStream
        Reader reader = Files.newBufferedReader(Paths.get(SAMPLE_CSV_FILE_PATH));
        CSVPrinter csvPrinter = new CSVPrinter(reader, CSVFormat.DEFAULT);              

        if (workbook != null) {
            XSSFSheet sheet = workbook.getSheetAt(0); // Sheet #0
            Iterator<Row> rowIterator = sheet.rowIterator();
            while (rowIterator.hasNext()) {               
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    csvPrinter.print(cell.getStringCellValue()); // Call Commons CSV here to print
                }
                // Newline after each row
                csvPrinter.println();
            }

        }
        // at the end, close and flush CSVPrinter
        csvPrinter.flush();
        csvPrinter.close();