Today we want to focus our efforts on getting an Android app to open an Excel spreadsheet to pull data from it and populate objects that use the data. The packages that we will be using are:
- jxl.Cell - represents the cell within a sheet
- jxl.Sheet - represents the sheet within a workbook
- jxl.Workbook - represents the workbook
- jxl.read.biff.BiffException - exception thrown when a biff file (like an Excel spreadsheet) is read
- java.text.NumberFormat - abstract base class for all number formats
- java.util.Locale - represents a specific geographical, political or cultural region
- java.util.ParseException - used to indicate an error was reached while parsing
We start out by using File object to get the path of the files we want to open and the name of the file we want to use. The name of the file we would select is usually displayed in a ListView that has the files located in the path and they are normally stored as an ArrayList. We have the file path and the filename enclosed inside of a try-catch-finally block that will cover any problems such as an IOException that handles any input/output failures or interruptions. An example of this is:
try {
File fPath = new File(getApplicationContext().getExternalFilesDir(null) + "/TestApp");
String strFilePathXLS = fPath.getPath() + "/" + itemValue + ".xls";
} catch (IOException e) {
Toast.makeText(getApplicationContext(), "IO Exception", Toast.LENGTH_SHORT).show();
} finally {
}
The next item we want to do next is to setup a new Workbook object that opens the Excel spreadsheet and a new Sheet object that interfaces with the Workbook object. This is also enclosed inside a try-catch block that will cover exceptions such as FileNotFoundException (to see if a file is not found, which will not be an issue since the file exists), ParseException (parsing issues) and BiffException (you will get this if you open a CSV or XLSX files). Once you're done, your code should look similar to this.
try {
File fPath = new File(getApplicationContext().getExternalFilesDir(null) + "/TestApp");
String strFilePathXLS = fPath.getPath() + "/" + itemValue + ".xls";
try {
Workbook wbExcelFile = Workbook.getWorkbook(new File(strFilePathXLS));
Sheet shWorkSheet = wbExcelFile.getSheet(0);
} catch (FileNotFoundException e) {
Toast.makeText(getApplicationContext(), "File Not Found", Toast.LENGTH_SHORT).show();
} catch (BiffException e) {
Toast.makeText(getApplicationContext(), "Biff Exception", Toast.LENGTH_SHORT).show();
} catch (ParseException e) {
Toast.makeText(getApplicationContext(), e.toString(), Toast.LENGTH_SHORT).show(); }
} catch (IOException e) {
Toast.makeText(getApplicationContext(), "IO Exception", Toast.LENGTH_SHORT).show();
} finally {
}
Next we set up Cell objects that get the data that resides in the cells of the spreadsheet. One way to get the data is to create a Cell object that call the getCell function of the Sheet class. It takes two arguments (one for the row and one for the column). An example of this would be:
Cell celSample = shWorkSheet.getCell(1, 1); // first argument is row
// second argument is column
Then we convert the contents of the cell to a String or a number depending on what you would like to do with them. If you desire to convert the contents to a String, then the Cell class has a getContents function that would allow you to do just that. An example of this would be:
String strSample = celSample.getContents();
If you were to convert the contents to an int or a double, all you have to do at this point is to call the parse function that does the task.
int nSample = Integer.parseInt(celSample.getContents());
double dSample = Double.parseDouble(celSample.getContents());
Let's say you have a cell that has been formatted in a different manner, such as a currency amount or a percentage. This is where the Locale and NumberFormat classes come into play. Since we're working with American currency, it would be appropriate to set the Locale object to American values and set NumberFormat objects that speak to currency and percentage.
Locale us = new Locale("en", "US");
NumberFormat nbfDollarSample = NumberFormat.getCurrencyInstance(us);
NumberFormat nbfPercentSample = NumberFormat.getPercentInstance(us);
The next step would be to use the NumberFormat objects to parse the contents into a Number object. From there, you can convert the Number object into either an int or a double. However, should you choose to convert a percentage into a double, it is recommended that you cast the Number object to a Double and multiply it by 100.0.
Number numSample = nbfDollarSample.parse(celSample.getContents());
double dSample = numSample.doubleValue();
Number numPercentSample = nbfPercentSample.parse(celSample.getContents());
double dPercentSample = (Double)numPercentSample * 100.0;
Once you have completed all of these steps, your code snippet should look similar to this:
Intent newActivity;
Sample samS = null;
try {
File fPath = new File(getApplicationContext().getExternalFilesDir(null) + "/TestApp");
String strFilePathXLS = fPath.getPath() + "/" + itemValue + ".xls";
try {
Workbook wbExcelFile = Workbook.getWorkbook(new File(strFilePathXLS));
Sheet shWorkSheet = wbExcelFile.getSheet(0);
Cell celSample1 = shWorkSheet.getCell(1, 1);
Cell celSample2 = shWorkSheet.getCell(1, 2);
Cell celSample3 = shWorkSheet.getCell(1, 3);
Cell celSample4 = shWorkSheet.getCell(1, 4);
Cell celSample5 = shWorkSheet.getCell(1, 5);
String strSample1 = celSample1.getContents();
int nSample2 = Integer.parseInt(celSample2.getContents());
double dSample3 = Double.parseDouble(celSample3.getContents());
Locale us = new Locale("en", "US");
NumberFormat nbfDollarSample = NumberFormat.getCurrencyInstance(us);
NumberFormat nbfPercentSample = NumberFormat.getPercentInstance(us);
Number numSample4 = nbfDollarSample.parse(celSample4.getContents());
double dSample4 = numSample4.doubleValue();
Number numPercentSample = nbfPercentSample.parse(celSample5.getContents());
double dPercentSample = (Double)numPercentSample * 100.0;
samS = new Sample(strSample1, nSample2, dSample3, dSample4, dPercentSample);
} catch (FileNotFoundException e) {
Toast.makeText(getApplicationContext(), "File Not Found", Toast.LENGTH_SHORT).show();
} catch (BiffException e) {
Toast.makeText(getApplicationContext(), "Biff Exception", Toast.LENGTH_SHORT).show();
} catch (ParseException e) {
Toast.makeText(getApplicationContext(), e.toString(), Toast.LENGTH_SHORT).show(); }
} catch (IOException e) {
Toast.makeText(getApplicationContext(), "IO Exception", Toast.LENGTH_SHORT).show();
} finally {
newActivity = new Intent(OpenActivity.this, SampleActivity.class);
newActivity.putExtra("Sample", samS);
startActivity(newActivity);
finish();
}
Please stay tuned to this channel for more aspects on how to put an Android app together.
No comments:
Post a Comment