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

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_34650238/article/details/79976524

1. AndroidStudio使用

//导入jar包
dependencies {

    compile files('libs/poi-3.15.jar')
    compile 'org.litepal.android:core:1.4.0'

}

2. 使用

//fileChooser界面就省略了,直接调用系统的文件管理,触发事件
...

//导入格式为 .xls .xlsx
Intent 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;
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
//数据导出到excel
String 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;
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 注: 这里导入导出都是用的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"}

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页