从Excel工作表JAVA获取特定数据

I am trying to get specific data from an excel sheet, The data is dynamic. It can be anything really. The column headers are the only things i can use for placeholders, but the column header positions can vary in the sheet.

我试图从Excel工作表中获取特定数据,数据是动态的。它可以是真的。列标题是我可以用于占位符的唯一内容,但列标题位置可能在工作表中有所不同。

For example i have a sheet like this :

例如,我有一个这样的表:

|Name| Surname| Value|

|名称|姓|值|

|bar | poo | 5|

|酒吧|便便| 5 |

|baz | foo | 7|

|巴兹| foo | 7 |

But for example i need to traverse the sheet to get the surname column and then if i find surname = 'poo' i must then pull its corresponding value which in the sheet is in the next colum but this is dynamic. The surname and value column arent always next to each other, they can be in any position at the top. But if i find a specific 'thing' in the surname column i need to pull its value.

但是例如我需要遍历工作表以获取姓氏列,然后如果我找到surname ='poo',那么我必须拉出其相应的值,这在表中是在下一列中,但这是动态的。姓氏和价值列并不总是彼此相邻,它们可以位于顶部的任何位置。但如果我在姓氏列中找到一个特定的“东西”,我需要提取它的价值。

I have managed to traverse through the sheet and store all the data in a 2d array And display that data. from the research ive done , this isnt an efficient approach as traversing and storing large data from sheets can use alot of memory. Ive read that you can read through an excel sheet and instead of storing those values in an array you can write them immediately to another sheet, if they match a certain condition. EG: (pseudo) If(columnheader == surname && surname == foo )then get corresponding value, then write that value to a new sheet.

我已设法遍历工作表并将所有数据存储在二维数组中并显示该数据。从我所做的研究来看,这不是一种有效的方法,因为遍历和存储来自工作表的大量数据可以使用大量内存。我已经读过你可以通过excel表读取,而不是将这些值存储在一个数组中,如果它们符合某个条件,你可以立即将它们写入另一个工作表。 EG :(伪)If(columnheader == surname && surname == foo)然后获取相应的值,然后将该值写入新工作表。

Okay so my questions are :

好的,我的问题是:

1.How do i achieve iterating through the sheet not storing it in an array and writing it straight to another sheet if it matches a condition?

1.如何匹配条件,我如何实现在不将数据存储在数组中并将其直接写入另一个工作表的迭代?

2.From the code i have below, how do i achieve sorting through the data in the array and finding if surname = foo get its corresponding value?

2.从下面的代码中,我如何实现对数组中数据的排序,并查找surname = foo是否得到相应的值?

Like i said the data in the sheet is dynamic except for the column headers, but there positions as headers are dynamic.

就像我说的那样,表格中的数据是动态的,除了列标题,但是标题的位置是动态的。

Sorry for the long post , any help will be greatly appreciated.

对不起,很长的帖子,任何帮助将不胜感激。

package demo.poi;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class test  {

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

        File excel = new File("test.xlsx");
        FileInputStream fis = new FileInputStream(excel);

        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet ws = wb.getSheetAt(0);
        ws.setForceFormulaRecalculation(true);

        int rowNum = ws.getLastRowNum() + 1;
        int colNum = ws.getRow(0).getLastCellNum();
        int surnameHeaderIndex = -1, valueHeaderIndex = -1;

        //Read the headers first. Locate the ones you need
        XSSFRow rowHeader = ws.getRow(0);
        for (int j = 0; j < colNum; j++) {
            XSSFCell cell = rowHeader.getCell(j);
            String cellValue = cellToString(cell);
            if("SURNAME".equalsIgnoreCase(cellValue)) {
                surnameHeaderIndex = j;
            } else if("VALUE".equalsIgnoreCase(cellValue)) {
                valueHeaderIndex = j;
            }
        }

        if(surnameHeaderIndex == -1 || valueHeaderIndex == -1) {
            throw new Exception("Could not find header indexes\nSurname : " + surnameHeaderIndex + " | Value : " + valueHeaderIndex);
        }
        //createnew workbook
        XSSFWorkbook workbook = new XSSFWorkbook(); 		
		//Create a blank sheet
		XSSFSheet sheet = workbook.createSheet("data");
		
        for (int i = 1; i < rowNum; i++) {
            XSSFRow row = ws.getRow(i);
           row = sheet.createRow(rowNum++);
            String surname = cellToString(row.getCell(surnameHeaderIndex));
            String value = cellToString(row.getCell(valueHeaderIndex));
            int cellIndex = 0;
            row.createCell(cellIndex++).setCellValue(surname);
            row.createCell(cellIndex++).setCellValue(value);
            

            
        }
        FileOutputStream fos = new FileOutputStream(new File("test1.xlsx"));
        workbook.write(fos);
        fos.close();
    }

    public static String cellToString(XSSFCell cell) {

        int type;
        Object result = null;
        type = cell.getCellType();

        switch (type) {

        case XSSFCell.CELL_TYPE_NUMERIC:
            result = BigDecimal.valueOf(cell.getNumericCellValue())
                    .toPlainString();

            break;
        case XSSFCell.CELL_TYPE_STRING:
            result = cell.getStringCellValue();
            break;
        case XSSFCell.CELL_TYPE_BLANK:
            result = "";
            break;
        case XSSFCell.CELL_TYPE_FORMULA:
            result = cell.getCellFormula();
        }

        return result.toString();
    }
}

