converting excel to csv in java

1import java.io.File;
2import java.io.FileInputStream;
3import java.io.FileOutputStream;
4import java.util.Iterator;
5
6import org.apache.commons.io.FilenameUtils;
7import org.apache.poi.hssf.usermodel.HSSFWorkbook;
8import org.apache.poi.ss.usermodel.Cell;
9import org.apache.poi.ss.usermodel.Row;
10import org.apache.poi.ss.usermodel.Sheet;
11import org.apache.poi.ss.usermodel.Workbook;
12import org.apache.poi.xssf.usermodel.XSSFWorkbook;
13
14public class XlsxtoCSV {
15
16    static void xlsx(File inputFile, File outputFile) {
17        // For storing data into CSV files
18        StringBuffer data = new StringBuffer();
19
20        try {
21            FileOutputStream fos = new FileOutputStream(outputFile);
22            // Get the workbook object for XLSX file
23            FileInputStream fis = new FileInputStream(inputFile);
24            Workbook workbook = null;
25
26            String ext = FilenameUtils.getExtension(inputFile.toString());
27
28            if (ext.equalsIgnoreCase("xlsx")) {
29                workbook = new XSSFWorkbook(fis);
30            } else if (ext.equalsIgnoreCase("xls")) {
31                workbook = new HSSFWorkbook(fis);
32            }
33
34            // Get first sheet from the workbook
35
36            int numberOfSheets = workbook.getNumberOfSheets();
37            Row row;
38            Cell cell;
39            // Iterate through each rows from first sheet
40
41            for (int i = 0; i < numberOfSheets; i++) {
42                Sheet sheet = workbook.getSheetAt(0);
43                Iterator<Row> rowIterator = sheet.iterator();
44
45                while (rowIterator.hasNext()) {
46                    row = rowIterator.next();
47                    // For each row, iterate through each columns
48                    Iterator<Cell> cellIterator = row.cellIterator();
49                    while (cellIterator.hasNext()) {
50
51                        cell = cellIterator.next();
52
53                        switch (cell.getCellType()) {
54                        case Cell.CELL_TYPE_BOOLEAN:
55                            data.append(cell.getBooleanCellValue() + ",");
56
57                            break;
58                        case Cell.CELL_TYPE_NUMERIC:
59                            data.append(cell.getNumericCellValue() + ",");
60
61                            break;
62                        case Cell.CELL_TYPE_STRING:
63                            data.append(cell.getStringCellValue() + ",");
64                            break;
65
66                        case Cell.CELL_TYPE_BLANK:
67                            data.append("" + ",");
68                            break;
69                        default:
70                            data.append(cell + ",");
71
72                        }
73                    }
74                    data.append('\n'); // appending new line after each row
75                }
76
77            }
78            fos.write(data.toString().getBytes());
79            fos.close();
80
81        } catch (Exception ioe) {
82            ioe.printStackTrace();
83        }
84    }
85
86    // testing the application
87
88    public static void main(String[] args) {
89        // int i=0;
90        // reading file from desktop
91        File inputFile = new File(".//src//test//resources//yourExcel.xls"); //provide your path
92        // writing excel data to csv
93        File outputFile = new File(".//src//test//resources//yourCSV.csv");  //provide your path
94        xlsx(inputFile, outputFile);
95        System.out.println("Conversion of " + inputFile + " to flat file: "
96                + outputFile + " is completed");
97    }
98}
99