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

 

dwd2
Download It – ExcelToCSV

 

3 Comments

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

  2. Nice one