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

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);//然后进入系统的文件管理,选择文件后
@Overrideprotected 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.xlsif (excelPath.contains(".xls") || excelPath.contains(".xlsx")) {showSnack("正在加载Excel中...");//载入excelreadExcel(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 sheetIterator<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// contentIterator<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;}

//数据导出到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);//新建excelHSSFWorkbook workBook = new HSSFWorkbook();//新建sheetHSSFSheet 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"}

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

1. AndroidStudio使用

2. 使用

相关文章

[转]Android入门基础教程

python学习（一）：万能的python语言，基础到高级学习

VB速查大全(数据库、表格及报表编程) ★ VB错误处理，ado常见错误，VB数据类型等，网上有很多教程是错的，强烈建议看此文

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

一文搞定CentOS下python(django)+env+mysql+uwsgi+ngnix的安装配置

ios UIWebView 在开发中加载文件

futter开发错误积累

ABAP学习（8）：操作EXCEL