1 个解决方案

#1

Something like this should be a good starting point. Basically you parse the first row, where the headers are located. You find the position of the headers you want and keep them. In this example there are only two headers (surname, value) that are needed so I just keep two variables. If there are more, then the solution would be to keep the position of those headers in a HashMap, where the key is the name of the header. After that an iteration of the rows begins. The program parses the values of the columns that are needed (row.getCell(index)). Now you have the values that you need, and only them. You can do whatever you want, you can print them or write a file or whatnot.

这样的事情应该是一个很好的起点。基本上你解析标题所在的第一行。您可以找到所需标题的位置并保留它们。在这个例子中只有两个标题(姓氏,值),所以我只保留两个变量。如果还有更多,那么解决方案是将这些头的位置保持在HashMap中,其中键是头的名称。之后,行的迭代开始。该程序解析所需列的值(row.getCell(index))。现在您拥有了所需的值,并且只有它们。你可以做任何你想做的事,你可以打印它们或写一个文件或诸如此类的东西。

Here is an example. The error handling is up to you. This is only an example.

这是一个例子。错误处理取决于您。这只是一个例子。

package POIParser;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class MainPoi {

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

        File excel = new File("test.xlsx");
        FileInputStream fis = new FileInputStream(excel);

        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet ws = wb.getSheetAt(0);
        ws.setForceFormulaRecalculation(true);

        int rowNum = ws.getLastRowNum() + 1;
        int colNum = ws.getRow(0).getLastCellNum();
        int surnameHeaderIndex = -1, valueHeaderIndex = -1;

        // Read the headers first. Locate the ones you need
        XSSFRow rowHeader = ws.getRow(0);
        for (int j = 0; j < colNum; j++) {
            XSSFCell cell = rowHeader.getCell(j);
            String cellValue = cellToString(cell);
            if ("SURNAME".equalsIgnoreCase(cellValue)) {
                surnameHeaderIndex = j;
            } else if ("VALUE".equalsIgnoreCase(cellValue)) {
                valueHeaderIndex = j;
            }
        }

        if (surnameHeaderIndex == -1 || valueHeaderIndex == -1) {
            throw new Exception("Could not find header indexes\nSurname : "
                    + surnameHeaderIndex + " | Value : " + valueHeaderIndex);
        }
        // createnew workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet
        XSSFSheet sheet = workbook.createSheet("data");

        for (int i = 1; i < rowNum; i++) {
            XSSFRow row = ws.getRow(i);
            String surname = cellToString(row.getCell(surnameHeaderIndex));
            String value = cellToString(row.getCell(valueHeaderIndex));
            int cellIndex = 0;
            //Create a newRow object for the output excel. 
            //We begin for i = 1, because of the headers from the input excel, so we go minus 1 in the new (no headers).
            //If for the output we need headers, add them outside this for loop, and go with i, not i-1
            XSSFRow newRow = sheet.createRow(i-1);  
            newRow.createCell(cellIndex++).setCellValue(surname);
            newRow.createCell(cellIndex++).setCellValue(value);
        }

        FileOutputStream fos = new FileOutputStream(new File("test1.xlsx"));
        workbook.write(fos);
        fos.close();
    }

    public static String cellToString(XSSFCell cell) {

        int type;
        Object result = null;
        type = cell.getCellType();

        switch (type) {

        case XSSFCell.CELL_TYPE_NUMERIC:
            result = BigDecimal.valueOf(cell.getNumericCellValue())
                    .toPlainString();

            break;
        case XSSFCell.CELL_TYPE_STRING:
            result = cell.getStringCellValue();
            break;
        case XSSFCell.CELL_TYPE_BLANK:
            result = "";
            break;
        case XSSFCell.CELL_TYPE_FORMULA:
            result = cell.getCellFormula();
        }

        return result.toString();
    }
}