Pages Navigation Menu

Coding is much easier than you think

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

 

14 Comments

  1. Can you provide a converter in java that can convert any xls file depending upon the headers in it

  2. hi jamil.,
    I have a situation of sending email with attachment of xls file.. I need to attach a file but i generated a hssworkbook. I need to convert it as a File .. How can i acheive this..?

  3. Hi Jamil,

    this is abhinav, i have run the above program the same to convert operation but i am getting the errors as below
    Error(10,37): package org.apache.poi.hssf.usermodel does not exist
    Error(11,37): package org.apache.poi.hssf.usermodel does not exist
    Error(12,35): package org.apache.poi.ss.usermodel does not exist
    Error(13,35): package org.apache.poi.ss.usermodel does not exist

    pls help me out.

  4. Lots Of Thanksssssssss…

  5. Hi Jamil the sheet.iterator() method is not available in HSSFSheet class .please check it

  6. Hi Mate,
    I think to use this portion of code.
    Thanks for share.

  7. Hi Jamil,
    I tried to convert an excel document (which has 2 columns) to a word document with using apache poi.Could you help me about this?
    Thanks.

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

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

      • the line:

        data.append(‘\n’);

        is in the wrong place, put it two line after actual position ^_^

        p.s.: thanks