1.导入依赖
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.17' implementation group: 'org.apache.xmlbeans', name: 'xmlbeans', version: '3.1.0' implementation 'javax.xml.stream:stax-api:1.0' implementation 'com.fasterxml:aalto-xml:1.2.2'
工具类
package com.example.exportfile;
import android.content.ContentUris;
import android.content.Context;
import android.database.Cursor;
import android.graphics.Canvas;
import android.graphics.pdf.PdfDocument;
import android.net.Uri;
import android.os.Build;
import android.os.Environment;
import android.provider.DocumentsContract;
import android.provider.MediaStore;
import android.text.TextUtils;
import android.util.Log;
import android.view.View;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
public class SheetHelper {
static String TAG = "<<< SheetHelper >>>";
/**
* 导出Excel
*
* @param title 标题,配合 DeviceInfo 按需传入
* @param listData 导出行数据
* @param fileDir 导出文件夹
* @param fileName 导出文件名
* @param context activity上下文
* @param fileNameReplace 文件名称存在时,是否需要替换
* @return
*/
public static boolean exportExcel(String[] title, List<DeviceInfo> listData, String fileDir, String fileName, Context context, boolean fileNameReplace) {
if (TextUtils.isEmpty(fileDir) || TextUtils.isEmpty(fileName) || listData == null) {
Log.e(TAG, " 导出" + "入参不合规");
return false;
}
try {
// 创建excel xlsx格式
Workbook wb = new XSSFWorkbook();
// 创建工作表
Sheet sheet = wb.createSheet();
//创建行对象
Row row = sheet.createRow(0);
// 设置有效数据的行数和列数
int colNum = title.length; // String[] title = {"序号", "设备号", "手机品牌", "最新位置", "换机换卡次数", "应用安装数量", "卸载应用次数", "违规app个数"};
for (int i = 0; i < colNum; i++) {
sheet.setColumnWidth(i, 20 * 256); // 显示20个字符的宽度 列宽
Cell cell1 = row.createCell(i);
//第一行
cell1.setCellValue(title[i]);
}
// 导入数据
for (int rowNum = 0; rowNum < listData.size(); rowNum++) {
// 之所以rowNum + 1 是因为要设置第二行单元格
row = sheet.createRow(rowNum + 1);
// 设置单元格显示宽度
row.setHeightInPoints(28f);
// DeviceInfo 这个是我的业务类,这个是根据业务来进行填写数据
DeviceInfo bean = listData.get(rowNum);
for (int j = 0; j < title.length; j++) {
Cell cell = row.createCell(j);
//要和title[]一一对应
switch (j) {
case 0:
//序号
cell.setCellValue(bean.getRow());
break;
case 1:
//设备id
cell.setCellValue(bean.getDeviceId());
break;
case 2:
//手机品牌
cell.setCellValue(bean.getPhoneBrand());
break;
case 3:
//最新位置
cell.setCellValue(bean.getLatestLocation());
break;
case 4:
//换机换卡次数
cell.setCellValue(bean.getChangeSum());
break;
case 5:
//应用安装数量
cell.setCellValue(bean.getInstallAppSum());
break;
case 6:
//卸载应用次数
cell.setCellValue(bean.getUninstallAppSum());
break;
case 7:
//违规app个数
cell.setCellValue(bean.getViolationAppSum());
break;
}
}
}
String s = Environment.getExternalStorageDirectory() + "/" + fileDir;
// String mSDCardFolderPath = context.getExternalFilesDir(Environment.DIRECTORY_DOWNLOADS) + "/" + fileDir;
File dir = new File(s);
//判断文件是否存在
if (!dir.exists()) {
//不存在则创建
dir.mkdirs();
}
File excel = new File(dir, fileName + ".xlsx");
if (!excel.exists()) {
excel.createNewFile();
} else {
if (fileNameReplace) {
//String newFileName = getNewFileName(getFiles(dir.getPath(), new ArrayList<>()), excel.getPath());
String newFileName = getXlsxNewFileName(excel);
excel = new File(newFileName);
excel.createNewFile();
}
}
Log.e(TAG, " 导出路径" + excel.getPath().toString());
FileOutputStream fos = new FileOutputStream(excel);
wb.write(fos);
wb.close();
fos.flush();
fos.close();
return true;
} catch (IOException e) {
Log.e("ExpressExcle", "exportExcel", e);
return false;
}
}
/**
* 导入excel
*
* @param fileName 本地文件路径
*/
public static List<String> readExcel(String fileName) {
Log.d(TAG, "!!!导入路径!!!" + fileName);
if (TextUtils.isEmpty(fileName)) {
Log.d(TAG, "!!!导入失败!!!" + " 路径为空 ");
return null;
}
try {
InputStream inputStream = new FileInputStream(fileName);
Workbook workbook;
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else {
Log.d(TAG, "!!!导入失败!!!" + " 文件格式错误 ");
return null;
}
int numberOfSheets = workbook.getNumberOfSheets();
List<String> strings = new ArrayList<>();
for (int u = 0; u < numberOfSheets; u++) {
Sheet sheet = workbook.getSheetAt(u);//获取表
int rowsCount = sheet.getPhysicalNumberOfRows();//获取行数
int lastRowNum = sheet.getLastRowNum();//获取最后一行,,从0开始
Log.d(TAG, "行数:" + (lastRowNum + 1));
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
for (int r = 0; r <= lastRowNum; r++) {
String str = "";
Row row = sheet.getRow(r);//拿到行对象
if (row != null) {
int physicalNumberOfCells = row.getPhysicalNumberOfCells();//获取该行可用的列数、
short lastCellNum = row.getLastCellNum();//获取改行最后一列的列数
int lastCell = lastCellNum;
Log.d(TAG, "导入 第" + (r + 1) + "行最后一列:" + lastCell);
for (int i = 0; i < lastCell; i++) {
CellValue v0 = formulaEvaluator.evaluate(row.getCell(i));//获取单元格对象
if (v0 != null) {
CellType cellTypeEnum = v0.getCellTypeEnum();
if (cellTypeEnum.getCode() == 1) {
//文本类型
str += v0.getStringValue() + "&&";
} else if (cellTypeEnum.getCode() == 0) {
//整数,小数类型
// double numberValue = v0.getNumberValue();
String stringValue = new NumberEval(v0.getNumberValue()).getStringValue();
str += stringValue + "&&";
} else {
//其他类型,暂时不解析
}
}
}
Log.d(TAG, "导入 第" + (r + 1) + "行 内容:" + str);
strings.add(str);
} else {
Log.d(TAG, "第 " + (r + 1) + " 行没有可用表格,跳过");
continue;
}
}
}
workbook.close();
return strings;
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
/**
* 导出PDF
*
* @param view 要导出的view,如果view高度过高(超过一屏的高度),在改view外部套一层Scrollview即可
* @param fileDir 导出文件夹
* @param fileName 导出文件名称
* @param fileNameReplace 文件名称存在时,是否需要替换
* @return
*/
public static boolean createPdfFromView(View view, String fileDir, String fileName, boolean fileNameReplace) {
try {
if (view == null || fileDir == null || fileName == null) {
Log.e(TAG, "导出PDF" + "入参为空");
return false;
}
String s = Environment.getExternalStorageDirectory() + "/" + fileDir;
// String mSDCardFolderPath = context.getExternalFilesDir(Environment.DIRECTORY_DOWNLOADS) + "/" + fileDir;
File dir = new File(s);
//判断文件是否存在
if (!dir.exists()) {
//不存在则创建
dir.mkdirs();
}
File pdfFile = new File(dir, fileName + ".PDF");
if (!pdfFile.exists()) {
pdfFile.createNewFile();
} else {
if (fileNameReplace) {
String newFileName = getPDFNewFileName(pdfFile);
pdfFile = new File(newFileName);
pdfFile.createNewFile();
}
}
PdfDocument document = new PdfDocument();
//页对象
PdfDocument.PageInfo pageInfo = new PdfDocument.PageInfo.Builder(
view.getWidth(),
view.getHeight(),
1)
.create();
// 开始页
PdfDocument.Page page = document.startPage(pageInfo);
//绘制页
Canvas canvas = page.getCanvas();
view.draw(canvas);
//结束页
document.finishPage(page);
//TODO 需要的话,增加更多页
//导出文档
FileOutputStream os = null;
Log.i(TAG, "导出PDF" + " 开始导出,导出路径:" + pdfFile);
os = new FileOutputStream(pdfFile);
document.writeTo(os);
os.close();
Log.i(TAG, "导出PDF" + " 导出成功");
document.close();
return true;
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
private static String getXlsxNewFileName(File file) {
if (file.exists()) {
String newPath = file.getPath().substring(0, file.getPath().length() - 5) + "(1).xlsx";
return getXlsxNewFileName(new File(newPath));
} else {
return file.getPath();
}
}
private static String getPDFNewFileName(File file) {
if (file.exists()) {
String newPath = file.getPath().substring(0, file.getPath().length() - 4) + "(1).PDF";
return getPDFNewFileName(new File(newPath));
} else {
return file.getPath();
}
}
static class DeviceInfo {
private String row;//行
private String deviceId;//设备id
private String phoneBrand;//手机品牌
private String latestLocation;//最新位置
private String changeSum;//换机换卡次数
private String installAppSum;//应用安装数量
private String uninstallAppSum;//卸载应用次数
private String violationAppSum;//违规app个数
public DeviceInfo() {
}
public String getRow() {
return row;
}
public void setRow(String row) {
this.row = row;
}
public String getDeviceId() {
return deviceId;
}
public void setDeviceId(String deviceId) {
this.deviceId = deviceId;
}
public String getPhoneBrand() {
return phoneBrand;
}
public void setPhoneBrand(String phoneBrand) {
this.phoneBrand = phoneBrand;
}
public String getLatestLocation() {
return latestLocation;
}
public void setLatestLocation(String latestLocation) {
this.latestLocation = latestLocation;
}
public String getChangeSum() {
return changeSum;
}
public void setChangeSum(String changeSum) {
this.changeSum = changeSum;
}
public String getInstallAppSum() {
return installAppSum;
}
public void setInstallAppSum(String installAppSum) {
this.installAppSum = installAppSum;
}
public String getUninstallAppSum() {
return uninstallAppSum;
}
public void setUninstallAppSum(String uninstallAppSum) {
this.uninstallAppSum = uninstallAppSum;
}
public String getViolationAppSum() {
return violationAppSum;
}
public void setViolationAppSum(String violationAppSum) {
this.violationAppSum = violationAppSum;
}
}
}
2.导出 xls 需要异步调用
核心代码:
boolean isSuccess = SheetHelper.exportExcel(title, deviceInfos, "应用痕迹导出文件夹", "导出测试", MainActivity.this, true);
示例:
List<SheetHelper.DeviceInfo> deviceInfos = new ArrayList<>();
for (int i = 0; i < 20; i++) {
SheetHelper.DeviceInfo deviceInfo = new SheetHelper.DeviceInfo();
deviceInfo.setRow(String.valueOf(i + 1));//序号
deviceInfo.setDeviceId("1531359236" + i);//设备id
deviceInfo.setPhoneBrand("华为" + i);//手机品牌
deviceInfo.setLatestLocation("北京" + i);//最新位置
deviceInfo.setChangeSum(String.valueOf(i));//换机换卡次数
deviceInfo.setInstallAppSum(String.valueOf(i));//应用安装数量
deviceInfo.setUninstallAppSum(String.valueOf(i));//卸载应用次数
deviceInfo.setViolationAppSum(String.valueOf(i));//违规app个数
deviceInfos.add(deviceInfo);
}
try {
String[] PERMISSIONS_STORAGE = {Manifest.permission.READ_EXTERNAL_STORAGE, Manifest.permission.WRITE_EXTERNAL_STORAGE};
int permission = ActivityCompat.checkSelfPermission(MainActivity.this, "android.permission.WRITE_EXTERNAL_STORAGE");
if (permission != PackageManager.PERMISSION_GRANTED) {
ActivityCompat.requestPermissions(MainActivity.this, PERMISSIONS_STORAGE, REQUEST_CODE);
} else {
// boolean isSuccess = SheetHelper.exportExcel(phonebillExpressBeans, "应用痕迹导出文件夹", "导出测试", MainActivity.this);
String[] title = {"序号", "设备号", "手机品牌", "最新位置", "换机换卡次数", "应用安装数量", "卸载应用次数", "违规app个数"};
boolean isSuccess = SheetHelper.exportExcel(title, deviceInfos, "应用痕迹导出文件夹", "导出测试", MainActivity.this, true);
runOnUiThread(new Runnable() {
@Override
public void run() {
if (isSuccess) {
Toast.makeText(MainActivity.this, "导出成功", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(MainActivity.this, "导出失败", Toast.LENGTH_SHORT).show();
}
}
});
}
} catch (Exception e) {
e.printStackTrace();
}
}
}).start();
3.导入 需要异步调用
首先调用系统文件管理器
Intent intent = new Intent(Intent.ACTION_GET_CONTENT);
intent.setType("*/*");//设置类型,我这里是任意类型,任意后缀的可以这样写。
intent.addCategory(Intent.CATEGORY_OPENABLE);
startActivityForResult(intent, 1000);
接受管理器选中的文件
@Override
protected void onActivityResult(int requestCode, int resultCode, @Nullable Intent data) {
super.onActivityResult(requestCode, resultCode, data);
try {
if (resultCode == Activity.RESULT_OK && requestCode == 1000) {
Uri uri = data.getData();//得到uri,后面就是将uri转化成file的过程。
String absolutePath = FileChooseUtil.uriToPath(this, uri);
Log.d("选择了文件", "文件路径:" + absolutePath);
new Thread(new Runnable() {
@Override
public void run() {
List<String> strings = SheetHelper.readExcel(absolutePath);
runOnUiThread(new Runnable() {
@Override
public void run() {
if (strings != null) {
String str = "";
for (String string : strings) {
String[] split = string.split("&&");
for (String s : split) {
Log.i("拿到数据", s);
str += s;
}
str += "\n";
}
mTv_content.setText(str);
Toast.makeText(MainActivity.this, "导入成功", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(MainActivity.this, "导入失败", Toast.LENGTH_SHORT).show();
}
}
});
}
}).start();
}
} catch (Exception e) {
Toast.makeText(MainActivity.this, "导入异常", Toast.LENGTH_SHORT).show();
e.printStackTrace();
}
}
产考:
Android 系统文件浏览器_钟情短发姑娘的博客-CSDN博客
4.注意:
需要读写权限
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" /> <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
高版本动态申请权限
String[] PERMISSIONS_STORAGE = {Manifest.permission.READ_EXTERNAL_STORAGE, Manifest.permission.WRITE_EXTERNAL_STORAGE}; int permission = ActivityCompat.checkSelfPermission(this, "android.permission.WRITE_EXTERNAL_STORAGE"); if (permission != PackageManager.PERMISSION_GRANTED) { ActivityCompat.requestPermissions(this, PERMISSIONS_STORAGE, 1); } else { //logic }
Android 10 有私密文件策略。解决:
res下新建xml文件夹,新建文件file_paths,文件内容如下
<resources> <paths> <root-path name="root" path="" /> <files-path name="files" path="" /> <cache-path name="cache" path="" /> <external-path name="external" path="" /> <external-files-path name="external_file_path" path="" /> <external-cache-path name="external_cache_path" path="" /> </paths> </resources>
manifest application 注册fileprovider
<provider android:name="androidx.core.content.FileProvider" android:authorities="cn.xy.phonebilladmin.fileProvider" android:exported="false" android:grantUriPermissions="true"> <meta-data android:name="android.support.FILE_PROVIDER_PATHS" android:resource="@xml/file_paths" /> </provider>
sdk 29以上 允许根目录新建文件
application 加上
android:requestLegacyExternalStorage="true"