diff --git a/ImportExcel.iml b/ImportExcel.iml new file mode 100644 index 0000000..d2bcad3 --- /dev/null +++ b/ImportExcel.iml @@ -0,0 +1,219 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file diff --git a/out/production/ImportExcel/com/tesr/Main.class b/out/production/ImportExcel/com/tesr/Main.class new file mode 100644 index 0000000..7b7f372 --- /dev/null +++ b/out/production/ImportExcel/com/tesr/Main.class Binary files differ diff --git a/out/production/ImportExcel/com/tesr/Main2.class b/out/production/ImportExcel/com/tesr/Main2.class new file mode 100644 index 0000000..094c7f1 --- /dev/null +++ b/out/production/ImportExcel/com/tesr/Main2.class Binary files differ diff --git a/out/production/ImportExcel/com/tesr/services/implementations/ExcelService.class b/out/production/ImportExcel/com/tesr/services/implementations/ExcelService.class new file mode 100644 index 0000000..4bb2138 --- /dev/null +++ b/out/production/ImportExcel/com/tesr/services/implementations/ExcelService.class Binary files differ diff --git a/out/production/ImportExcel/com/tesr/services/implementations/SortExcelService.class b/out/production/ImportExcel/com/tesr/services/implementations/SortExcelService.class new file mode 100644 index 0000000..3b1af01 --- /dev/null +++ b/out/production/ImportExcel/com/tesr/services/implementations/SortExcelService.class Binary files differ diff --git a/out/production/ImportExcel/com/tesr/services/models/Row.class b/out/production/ImportExcel/com/tesr/services/models/Row.class new file mode 100644 index 0000000..3b72dca --- /dev/null +++ b/out/production/ImportExcel/com/tesr/services/models/Row.class Binary files differ diff --git a/out/production/ImportExcel/test/com/tesr/services/implementations/ExcelServiceTest.class b/out/production/ImportExcel/test/com/tesr/services/implementations/ExcelServiceTest.class new file mode 100644 index 0000000..e7b6114 --- /dev/null +++ b/out/production/ImportExcel/test/com/tesr/services/implementations/ExcelServiceTest.class Binary files differ diff --git a/src/.DS_Store b/src/.DS_Store new file mode 100644 index 0000000..b3c31b8 --- /dev/null +++ b/src/.DS_Store Binary files differ diff --git a/src/com/.DS_Store b/src/com/.DS_Store new file mode 100644 index 0000000..6327077 --- /dev/null +++ b/src/com/.DS_Store Binary files differ diff --git a/src/com/tesr/.DS_Store b/src/com/tesr/.DS_Store new file mode 100644 index 0000000..137b1c0 --- /dev/null +++ b/src/com/tesr/.DS_Store Binary files differ diff --git a/src/com/tesr/Main.java b/src/com/tesr/Main.java new file mode 100644 index 0000000..2809226 --- /dev/null +++ b/src/com/tesr/Main.java @@ -0,0 +1,269 @@ +package com.tesr; + +import org.apache.poi.ss.usermodel.*; + +import javax.swing.*; +import java.io.File; +import java.io.FileOutputStream; +import java.util.ArrayList; +import java.util.List; + +public class Main { + static String PfadDerTemplateDatei = "/Users/michel/Downloads/ProductionSheet Template.xlsx"; + static String PfadDerAusgangsdatei = "/Users/michel/Downloads/ProductionSheet Eingabe.xlsx"; + static String PfadDerEndDatei = "/Users/michel/Downloads/ProductionSheet.xlsx"; + + public static void main(String[] args) { + + try { + // Variables + //Optimizer myBasicOptimizer = new Optimizer(); + System.out.println("Optimizer instantiated..."); + + // Select Excel File which the user wants to investigate regarding machine optimization potential + // hier wieder ändern String excelFileName = selectExcelFile(); + //hier wieder ändern System.out.println("File via openFileDialog selected: " + excelFileName); + + // Read out data from excel sheet via Apache POI + // hier wieder ändern readExcelFileData(excelFileName); + Sheet sheet = readExcelFileData(PfadDerAusgangsdatei); + List rows = sortExcelFileData(sheet, 19); + + // Optimize machine planning and scheduling + //myBasicOptimizer.process(); + + // Write optimized data back to excel sheet + writeExcelFileData(PfadDerEndDatei, rows, 1); + + System.out.println("Optimization done."); + } catch (Exception ex) { + System.out.println(ex.toString()); + } + } + + + //Funktion zum Sortieren der Exceldatei nach der 19 Spalte (Farben) + private static List sortExcelFileData(Sheet sheet, int sortColumn) { + + //Einlesen aller Zeilen in ein Array + List rows = new ArrayList<>(); + try { + + for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { + rows.add(sheet.getRow(i)); + } + //konkretes Sortieren der Spalte 19 nach Buchstabenwerten (Alphabet) + rows.sort((r1, r2) -> r1.getCell(sortColumn).getStringCellValue().compareTo(r2.getCell(sortColumn).getStringCellValue())); + } + // Fehler beim Einlesen/Sortieren des Arrays wird ausgegeben + catch (Exception exp) { + System.out.println("Error in writeExcelFileData()"); + System.out.println("Error details: " + exp.toString()); + } + return rows; + } + + //Funktion zum Einlesen der oben ausgewählten Exceldatei + private static Sheet readExcelFileData(String fileName) { + Sheet sheet = null; + try { + Workbook workbook = WorkbookFactory.create(new File(fileName)); + sheet = workbook.getSheetAt(1); + //Fehler beim Einlesen der Exceldatei wird ausgegeben + } catch (Exception exReadExcelFile) { + System.out.println("Error in readExcelFileData()"); + System.out.println("Error details: " + exReadExcelFile.toString()); + } + return sheet; + } + + //Funktion zum Ausfüllen der leeren Exceltemplatedatei + private static void writeExcelFileData(String filename, List rows, int startRow) { + try { + double Kabine = 0; + int c = 1; + int h = 0; + + Workbook workbook = WorkbookFactory.create(new File(PfadDerTemplateDatei)); + Sheet sheet = workbook.getSheet("Input"); + //Durchläuft alle Zeilen und ordnet Farbe Kabinezuweisung zu + Sheet sheet0 = workbook.getSheet("Metadaten"); + Row row0 = sheet0.getRow(2); + Cell cell0 = row0.getCell(7); + cell0.setCellValue(120); + for (int i = 0; i < rows.size(); i++) { + double Kabinenfläche = rows.get(i).getCell(5).getNumericCellValue(); + Kabinenfläche = Kabinenfläche * 5; + rows.get(i).createCell(21).setCellValue(Kabinenfläche); + rows.get(i).createCell(0).setCellValue(i + 1); + String Farbe = rows.get(i).getCell(19).getStringCellValue(); + //Für Fall Blau: Kabine 1 und Zuweisung der kombinierten Auftragsnummer und Auftragsanzahl + if (Farbe.equals("Blau")) { + rows.get(i).createCell(3).setCellValue("Paint_Shop_1"); + Kabine = Kabine + rows.get(i).getCell(14).getNumericCellValue(); + + if (Kabine < 30) { + + rows.get(i).createCell(1).setCellValue(c); + //rows.get(i).getCell(2).setCellValue(h); + h++; + } + + if (Kabine >= 30) { + c++; + /*for (int hauke = h; hauke >= 0; hauke--) { + rows.get(i - hauke+1).getCell(2).setCellValue(h); + }*/ + rows.get(i).createCell(1).setCellValue(c); + //rows.get(i).getCell(2).setCellValue(h); + h++; + + Kabine = rows.get(i).getCell(14).getNumericCellValue(); + } + } + //Für Fall Gelb: Kabine 2 und Zuweisung der kombinierten Auftragsnummer und Auftragsanzahl + else if (Farbe.equals("Gelb")) { + rows.get(i).getCell(3).setCellValue("Paint_Shop_2"); + + Kabine = Kabine + rows.get(i).getCell(14).getNumericCellValue(); + + if (Kabine < 30) { + + rows.get(i).createCell(1).setCellValue(c); + //rows.get(i).getCell(2).setCellValue(h); + h++; + } + + if (Kabine >= 30) { + h = 1; + c++; + rows.get(i).createCell(1).setCellValue(c); + //rows.get(i).getCell(2).setCellValue(h); + h++; + + Kabine = rows.get(i).getCell(14).getNumericCellValue(); + } + } + //Für Fall Grün: Kabine 1 und Zuweisung der kombinierten Auftragsnummer und Auftragsanzahl + else if (Farbe.equals("Grün")) { + rows.get(i).getCell(3).setCellValue("Paint_Shop_3"); + + Kabine = Kabine + rows.get(i).getCell(14).getNumericCellValue(); + + if (Kabine < 30) { + + rows.get(i).createCell(1).setCellValue(c); + //rows.get(i).getCell(2).setCellValue(h); + h++; + } + + if (Kabine >= 30) { + h = 1; + c++; + rows.get(i).createCell(1).setCellValue(c); + //rows.get(i).getCell(2).setCellValue(h); + h++; + + Kabine = rows.get(i).getCell(14).getNumericCellValue(); + } + } +//Für Fall Rot: Kabine 2 und Zuweisung der kombinierten Auftragsnummer und Auftragsanzahl + else if (Farbe.equals("Rot")) { + rows.get(i).getCell(3).setCellValue("Paint_Shop_1"); + + Kabine = Kabine + rows.get(i).getCell(14).getNumericCellValue(); + + if (Kabine < 30) { + + rows.get(i).createCell(1).setCellValue(c); + //rows.get(i).getCell(2).setCellValue(h); + h++; + } + + if (Kabine >= 30) { + h = 1; + c++; + rows.get(i).createCell(1).setCellValue(c); + + h++; + + Kabine = rows.get(i).getCell(14).getNumericCellValue(); + } + + } + } + + ArrayList KombAuftragsgroeße = new ArrayList(); + ArrayList Auftragsanzahl = new ArrayList(); + + double KombAuftragsnummerVergleich = 1; + double Zellenwert_Kombauftragsfläche1 = 0; + int hochzaehlen = 0; + for (int xx = 0; xx < rows.size(); ) { + double Zellenwert_KombAuftragsnummer = rows.get(xx).getCell(1).getNumericCellValue(); + double Zellenwert_KombAuftragsflaeche2 = rows.get(xx).getCell(5).getNumericCellValue(); + if (Zellenwert_KombAuftragsnummer == KombAuftragsnummerVergleich) { + xx++; + hochzaehlen++; + Zellenwert_Kombauftragsfläche1 = Zellenwert_Kombauftragsfläche1 + Zellenwert_KombAuftragsflaeche2; + + } else { + KombAuftragsnummerVergleich++; + Auftragsanzahl.add(hochzaehlen); + KombAuftragsgroeße.add(Zellenwert_Kombauftragsfläche1); + Zellenwert_Kombauftragsfläche1 = 0; + hochzaehlen = 0; + } + } + int Reihe = 1; + for (int ij = 0; ij < Auftragsanzahl.size(); ij++) { + + double Arraystellenwert_Auftraege = Auftragsanzahl.get(ij); + double Arraystellenwert_Flaeche = KombAuftragsgroeße.get(ij); + //System.out.println(Arraystellenwert_Auftraege); + + for (int ijk = 1; ijk <= Arraystellenwert_Auftraege; ijk++) { + rows.get(Reihe).createCell(2).setCellValue(Arraystellenwert_Auftraege); + Reihe++; + rows.get(Reihe).createCell(5).setCellValue(Arraystellenwert_Flaeche); + System.out.println(Arraystellenwert_Auftraege); + System.out.println(Arraystellenwert_Flaeche); + } + } + //Korrekte Übertragung der Ausgangsdatei in die neue Exceldatei + for (int i = 0; i < rows.size(); i++) { + Row row = sheet.createRow(i + startRow); + for (int x = 0; x < rows.get(i).getPhysicalNumberOfCells(); x++) { + //Fall: Zelle ist eine Zahl + if (rows.get(i).getCell(x).getCellType() == CellType.NUMERIC) { + row.createCell(x).setCellValue(rows.get(i).getCell(x).getNumericCellValue()); + } + //Fall: Zelle ist ein Buchstabe + else if (rows.get(i).getCell(x).getCellType() == CellType.STRING) { + row.createCell(x).setCellValue(rows.get(i).getCell(x).getStringCellValue()); + } + //Fall: Zelle ist eine Formel + else if (rows.get(i).getCell(x).getCellType() == CellType.FORMULA) { + String cellFormula = rows.get(i).getCell(x).getCellFormula(); + String[] formulaSplitted = cellFormula.split("\\*"); + for (int y = 0; y < formulaSplitted.length; y++) { + if (!formulaSplitted[y].matches("^[0-9]*$")) { + formulaSplitted[y] = formulaSplitted[y].replace(String.valueOf(rows.get(i).getRowNum() + 1), String.valueOf(i + startRow + 1)); + } + } + row.createCell(x).setCellFormula(String.join("*", formulaSplitted)); + } else { + System.out.println(rows.get(i).getCell(x).getCellType()); + } + } + } + System.out.println("Called writeExcelFileData()"); + try (FileOutputStream outputStream = new FileOutputStream(filename)) { + workbook.write(outputStream); + } + } catch (Exception exWriteExcelFile) { + System.out.println("Error in writeExcelFileData()"); + System.out.println("Error details: " + exWriteExcelFile.toString()); + } + } +} diff --git a/src/com/tesr/OldMain.java b/src/com/tesr/OldMain.java new file mode 100644 index 0000000..2181eca --- /dev/null +++ b/src/com/tesr/OldMain.java @@ -0,0 +1,144 @@ +package com.tesr; + +import org.apache.poi.ss.usermodel.*; + +import javax.swing.*; +import java.io.File; +import java.io.FileOutputStream; +import java.util.ArrayList; +import java.util.List; + +public class OldMain { + + public static void main(String[] args) { + try { + // Variables + //Optimizer myBasicOptimizer = new Optimizer(); + System.out.println("Optimizer instantiated..."); + + // Select Excel File which the user wants to investigate regarding machine optimization potential + // hier wieder ändern String excelFileName = selectExcelFile(); + //hier wieder ändern System.out.println("File via openFileDialog selected: " + excelFileName); + + // Read out data from excel sheet via Apache POI + // hier wieder ändern readExcelFileData(excelFileName); + Sheet sheet = readExcelFileData("/Users/michel/Downloads/Project/ProductionSheet.xlsx"); + List rows = sortExcelFileData(sheet,19); + + // Optimize machine planning and scheduling + //myBasicOptimizer.process(); + + // Write optimized data back to excel sheet + writeExcelFileData("/Users/michel/Downloads/Project/ProductionSheet NEU.xlsx", rows, 1); + + System.out.println("Optimization done."); + } catch (Exception ex) { + System.out.println(ex.toString()); + } + } + + // Select Excel file which we want to read out and retrieve necessary machine data + private static String selectExcelFile(){ + String strFilename = ""; + + try { + JFileChooser fileChooser = new JFileChooser(); + fileChooser.setCurrentDirectory(new File(System.getProperty("user.home"))); + + int result = fileChooser.showOpenDialog(null); + + switch(result) { + case JFileChooser.APPROVE_OPTION: + File selectedFile = fileChooser.getSelectedFile(); + + // user made a selection -> return selected file path + strFilename = selectedFile.toString(); + break; + + case JFileChooser.CANCEL_OPTION: + // user made no selection -> shutdown application + System.out.println("No selection made. Shutting down application..."); + System.exit(0); + break; + + default: + System.out.println("Something went wrong. Shutting down application..."); + System.exit(0); + break; + } + } catch (Exception exFileSelection) { + System.out.println("Error in selectExcelFile()"); + System.out.println("Error details: " + exFileSelection.toString()); + } finally { + return strFilename; + } + } + + private static List sortExcelFileData(Sheet sheet, int sortColumn) + { + List rows = new ArrayList<>(); + try { + + for (int i = 1; i r1.getCell(sortColumn).getStringCellValue().compareTo(r2.getCell(sortColumn).getStringCellValue())); + } + catch (Exception exp) { + System.out.println("Error in writeExcelFileData()"); + System.out.println("Error details: " + exp.toString()); + } + return rows; + } + + private static Sheet readExcelFileData(String fileName) { + Sheet sheet = null; + try { + Workbook workbook = WorkbookFactory.create(new File(fileName)); + sheet = workbook.getSheetAt(1); + } catch (Exception exReadExcelFile) { + System.out.println("Error in readExcelFileData()"); + System.out.println("Error details: " + exReadExcelFile.toString()); + } + return sheet; + } + + private static void writeExcelFileData(String filename, List rows, int startRow){ + try { + Workbook workbook = WorkbookFactory.create(new File("/Users/michel/Downloads/Project/ProductionSheet.xlsx")); + Sheet sheet = workbook.getSheet("Input"); + for (int i = 0; i < rows.size(); i++) + { + Row row = sheet.getRow(rows.get(i).getRowNum()); + row.setRowNum(i + startRow); + for (int x = 0; x < rows.get(i).getPhysicalNumberOfCells(); x++) + { + if (rows.get(i).getCell(x).getCellType() == CellType.FORMULA) + { + String cellFormula = rows.get(i).getCell(x).getCellFormula(); + String[] formulaSplitted = cellFormula.split("\\*"); + for (int y = 0; y < formulaSplitted.length; y++) { + if (!formulaSplitted[y].matches("^[0-9]*$")) { + formulaSplitted[y] = formulaSplitted[y].replace(String.valueOf(rows.get(i).getRowNum() + 1), String.valueOf(i + startRow + 1)); + } + } + row.createCell(x).setCellFormula(String.join("*", formulaSplitted)); + } + else + { + System.out.println(rows.get(i).getCell(x).getCellType()); + } + } + } + try (FileOutputStream outputStream = new FileOutputStream(filename)) { + workbook.write(outputStream); + } + System.out.println("Called writeExcelFileData()"); + } catch (Exception exWriteExcelFile) { + System.out.println("Error in writeExcelFileData()"); + System.out.println("Error details: " + exWriteExcelFile.toString()); + } + } +} diff --git a/src/com/tesr/services/implementations/ExcelService.java b/src/com/tesr/services/implementations/ExcelService.java new file mode 100644 index 0000000..6cea998 --- /dev/null +++ b/src/com/tesr/services/implementations/ExcelService.java @@ -0,0 +1,57 @@ +package com.tesr.services.implementations; + +import java.io.File; +import java.io.IOException; +import java.util.*; + +import com.tesr.services.models.Row; +import org.apache.poi.ss.usermodel.CellType; +import org.apache.poi.xssf.usermodel.XSSFRow; +import org.apache.poi.xssf.usermodel.XSSFSheet; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; + +public class ExcelService { + private XSSFWorkbook workbook; + private XSSFSheet sheet; + + public boolean ReadExcelFile(String filename, String sheetname) + { + try + { + workbook = new XSSFWorkbook(filename); + sheet = workbook.getSheet(sheetname); + return true; + } + catch (IOException exp) { + System.out.println(exp.getMessage()); + System.out.println(exp.getCause()); + exp.printStackTrace(); + return false; + } + } + + public List SortSheetColumn(int columnToSort, int startRow) + { + List rows; + if (sheet != null) + { + rows = new ArrayList<>(); + CellType cellType = sheet.getRow(startRow).getCell(columnToSort).getCellType(); + List sortRows = new ArrayList<>(); + for (int i = startRow; i < sheet.getPhysicalNumberOfRows(); i++) + { + XSSFRow row = sheet.getRow(i); + sortRows.add(new Row(row.getCell(columnToSort).getStringCellValue(), row)); + } + sortRows.sort((r1, r2) -> r1.getKey().compareTo(r2.getKey())); + sortRows.forEach(r -> { + rows.add(r.getRow()); + }); + } + else + { + throw new NullPointerException("Null Pointer Exception sheet"); + } + return rows; + } +} diff --git a/src/com/tesr/services/implementations/SortExcelService.java b/src/com/tesr/services/implementations/SortExcelService.java new file mode 100644 index 0000000..4d24ca7 --- /dev/null +++ b/src/com/tesr/services/implementations/SortExcelService.java @@ -0,0 +1,4 @@ +package com.tesr.services.implementations; + +public class SortExcelService { +} diff --git a/src/com/tesr/services/models/Row.java b/src/com/tesr/services/models/Row.java new file mode 100644 index 0000000..b113d05 --- /dev/null +++ b/src/com/tesr/services/models/Row.java @@ -0,0 +1,32 @@ +package com.tesr.services.models; + +import org.apache.poi.xssf.usermodel.XSSFRow; + +public class Row +{ + private String Key; + + public String getKey() { + return Key; + } + + public void setKey(String key) { + Key = key; + } + + private XSSFRow Row; + + public XSSFRow getRow() { + return Row; + } + + public void setRow(XSSFRow row) { + Row = row; + } + + public Row(String key, XSSFRow row) + { + this.Key = key; + this.Row = row; + } +} diff --git a/src/test/.DS_Store b/src/test/.DS_Store new file mode 100644 index 0000000..9201764 --- /dev/null +++ b/src/test/.DS_Store Binary files differ diff --git a/src/test/com/.DS_Store b/src/test/com/.DS_Store new file mode 100644 index 0000000..6327077 --- /dev/null +++ b/src/test/com/.DS_Store Binary files differ diff --git a/src/test/com/tesr/.DS_Store b/src/test/com/tesr/.DS_Store new file mode 100644 index 0000000..3ac8e39 --- /dev/null +++ b/src/test/com/tesr/.DS_Store Binary files differ diff --git a/src/test/com/tesr/services/.DS_Store b/src/test/com/tesr/services/.DS_Store new file mode 100644 index 0000000..041ed49 --- /dev/null +++ b/src/test/com/tesr/services/.DS_Store Binary files differ diff --git a/src/test/com/tesr/services/implementations/ExcelServiceTest.java b/src/test/com/tesr/services/implementations/ExcelServiceTest.java new file mode 100644 index 0000000..b3a85bd --- /dev/null +++ b/src/test/com/tesr/services/implementations/ExcelServiceTest.java @@ -0,0 +1,58 @@ +package test.com.tesr.services.implementations; + +import com.tesr.services.implementations.ExcelService; +import org.apache.poi.xssf.usermodel.XSSFRow; +import org.junit.Assert; +import org.junit.Test; +import org.junit.Before; +import org.junit.After; + +import java.util.List; + +/** +* ExcelService Tester. +* +* @author +* @since 
Juni 3, 2021
+* @version 1.0 +*/ +public class ExcelServiceTest { + +@Before +public void before() throws Exception { +} + +@After +public void after() throws Exception { +} + +/** +* +* Method: ReadExcelFile(String filename, String sheetname) +* +*/ +@Test +public void testReadExcelFile() throws Exception { + ExcelService excelService = new ExcelService(); + Assert.assertTrue(excelService.ReadExcelFile("/Users/michel/Downloads/Financial Sample.xlsx", "Sheet1")); +} + +/** +* +* Method: SortSheetColumn(int columnToSort, int startRow, boolean wholeTable) +* +*/ +@Test +public void testSortSheetColumn() throws Exception { + ExcelService excelService = new ExcelService(); + excelService.ReadExcelFile("/Users/michel/Downloads/Financial Sample.xlsx", "Sheet1"); + List result = excelService.SortSheetColumn(1, 2); + Assert.assertNotNull(result); + Assert.assertTrue(result.get(19).getCell(1).getStringCellValue().equals("Canada")); + Assert.assertTrue(result.get(141).getCell(1).getStringCellValue().equals("France")); + Assert.assertTrue(result.get(279).getCell(1).getStringCellValue().equals("Germany")); + Assert.assertTrue(result.get(279).getCell(4).getNumericCellValue() == 1321); +} + + +}