Android 将数据库数据导出至Excel文件 - 一缕半夏微光

Android 将数据库数据导出至Excel文件

ExcelUtils.java

import android.content.Context;
import android.widget.Toast;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


/**
 * Excel导出工具
 */
public class ExcelUtils {

    public static WritableFont arial12font = null;
    public static WritableCellFormat arial12format = null;

    public final static String UTF8_ENCODING = "UTF-8";
    public final static String GBK_ENCODING = "GBK";


    public static void format() {
        try {
            arial12font = new WritableFont(WritableFont.ARIAL, 12);
            arial12format = new WritableCellFormat(arial12font);
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }


    /**
     * 初始化表格，包括文件名、sheet名、各列的名字
     *
     * @param filePath  文件路径
     * @param sheetName sheet名
     * @param colName   各列的名字
     */
    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();
            }
            workbook = Workbook.createWorkbook(file);
            WritableSheet sheet = workbook.createSheet(sheetName, 0);
            sheet.addCell((WritableCell) new Label(0, 0, filePath, arial12format));
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 0, colName[col], arial12format));
            }
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

    }

    /**
     * 将数据写入Excel表格
     *
     * @param objList  要写的列表数据
     * @param filePath 文件路径
     * @param c        上下文
     * @param <T>
     */
    public static <T> void writeObjListToExcel(List<T> objList, String filePath, Context c) {
        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(filePath));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(filePath), workbook);
                WritableSheet sheet = writebook.getSheet(0);
                for (int j = 0; j < objList.size(); j++) {
                    ArrayList<String> list = (ArrayList<String>) objList.get(j);
                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j + 1, list.get(i), arial12format));
                    }
                }
                writebook.write();
                Toast.makeText(c, " 导出成功 ", Toast.LENGTH_SHORT).show();
            } 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();
                    }
                }
            }

        }
    }


}

InformationActivity.java

public class InformationActivity extends AppCompatActivity {

    //查看excel表按钮
    private Button mLookExcel;
    private String excelFilePath = "";
    private String[] colNames = new String[]{"姓名","学号","班级","电话号码","日期", "时间", "体温", "特殊情况", "地理位置"};
    String[] pess = new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE, Manifest.permission.READ_EXTERNAL_STORAGE};

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_information);

        //查看excel表格
        mLookExcel=findViewById(R.id.Look_excel);
        mLookExcel.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                export();
            }
        });
    }



    /**
     * 导出表格的操作
     * "新的运行时权限机制"只在应用程序的targetSdkVersion>=23时生效，并且只在6.0系统之上有这种机制，在低于6.0的系统上应用程序和以前一样不受影响。
     * 当前应用程序的targetSdkVersion小于23（为22），系统会默认其尚未适配新的运行时权限机制，安装后将和以前一样不受影响：即用户在安装应用程序的时候默认允许所有被申明的权限
     */
    private void export() {
        if (this.getApplicationInfo().targetSdkVersion >= 23 && Build.VERSION.SDK_INT >= 23) {
            requestPermission();
        } else {
            writeExcel();
        }
    }


    /**
     * 动态请求读写权限
     */
    private void requestPermission() {
        if (!checkPermission()) {//如果没有权限则请求权限再写
            ActivityCompat.requestPermissions(this, pess, 100);
        } else {//如果有权限则直接写
            writeExcel();
        }
    }


    /**
     * 检测权限
     *
     * @return
     */
    private boolean checkPermission() {
        for (String permission : pess) {
            if (ContextCompat.checkSelfPermission(this, permission) != PackageManager.PERMISSION_GRANTED) {
                // 只要有一个权限没有被授予, 则直接返回 false
                return false;
            }
        }
        return true;
    }


    @Override
    public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults) {
        super.onRequestPermissionsResult(requestCode, permissions, grantResults);
        if (requestCode == 100) {
            boolean isAllGranted = true;
            for (int grant : grantResults) {
                if (grant != PackageManager.PERMISSION_GRANTED) {
                    isAllGranted = false;
                    break;
                }
            }
            if (isAllGranted) {//请求到权限了，写Excel
                writeExcel();
            } else {//权限被拒绝，不能写
                Toast.makeText(this, "读写手机存储权限被禁止，请在权限管理中心手动打开权限", Toast.LENGTH_LONG).show();
            }
        }
    }

    /**
     * 将数据写入excel表格
     */
    private void writeExcel() {
        if (getExternalStoragePath() == null) return;
        excelFilePath = getExternalStoragePath() + "/ExportExcel/mine.xls";
        if (checkFile(excelFilePath)) {
            deleteByPath(excelFilePath);//如果文件存在则先删除原有的文件
        }
        File file = new File(getExternalStoragePath() + "/ExportExcel");
        makeDir(file);
        ExcelUtils.initExcel(excelFilePath, "中文版",colNames);//需要写入权限
        ExcelUtils.writeObjListToExcel(getTravelData(), excelFilePath, this);
    }

    /**
     * 根据路径生成文件夹
     *
     * @param filePath
     */
    public static void makeDir(File filePath) {
        if (!filePath.getParentFile().exists()) {
            makeDir(filePath.getParentFile());
        }
        filePath.mkdir();
    }

    /**
     * 获取外部存储路径
     *
     * @return
     */
    public String getExternalStoragePath() {
        File sdDir = null;
        boolean sdCardExist = Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED);
        if (sdCardExist) {
            sdDir = Environment.getExternalStorageDirectory();
            return sdDir.toString();
        } else {
            Toast.makeText(this, "找不到外部存储路径，读写手机存储权限被禁止，请在权限管理中心手动打开权限", Toast.LENGTH_LONG).show();
            return null;
        }
    }

    /**
     * 测试数据
     *
     * @return
     */
    public ArrayList<ArrayList<String>> getTravelData() {
        ArrayList<ArrayList<String>> datas = new ArrayList<>();
        List<All> list=new ArrayList<All>();
        Dao dao=new Dao(InformationActivity.this);
        list=dao.Query();
        for(int i=0;i<list.size();i++){
            All all=list.get(i);
            ArrayList<String> data = new ArrayList<>();
            data.add(all.getName());
            data.add(all.getID());
            data.add(all.getClasss());
            data.add(all.getPhone());
            data.add(all.getDate());
            data.add(all.getTime());
            data.add(all.getTemperature());
            data.add(all.getInstruction());
            data.add(all.getLocal());
            datas.add(data);
        }
        return datas;
    }

    /**
     * 根据文件路径检测文件是否存在,需要读取权限
     *
     * @param filePath 文件路径
     * @return true存在
     */
    private boolean checkFile(String filePath) {
        File file = new File(filePath);
        if (file.exists()) {
            if (file.isFile()) return true;
            else return false;
        } else {
            return false;
        }
    }


    /**
     * 根据文件路径删除文件
     *
     * @param filePath
     */
    private void deleteByPath(String filePath) {
        File file = new File(filePath);
        if (file.exists()) {
            if (file.isFile())
                file.delete();
        }
    }

}

参考博文：https://blog.csdn.net/Kikitious_Du/article/details/72271797