Pages Navigation Menu

Coding is much easier than you think

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
	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:
			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<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:
		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

 

dwd2
Download It – ExcelToCSV

 

About Gokul


I am very much interested in android and Wicket framework. A core android developer and working in android native app development and responsive web mobile design. I have also worked in wicket fame work and java web development. I will keep on updating you about android and wicket framework and answer your query.

  • nivedita kumari

    // 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

    • Saood Alam

      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..

  • Abcd

    Nice one