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

    }

下面是输出的第1页=>partners

使用迭代器迭代行和列

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.  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  

第3页=>附表

使用迭代器迭代行和列

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  


                                        
                                                
                                                
                                

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 = sheetIterator.next();

但是，很奇怪的是，您正在重写表。迭代器为您提供索引为零的表（这是工作簿的第一个表），其中显示了代码行：

sheet = workbook.getSheetAt(0);

当然，您的while-loop实际上总是迭代工作簿的第一个工作表（索引为零的工作表）。去掉那条坏的坏线，问题就解决了：

// ...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...