How do I delete a row in an excel (csv) sheet using Java?

Question

I am coding a program in which I can add the details of people to an excel sheet. I must also be able to edit these details and therefore what I want to do is delete the old details and then just write the new ones to the file. All details of each person are stored in one row. I want to be able to target each row by using the email of the person that is being altered. How can I do this? I have tried other peoples solutions that I have found but they are not working for my program. Belo is a basic version of my program to help you to understand:

The following code is where I write the details of the people to the file. This all works fine.

JButton addClientButton = new JButton("Add");
    addClientButton.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {
            PrintWriter pw = null;
            Client newClient = new Client(firstNameTextField.getText(), lastNameTextField.getText(), emailTextField.getText(), phoneTextField.getText(), weightTextField.getText(), heightTextField.getText(), ageSpinner.getValue(), activityLevelComboBox.getSelectedItem());
            try {

                pw = new PrintWriter(new FileOutputStream(new File("Clients.csv"), true)); 
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
            StringBuilder sb = new StringBuilder();
            sb.append(newClient.getFirst());
            sb.append(",");
            sb.append(newClient.getLast());
            sb.append(",");
            sb.append(newClient.getEmail());
            sb.append(",");
            sb.append(newClient.getPhone());
            sb.append(",");
            sb.append(newClient.getWeight());
            sb.append(",");
            sb.append(newClient.getHeight());
            sb.append(",");
            sb.append(newClient.getClientAge());
            sb.append(",");
            sb.append(newClient.getClientActivity());
            sb.append("\n");

            pw.write(sb.toString());
            pw.close();
        }
    });

Below is the start of the code in which I must be able to use the new edited details stored in the "editClient" object to replace the old details.

JButton btnSave1 = new JButton("Save");
        btnSave1.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                Client editClient = new Client(firstNameField1.getText(), lastNameField1.getText(), emailField1.getText(), phoneField1.getText(), weightField1.getText(), heightField1.getText(), ageSpinner1.getValue(), activityComboBox1.getSelectedItem());     
            }
        });

Why did those solutions not work? Maybe you can share some code? Is it Excel or just CSV? — home, Jul 30 '17 at 12:09
`solution not working` is not a proper term for programming. The proper term is `I have an exception/unexpected behaviour when I'm executing the following code` — J-Alex, Jul 30 '17 at 12:11

Oguz · Accepted Answer · 2017-07-30T17:45:24.167

You can use Apache POI lib, you can download here.

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

import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

public class Operations {

private static final String FILE_NAME = "MyFirstExcel.xls";

public static void main(String[] args) {
    //ArrayList<String> eMails = new ArrayList<String>();
    //int cellIndex = 0;
    try {

        FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
        Workbook workbook = new HSSFWorkbook(excelFile);
        Sheet datatypeSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = datatypeSheet.iterator();

        //for (String eMail : eMails) {
            while (iterator.hasNext()) {

                Row currentRow = iterator.next();

                Iterator<Cell> cellIterator = currentRow.iterator();

                //while (cellIterator.hasNext()) {

                    if (currentRow.getCell(2).getCellTypeEnum() == CellType.STRING) {
                        // control your email, I presume email stays in the 3rd cell
                        if (currentRow.getCell(2).getStringCellValue().compareTo(editClient.getEmail()) != 0) {
                            continue;
                        }
                    }
                    //Cell currentCell = cellIterator.next();

                    // your code here..
                   currentRow.getCell(0).setCellValue(editClient.getFirst());
                   currentRow.getCell(1).setCellValue(editClient.getLast());
                   //.. 
                   //currentCell.setCellValue(editClient.getFirst());


                //}

            }
        //}
        // update
        // after refreshin your datatypeSheet objet you should write back to file
        FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
        workbook.write(outputStream);
        workbook.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }   

}

Here another useful topic.

Thank you very much, I will try this solution now and let you know how I get on. — Trey Collier, Jul 30 '17 at 16:41
In your code, you write "outputStream" to the file. Why do you do this? And where do I write the new edited details of the client? — Trey Collier, Jul 30 '17 at 16:51
You are editing datatypeSheet which is a Sheet object and this sheet is a sheet of the workbook . " workbook.write(outputStream) " export the workbook items into the actual excel file. — Oguz, Jul 30 '17 at 16:56
I edited, too. You should add cell by cell with cell indexes. I believe that is what you need. — Oguz, Jul 30 '17 at 17:48

score 1 · Answer 2 · answered Jul 30 '17 at 18:08

1

Yes. You can use either Apache POI or JXL to edit the excel or csv fiels.

answered Jul 30 '17 at 18:08

Anil Kumar Athuluri

643
3
11

How do I delete a row in an excel (csv) sheet using Java?

2 Answers2