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

ExcelUtils.java

  1 import android.content.Context;
  2 import android.widget.Toast;
  3 
  4 import java.io.File;
  5 import java.io.FileInputStream;
  6 import java.io.IOException;
  7 import java.io.InputStream;
  8 import java.util.ArrayList;
  9 import java.util.List;
 10 
 11 import jxl.Workbook;
 12 import jxl.WorkbookSettings;
 13 import jxl.write.Label;
 14 import jxl.write.WritableCell;
 15 import jxl.write.WritableCellFormat;
 16 import jxl.write.WritableFont;
 17 import jxl.write.WritableSheet;
 18 import jxl.write.WritableWorkbook;
 19 import jxl.write.WriteException;
 20 
 21 
 22 /**
 23  * Excel导出工具
 24  */
 25 public class ExcelUtils {
 26 
 27     public static WritableFont arial12font = null;
 28     public static WritableCellFormat arial12format = null;
 29 
 30     public final static String UTF8_ENCODING = "UTF-8";
 31     public final static String GBK_ENCODING = "GBK";
 32 
 33 
 34     public static void format() {
 35         try {
 36             arial12font = new WritableFont(WritableFont.ARIAL, 12);
 37             arial12format = new WritableCellFormat(arial12font);
 38             arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
 39         } catch (WriteException e) {
 40             e.printStackTrace();
 41         }
 42     }
 43 
 44 
 45     /**
 46      * 初始化表格，包括文件名、sheet名、各列的名字
 47      *
 48      * @param filePath  文件路径
 49      * @param sheetName sheet名
 50      * @param colName   各列的名字
 51      */
 52     public static void initExcel(String filePath, String sheetName, String[] colName) {
 53         format();
 54         WritableWorkbook workbook = null;
 55         try {
 56             File file = new File(filePath);
 57             if (!file.exists()) {
 58                 file.createNewFile();
 59             }
 60             workbook = Workbook.createWorkbook(file);
 61             WritableSheet sheet = workbook.createSheet(sheetName, 0);
 62             sheet.addCell((WritableCell) new Label(0, 0, filePath, arial12format));
 63             for (int col = 0; col < colName.length; col++) {
 64                 sheet.addCell(new Label(col, 0, colName[col], arial12format));
 65             }
 66             workbook.write();
 67         } catch (Exception e) {
 68             e.printStackTrace();
 69         } finally {
 70             if (workbook != null) {
 71                 try {
 72                     workbook.close();
 73                 } catch (Exception e) {
 74                     e.printStackTrace();
 75                 }
 76             }
 77         }
 78 
 79     }
 80 
 81     /**
 82      * 将数据写入Excel表格
 83      *
 84      * @param objList  要写的列表数据
 85      * @param filePath 文件路径
 86      * @param c        上下文
 87      * @param <T>
 88      */
 89     public static <T> void writeObjListToExcel(List<T> objList, String filePath, Context c) {
 90         if (objList != null && objList.size() > 0) {
 91             WritableWorkbook writebook = null;
 92             InputStream in = null;
 93             try {
 94                 WorkbookSettings setEncode = new WorkbookSettings();
 95                 setEncode.setEncoding(UTF8_ENCODING);
 96                 in = new FileInputStream(new File(filePath));
 97                 Workbook workbook = Workbook.getWorkbook(in);
 98                 writebook = Workbook.createWorkbook(new File(filePath), workbook);
 99                 WritableSheet sheet = writebook.getSheet(0);
100                 for (int j = 0; j < objList.size(); j++) {
101                     ArrayList<String> list = (ArrayList<String>) objList.get(j);
102                     for (int i = 0; i < list.size(); i++) {
103                         sheet.addCell(new Label(i, j + 1, list.get(i), arial12format));
104                     }
105                 }
106                 writebook.write();
107                 Toast.makeText(c, " 导出成功 ", Toast.LENGTH_SHORT).show();
108             } catch (Exception e) {
109                 e.printStackTrace();
110             } finally {
111                 if (writebook != null) {
112                     try {
113                         writebook.close();
114                     } catch (Exception e) {
115                         e.printStackTrace();
116                     }
117 
118                 }
119                 if (in != null) {
120                     try {
121                         in.close();
122                     } catch (IOException e) {
123                         e.printStackTrace();
124                     }
125                 }
126             }
127 
128         }
129     }
130 
131 
132 }

