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);
        }
    }
  }
}

Answer 2

i do not understand how to map with reader?

You don't map with reader.

You read into / as an in-memory data structure, and then write out the data structure as XML. Assuming that is what you are trying to do here.

(You could also do it incrementally, but lets not complicate things.)