package com.basic.security.utils;
|
|
import android.content.ContextWrapper;
|
import android.os.SystemClock;
|
import android.text.TextUtils;
|
|
import com.basic.security.base.BaseApplication;
|
import com.basic.security.manager.IdentityManager;
|
import com.basic.security.manager.OrgManager;
|
import com.basic.security.manager.PersonIdentityManager;
|
import com.basic.security.manager.PersonManager;
|
import com.basic.security.manager.TimeManager;
|
import com.basic.security.model.Identity;
|
import com.basic.security.model.ModelAdapter;
|
import com.basic.security.model.Org;
|
import com.basic.security.model.Person;
|
import com.basic.security.model.Time;
|
|
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellValue;
|
import org.apache.poi.ss.usermodel.FormulaEvaluator;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.FileNotFoundException;
|
import java.io.IOException;
|
import java.text.DecimalFormat;
|
import java.text.SimpleDateFormat;
|
import java.util.HashMap;
|
import java.util.Map;
|
|
public class ExcelUtil {
|
static String cellData;
|
static FormulaEvaluator formulaEvaluator;
|
static Row row;
|
static DecimalFormat decimalFormat = new DecimalFormat("###.#");
|
|
private static String getCellAsString(int columnIndex) {
|
String value = "";
|
try {
|
Cell cell = row.getCell(columnIndex);
|
CellValue cellValue = formulaEvaluator.evaluate(cell);
|
switch (cellValue.getCellType()) {
|
case Cell.CELL_TYPE_BOOLEAN:
|
value = "" + cellValue.getBooleanValue();
|
break;
|
case Cell.CELL_TYPE_NUMERIC:
|
double numericValue = cellValue.getNumberValue();
|
if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
double date = cellValue.getNumberValue();
|
SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yy");
|
value = formatter.format(HSSFDateUtil.getJavaDate(date));
|
} else {
|
value = "" + decimalFormat.format(numericValue);
|
}
|
break;
|
case Cell.CELL_TYPE_STRING:
|
value = "" + cellValue.getStringValue();
|
break;
|
default:
|
}
|
} catch (NullPointerException e) {
|
// ToastUtil.show("Null Pointer Exception in getCellAsString");
|
e.printStackTrace();
|
}
|
return value;
|
}
|
|
public static void parseExcel(File excelFile, Map<String, File> photoFileMap) {
|
FaceId.instance.initSdk(new ContextWrapper(BaseApplication.getApplication().activity).getFilesDir().getAbsolutePath());
|
String rootOrgId = OrgManager.findRootOrg().getString(Org.id);
|
if (excelFile != null) {
|
try {
|
FileInputStream inputStream = new FileInputStream(excelFile.getAbsolutePath());
|
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
|
XSSFSheet sheet = workbook.getSheetAt(0);
|
int rowCount = sheet.getPhysicalNumberOfRows();
|
formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
|
Map<String, ModelAdapter> namesOrgMap = new HashMap<>();
|
for (int r = 1; r < rowCount; r++) {
|
try {
|
row = sheet.getRow(r);
|
int colCount = row.getPhysicalNumberOfCells();
|
String photoId = getCellAsString(0);
|
File photoFile = photoFileMap.get(photoId);
|
if (photoFile == null) {
|
continue;
|
}
|
photoFileMap.remove(photoId);
|
ModelAdapter savedPerson = PersonManager.savePicToDatabase(photoFile, rootOrgId);
|
if (savedPerson != null) {
|
ModelAdapter personOrg;
|
String personName = getCellAsString(1);
|
savedPerson.setString(Person.name, personName);
|
String schoolName = getCellAsString(2);
|
ModelAdapter schoolOrg = OrgManager.buildParentOrg(schoolName);
|
personOrg = schoolOrg;
|
String gradeName = getCellAsString(3);
|
if (!TextUtils.isEmpty(gradeName)) {
|
ModelAdapter gradeOrg = OrgManager.addChildOrg(schoolOrg, gradeName);
|
personOrg = gradeOrg;
|
String className = getCellAsString(4);
|
if (!TextUtils.isEmpty(className)) {
|
personOrg = OrgManager.addChildOrg(gradeOrg, className);
|
}
|
}
|
savedPerson.setString(Person.org_id, personOrg.getString(Org.id));
|
savedPerson.setString(Person.org_ids, personOrg.getString(Org.ids));
|
String studentId = getCellAsString(5);
|
savedPerson.setString(Person.student_id, studentId);
|
String id_card = getCellAsString(6);
|
savedPerson.setString(Person.id_card_number, id_card);
|
String phone = getCellAsString(7);
|
savedPerson.setString(Person.phone, phone);
|
String identity = getCellAsString(8);
|
if (identity.contains(",")) {
|
String[] identityArray = identity.split(",");
|
for (String identityStr : identityArray) {
|
if (!TextUtils.isEmpty(identityStr)) {
|
ModelAdapter identityModel = IdentityManager.findIdentityByName(identityStr);
|
PersonIdentityManager.save(savedPerson.getString(Person.id), identityModel.getString(Identity.id));
|
}
|
}
|
} else {
|
if (!TextUtils.isEmpty(identity)) {
|
ModelAdapter identityModel = IdentityManager.findIdentityByName(identity);
|
PersonIdentityManager.save(savedPerson.getString(Person.id), identityModel.getString(Identity.id));
|
}
|
}
|
String signUpRule = getCellAsString(9);
|
savedPerson.setString(Person.sign_up_rule, signUpRule);
|
if ("有效时间内通行".equals(signUpRule)) {
|
boolean hasCheckedTimeRuleId = false;
|
String timeRuleName = getCellAsString(10);
|
if (!TextUtils.isEmpty(timeRuleName)) {
|
ModelAdapter time = TimeManager.findByTimeRuleName(timeRuleName);
|
if (time != null) {
|
String checked_time_rule_id = time.getString(Time.id);
|
if (!TextUtils.isEmpty(checked_time_rule_id)) {
|
hasCheckedTimeRuleId = true;
|
savedPerson.setString(Person.checked_time_rule_id, checked_time_rule_id);
|
}
|
}
|
}
|
if (!hasCheckedTimeRuleId) {
|
ToastUtil.show("请添加名称为 " + timeRuleName + " 的时间规则");
|
}
|
}
|
savedPerson.setString(Person.sign_up_method, "批量导入");
|
savedPerson.setString(Person.all_device, "1");
|
PersonManager.save(savedPerson);
|
SocketUtil.rpcCallSavePerson(savedPerson);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
workbook.close();
|
OrgManager.initTreeNodeList = false;
|
OrgManager.initTreeNodeList();
|
SocketUtil.rpcCallInitTreeNodeList();
|
} catch (FileNotFoundException e) {
|
ToastUtil.show("File Not Found");
|
} catch (IOException e) {
|
ToastUtil.show("IO Error reading input stream");
|
}
|
}
|
for (File photoFile : photoFileMap.values()) {
|
try {
|
ModelAdapter person = PersonManager.savePicToDatabase(photoFile, rootOrgId);
|
SocketUtil.rpcCallSavePerson(person);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
long begin = System.currentTimeMillis();
|
while (!OrgManager.initTreeNodeList) {
|
SystemClock.sleep(1000);
|
if (System.currentTimeMillis() - begin > 5 * 1000) {
|
break;
|
}
|
}
|
ToastUtil.show("导入成功");
|
BaseApplication.getApplication().activity.runOnUiThread(() -> BaseApplication.getApplication().activity.fragment_face_list.signed_up());
|
}
|
|
public static void main(String[] args) {
|
String names = "aaaa,,,";
|
String[] nameArray = names.split(",,,");
|
System.out.println("ExcelUtil.main " + names.split("|||").length);
|
// ExcelUtil.parseExcel("E:\\workspace\\Security_excel\\app\\src\\main\\assets\\person.xlsx");
|
}
|
}
|
