As soon as you add dependencies (be sure to use new versions of dependencies to prevent some bugs)
minSdk= 26 //Note that the minimum supported 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'
}
Second, creation method
private void createExcelFile(String Path) {<!-- -->
//Create workbook
Workbook workbook = new XSSFWorkbook();
//Create worksheet
Sheet sheet = workbook.createSheet("name");
// create row
/* Row row = sheet.createRow(0);
//Create cell
for (int i = 0; i <10; i + + ) {
Cell cell = row.createCell(i);
//Set the cell value
cell.setCellValue("Fengfeng");
}*/
ArrayList<Map<Integer,Object>> arrayList = new ArrayList<>();
Map<Integer,Object> m = new HashMap<>();
m.put(0,"Material ID");
m.put(1,"Material code");
m.put(2,"name");
m.put(3,"number");
m.put(4,"Specification");
m.put(5,"unit");
m.put(6,"unit price");
m.put(7,"quantity");
m.put(8,"Manufacturer");
m.put(9,"category");
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));
}
}
//Save Excel file
try {<!-- -->
File file = new File(Path, "example.xlsx");
FileOutputStream outputStream = new FileOutputStream(file);
workbook.write(outputStream);
outputStream.close();
Toast.makeText(this, "Excel file has been created", Toast.LENGTH_SHORT).show();
} catch (IOException e) {<!-- -->
e.printStackTrace();
}
}
Three, if there is a way to append tables (the room database is imported here)
public class DbConvertExcel {<!-- -->
public static void appendToExcelFile(Context context, String filePath) {<!-- -->
// worksheet name
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(); // Close OPCPackage*/
FileInputStream inputStream = new FileInputStream(file);
workbook = new XSSFWorkbook(inputStream);
sheet = workbook.getSheet(sheetName);
} else {<!-- -->
workbook = new XSSFWorkbook();
sheet = workbook.createSheet(sheetName);
//Add title row
Row titleRow = sheet.createRow(0);
titleRow.createCell(0).setCellValue("number");
titleRow.createCell(1).setCellValue("name");
titleRow.createCell(2).setCellValue("type");
}
int lastRowNum = sheet.getLastRowNum();
List<test> all = testDao.getAll();
for (test test: all) {<!-- -->
Row row = sheet.createRow(lastRowNum + 1); // Start writing from the next row of the last row
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, "Data has been appended to Excel file", Toast.LENGTH_SHORT).show();
} catch (IOException e) {<!-- -->
e.printStackTrace();
Log.e("===========", e.toString());
}
}
}
Four, put the data in Excel back into the database
public static void readFromExcelFile(Context context, String filePath) {<!-- -->
try {<!-- -->
File file = new File(filePath, "test.xlsx");
if (file.exists()) {<!-- -->
FileInputStream inputStream = new FileInputStream(file);
//HSSFWorkbook workbook = new HSSFWorkbook(inputStream); //xls
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);//xlsx
Sheet sheet = workbook.getSheet("test");
testDao testDao = testDatabase.getDatabaseInstance(context).getLaserMachDao();
Iterator<Row> rowIterator = sheet.iterator();
// skip title row
if (rowIterator.hasNext()) {<!-- -->
rowIterator.next();
}
while (rowIterator.hasNext()) {<!-- -->
Row row = rowIterator.next();
//Determine the data type in exl
for (int i = 0; i < row.getLastCellNum(); i + + ) {<!-- -->
Cell cell = row.getCell(i); // Get the current cell
if (cell.getCellType() == CellType.STRING) {<!-- -->
String cellValue = cell.getStringCellValue();
Log.e("===========", "readFromExcelFile:1111 " + cellValue);
} else if (cell.getCellType() == CellType.NUMERIC) {<!-- -->
double cellValue = cell.getNumericCellValue();
Log.e("===========", "readFromExcelFile:2222 " + cellValue);
}
}*/
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, "Data has been imported from Excel file to database", Toast.LENGTH_SHORT).show();
workbook.close();
inputStream.close();
} else {<!-- -->
// Processing logic for file that does not exist
}
} catch (IOException e) {<!-- -->
e.printStackTrace();
Log.e("===========", e.toString());
}
}