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 IteratorrowIterator = 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 ^_^