Android 导入导出excel xls、xlsx

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 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 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 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 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 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.注意：

需要读写权限

高版本动态申请权限

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，文件内容如下

manifest  application 注册fileprovider

sdk 29以上 允许根目录新建文件

application 加上

android:requestLegacyExternalStorage="true"