How list has map values write to excel file using Apache poi

Question

I'm getting list hash map key and values like:{1=[ACSS Description1, ACSS Description2, ACSS Description3, SACSS Description4], 2=[11, 1, 4, 12]}

I would like to set excel cell values like that:

ACSS Description1      11
ACSS Description2      1
ACSS Description3      4
ACSS Description4      12

But I'm getting write excel file result like that :

     empty                 11
     empty                  1
     empty                  4
     empty                 12

But my sample snippet code always showing second column values ,first column values showing empty column .please let me know where I have made mistaken ? Thanks .

  public  void  listhashMapValues(Map<Integer,List<String>> hashmap,List<Export>list){

    List<String> listpropertvalue =new ArrayList<String>();

    for(int i=0;i<list.size();i++){  //example size is 5

    String strValue=list.get(i).getDescription();

    System.out.println(strValue);
    listpropertvalue.add(strValue);
    hashmap.put(1, listpropertvalue); 
    }
    listpropertvalue =new ArrayList<String>();

    for(int i=0;i<list.size();i++){

    String strInterValue=list.get(i).getExportIntervalId().toString();

    listpropertvalue.add(strInterValue);
    hashmap.put(2, listpropertvalue); 
    }
    int rownum =1;
    int cellnum = 0;
    for(int i=0;i<hashmap.size();i++){

    List<Integer> listMap =new ArrayList<Integer>(hashmap.keySet());

    Integer key = listMap.get(i);

    List<String> nameList = hashmap.get(key);

     for(Object obj : nameList){

     rowtitle =worksheet.createRow(rownum++); 

    celltitle =rowtitle.createCell(cellnum); 

    if (obj instanceof String){

    celltitle =rowtitle.createCell(cellnum);

    celltitle.setCellValue((String) obj);
    }
    }
}
cellnum++;
rownum=1;
}
}

My pojo class like :

@Entity

@Table(name ="T_KPI_AUTO_EXPORT_CONFIG")

public class ExportReport implements Serializable  {

private String description;

private Integer exportIntervalId;

@Column(name ="Export_Interval_Id", nullable = false)

    public Integer getExportIntervalId() {

        return exportIntervalId;
    }
    public void setExportIntervalId(Integer exportIntervalId) {

        this.exportIntervalId = exportIntervalId;
    }
    @Column(name ="Description", nullable = false)

    public String getDescription() {

        return description;
    }
    public void setDescription(String description) {

        this.description = description;
    }
}

Can you confirm that hashmap is registered with required data before it is exported to the excel by debugging? — Akash Rajbanshi
– Akash Rajbanshi, Commented May 9, 2015 at 3:06
Yes ,when I added all list data into hash maps even getting same result please let me know where I made mistaken ? — sameer
– sameer, Commented May 9, 2015 at 5:25
Thanks replied me .I have received data in List .Criteria criteria = hibernateTemplate.getSessionFactory().openSession().createCriteria(ExportReport.class); List<ExportReport> list = criteria.list(); — sameer
– sameer, Commented May 9, 2015 at 7:16

JHDev · Accepted Answer · 2015-05-09 11:31:08Z

I create a class contains methods that will help you to:

Put your data from Criteria criteria=hibernateTemplate.getSessionFactory().openSession().createCriteria(ExportReport.‌class); List<ExportReport> list = criteria.list(); to a map

Create a Xlsx file and initialize it with the number of the rows and cells needed for writing the data from map.

