CODEWITHSUNDEEP
apache-poi
reddyvaribabu
reddyvaribabu

Reputation: 113

CSV generation possible with Apache POI?

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

Upvotes: 9

Views: 58421

Answers (4)

Denis Abakumov
Denis Abakumov

Reputation: 422

An improved and tested version of gene b's response is this:

/**
 * Saves all rows from a single Excel sheet in a workbook to a CSV file.
 *
 * @param excelWorkbook path to the Excel workbook.
 * @param sheetNumber   sheet number to export.
 * @param csvFile       CSV file path for output.
 * @throws IOException if failed to read the Excel file or create/write to a CSV file.
 */
public static void excelToCsv(String excelWorkbook, int sheetNumber, String csvFile) throws IOException {
    try (Workbook workbook = WorkbookFactory.create(new File(excelWorkbook), null, true); // Read-only: true
         BufferedWriter writer = new BufferedWriter(new FileWriter(csvFile));
         CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT)) {
        Sheet sheet = workbook.getSheetAt(sheetNumber);
        DataFormatter format = new DataFormatter();
        for (Row row : sheet) {
            for (int c = 0; c < row.getLastCellNum(); c++) {
                // Null cells returned as blank
                Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                String cellValue = format.formatCellValue(cell);
                csvPrinter.print(cellValue);
            }
            csvPrinter.println();
        }
        csvPrinter.flush();
    }
}

The following improvements were made:

  1. NullPointerException won't be thrown if a cell in an Excel Row was never edited. A blank value will be written to the CSV instead.
  2. Excel values are rendered using DataFormatter allowing the CSV to match the visual representation of the Excel sheet.
  3. try-with-source used for auto-close of the file objects.
  4. The workbook is opened in the read-only mode.

Upvotes: 1

gokhansari
gokhansari

Reputation: 2439

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

Upvotes: 10

gene b.
gene b.

Reputation: 11984

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

Upvotes: 1

gregshap
gregshap

Reputation: 154

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

Upvotes: 12

Related Questions