3
  • Hi,I'm getting exact values from hash map but my Apache POI Row and Cell cannot set values properly expected result like that please let me know.Thanks

I'm getting result hash-map like that:

{1=[ACSS Description1, ACSS Description2, ACSS Description3, SACSS Description4], 2=[11, 1, 4, 12]}

I'm expecting:

![enter image description here](https://i.sstatic.net/nTnMX.png)

I'm getting result based on below code :

enter image description here

And that's my code:

   public void getList(List<ExportReport> listcriteria)
    {
        Map<Integer, List<String>> hashmap = new HashMap<Integer , List<String>>();
        List<String> listpropertyvalue =new ArrayList<String>();
        for(int i=0; i < listcriteria.size(); i++)
        {
            String strValue =listcriteria.get(i).getDescription();
            listpropertyvalue.add(strValue);
            hashmap.put(1, listpropertyname); 
        }
           listpropertyvalue =new ArrayList<String>();
            for(int i=0;i<listcriteria.size();i++){

                String strInterValue=listcriteria.get(i).getExportIntervalId().toString();
                listpropertyvalue.add(strInterValue);
                hashmap.put(2, listpropertvalue); 
            }
    }
    Set<Integer> keyset = hashmap.keySet();
    int rownum = 1;
  int cellnum = 0

    for(Integer key : keyset){

        Row row = worksheet.createRow(rownum++);

        Cell cell = row.createCell(cellnum);
        List<String> nameList = hashmap.get(key);
        for(Object obj : nameList)
        {
            if(obj instanceof Date)
            {
                cell.setCellValue((Date) obj);
            }
            else if(obj instanceof Boolean)
            {
                cell.setCellValue((Boolean) obj);
            }
            else if(obj instanceof String)
            {
                cell.setCellValue((String) obj);
            }
            else if(obj instanceof Double)
            {
                 cell.setCellValue((Double) obj);
            }
        }
           cellnum++;
            rownum=1;
    }
}

What am I doing wrong?

Improve this question
2
  • 1
    could you please show a snippet of excel file that you expected Commented May 7, 2015 at 8:12
  • Just now I have updated my post .please let me know is there any other way .Thanks Commented May 7, 2015 at 10:04

2 Answers 2

Reset to default
1

Sorry for this late reply.

Using you code, I created a working program that initialize a Excel file Writesheet.xlsx with 5 rows each row contains 5 Cells. Also, I created a List that contains 5 strings. Then I used getList(List<ExportReport> listcriteria) method to write the content of this List on Writesheet.xlsx

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class Test {


    static XSSFWorkbook workbook = new XSSFWorkbook();

    public void getList(List<String> listcriteria){
        Map<Integer, List<String>> hashmap = new HashMap<Integer , List<String>>();
        //create 5 key value pairs 
        for(int i=0; i < 5; i++){
            hashmap.put(i, listcriteria); 
        }
        System.out.println("hashmap : "+hashmap);
        Set<Integer> keyset = hashmap.keySet();
        int rownum = 0;
        int cellnum = 0;
        XSSFSheet sheet = workbook.getSheetAt(0);
        rownum = 0;
        for(Integer key : keyset){
            List<String> nameList = hashmap.get(key);
            for(String s : nameList){
                XSSFRow row = sheet.getRow(rownum++);
                Cell cell = row.getCell(cellnum);
                if(null!=cell){
                    cell.setCellValue(s);
                }
            }
            cellnum++;
            rownum=0;
        }
    }

    public static void main(String[] args) throws IOException {
        //Creation of List from an Array to test getList Method
        String[] ss = {"a","b","c","d","e"};
        List<String> listcriteria = new ArrayList<String>();
        listcriteria.addAll(Arrays.asList(ss));
        /***********************************************************/

        Test t = new Test();
        // Because I put 5 key values pairs in hashmap (see getList method), I create  Writesheet.xlsx 
        // file that contains 5 rows each row contains 5 cell
        FileOutputStream out = new FileOutputStream( new File("Writesheet.xlsx"));
        XSSFSheet sheet = workbook.createSheet();
        for(int i = 0;i<5;i++){
            XSSFRow row = sheet.createRow(i);
            for(int j=0;j<5;j++)
            row.createCell(j);
        }
        workbook.write(out);
        out.close();//end creation of Excel file

        // I open Writesheet.xlsx file and write the data on it
        InputStream inp = new FileInputStream( new File("Writesheet.xlsx"));
        workbook = new XSSFWorkbook(inp);
        // listcriteria contains the data that will be written it on  Writesheet.xlsx
        t.getList(listcriteria);
        out = new FileOutputStream( new File("Writesheet.xlsx"));
        workbook.write(out);
        out.close();
        inp.close();
        System.out.println("Writesheet.xlsx written successfully" );

    }

}
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for replied me .above code if I have change variable to value 1 it's working fine i don't want to use hardcode ? hashmap.put(i, listcriteria);
Thanks please look at my post now I have updated again as per expect result needed please let me know where I have made mistaken again ?
0

