Ich habe eine Excel Tabelle, aus der ich Daten ablesen will. Dafür brauche ich CellReference. Hier ein Teil meines Codes:
"D4" ist hierbei variabel. Das problem ist jetzt, dass mir immer der falsche Wert für die Row zurückgegeben wird. für D4 wird praktisch D5 zurückgegeben, für G11 G12, für B5 B6, usw.
Hier mein gesamter Quellcode:
Anbei auch die Excel Datei (Als ZIP weil man keine .xlsx Dateien hochladen kann).
Ich bin dankbar für jede Hilfe.
Java:
XSSFRow row;
XSSFCell cell;
CellReference cr = new CellReference("D4");
row = sheet.getRow(cr.getRow());
cell = row.getCell(cr.getCol(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (cell != null && cell.getCellType() == CellType.NUMERIC) {
return cell.getNumericCellValue();
} else {
return 0.0;
}
"D4" ist hierbei variabel. Das problem ist jetzt, dass mir immer der falsche Wert für die Row zurückgegeben wird. für D4 wird praktisch D5 zurückgegeben, für G11 G12, für B5 B6, usw.
Hier mein gesamter Quellcode:
Code:
package utils;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import javax.sound.midi.SysexMessage;
import java.io.*;
import java.util.concurrent.ThreadLocalRandom;
public class ExcelUtils {
HashMap<String, String> namesLetters = new HashMap<>();
HashMap<String, Integer> namesNumber = new HashMap<>();
HashMap<Integer, String> numberLetter = new HashMap<>();
HashMap<String, String> letterNames = new HashMap<>();
HashMap<Integer, String> numberNames = new HashMap<>();
HashMap<String, String> pairs = new HashMap<>();
HashMap<String, String> usedPairs = new HashMap<>();
File file = new File("/Users/thierry/Desktop/Sitzplan/data/savedPairsHashMaps.rtf");
Path filePath = Paths.get("/Users/thierry/Desktop/Sitzplan/data/savedPairsHashMaps.rtf");
Charset charset = StandardCharsets.UTF_8;
String excelPath = "/Users/thierry/Desktop/Sitzplan/data/Sitzplan Sitznachbarn.xlsx";
HashMap<String, String> checkMap = new HashMap<>();
public static void main(String[] args) throws Exception {
new ExcelUtils();
}
public ExcelUtils() throws IOException {
removeTextFromTxt(file);
putHashMapData();
sitznachbarpaare();
}
public void sitznachbarpaare() throws IOException {
/*
double overallResult = 0;
double overallResult2 = 0;
double result1;
int randName1 = ThreadLocalRandom.current().nextInt(2, 32);
int randName2 = ThreadLocalRandom.current().nextInt(2, 32);
while (randName1 == randName2) {
randName1 = ThreadLocalRandom.current().nextInt(2, 32);
randName2 = ThreadLocalRandom.current().nextInt(2, 32);
}
int k = 0;
String[] splittedNames;
while (k < 30 * 29) {
for (int i = 0; i < 15; i++) {
double x = getCellDataByIndex(randName1, randName2);
double y = getCellDataByIndex(randName2, randName1);
result1 = x + y;
overallResult2 += result1;
String names = getNamesFromIndex(randName1, randName2);
splittedNames = names.split("\\s+");
pairs.put(splittedNames[0], splittedNames[1]);
randName1 = ThreadLocalRandom.current().nextInt(2, 32);
randName2 = ThreadLocalRandom.current().nextInt(2, 32);
while (pairs.containsKey(numberNames.get(randName1)) ||
pairs.containsValue(numberNames.get(randName1)) ||
randName1 == randName2
)
{
randName1 = ThreadLocalRandom.current().nextInt(2, 32);
}
while (pairs.containsKey(numberNames.get(randName2)) ||
pairs.containsValue(numberNames.get(randName2)) ||
randName1 == randName2)
{
randName2 = ThreadLocalRandom.current().nextInt(2, 32);
}
System.out.println(pairs);
//addUsedPairsFromTxt(usedPairs);
pairs.clear();
}
if(overallResult2 >= overallResult)
{
overallResult = overallResult2;
}
overallResult2 = 0;
k++;
writePairsInTxt(pairs);
System.out.println(overallResult);
}
*/
System.out.println(getCellDataByIndex(11, 7));
System.out.println(getNamesFromIndex(11, 7));
}
public void addUsedPairsFromTxt(Map<String, String> map) {
try (BufferedReader br = Files.newBufferedReader(filePath, charset)) {
String line;
String[] names;
while ((line = br.readLine()) != null) {
names = line.split("\\s+");
map.put(names[0], names[1]);
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
public void removeTextFromTxt(File file) throws FileNotFoundException {
PrintWriter writer = new PrintWriter(file);
writer.close();
}
public void writePairsInTxt(Map<String, String> map) {
try (BufferedWriter bf = new BufferedWriter(new FileWriter(file, true))) {
for (Map.Entry<String, String> entry : map.entrySet()) {
bf.write(entry.getKey() + " " + entry.getValue());
bf.newLine();
}
bf.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
public String getNamesFromIndex(int row, int column) {
String firstName = numberNames.get(row);
String secondName = letterNames.get(numberLetter.get(column));
return firstName + " " + secondName;
}
public Double getCellDataByName(String nameRow, String nameCol) throws IOException {
FileInputStream inputstream = new FileInputStream(excelPath);
XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
String ref = namesLetters.get(nameCol) + namesNumber.get(nameRow);
CellReference cr = new CellReference(ref);
row = sheet.getRow(cr.getRow());
cell = row.getCell(cr.getCol(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (cell != null && cell.getCellType() == CellType.NUMERIC) {
return cell.getNumericCellValue();
} else {
return 0.0;
}
}
public Double getCellDataByIndex(int excelrow, int excelcolumn) throws IOException {
FileInputStream inputstream = new FileInputStream(excelPath);
XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
String name1 = numberNames.get(excelrow);
String name2 = numberNames.get(excelcolumn);
System.out.println(numberLetter.get(excelcolumn) + excelrow);
CellReference cr = new CellReference(numberLetter.get(excelcolumn) + excelrow);
row = sheet.getRow(cr.getRow());
cell = row.getCell(cr.getCol()/*, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK*/);
if (/*cell != null && */cell.getCellType() == CellType.NUMERIC) {
return cell.getNumericCellValue();
} else {
return 0.0;
}
}
public void putHashMapData() {
namesLetters.put("Yuri", "B");
namesLetters.put("Thierry", "C");
namesLetters.put("Jennifer", "D");
namesLetters.put("Béla", "E");
namesLetters.put("Theodor", "F");
namesLetters.put("David", "G");
namesLetters.put("Yuliana", "H");
namesLetters.put("Ariana", "I");
namesLetters.put("Jonah", "J");
namesLetters.put("Alina", "K");
namesLetters.put("Thomas", "L");
namesLetters.put("Bjarne", "M");
namesLetters.put("Livia", "N");
namesLetters.put("Till", "O");
namesLetters.put("Jason", "P");
namesLetters.put("Justus", "Q");
namesLetters.put("Timofej", "R");
namesLetters.put("Nikolai", "S");
namesLetters.put("An", "T");
namesLetters.put("Khoi", "U");
namesLetters.put("Mai", "V");
namesLetters.put("Florian", "W");
namesLetters.put("Giuglio", "X");
namesLetters.put("Fridtjof", "Y");
namesLetters.put("Mark", "Z");
namesLetters.put("Markus", "AA");
namesLetters.put("Felix", "AB");
namesLetters.put("Max", "AC");
namesLetters.put("Daniil", "AD");
namesLetters.put("Yared", "AE");
namesNumber.put("Yuri", 3);
namesNumber.put("Thierry", 4);
namesNumber.put("Jennifer", 5);
namesNumber.put("Béla", 6);
namesNumber.put("Theodor", 7);
namesNumber.put("David", 8);
namesNumber.put("Yuliana", 9);
namesNumber.put("Ariana", 10);
namesNumber.put("Jonah", 11);
namesNumber.put("Alina", 12);
namesNumber.put("Thomas", 13);
namesNumber.put("Bjarne", 14);
namesNumber.put("Livia", 15);
namesNumber.put("Till", 16);
namesNumber.put("Jason", 17);
namesNumber.put("Justus", 18);
namesNumber.put("Timofej", 19);
namesNumber.put("Nikolai", 20);
namesNumber.put("An", 21);
namesNumber.put("Khoi", 22);
namesNumber.put("Mai", 23);
namesNumber.put("Florian", 24);
namesNumber.put("Giuglio", 25);
namesNumber.put("Fridtjof", 26);
namesNumber.put("Mark", 27);
namesNumber.put("Markus", 28);
namesNumber.put("Felix", 29);
namesNumber.put("Max", 30);
namesNumber.put("Daniil", 31);
namesNumber.put("Yared", 32);
numberLetter.put(1, "A");
numberLetter.put(2, "B");
numberLetter.put(3, "C");
numberLetter.put(4, "D");
numberLetter.put(5, "E");
numberLetter.put(6, "F");
numberLetter.put(7, "G");
numberLetter.put(8, "H");
numberLetter.put(9, "I");
numberLetter.put(10, "J");
numberLetter.put(11, "K");
numberLetter.put(12, "L");
numberLetter.put(13, "M");
numberLetter.put(14, "N");
numberLetter.put(15, "O");
numberLetter.put(16, "P");
numberLetter.put(17, "Q");
numberLetter.put(18, "R");
numberLetter.put(19, "S");
numberLetter.put(20, "T");
numberLetter.put(21, "U");
numberLetter.put(22, "V");
numberLetter.put(23, "W");
numberLetter.put(24, "X");
numberLetter.put(25, "Y");
numberLetter.put(26, "Z");
numberLetter.put(27, "AA");
numberLetter.put(28, "AB");
numberLetter.put(29, "AC");
numberLetter.put(30, "AD");
numberLetter.put(31, "AE");
numberNames.put(3, "Yuri");
numberNames.put(4, "Thierry");
numberNames.put(5, "Jennifer");
numberNames.put(6, "Béla");
numberNames.put(7, "Theodor");
numberNames.put(8, "David");
numberNames.put(9, "Yuliana");
numberNames.put(10, "Ariana");
numberNames.put(11, "Jonah");
numberNames.put(12, "Alina");
numberNames.put(13, "Thomas");
numberNames.put(14, "Bjarne");
numberNames.put(15, "Livia");
numberNames.put(16, "Till");
numberNames.put(17, "Jason");
numberNames.put(18, "Justus");
numberNames.put(19, "Timofej");
numberNames.put(20, "Nikolai");
numberNames.put(21, "An");
numberNames.put(22, "Khoi");
numberNames.put(23, "Mai");
numberNames.put(24, "Florian");
numberNames.put(25, "Giuglio");
numberNames.put(26, "Fridtjof");
numberNames.put(27, "Mark");
numberNames.put(28, "Markus");
numberNames.put(29, "Felix");
numberNames.put(30, "Max");
numberNames.put(31, "Daniil");
numberNames.put(32, "Yared");
letterNames.put("B", "Yuri");
letterNames.put("C", "Thierry");
letterNames.put("D", "Jennifer");
letterNames.put("E", "Béla");
letterNames.put("F", "Theodor");
letterNames.put("G", "David");
letterNames.put("H", "Yuliana");
letterNames.put("I", "Ariana");
letterNames.put("J", "Jonah");
letterNames.put("K", "Alina");
letterNames.put("L", "Thomas");
letterNames.put("M", "Bjarne");
letterNames.put("N", "Livia");
letterNames.put("O", "Till");
letterNames.put("P", "Jason");
letterNames.put("Q", "Justus");
letterNames.put("R", "Timofej");
letterNames.put("S", "Nikolai");
letterNames.put("T", "An");
letterNames.put("U", "Khoi");
letterNames.put("V", "Mai");
letterNames.put("W", "Florian");
letterNames.put("X", "Giuglio");
letterNames.put("Y", "Fridtjof");
letterNames.put("Z", "Mark");
letterNames.put("AA", "Markus");
letterNames.put("AB", "Felix");
letterNames.put("AC", "Max");
letterNames.put("AD", "Daniil");
letterNames.put("AE", "Yared");
}
}
Beispielscreenshots von der Konsole:
[ATTACH type="full"]17999[/ATTACH]
[ATTACH type="full"]18000[/ATTACH]
Anbei auch die Excel Datei (Als ZIP weil man keine .xlsx Dateien hochladen kann).
Ich bin dankbar für jede Hilfe.