android - 使用excel作为数据库的Android条码扫描器
问题描述
我正在制作一个条形码扫描仪应用程序（使用条形码扫描仪库和 Google Vision），它使用 excel 文件作为数据库并写入 excel 文件。Excel 数据库将如下所示：“产品名称”“条形码”。当有人使用条形码扫描仪扫描条形码并与数据库单元“条形码”匹配时，将弹出一个自定义对话框。在自定义对话框中有一个 Textview 将显示产品名称（匹配行），在这里我们可以输入一个数字然后保存它。此保存按钮会将所有数据（“产品名称”、“条形码”、“数量”）插入到 Excel 文件中。
所以我的问题是： 1.如何读取excel作为我们选择的数据库？2. 如何弹出自定义对话框“onScan”？3、如何将这些数据插入到我们创建的excel中？
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() {
}
}
解决方案
对于问题1和3，在Android中读写excel文件可以使用Apache POI来实现。我看到大多数人都在努力寻找正确的 Jar，但无需单独下载 jar，只需使用下面提到的依赖项即可。
dependencies {
implementation 'org.apache.poi:poi:3.12'
implementation 'org.apache.poi:poi-ooxml:3.12'
implementation 'com.fasterxml:aalto-xml:1.0.0'
}
用于从 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) {
}
}
用于写入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) {
}
}
参考https://www.mysamplecode.com/2012/08/android-read-excel-file-from-website.html
问题 2. 在 android 中创建弹出窗口相当容易。可以使用 AlertDialog 或 MaterialDialog。
推荐阅读
- mongodb - 主题未看到 Mongodb Kafka 消息
- vb.net - 无法在 VB.NET 中访问已释放的对象
- postgresql - 如何将数据从 Elastic Search 传输到 Postgres？
- java - 如何将 WebRTC 视频流视为虚拟网络摄像头
- typescript - 如何理解“类型中缺少索引签名...（2344）”错误？
- ethereum - 在 Go 中解析字符串中的以太坊私钥
- java - 在获取用户输入以填充 JAVA 中的字符串数组时出现问题 - 无法填充数组的第一个索引位置
- sql - 简化值+空检查
- reactjs - ReferenceInput 未归档选择组件
- html - 如何将href添加到整个图形