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
Can you provide a converter in java that can convert any xls file depending upon the headers in it
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..?
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.
Lots Of Thanksssssssss…
Hi Jamil the sheet.iterator() method is not available in HSSFSheet class .please check it
Hi Mate,
I think to use this portion of code.
Thanks for share.
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.
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
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
Thankyou :)
you are welcome ^_^