Take text from excel cell

Question

Take text from excel cell

Asked 5 years, 6 months ago

Viewed 254 times

1

I have an application that reads Excel data, but I can only read the value of the cell and I can’t read what the cell is literally showing. For example: Here I select where the spreadsheet is and which row and which column I want to take. In output 1 shows the formula and in output 2 shows the cell value.

But I wanted the output 2 to show what is being shown in the spreadsheet, for example: "seg" and not "01-apr-2019".

I’m using the Apache POI Framework. Follow code I used to make an example:

package newpackage;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class NewJFrame2 extends javax.swing.JFrame {

public NewJFrame2() {
    initComponents();
}

@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">                          
private void initComponents() {

    jPanel1 = new javax.swing.JPanel();
    jButton1 = new javax.swing.JButton();
    jLabel1 = new javax.swing.JLabel();
    jLabel2 = new javax.swing.JLabel();
    textoLinha = new javax.swing.JTextField();
    textoColuna = new javax.swing.JTextField();
    saida1 = new javax.swing.JTextField();
    jLabel3 = new javax.swing.JLabel();
    textoCaminho = new javax.swing.JTextField();
    saida2 = new javax.swing.JTextField();
    jLabel4 = new javax.swing.JLabel();
    jLabel5 = new javax.swing.JLabel();

    setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

    jButton1.setText("jButton1");
    jButton1.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(java.awt.event.ActionEvent evt) {
            jButton1ActionPerformed(evt);
        }
    });

    jLabel1.setText("Linha");

    jLabel2.setText("Coluna");

    saida1.setEditable(false);

    jLabel3.setText("Caminho");

    saida2.setEditable(false);

    jLabel4.setText("Saida 1");

    jLabel5.setText("Saida 2");

    javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
    jPanel1.setLayout(jPanel1Layout);
    jPanel1Layout.setHorizontalGroup(
        jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addGroup(jPanel1Layout.createSequentialGroup()
            .addContainerGap()
            .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(jPanel1Layout.createSequentialGroup()
                    .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                        .addGroup(javax.swing.GroupLayout.Alignment.LEADING, jPanel1Layout.createSequentialGroup()
                            .addComponent(jLabel1)
                            .addGap(18, 18, 18)
                            .addComponent(textoLinha))
                        .addGroup(javax.swing.GroupLayout.Alignment.LEADING, jPanel1Layout.createSequentialGroup()
                            .addComponent(jLabel2)
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                            .addComponent(textoColuna, javax.swing.GroupLayout.PREFERRED_SIZE, 45, javax.swing.GroupLayout.PREFERRED_SIZE)))
                    .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 229, Short.MAX_VALUE)
                    .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 121, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGroup(jPanel1Layout.createSequentialGroup()
                    .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                        .addComponent(jLabel4)
                        .addComponent(jLabel3)
                        .addComponent(jLabel5))
                    .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                    .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                        .addComponent(textoCaminho)
                        .addGroup(jPanel1Layout.createSequentialGroup()
                            .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                                .addComponent(saida2)
                                .addComponent(saida1, javax.swing.GroupLayout.PREFERRED_SIZE, 121, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addGap(0, 0, Short.MAX_VALUE)))))
            .addContainerGap())
    );
    jPanel1Layout.setVerticalGroup(
        jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel1Layout.createSequentialGroup()
            .addContainerGap()
            .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                .addComponent(jLabel1)
                .addComponent(textoLinha, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
            .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                .addComponent(jLabel2)
                .addComponent(textoColuna, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
            .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                .addComponent(jLabel3)
                .addComponent(textoCaminho, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
            .addGap(18, 18, 18)
            .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(jPanel1Layout.createSequentialGroup()
                    .addComponent(jLabel4)
                    .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                        .addGroup(jPanel1Layout.createSequentialGroup()
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jButton1)
                            .addContainerGap())
                        .addGroup(jPanel1Layout.createSequentialGroup()
                            .addGap(18, 18, 18)
                            .addComponent(jLabel5)
                            .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))))
                .addGroup(jPanel1Layout.createSequentialGroup()
                    .addComponent(saida1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addGap(18, 18, 18)
                    .addComponent(saida2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addGap(0, 25, Short.MAX_VALUE))))
    );

    javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
    getContentPane().setLayout(layout);
    layout.setHorizontalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addGroup(layout.createSequentialGroup()
            .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
            .addGap(0, 53, Short.MAX_VALUE))
    );
    layout.setVerticalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addGroup(layout.createSequentialGroup()
            .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
            .addGap(0, 0, Short.MAX_VALUE))
    );

    pack();
}// </editor-fold>                        

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    String caminhoPlanilha = textoCaminho.getText();
    String linha = textoLinha.getText();
    String coluna = textoColuna.getText();
    try (InputStream inp = new FileInputStream(caminhoPlanilha)) {
        Workbook wb = WorkbookFactory.create(inp);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet1 = wb.getSheetAt(0);
        Row row = sheet1.getRow(Integer.parseInt(linha));
        Cell cell = row.getCell(Integer.parseInt(coluna));
        saida1.setText(cell.toString());
        saida2.setText(evaluator.evaluateInCell(cell).toString());
    } catch (IOException ex) {
        Logger.getLogger(NewJFrame2.class.getName()).log(Level.SEVERE, null, ex);
    }
}                                        

