Write multiple records in two column in excel using java

Question

I have two column namely Setup and Request in excel, need to write multiple rows in excel using java

public void WriteExcelValues() throws IOException {
String Ustr= setup.getText();
String urequest = request.getText();
    File file = new File(System.getProperty("user.dir") 
                            + "/src/main/uat/testdata/FreeTestData.xlsx");
    FileInputStream fis = new FileInputStream(file);
    Workbook workbook = new XSSFWorkbook(fis);
    Sheet sheet = workbook.getSheetAt(0);
    int lastRow = sheet.getLastRowNum();
    int n = 1;

    for (int i = 1; i <= n; i++) {   
        Row row = sheet.getRow(i); 
        Cell cell = row.createCell(8);
        Cell cell1 = row.createCell(9);

        cell.setCellValue(Ustr);
        cell1.setCellValue(urequest);
        //cell.setCellValue("AcknowledgementId"); 
    }

    FileOutputStream fos = new FileOutputStream(file);   
    workbook.write(fos);
    fos.close();
}

for example

Above code is not complete and doesn't satisfy the criteria also.

Personally I've never used it, but there is a framework for working with xlxs: mkyong.com/java/… — Sterconium
– Sterconium, Commented Oct 11, 2019 at 10:34
Setup and Request are column name in excel, i have shared the image , how data has to be written in excel — Vipin Singh
– Vipin Singh, Commented Oct 11, 2019 at 10:39
Why do you create cells 8 and 9 when you obviously want to insert values into the cells 0 and 1? Additionally, you don't close the workbook instance, which may lead to broken files. — deHaar
– deHaar, Commented Oct 11, 2019 at 10:41

Coma · Accepted Answer · 2023-02-23 13:13:50Z

1


Workbook workbook = new XSSFWorkbook();

Sheet sheet = workbook.createSheet();

Row headerRow = sheet.createRow(0);

headerRow.createCell(0).setCellValue("Setupid");
headerRow.createCell(1).setCellValue("Request");

int cellCount = 10;

for (int i = 1; i <= cellCount; i++) {

    Row row = sheet.createRow(i);

    row.createCell(0).setCellValue("cell " + i);
    row.createCell(1).setCellValue("cell " + i);
}

File file = new File(System.getProperty("user.dir") 
                            + "/src/main/uat/testdata/FreeTestData.xlsx");

if(file.exists()) {
    file.delete();
}

file.createNewFile();

FileOutputStream fos = new FileOutputStream(file);

workbook.write(fos);

fos.close();

workbook.close();

edited Feb 23, 2023 at 13:13

answered Oct 11, 2019 at 10:56

Coma

3151 silver badge7 bronze badges

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

4 Comments

Vipin Singh Over a year ago

I have write in the existing excel sheet only , what changes have to be done in the above code

Coma Over a year ago

@VipinSingh try Workbook workbook = new XSSFWorkbook(file); Sheet sheet = workbook.getSheetAt(index);

Vipin Singh Over a year ago

String Ustr= setup.getText(); String urequest = request.getText(); cell.setCellValue(Ustr); cell1.setCellValue(urequest); I have to pass the string value to setCellValue which will be unique or same

Vipin Singh Over a year ago

Can u please help in modifying the code using the above requirements-@Coma

SJN · Accepted Answer · 2019-10-11 10:48:12Z

0

I think your for loop is not correct

 for(int i=1; i<=n; i++){

It should be

for(int i=1; i<=lastRow; i++){

answered Oct 11, 2019 at 10:48

SJN

3832 gold badges8 silver badges18 bronze badges

Collectives™ on Stack Overflow

Write multiple records in two column in excel using java

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related