Changing the list from an Apache excel import api

Question

Changing the list from an Apache excel import api

Navigation

#1 by (1 votes)

1

The system can generate an excel file where all the products in a serialized inventory are found. What I would like is that when this file is imported back all changes made to it are applied inside 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);
    }
}

java apache-poi

asked by anonymous 06.12.2017 / 12:53

1 answer

remove files from inside the container after the build. Can you do it? Wp-admin redirect

score 1 · Accepted Answer

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

Code that loads 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 want to do this update, you can do the following:

Load the data into a map.

Iterate old products.

Search the products loaded from the spreadsheet through the product code.

Verify that the data has been changed using the hashCode method. For this the hashCode method must take into account all the attributes of the class.

If hashCode is not equal, refresh the data through a method that receives the updated product.

Method hashCode:

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;
    }

Excerpt that iterates old products, searches for new ones and updates values:

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