|
在这里，我使用的poi是3.12版本的，它不是Apache官方poi包
下载jar包后，将其放进libs文件夹里面，选中两个jar包，鼠标右键点击add As library…添加为库，下载jar点击这里  点击OK  打开build.gradle文件，添加完依赖后就可以开始 敲键盘  开发文档可以参考这里，需要注意的是我们使用的poi包不是Apache软件基金会的，部分方法及其功效会有所不同！！！
这里简要介绍我们用到的方法：
创建.xlsx格式文件对象
workbook = new XSSFWorkbook(inputStream);
创建.xls格式文件对象
workbook = new HSSFWorkbook(inputStream);
获取工作表的对象
Sheet sheetAt = workbook.getSheetAt(0);
获取工作表的行
Row row = sheetAt.getRow(0);
获取实际单元格数
int physicalNumberOfCells = row.getPhysicalNumberOfCells();
获取工作表的单元格
Row.getCell(i)；
获得单元格格式
Cell.getCellType()；
获取单元格类型
Cell.getBooleanCellValue();//获取布尔类型的单元格
Cell.getNumericCellValue();//获取数字类型的单元格
Cell.getDateCellValue();//获取日期类型的单元格
Cell.getNumericCellValue();//获取数值类型的单元格
Cell.getStringCellValue();//获取字符串类型的单元格
获取实际行数
SheetAt.getPhysicalNumberOfRows();
创建工作表的名字
XSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("Sheet1"));
创建行
Row row = sheet.createRow(int i);
创建列
Cell cell = row.createCell(int i);
将需要添加到Excel的文本添加到对应的Cell
Cell.setCellValue((String) map.get(j));
将数据写入文件并保存在指定文件夹
OutputStream outputStream = context.getContentResolver().openOutputStream(Uri uri);
XSSFWorkbook.write(outputStream);
读取Excel并将其写入数据库：
public List readExcel(Context context, Uri fileUri, String strFileUri) {
mySQLHelp = new MySQLHelp(context, "mydb.db", null, 1);
SQLiteDatabase writableDatabase = mySQLHelp.getWritableDatabase();
excelStr = strFileUri.substring(strFileUri.lastIndexOf("."));
try {
inputStream = context.getContentResolver().openInputStream(fileUri);
if (excelStr.equals(".xlsx")) workbook = new XSSFWorkbook(inputStream);
else if (excelStr.equals(".xls")) workbook = new HSSFWorkbook(inputStream);
else workbook = null;
if (workbook != null) {
Sheet sheetAt = workbook.getSheetAt(0);
Row row = sheetAt.getRow(0);
int physicalNumberOfCells = row.getPhysicalNumberOfCells();//获取实际单元格数
Map map = new HashMap();
for (int i = 0; i
Map map1 = new HashMap();
Row row1 = sheetAt.getRow(i);
if (!row1.equals(null)) {
for (int j = 0; j
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return dataList;
}
private static Object getCellFormatValue(Cell cell) {
Object cellValue;
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = cell.getDateCellValue();
} else {
cellValue = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
读取数据库数据将其写入Excel并保存到指定路径文件夹
public void getDataAndSave(Context context,Uri uri) {
ArrayList arrayList = new ArrayList();
Map m = new HashMap();
m.put(0,"物料ID");
m.put(1,"物料编码");
m.put(2,"名称");
m.put(3,"编号");
m.put(4,"规格");
m.put(5,"单位");
m.put(6,"单价");
m.put(7,"数量");
m.put(8,"厂家");
m.put(9,"类别");
m.put(10,"经手人");
m.put(11,"存放地点");
m.put(12,"状态");
arrayList.add(m);
mySQLHelp = new MySQLHelp(context, "mydb.db", null, 1);
SQLiteDatabase readableDatabase = mySQLHelp.getReadableDatabase();
cursor = readableDatabase.rawQuery("select * from module", null);
while (cursor.moveToNext()) {
Map map = new HashMap();
String materialID = cursor.getString(cursor.getColumnIndex("materialID"));
String materialEncoding = cursor.getString(cursor.getColumnIndex("materialEncoding"));
String materialName = cursor.getString(cursor.getColumnIndex("materialName"));
String materialModel = cursor.getString(cursor.getColumnIndex("materialModel"));
String materialSize = cursor.getString(cursor.getColumnIndex("materialSize"));
String unit = cursor.getString(cursor.getColumnIndex("unit"));
String price = cursor.getString(cursor.getColumnIndex("price"));
String count = cursor.getString(cursor.getColumnIndex("count"));
String manufacturers = cursor.getString(cursor.getColumnIndex("manufacturers"));
String type = cursor.getString(cursor.getColumnIndex("type"));
String receiptor = cursor.getString(cursor.getColumnIndex("receiptor"));
String storagelocation = cursor.getString(cursor.getColumnIndex("storagelocation"));
String materialState = cursor.getString(cursor.getColumnIndex("materialState"));
map.put(0,materialID);
map.put(1,materialEncoding);
map.put(2,materialName);
map.put(3,materialModel);
map.put(4,materialSize);
map.put(5,unit);
map.put(6,price);
map.put(7,count);
map.put(8,manufacturers);
map.put(9,type);
map.put(10,receiptor);
map.put(11,storagelocation);
map.put(12,materialState);
arrayList.add(map);
}
try {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("Sheet1"));
Cell cell;
int size = arrayList.get(0).size();
for (int i = 0;i
cell = row.createCell(j);
cell.setCellValue((String) map.get(j));
}
}
OutputStream outputStream = context.getContentResolver().openOutputStream(uri);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
Toast.makeText(context, "另存成功", Toast.LENGTH_SHORT).show();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
点击链接前往Github下载源代码
注意： 1、部分xls格式的Excel文件不支持不代表全部xls格式的Excel文件不支持 2、不支持合并单元格(会出现格式调乱和读取失败等问题) 3、jar包必须是poi-3.12-android-a.jar和poi-ooxml-schemas-3.12-20150511-a.jar
|