1

I am trying to read excel files using jXLS library and I want to make it dynamic using hashmaps but I am stuck there. Provided below are the relevant classes.

Please help to make use of hashmaps instead of arraylists.

JXLSParser

package com.metricstream.service.xlsparser;

       import java.io.InputStream;
       import java.util.ArrayList;
       import java.util.HashMap;
       import java.util.List;
       import java.util.Map;

       import net.sf.jxls.reader.ReaderBuilder;
       import net.sf.jxls.reader.XLSReadStatus;
       import net.sf.jxls.reader.XLSReader;

       import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
       import org.xml.sax.SAXException;

       public class JXLSParser {
       private static final String CHAR_S = "s";

       public <T> List<T> parseXLS(InputStream xmlStream, InputStream xlsStream,
        Class<T> tClass) throws AppException {
    List<T> tList = new ArrayList<T>();

    XLSReader mainReader = null;

    try {
        mainReader = ReaderBuilder.buildFromXML(xmlStream);
    } catch (IOException ioException) {
        throw new AppException(ioException);
    } catch (SAXException saxException) {
        throw new AppException(saxException);
    }

    Map<String, Object> beans = new HashMap<String, Object>();
    beans.put(tClass.getSimpleName().toLowerCase().concat(CHAR_S), tList);
    XLSReadStatus readStatus = null;

    try {
        readStatus = mainReader.read(xlsStream, beans);
    } catch (InvalidFormatException invalidFormatException) {
        throw new AppException(invalidFormatException);
    } catch (IOException ioException) {
        throw new AppException(ioException);
    }



    if (readStatus.isStatusOK()) {
        System.out.println("Read Status: ok");

    } else {
        throw new AppException("xls cannot be read properly");
    }

    return tList;
}
}

Main

     package com.metricstream.service.xlsparser;

     import java.io.BufferedInputStream;
     import java.io.FileInputStream;
     import java.io.FileNotFoundException;
     import java.io.InputStream;
     import java.util.HashMap;
      import java.util.List;
     import java.util.Map;

     import org.apache.poi.ss.formula.functions.T;

     import com.metricstream.exercise.employee.Employee;

    public class Exercise {
       public static void main(String args[]) throws AppException {
    String xmlConfig = "F:\\workspace/XLSReader/XLSReader/XLSReader/employee.xml";
    String dataXLS = "F:\\workspace/XLSReader/XLSReader/XLSReader/employee.xlsx";
    JXLSParser employeeParser = new JXLSParser();
    InputStream inputXML;
    try {
        inputXML = new BufferedInputStream(new FileInputStream(xmlConfig));
        InputStream inputXLS = new BufferedInputStream(new FileInputStream(
                dataXLS));
        List<Employee> employee = employeeParser.parseXLS(inputXML,
                inputXLS, Employee.class);
    //  Map<String,Object> map = new HashMap<String,Object>(employee.size());
    /*  for( Employee emp: employee)
        {
            map.put(emp.getKey(),emp.getValue());
            System.out.println(emp);
        }*/

        System.out.println(employee);
    } catch (FileNotFoundException e) {
        throw new AppException(e);
    }

}
 }

Excel file content

enter image description here

2 Answers 2

1
<?xml version="1.0" encoding="ISO-8859-1"?>
<workbook>
    <worksheet name="Sheet1">
        <loop startRow="1" endRow="1" items="simples" var="simple"
            varType="java.util.HashMap">

            <section startRow="1" endRow="1">
                <mapping row="2" col="0">simple.firstName</mapping>
                <mapping row="2" col="1">simple.lastName</mapping>
                <mapping row="2" col="2">simple.age</mapping>
                <mapping row="2" col="3">simple.des</mapping>
                <mapping row="2" col="4">simple.salary</mapping> 
            </section>
            <loopbreakcondition>
                <rowcheck offset="0">
                    <cellcheck offset="0" />
                </rowcheck>
            </loopbreakcondition>
        </loop>
    </worksheet>
</workbook>
Sign up to request clarification or add additional context in comments.

1 Comment

Please provide context and explanation for the answer, thanks.
1

Try set "varType" attribute to "java.util.HashMap" and set "type" attribute of "mapping" elements to "java.util.String" or other. For example:

<?xml version="1.0" encoding="utf-8"?>
<workbook>
    <worksheet name="Sheet1">
        <section startRow="0" endRow="0">
        </section>
        <loop startRow="1" endRow="1" items="rows" var="row" varType="java.util.HashMap">
            <section startRow="1" endRow="1">
                <mapping row="1" col="0" type="java.lang.String">row.id</mapping>
                <mapping row="1" col="1" type="java.lang.String">row.name</mapping>
                <mapping row="1" col="2" type="java.lang.String">row.code</mapping>
            </section>
            <loopbreakcondition>
                <rowcheck offset="0">
                    <cellcheck offset="0">Summary</cellcheck>
                </rowcheck>
            </loopbreakcondition>
        </loop>
    </worksheet>
</workbook>

Its work for me. Jxls Reader version is 2.4.0

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.