Android App that writes to excel

+1 th3 ramr0d · November 20, 2014
I plan on writing a simple app, won't be simple for me, that takes user input and upon the click of a button exports it out to an excel file in a particular format. All I have been able to find so far is that you import the apache poi lib and put the following code in. The code here works perfectly fine when I run it to my Nexus 7 (2013). My 2 questions are "Am I on the right track?" and "Can someone explain to me whats going on in the code?" so I can better understand it to learn it or point me to a good tutorial on this. Haven't been able to find one yet. Thanks.

MainActivity.java

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import android.app.Activity;
import android.content.Context;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;
 
public class MainActivity extends Activity implements OnClickListener
{
Button writeExcelButton,readExcelButton;
static String TAG = "ExelLog";
    @Override
    public void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
 
        writeExcelButton = (Button) findViewById(R.id.writeExcel);
        writeExcelButton.setOnClickListener(this);
        readExcelButton = (Button) findViewById(R.id.readExcel);
        readExcelButton.setOnClickListener(this);
 
    }
 
    public void onClick(View v) 
    {
        switch (v.getId()) 
        {
        case R.id.writeExcel:
            saveExcelFile(this,"myExcel.xls");
            break;
        case R.id.readExcel:
            readExcelFile(this,"myExcel.xls");
            break;   
        }
    }
 
    private static boolean saveExcelFile(Context context, String fileName) { 
 
        // check if available and not read only 
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { 
            Log.e(TAG, "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("myOrder");
 
        // Generate column headings
        Row row = sheet1.createRow(0);
 
        c = row.createCell(0);
        c.setCellValue("Item Number");
        c.setCellStyle(cs);
 
        c = row.createCell(1);
        c.setCellValue("Quantity");
        c.setCellStyle(cs);
 
        c = row.createCell(2);
        c.setCellValue("Price");
        c.setCellStyle(cs);
 
        sheet1.setColumnWidth(0, (15 * 500));
        sheet1.setColumnWidth(1, (15 * 500));
        sheet1.setColumnWidth(2, (15 * 500));
 
        // Create a path where we will place our List of objects on external storage 
        File file = new File(context.getExternalFilesDir(null), fileName); 
        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; 
    } 
 
    private static void readExcelFile(Context context, String filename) { 
 
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) 
        { 
            Log.e(TAG, "Storage not available or read only"); 
            return; 
        } 
 
        try{
            // Creating Input Stream 
            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 rowIter = mySheet.rowIterator();
 
            while(rowIter.hasNext()){
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator cellIter = myRow.cellIterator();
                while(cellIter.hasNext()){
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    Log.d(TAG, "Cell Value: " +  myCell.toString());
                    Toast.makeText(context, "cell Value: " + myCell.toString(), Toast.LENGTH_SHORT).show();
                }
            }
        }catch (Exception e){e.printStackTrace(); }
 
        return;
    } 
 
    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; 
    } 
}

Post a Reply

Replies

Oldest  Newest  Rating
0 th3 ramr0d · November 21, 2014
OK I think I'm making decent progress. After hours of trying to figure out what to do and near a hundred Google searches I finally got that code implemented into my current project. But now I have a couple questions as to what to do next. I edited the code to take out the read function as that isn't needed. I added the ability to change the file name with a string from the input text which works great:

public void onClick(View v) 
    {

EditText inputTxt = (EditText) findViewById(R.id.wkOneSaveFile);
        String str = inputTxt.getText().toString();

        switch (v.getId()) 
        {
        case R.id.wk1Save:
            saveExcelFile(this,str + ".xls");   
        }


My next question is I want this user input to be used again to name the sheet created in the workbook

sheet1 = wb.createSheet("1 Week PT Plan");


Is there a way to use the variable I created "str" in more places than just the onClick or do I need to create another variable to get that info again when the workbook is created? I would like to do something like:

sheet1 = wb.createSheet(str);

But it says I have to create another local variable. And what are these little blocks of code called such as the following? Are they called methods or am I way off? Just want to learn the lingo so I'm not lost when I'm searching this stuff.
public void onClick(View v) 
    {

EditText inputTxt = (EditText) findViewById(R.id.wkOneSaveFile);
        String str = inputTxt.getText().toString();

        switch (v.getId()) 
        {
        case R.id.wk1Save:
            saveExcelFile(this,str + ".xls");   
        }


Thanks for all your help!
0 Chris Whitlock · November 20, 2014
Okay, then it seems you're on the right track. Let me know if you need more help.
-1 th3 ramr0d · November 20, 2014
Goal is to be able to build an Army Physical Fitness Plan by making selections on the app then it prints it out to an excel file for distribution. 
0 Chris Whitlock · November 20, 2014
The top is all the includes required to run what your doing, probably more than you actually need but thats fine. The next section (as in section I'm talking about with in the next set of " { } ".)  defines the button itself. The onClick section defines  to  either run the section "saveExcelFile" or "readExcelFile". Directly after that is the section "saveExcelFile" which checks to make sure room is available and the external storage is active e.i. read/write allowed on SD card. Though out that section it goes through the data input and reads what you want where and tries to save the file, if it fails it logs the error, if all goes well than your good.

"readExcelFile"  like the previous section it checks the SD card is readable if so it goes ahead and opens the selected file and takes out the data and puts it into the form - like "saveExcelFile" but backwards.

I may not have explained the sections too well but I tried to do a quick over. If you'd like me to better explain it, let me know. Pretty simple app. Sounds like your new to it, just read though it and it will pop out at you like if it as write than its making the file etc. there is read english with in. lol

Now, weather your doing it right? That depends what the main goal of the application is for. There may be better ways of going around it. It's best to make things as simple and effective as possible, especially for a application like this. So please explain your goal.


Best regards,
Chris W.
  • 1

Java / Android Development

107,307 followers
About

Very popular language used to create desktop applications, website applets, and Android apps.

Links
Moderators
Bucky Roberts Administrator