How to read excel file, sheets through sheet using jxl

Question

I am working on a utility to convert an excel file from one format to another. I was asked to use the jxl library to meet the requirements that follow as follows: read the file sheet by sheet and follow these steps

get the name of the sheet, make it the map key, and then get the column headers and make their values.

this will lead to the following

Map<String, List<String>> result = result<key=sheetName, value=list of column headers> 

Do this for all sheets in this file.

I did it as follows

public Map> function (String filePath) throws an IOException, BiffException {

  Map<String, List<String>> map = new HashMap<String, List<String>>(); Workbook workBook=Workbook.getWorkbook(new File (filePath)); String [] sheetNames = workBook.getSheetNames(); Sheet sheet=null; List<String > fields = new ArrayList<String>(); for (int sheetNumber =0; sheetNumber < sheetNames.length; sheetNumber++){ sheet=workBook.getSheet(sheetNames[sheetNumber]); for (int columns=0;columns < sheet.getColumns();columns++){ fields.add(sheet.getCell(columns, 0).getContents()); } map.put(sheetNames[sheetNumber],fields); } return map; } 

I did this with the hope of getting the desired result, but what it does is that it stores the column headings of all sheets for each key as a value. those. if in a file named

There are two sheets:

• sheet1
• sheet2

and follow the column headings

Sheet1 -> id, name
sheet2 → category, price

then the card will look like

 result<sheet1,<id, name, caegory, price>> result<sheet2,<id, name, caegory, price>> 

Can't understand what I'm doing wrong? Please help, as my project has many internal calculations, and I do not want to spend a lot of time on this.

Any help would be greatly appreciated

Answer 1

I solved the problem by posting it here, but forgot to give a solution here. The problem in the code above is the location of the list declaration.
It must be declared inside for so that it is updated (cleared) after each iteration of the loop and only stores data one sheet at a time. The following is the correct code.

  Map<String, List<String>> map = new HashMap<String, List<String>>(); Workbook workBook=Workbook.getWorkbook(new File (filePath)); String [] sheetNames = workBook.getSheetNames(); Sheet sheet=null; for (int sheetNumber =0; sheetNumber < sheetNames.length; sheetNumber++){ List<String > fields = new ArrayList<String>(); sheet=workBook.getSheet(sheetNames[sheetNumber]); for (int columns=0;columns < sheet.getColumns();columns++){ fields.add(sheet.getCell(columns, 0).getContents()); } map.put(sheetNames[sheetNumber],fields); } return map; } 

Hope this helps someone

Answer 2

 public String ExcelValueGetFn(String FilePath, int column, int row) throws BiffException, IOException { Workbook workbook = null; final String EXCEL_FILE_LOCATION = FilePath; workbook = Workbook.getWorkbook(new File(EXCEL_FILE_LOCATION)); Sheet sheet = workbook.getSheet(0); Cell cell1 = sheet.getCell(column, row); System.out.print(cell1.getContents() + ":"); String textToSendFromExcel = cell1.getContents(); return textToSendFromExcel; }