java - Excel中的Outputdata在SpringBoot中无法正确迭代

Question

所以我试图在springboot中读取一个excel文件，excel文件包含10张，代码成功地迭代了所有的表，但是除了第一张表之外，行标题和单元格数据不正确，即倒数第二张表正在使用第一张表信息

输出也没有很好的安排，有没有办法让它干净

下面是代码

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.IOException;
import java.util.Iterator;


@RestController
@RequestMapping("datafile")
public class DataController {

    @RequestMapping(value = "getdata", method = RequestMethod.GET)
    public void createBus() throws IOException {


        final String SAMPLE_XLSX_FILE_PATH = "C:\\project\\transita\\src\\main\\resources\\transita.xlsx";


        // Creating a Workbook from an Excel file (.xls or .xlsx)
        Workbook workbook;

        {
            try {
                workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));



                // Retrieving the number of sheets in the Workbook
                System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");





                Iterator<Sheet> sheetIterator = workbook.sheetIterator();
                System.out.println("Retrieving Sheets using Iterator");
                while (sheetIterator.hasNext()) {
                    Sheet sheet = sheetIterator.next();
                    System.out.println("=> " + sheet.getSheetName());

                    sheet = workbook.getSheetAt(0);

                    // Create a DataFormatter to format and get each cell's value as String
                    DataFormatter dataFormatter = new DataFormatter();

                    // 1. You can obtain a rowIterator and columnIterator and iterate over them
                    System.out.println("\n\nIterating over Rows and Columns using Iterator\n");
                    Iterator<Row> rowIterator = sheet.iterator();
                    while (rowIterator.hasNext()) {
                        Row row = rowIterator.next();

                        // Now let's iterate over the columns of the current row
                        Iterator<Cell> cellIterator = row.cellIterator();

                        while (cellIterator.hasNext()) {
                            Cell cell = cellIterator.next();
                            String cellValue = dataFormatter.formatCellValue(cell);
                            System.out.print(cellValue + "\t");
                        }
                        System.out.println();
                    }

                    try {
                        workbook.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }
        }

    }

下面是输出的第一张表 => 合作伙伴

使用迭代器迭代行和列

partner_id  partner_code    partner_name    partner_logo    partner_address partner_telephone   partner_email   partner_website External    Agency Oportunity   Search  sell    partner_id  
001 ABC ABC Transport       Km 5 MCC Uratta Rd, Owerri, Imo State -1111 2348139862090, 0700222872678    [e-mail]   https://www.abctransport.com    TRUE    TRUE    FALSE   FALSE   A4  
002 CHT Chisco Transport Ltd        104, Funsho Williams Avenue, Iponri, Surulere, .    0816517669, 08089273799, 08113798985    [e-mail]  https://www.chiscotransport.com.ng  TRUE    TRUE    FALSE   FALSE   A5  
003 LIB Libra Motors NIgeria Ltd        Cele Okota Road
Lagos Nigeria   09031565022 [e-mail] www.libmot.com  TRUE    TRUE    FALSE   FALSE   A6  
004 GIG GIGM Ltd        20 Ikorodu Express Road, Jibowu, Lagos.  08139851110    [e-mail].   https://gigm.com/   TRUE    FALSE   FALSE   FALSE       A7  
005 GUO GUO     Jibowu street along ikorodu express, Jibowu, Lagos. 2348144988273   [e-mail]   https://www.guotransport.com    TRUE    FALSE   FALSE   FALSE   A8  

第二张 => p_policies

使用迭代器迭代行和列

partner_id  partner_code    partner_name    partner_logo    partner_address partner_telephone   partner_email   partner_website External    Agency Oportunity   Search  sell    partner_id  
001 ABC ABC Transport       Km 5 MCC Uratta Rd, Owerri, Imo State -1111 2348139862090, 0700222872678    [e-mail]   https://www.abctransport.com    TRUE    TRUE    FALSE   FALSE   A4  
002 CHT Chisco Transport Ltd        104, Funsho Williams Avenue, Iponri, Surulere.  0816517669, 08089273799, 08113798985    [e-mail]  https://www.chiscotransport.com.ng  TRUE    TRUE    FALSE   FALSE   A5  
003 LIB Libra Motors NIgeria Ltd        Cele Okota Road
Lagos Nigeria   09031565022 [e-mail] www.libmot.com  TRUE    TRUE    FALSE   FALSE   A6  
004 GIG GIGM Ltd        20 Ikorodu Express Road, Jibowu, Lagos.  08139851110    [e-mail].   https://gigm.com/   TRUE    FALSE   FALSE   FALSE       A7  
005 GUO GUO     Jibowu street along ikorodu express, Jibowu, Lagos. 2348144988273   [e-mail]   https://www.guotransport.com    TRUE    FALSE   FALSE   FALSE   A8  

第三张 => 时间表

使用迭代器迭代行和列

partner_id  partner_code    partner_name    partner_logo    partner_address partner_telephone   partner_email   partner_website External    Agency Oportunity   Search  sell    partner_id  
001 ABC ABC Transport       Km 5 MCC Uratta Rd, Owerri, Imo State -1111 2348139862090, 0700222872678    [e-mail]   https://www.abctransport.com    TRUE    TRUE    FALSE   FALSE   A4  
002 CHT Chisco Transport Ltd        104, Funsho Williams Avenue, Iponri, Surulere, L.   0816517669, 08089273799, 08113798985    [e-mail]  https://www.chiscotransport.com.ng  TRUE    TRUE    FALSE   FALSE   A5  
003 LIB Libra Motors NIgeria Ltd        Cele Okota Road
Lagos Nigeria   09031565022 [e-mail] www.libmot.com  TRUE    TRUE    FALSE   FALSE   A6  
004 GIG GIGM Ltd        20 Ikorodu Express Road, Jibowu, Lagos.  08139851110    [e-mail].   https://gigm.com/   TRUE    FALSE   FALSE   FALSE       A7  
005 GUO GUO     Jibowu street along ikorodu express, Jibowu, Lagos. 2348144988273   [e-mail]   https://www.guotransport.com    TRUE    FALSE   FALSE   FALSE   A8  


                                        
                                                
                                                
                                

标签： javaexcelspring-bootspring-mvcapache-poi

Answer 1

问题出在您发布的代码的第一部分，即：

// ...other stuff...

Iterator<Sheet> sheetIterator = workbook.sheetIterator();
System.out.println("Retrieving Sheets using Iterator");
while (sheetIterator.hasNext()) {
     Sheet sheet = sheetIterator.next();
     System.out.println("=> " + sheet.getSheetName());

     sheet = workbook.getSheetAt(0);

     // Create a DataFormatter to format and get each cell's value as String
     DataFormatter dataFormatter = new DataFormatter();

// ...other stuff...

您正在正确使用工作表迭代Sheet器，在使用以下行的同时从迭代器中检索一个新的：

Sheet sheet = sheetIterator.next();

但是，然后，神秘地，您覆盖了Sheet迭代器，为您提供了Sheetat 索引零（这是您的第一个索引Workbook）以及指定的代码行：

sheet = workbook.getSheetAt(0);

所以当然你的while循环实际上总是迭代你的第一个 （索引为零）。删除那个坏坏行，问题就解决了：SheetWorkbookSheet

// ...other stuff...

Iterator<Sheet> sheetIterator = workbook.sheetIterator();
System.out.println("Retrieving Sheets using Iterator");
while (sheetIterator.hasNext()) {
     Sheet sheet = sheetIterator.next();
     System.out.println("=> " + sheet.getSheetName());

     // sheet = workbook.getSheetAt(0); <-- BAD, BAD LINE!

     // Create a DataFormatter to format and get each cell's value as String
     DataFormatter dataFormatter = new DataFormatter();

// ...other stuff...