I'm making a barcode scanner application(Using Barcode Scanner library and Google Vision) which is using an excel file as database and write into excel file. Excel database will look like this : "Product name" "Barcode". When someone using the barcode scanner and it scanned a Barcode and matching with database cell "Barcode" a custom dialog will pop up. In the custom dialog there is a Textview which will show the product name (Matched row) and here we can put a number and then save it. This save button will insert into an excel file all data("Product Name","Barcode","Quantity").
So my questions are : 1. How to read excel as database which we select? 2. How to pop up the custom dialog "onScan"? 3. How to insert these datas to the excel we created?
public class SecondActivity extends AppCompatActivity {
public static final int PICKFILE_RESULT_CODE = 1;
public static String filename;
public static File file;
public static RelativeLayout relativeLayout;
public static Button buttonsecond;
public static Uri fileUri;
public static String filePath;
public static final int REQUEST_CODE = 10;
String[] permissionsall = {
Manifest.permission.WRITE_EXTERNAL_STORAGE,
Manifest.permission.CAMERA};
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_second);
relativeLayout = findViewById(R.id.activity_second);
relativeLayout.setBackgroundColor(Color.BLACK);
if(checkPermission()){
}else{
}
buttonsecond = findViewById(R.id.ScanBarcode);
buttonsecond.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
startActivity(new Intent(SecondActivity.this,ScanActivity.class));
}
});
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.second_activity_menu, menu);
return true;
}
@RequiresApi(api = Build.VERSION_CODES.KITKAT)
@Override
public boolean onOptionsItemSelected(MenuItem item) {
int id = item.getItemId();
switch (id){
case R.id.item1:
createnewFile();
return true;
case R.id.item2:
try {
openDatabase();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
default:
return super.onOptionsItemSelected(item);
}
}
public boolean checkPermission(){
int result;
List<String> listPermissionisNeed = new ArrayList<>();
for(String permission : permissionsall ){
result = ContextCompat.checkSelfPermission(this,permission);
if(result != PackageManager.PERMISSION_GRANTED){
listPermissionisNeed.add(permission);
}
}
if (!listPermissionisNeed.isEmpty()){
ActivityCompat.requestPermissions(this,listPermissionisNeed.toArray(new String[listPermissionisNeed.size()]),REQUEST_CODE);
return false;
}
return true;
}
@RequiresApi(api = Build.VERSION_CODES.KITKAT)
@Override
public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions,@NonNull int[] grantResults){
if(requestCode == REQUEST_CODE){
HashMap<String, Integer> permissionResults = new HashMap<>();
int deniedCOunt = 0;
for(int i=0; i<grantResults.length;i++){
if(grantResults[i] == PackageManager.PERMISSION_DENIED){
permissionResults.put(permissions[i],grantResults[i]);
deniedCOunt++;
}
}
if(deniedCOunt == 0)
{
}
else{
for(Map.Entry<String, Integer > entry : permissionResults.entrySet())
{
String permName = entry.getKey();
int permResult = entry.getValue();
if(ActivityCompat.shouldShowRequestPermissionRationale(this,permName)){
shownewDialog();
}else{
showsecondDialog();
}
}
}
}
}
@RequiresApi(api = Build.VERSION_CODES.KITKAT)
public void shownewDialog(){
ViewGroup viewGroup = findViewById(android.R.id.content);
View dialogView = LayoutInflater.from(this).inflate(R.layout.my_dialog, viewGroup, false);
AlertDialog.Builder builder = new AlertDialog.Builder(this);
builder.setView(dialogView);
final AlertDialog dialog = builder.create();
Objects.requireNonNull(dialog.getWindow()).setBackgroundDrawable(new ColorDrawable(Color.BLACK));
dialog.show();
Button dialogokbutton = (Button) dialog.findViewById(R.id.buttonOk);
dialogokbutton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
dialog.dismiss();
checkPermission();
}
});
Button dialogcancelbutton = (Button) dialog.findViewById(R.id.buttonCancel);
dialogcancelbutton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
dialog.dismiss();
showsecondDialog();
}
});
}
@RequiresApi(api = Build.VERSION_CODES.KITKAT)
public void showsecondDialog(){
ViewGroup viewGroup = findViewById(android.R.id.content);
View dialogView = LayoutInflater.from(this).inflate(R.layout.my_second_dialog, viewGroup, false);
AlertDialog.Builder builder = new AlertDialog.Builder(this);
builder.setView(dialogView);
final AlertDialog dialog = builder.create();
Objects.requireNonNull(dialog.getWindow()).setBackgroundDrawable(new ColorDrawable(Color.BLACK));
dialog.show();
Button dialogokbutton = (Button) dialog.findViewById(R.id.secondbuttonOk);
dialogokbutton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
dialog.dismiss();
Intent intent = new Intent(Settings.ACTION_APPLICATION_DETAILS_SETTINGS,Uri.fromParts("package",getPackageName(),null));
intent.addFlags(Intent.FLAG_ACTIVITY_NEW_TASK);
startActivity(intent);
}
});
Button dialogcancelbutton = (Button) dialog.findViewById(R.id.secondbuttonCancel);
dialogcancelbutton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
dialog.dismiss();
finish();
}
});
}
@RequiresApi(api = Build.VERSION_CODES.KITKAT)
public void createnewFile(){
ViewGroup viewGroup = findViewById(android.R.id.content);
View dialogView = LayoutInflater.from(this).inflate(R.layout.file_creator, viewGroup, false);
AlertDialog.Builder builder = new AlertDialog.Builder(this);
builder.setView(dialogView);
final AlertDialog dialog = builder.create();
Objects.requireNonNull(dialog.getWindow()).setBackgroundDrawable(new ColorDrawable(Color.BLACK));
dialog.show();
final EditText userInput = (EditText) dialog.findViewById(R.id.filecreatortext);
Button filecreatebutton = (Button) dialog.findViewById(R.id.filercreatorcreatebutton);
filecreatebutton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Workbook wb=new HSSFWorkbook();
Cell cell=null;
CellStyle cellStyle=wb.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
Sheet sheet=null;
sheet = wb.createSheet("Products");
Row row =sheet.createRow(0);
cell=row.createCell(0);
cell.setCellValue("Product Name");
cell.setCellStyle(cellStyle);
cell=row.createCell(1);
cell.setCellValue("Barcodenumber");
cell.setCellStyle(cellStyle);
cell=row.createCell(2);
cell.setCellValue("Quantity");
cell.setCellStyle(cellStyle);
sheet.setColumnWidth(0,(10*200));
sheet.setColumnWidth(1,(10*200));
sheet.setColumnWidth(2,(10*200));
File file = new File(getExternalFilesDir(null),userInput.getText()+".xls");
FileOutputStream outputStream =null;
try {
outputStream=new FileOutputStream(file);
wb.write(outputStream);
Toast.makeText(SecondActivity.this,userInput.getText()+" File Created!",Toast.LENGTH_LONG).show();
} catch (java.io.IOException e) {
e.printStackTrace();
Toast.makeText(SecondActivity.this,"Couldn't create the file!",Toast.LENGTH_LONG).show();
try {
outputStream.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
dialog.dismiss();}
});
}
public void openDatabase() throws FileNotFoundException {
Intent chooseFile = new Intent(Intent.ACTION_GET_CONTENT);
chooseFile.setType("*/*");
chooseFile = Intent.createChooser(chooseFile, "Choose a file");
startActivityForResult(chooseFile, PICKFILE_RESULT_CODE);
file = new File(fileUri.getPath());
try {
AssetManager assetManager=getAssets();
FileInputStream inputStream = (FileInputStream) assetManager.open(file+"");
filename = String.valueOf(inputStream);
Toast.makeText(this, filename + "Opened!", Toast.LENGTH_LONG).show();
jxl.Workbook wb = jxl.Workbook.getWorkbook(inputStream);
jxl.Sheet sheet = wb.getSheet(0);
int row = sheet.getRows();
int col = sheet.getColumns();
for (int i=0;i<row;row++){
for (int c=0;c<col;col++){
jxl.Cell z = sheet.getCell(c,i);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
public void onActivityResult(int requestCode, int resultCode, Intent data) {
super.onActivityResult(requestCode, resultCode, data);
switch (requestCode) {
case PICKFILE_RESULT_CODE:
if (resultCode == -1) {
fileUri = data.getData();
filePath = fileUri.getPath();
}
break;
}
}
}
public class ScanActivity extends AppCompatActivity implements BarcodeReader.BarcodeReaderListener{
private static HSSFWorkbook xlWBook;
private static HSSFSheet xlSheet;
private static HSSFRow xlRow;
private static HSSFCell xlCell;
private static int row;
private static int cell;
private static int column;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_barcode_scanner);
}
@RequiresApi(api = Build.VERSION_CODES.KITKAT)
@Override
public void onScanned(Barcode barcode) {
}
@Override
public void onScannedMultiple(List<Barcode> barcodes) {
}
@Override
public void onBitmapScanned(SparseArray<Barcode> sparseArray) {
}
@Override
public void onScanError(String errorMessage) {
}
@Override
public void onCameraPermissionDenied() {
}
}
For question 1 and 3 reading and writing excel files in Android can be achieved using Apache POI. I have seen most people struggle with finding right Jar but no need to download a jar separately simply use below mentioned dependencies.
dependencies {
implementation 'org.apache.poi:poi:3.12'
implementation 'org.apache.poi:poi-ooxml:3.12'
implementation 'com.fasterxml:aalto-xml:1.0.0'
}
For reading from excel
private void readXml(InputStream fis) {
//fis is FileInputStream to your excel
try {
// Create a workbook using the Input Stream
HSSFWorkbook myWorkBook = new HSSFWorkbook(fis);
// 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();
while (rowIter.hasNext()) {
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator<Cell> cellIter = myRow.cellIterator();
while (cellIter.hasNext()) {
HSSFCell myCell = (HSSFCell) cellIter.next();
String cellValue = "";
// Check for cell Type
if (myCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
cellValue = myCell.getStringCellValue();
} else {
cellValue = String.valueOf(myCell.getNumericCellValue());
}
// Just some log information
Log.v(LOG_TAG, cellValue);
}
}
} catch (Exception e) {
}
}
For Writing into excel
private void writeToXml() {
try {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Users"); //Creating a sheet
for(int i=0; i<userProfilesWithFilter.size(); i++){
Row row = sheet.createRow(i);
row.createCell(CELL_INDEX_0).setCellValue(VALUE_YOU_WANT_TO_KEEP_ON_1ST_COLUMN);
row.createCell(CELL_INDEX_1).setCellValue(VALUE_YOU_WANT_TO_KEEP_ON_2ND_COLUMN);
}
String fileName = "FileName.xlsx"; //Name of the file
String extStorageDirectory = Environment.getExternalStorageDirectory()
.toString();
File folder = new File(extStorageDirectory, "FolderName");// Name of the folder you want to keep your file in the local storage.
folder.mkdir(); //creating the folder
File file = new File(folder, fileName);
try {
file.createNewFile(); // creating the file inside the folder
} catch (IOException e1) {
e1.printStackTrace();
}
try {
FileOutputStream fileOut = new FileOutputStream(file); //Opening the file
workbook.write(fileOut); //Writing all your row column inside the file
fileOut.close(); //closing the file and done
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} catch (Exception e) {
}
}
Refer https://www.mysamplecode.com/2012/08/android-read-excel-file-from-website.html
Question 2. Creating a pop up is fairly easy in android. AlertDialog or MaterialDialog can be used.