List change from an Apache poi excel import

Question

The system can generate an excel file where all the products of a serialized stock are found. What I would like is that when this file is imported back that all changes made to it are applied within the system. Here’s a minimal verifiable example of what I’ve been able to do so far

import java.awt.Label;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.swing.JFileChooser;
import javax.swing.JFormattedTextField;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.filechooser.FileFilter;
import javax.swing.table.AbstractTableModel;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class MinimoVerificavel
{
    public class Products
    {
        private int code;
        private String name;
        private float cost;
        private float price;
        private float netProfit;

        public int getCode()
        {
            return code;
        }

        public void setCode(int code)
        {
            this.code = code;
        }

        public String getName()
        {
            return name;
        }

        public void setName(String name)
        {
            this.name = name;
        }

        public float getCost()
        {
            return cost;
        }

        public void setCost(float cost)
        {
            this.cost = cost;
        }

        public float getPrice()
        {
            return price;
        }

        public void setPrice(float price)
        {
            this.price = price;
        }

        public float getNetProfit()
        {
            return netProfit;
        }

        public void setNetProfit(float netProfit)
        {
            this.netProfit = netProfit;
        }
    }

    // -------------------------------------------------------------------------------------------------------------
    public interface Dao<P>
    {
        public void change(P obj) throws Exception;
    }

    // -------------------------------------------------------------------------------------------------------------
    public class Change implements Dao<Products>
    {
        private List<Products> productsList = new ArrayList<>();

        @Override
        public void change(Products obj) throws Exception
        {
            int code = obj.getCode();

            for (Products search : productsList)
            {
                if (search.getCode() == code)
                {
                    search.setName(obj.getName());
                    search.setCost(obj.getCost());
                    search.setPrice(obj.getPrice());
                    search.setNetProfit(obj.getNetProfit());

                    productsList.add(search);
                }
            }
        }
    }

    // -------------------------------------------------------------------------------------------------------------
    @SuppressWarnings("serial")
    public class ModelTableProduts extends AbstractTableModel
    {
        private List<Products> products = new ArrayList<>();

        public ModelTableProduts(List<Products> list)
        {
            products = list;
        }

        @Override
        public String getColumnName(int column)
        {
            switch (column)
            {
                case 0:
                    return "Cod";
                case 1:
                    return "Nome";
                case 2:
                    return "Custo";
                case 3:
                    return "Preço";
                case 4:
                    return "Lucro";
            }
            return super.getColumnName(column);
        }

        @Override
        public int getColumnCount()
        {
            return 5;
        }

        @Override
        public int getRowCount()
        {
            return products.size();
        }

        @Override
        public Object getValueAt(int row, int column)
        {
            Products p = products.get(row);
            switch (column)
            {
                case 0:
                    return p.getCode();
                case 1:
                    return p.getName();
                case 2:
                    return p.getCost();
                case 3:
                    return p.getPrice();
                case 4:
                    return p.getNetProfit();
                default:
                    return null;
            }
        }
    }

    // -------------------------------------------------------------------------------------------------------------
    @SuppressWarnings("serial")
    public class FrameMinimo extends JFrame
    {
        // Menu
        private JMenuBar menuBar;
        private JMenu menu;
        private JMenuItem exp;
        private JMenuItem imp;

        // Cod
        private Label lblCode;
        private JFormattedTextField ftCode;

        // Name
        private JLabel lblName;
        private JTextField txtName;

        // Cost
        private JLabel lblCost;
        private JFormattedTextField ftCost;

        // Price
        private JLabel lblPrice;
        private JFormattedTextField ftPrice;

        // Net profit
        private JLabel lblNetProfit;
        private JFormattedTextField ftNetProfit;

        // table
        private JTable table;
        private JScrollPane scroll;

        // List
        private List<Products> productsList = new ArrayList<>();

        // DAO
        private Dao<Products> dao = new Change();

        public FrameMinimo()
        {
            components();
            events();
            addProduct();
            buildTable();
        }

        private void components()
        {
            setTitle("Importar Excel e alterar");
            setSize(655, 300);
            setLocationRelativeTo(null);
            setLayout(null);
            setResizable(false);
            setDefaultCloseOperation(EXIT_ON_CLOSE);

            // Menu
            menuBar = new JMenuBar();
            menuBar.setFont(getFont());
            setJMenuBar(menuBar);

            menu = new JMenu("Menu");
            menu.setFont(getFont());
            menuBar.add(menu);

            exp = new JMenuItem("Exportar");
            exp.setFont(getFont());
            menu.add(exp);

            imp = new JMenuItem("Importar");
            imp.setFont(getFont());
            menu.add(imp);

            // cod
            lblCode = new Label("Cod");
            lblCode.setFont(getFont());
            lblCode.setSize(50, 25);
            lblCode.setLocation(10, 10);
            add(lblCode);
            ftCode = new JFormattedTextField();
            ftCode.setFont(getFont());
            ftCode.setSize(50, 25);
            ftCode.setLocation(10, lblCode.getY() + lblCode.getHeight());
            ftCode.setEditable(false);
            add(ftCode);

            // Name
            lblName = new JLabel("Nome");
            lblName.setFont(getFont());
            lblName.setSize(50, 25);
            lblName.setLocation(10, ftCode.getY() + ftCode.getHeight());
            add(lblName);
            txtName = new JTextField();
            txtName.setFont(getFont());
            txtName.setSize(300, 25);
            txtName.setLocation(10, lblName.getY() + lblName.getHeight());
            txtName.setEditable(false);
            add(txtName);

            // Cost
            lblCost = new JLabel("Custo");
            lblCost.setFont(getFont());
            lblCost.setSize(50, 25);
            lblCost.setLocation(txtName.getX() + txtName.getWidth() + 10, ftCode.getY() + ftCode.getHeight());
            add(lblCost);
            ftCost = new JFormattedTextField();
            ftCost.setFont(getFont());
            ftCost.setSize(100, 25);
            ftCost.setLocation(txtName.getX() + txtName.getWidth() + 10, lblCost.getY() + lblCost.getHeight());
            ftCost.setEditable(false);
            add(ftCost);

            // Price
            lblPrice = new JLabel("Preço");
            lblPrice.setFont(getFont());
            lblPrice.setSize(50, 25);
            lblPrice.setLocation(ftCost.getX() + ftCost.getWidth() + 10, ftCode.getY() + ftCode.getHeight());
            add(lblPrice);
            ftPrice = new JFormattedTextField();
            ftPrice.setFont(getFont());
            ftPrice.setSize(100, 25);
            ftPrice.setLocation(ftCost.getX() + ftCost.getWidth() + 10, lblPrice.getY() + lblPrice.getHeight());
            ftPrice.setEditable(false);
            add(ftPrice);

            // Net profit
            lblNetProfit = new JLabel("Lucro liquido");
            lblNetProfit.setFont(getFont());
            lblNetProfit.setSize(100, 25);
            lblNetProfit.setLocation(ftPrice.getX() + ftPrice.getWidth() + 10, ftCode.getY() + ftCode.getHeight());
            add(lblNetProfit);
            ftNetProfit = new JFormattedTextField();
            ftNetProfit.setFont(getFont());
            ftNetProfit.setSize(100, 25);
            ftNetProfit.setLocation(ftPrice.getX() + ftPrice.getWidth() + 10, lblNetProfit.getY() + lblNetProfit.getHeight());
            ftNetProfit.setEditable(false);
            add(ftNetProfit);

            // Table
            table = new JTable();
            scroll = new JScrollPane(table);
            scroll.setSize(631, 103);
            scroll.setLocation(10, txtName.getY() + txtName.getHeight() + 5);
            add(scroll);

        }

        private void buildTable()
        {
            ModelTableProduts modelTableProduts = new ModelTableProduts(productsList);
            table.setModel(modelTableProduts);
        }

        private void exp()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("Estoque");
            File file = new File("Estoque.xls");
            JFileChooser chooser = new JFileChooser();

            // Chooser properties
            chooser.setDialogTitle("Exportar para XLS");
            chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
            chooser.setFileFilter(null);

            chooser.setFileFilter(new FileFilter()
            {
                @Override
                public String getDescription()
                {
                    return "XLS File";
                }

                @Override
                public boolean accept(File f)
                {
                    return f.getName().toLowerCase().endsWith("xls");
                }
            });

            int action = chooser.showSaveDialog(null);

            if (action == JFileChooser.APPROVE_OPTION)
            {
                file = chooser.getSelectedFile();
                String fileString = file.toString();

                if (fileString.toLowerCase().endsWith("xls") == false)
                {
                    fileString += ".xls";
                    file = new File(fileString);
                }
            }

            int index = 0;

            for (Products products : productsList)
            {
                HSSFRow row = sheet.createRow(index);
                row.createCell(0).setCellValue(products.getCode());
                row.createCell(1).setCellValue(products.getName());
                row.createCell(2).setCellValue(products.getCost());
                row.createCell(3).setCellValue(products.getPrice());
                row.createCell(4).setCellValue(products.getNetProfit());

                index++;
            }

            try
            {
                workbook.write(file);
                workbook.close();

                System.out.println("Exportado!");
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }

        private void imp() throws IOException
        {

            File file = null;
            Products sameProduct = null;

            JFileChooser chooser = new JFileChooser();
            chooser.setDialogTitle("Importar Arquivos XLS");
            chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
            chooser.setFileFilter(null);

            int showOpenDialog = chooser.showOpenDialog(chooser.getParent()); // Stores user choice

            if (showOpenDialog == JFileChooser.APPROVE_OPTION) // Checks the user's choice
            {
                file = chooser.getSelectedFile(); // Get the file way if approved
            }

            FileInputStream fis = new FileInputStream(file); // Points to an instance of the file

            HSSFWorkbook workbook = new HSSFWorkbook(fis); // Passing FIS to workbook representation

            HSSFSheet sheet = workbook.getSheetAt(0); // Pick the page to be used

            // TODO
            Products product = sameProduct;

            Iterator<Row> iterator = sheet.iterator();

            while (iterator.hasNext())
            {
                HSSFRow row = (HSSFRow) iterator.next();

                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext())
                {
                    // FIXME
                }
            }

            try
            {
                dao.change(product); // Correct?
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }

        private void addProduct()
        {
            Products p1 = new Products();
            p1.setCode(1);
            p1.setName("Teste 1");
            p1.setCost(50);
            p1.setPrice(100);
            p1.setNetProfit(50);
            productsList.add(p1);

            Products p2 = new Products();
            p2.setCode(2);
            p2.setName("Teste 2");
            p2.setCost(50);
            p2.setPrice(100);
            p2.setNetProfit(50);
            productsList.add(p2);

            Products p3 = new Products();
            p3.setCode(3);
            p3.setName("Teste 3");
            p3.setCost(50);
            p3.setPrice(100);
            p3.setNetProfit(50);
            productsList.add(p3);

            Products p4 = new Products();
            p4.setCode(4);
            p4.setName("Teste 4");
            p4.setCost(50);
            p4.setPrice(100);
            p4.setNetProfit(50);
            productsList.add(p4);

            Products p5 = new Products();
            p5.setCode(5);
            p5.setName("Teste 5");
            p5.setCost(50);
            p5.setPrice(100);
            p5.setNetProfit(50);
            productsList.add(p5);
        }

        private void events()
        {
            exp.addActionListener(new ActionListener()
            {
                @Override
                public void actionPerformed(ActionEvent e)
                {
                    exp();
                }
            });

            imp.addActionListener(new ActionListener()
            {
                @Override
                public void actionPerformed(ActionEvent e)
                {
                    try
                    {
                        imp();
                    }
                    catch (IOException e1)
                    {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                    }
                }
            });
        }

    }

    public static void main(String[] args)
    {
        MinimoVerificavel mv = new MinimoVerificavel();
        FrameMinimo fm = mv.new FrameMinimo();
        fm.setVisible(true);
    }
}

