gpt4 book ai didi

java - 如何使用 apache poi 读取 .xlsx 文件?

转载 作者:行者123 更新时间:2023-12-02 01:10:17 26 4
gpt4 key购买 nike

参见下图,我正在尝试编写一个程序,可以“扫描”给定的行,不受从哪个单元格到哪个单元格的限制,然后找到所有相同的“字符串”。可以这样做吗?谢谢。

举个例子,这样就不会很困惑,例如:在H行,你看到有客户的名字,有“Coresystem”,“华为”,“VIVO”等...现在问题是,如果名称没有组合在一起,它们都是分开的,比如,在 H5 上,它将是“华为”,在 H9 上,它将是“VIVO”等,就像,与提供的图片不同下面,在 H 行,所有名称都被分开,我希望 apache POI 找到所有具有相同名称的客户,例如:如果用户输入“coReSysteM”,它应该能够找到所有 H行所有Coresystem的.equalsIgnoreCase(顺便说一句,用户应该能够输入他们想要输入的客户姓名和他们想要搜索的行),并从A5,B5,C5,D5显示,E5,F5,G5,H5到A14,B14,C14,D14,E14,F14,G14,H5,可以吗? 我正在考虑设置一个公式来查找所有客户,例如:=CountIf

这些是我目前正在尝试执行的代码,但后来我坚持了下来:

package excel_reader;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Scanner;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader2d {
ExcelReader link = new ExcelReader();
Scanner scan = new Scanner(System.in);

// instance data
private static int numberGrid[][] = null;
private static String stringGrid[][] = null;

// constructor
public ExcelReader2d(String desLink) {

}

// methods
public void ExeScan() throws FileNotFoundException, IOException {
Scanner scan = new Scanner(System.in);
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("C:\\Users\\Sonic\\Desktop\\20191223 IMPORTS.xlsx"));

XSSFSheet sheet = workbook.getSheetAt(0);
final int rowStart = Math.min(15, sheet.getFirstRowNum()), rowEnd = Math.max(1400, sheet.getLastRowNum());

System.out.print("Enter the rows that you want to search for: (for ex. the rows that stores customer's name) ");
int searchRows = scan.nextInt();
System.out.print("Enter the customer's name that you are looking for: ");
String name = scan.nextLine();
//int rowNum;

// Search given row
XSSFRow row = sheet.getRow(searchRows);
try {
for (int j = 4; j < rowEnd; j++) {
Row r = sheet.getRow(j);
if (name.equalsIgnoreCase(name)) {
row.getCell(j).getStringCellValue();
}

// skip to next iterate if that specific cell is empty
if (r == null)
continue;

}
} catch (Exception e){
System.out.println("Something went wrong.");
}



}

}

ps。我知道这会很令人困惑,但请随时提出任何问题,以帮助您摆脱困惑并帮助我,因为这对我来说一直是个问题。非常感谢您,我将非常感谢您的帮助。目前使用apache poi、vscode、java。

最佳答案

我将迭代工作表中的行并从每行获取单元格 7 ( H ) 的字符串内容。如果该字符串满足要求 equalsIgnoreCase搜索值,该行是结果行之一,否则不是。

可以将结果行收集到 List<Row> 中。那么这个List包含之后的结果行。

示例:

ExcelWorkbook.xlsx :

enter image description here

代码:

import org.apache.poi.ss.usermodel.*;

import java.util.List;
import java.util.ArrayList;

import java.io.FileInputStream;

public class ExcelReadRowsByColumnValue {

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

String filePath = "./ExcelWorkbook.xlsx";

String toSearch = "coresystem";
int searchColumn = 7; // column H
List<Row> results = new ArrayList<Row>();

DataFormatter dataFormatter = new DataFormatter();
Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
Sheet sheet = workbook.getSheetAt(0);

for (Row row : sheet) { // iterate over all rows in the sheet
Cell cellInSearchColumn = row.getCell(searchColumn); // get the cell in seach column (H)
if (cellInSearchColumn != null) { // if that cell is present
String cellValue = dataFormatter.formatCellValue(cellInSearchColumn, formulaEvaluator); // get string cell value
if (toSearch.equalsIgnoreCase(cellValue)) { // if cell value equals the searched value
results.add(row); // add that row to the results
}
}
}

// print the results
System.out.println("Found results:");
for (Row row : results) {
int rowNumber = row.getRowNum()+1;
System.out.print("Row " + rowNumber + ":\t");
for (Cell cell : row) {
String cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
System.out.print(cellValue + "\t");
}
System.out.println();
}

workbook.close();
}
}

结果:

enter image description here

关于java - 如何使用 apache poi 读取 .xlsx 文件?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59497536/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com