I just change the code on void getList(List<String> listcriteria) so the data

{1=[ACSS Description1, ACSS Description2, ACSS Description3, SACSS Description4], 2=[11, 1, 4, 12]}

will be put it on hashmap.The rest of code still the same and Voilà you get on Writesheet.xlsx what you want.

Map<Integer, List<String>> hashmap = new HashMap<Integer , List<String>>();
        String[] data1 = {"ACSS Description1", "ACSS Description2", "ACSS Description3", "SACSS Description4"};
        List s1 = Arrays.asList(data1);
        hashmap.put(1,s1); 
        String[] data2 = {"11", "1", "4", "12"};
        List s2 = Arrays.asList(data2);
        hashmap.put(2,s2); 

        System.out.println("hashmap : "+hashmap);
    //the rest of code it is the same

All the code with Change

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class Test {


    static XSSFWorkbook workbook = new XSSFWorkbook();

    public void getList(List<String> listcriteria){
        Map<Integer, List<String>> hashmap = new HashMap<Integer , List<String>>();
        String[] data1 = {"ACSS Description1", "ACSS Description2", "ACSS Description3", "SACSS Description4"};
        List s1 = Arrays.asList(data1);
        hashmap.put(1,s1); 
        String[] data2 = {"11", "1", "4", "12"};
        List s2 = Arrays.asList(data2);
        hashmap.put(2,s2); 

        System.out.println("hashmap : "+hashmap);
        Set<Integer> keyset = hashmap.keySet();
        int rownum = 1;
        int cellnum = 0;
        XSSFSheet sheet = workbook.getSheetAt(0);
        for(Integer key : keyset){
            List<String> nameList = hashmap.get(key);
            for(String s : nameList){
                XSSFRow row = sheet.getRow(rownum++);
                Cell cell = row.getCell(cellnum);
                if(null!=cell){
                    cell.setCellValue(s);
                }
            }
            cellnum++;
            rownum=1;
        }
    }

    public static void main(String[] args) throws IOException {
        //Creation of List from an Array to test getList Method
        String[] ss = {"a","b","c","d","e"};
        List<String> listcriteria = new ArrayList<String>();
        listcriteria.addAll(Arrays.asList(ss));
        /***********************************************************/

        Test t = new Test();
        // Because I put 5 key values pairs in hashmap (see getList method), I create  Writesheet.xlsx 
        // file that contains 5 rows each row contains 5 cell
        FileOutputStream out = new FileOutputStream( new File("Writesheet.xlsx"));
        XSSFSheet sheet = workbook.createSheet();
        for(int i = 0;i<5;i++){
            XSSFRow row = sheet.createRow(i);
            for(int j=0;j<5;j++)
            row.createCell(j);
        }
        workbook.write(out);
        out.close();//end creation of Excel file

        // I open Writesheet.xlsx file and write the data on it
        InputStream inp = new FileInputStream( new File("Writesheet.xlsx"));
        workbook = new XSSFWorkbook(inp);
        // listcriteria contains the data that will be written it on  Writesheet.xlsx
        t.getList(listcriteria);
        out = new FileOutputStream( new File("Writesheet.xlsx"));
        workbook.write(out);
        out.close();
        inp.close();
        System.out.println("Writesheet.xlsx written successfully" );

    }

}

And here the result enter image description here

Improve this answer

5 Comments

Thanks replied me same way I have done but my write excel file only showing cell[1] values(11,1,4,2) not cell[0] values are empty .
Thanks replied , yes exactly same as .
As per referencelinkI have tried but still expected result I cannot get it .
Sorry, I am Little confused. Do you want the result like you ask in your first post or it is a new question. Because if it is a new one please create a new post
please click new post link

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.