public static void main(String args[]) {
    try {
        for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
            if ("Nimbus".equals(info.getName())) {
                javax.swing.UIManager.setLookAndFeel(info.getClassName());
                break;
            }
        }
    } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | javax.swing.UnsupportedLookAndFeelException ex) {
        java.util.logging.Logger.getLogger(NewJFrame2.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    }
    java.awt.EventQueue.invokeLater(new Runnable() {
        public void run() {
            new NewJFrame2().setVisible(true);
        }
    });
}

// Variables declaration - do not modify                     
private javax.swing.JButton jButton1;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JLabel jLabel5;
private javax.swing.JPanel jPanel1;
private javax.swing.JTextField saida1;
private javax.swing.JTextField saida2;
private javax.swing.JTextField textoCaminho;
private javax.swing.JTextField textoColuna;
private javax.swing.JTextField textoLinha;
// End of variables declaration                   
}

I made the screen very simple in an IDE, because what I need to know is just the read part of the Excel spreadsheet.

I got a class that reads exactly what is being presented: Xssfexcelextractor. But I can’t place it in a cell, it reads all the contents of the spreadsheet, someone can help me?

– Pedro Afonso

2019/05/30 at 13:11

1 answer

Browser other questions tagged java excel apache-poi

You are not signed in. Login or sign up in order to post.

by Pedro Afonso • **119** points · Answer 1 · 2019-05-30T14:04:59+00:00

I don’t know if it’s the best way to solve the problem, but it was the way I was able to do it.

First I copied my spreadsheet to another Workbook:

Workbook wb1 = wb;

Then I went through all the values of the newly created spreadsheet and cleaned what not the selected row and column:

Sheet s = wb1.getSheetAt(0);
        for (Row r : s) {
            for (Cell c : r) {
                if(c.getRowIndex() != Integer.parseInt(linha) || c.getColumnIndex() != Integer.parseInt(coluna))
                    c.setCellValue("");
            }
        }

Then I created the XSSFExcelExtractor to the new Workbook that I just created:

XSSFExcelExtractor extractor = new XSSFExcelExtractor((XSSFWorkbook) wb1);

And finally I took the value of XSSFExcelExtractor replacing the \n, the \t and the name of the spreadsheet s.getSheetName() by emptiness.

Follow the code of how was the new action of the full button:

    String caminhoPlanilha = textoCaminho.getText();
    String linha = textoLinha.getText();
    String coluna = textoColuna.getText();
    try (InputStream inp = new FileInputStream(caminhoPlanilha)) {
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet1 = wb.getSheetAt(0);
        Row row = sheet1.getRow(Integer.parseInt(linha));
        Cell cell = row.getCell(Integer.parseInt(coluna));
        Workbook wb1 = wb;
        Sheet s = wb1.getSheetAt(0);
        for (Row r : s) {
            for (Cell c : r) {
                if(c.getRowIndex() != Integer.parseInt(linha) || c.getColumnIndex() != Integer.parseInt(coluna))
                    c.setCellValue("");
            }
        }
        saida1.setText(cell.toString());
        XSSFExcelExtractor extractor = new XSSFExcelExtractor((XSSFWorkbook) wb1);
        saida2.setText(extractor.getText().replace("\n", "").replace("\t", "").replace(s.getSheetName(), ""));
    } catch (IOException ex) {
        Logger.getLogger(NewJFrame2.class.getName()).log(Level.SEVERE, null, ex);
    }

The result was: