Delimited text or .csv to Excel using Apache POI
May 23, 2011 5 Comments
package com.hi.hiqww.jfxclient.export; import java.io.DataInputStream; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class DelimitedToXls { @SuppressWarnings("deprecation") public static void main(String args[]) throws IOException { ArrayList<ArrayList<String>> allRowAndColData = null; ArrayList<String> oneRowData = null; String fName = "C:\\input.csv"; String currentLine; FileInputStream fis = new FileInputStream(fName); DataInputStream myInput = new DataInputStream(fis); int i = 0; allRowAndColData = new ArrayList<ArrayList<String>>(); while ((currentLine = myInput.readLine()) != null) { oneRowData = new ArrayList<String>(); String oneRowArray[] = currentLine.split(";"); for (int j = 0; j < oneRowArray.length; j++) { oneRowData.add(oneRowArray[j]); } allRowAndColData.add(oneRowData); System.out.println(); i++; } try { HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("sheet1"); for (int i = 0; i < allRowAndColData.size(); i++) { ArrayList<?> ardata = (ArrayList<?>) allRowAndColData.get(i); HSSFRow row = sheet.createRow((short) 0 + i); for (int k = 0; k < ardata.size(); k++) { System.out.print(ardata.get(k)); HSSFCell cell = row.createCell((short) k); cell.setCellValue(ardata.get(k).toString()); } System.out.println(); } FileOutputStream fileOutputStream = new FileOutputStream("C:\\outputFile.xls"); workBook.write(fileOutputStream); fileOutputStream.close(); } catch (Exception ex) { } } }
Hi friends,
I have used this program…
it can handle 255 columns data…. if it exceeds means it shows like it can’t handle..
is any other packages can handle more number of columns..???
or how can use this code for 1000 columns…?
Thanks in advance
if v reading csv ,while creating xls file… all yhe numeric values will appear as string
You can create CSV Files or convert CSV to excel and vice versa by using Aspose.Cells for Java Library:
http://www.aspose.com/java/excel-component.aspx
text to excel is possible
text to excel is possible in this program???? help me