本文介绍如何在Android应用中利用JXL库进行Excel文件的读写操作，包括导出特定数据到Excel及从Excel文件中查询信息的方法。通过示例代码展示了如何创建、写入和读取Excel文件，以及处理文件路径和权限检查的过程。
需要导入jxl包
implementation ‘net.sourceforge.jexcelapi:jxl:2.6.12’
直接上代码
/**
* @author Guangnian
* @data 2019-11-04 10:06:10
* */
public class ExcelUtil {
//内存地址
public static String root = Environment.getExternalStorageDirectory()
.getPath();
public static void writeExcel(Context context, List<PatrolEntity> exportOrder,
String fileName) throws Exception {
if (!Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)&&getAvailableStorage()>1000000) {
Toast.makeText(context, "SD卡不可用", Toast.LENGTH_LONG).show();
return;
}
//这些是你要导出的字段
String[] title = { "巡查时间", "巡查人员"};
File file;
File dir = new File(context.getExternalFilesDir(null).getPath());
file = new File(dir, fileName + ".xls");
if (!dir.exists()) {
dir.mkdirs();
}
// 创建Excel工作表
WritableWorkbook wwb;
OutputStream os = new FileOutputStream(file);
wwb = Workbook.createWorkbook(os);
// 添加第一个工作表并设置第一个Sheet的名字
WritableSheet sheet = wwb.createSheet("a", 0);
Label label;
for (int i = 0; i < title.length; i++) {
// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
// 在Label对象的子对象中指明单元格的位置和内容
label = new Label(i, 0, title[i], getHeader());
// 将定义好的单元格添加到工作表中
sheet.addCell(label);
}
//exportOrder就是你要导出的对应字段值
for (int i = 0; i < exportOrder.size(); i++) {
PatrolEntity order = exportOrder.get(i);
Label a= new Label(0, i + 1, order.a);
Label b= new Label(1, i + 1, b);
sheet.addCell(a);
sheet.addCell(b);
Toast.makeText(context, "写入成功", Toast.LENGTH_LONG).show();
}
// 写入数据
wwb.write();
// 关闭文件
wwb.close();
//QueryUser(new File(dir, "采矿权信息表" + ".xls"));
}
public static WritableCellFormat getHeader() {
WritableFont font = new WritableFont(WritableFont.TIMES, 10,
WritableFont.BOLD);// 定义字体
try {
font.setColour(Colour.BLUE);// 蓝色字体
} catch (WriteException e1) {
e1.printStackTrace();
}
WritableCellFormat format = new WritableCellFormat(font);
try {
format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
// format.setBorder(Border.ALL, BorderLineStyle.THIN,
// Colour.BLACK);// 黑色边框
// format.setBackground(Colour.YELLOW);// 黄色背景
} catch (WriteException e) {
e.printStackTrace();
}
return format;
}
/** 获取SD可用容量 */
private static long getAvailableStorage() {
StatFs statFs = new StatFs(root);
long blockSize = statFs.getBlockSize();
long availableBlocks = statFs.getAvailableBlocks();
long availableSize = blockSize * availableBlocks;
// Formatter.formatFileSize(context, availableSize);
return availableSize;
}
/**
* 查询excel表格结果
* */
public static List<MiningEntity> mListMining = new ArrayList<>();
public static Activity activity;
public static List<MiningEntity> QueryUser(Activity activity,File file){
InputStream is = null;
Workbook workbook = null;
try {
is = new FileInputStream(file.getPath());//获取流
workbook = Workbook.getWorkbook(is);
Sheet sheet = workbook.getSheet(0);
for (int i = 0; i < sheet.getRows(); i++) {
Log.i("asdasd", sheet.getCell(0, i).getContents() + "-" + sheet.getCell(1, i).getContents() +
"-" + sheet.getCell(2, i).getContents()+"-" + sheet.getCell(3, i).getContents());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
workbook.close();
}
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return mListMining;
}
}
导出：writeExcel(上下文，数据，导出文件名);
查询：QueryUser(上下文，查询文件路径);
导出路径为包名目录下的file，查询可以弄个文件选择器来获取相应excle里的内容
文件选择器
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.ll_search:
Intent intent = new Intent(Intent.ACTION_GET_CONTENT);
intent.setType("*/*");//设置任意类型
intent.addCategory(Intent.CATEGORY_OPENABLE);
startActivityForResult(intent, 1);
break;
}
}
//这里处理回调
@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
super.onActivityResult(requestCode, resultCode, data);
if (requestCode == 1) {
if (resultCode == RESULT_OK) {
importExcle(data);
}
}
}
/**
* 导入选中文件
* */
public void importExcle(Intent data){
Uri uri = data.getData();
if (uri != null) {
String path = getPath(this, uri);
if (path != null) {
File file = new File(path);
if (file.exists()) {
//获取的路径
upLoadFilePath = file.toString();
//文件名
upLoadFileName = file.getName();
String[] strArray = upLoadFileName.split("\\.");
int suffixIndex = strArray.length -1;
File dir = new File(upLoadFilePath);
//调用查询方法
ExcelUtil.QueryUser(this, dir);
}
}
}
}
}
文件选择还需要加上几个方法
public String getPath(final Context context, final Uri uri) {
final boolean isKitKat = Build.VERSION.SDK_INT >= Build.VERSION_CODES.KITKAT;
if (isKitKat && DocumentsContract.isDocumentUri(context, uri)) {
if (isExternalStorageDocument(uri)) {
final String docId = DocumentsContract.getDocumentId(uri);
final String[] split = docId.split(":");
final String type = split[0];
if ("primary".equalsIgnoreCase(type)) {
return Environment.getExternalStorageDirectory() + "/" + split[1];
}
}
else if (isDownloadsDocument(uri)) {
final String id = DocumentsContract.getDocumentId(uri);
final Uri contentUri = ContentUris.withAppendedId(
Uri.parse("content://downloads/public_downloads"), Long.valueOf(id));
return getDataColumn(context, contentUri, null, null);
}
// MediaProvider
else if (isMediaDocument(uri)) {
final String docId = DocumentsContract.getDocumentId(uri);
final String[] split = docId.split(":");
final String type = split[0];
Uri contentUri = null;
if ("image".equals(type)) {
contentUri = MediaStore.Images.Media.EXTERNAL_CONTENT_URI;
} else if ("video".equals(type)) {
contentUri = MediaStore.Video.Media.EXTERNAL_CONTENT_URI;
} else if ("audio".equals(type)) {
contentUri = MediaStore.Audio.Media.EXTERNAL_CONTENT_URI;
}
final String selection = "_id=?";
final String[] selectionArgs = new String[]{split[1]};
return getDataColumn(context, contentUri, selection, selectionArgs);
}
}
// MediaStore (and general)
else if ("content".equalsIgnoreCase(uri.getScheme())) {
// Log.i(TAG,"content***"+uri.toString());
return getDataColumn(context, uri, null, null);
}
// Files
else if ("file".equalsIgnoreCase(uri.getScheme())) {
// Log.i(TAG,"file***"+uri.toString());
return uri.getPath();
}
return null;
}
/**
* Get the value of the data column for this Uri. This is useful for
* MediaStore Uris, and other file-based ContentProviders.
*
* @param context The context.
* @param uri The Uri to query.
* @param selection (Optional) Filter used in the query.
* @param selectionArgs (Optional) Selection arguments used in the query.
* @return The value of the _data column, which is typically a file path.
*/
public String getDataColumn(Context context, Uri uri, String selection,
String[] selectionArgs) {
Cursor cursor = null;
final String column = "_data";
final String[] projection = {column};
try {
cursor = context.getContentResolver().query(uri, projection, selection, selectionArgs,
null);
if (cursor != null && cursor.moveToFirst()) {
final int column_index = cursor.getColumnIndexOrThrow(column);
return cursor.getString(column_index);
}
} finally {
if (cursor != null)
cursor.close();
}
return null;
}
public boolean isExternalStorageDocument(Uri uri) {
return "com.android.externalstorage.documents".equals(uri.getAuthority());
}
public boolean isDownloadsDocument(Uri uri) {
return "com.android.providers.downloads.documents".equals(uri.getAuthority());
}
public boolean isMediaDocument(Uri uri) {
return "com.android.providers.media.documents".equals(uri.getAuthority());
}
查询Excle遇到问题
解析时报错
1.可能是伪.xls文件，重新将该文件另存为.xls文件，再重新获取
扫一扫
587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
