Android 使用poi生成Excel ,word并保存在指定路径内

一添加依赖（一定要用新版依赖防止一些bug）

minSdk= 26   //注意最小支持SDK26
dependencies {implementation 'org.apache.poi:poi:5.2.4'implementation 'org.apache.poi:poi-ooxml:5.2.4'implementation 'javax.xml.stream:stax-api:1.0-2'
}

二，创建方法

private void createExcelFile(String Path) {// 创建工作簿Workbook workbook = new XSSFWorkbook();// 创建工作表Sheet sheet = workbook.createSheet("姓名");// 创建行/*   Row row = sheet.createRow(0);// 创建单元格for (int i = 0; i <10 ; i++) {Cell cell = row.createCell(i);// 设置单元格的值cell.setCellValue("Fengfeng");}*/ArrayList<Map<Integer,Object>> arrayList = new ArrayList<>();Map<Integer,Object> 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,"类别");arrayList.add(m);for (int i = 0; i <10 ; i++) {Map<Integer,Object> map = new HashMap<>();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");arrayList.add(map);}Cell cell;int size = arrayList.get(0).size();for (int i = 0;i < arrayList.size();i++){Row row = sheet.createRow(i);Map<Integer, Object> map1 = arrayList.get(i);for (int j = 0;j < size;j++){cell = row.createCell(j);cell.setCellValue((String) map1.get(j));}}// 保存Excel文件try {File file = new File(Path, "example.xlsx");FileOutputStream outputStream = new FileOutputStream(file);workbook.write(outputStream);outputStream.close();Toast.makeText(this, "Excel文件已创建", Toast.LENGTH_SHORT).show();} catch (IOException e) {e.printStackTrace();}
}

三，如果有表追加的做法(这里使用的是room数据库导入的)

public class DbConvertExcel {public static void appendToExcelFile(Context context, String filePath) {// 工作表名称String sheetName = "test";testDao testDao = testDatabase.getDatabaseInstance(context).getLaserMachDao();try {File file = new File(filePath, "test.xlsx");XSSFWorkbook workbook;Sheet sheet;if (file.exists()) {/*          FileInputStream inputStream = new FileInputStream(file);OPCPackage opc = OPCPackage.open(inputStream);workbook = new XSSFWorkbook(opc);sheet = workbook.getSheet(sheetName);opc.close();  // 关闭OPCPackage*/FileInputStream inputStream = new FileInputStream(file);workbook = new XSSFWorkbook(inputStream);sheet = workbook.getSheet(sheetName);} else {workbook = new XSSFWorkbook();sheet = workbook.createSheet(sheetName);// 添加标题行Row titleRow = sheet.createRow(0);titleRow.createCell(0).setCellValue("编号");titleRow.createCell(1).setCellValue("名称");titleRow.createCell(2).setCellValue("类型");}int lastRowNum = sheet.getLastRowNum();List<test> all = testDao.getAll();for (test test: all) {Row row = sheet.createRow(lastRowNum + 1); // 从最后一行的下一行开始写入row.createCell(0).setCellValue(String.valueOf(test.getId()));row.createCell(1).setCellValue(test.getName());row.createCell(2).setCellValue(String.valueOf(test.getHandType()));lastRowNum++;}FileOutputStream outputStream = new FileOutputStream(file);workbook.write(outputStream);outputStream.close();Toast.makeText(context, "数据已追加到Excel文件", Toast.LENGTH_SHORT).show();} catch (IOException e) {e.printStackTrace();Log.e("===========", e.toString());}}
}

四，把Excel 里的数据放回数据库

public static void readFromExcelFile(Context context, String filePath) {try {File file = new File(filePath, "test.xlsx");if (file.exists()) {FileInputStream inputStream = new FileInputStream(file);XSSFWorkbook workbook = new XSSFWorkbook(inputStream);Sheet sheet = workbook.getSheet("test");testDao testDao = testDatabase.getDatabaseInstance(context).getLaserMachDao();Iterator<Row> rowIterator = sheet.iterator();// 跳过标题行if (rowIterator.hasNext()) {rowIterator.next();}while (rowIterator.hasNext()) {Row row = rowIterator.next();test test = new test();test.setId((int) row.getCell(0).getNumericCellValue());test.setName(row.getCell(1).getStringCellValue());test.setHandType((int) row.getCell(2).getNumericCellValue());testDao.insert(test);}Toast.makeText(context, "数据已从Excel文件导入到数据库", Toast.LENGTH_SHORT).show();workbook.close();inputStream.close();} else {// 文件不存在的处理逻辑}} catch (IOException e) {e.printStackTrace();Log.e("===========", e.toString());}
}