Controller.ThreadExcelImport.java Source code

Java tutorial

Introduction

Here is the source code for Controller.ThreadExcelImport.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Observable;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import com.monitorjbl.xlsx.StreamingReader;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import javax.swing.JOptionPane;
import org.apache.commons.codec.digest.DigestUtils;

/**
 *
 * @author moises
 */
public class ThreadExcelImport extends Observable implements java.lang.Runnable {

    public boolean isRunning = false;
    private String messageProgress = null;
    private String templateName;
    private String srcFileP1;
    private String srcFileP2;
    public String threadName = null;
    private ArrayList<Header> headerP1 = null;
    private ArrayList<Header> headerP2 = null;
    private ArrayList<String> colunaChave = new ArrayList<String>();
    private Map<String, InterfaceMigracao> listaP2 = new HashMap<String, InterfaceMigracao>();

    public ThreadExcelImport(String templateName, String srcFileP1, String srcFileP2, ArrayList<Header> header,
            ArrayList<String> colunaChave) {
        this.templateName = templateName;
        this.srcFileP1 = srcFileP1;
        this.srcFileP2 = srcFileP2;
        this.headerP1 = new ArrayList<Header>(header);
        this.headerP2 = new ArrayList<Header>(header);
        this.threadName = threadName;
        this.colunaChave = colunaChave;

        isRunning = true;
    }

    public boolean isRunning() {
        return isRunning;
    }

    @Override
    public void run() {

        //******
        // CRIA STREAM DAS PLANILHAS
        // *******************

        // stream planilha 1
        InputStream stream1 = null;
        try {
            stream1 = new FileInputStream(new File(srcFileP1));
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
        }
        Workbook workbook1 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
                .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
                .open(stream1);

        // stream planilha 2
        InputStream stream2 = null;
        try {
            stream2 = new FileInputStream(new File(srcFileP2));
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
        }
        Workbook workbook2 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
                .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
                .open(stream2);

        //******
        // VERIFICA OS CABECALHOS
        // *******************

        // cabealhos da planilha 1
        Sheet sheet1 = null;
        sheet1 = workbook1.getSheetAt(0);

        // Pega de acordo com o cabealho as opes
        for (Row r : sheet1) {
            if (r.getRowNum() > 0)
                break;
            for (Integer i = 0; i < headerP1.size(); i++) {
                for (Cell c : r) {
                    if (c.getStringCellValue().toLowerCase()
                            .equals(headerP1.get(i).getColumnName().toLowerCase())) {
                        // Adiciona o numero da coluna ao header
                        headerP1.get(i).setColumnNumber(c.getColumnIndex());
                        break;
                    }
                }

                if (headerP1.get(i).getColumnNumber() == null) {
                    // Alguma coluna do template est ausente
                    JOptionPane.showMessageDialog(null, "A coluna " + headerP1.get(i).getColumnName().toLowerCase()
                            + " do template no existe como cabealho na planilha 1");
                    System.exit(0);
                }

            }
        }
        // cabealhos da planilha 2
        Sheet sheet2 = null;
        sheet2 = workbook2.getSheetAt(0);

        // Pega de acordo com o cabealho as opes
        for (Row r : sheet2) {
            if (r.getRowNum() > 0)
                break;
            for (Integer i = 0; i < headerP2.size(); i++) {
                for (Cell c : r) {
                    if (c.getStringCellValue().toLowerCase()
                            .equals(headerP2.get(i).getColumnName().toLowerCase())) {
                        // Adiciona o numero da coluna ao header
                        headerP2.get(i).setColumnNumber(c.getColumnIndex());
                        break;
                    }
                }

                if (headerP2.get(i).getColumnNumber() == null) {
                    // Alguma coluna do template est ausente
                    JOptionPane.showMessageDialog(null, "A coluna " + headerP2.get(i).getColumnName().toLowerCase()
                            + " do template no existe como cabealho na planilha 2");
                    System.exit(0);
                }

            }
        }

        //******
        // GRAVA EM MEMRIA A PLANILHA 2 PARA EVITAR O REABRIMENTO DA MESMA A CADA ITERAO DA PLANILHA 1
        // *******************
        stream2 = null;
        try {
            stream2 = new FileInputStream(new File(srcFileP2));
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
        }
        workbook2 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
                .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
                .open(stream2);

        sheet2 = null;
        sheet2 = workbook2.getSheetAt(0);

        for (Row rowP2 : sheet2) {

            if (rowP2.getRowNum() > 0) {
                InterfaceMigracao objInterfaceP2 = Factory.getInstance(templateName);

                // calcula o hash
                String hashChaveP2 = "";
                for (String chaveP2 : colunaChave) {
                    Integer columIndex = -1;
                    for (Header he2 : headerP2) {
                        if (he2.getColumnName().equals(chaveP2)) {
                            columIndex = he2.getColumnNumber();
                            break;
                        }
                    }

                    if (columIndex > -1) {
                        Cell cell = null;
                        cell = rowP2.getCell(columIndex, Row.CREATE_NULL_AS_BLANK);
                        // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 );
                        hashChaveP2 = DigestUtils
                                .sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP2);
                    }

                }

                for (Header he2 : headerP2) {

                    Cell cell = rowP2.getCell(he2.getColumnNumber(), Row.CREATE_NULL_AS_BLANK);
                    objInterfaceP2.setString(he2.getColumnName(), cell.getStringCellValue().trim().toLowerCase());
                    objInterfaceP2.setExcelRowNumber((rowP2.getRowNum() + 1));
                    //System.out.println("Novo loop HeaderP2 da linhaP2 " + String.valueOf(rowP2.getRowNum()) + " coluna " + he2.getColumnName() );
                }

                if (hashChaveP2.equals("")) {
                    JOptionPane.showMessageDialog(null, "A linha " + String.valueOf((rowP2.getRowNum() + 1))
                            + " da planilha 2 tem as colunas chaves nula");
                    System.exit(0);
                } else
                    listaP2.put(hashChaveP2, objInterfaceP2);

            }
        }

