Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.io.File;
- import java.io.FileNotFoundException;
- import java.io.FileWriter;
- import java.io.IOException;
- import java.io.PrintWriter;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.Iterator;
- import java.util.Scanner;
- import org.apache.poi.hssf.util.CellReference;
- import org.apache.poi.ss.usermodel.*;
- public class Main {
- public static ArrayList<File> files = new ArrayList<>();
- public static void main(String[] args) {
- Scanner in = new Scanner(System.in);
- System.out.println("Unesite pocetni indeks: ");
- int ID = in.nextInt() - 1;
- String tmpParent = null;
- System.out.println("Unesite putanju do IZVOZ_arh");
- in = new Scanner(System.in);
- String path = in.nextLine();
- listFilesForFolder(path,files);
- Workbook workbook = null;
- boolean isPL;
- for(File f: files){
- isPL = false;
- if(f.getName().contains("xlsx") && f.getName().charAt(0)!='~') {
- try {
- workbook = WorkbookFactory.create(f);
- for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
- if (workbook.getSheetAt(i).getSheetName().contains("PL"))
- isPL = true;
- }
- if(!f.getParent().equals(tmpParent)) {
- ID++;
- tmpParent = f.getParent();
- }
- if (isPL) {
- System.out.println("\nPACKING LIST\n");
- System.out.println(f.getAbsoluteFile());
- //printToFile("\n"+f.getAbsolutePath()+"\n",true);
- handlePL(workbook,f,ID);
- } else {
- System.out.println("\nOTHER\n");
- System.out.println(f.getAbsoluteFile());
- //printToFile("\n"+f.getAbsolutePath()+"\n",false);
- handleOther(workbook,f,ID);
- }
- }catch (Exception e) {
- e.printStackTrace();
- System.err.println("\n\n"+f.getAbsolutePath()+"\n\n*******Neispravan fajl*******");
- }
- }
- }
- }
- private static void handlePL(Workbook workbook, File f, int ID){
- int gornjaGranica = 9;
- FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
- boolean imaNapomenu = false;
- int cliente = 0;
- try {
- String[] fakture = null;
- Sheet sheet;
- Cell fatturaCell = null;
- String fattura;
- Iterator<Row> rowIterator = null;
- Cell dateCell = null;
- sheet = workbook.getSheet("PL");
- if (sheet.iterator() != null)
- rowIterator = sheet.iterator();
- if (sheet.getRow(9).getCell(0).getCellType() == Cell.CELL_TYPE_STRING)
- if (sheet.getRow(9).getCell(0).getStringCellValue().contains("CLIENTE")) {
- gornjaGranica = 10;
- cliente = 1;
- }
- if (rowIterator != null) {
- while (rowIterator.hasNext()) {
- Row r = rowIterator.next();
- Cell c;
- boolean found = false;
- if (r.getRowNum() > 4)
- break;
- int start = r.getFirstCellNum();
- int end = r.getLastCellNum();
- for (int i = start; i < end; i++) {
- if ((r.getCell(i) != null) && (c = r.getCell(i)).getCellType() == Cell.CELL_TYPE_STRING) {
- if (c.getStringCellValue().contains("Fattura") || c.getStringCellValue().contains("fattura")) {
- if(cliente == 0)
- fatturaCell = c;
- else
- fatturaCell = r.getCell(i+1);
- found = true;
- break;
- }
- }
- }
- if (found)
- break;
- }
- }
- if (fatturaCell != null) {
- fattura = fatturaCell.getStringCellValue();
- if(cliente == 0) {
- fakture = fattura.substring(fattura.indexOf(":") + 1).trim().split(";");
- System.out.println(fattura.substring(fattura.indexOf(":") + 1).trim());
- }
- else{
- fakture = fattura.trim().split(";");
- System.out.println(fattura.trim());
- }
- //printToFile(fattura.substring(fattura.indexOf(":") + 1).trim(),true);
- }
- rowIterator = sheet.iterator();
- while (rowIterator.hasNext()) {
- Row r = rowIterator.next();
- Cell c;
- boolean found = false;
- if (r.getRowNum() > 4)
- break;
- int start = r.getFirstCellNum();
- int end = r.getLastCellNum();
- for (int i = start; i <= end; i++) {
- if (r.getCell(i) != null && (c = r.getCell(i)).getCellType() == Cell.CELL_TYPE_NUMERIC) {
- if (DateUtil.isCellDateFormatted(c)) {
- dateCell = c;
- found = true;
- break;
- }
- }
- }
- if (found)
- break;
- }
- if (dateCell != null) {
- String format = "yyyy-MM-dd";
- SimpleDateFormat date = new SimpleDateFormat(format);
- String dateString = date.format(dateCell.getDateCellValue());
- System.out.println("DATUM: " + dateString);
- //printToFile("DATUM: " + dateString,true);
- if (fatturaCell != null) {
- for (int i = 0; i < fakture.length; i++) {
- printID(ID + "#" + dateString + "#" + fakture[i].trim());
- }
- }
- }
- rowIterator = sheet.iterator();
- while (rowIterator.hasNext()) {
- Row rowIte = rowIterator.next();
- if (cliente == 0)
- if (rowIte.getRowNum() == gornjaGranica - 1) {
- if (rowIte.getCell(12) != null && rowIte.getCell(12).getCellType() == Cell.CELL_TYPE_STRING &&
- rowIte.getCell(12).getStringCellValue().contains("Napomena")) {
- imaNapomenu = true;
- }
- }
- String rowBuff = "";
- if (cliente == 0) {
- try {
- if (rowIte.getRowNum() >= gornjaGranica) {
- if(rowIte.getCell(0)!= null) {
- if (rowIte.getCell(0).getCellType() == Cell.CELL_TYPE_BLANK)
- return;
- if (rowIte.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
- if (rowIte.getCell(0).getStringCellValue().contains("blank"))
- return;
- }
- }
- else return;
- if (rowIte.getCell(2 ).getCellType() == Cell.CELL_TYPE_STRING)
- rowBuff += rowIte.getCell(2 ).getStringCellValue();
- else if (rowIte.getCell(2 ).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += rowIte.getCell(2 ).getNumericCellValue();
- rowBuff += "#";
- if (rowIte.getCell(3 ).getCellType() == Cell.CELL_TYPE_STRING)
- rowBuff += rowIte.getCell(3 ).getStringCellValue();
- else if (rowIte.getCell(3 ).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += rowIte.getCell(3 ).getNumericCellValue();
- rowBuff += "#";
- if (rowIte.getCell(8 ).getCellType() == Cell.CELL_TYPE_STRING)
- rowBuff += rowIte.getCell(8 ).getStringCellValue();
- else if (rowIte.getCell(8 ).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += rowIte.getCell(8 ).getNumericCellValue();
- rowBuff += "#";
- if (evaluator.evaluate(rowIte.getCell(9 )).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += String.format("%.0f", evaluator.evaluate(rowIte.getCell(9 )).getNumberValue());
- else
- rowBuff += evaluator.evaluate(rowIte.getCell(9 )).getStringValue();
- rowBuff += "#";
- int napomena;
- if (imaNapomenu) napomena = 1;
- else napomena = 0;
- if(evaluator.evaluate(rowIte.getCell(12+napomena))!= null) {
- if (evaluator.evaluate(rowIte.getCell(12 + napomena)).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += String.format("%.0f", evaluator.evaluate(rowIte.getCell(12 + napomena)).getNumberValue());
- else
- rowBuff += evaluator.evaluate(rowIte.getCell(12 + napomena)).getStringValue();
- }
- rowBuff += "#";
- if (rowIte.getCell(13 + napomena ) != null && evaluator.evaluate(rowIte.getCell(13 + napomena )).getCellType() == Cell.CELL_TYPE_NUMERIC) {
- double n = evaluator.evaluate(rowIte.getCell(13 + napomena )).getNumberValue();
- String s = String.format("%.0f", n);
- int length = s.length();
- for (int i = 0; i < (12 - length); i++) {
- s += "0";
- }
- rowBuff += s;
- } else if (rowIte.getCell(13 + napomena ) != null) {
- String s = evaluator.evaluate(rowIte.getCell(13 + napomena )).getStringValue();
- int length = s.length();
- for (int i = 0; i < (12 - length); i++) {
- s += "0";
- }
- rowBuff += s;
- } else rowBuff += "";
- rowBuff += "#";
- if (evaluator.evaluate(rowIte.getCell(14 + napomena)) != null) {
- if (evaluator.evaluate(rowIte.getCell(14 + napomena)).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += String.format("%.0f", evaluator.evaluate(rowIte.getCell(14 + napomena)).getNumberValue());
- else if (rowIte.getCell(14 + napomena) != null)
- rowBuff += evaluator.evaluate(rowIte.getCell(14 + napomena)).getStringValue();
- }
- else
- System.err.println("\nGRESKA:Neispravna celija u 14. koloni " + rowIte.getRowNum() + ". reda");
- }
- } catch (NullPointerException e) {
- e.printStackTrace();
- System.err.println(f.getAbsolutePath());
- }
- if (!rowBuff.isEmpty() && !rowBuff.contains("####")) {
- printToFile(ID + "#" + rowBuff, true);
- System.out.print("\n" + ID + "#" + rowBuff);
- // System.out.println(Folder1 + " ~ " + Fajl1 + " VAL");
- // Bila Metoda
- //metoda("", "", rowBuff);
- }
- } else {
- try {
- if (rowIte.getRowNum() >= gornjaGranica) {
- if (rowIte.getCell(1).getCellType() == Cell.CELL_TYPE_BLANK)
- return;
- if (rowIte.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) {
- if (rowIte.getCell(1).getStringCellValue().contains("blank"))
- return;
- }
- if (rowIte.getCell(3).getCellType() == Cell.CELL_TYPE_STRING)
- rowBuff += rowIte.getCell(3).getStringCellValue();
- else if (rowIte.getCell(3).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += rowIte.getCell(3).getNumericCellValue();
- rowBuff += "#";
- if (rowIte.getCell(4).getCellType() == Cell.CELL_TYPE_STRING)
- rowBuff += rowIte.getCell(4).getStringCellValue();
- else if (rowIte.getCell(4).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += rowIte.getCell(4).getNumericCellValue();
- rowBuff += "#";
- if (rowIte.getCell(6).getCellType() == Cell.CELL_TYPE_STRING)
- rowBuff += rowIte.getCell(6).getStringCellValue();
- else if (rowIte.getCell(6).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += rowIte.getCell(6).getNumericCellValue();
- rowBuff += "#";
- if (rowIte.getCell(11).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += String.format("%.0f", rowIte.getCell(11).getNumericCellValue());
- else
- rowBuff += rowIte.getCell(11).getStringCellValue();
- rowBuff += "#";
- // 3266
- // if (rowIte.getCell(12 ) != null && rowIte.getCell(12 ).getCellType() == Cell.CELL_TYPE_NUMERIC)
- // rowBuff += String.format("%.0f", rowIte.getCell(12 ).getNumericCellValue());
- // else if (rowIte.getCell(12 ) != null)
- // rowBuff += rowIte.getCell(12 ).getStringCellValue();
- // rowBuff += "#";
- if (rowIte.getCell(8) != null && evaluator.evaluate(rowIte.getCell(8)).getCellType() == Cell.CELL_TYPE_NUMERIC) {
- double n = evaluator.evaluate(rowIte.getCell(8)).getNumberValue();
- String s = String.format("%.0f", n);
- int length = s.length();
- for (int i = 0; i < (12 - length); i++) {
- s += "0";
- }
- rowBuff += s;
- } else if (rowIte.getCell(8) != null) {
- String s = evaluator.evaluate(rowIte.getCell(8)).getStringValue();
- int length = s.length();
- for (int i = 0; i < (12 - length); i++) {
- s += "0";
- }
- rowBuff += s;
- } else rowBuff += "";
- // rowBuff += "#";
- //
- // if (rowIte.getCell(14 ) != null && evaluator.evaluate(rowIte.getCell(14 )).getCellType() == Cell.CELL_TYPE_NUMERIC)
- // rowBuff += String.format("%.0f", evaluator.evaluate(rowIte.getCell(14 )).getNumberValue());
- // else if (rowIte.getCell(14 ) != null)
- // rowBuff += evaluator.evaluate(rowIte.getCell(14 )).getStringValue();
- //
- // else
- // System.err.println("\nGRESKA:Neispravna celija u 14. koloni " + rowIte.getRowNum() + ". reda");
- }
- } catch (NullPointerException e) {
- e.printStackTrace();
- System.err.println(f.getAbsolutePath());
- }
- if (!rowBuff.isEmpty() && !rowBuff.contains("####")) {
- printToFile(ID + "#" + rowBuff, true);
- System.out.print("\n" + ID + "#" + rowBuff);
- // System.out.println(Folder1 + " ~ " + Fajl1 + " VAL");
- // Bila Metoda
- //metoda("", "", rowBuff);
- }
- }
- }
- } catch(Exception e){
- e.printStackTrace();
- System.err.println(f.getAbsolutePath());
- }
- }
- private static void handleOther(Workbook workbook, File f, int ID){
- int gornjaGranica = 4;
- try {
- Sheet sheet = workbook.getSheetAt(0);
- Iterator<Row> rowIterator = sheet.iterator();
- String tmp = "";
- while (rowIterator.hasNext()) {
- Row rowIte = rowIterator.next();
- String rowBuff = "";
- try {
- if (rowIte.getRowNum() >= gornjaGranica) {
- if(rowIte.getCell(0) == null)
- continue;
- if(rowIte.getCell(0).getCellType() == Cell.CELL_TYPE_STRING){
- if(rowIte.getCell(0).getStringCellValue().contains("ID"))
- continue;
- if(rowIte.getCell(0).getStringCellValue().contains("Codice"))
- continue;
- if(rowIte.getCell(0).getStringCellValue().contains("Deposito")){
- tmp = rowIte.getCell(1).getStringCellValue().trim();
- continue;
- }
- }
- if(rowIte.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += String.format("%.0f",rowIte.getCell(0).getNumericCellValue());
- else
- rowBuff +=rowIte.getCell(0).getStringCellValue();
- rowBuff += "#";
- rowBuff += tmp;
- rowBuff += "#";
- if(rowIte.getCell(11) != null &&rowIte.getCell(11).getCellType()!=Cell.CELL_TYPE_BLANK){
- if(rowIte.getCell(11).getCellType() == Cell.CELL_TYPE_NUMERIC)
- rowBuff += String.format("%d",((int) rowIte.getCell(11).getNumericCellValue()));
- else if (rowIte.getCell(11).getCellType() == Cell.CELL_TYPE_STRING)
- rowBuff += rowIte.getCell(11).getStringCellValue();
- }
- }
- }catch (NullPointerException e) {
- e.printStackTrace();
- System.err.println(f.getAbsolutePath());
- }
- if(!rowBuff.isEmpty() && !rowBuff.contains("####")) {
- printToFile(ID+"#"+rowBuff, false);
- System.out.print("\n" + ID+"#"+rowBuff);
- }
- }
- } catch (Exception e){
- e.printStackTrace();
- System.err.println(f.getAbsolutePath());
- }
- }
- private static void listFilesForFolder(String path, ArrayList<File> files) {
- File directory = new File(path);
- File[] flist = directory.listFiles();
- if(flist!=null) {
- for (final File file : flist) {
- if (file.isFile()) {
- files.add(file);
- } else if (file.isDirectory()) {
- listFilesForFolder(file.getPath(), files);
- }
- }
- }
- }
- // Stampanje u tekstualni fajl
- private static void printToFile(String xlsxBuff, boolean pl ) throws IOException {
- String fileName;
- if(pl)
- fileName = "packlist.txt";
- else fileName = "other.txt";
- try {
- PrintWriter outStream = new PrintWriter(new FileWriter(fileName, true));
- outStream.println(xlsxBuff );
- outStream.close();
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- }
- }
- private static void printID(String line) throws IOException{
- String fileName = "packlistID.txt";
- try {
- PrintWriter outStream = new PrintWriter(new FileWriter(fileName, true));
- outStream.println(line );
- outStream.close();
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement