Headertab

Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Thursday, 9 June 2016

ExcelToJson sample in android

Hello friends,

Firstly you add excel library


MainActvity.java


public class RowReadActvity extends AppCompatActivity {

    static int rowSize=25;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_row_read_actvity);
        read();
    }

public void read() {
    InputStream stream = getResources().openRawResource(R.raw.test);
    List<HashMap<String,HashMap<String,List>>> stdList = new ArrayList();

 
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(stream);
        int numberOfSheets = workbook.getNumberOfSheets();

        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator rowIterator = sheet.iterator();

            while (rowIterator.hasNext()) {
                Row row = (Row) rowIterator.next();
                Iterator cellIterator = row.cellIterator();

                    for(int j=0; j<rowSize;j++)
                    {

                    if (row.getRowNum()==j)
                    {
                        HashMap<String,HashMap<String,List>> map=new HashMap<>();
                        HashMap<String,List> map2=new HashMap<>();
                        List list = new ArrayList();
                        while(cellIterator.hasNext()){
                            Cell cell = (Cell) cellIterator.next();
                            list.add(cell.toString());
                     
                            map2.put("shirt",list);

                        }

                        map.put("cloth",map2);
                        stdList.add(map);
                    }
                }
            }

        }
        Gson gson = new Gson();
        String json = gson.toJson(stdList);
        Log.e("Row", "jason data: " +  json.toString());
        cretaeJson(json.toString());


    } catch (Exception ex) {
        ex.printStackTrace();
    }

}


public void cretaeJson(String object) throws IOException {
    String s=" hello sam";
    File resolveMeSDCard = new File(getExternalFilesDir(Environment.DIRECTORY_DOWNLOADS),"hello_file.txt");
    resolveMeSDCard.createNewFile();
    FileOutputStream fos = new FileOutputStream(resolveMeSDCard);
    fos.write(s.getBytes());
    fos.close();


    //write the JSON data to file
   
BufferedWriter bufferedWriter = null;
    try {

        File file = new File(getExternalFilesDir(Environment.DIRECTORY_DOWNLOADS),"myFile.json");
        if(!file.exists()){
            file.createNewFile();
        }

        FileWriter fileWriter = new FileWriter(file);
        bufferedWriter = new BufferedWriter(fileWriter);
        bufferedWriter.write(object);
        Log.e("Row"," ct=reated");


    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            if (bufferedWriter != null){
                bufferedWriter.close();
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }


}

// OTHER WAY YOU USE


public void onReadClick() {
    //printlnToUser("reading XLSX file from resources");
   
InputStream stream = context.getResources().openRawResource(R.raw.test);
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(stream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowsCount = sheet.getPhysicalNumberOfRows();
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        for (int r = 0; r < rowsCount; r++) {
            Row row = sheet.getRow(r);
            int cellsCount = row.getPhysicalNumberOfCells();
            for (int c = 0; c < cellsCount; c++) {
                String value = getCellAsString(row, c, formulaEvaluator);

                String cellInfo = "r:" + r + "; c:" + c + "; v:" + value;

                //Log.e("Test", " Value " + cellInfo);
           
}
        }
    } catch (Exception e) {
        /* proper exception handling to be here */
       
e.printStackTrace();
    }
}



protected String getCellAsString(Row row, int c, FormulaEvaluator formulaEvaluator) {
    String value = "";
    try {
        Cell cell = row.getCell(c);
        CellValue cellValue = formulaEvaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                value = "" + cellValue.getBooleanValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                double numericValue = cellValue.getNumberValue();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    double date = cellValue.getNumberValue();
                    SimpleDateFormat formatter =
                            new SimpleDateFormat("dd/MM/yy");
                    value = formatter.format(HSSFDateUtil.getJavaDate(date));
                } else {
                    value = "" + numericValue;
                }
                break;
            case Cell.CELL_TYPE_STRING:
                value = "" + cellValue.getStringValue();
                break;
            default:
        }
    } catch (NullPointerException e) {
        /* proper error handling should be here */
       
e.printStackTrace();
    }
    return value;
}



build.gradle


apply plugin: 'com.android.application'

android {
    compileSdkVersion 23
    buildToolsVersion "23.0.3"

   
defaultConfig {
        applicationId "com.samset.xlsxreadwrite"
       
minSdkVersion 15
        targetSdkVersion 23
        versionCode 1
        versionName "1.0"
       
multiDexEnabled true
   
}
    buildTypes {
        release {
            minifyEnabled false
           
proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'
       
}
    }
}

dependencies {
    compile fileTree(dir: 'libs', include: ['*.jar'])
    testCompile 'junit:junit:4.12'
   
compile 'com.android.support:appcompat-v7:23.3.0'
   
compile 'com.android.support:multidex:1.0.1'
   
compile 'com.google.code.gson:gson:2.2.+'
   
compile files('libs/poi-3.12-android-a.jar')
    compile files('libs/poi-ooxml-schemas-3.12-20150511-a.jar')
}