how to create excel sheet in workbook using java?

Question

I have to find the example string in excel sheet as I have to change it using java. I did it easily. But the problem comes when I have to do it same for specific times and each time I have to copy the data into new sheet. So if I have to do it for 5 times then there should be 5 worksheet in my excel workbok.

Here is my code.

int a=1;            
String match="Keval Dalsaniya";    
String[] name={"Ankit","Keval","Varun","Dhaval","Nirav"}; 
int i;
try {
FileInputStream file = new FileInputStream(new File("D:\\Ankit\\Data\\data.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);

        for(i=0;a<=name.length;i++,a++)
        {
            Cell cell = null;
            cell = sheet.getRow(0).getCell(4);
            workbook.createSheet(name[i]);          
            if(cell.getStringCellValue().equals(match))
                cell.setCellValue(name[i]);
            else
                JOptionPane.showMessageDialog(null, "No match found.");

            System.out.println(name[i]);

        } 
            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("D:\\Ankit\\Data\\update.xlsx"));
            workbook.write(outFile);
            outFile.close();


    } 
    catch (Exception e) 
    {
        e.printStackTrace();
    }

so mainly u r replacing full name with first name in excel sheet ? — Rohit Sachan
– Rohit Sachan, Commented Feb 5, 2014 at 5:48
I have a standard format of excel. And then I have to replace it with different strings on a specific position and need to store the result in different worksheets. — Ankit Patel
– Ankit Patel, Commented Feb 5, 2014 at 5:52
I guess coying will not the solution as i have to edit it each n every time. — Ankit Patel
– Ankit Patel, Commented Feb 5, 2014 at 5:54

Tanmay Patil · Accepted Answer · 2014-02-05 09:08:20Z

1

Use

XSSFSheet newSheet = workbook.createSheet();

and put edited String in the newly created sheet.

Hope this helps.

Edit:

You are creating a new sheet but not using it.
Try the following.

XSSFSheet sheet = workbook.getSheetAt(0);

for (i = 0; a <= name.length; i++,a++)
{
    Cell cell = null;
    cell = sheet.getRow(0).getCell(4);
    XSSFSheet newSheet = workbook.createSheet(name[i]);
    // createRow and createCell should come to rescue here.
    Cell cellInNewSheet = newSheet.getRow(0).getCell(4);          
    if (cell.getStringCellValue().equals(match)) {
        cellInNewSheet.setCellValue(name[i]);
    }
    else {
        JOptionPane.showMessageDialog(null, "No match found.");
    }
    System.out.println(name[i]);
}

edited Feb 5, 2014 at 9:08

answered Feb 5, 2014 at 5:58

Tanmay Patil

7,0752 gold badges27 silver badges45 bronze badges

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

4 Comments

Ankit Patel Over a year ago

the problem is that how to put the edited data in to worksheet.

Tanmay Patil Over a year ago

Edited.. If you have further problem, consider explaining your problem in other words.

Ankit Patel Over a year ago

This line will generate nullpointer exception as we have not created any cells yet in the sheet.Cell cellInNewSheet = newSheet.getRow(0).getCell(4

Tanmay Patil Over a year ago

I suppose you should be able to take it from here.. Check the comment. Good luck.

Collectives™ on Stack Overflow

how to create excel sheet in workbook using java?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related