列表如何有地图值写入使用Apache POI到excel文件 [英] How list has map values write to excel file using Apache poi

问题描述

我收到名单哈希映射键和像值： {1 = [ACSS说明1，说明2 ACSS，ACSS Description3，SACSS Description4]，2 = 11，1，4，12]}

我想设置这样的Excel单元格的值：

  ACSS说明1 11
ACSS说明2 1
ACSS Description3 4
ACSS Description4 12
 

但是我却越来越擅长写这样的文件结果是：

  11的空
     空1
     空4
     空12
 

  

但我的示例代码段code总是显示第二列的值，第一
  显示空列的列值。请让我知道我有
  发错了？谢谢你。

 公共无效listhashMapValues​​（地图＆LT;整数，列表与LT;弦乐＆GT;＆GT; HashMap中，列表与LT;出口＆GT;名单）{    清单＆LT;串GT; listpropertvalue =新的ArrayList＆LT;串GT;（）;    的for（int i = 0; I＆LT;则为list.size（）;我++）{//例如大小为5    串strValue中= list.get（ⅰ）.getDescription（）;    的System.out.println（strValue中）;
    listpropertvalue.add（strValue中）;
    hashmap.put（1，listpropertvalue）;
    }
    listpropertvalue =新的ArrayList＆LT;串GT;（）;    的for（int i = 0; I＆LT;则为list.size（）;我++）{    。字符串strInterValue = list.get（I）.getExportIntervalId（）的toString（）;    listpropertvalue.add（strInterValue）;
    hashmap.put（2，listpropertvalue）;
    }
    INT ROWNUM = 1;
    INT cellnum = 0;
    的for（int i = 0; I＆LT; hashmap.size（）;我++）{    清单＆LT;整数GT;列表地图=新的ArrayList＆LT;整数GT;（hashmap.keySet（））;    整数键= listMap.get（I）    清单＆LT;串GT; NAMELIST = hashmap.get（键）;     对于（obj对象：名称列表）{     rowtitle = worksheet.createRow（ROWNUM ++）;    celltitle = rowtitle.createCell（cellnum）;    如果（OBJ的instanceof字符串）{    celltitle = rowtitle.createCell（cellnum）;    celltitle.setCellValue（（字符串）目标文件）;
    }
    }
}
cellnum ++;
ROWNUM = 1;
}
}
 

我的POJO类，如：

  @Entity@Table（NAME =T_KPI_AUTO_EXPORT_CONFIG）公共类ExportReport实现Serializable {私人字符串描述;私人整数exportIntervalId;@Column（NAME =Export_Interval_Id，可为空= FALSE）    公共整数getExportIntervalId（）{        返回exportIntervalId;
    }
    公共无效setExportIntervalId（整数exportIntervalId）{        this.exportIntervalId = exportIntervalId;
    }
    @Column（name =说明，可为空= FALSE）    公共字符串getDescription（）{        返回描述;
    }
    公共无效setDescription（字符串描述）{        this.description =描述;
    }
}
 

解决方案

我创建一个类包含的方法，这将有助于您：

从把你的数据标准criteria=hibernateTemplate.getSessionFactory().openSession().createCriteria(ExportReport.‌​class);清单＆LT; ExportReport＆GT;清单= criteria.list（）; 到地图

创建XLSX文件，并与所需要的从地图写入数据的行和细胞的数目初始化

从地图将数据写入XLSX档案

 进口的java.io.File;