Answer 1

I think it is not worth loading the data from the spreadsheet and checking which ones have changed. You can simply replace the system data with the data from the spreadsheet. This approach will be much faster. It is interesting to put this data on a map, because then you can access much faster the products you want through the product code. To use the map you need to implement the methods hashCode and equals.

Code that carries the products:

try (
            InputStream inputStream = ReadExcelFormula.class.getResourceAsStream("teste.xls");
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        ) {
            Map<Integer, Products> productsMap = new HashMap<>();
            HSSFSheet sheet = workbook.getSheetAt(0);
            for(Row row : sheet) {
                Products product = new Products();
                product.setCode(Double.valueOf(row.getCell(0).getNumericCellValue()).intValue());
                product.setName(row.getCell(1).getStringCellValue());
                product.setCost(Double.valueOf(row.getCell(2).getNumericCellValue()).floatValue());
                product.setPrice(Double.valueOf(row.getCell(3).getNumericCellValue()).floatValue());
                product.setNetProfit(Double.valueOf(row.getCell(4).getNumericCellValue()).floatValue());

                productsMap.put(product.getCode(), product);
            }
        }

If you still wish to make this update, the following procedure can be performed:

1. Load data into a map.
2. Iterate the old products.
3. Search the products loaded from the spreadsheet through the product code.
4. Check that the data has been changed using the hashcode method. For this the hashcode menu must take into account all attributes of the class.
5. If the hashcode is not equal, update the data through a method that receives the updated product.

Hashcode method:

public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + code;
        result = prime * result + Float.floatToIntBits(cost);
        result = prime * result + ((name == null) ? 0 : name.hashCode());
        result = prime * result + Float.floatToIntBits(netProfit);
        result = prime * result + Float.floatToIntBits(price);
        return result;
    }

Method to update the product (this method must be in the Products class itself):

public void updateValues(Products updatedProduct) {
        this.name = updatedProduct.name;
        this.cost = updatedProduct.cost;
        this.price = updatedProduct.price;
        this.netProfit = updatedProduct.netProfit;
    }

Section that iterates the old products, searches the new ones and updates the values:

List<Products> oldProducts = getOldProducts();
            for (Products oldProduct : oldProducts) {
                Products updatedProduct = productsMap.get(oldProduct.getCode());
                if (oldProduct.hashCode() != updatedProduct.hashCode()) {
                    oldProduct.updateValues(updatedProduct);
                }
            }