英文:
Java Excel search and Array Lists not working correctly
问题
I have trouble with my code. It does not work the way I wanted it to work. Sorry if I explain myself so badly. 
(I use Apache POI version 4.1.1)
My problem: I have an excel-sheet with multiple entries. Each entry contains, among other things, the shipping date in column 1, the customer name in column 2, the pallet ID in column 15, and the shipping status in column 16. On top of everything, there is a headline for the columns.
The excel contains multiple customers and some of them multiple times, the only difference are the pallet IDs. My customer object class contains, among other things, an array list, which may contain multiple, different, pallet IDs.
I want to list each customer, whose shipping is today, only once, but with all his pallet IDs.
My customer class looks like this:
import java.util.ArrayList;
import java.util.Date;
public class Customer {
private String customername;
private ArrayList<String> pallet = new ArrayList<>();
private String date;
private String status;
public Customer(){
}
public String getCustomerName() {
return customername;
}
public void setCustomerName(String customername) {
this.customername = customername;
}
public String getFromPallet(int i) {
return pallet.get(i);
}
public ArrayList<String> getPallet() {
return pallet;
}
public ArrayList<String> get(int j) {
return pallet;
}
public void setPallet(ArrayList<String> pallet) {
this.pallet = pallet;
}
public void addPallet(String string) {
this.pallet.add(string);
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public String toString(){
return String.format("%1s - %2s - %3s", customername, date, status);
}
}
my main class looks like this:
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class NewTrueSimpleExcelReader {
SimpleDateFormat SDF = new SimpleDateFormat("dd.MM.yyyy");
Date today = new Date();
Date BBB;
int lastfound = 0;
public List<Customer> readCustomersFromExcelFile(String excelFilePath) throws IOException, ParseException {
List<Customer> listCustomers = new ArrayList<>();
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow firstrow = sheet.getRow(1);
int fr = firstrow.getRowNum();
XSSFRow row;
while(fr < sheet.getLastRowNum()){
row = sheet.getRow(fr);
if(!row.getCell(0).getStringCellValue().equals("")){
String AAA = row.getCell(0).getStringCellValue().trim();
BBB = SDF.parse(AAA);
if((SDF.format(BBB)).equals(SDF.format(today))){
Customer aCustomer = new Customer();
System.out.println("Customer: "+row.getCell(1).getStringCellValue().trim());
if(listCustomers.size() > 0 && row.getCell(16) == null){
for(int i=0; i<listCustomers.size(); i++){
System.out.println("i is: "+i);
String string1 = listCustomers.get(i).getCustomerName();
String string2 = row.getCell(1).getStringCellValue();
if(string1.equals(string2) && row.getRowNum() != lastfound){
lastfound = row.getRowNum();
System.out.println("Dupl found");
for(int j=0; j<listCustomers.get(i).get(j).size(); j++){
if(!row.getCell(15).getStringCellValue().trim().equals(listCustomers.get(i).getFromPallet(j))){
listCustomers.get(i).addPallet(row.getCell(15).getStringCellValue());
}
}
} else{
if(row.getRowNum() != lastfound){
System.out.println("second+: "+row.getCell(0).getStringCellValue().trim());
lastfound = row.getRowNum();
aCustomer.setDate(row.getCell(0).getStringCellValue().trim());
aCustomer.setCustomerName(row.getCell(1).getStringCellValue());
aCustomer.setStatus("unused");
if(row.getCell(15) != null){
aCustomer.addPallet(row.getCell(15).getStringCellValue());
}
listCustomers.add(aCustomer);
}
}
}
} else{
if(row.getCell(16) == null){
lastfound = row.getRowNum();
System.out.println("first: "+row.getCell(0).getStringCellValue().trim());
aCustomer.setDate(row.getCell(0).getStringCellValue().trim());
aCustomer.setCustomerName(row.getCell(1).getStringCellValue());
aCustomer.setStatus("unused");
if(row.getCell(15) != null){
aCustomer.addPallet(row.getCell(15).getStringCellValue());
}
listCustomers.add(aCustomer);
}
}
}
}
fr++;
}
System.out.println(listCustomers.size());
wb.close();
inputStream.close();
return listCustomers;
}
}
Some customers are listed multiple times with the same name on the same date, but with different pallet IDs, so my code should just add the different IDs to already existing customers in the ArrayList containing the objects. But it does not work. It simply just adds them as new objects in the list! And I do not understand why.
I know that I may could have used iterators, or that there are probably better ways to do what I want, but my knowledge of them is simply not good enough.
I hope you guys could understand my problem and can help me out. 
Sincerely yours,
Shathos
UPDATE:
Thanks to the help of Hades, I managed to find a solution to my problem! I replaced both ArrayLists with HashMaps and adjusted the customer class. Wohooo! Everything works for me! Thank you very much, Hades! Here is my working function:
public HashMap<String, Customer> readCustomersFromExcelFile(String excelFilePath) throws IOException, ParseException {
HashMap<String, Customer> map = new HashMap<String, Customer>();
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheetAt(0);
int fr = sheet.getRow(1).getRowNum();
XSSFRow row;
while(fr < sheet.getLastRowNum()){
row = sheet.getRow(fr);
if(!row.getCell(0).getStringCellValue().equals("")){
String
<details>
<summary>英文:</summary>
I have trouble with my code.
It does not work the way I wanted it to work.
Sorry if I explain myself so badly. :(
(I use Apache POI version 4.1.1)
My problem: I have an excel-sheet with multiple entries. Each entry contains, among other things, the shipping date in column 1, the customer name in column 2, the pallet ID in column 15 and the shipping status in column 16.
On top of everything, there is a headline for the columns.
The excel contains multiple customers and some of them multiple times, the only difference are the pallet IDs.
My customer object class contains, among other things, a array list, which may contain multiple, different, pallet IDs.
I want list each customer, which shipping is today, only once, but with all his pallet IDs.
My customer class looks like this:
import java.util.ArrayList;
import java.util.Date;
public class Customer {
private String customername;
private ArrayList<String> pallet = new ArrayList<>();
private String date;
private String status;
public Customer(){
}
public String getCustomerName() {
return customername;
}
public void setCustomerName(String customername) {
this.customername = customername;
}
public String getFromPallet(int i) {
return pallet.get(i);
}
public ArrayList<String> getPallet() {
return pallet;
}
public ArrayList<String> get(int j) {
return pallet;
}
public void setPallet(ArrayList<String> pallet) {
this.pallet = pallet;
}
public void addPallet(String string) {
this.pallet.add(string);
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public String toString(){
return String.format("%1s - %2s - %3s", customername, date, status);
}
}
my main class looks like this:
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class NewTrueSimpleExcelReader {
SimpleDateFormat SDF = new SimpleDateFormat("dd.MM.yyyy");
Date today = new Date();
Date BBB;
int lastfound = 0;
public List<Customer> readCustomersFromExcelFile(String excelFilePath) throws IOException, ParseException {
List<Customer> listCustomers = new ArrayList<>();
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow firstrow = sheet.getRow(1);
int fr = firstrow.getRowNum();
XSSFRow row;
while(fr < sheet.getLastRowNum()){
row = sheet.getRow(fr);
if(!row.getCell(0).getStringCellValue().equals("")){
String AAA = row.getCell(0).getStringCellValue().trim();
BBB = SDF.parse(AAA);
if((SDF.format(BBB)).equals(SDF.format(today))){
Customer aCustomer = new Customer();
System.out.println("Customer: "+row.getCell(1).getStringCellValue().trim());
if(listCustomers.size() > 0 && row.getCell(16) == null){
for(int i=0; i<listCustomers.size(); i++){
System.out.println("i is: "+i);
String string1 = listCustomers.get(i).getCustomerName();
String string2 = row.getCell(1).getStringCellValue();
if(string1.equals(string2) && row.getRowNum() != lastfound){
lastfound = row.getRowNum();
System.out.println("Dupl found");
for(int j=0; j<listCustomers.get(i).get(j).size(); j++){
if(!row.getCell(15).getStringCellValue().trim().equals(listCustomers.get(i).getFromPallet(j))){
listCustomers.get(i).addPallet(row.getCell(15).getStringCellValue());
}
}
} else{
if(row.getRowNum() != lastfound){
System.out.println("second+: "+row.getCell(0).getStringCellValue().trim());
lastfound = row.getRowNum();
aCustomer.setDate(row.getCell(0).getStringCellValue().trim());
aCustomer.setCustomerName(row.getCell(1).getStringCellValue());
aCustomer.setStatus("unused");
if(row.getCell(15) != null){ //&& !string1.equals(string2)
aCustomer.addPallet(row.getCell(15).getStringCellValue());
}
listCustomers.add(aCustomer);
}
}
}
} else{
if(row.getCell(16) == null){
lastfound = row.getRowNum();
System.out.println("first: "+row.getCell(0).getStringCellValue().trim());
aCustomer.setDate(row.getCell(0).getStringCellValue().trim());
aCustomer.setCustomerName(row.getCell(1).getStringCellValue());
aCustomer.setStatus("unused");
if(row.getCell(15) != null){
aCustomer.addPallet(row.getCell(15).getStringCellValue());
}
listCustomers.add(aCustomer);
}
}
}
}
fr++;
}
System.out.println(listCustomers.size());
wb.close();
inputStream.close();
return listCustomers;
}
}
Some customers are listed multiple times with the same name on the same date, but with different pallet IDs, so my code should just add the different IDs to already existing customers in the Array List containing the objects. But it does not work. :( It simply just adds them as new objects in the list!
And I do net understand why. :(
I know that I may could have used iterators, or that there are probably better ways to to what I want, but my knowledge of them is simply not good enough.
I hope you guys could understand my problem and can help me out. :)
Sincerely yours,
Shathos
**UPDATE:**
Thanks to the help of ***Hades***, I managed to find a solution to my problem! I replaced both Array Lists with HashMaps and adjusted the customer class. Wohooo! Everything works for me! Thank you very much, ***Hades***! Here is my working function:
public HashMap<String, Customer> readCustomersFromExcelFile(String excelFilePath) throws IOException, ParseException {
HashMap<String, Customer> map = new HashMap<String, Customer>();
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheetAt(0);
int fr = sheet.getRow(1).getRowNum();
XSSFRow row;
while(fr < sheet.getLastRowNum()){
row = sheet.getRow(fr);
if(!row.getCell(0).getStringCellValue().equals("")){
String AAA = row.getCell(0).getStringCellValue().trim();
BBB = SDF.parse(AAA);
if((SDF.format(BBB)).equals(SDF.format(today))){
Customer aCustomer = new Customer();
System.out.println("Customer: "+row.getCell(1).getStringCellValue().trim());
if(row.getCell(15) == null || row.getCell(15).getCellType() == CellType.BLANK){
INPUTSTRING = "not found";
} else {
INPUTSTRING = row.getCell(15).getStringCellValue();
}
if(map.containsKey(row.getCell(1).getStringCellValue()) && (row.getCell(16) == null || row.getCell(16).getCellType() == CellType.BLANK)) { //Check the map for new customer
System.out.println("Input is: "+INPUTSTRING);
if(map.get(row.getCell(1).getStringCellValue()).getPallet().containsKey(INPUTSTRING)){
System.out.println("Duplicate found! -> "+INPUTSTRING);
if((INPUTSTRING.equals("not found"))){
System.out.println("Empty duplicate found!");
}
} else {
System.out.println("No Duplicate found! -> "+INPUTSTRING);
map.get(row.getCell(1).getStringCellValue()).getPallet().put(INPUTSTRING, INPUTSTRING); //If already in map add Pallet to the Object
}
}else if(row.getCell(16) == null || row.getCell(16).getCellType() == CellType.BLANK){
aCustomer = new Customer(); //populate this with the current values
aCustomer.setCustomerName(row.getCell(1).getStringCellValue());
aCustomer.setDate(row.getCell(0).getStringCellValue());
aCustomer.setStatus("unverladen");
if(row.getCell(15) == null || row.getCell(15).getCellType() == CellType.BLANK){
System.out.println("EMPTY FOUND: "+row.getCell(1).getStringCellValue());
aCustomer.addPallet("not found", "not found");
} else {
aCustomer.addPallet(row.getCell(15).getStringCellValue(), row.getCell(15).getStringCellValue());
}
map.put(row.getCell(1).getStringCellValue(), aCustomer); //add to the map when customer encounter for the first time
}
}
}
fr++;
}
wb.close();
inputStream.close();
return map;
}
:)
</details>
# 答案1
**得分**: 1
创建一个如下的映射：
```java
Map<String, Customer> map = new HashMap<String, Customer>();
键将是String，即客户名称，值将是Customer类。
当满足日期条件时，将托盘添加到现有客户，如下所示：
if (map.containsKey(customerName)) { //检查新客户是否在映射中
map.get(customerName).getPallet.add(palletId); //如果已经在映射中，将托盘添加到对象中
} else {
customer = new Customer(); //使用当前值填充此对象
map.put(customerName, customer); //当首次遇到客户时将其添加到映射中
}
英文:
Create a Map a below
Map<String, Customer> map = new HashMap<String, Customer>();
The key will be String i.e. customer Name and value will be Customer Class.
When your date condition satisfies then add the pallet to the existing customer like below
if(map.containsKey(customerName)) { //Check the map for new customer
map.get(customerName).getPallet.add(palletId); //If already in map add Pallet to the Object
}else {
customers = new Customer(); //populate this with the current values
map.put(customerName, customer); //add to the map when customer encounter for the first time
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论