Create excel sheet and store the data by activity

Question

I want to create a excel sheet and want to store the data in that excel sheet, for example I want to store the image and text message. Actually I am developing a android app for measurement, I completed the measurement part and now I want to do the reporting. I did search many time in Google but nothing find any information. Can any one knows that how to create a excel sheet from the android code and store the data.

Answer 1

i used this lib for excel import and export , support upto ms2007

http://poi.apache.org/download.html , easily integrate it and for use

creating a file 


// check if available and not read only 
    if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { 
        Toast.makeText(context, "No External Storage", Toast.LENGTH_SHORT).show();
        Log.w("FileUtils", "Storage not available or read only"); 
        return false; 
    } 

    boolean success = false; 

    //New Workbook
    Workbook wb = new HSSFWorkbook();

    Cell c = null;

    //Cell style for header row
    CellStyle cs = wb.createCellStyle();
    cs.setFillForegroundColor(HSSFColor.LIME.index);
    cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    //New Sheet
    Sheet sheet1 = null;
    sheet1 = wb.createSheet("job");


    Row row = sheet1.createRow(0);

    c = row.createCell(0);
    c.setCellValue("OperatorId : ");
    c.setCellStyle(cs);

    c = row.createCell(1);
    c.setCellValue("OperatorName : " );
    c.setCellStyle(cs);

    c = row.createCell(2);
    c.setCellValue("Blade : " );
    c.setCellStyle(cs);
    c = row.createCell(3);
    c.setCellValue("ShiftType : " );
    c.setCellStyle(cs);

    c = row.createCell(4);
    c.setCellValue("Bom : ");
    c.setCellStyle(cs);

    c = row.createCell(5);
    c.setCellValue("Job Created at : ");
    c.setCellStyle(cs);

    c = row.createCell(6);
    c.setCellValue("Blade Number: " );
    c.setCellStyle(cs);


    sheet1.setColumnWidth(0, (15 * 500));
    sheet1.setColumnWidth(1, (15 * 500));
    sheet1.setColumnWidth(2, (15 * 500));
    sheet1.setColumnWidth(3, (15 * 500));
    sheet1.setColumnWidth(4, (15 * 500));
    sheet1.setColumnWidth(5, (15 * 500));
    sheet1.setColumnWidth(6, (15 * 500));


    Row row2 = sheet1.createRow(1);

    c = row2.createCell(0);
    c.setCellValue("JobId");
    c.setCellStyle(cs);

    c = row2.createCell(1);
    c.setCellValue("BladeName");
    c.setCellStyle(cs);

    c = row2.createCell(2);
    c.setCellValue("Start Time");
    c.setCellStyle(cs);

    c = row2.createCell(3);
    c.setCellValue("End Time");
    c.setCellStyle(cs);

    c = row2.createCell(4);
    c.setCellValue("Time Taken");
    c.setCellStyle(cs);

    c = row2.createCell(5);
    c.setCellValue("Ply Number");
    c.setCellStyle(cs);




    SimpleDateFormat sdf = new SimpleDateFormat("HHmm_ddMMyyyy");
    String currentDateandTime = sdf.format(new Date());
    File file = new File(context.getExternalFilesDir(null), historyID+"History_"+currentDateandTime+".xls"); 
    FileOutputStream os = null; 

    try { 
        os = new FileOutputStream(file);
        wb.write(os);
        Log.w("FileUtils", "Writing file" + file); 
        success = true; 
    } catch (IOException e) { 
        Log.w("FileUtils", "Error writing " + file, e); 
    } catch (Exception e) { 
        Log.w("FileUtils", "Failed to save file", e); 
    } finally { 
        try { 
            if (null != os) 
                os.close(); 
        } catch (Exception ex) { 
        } 
    } 

    return success; 
} 


 public static boolean isExternalStorageReadOnly() { 
    String extStorageState = Environment.getExternalStorageState(); 
    if (Environment.MEDIA_MOUNTED_READ_ONLY.equals(extStorageState)) { 
        return true; 
    } 
    return false; 
} 

public static boolean isExternalStorageAvailable() { 
    String extStorageState = Environment.getExternalStorageState(); 
    if (Environment.MEDIA_MOUNTED.equals(extStorageState)) { 
        return true; 
    } 
    return false; 
} 

use permissions

 android.permission.WRITE_EXTERNAL_STORAGE
 android.permission.READ_EXTERNAL_STORAGE


// Reading a excel file
        File file = new File(context.getExternalFilesDir(null), filename); 
        FileInputStream myInput = new FileInputStream(file);

        // Create a POIFSFileSystem object 
        POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

        // Create a workbook using the File System 
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

        // Get the first sheet from workbook 
        HSSFSheet mySheet = myWorkBook.getSheetAt(0);

        /** We now need something to iterate through the cells.**/
        Iterator<Row> rowIter = mySheet.rowIterator();
        Cell bladeIDcell = mySheet.getRow(0).getCell(0);
        Cell bladeNameCell = mySheet.getRow(0).getCell(0);


        HSSFSheet mySheet2 = myWorkBook.getSheetAt(1);
            Iterator<Row> rowIter2 = mySheet2.rowIterator();

            while(rowIter2.hasNext()){
                HSSFRow myRow = (HSSFRow) rowIter2.next();
                Iterator<Cell> cellIter = myRow.cellIterator();
                ArrayList<String> Valuesfromcell = new ArrayList<String>();
                while(cellIter.hasNext()){
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    Log.w("FileUtils", "Cell Value: " +  myCell.toString());
                    Valuesfromcell.add(myCell.toString());
                    //Toast.makeText(context, "cell Value: " + myCell.toString(), Toast.LENGTH_SHORT).show();
                }

Answer 2

Try below code for storing text :

        String filename = "abc.csv";
        String data = arrayListDGId.toString();  //arraylist contain your text
        data = data.replace("[", "").replace("]", "");              
        FileOutputStream fos;  
           try {  
               File dir = new File("/sdcard/FOLDERNAME/");
               boolean b = dir.mkdir();
                File myFile = new File(dir, filename);  
                myFile.createNewFile();  
                FileOutputStream fOut = new FileOutputStream(myFile);  
                OutputStreamWriter myOutWriter = new OutputStreamWriter(fOut);  
                for (String s : arrayListDGId) {
                    myOutWriter.append(s + "\n"); //stores data to newline in same column
                }

                myOutWriter.close();  
                fOut.close();  
           } catch (FileNotFoundException e) {e.printStackTrace();}  
           catch (IOException e) {e.printStackTrace();}