Create Xls / Excel dynamically and reuse the code globally.

 

Hello everyone this blog is about create the excel file dynamically in android studio. Dynamically in the scenes, not fixed column count dynamic column count.

If your lazy to read this blog, then check out this youtube video 

Still your lazy to see video also then check this Github

Now let's jump in to the blog. First of all try to understand what I have done here. Understanding this thing is so simple. I just create an interface here to create and export the xls file. Inside the interface I write one function called generateXlsFile(); .I use that function everywhere to create the dynamic xls file. Actually what the function will do is I added some parameters to the function it help the function to create a dynamic xls file.

/**
 * Method use to create a Xls Files
 * @param activity Current Activity Context
 * @param titles Title need to show in the sheet
 * @param indexName Model class variable name based on the title
 * @param jsonArray Whole list converted to json array
 * @param otherValueMap other details need to show outside the list
 * @param sheetName Sheet name
 * @param fileName File name to Save
 * @param otherRowItemCount Other value row count
 * @return File
 */
default File generateXlsFile(Activity activity, String[] titles, String[] indexName,
                             JsonArray jsonArray, HashMap<String, String> otherValueMap,
                             String sheetName, String fileName, int otherRowItemCount){

    try(Workbook wb = new HSSFWorkbook()){
        Sheet sheet = wb.createSheet(sheetName);
        Cell cell ;
        var rowIndex = 0;

        if (!otherValueMap.isEmpty() && !otherValueMap.keySet().isEmpty()){
            var keys = otherValueMap.keySet();
            var i =0;
            var limit =0;
            Row row = sheet.createRow(rowIndex);
            for (String one: keys){
                if (otherValueMap.containsKey(one)){
                    if (limit == otherRowItemCount){
                        ++rowIndex;
                        row =sheet.createRow(rowIndex);
                        i=0;
                        limit =0;
                    }else {
                        if (i!=0){
                            ++i;
                            cell = row.createCell(i);
                            cell.setCellValue("");
                        }
                    }
                    cell = row.createCell(i);
                    cell.setCellValue(one);
                    ++i;

                    cell = row.createCell(i);
                    cell.setCellValue(otherValueMap.get(one));
                    ++i;
                    ++limit;
                }
            }
            ++rowIndex;
            sheet.createRow(rowIndex);
            ++rowIndex;
        }
        Row row =sheet.createRow(rowIndex);
        ++rowIndex;
        var  a=0;
        for (String title: titles){
            cell = row.createCell(a);
            cell.setCellValue(title);
            ++a;
        }

        for (int j=0; j<123;j++){
            sheet.setColumnWidth(j,(30*200));
        }


        for (int i=0; i < jsonArray.size(); i++){
            JsonObject jsonObject = jsonArray.get(i).getAsJsonObject();

            if (jsonObject!= null){
                var b =0;
                Row row1 =  sheet.createRow(i + rowIndex);

                for (String  index : indexName){
                    cell = row1.createCell(b);
                    try{
                        if (index!=null && !TextUtils.isEmpty(index)){
                            if (jsonObject.has(index) && jsonObject.get(index).getAsString() != null
                            && !TextUtils.isEmpty(jsonObject.get(index).getAsString())){
                                cell.setCellValue(jsonObject.get(index).getAsString());
                            }else {
                                cell.setCellValue(" - ");
                            }
                        }
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                    ++b;
                }
            }
        }

        File file = null;

        try{
            file = getFile(activity,fileName + System.currentTimeMillis()+".xls");
        }catch (Exception e){
            e.printStackTrace();
        }

        FileOutputStream fileOutputStream =new FileOutputStream(file.getPath());
        wb.write(fileOutputStream);
        fileOutputStream.close();
        return file;

    }catch (Exception e){
        e.printStackTrace();
    }
    return null;
}

By using the above function we can create the dynamic xls files.

/**
 * Method use to get the storage location.
 * @param activity Current Activity
 * @param fileName FileName
 * @return File
 * @throws IOException exception
 */
default File getFile(Activity activity, String fileName) throws IOException {
    var now = new Date();
    DateFormat.format("yyyy-MM-dd_hh:mm:ss",now);
    var rootFolder = new File(String.valueOf(activity.getExternalFilesDir(Environment.DIRECTORY_DCIM)));
    final var filePath = new File(rootFolder,fileName.replaceAll("^a-zA-Z0-9._&",""));

    if (!filePath.exists()){
        filePath.mkdir();
    }
    if (!filePath.exists()){
        filePath.createNewFile();
    }else {
        filePath.delete();
        filePath.createNewFile();
    }
    return filePath;
}

The above function is use to get he file to save the file.

Let me show you how to send the parameters to this interface.

List<XlsMode> xlsModeList = new ArrayList<>();

xlsModeList.add(new XlsMode("1","James", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("2","Kindlsy", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("3","Wesley", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("4","Aberaham", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("5","Mari", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("6","Durai", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("7","Livingstron", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("8","Bravo", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("9","Wex", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("10","Cloe", "X","2","12","Male","a"));
xlsModeList.add(new XlsMode("11","Vertex", "X","2","12","Male","a"));

Gson gson = new Gson();
var jsonArray = gson.toJsonTree(xlsModeList).getAsJsonArray();

String[] titles = new String[]{"ID", "Name", "Class", "Bench", "Age", "Gender","Grade"};
String[] indexName = new String[]{"studentId", "studentName", "studentClass", "studentBench", "studentAge", "studentGender","studentGrade"};

HashMap<String, String> otherValue  = new HashMap<>();
otherValue.put("Record", "Student Record");
otherValue.put("Place", "Campus City");
otherValue.put("City", "Toranto");


var file = generateXlsFile(this, titles,indexName,jsonArray,otherValue,"Student Record","students",1);

This is how we need to pass the parameter and the function will return you the xls file.

THANK YOU