进口java.io.FileInputStream中;
进口java.io.FileOutputStream中;
进口java.io.IOException异常;
进口的java.io.InputStream;
进口的java.util.ArrayList;
进口的java.util.List;
进口的java.util.Map;
进口java.util.Set中;
进口java.util.TreeMap中;进口org.apache.poi.ss.usermodel.Cell;
进口org.apache.poi.xssf.usermodel.XSSFRow;
进口org.apache.poi.xssf.usermodel.XSSFSheet;
进口org.apache.poi.xssf.usermodel.XSSFWorkbook;
公共类UtilsMethod {    私人XSSFWorkbook工作簿=新XSSFWorkbook（）;    / *
     *以一个地图整数和字符串的列表和
     *具有行等于名称列表的大小和细胞等于键集大小数数给出创建路径上的XLSX文件
     * /
    公共无效initializeExcelFile（地图＆LT;整数，列表与LT;弦乐＆GT;＆GT;的HashMap，字符串路径）抛出IOException        FileOutputStream中出=新的FileOutputStream（新文件（路径））;
        SET＆LT;整数GT;键集= hashmap.keySet（）;
        XSSFSheet片= workbook.createSheet（）;
        XSSFRow排= NULL;
        清单＆LT;串GT; NAMELIST = hashmap.get（keyset.toArray（）[0]）;        为（诠释J = 1; J＆下; nameList.size（）+ 1; J ++）{
            行= sheet.createRow（J）;
            如果（NULL！=行）{
                的for（int i = 0; I＆LT; keyset.size（）;我++）{
                    row.createCell（ⅰ）;
                }
            }
        }        workbook.write（出）;
        out.close（）;
    }    / *
     *使用initializeExcelFile（HashMap中，路径）在给定的路径初始化文件XLSX
     *之后，写的HashMap的内容到XLSX文件
     * /
    公共无效writeToExcelfile（地图＆LT;整数，列表与LT;弦乐＆GT;＆GT;的HashMap，字符串路径）抛出IOException        SET＆LT;整数GT;键集= hashmap.keySet（）;
        InputStream的INP =新的FileInputStream（新文件（路径））;
        FileOutputStream中出=新的FileOutputStream（新文件（路径））;
        INT ROWNUM = 1;
        INT cellnum = 0;        initializeExcelFile（HashMap中，路径）;        工作簿=新XSSFWorkbook（INP）;        XSSFSheet片= workbook.getSheetAt（0）;        对于（整数键：键集）{
            清单＆LT;串GT; NAMELIST = hashmap.get（键）;
            对于（一个String：名称列表）{
                XSSFRow行= sheet.getRow（ROWNUM ++）;
                电池单元= row.getCell（cellnum）;
                如果（NULL！=细胞）{
                    cell.setCellValue（多个）;
                }
            }
            cellnum ++;
            ROWNUM = 1;
        }        workbook.write（出）;
        out.close（）;
        inp.close（）;
    }    公共地图＆LT;整数，列表与LT;弦乐＆GT;＆GT; putListIntoMap（列表＆LT; ExportReport＆GT; exportReports）{        地图＆LT;整数，列表与LT;弦乐＆GT;＆GT; exportRep =新TreeMap的＆LT;整数，列表与LT;弦乐＆GT;＆GT;（）;
        清单＆LT;串GT;说明=新的ArrayList＆LT;串GT;（）;
        清单＆LT;串GT; exportIntervalIds =新的ArrayList＆LT;串GT;（）;        对于（ExportReport报告：exportReports）{
            descriptions.add（report.getDescription（））;
            exportIntervalIds.add（report.getExportIntervalId（）的toString（））;
        }        exportRep.put（1，说明）;
        exportRep.put（2，exportIntervalIds）;        返回exportRep;
    }    公共静态无效的主要（字串[] args）{
        // TODO自动生成方法存根    }}
 

和测试类测试所有UtilsMethod类的方法

 进口java.io.IOException异常;
进口的java.util.ArrayList;
进口的java.util.List;
进口的java.util.Map;
公共类的测试{
    公共静态无效的主要（字串[] args）抛出IOException        ExportReport exportReport =新ExportReport（）;
        exportReport.setExportIntervalId（11）;
        exportReport.setDescription（ACCSDESCRIPTION1）;        ExportReport exportReport2 =新ExportReport（）;
        exportReport2.setExportIntervalId（1）;
        exportReport2.setDescription（ACCSDESCRIPTION2）;        ExportReport exportReport3 =新ExportReport（）;
        exportReport3.setExportIntervalId（4）;
        exportReport3.setDescription（ACCSDESCRIPTION3）;        ExportReport exportReport4 =新ExportReport（）;
        exportReport4.setExportIntervalId（12）;
        exportReport4.setDescription（ACCSDESCRIPTION4）;        清单＆LT; ExportReport＆GT; exportReports =新的ArrayList＆LT; ExportReport＆GT;（）;        exportReports.add（exportReport）;
        exportReports.add（exportReport2）;
        exportReports.add（exportReport3）;
        exportReports.add（exportReport4）;        UtilsMethod utilsMethod =新UtilsMethod（）;        地图＆LT;整数，列表与LT;弦乐＆GT;＆GT;地图= utilsMethod.putListIntoMap（exportReports）;
        的System.out.println（图）
        utilsMethod.writeToExcelfile（地图Writesheet.xlsx）;        的System.out.println（Writesheet.xlsx成功写入）;    }}
 

运行测试类后，你会得到这个XLSX文件

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

解决方案

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

这篇关于列表如何有地图值写入使用Apache POI到excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！