CODEWITHSUNDEEP
javaexcelcsvapache-poixlsx
Tharani
Tharani

Reputation: 141

How to convert an xlsx (Microsoft Excel) file to csv?

Currently I am using below code to convert XLSX file to CSV using Java. I need a faster solution because this is too slow.

public class Test1 {
    static void convert(File inputFile, File outputFile) {
        try {
            FileOutputStream fos = new FileOutputStream(outputFile);
            // Get the workbook object for XLSX file
            XSSFWorkbook wBook = new XSSFWorkbook(
                    new FileInputStream(inputFile));
            // Get first sheet from the workbook
            XSSFSheet sheet = wBook.getSheetAt(0);
            Row row;
            Cell cell;
            // 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 + ",");

                    }
                }
            }

            fos.write(data.toString().getBytes());
            fos.close();

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

    // testing the application

    public static void main(String[] args) {
        // reading file from desktop
        File inputFile = new File("D:\\Test.xlsx");
        // writing excel data to csv
        File outputFile = new File("D:\\Test1.csv");
        convert(inputFile, outputFile);
    }
}

Upvotes: 10

Views: 71429

Answers (3)

OrangeDog
OrangeDog

Reputation: 38816

The text extractors will dump a TSV of the entire workbook. Performance depends on the implementation chosen and your memory availability.

You can then pipe that into a CSVPrinter to get correct CSV output. I don't think Excel cells can ever contain tab characters, so this should be safe. If you have newlines in your cells I'm not sure whether the TSV output will be valid, but if it is you can use a CSVParser to read it instead of lines().

XSSFWorkbook input = new XSSFWorkbook(new File("input.xlsx"));
CSVPrinter output = new CSVPrinter(new FileWriter("output.csv"), CSVFormat.DEFAULT); 

String tsv = new XSSFExcelExtractor(input).getText();
BufferedReader reader = new BufferedReader(new StringReader(tsv));
reader.lines().map(line -> line.split("\t").forEach(output::printRecord);

If buffering the whole thing as a String is too inefficient, copy the implementation of one of the event-based extractors and write directly to the CSVPrinter instead.

Upvotes: 8

anurag
anurag

Reputation: 65

Simple way to convert xls/xlsx into csv by using apache POI.

public class XLSXToCSVConverter {

    public InputStream convertxlstoCSV(InputStream inputStream) throws IOException, InvalidFormatException {

        Workbook wb = WorkbookFactory.create(inputStream);

        return  csvConverter(wb.getSheetAt(0));
    }

    private InputStream csvConverter(Sheet sheet) {
        Row row = null;
        String str = new String();
        for (int i = 0; i < sheet.getLastRowNum()+1; i++) {
            row = sheet.getRow(i);
            String rowString = new String();
            for (int j = 0; j < 3; j++) {
                if(row.getCell(j)==null) {
                    rowString = rowString + Utility.BLANK_SPACE + Utility.COMMA;
                }
                else {
                    rowString = rowString + row.getCell(j)+ Utility.COMMA;
                }
            }
            str = str + rowString.substring(0,rowString.length()-1)+ Utility.NEXT_LINE_OPERATOR;
        }
        System.out.println(str);
        return new ByteArrayInputStream(str.getBytes(StandardCharsets.UTF_8));
    }
}

Hope this will help you , Thanks.

Upvotes: 1

Hari Krishna
Hari Krishna

Reputation: 67

Try using POI API. Here is the reference link

Java - Apache POI - Convert XLS/XLSX to CSV

Upvotes: 0

Related Questions