Android studio将数据导出到外部存储（手机路径）excel表中

本文链接：https://blog.youkuaiyun.com/m0_46301905/article/details/135012352

1、首先导入依赖（因为我用的是最新版android，所以在build.gradle.kts下导入）

       implementation("net.sourceforge.jexcelapi:jxl:2.6.12")

导入依赖之后一定要进行同步

2、准备实体类，将你想到把那些数据写入到excel中（这里只是写入将word和chinese写入到表中）

public class Word {
    private Integer id;
    private String word;
    private String chinese;

    public Word(Integer id，String word, String chinese) {
         this.id = id;
        this.word = word;
        this.chinese = chinese;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getWord() {
        return word;
    }
    public void setWord(String word) {
        this.word = word;
    }
    public String getChinese() {
        return chinese;
    }
    public void setChinese(String chinese) {
        this.chinese = chinese;
    }
}

3、写入一个生成excel工具类（这里我已经封装好了，可以直接拿来使用）

注意这里写入的是自己写的实体类名称，写入字段不同也要进行相应修改

public class ExcelUtil1 {

    private static WritableFont arial14font = null;
    private static WritableCellFormat arial14format = null;
    private static WritableFont arial10font = null;
    private static WritableCellFormat arial10format = null;
    private static WritableFont arial12font = null;
    private static WritableCellFormat arial12format = null;
    private final static String UTF8_ENCODING = "UTF-8";


    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    private static void format() {
        try {
            arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
            arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(jxl.format.Alignment.CENTRE);
            arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);

            arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial10format.setBackground(Colour.GRAY_25);

            arial12font = new WritableFont(WritableFont.ARIAL, 10);
            arial12format = new WritableCellFormat(arial12font);
            //对齐格式
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            //设置边框
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

        } catch (WriteException e) {
            e.printStackTrace();
        }
    }


    /**
     * 初始化Excel表格
     *
     * @param filePath  存放excel文件的路径（path/demo.xls）
     * @param sheetName Excel表格的表名
     * @param colName   excel中包含的列名（可以有多个）
     */
    public static void initExcel(String filePath, String sheetName, String[] colName) {
        format();
        WritableWorkbook workbook = null;
        try {
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            } else {
                return;
            }
            workbook = Workbook.createWorkbook(file);
            //设置表格的名字
            WritableSheet sheet = workbook.createSheet(sheetName, 0);
            //创建标题栏
            sheet.addCell((WritableCell) new Label(0, 0, filePath, arial14format));
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 0, colName[col], arial10format));
            }
            //设置行高
            sheet.setRowView(0, 340);
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 将制定类型的List写入Excel中
     *
     * @param <T>
     * @param objList  待写入的list  自定义的实体类MyDataDTO
     * @param fileName
     * @param mContext
     */
    @SuppressWarnings("unchecked")
    public static <T> void writeObjListToExcel(List<Word> objList, String fileName, Runnable mContext) {
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);

                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName), workbook);
                WritableSheet sheet = writebook.getSheet(0);

                for (int j = 0; j < objList.size(); j++) {
                    Word bean = (Word) objList.get(j);
                    List<String> list = new ArrayList<>();
                    // MyDataDTO 自定义实体类
                    list.add(String.valueOf(bean.getId()));
                    list.add(bean.getWord());
                    list.add(bean.getChinese());
//                    list.add(bean.getStartTime());
//                    list.add(bean.getEndTime());

                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j + 1, list.get(i), arial12format));
                        if (list.get(i).length() <= 4) {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 8);
                        } else {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 5);
                        }
                    }
                    //设置行高
                    sheet.setRowView(j + 1, 350);
                }

                writebook.write();
                workbook.close();
                Log.e("ExcelUtil","导出Excel成功");
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        }
    }


}

4、准备数据（这里只是简单写一些数据放入集合list中）这里根据自己需要来写

5、在页面中写入一个导出按钮，在获取控件之后在控件下写如下代码

 btn7.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        //设置保存文件夹
                        File filePath = getExternalFilesDir(null).getAbsoluteFile();
//                        String filePath = "/sdcard/MyExcelFolder";
                        String filepath = filePath+"";
                        File file = new File(filepath);
                        if (!file.exists()) {
                            file.mkdirs();
                        }
                        //设置保存文件名
                        String excelFileName = "/MyList.xls";
                        //设置Excel第一行表头
                        String[] title = {"id","word", "chinese"};
                        //设置Excel的Sheet名
                        String sheetName = "demoSheetName";
                        String filePaths = filePath + excelFileName;
                        ExcelUtil1.initExcel(filePaths, sheetName, title);
                        ExcelUtil1.writeObjListToExcel(value, filePaths, this);
                    }
                }).start();
            }
        });

6、运行程序（通过usb数据线连接手机注意一定在手机上打开开发者模式点击usb调试）点击导出按钮