Appending data to existing excel file

Aspose.Cells Product Family
1

Hi ! I use Java Aspose Cells and i have a form created in Java with firstname, lastname, etc. I want to create a excel file and append data to every time when i complete form. I have searched a lot but i didn’t find something which can help me. My application creates a new excel every time and remove the last content. Can you help me please with some examples ?

2

@Luca_Macdonald,
You can append data to an existing Excel file by opening it and then finding the last data row in the file. Once you get the last row, you may put new data in the next row whenever form is filled. Use following code to get the last row:

Workbook wb = new Workbook(filePath);
Worksheet ws = wb.getWorksheets().get(0);
int maxDataRow = ws.getCells().getMaxDataRow();

If it does not fulfill your requirement, share your runnable program code and template file for our testing. We will test the scenario here and provide assistance.

3

Sorry but it does not work. I’ve tried to append after the last row but it doesn’t succeed. I have a basic java class. It throws an exception if i want to read that file

“Exception in thread “main” com.aspose.cells.CellsException: The same worksheet name already exists”

public class ExcelFileService {
private final Logger logger = Logger.getLogger(getClass().getName());
private Workbook excel;
private static final String FILE_PATH = “C:\Users\User\Desktop\Book3.xlsx”;

public ExcelFileService() {
    try {
        this.excel = new Workbook(FILE_PATH);
    } catch (Exception e) {
        logger.info(e.getMessage());
    }
}

public void writeToFile() {
    WorksheetCollection worksheets = excel.getWorksheets();
    int serialSheetIndex = worksheets.add();

    Worksheet firstWorksheet = worksheets.get(serialSheetIndex);
    firstWorksheet.setName("Test");

    Cells cells = firstWorksheet.getCells();

    int maxDataRow = cells.getMaxDataRow();
    cells.get("A1").setValue("Firstname");
    cells.get("A2").setValue("John");
    cells.get("A3").setValue("Jane");
    cells.get("B1").setValue("Lastname");
    cells.get("B2").setValue("Mikey");
    cells.get("B3").setValue("Dumont");

    try {
        excel.save("C:\\Users\\User\\Desktop\\Book3.xlsx");
        logger.info("Done !");
    } catch (Exception e) {
        logger.info(e.getMessage());
    }
}

}

4

Can you help me, please ?

5

@Luca_Macdonald,

Could you please zip your Excel file and attach it, we will check it soon.

6

@Luca_Macdonald,

Please forget it as no need to send your file.
After analyzing your code further, we found your code is not right, so you are getting the exception. For example, see your line of code:
firstWorksheet.setName(“Test”);

Every time you load your file you add a new sheet with the same name(“Test”). You cannot add a sheet with the same name in the workbook, sheet name should be unique in the workbook.

Moreover, the following line has no usage for your scenario as you are always adding new sheet to insert data into same set of cells and do not use an existing sheet.

Please fix your code and it will work fine.

Let us know if you still find any issue

7

I don’t know what to do after i get maxDataRow. I must create new Row and append data ? Give some examples, i don’t understand

8

@Luca_Macdonald,

See the sample code segment for your reference:
e.g
Sample code:

.....
    WorksheetCollection worksheets = workbook.getWorksheets();
    		Worksheet firstWorksheet = worksheets.get(0);
    	    
    	    Cells cells = firstWorksheet.getCells();
    	    
    	    int maxDataRow = firstWorksheet.getCells().getMaxDataRow();
            int maxDataCol = firstWorksheet.getCells().getMaxDataColumn();
             
            //if worksheet is empty (i.e., first time it will be empty)
            if(maxDataCol == -1 & maxDataRow ==-1)
            {
                cells.get("A1").setValue("Firstname");
         	    cells.get("A2").setValue("John");
         	    cells.get("A3").setValue("Jane");
         	    cells.get("B1").setValue("Lastname");
         	    cells.get("B2").setValue("Mikey");
         	    cells.get("B3").setValue("Dumont");
            firstWorksheet.setName("Test");
            }  
            //when the worksheet is not empty (it has existing data in it)
            else
            {
                //get cell after last data cell, e.g A4, A5, B4, B5, and so on... ("0" refer to first column and "1" refer to second column).
            	cells.get(maxDataRow +1, 0).setValue("John1");
         	    cells.get(maxDataRow +2, 0).setValue("Jane1");
         	    cells.get(maxDataRow +1, 1).setValue("Mikey1");
         	    cells.get(maxDataRow +2, 1).setValue("Dumont1");
            	//...
            }

    .........

Hope, this helps a bit.

9

Thanks a lot ! I reallly appreciate your help.

10

@Luca_Macdonald,

You are welcome. And, good to know that the suggested code segment helps to figure out your issue now. In the event of any further query or comments, please feel free to contact us and we will be happy to assist you soon.