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
– home, Commented Jul 30, 2017 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
– J-Alex, Commented Jul 30, 2017 at 12:11

Oguz · Accepted Answer · 2017-07-30 17:45:24Z

2

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.

edited Jul 30, 2017 at 17:45

answered Jul 30, 2017 at 14:23

Oguz

1,9361 gold badge17 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Trey Collier Over a year ago

Thank you very much, I will try this solution now and let you know how I get on.

Trey Collier Over a year ago

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?

Oguz Over a year ago

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 Over a year ago

I edited, too. You should add cell by cell with cell indexes. I believe that is what you need.

Anil Kumar Athuluri · Accepted Answer · 2017-07-30 18:08:55Z

1

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

answered Jul 30, 2017 at 18:08

Anil Kumar Athuluri

6533 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related