Write the data from map to Xlsx file

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.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

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 UtilsMethod {

    private XSSFWorkbook workbook = new XSSFWorkbook();

    /*
     * Take a Map of integer and List of string and
     *Create a Xlsx file on given path with number of row equal to size of nameList And number of Cell equal to keyset size
     */
    public void initializeExcelFile(Map<Integer,List<String>>hashmap,String path) throws IOException{

        FileOutputStream out = new FileOutputStream( new File(path));
        Set<Integer> keyset = hashmap.keySet();
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        List<String> nameList = hashmap.get(keyset.toArray()[0]);

        for(int j=1; j<nameList.size()+1;j++){
            row = sheet.createRow(j);
            if(null != row){
                for(int i=0;i<keyset.size();i++){
                    row.createCell(i);
                }
            }
        }

        workbook.write(out);
        out.close();
    }

    /*
     * Use initializeExcelFile(hashmap,path) to initialize a Xlsx file in given path 
     * After that, write the  content of hashmap into Xlsx file
     */
    public void writeToExcelfile(Map<Integer,List<String>>hashmap,String path) throws IOException{

        Set<Integer> keyset = hashmap.keySet();
        InputStream inp = new FileInputStream( new File(path));
        FileOutputStream out = new FileOutputStream( new File(path));
        int rownum = 1;
        int cellnum = 0;

        initializeExcelFile(hashmap,path);

        workbook = new XSSFWorkbook(inp);

        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;
        }

        workbook.write(out);
        out.close();
        inp.close();
    }

    public Map<Integer,List<String>> putListIntoMap(List<ExportReport>exportReports) {

        Map<Integer,List<String>> exportRep = new TreeMap<Integer, List<String>>();
        List<String> descriptions = new ArrayList<String>();
        List<String> exportIntervalIds = new ArrayList<String>();

        for(ExportReport report:exportReports){
            descriptions.add(report.getDescription());
            exportIntervalIds.add(report.getExportIntervalId().toString());
        }

        exportRep.put(1, descriptions);
        exportRep.put(2, exportIntervalIds);

        return exportRep;
    }

    public static void main(String[] args) {
        // TODO Auto-generated method stub

    }

}

And test Class for testing all UtilsMethod class' method

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public class Test {


    public static void main(String[] args) throws IOException {

        ExportReport exportReport = new ExportReport();
        exportReport.setExportIntervalId(11);
        exportReport.setDescription("ACCSDESCRIPTION1");

        ExportReport exportReport2 = new ExportReport();
        exportReport2.setExportIntervalId(1);
        exportReport2.setDescription("ACCSDESCRIPTION2");

        ExportReport exportReport3 = new ExportReport();
        exportReport3.setExportIntervalId(4);
        exportReport3.setDescription("ACCSDESCRIPTION3");

        ExportReport exportReport4 = new ExportReport();
        exportReport4.setExportIntervalId(12);
        exportReport4.setDescription("ACCSDESCRIPTION4");

        List<ExportReport> exportReports = new ArrayList<ExportReport>();

        exportReports.add(exportReport);
        exportReports.add(exportReport2);
        exportReports.add(exportReport3);
        exportReports.add(exportReport4);

        UtilsMethod utilsMethod = new UtilsMethod();

        Map<Integer,List<String>> map = utilsMethod.putListIntoMap(exportReports);
        System.out.println(map);


        utilsMethod.writeToExcelfile(map, "Writesheet.xlsx");

        System.out.println("Writesheet.xlsx written successfully" );

    }

}

After running Test class, you will get this Xlsx file

enter image description here

I'm getting error line workbook.write(out); java.io.IOException: Stream closed

N.Tholiya · Accepted Answer · 2016-06-16 05:45:52Z

HashMap Sheetmap = new HashMap();

     SheetColumnName s1 = new  SheetColumnName(1);
     s1.setFile_Name("Censtar Energy- Elect.xlsx");

     Sheetmap.put(s1, s1.setState("A7"));
     Sheetmap.put(s1, s1.setZone("B7"));

     SheetColumnName s2 = new  SheetColumnName(2);
     s2.setFile_Name("Direct Energy- Elect.xls"); 
     Sheetmap.put(s2, s2.setState("A55"));

     System.out.println(Sheetmap.get(s1));
     System.out.println(Sheetmap.get(s2));

    Collection<String> values = Sheetmap.values();

     ArrayList<String> listOfValues = new ArrayList<String>(values);

     for (String value : listOfValues) 
     {
            System.out.println(value);
            FileReader fileReader=new FileReader();
            fileReader.ProcessFile(s1.getFile_Name(),value);
     }

Collectives™ on Stack Overflow

How list has map values write to excel file using Apache poi

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related