[Android]导入导出Excel表格到本地SQLite

[Android]导入导出Excel表格到SQLite

@Author GQ 2016年11月16日  最近郭神出了LitePal的新版本,感觉好用的不要不要的,导入数据的时候每次都要手写添加,不如直接用excel导入方便多了.

效果图

• 需要导入的excel资源,自己随便写的,只有学号和学生姓名两列

• 导入后

查阅资料后,发现有 jxl 和 poi 两种, 貌似前者不太受欢迎了,所以这里记录POI方式:

用到的jar包: http://poi.apache.org/download.html#POI-3.15

1. AndroidStudio使用

//导入jar包dependencies {    compile files('libs/poi-3.15.jar')    compile 'org.litepal.android:core:1.4.0'}

2. 使用

• POI:

HSSF － 提供读写Microsoft Excel XLS格式档案的功能。　　

XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。　　

HWPF － 提供读写Microsoft Word DOC格式档案的功能。　　

HSLF － 提供读写Microsoft PowerPoint格式档案的功能。　　

HDGF － 提供读Microsoft Visio格式档案的功能。　　

HPBF － 提供读Microsoft Publisher格式档案的功能。　　

HSMF － 提供读Microsoft Outlook格式档案的功能。

//fileChooser界面就省略了,直接调用系统的文件管理,触发事件...//导入格式为 .xls .xlsxIntent intent = new Intent(Intent.ACTION_GET_CONTENT);intent.setType("application/*");//设置类型intent.addCategory(Intent.CATEGORY_OPENABLE);startActivityForResult(intent, 1);//然后进入系统的文件管理,选择文件后@Override    protected void onActivityResult(int requestCode, int resultCode, Intent data) {        if (resultCode == RESULT_OK && data != null) {            LogUtil.e(TAG, "选择的文件Uri = " + data.toString());            //通过Uri获取真实路径            final String excelPath = getRealFilePath(this, data.getData());            LogUtil.e(TAG, "excelPath = " + excelPath);//    /storage/emulated/0/test.xls           if (excelPath.contains(".xls") || excelPath.contains(".xlsx")) {                showSnack("正在加载Excel中...");                //载入excel                readExcel(excelPath);            } else {                showSnack("此文件不是excel格式");            }        }    }//读取Excel表    private void readExcel(String excelPath) {        try {            InputStream input = new FileInputStream(new File(excelPath));            POIFSFileSystem fs = new POIFSFileSystem(input);            HSSFWorkbook wb = new HSSFWorkbook(fs);            HSSFSheet sheet = wb.getSheetAt(0);            // Iterate over each row in the sheet            Iterator<Row> rows = sheet.rowIterator();            while (rows.hasNext()) {                HSSFRow row = (HSSFRow) rows.next();                System.out.println("Row #" + row.getRowNum());                //每一行 = 新建一个学生                Student stu = new Student();                // Iterate over each cell in the row and print out the cell"s                // content                Iterator<Cell> cells = row.cellIterator();                while (cells.hasNext()) {                    HSSFCell cell = (HSSFCell) cells.next();                    switch (cell.getCellType()) {                        case HSSFCell.CELL_TYPE_NUMERIC:                            System.out.println("number= " + (int) (cell.getNumericCellValue()));                        //自定操作,我这里写入学号                            stu.setSno((int) (cell.getNumericCellValue()) + "");                            break;                        case HSSFCell.CELL_TYPE_STRING:                            System.out.println("string= " + cell.getStringCellValue());                        //自定操作,我这里写入姓名                            stu.setName(cell.getStringCellValue());                            break;                        case HSSFCell.CELL_TYPE_BOOLEAN:                            System.out.println("boolean= " + cell.getBooleanCellValue());                            break;                        case HSSFCell.CELL_TYPE_FORMULA:                            System.out.println("formula= " + cell.getCellFormula());                            break;                        default:                            System.out.println("unsuported sell type");                            break;                    }                }                stu.save();            }        } catch (IOException ex) {            ex.printStackTrace();        }        //刷新列表        getAllStudent();    }//查询所有学生private void getAllStudent() {    studentList = DataSupport.findAll(Student.class);}    /**     * 根据Uri获取真实图片路径     * <p/>     * 一个android文件的Uri地址一般如下：     * content://media/external/images/media/62026     *     * @param context     * @param uri     * @return     */    public static String getRealFilePath(final Context context, final Uri uri) {        if (null == uri) return null;        final String scheme = uri.getScheme();        String data = null;        if (scheme == null)            data = uri.getPath();        else if (ContentResolver.SCHEME_FILE.equals(scheme)) {            data = uri.getPath();        } else if (ContentResolver.SCHEME_CONTENT.equals(scheme)) {            Cursor cursor = context.getContentResolver().query(uri, new String[]{MediaStore.Images.ImageColumns.DATA}, null, null, null);            if (null != cursor) {                if (cursor.moveToFirst()) {                    int index = cursor.getColumnIndex(MediaStore.Images.ImageColumns.DATA);                    if (index > -1) {                        data = cursor.getString(index);                    }                }                cursor.close();            }        }        return data;    }

