Delimited text or .csv to Excel using Apache POI

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) {
   }
 }
}

About Richa Jain
Project Manager. Former - Scrum Master and Java / J2EE Developer. Areas of Interest : Estimations, Planning, execution and Delivery of Agile/waterfall based projects, People Management, Risk Management, Tech Design Reviews, Automation and innovations

5 Responses to Delimited text or .csv to Excel using Apache POI

  1. vidyasekar says:

    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

  2. anees says:

    if v reading csv ,while creating xls file… all yhe numeric values will appear as string

  3. Pamela says:

    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

  4. Ananth says:

    text to excel is possible

  5. Ananth says:

    text to excel is possible in this program???? help me

Leave a comment