如何使用Java中的Apache POI从数据库将数据导入excel文件

I have a table, from that table I'm retrieving the data into a list. Now I need to fill that data into an excel file using Apache POI in java. see the below image

我有一张表,从那张表中我将数据检索到一个列表中。现在我需要使用Java中的Apache POI将该数据填充到excel文件中。见下图

Like the above image i need to fill the data into excel file

像上面的图像我需要将数据填充到excel文件中

Now my problem is how fill that data into excel file. I was tried some code, but its not working for me.

现在我的问题是如何将数据填充到excel文件中。我尝试了一些代码,但它不适合我。

Here is my code.

这是我的代码。

    ReportUtils rutils = new ReportUtils();
            List<CreateJobOrderBean>  list = rutils.getDailyreports(year); 
            int r = 10;
            int c = 0;
            for(int i=1; i<list.size();i++){
                row=spreadsheet.createRow(r); 

                cell1 = row.createCell(c);

                cell1.setCellValue(list.get(i).getSno());
                if(list.get(i).getJobCreatesOn() != null){
                    cell1.setCellValue(list.get(i).getJobCreatesOnasString());
                }
                if(list.get(i).getSurveydate() != null){
                    cell1.setCellValue(list.get(i).getSurveydateasString());
                }

                cell1.setCellValue(list.get(i).getQsemail());
                cell1.setCellValue(list.get(i).getLocation());
                cell1.setCellValue(list.get(i).getNatureofcase());
                if(list.get(i).getDol() != null){
                    cell1.setCellValue(list.get(i).getDolOnasString());
                }

                cell1.setCellValue(list.get(i).getPolicyinfo());
                cell1.setCellValue(list.get(i).getSuminsured());
                cell1.setCellValue(list.get(i).getAdjusterco());
                cell1.setCellValue(list.get(i).getAdjustername());
                cell1.setCellValue(list.get(i).getRemarks());

                style2 = workbook.createCellStyle();
                style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
                style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
                cell1.setCellStyle(style2);
                font = workbook.createFont();//Create font
                spreadsheet.setColumnWidth(c,8000);
                font.setFontHeightInPoints((short) 14);
                style2.setFont(font);
                font.setColor(HSSFColor.RED.index);
                font.setBold(true);
                style2.setFont(font);
            }

The above code is working fine, but the data not inserting into the corresponding cells.

上面的代码工作正常,但数据没有插入相应的单元格。

2 个解决方案

#1

You insert/overwrite all data in the same cell (cell1). Instead, you have to create a new cell per column:

您插入/覆盖同一单元格(cell1)中的所有数据。相反,您必须为每列创建一个新单元格:

            row=spreadsheet.createRow(r); 
            cell1 = row.createCell(c);
            cell1.setCellValue(list.get(i).getSno());
            cell1 = row.createCell(++c); // or however you want to track the column index
            cell1.setCellValue(list.get(i).getQsemail());
            cell1 = row.createCell(++c);
            ... and so on

As a matter of style: I'd rather not reuse the cell1 variable in reality. Declare them on the fly, that makes the code clearer:

作为一种风格问题:我宁愿不在现实中重复使用cell1变量。动态声明它们,使代码更清晰:

            Cell cell1 = row.createCell(c);
            cell1.setCellValue(list.get(i).getSno());
            Cell cell2 = row.createCell(++c);
            cell2.setCellValue(list.get(i).getQsemail());
            Cell cell3 = row.createCell(++c);
            ...

Note that you'll have to reset the c variable for the column index in each iteration of the loop.

请注意,您必须在循环的每次迭代中重置列索引的c变量。

#2

You need to increment c values after inserting one cell values.

插入一个单元格值后,需要增加c值。

#1