Java code to convert xlsx & xls to csv
images (1)
Note : This Program uses XSSFWorkbook for xlsx and HSSFWorkbook for xlx.
package com.simplecode.excel; import java.io.*; import java.util.Iterator; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; class ExcelToCSV { static void convertToXlsx(File inputFile, File outputFile) { // For storing data into CSV files StringBuffer cellValue = new StringBuffer(); try { FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLSX file XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile)); // Get first sheet from the workbook XSSFSheet sheet = wb.getSheetAt(0); Row row; Cell cell; // Iterate through each rows from first sheet IteratorrowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator
cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellValue.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellValue.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellValue.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellValue.append("" + ","); break; default: cellValue.append(cell + ","); } } } fos.write(cellValue.toString().getBytes()); fos.close(); } catch (Exception e) { System.err.println("Exception :" + e.getMessage()); } } static void convertToXls(File inputFile, File outputFile) { // For storing data into CSV files StringBuffer cellDData = new StringBuffer(); try { FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile)); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); Cell cell; Row row; // Iterate through each rows from first sheet Iterator | rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator
cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellDData.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellDData.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellDData.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellDData.append("" + ","); break; default: cellDData.append(cell + ","); } } } fos.write(cellDData.toString().getBytes()); fos.close(); } catch (FileNotFoundException e) { System.err.println("Exception" + e.getMessage()); } catch (IOException e) { System.err.println("Exception" + e.getMessage()); } } public static void main(String[] args) { File inputFile = new File("C:\\input.xls"); File outputFile = new File("C:\\output1.csv"); File inputFile2 = new File("C:\\input.xlsx"); File outputFile2 = new File("C:\\output2.csv"); convertToXls(inputFile, outputFile); convertToXlsx(inputFile2, outputFile2); } } |
To execute the above code you must have the following libraries
- dom4j-1.1.jar
- poi-3.7-20101029.jar
- poi-ooxml-3.7-20101029.jar
- poi-ooxml-schemas-3.7-20101029.jar
- xmlbeans-2.3.0.jar
|
// Get the workbook instance for XLSX file
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));
Hi I am getting error in above line though I have added all the required jars.
Tha input parameter for XSSFWORKBOOK is not recognized.
Please help.
Thanks
may be you are not including the “poi-ooxml-3.7-20101029.jar” and if included please make sure that
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
both are imported..
Nice one