//数据导出到excelString ROOT_PATH = Environment.getExternalStorageDirectory().getAbsolutePath() + "/" + getResources().getString(R.string.app_name) + "/";    public void writeExcel(String exFileName, String sheetName) {        try {            File dir = new File(ROOT_PATH);            if (!dir.exists()) {                dir.mkdirs();            }            String exPath = ROOT_PATH + exFileName + ".xls";            File file = new File(exPath);            file.createNewFile();            OutputStream out = new FileOutputStream(file);            //新建excel            HSSFWorkbook workBook = new HSSFWorkbook();            //新建sheet            HSSFSheet sheet = workBook.createSheet(sheetName);            //创建单元格样式            HSSFCellStyle style = getStyle(workBook);            for (int i = 0; i < adapterList.size(); i++) {                //创建行                HSSFRow row = sheet.createRow(i);                ListInfo info = adapterList.get(i);                for (int j = 0; j < 13; j++) {                    //创建列单元格                    HSSFCell cell = row.createCell(j);                    cell.setCellStyle(style);                    switch (j) {                        case 0://时间                            cell.setCellValue(info.getTiem());                            break;                        case 1:                            cell.setCellValue(info.getWq());                            break;                        case 2:                            cell.setCellValue(info.getWb());                            break;                        case 3:                            cell.setCellValue(info.getWs());                            break;                        case 4:                            cell.setCellValue(info.getWg());                            break;                        case 5:                            cell.setCellValue(info.getWq());                            break;                        case 6:                            cell.setCellValue(info.getQb());                            break;                        case 7:                            cell.setCellValue(info.getQs());                            break;                        case 8:                            cell.setCellValue(info.getQg());                            break;                        case 9:                            cell.setCellValue(info.getBs());                            break;                        case 10:                            cell.setCellValue(info.getBg());                            break;                        case 11:                            cell.setCellValue(info.getSg());                            break;                        case 12://号码                            cell.setCellValue(info.getCode());                            break;                    }                    //合并单元格，参数是起始行，结束行，起始列，结束列//                    sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, i, i));                }            }            workBook.write(out);            out.flush();            out.close();            showShortToast("Excel文件保存到 ：" + ROOT_PATH);        } catch (Exception e) {            e.printStackTrace();            showShortToast("Excel文件" + exFileName + "生成失败：" + e);        }    }    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {        //设置样式;        HSSFCellStyle style = workbook.createCellStyle();        //设置底边框;        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);        //设置底边框颜色;        style.setBottomBorderColor(HSSFColor.BLACK.index);        //设置左边框;        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);        //设置左边框颜色;        style.setLeftBorderColor(HSSFColor.BLACK.index);        //设置右边框;        style.setBorderRight(HSSFCellStyle.BORDER_THIN);        //设置右边框颜色;        style.setRightBorderColor(HSSFColor.BLACK.index);        //设置顶边框;        style.setBorderTop(HSSFCellStyle.BORDER_THIN);        //设置顶边框颜色;        style.setTopBorderColor(HSSFColor.BLACK.index);        //设置自动换行;        style.setWrapText(false);        //设置水平对齐的样式为居中对齐;        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);        //设置垂直对齐的样式为居中对齐;        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        // 设置单元格字体        HSSFFont font = workbook.createFont();        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        font.setFontName("宋体");        font.setFontHeight((short) 200);        style.setFont(font);        return style;    }

• 注: 这里导入导出都是用的excel2003 (使用2007的每次都报错,暂时没有解决)

```java//intent.setType("*/*")格式大全    {".3gp",    "video/3gpp"},    {".apk",    "application/vnd.android.package-archive"},    {".asf",    "video/x-ms-asf"},    {".avi",    "video/x-msvideo"},    {".bin",    "application/octet-stream"},    {".bmp",    "image/bmp"},    {".c",  "text/plain"},    {".class",  "application/octet-stream"},    {".conf",   "text/plain"},    {".cpp",    "text/plain"},    {".doc",    "application/msword"},    {".docx",   "application/vnd.openxmlformats-officedocument.wordprocessingml.document"},    {".xls",    "application/vnd.ms-excel"},    {".xlsx",   "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"},    {".exe",    "application/octet-stream"},    {".gif",    "image/gif"},    {".gtar",   "application/x-gtar"},    {".gz", "application/x-gzip"},    {".h",  "text/plain"},    {".htm",    "text/html"},    {".html",   "text/html"},    {".jar",    "application/java-archive"},    {".java",   "text/plain"},    {".jpeg",   "image/jpeg"},    {".jpg",    "image/jpeg"},    {".js", "application/x-javascript"},    {".log",    "text/plain"},    {".m3u",    "audio/x-mpegurl"},    {".m4a",    "audio/mp4a-latm"},    {".m4b",    "audio/mp4a-latm"},    {".m4p",    "audio/mp4a-latm"},    {".m4u",    "video/vnd.mpegurl"},    {".m4v",    "video/x-m4v"},    {".mov",    "video/quicktime"},    {".mp2",    "audio/x-mpeg"},    {".mp3",    "audio/x-mpeg"},    {".mp4",    "video/mp4"},    {".mpc",    "application/vnd.mpohun.certificate"},    {".mpe",    "video/mpeg"},    {".mpeg",   "video/mpeg"},    {".mpg",    "video/mpeg"},    {".mpg4",   "video/mp4"},    {".mpga",   "audio/mpeg"},    {".msg",    "application/vnd.ms-outlook"},    {".ogg",    "audio/ogg"},    {".pdf",    "application/pdf"},    {".png",    "image/png"},    {".pps",    "application/vnd.ms-powerpoint"},    {".ppt",    "application/vnd.ms-powerpoint"},    {".pptx",   "application/vnd.openxmlformats-officedocument.presentationml.presentation"},    {".prop",   "text/plain"},    {".rc", "text/plain"},    {".rmvb",   "audio/x-pn-realaudio"},    {".rtf",    "application/rtf"},    {".sh", "text/plain"},    {".tar",    "application/x-tar"},    {".tgz",    "application/x-compressed"},    {".txt",    "text/plain"},    {".wav",    "audio/x-wav"},    {".wma",    "audio/x-ms-wma"},    {".wmv",    "audio/x-ms-wmv"},    {".wps",    "application/vnd.ms-works"},    {".xml",    "text/plain"},    {".z",  "application/x-compress"},    {".zip",    "application/x-zip-compressed"}