        // limpa da memoria a workbook2
        try {
            if (workbook2 != null)
                workbook2.close();
        } catch (IOException ex) {
            Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
        }

        // limpa da memoria o stream com workbook2
        if (stream2 != null)
            try {
                stream2.close();
            } catch (IOException ex) {
                Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
            }

        //******
        // FAZ A VALIDAO
        // OBSERVE QUE POR TER FEITO O FOREACH NOS PLANILHAS SE TORNA NECESS?RIO RECRIAR O STREAMING
        // *******************

        // Executa o loop nas linhas da planilha

        stream1 = null;
        try {
            stream1 = new FileInputStream(new File(srcFileP1));
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
        }
        workbook1 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
                .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
                .open(stream1);

        sheet1 = null;
        sheet1 = workbook1.getSheetAt(0);

        InterfaceMigracao objInterfaceP1 = null;

        for (Row rowP1 : sheet1) {

            // Pega o hash dos campos chaves da planilha 1 a fim de localizar na planilha 1
            String hashChaveP1 = "";
            for (String chaveP1 : colunaChave) {
                Integer columIndex = -1;
                for (Header he1 : headerP1) {
                    if (he1.getColumnName().equals(chaveP1)) {
                        columIndex = he1.getColumnNumber();
                        break;
                    }
                }

                if (columIndex > -1) {
                    Cell cell = null;
                    cell = rowP1.getCell(columIndex, Row.CREATE_NULL_AS_BLANK);
                    // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 );
                    hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1);
                }

            }

            objInterfaceP1 = Factory.getInstance(templateName);
            // objInterfaceP2 = Factory.getInstance(templateName);

            objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1));
            Notify notify = new Notify();

            if (hashChaveP1.equals(""))
                notify.setLocalizadoP1(false);
            else {
                notify.setLocalizadoP1(true);
                //seta o numero da linha no excel

                // Preenche o objeto de interface da planilha 1 com seus respectivos dados
                for (Header he1 : headerP1) {

                    Cell cell = null;
                    cell = rowP1.getCell(he1.getColumnNumber(), Row.CREATE_NULL_AS_BLANK);
                    objInterfaceP1.setString(he1.getColumnName(), cell.getStringCellValue().trim().toLowerCase());
                }

                boolean p2Localizado = false;

                // Preenche o objeto de interface da planilha 2 com seus respectivos dados
                if (rowP1.getRowNum() > 0) {
                    InterfaceMigracao objInterfaceMigracaoP2 = listaP2.get(hashChaveP1);
                    if (objInterfaceMigracaoP2 != null) {
                        p2Localizado = true;
                        notify.setEntidadeP2(objInterfaceMigracaoP2);
                    }

                }
                notify.setLocalizadoP2(p2Localizado);

            }

            isRunning = true;

            objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1));
            notify.setEntidadeP1(objInterfaceP1);
            notify.setTotalRow((sheet1.getLastRowNum() + 1));

            notify.setRunning(isRunning);
            notify.setHeaderP1(headerP1);
            notify.setHeaderP2(headerP2);

            setChanged();
            notifyObservers(notify);

        }

        isRunning = false;
        // Notifica os observadores de que a execuo terminou
        Notify notify = new Notify();
        notify.setRunning(false);
        setChanged();
        notifyObservers(notify);
        listaP2 = null;

    }

    private String getStringFromCell(Cell cell) {
        int tipo = cell.getCellType();
        String value = "";
        switch (tipo) {

        //NUMERIC
        case 0:
            DataFormatter df = new DataFormatter();
            // value = df.formatCellValue(row.getCell(columnPos));
            value = String.valueOf(cell.getNumericCellValue()).trim().toLowerCase();
            //at.setString(columnName, value);
            break;
        //STRING
        case 1:
            value = cell.getStringCellValue().trim().toLowerCase();
            //at.setString(columnName, value);
            break;
        // FORMULA

        // BLANK
        case 3:
            value = ""; //row.getCell(columnPos).getStringCellValue();
            //at.setString(columnName, value);
            break;
        // BOOLEAN
        case 4:
            value = String.valueOf(cell.getBooleanCellValue()).trim().toLowerCase();
            //  at.setString(columnName, value);
            break;
        // NONE (ERROR)
        case 5:
            value = cell.getStringCellValue().trim().toLowerCase();
            //at.setString(columnName, value);
            break;

        }
        return value;

        //return  String.valueOf( cell.getNumericCellValue());
    }

}