I want to convert excel to xml

Question

I want to convert excel to xml.Can any one tell me how to proceed with mapper? My code:

public class WorkGroupReader implements ItemReader<List<String>> {
    WorkGroupMapper linemapper;

    public void setLinemapper(WorkGroupMapper linemapper) {
        this.linemapper = linemapper;
    }

    @Override
    public List<String> read() throws Exception, UnexpectedInputException, ParseException, NonTransientResourceException,BiffException, IOException {
            String FilePath = "E:\\ide-workspaces\\OmniFeed\\input\\WorkGroup.xls";
            FileInputStream fs = new FileInputStream(FilePath);
            Workbook wb = Workbook.getWorkbook(fs);

            // TO get the access to the sheet
            Sheet sh = wb.getSheet("WorkGroup");

            // To get the number of rows present in sheet
            int totalNoOfRows = sh.getRows();

            // To get the number of columns present in sheet
            int totalNoOfCols = sh.getColumns();

            List<String> list=new ArrayList<String>();
            for (int row = 1; row < totalNoOfRows; row++) {

                for (int col = 1; col < totalNoOfCols; col++) {
                    //System.out.print(sh.getCell(col, row).getContents() + "\t");
                    list.add(sh.getCell(col, row).getContents());
                    //return linemapper.mapLine(sh.getCell(col, row).getContents(), 0);
                } 
            }

            return list;

i do not understand how to map with reader?

Answer 1

You can use Apache POI library to read .xls or .xlsx
For Maven projects: Add the following dependency to your project’s pom.xml file:

<!--For Excel 2003 format only (HSSF) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>2.6.12</version>
</dependency>

<!--For Excel 2007 format (XSSF) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>

Here is an example code using XSSF

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.util.IOUtils;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelToXml {
public static void main(String[] args) {
File excelFile = new File(args[0]);
if (excelFile.exists()) {
Workbook workbook = null;
FileInputStream inputStream = null;
try {
inputStream = new FileInputStream(excelFile);
workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue());
break;
}
System.out.print("\t");
}
System.out.println();
}
} catch (IOException e) {
// handle the exception
} finally {
IOUtils.closeQuietly(workbook);
IOUtils.closeQuietly(inputStream);
}
}
}
}