Pages Navigation Menu

Coding is much easier than you think

Converting XLS to CSV files Using Java

Converting XLS to CSV files Using Java

 

 
package com.simplecode.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
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;

class XlstoCSV 
{
static void xls(File inputFile, File outputFile) 
{
	// For storing data into CSV files
	StringBuffer data = new StringBuffer();
	try 
	{
	FileOutputStream fos = new FileOutputStream(outputFile);

	// Get the workbook object 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:
				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 + ",");
			}
                        
                        data.append('\n'); 
		}
	}

	fos.write(data.toString().getBytes());
	fos.close();
	}
	catch (FileNotFoundException e) 
	{
		e.printStackTrace();
	}
	catch (IOException e) 
	{
		e.printStackTrace();
	}
	}

	public static void main(String[] args) 
	{
		File inputFile = new File("C:\test.xls");
		File outputFile = new File("C:\output.csv");
		xls(inputFile, outputFile);
	}
}

 

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

 

About Mohaideen Jamil


Am currently working as a Struts 2, Webservices Developer in a reputed IT Organisations. I can help you with teaching Core java , Struts 2 and Webservices.

Java Code to Convert XLSX to CSV Files
Struts2 Configuration file and its roles

4 Comments

  1. Hi Jamil,

    thx a lot for your answer. I’ve still another problem. When in the excel file are 2 empty columns behind each other, the csv has only one empty column inside.

    For example: DATA, EMPTY, EMPTY, DATA, DATA

    in the CSV it looks like

    DATA; EMPTY; DATA; DATA

    any idea?

    Best Regards
    Thomas

    • Hi Thomas,

      Sorry for delay. I will look into this issue and let you know.

      But mean while if you have identified the problem, please share it.

      Thanks

  2. Hi Jamil,

    i tried to run your code, but i’ve the problem that the output is only in one row. Isn’t possible to make a crlf in the end of each row?

    Best Regards
    Thomas

    • Hi Thomas,

      Sorry I forgot to insert a new line character after end of each row,
      Include the line data.append(‘\n’); in the code.

      Have update the same in this article.

Leave a Reply Note To post source code in comment, use [code] [/code] tag

SimpleCodeStuffs located at , India, Chennai . Reviewed by rated: 8.8 / 10
%d bloggers like this: