-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathLookupWords.java
More file actions
131 lines (113 loc) · 4.63 KB
/
LookupWords.java
File metadata and controls
131 lines (113 loc) · 4.63 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
package scanning;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.NoSuchFileException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class LookupWords {
private static final Logger logger = LoggerFactory.getLogger(LookupWords.class);
public static List<LookupWordsTO> buildWordList() {
try {
return readExcelCreateWordList();
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
return null; // TODO
}
private static List<LookupWordsTO> readExcelCreateWordList() throws IOException, InvalidFormatException {
// kopieren Wordlist Excel auf working-copy, damit das normale Excel offen gelassen werden kann
File dirFrom = new File(Const.WORDLIST_SRC_EXCEL_FILE);
File dirTo = new File(Const.WORDLIST_WORKING_EXCEL_FILENAME);
try {
copyFile(dirFrom, dirTo);
} catch (IOException ex) {
ex.printStackTrace();
}
List<LookupWordsTO> lookupWordsTOList = new ArrayList<>();
LookupWordsTO lookupWordsTO;
logger.info("");
// Creating a Workbook from an Excel file (.xls or .xlsx)
Workbook workbook = WorkbookFactory.create(dirTo);
// Retrieving the number of sheets in the Workbook
System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");
/*
=============================================================
Iterating over all the sheets in the workbook (Multiple ways)
=============================================================
*/
System.out.println("Retrieving Sheets using for-each loop");
for(Sheet sheet: workbook) {
System.out.println("=> " + sheet.getSheetName());
}
/*
==================================================================
Iterating over all the rows and columns in a Sheet (Multiple ways)
==================================================================
*/
// single row
lookupWordsTO = new LookupWordsTO();
// Getting the Sheet at index zero
Sheet sheet = workbook.getSheetAt(0);
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
// 2. Or you can use a for-each loop to iterate over the rows and columns
System.out.println("\n\nIterating over Rows and Columns using for-each loop\n");
for (Row row: sheet) {
// single row
System.out.println();
lookupWordsTO = new LookupWordsTO();
int z = 0;
for(Cell cell: row) {
String cellValue = dataFormatter.formatCellValue(cell);
System.out.print(cellValue + "\t");
z++;
if (z==1) lookupWordsTO.setZielKategorie(cellValue);
else if (z==2) lookupWordsTO.setZielName(cellValue);
else if (z==3) lookupWordsTO.setW1(cellValue);
else if (z==4) lookupWordsTO.setW2(cellValue);
else if (z==5) lookupWordsTO.setW3(cellValue);
else if (z==6) lookupWordsTO.setW4(cellValue);
}
if (lookupWordsTO.getW1()!= null) {
if (lookupWordsTO.getW1().length()>1) {
lookupWordsTOList.add(lookupWordsTO);
}
}
}
System.out.println(lookupWordsTOList.size());
// Closing the workbook
workbook.close();
return lookupWordsTOList;
}
// public List<LookupWordsTO> getAllData(){
// logger.info("");
// try {
// List<LookupWordsTO> LookupWordsTOList = readExcelCreateWordList();
// for (LookupWordsTO LookupWordsTO : LookupWordsTOList){
// LookupWordsTO.validate();
// }
// return LookupWordsTOList;
// } catch (IOException e) {
// e.printStackTrace();
// } catch (InvalidFormatException e) {
// e.printStackTrace();
// }
// return null;
// }
public static void copyFile( File from, File to ) throws IOException {
try {
Files.delete(to.toPath());
} catch (NoSuchFileException e) {
// nicht vorhanden
}
Files.copy( from.toPath(), to.toPath() );
}
}