InformationActivity.java

  1 public class InformationActivity extends AppCompatActivity {
  2 
  3     //查看excel表按钮
  4     private Button mLookExcel;
  5     private String excelFilePath = "";
  6     private String[] colNames = new String[]{"姓名","学号","班级","电话号码","日期", "时间", "体温", "特殊情况", "地理位置"};
  7     String[] pess = new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE, Manifest.permission.READ_EXTERNAL_STORAGE};
  8 
  9     @Override
 10     protected void onCreate(Bundle savedInstanceState) {
 11         super.onCreate(savedInstanceState);
 12         setContentView(R.layout.activity_information);
 13 
 14         //查看excel表格
 15         mLookExcel=findViewById(R.id.Look_excel);
 16         mLookExcel.setOnClickListener(new View.OnClickListener() {
 17             @Override
 18             public void onClick(View v) {
 19                 export();
 20             }
 21         });
 22     }
 23 
 24 
 25 
 26     /**
 27      * 导出表格的操作
 28      * "新的运行时权限机制"只在应用程序的targetSdkVersion>=23时生效，并且只在6.0系统之上有这种机制，在低于6.0的系统上应用程序和以前一样不受影响。
 29      * 当前应用程序的targetSdkVersion小于23（为22），系统会默认其尚未适配新的运行时权限机制，安装后将和以前一样不受影响：即用户在安装应用程序的时候默认允许所有被申明的权限
 30      */
 31     private void export() {
 32         if (this.getApplicationInfo().targetSdkVersion >= 23 && Build.VERSION.SDK_INT >= 23) {
 33             requestPermission();
 34         } else {
 35             writeExcel();
 36         }
 37     }
 38 
 39 
 40     /**
 41      * 动态请求读写权限
 42      */
 43     private void requestPermission() {
 44         if (!checkPermission()) {//如果没有权限则请求权限再写
 45             ActivityCompat.requestPermissions(this, pess, 100);
 46         } else {//如果有权限则直接写
 47             writeExcel();
 48         }
 49     }
 50 
 51 
 52     /**
 53      * 检测权限
 54      *
 55      * @return
 56      */
 57     private boolean checkPermission() {
 58         for (String permission : pess) {
 59             if (ContextCompat.checkSelfPermission(this, permission) != PackageManager.PERMISSION_GRANTED) {
 60                 // 只要有一个权限没有被授予, 则直接返回 false
 61                 return false;
 62             }
 63         }
 64         return true;
 65     }
 66 
 67 
 68     @Override
 69     public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults) {
 70         super.onRequestPermissionsResult(requestCode, permissions, grantResults);
 71         if (requestCode == 100) {
 72             boolean isAllGranted = true;
 73             for (int grant : grantResults) {
 74                 if (grant != PackageManager.PERMISSION_GRANTED) {
 75                     isAllGranted = false;
 76                     break;
 77                 }
 78             }
 79             if (isAllGranted) {//请求到权限了，写Excel
 80                 writeExcel();
 81             } else {//权限被拒绝，不能写
 82                 Toast.makeText(this, "读写手机存储权限被禁止，请在权限管理中心手动打开权限", Toast.LENGTH_LONG).show();
 83             }
 84         }
 85     }
 86 
 87     /**
 88      * 将数据写入excel表格
 89      */
 90     private void writeExcel() {
 91         if (getExternalStoragePath() == null) return;
 92         excelFilePath = getExternalStoragePath() + "/ExportExcel/mine.xls";
 93         if (checkFile(excelFilePath)) {
 94             deleteByPath(excelFilePath);//如果文件存在则先删除原有的文件
 95         }
 96         File file = new File(getExternalStoragePath() + "/ExportExcel");
 97         makeDir(file);
 98         ExcelUtils.initExcel(excelFilePath, "中文版",colNames);//需要写入权限
 99         ExcelUtils.writeObjListToExcel(getTravelData(), excelFilePath, this);
100     }
101 
102     /**
103      * 根据路径生成文件夹
104      *
105      * @param filePath
106      */
107     public static void makeDir(File filePath) {
108         if (!filePath.getParentFile().exists()) {
109             makeDir(filePath.getParentFile());
110         }
111         filePath.mkdir();
112     }
113 
114     /**
115      * 获取外部存储路径
116      *
117      * @return
118      */
119     public String getExternalStoragePath() {
120         File sdDir = null;
121         boolean sdCardExist = Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED);
122         if (sdCardExist) {
123             sdDir = Environment.getExternalStorageDirectory();
124             return sdDir.toString();
125         } else {
126             Toast.makeText(this, "找不到外部存储路径，读写手机存储权限被禁止，请在权限管理中心手动打开权限", Toast.LENGTH_LONG).show();
127             return null;
128         }
129     }
130 
131     /**
132      * 测试数据
133      *
134      * @return
135      */
136     public ArrayList<ArrayList<String>> getTravelData() {
137         ArrayList<ArrayList<String>> datas = new ArrayList<>();
138         List<All> list=new ArrayList<All>();
139         Dao dao=new Dao(InformationActivity.this);
140         list=dao.Query();
141         for(int i=0;i<list.size();i++){
142             All all=list.get(i);
143             ArrayList<String> data = new ArrayList<>();
144             data.add(all.getName());
145             data.add(all.getID());
146             data.add(all.getClasss());
147             data.add(all.getPhone());
148             data.add(all.getDate());
149             data.add(all.getTime());
150             data.add(all.getTemperature());
151             data.add(all.getInstruction());
152             data.add(all.getLocal());
153             datas.add(data);
154         }
155         return datas;
156     }
157 
158     /**
159      * 根据文件路径检测文件是否存在,需要读取权限
160      *
161      * @param filePath 文件路径
162      * @return true存在
163      */
164     private boolean checkFile(String filePath) {
165         File file = new File(filePath);
166         if (file.exists()) {
167             if (file.isFile()) return true;
168             else return false;
169         } else {
170             return false;
171         }
172     }
173 
174 
175     /**
176      * 根据文件路径删除文件
177      *
178      * @param filePath
179      */
180     private void deleteByPath(String filePath) {
181         File file = new File(filePath);
182         if (file.exists()) {
183             if (file.isFile())
184                 file.delete();
185         }
186     }
187 
188 }

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