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