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

The POI part looks okay - although I would go for for-loops for (int rowNum = ... instead. Can you give current output vs dwsired? — Jan
– Jan, Commented Dec 3, 2015 at 12:19
the current output getting like this... Abc Abc Bnf addr1 Abc phone1 Abc Entry USer1 Abc Sndr Addr1 — Syed Ziaula Huq
– Syed Ziaula Huq, Commented Dec 3, 2015 at 12:21
just now added this code in DataExp class its taking to much time to test the class...Object obj = PropertyUtils.getProperty(givenList.get(rowFlag), fieldName); for(int i=0;i<5;i++){ for(int j=0;j<columnFlag;j++){ if(obj!=null){ cell.setCellValue(obj.toString()); } } — Syed Ziaula Huq
– Syed Ziaula Huq, Commented Dec 3, 2015 at 12:23
edit your question when you update your code,and the new results, if its helped, and you should incude your expexed outcome, and actual outcome, in the question too not in the comments. — dave
– dave, Commented Dec 3, 2015 at 12:26
I would present a smaller code example that leaves everything out which is not necessary for the error to occur. — NoDataDumpNoContribution
– NoDataDumpNoContribution, Commented Dec 3, 2015 at 12:28

Jan · Accepted Answer · 2015-12-07 09:15:47Z

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();

edited Dec 7, 2015 at 9:15

answered Dec 3, 2015 at 13:36

Jan

13.9k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Jan Over a year ago

Please ask a new question and show (in a short verifyable example) what you have so far.

Syed Ziaula Huq Over a year ago

i want to insert Xyz values in next row

Jan Over a year ago

But? Whats not working? Show example / update your code please.

Syed Ziaula Huq Over a year ago

everything working fine but in my test class i have mention one more list values was not getting in next row

Jan Over a year ago

Ah - you don't reset column flag back to 0 for every row. So columns keep getting written further and further right. Is that what you see? If yes -> move columnFlag = 0 into the while(rowFlag... loop

|

Collectives™ on Stack Overflow

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

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related