Write data to xls,xlsx,csv file in java using apache POI

Question

I am having a problem here while writing some data to an Excel sheet by using Apache POI. My code is given below...

public class DataExp implements Serializable {
private static final Logger logger = Logger.getLogger(DataExp.class);
public static File exportToCsv(List<WebInOutTrx> givenList, List<DataExpTmplt> dataTmpltList) throws IOException {
        String url = "D:\\";
        File file = new File(url);
        String csv = "D:\\Output.csv";

        CSVWriter csvWriter = new CSVWriter(new FileWriter(csv), ',');

        List<String[]> data = toStringArray(givenList, dataTmpltList);

        csvWriter.writeAll(data);
        csvWriter.close();
        System.out.println(csv);
        return file;
    }

    private static List<String[]> toStringArray(List<WebInOutTrx> givenList,        
          List<DataExpTmplt> dataTmpltList) throws FileNotFoundException {

        List<String[]> records = new ArrayList<String[]>();

        records.add(new String[]{"sndrName", "noteToBnf", 
                   "sndrPhone","entryUser","sndrAdd"});

        Iterator<WebInOutTrx> it = givenList.iterator();

        while (it.hasNext()) {
            WebInOutTrx trx = it.next();

            records.add(new String[]{trx.getSndrName(),trx.getNoteToBnf(),trx.getSndrPhone(),
                    trx.getEntryUser(),trx.getSndrAdd()});
        }

        return records;                  
    }                     

    public static File exportToXLS(List<WebInOutTrx> givenList,
         List<DataExpTmplt> dataTmpltList) throws IOException, SQLException,     ClassNotFoundException, IllegalAccessException, InvocationTargetException,    NoSuchMethodException {

        String url = "D:\\";
        File file = new File(url);
        WebService iWeb = new WebService();
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Sample sheet");

        int rowFlag = 0;
    int rowCount=givenList.size();
    int columnCount=dataTmpltList.size();
    int columnFlag=0;
    while (rowFlag != rowCount) {
         Row row = sheet.createRow(rowFlag);         

         Iterator<WebInOutTrx> it = givenList.iterator();


         while (columnFlag != columnCount) {
            String fieldName = dataTmpltList.get(columnFlag).getFieldName();
            Cell cell = row.createCell(columnFlag);
            WebInOutTrx tempObj = givenList.get(rowFlag);


            Object obj = PropertyUtils.getProperty(givenList.get(rowFlag),    fieldName);
            if (obj != null) {
                cell.setCellValue(obj.toString());
            }

            columnFlag++;

        }

        rowFlag++;

    }

    FileOutputStream out = new FileOutputStream(new File("D:\\Result.xls"));
    {
        wb.write(out);
        out.close();
    }

    return file;
}                 
}

And here's my test class:

public class DataExpTest {

    public DataExpTest() {
    }
    private static WebService webService;
    private static List<WebInOutTrx> webInOutList=new ArrayList<WebInOutTrx>();
    private DataExp dataExp = new DataExp();
    private List<DataExpTmplt> dataTmpltList = new ArrayList<DataExpTmplt>  ();

    @BeforeClass
    public static void setUpClass() {
         WebInOutTrx temp=new WebInOutTrx();
        temp.setSndrName("Abc");
        temp.setNoteToBnf("Abc Bnf addr");
        temp.setSndrPhone("Abc phone");
        temp.setEntryUser("Abc Entry USer");
        temp.setSndrAdd("Abc Sndr Addr");
        webInOutList.add(temp);

        temp.setSndrName("Abc");
        temp.setNoteToBnf("Abc Bnf addr1");
        temp.setSndrPhone("Abc phone1");
        temp.setEntryUser("Abc Entry USer1");
        temp.setSndrAdd("Abc Sndr Addr1");
        webInOutList.add(temp);                                   
    }

    @AfterClass
    public static void tearDownClass() {
    }

    @Before
    public void setUp() {                        
       dataTmpltList.add(new DataExpTmplt(1, "sndrName", "String"));
        dataTmpltList.add(new DataExpTmplt(2, "noteToBnf", "String"));
        dataTmpltList.add(new DataExpTmplt(3, "sndrPhone", "String"));
        dataTmpltList.add(new DataExpTmplt(4, "entryUser", "String"));
        dataTmpltList.add(new DataExpTmplt(5, "sndrAdd", "String"));
     }

    @After
    public void tearDown() {
    }


    @Test
    public void testGetWebList() {
        assertFalse(webInOutList.isEmpty());    
    }

    @Test
    public void testCSVExport() throws IOException {
        assertNotNull(DataExp.exportToCsv(webInOutList,dataTmpltList));
    }

