Working with Excel Sheets using java programming

Question

I got a small problem while working with a project.

I already have an excel file with some sheets in it.

Whenever I need to create a new excel file sheet with a particular name, I need to check if there's already a sheet with that name,

If it is so,

1.Then I need to delete it(thus deleting any old info),note the position of the sheet and create a new one with the same name at the same position

If not

1.Then i need to create a new sheet

I am stuck with it.Could anyone give some insights/some steps to accomplishing the task? Could I do the same with Apache POI or conveniently any other API?

Thanks in advance.I am supposed to use java as a medium while doing this.

i think usage of JExcel API would be right for this kind of requirement — Pavan Kumar K, May 30 '13 at 05:56

score 3 · Answer 1 · answered May 30 '13 at 06:02

If you are using POI, you can get no. of sheets in xls file by workbook.getNumberOfSheets().

You can iterate over them using regular for loop and check sheet names, if name matches with sheet name, you can delete it using workbook.removeSheetAt(index). And now you can create new sheet with same name and at given index.

Hope this helps.

score 0 · Answer 2 · answered May 30 '13 at 06:02

0

xlsx4j is something you may want to look at

click here for more info

answered May 30 '13 at 06:02

MaheshVarma

2,081
7
35
58

score 0 · Answer 3 · answered May 30 '13 at 06:04

The folloiwng code from JExcelAPI should work....

InputStream inp = new FileInputStream("workbook.xls");
//InputStream inp = new FileInputStream("workbook.xlsx");

Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt("sheet_name");
if(sheet != null)
{
 wb.removeSheet(index);
 //further functionality
}
else
{
  //code if sheet does not exists
}

score 0 · Answer 4 · answered May 30 '13 at 06:05

0

apache-poi could menage it.

Use: HSSFWorkbook.createSheet(sheetName) to create the new sheet. If it throws java.lang.IllegalArgumentException then it already exists, so you can find and delete it:

HSSFWorkbook.getSheetIndex(sheetName) to get index and HSSFWorkbook.removeSheetAt(index). Finally you can move your new sheet at desired position by: HSSFWorkbook.setSheetOrder(sheetName, position)

answered May 30 '13 at 06:05

mauretto

3,183
3
27
28

@user151065 please remeber to accept this answer if it solves your question. – mauretto May 30 '13 at 06:56

Working with Excel Sheets using java programming

4 Answers4