    @Test
    public void testXLSExport() throws IOException, SQLException, ClassNotFoundException {
        try {
            assertNotNull(DataExp.exportToXLS(webInOutList, dataTmpltList));
        } catch (IllegalAccessException ex) {
            System.out.println(ex);
        } catch (InvocationTargetException ex) {
            System.out.println(ex);
        } catch (NoSuchMethodException ex) {
            System.out.println(ex);
        }
    }
    @Test
    public void testXLSXExport() throws IOException, SQLException, ClassNotFoundException {
        try {
            assertNotNull(DataExp.exportToXLSX(webInOutList, dataTmpltList));
        } catch (IllegalAccessException ex) {
            System.out.println(ex);
        } catch (InvocationTargetException ex) {
            System.out.println(ex);
        } catch (NoSuchMethodException ex) {
            System.out.println(ex);
        }
    }

}

this is my template class

public class DataExpTmplt implements Serializable {
   public static final long serialVersionUID = 1L;
   private Integer coloumnIndex;
    private String fieldName;
    private String fieldType;
    List<DataExpTmplt> importTemplate;

    public List<DataExpTmplt> getImportTemplate() {
        return importTemplate;
    }

    public void setImportTemplate(List<DataExpTmplt> importTemplate) {
        this.importTemplate = importTemplate;
    }
    public DataExpTmplt(){

     }
    public DataExpTmplt(Integer cIndex,String fName,String fType){
        this.coloumnIndex=cIndex;
        this.fieldName=fName;
        this.fieldType=fType;
    }

    public Integer getColoumnIndex() {
        return coloumnIndex;
    }
       public Integer getPOIColumnIndex(){
        int temp=coloumnIndex-1;
        return temp;
    }

    public void setColoumnIndex(Integer coloumnIndex) {
        this.coloumnIndex = coloumnIndex;
    }

    public String getFieldName() {
        return fieldName;
    }

    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }

    public String getFieldType() {
        return fieldType;
    }

    public void setFieldType(String fieldType) {
        this.fieldType = fieldType;
    }

    public DataExpTmplt getTypesAtColumnIndex(Integer colIndex){
        if(importTemplate==null||importTemplate.isEmpty()){
            return null;
        }
        for (DataExpTmplt currentTemplateVO : importTemplate) {
            if(currentTemplateVO.getPOIColumnIndex().equals(colIndex)){
                return currentTemplateVO;
            }
        }
        return null;
    }
}

Desired Output

        Abc AbcBnfaddr  Abcphone    AbcEntryUser    AbcSndrAddr 

        Xyz XyzBnfaddr1 Xyzphone1   XyzEntryUSer1   XyzSndrAddr1                        

i want output should be like this please help me out in this
thanking you advance..:

Can someone please help me on this or provide some code snippet? Thanks

Answer 1

I think I found it:

In your test, you add the same object twice, causing the same line to be produced twice in your XLS. As you just call the setters a 2nd time, you change the values of the object already passed into the list as well. See my correction:

@BeforeClass
public static void setUpClass() {
    WebInOutTrx temp=new WebInOutTrx();
    temp.setSndrName("Abc");
    temp.setNoteToBnf("Abc Bnf addr");
    temp.setSndrPhone("Abc phone");
    temp.setEntryUser("Abc Entry USer");
    temp.setSndrAdd("Abc Sndr Addr");
    webInOutList.add(temp);
    //Missing in your code
    WebInOutTrx temp=new WebInOutTrx();
    //End Missing
    temp.setSndrName("Abc");
    temp.setNoteToBnf("Abc Bnf addr1");
    temp.setSndrPhone("Abc phone1");
    temp.setEntryUser("Abc Entry USer1");
    temp.setSndrAdd("Abc Sndr Addr1");
    webInOutList.add(temp);                                   
}

This should in fact create two different test rows. If you want to match your desired output as given in the Question (which I reformatted a bit), you'd need to change to:

@BeforeClass
public static void setUpClass() {
    WebInOutTrx temp=new WebInOutTrx();
    temp.setSndrName("Abc");
    temp.setNoteToBnf("Abc Bnf addr");
    temp.setSndrPhone("Abc phone");
    temp.setEntryUser("Abc Entry USer");
    temp.setSndrAdd("Abc Sndr Addr");
    webInOutList.add(temp);
    //Missing in your code
    WebInOutTrx temp=new WebInOutTrx();
    //End Missing
    temp.setSndrName("Xyz ");
    temp.setNoteToBnf("Xyz Bnf addr1");
    temp.setSndrPhone("Xyz phone1");
    temp.setEntryUser("Xyz Entry USer1");
    temp.setSndrAdd("Xyz Sndr Addr1");
    webInOutList.add(temp);                                   
}

About the column/row counter

// int columnFlag=0; //too early here
while (rowFlag != rowCount) {
     Row row = sheet.createRow(rowFlag);

     int columnFlag=0; //here it belongs - reset columns for every row

     Iterator<WebInOutTrx> it = givenList.iterator();
     while (columnFlag != columnCount) {
        String fieldName = dataTmpltList.get(columnFlag).getFieldName();