public void parseExcelData(String filepath)
throws InvalidFormatException, IOException, ParseException {
Sheet sheet = loadWorksheet(filepath);
Iterator<Row> row_iter = sheet.iterator();
// Check if first row is a header row
if (row_iter.hasNext()) {
Row header_row = row_iter.next();
// if the first cell is numeric,
// there is no header row, reset the iterator
if (header_row.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC)
row_iter = sheet.iterator();
}
while (row_iter.hasNext()) {
InterviewInstance current_instance = getInstance(row_iter.next());
// if no interview is present, start one
if (interviews.isEmpty()) interviews.add(new Interview(current_instance));
// add to the current interview instance in the list
// make a new interview if the interview ID doesn't match up
if (!interviews.get(interviews.size() - 1).add(current_instance))
interviews.add(new Interview(current_instance));
}
}
public void loadBeachLocations() {
try {
FileInputStream file = new FileInputStream(new File("FISHERFOLK TEAM DATA SUMMARY.xls"));
workbook = new HSSFWorkbook(file);
Sheet sheet = workbook.getSheet("BEACH SUMMARY");
Iterator<Row> rowIterator = sheet.iterator();
rowIterator.next(); // Skip the header row.
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
String code = row.getCell(1).getStringCellValue().trim();
System.out.println(code);
if (!code.isEmpty()) {
String beachname = row.getCell(1).getStringCellValue().trim();
String county = row.getCell(2).getStringCellValue().trim();
String description = row.getCell(1).getStringCellValue().trim() + "Beach";
System.out.println(beachname + ":" + county + ":" + description);
Beach beach = new Beach(beachname, description, county);
beach.saveBeach();
}
}
JOptionPane.showMessageDialog(null, "Done Adding Beaches Locations....");
} catch (IOException ex) {
ex.printStackTrace();
Logger.getLogger(Reporting.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void loadData(Data data)
throws EncryptedDocumentException, InvalidFormatException, IOException {
FileInputStream file = new FileInputStream(new File(path));
org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);
org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
try {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
ArrayList<String> arrayRow = new ArrayList<String>();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
testDataType(cell, arrayRow);
}
data.getData().add(arrayRow);
}
} catch (Exception e) {
e.printStackTrace();
}
workbook.close();
file.close();
sheet = null;
workbook = null;
file = null;
}
private static void retrieveSheetInformation(Sheet sheet) {
// every sheet has rows, iterate over them
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
Row row = rowIterator.next();
// Every row has columns, get the column iterator and
// iterate over them
Iterator<Cell> cellIterator = row.cellIterator();
StringBuffer sb = new StringBuffer();
while (cellIterator.hasNext()) {
// Get the Cell object
Cell cell = cellIterator.next();
// check the cell type and process accordingly
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
sb.append(cell.getStringCellValue()).append(" $ ");
break;
case Cell.CELL_TYPE_NUMERIC:
sb.append(cell.getNumericCellValue()).append(" ");
}
} // end of cell iterator
System.out.println(sb.toString());
} // end of rows iterator
}
public List<Specialite> readBooksFromExcelFile(String excelFilePath) throws IOException {
List<Specialite> listSpecialite = new ArrayList<>();
FileInputStream file = new FileInputStream(new File(excelFilePath));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
Specialite sp = new Specialite();
Admin admin = new Admin();
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
int columnIndex = nextCell.getColumnIndex();
switch (columnIndex) {
case 0:
sp.setIntitule((String) getCellValue(nextCell));
break;
}
}
listSpecialite.add(sp);
}
file.close();
return listSpecialite;
}
public <T> List<T> map() throws Throwable {
InputStream inputStream = null;
List<T> items = new LinkedList<>();
try {
Iterator<Row> rowIterator;
inputStream = new FileInputStream(excelFile);
Workbook workbook = createWorkbook(inputStream);
int numberOfSheets = workbook.getNumberOfSheets();
for (int index = 0; index < numberOfSheets; index++) {
Sheet sheet = workbook.getSheetAt(index);
rowIterator = sheet.iterator();
Map<String, Integer> nameIndexMap = new HashMap<>();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
readExcelHeader(row, nameIndexMap);
} else {
items.add((T) readExcelContent(row, nameIndexMap));
}
}
}
} finally {
if (inputStream != null) {
inputStream.close();
}
}
return items;
}
public String parseExcelData(InputStream is) {
try {
System.out.println("--> open workbook");
workbook = new XSSFWorkbook(is);
System.out.println("start get sheet");
// Taking first sheet from the workbook
// XSSFSheet sheet = workbook.getSheetAt(0);
Sheet sheet = workbook.getSheetAt(0);
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
currentString = new StringBuilder();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
bytesRead++;
currentString.append(cell.getBooleanCellValue() + "\t");
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
bytesRead++;
currentString.append(cell.getNumericCellValue() + "\t");
System.out.print(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
bytesRead++;
currentString.append(cell.getStringCellValue() + "\t");
System.out.print(cell.getStringCellValue());
break;
}
}
currentString.append("\n");
}
is.close();
} catch (IOException e) {
LOG.error("IO Exception : File not found " + e);
}
return currentString.toString();
}
@Test
public void test() throws Throwable {
Workbook wb =
WorkbookFactory.create(new File(Resources.getResource("excel/employees.xlsx").getFile()));
Sheet sheet = wb.getSheet("employees");
Iterator<Row> itr = sheet.iterator();
itr.next();
List<Employee> employees = new LinkedList<>();
while (itr.hasNext()) {
employees.add(populate(itr.next()));
}
IOUtils.closeQuietly(wb);
Assert.assertEquals(2, employees.size());
TestUtils.print(employees);
}
private void processSheetRows(Sheet sheet) {
int flag = 0;
boolean exit = false;
Iterator<Row> rows = sheet.iterator();
while (rows.hasNext() && !exit) {
Row row = rows.next();
try {
String name = row.getCell(0).getStringCellValue();
Double val = row.getCell(22).getNumericCellValue();
if (name != null) {
if (name.equals("Актив")) {
if (flag > 0) {
exit = true;
} else {
flag = 1;
}
}
if (name.equals("Пассив")) {
flag = 2;
continue;
}
if (name.contains("баланс")) {
exit = true;
break;
}
}
if (flag == 1 && !name.contains("ИТОГО") && activeCriteria.contains(name)) {
activeMap.put(name, val);
}
if (flag == 2 && !name.contains("ИТОГО") && passiveCriteria.contains(name)) {
passiveMap.put(name, val);
}
} catch (Exception e) {
LOG.warning(e.getMessage());
}
}
}
public static ShippingRequestValueContainerCola parseToContainer() throws Exception {
ShippingRequestValueContainerCola containerCola = new ShippingRequestValueContainerCola();
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(containerCola.getFile_path()));
HSSFWorkbook wb = new HSSFWorkbook(fs);
Sheet sheet1 = wb.getSheetAt(0);
Row row1 = sheet1.getRow(start);
containerCola.setOd(row1.getCell(clmnod).getRichStringCellValue().getString());
System.out.println(containerCola.getOD());
containerCola.setRequestDate(row1.getCell(clmndateLoad).getRichStringCellValue().getString());
System.out.println(containerCola.getRequestDate());
containerCola.setRequestTime(row1.getCell(clmntimeLoad).getRichStringCellValue().getString());
System.out.println(containerCola.getRequestTime());
containerCola.setLoadStoreCode(
row1.getCell(clmnstoreToLoad).getRichStringCellValue().getString());
String codeLoad = containerCola.getLoadStoreCode();
System.out.println(containerCola.getLoadStoreCode());
containerCola.setDateUnload(row1.getCell(clmndateUnload).getRichStringCellValue().getString());
System.out.println(containerCola.getDateUnload());
containerCola.setTimeUnload(row1.getCell(clmntimeUnload).getRichStringCellValue().getString());
System.out.println(containerCola.getTimeUnload());
containerCola.setUnloadStoreCode(
row1.getCell(clmnstoreUnload).getRichStringCellValue().getString());
String codeUnload = containerCola.getUnloadStoreCode();
System.out.println(containerCola.getUnloadStoreCode());
containerCola.setRequestService(
row1.getCell(clmnserviceType).getRichStringCellValue().getString());
System.out.println(containerCola.getRequestService());
containerCola.setVehicleType(
row1.getCell(clmnvehicleType).getRichStringCellValue().getString());
System.out.println(containerCola.getVehicleType());
containerCola.setBodySpace(row1.getCell(clmnbodySpace).getRichStringCellValue().getString());
System.out.println(containerCola.getBodySpace());
containerCola.setLoad(row1.getCell(clmnload).getRichStringCellValue().getString());
System.out.println(containerCola.getLoad());
containerCola.setWeight(row1.getCell(clmnweight).getRichStringCellValue().getString());
System.out.println(containerCola.getWeight());
containerCola.setTripTypes(row1.getCell(clmntripTypes).getRichStringCellValue().getString());
System.out.println(containerCola.getTripTypes());
containerCola.setRegion(row1.getCell(clmnregion).getRichStringCellValue().getString());
System.out.println(containerCola.getRegion());
containerCola.setRequestTotalCargoCost(
row1.getCell(clmncost).getRichStringCellValue().getString());
System.out.println(containerCola.getRequestTotalCargoCost());
containerCola.setComment(row1.getCell(clmncomment).getRichStringCellValue().getString());
System.out.println(containerCola.getComment());
containerCola.setPrice(row1.getCell(clmnprice).getRichStringCellValue().getString());
Sheet sheet = wb.getSheetAt(1);
Iterator<Row> it = sheet.iterator();
while (it.hasNext()) {
Row row = it.next();
Cell code = row.getCell(0);
Cell value1 = row.getCell(1);
if (code.getRichStringCellValue().getString().equals(codeLoad)) {
containerCola.setAdressLoad(value1.getRichStringCellValue().getString());
}
if (code.getRichStringCellValue().getString().equals(codeUnload)) {
containerCola.setAdressUnload(value1.getRichStringCellValue().getString());
}
}
System.out.println("Adress load " + containerCola.getAdressLoad());
System.out.println("Adress Unload " + containerCola.getAdressUnload());
System.out.println("fin");
return containerCola;
}
// sheet Project
private void MapTableProject(Sheet sheet) {
System.out.println("Table : " + sheet.getSheetName());
String tableName = sheet.getSheetName();
if (tableName.equals("Du an")) {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
Project project = new Project();
Row row = rowIterator.next(); //
// Every row has columns, get the column iterator and iterate over
// them
Iterator<Cell> cellIterator = row.cellIterator();
if (row.getRowNum() > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getRow().getRowNum() == 0) {
System.out.println("---- Column name:" + cell.getNumericCellValue());
} else {
if (cell.getColumnIndex() == 2) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("STT " + cell.getRowIndex());
System.out.println("---- Parent Project: " + cell.getNumericCellValue());
if (String.valueOf(cell.getNumericCellValue()) != null) {
project.setParentCode(String.valueOf(cell.getNumericCellValue()));
}
} else {
System.out.println("STT " + cell.getRowIndex());
System.out.println("---- Parent Project: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null) {
project.setParentCode(cell.getStringCellValue());
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 1) {
System.out.println("---- Name Project: " + cell.getStringCellValue());
project.setName(cell.getStringCellValue());
if (project.getName().trim().length() == 0) {
continue;
}
}
if (cell.getColumnIndex() == 0) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
project.setCode(String.valueOf(cell.getNumericCellValue()));
} else {
project.setCode(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 3) {
System.out.println("---- Description: " + cell.getStringCellValue());
project.setDepartmentPart(cell.getStringCellValue());
}
if (cell.getColumnIndex() == 4) {
System.out.println("---- Description: " + cell.getStringCellValue());
project.setDescription(cell.getStringCellValue());
}
}
}
try {
if (checkProject(project)) {
project.setStatus("Đã hoàn thành");
RestaurantModelManager.getInstance().saveProject(project);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
// sheet Ca nhan
private void MapTableUser(Sheet sheet) throws IOException {
System.out.println("Table : " + sheet.getSheetName());
String tableName = sheet.getSheetName();
if (tableName.equals("Ca nhan")) {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
account = new Account();
account.setLoginId(new Date().getTime() + "");
account.setPassword("0000");
account.setType(Type.USER);
account.setLastLoginTime(new Date());
basicInformation = new BasicInformation();
contact = new Contact();
customer = new Customer();
customer.setOrganizationLoginId(ManagerAuthenticate.getInstance().getOrganizationLoginId());
String str = new Date().getTime() + "";
customer.setCode(str);
Row row = rowIterator.next(); //
// Every row has columns, get the column iterator and iterate over
// them
Iterator<Cell> cellIterator = row.cellIterator();
if (row.getRowNum() > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getRow().getRowNum() == 0) {
System.out.println("---- Column name:" + cell.getStringCellValue());
} else {
if (cell.getColumnIndex() == 0) {
// firstName
try {
System.out.println("STT " + cell.getRowIndex());
System.out.println("---- firstName: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
basicInformation.setFirstName(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 1) {
// lastName
try {
System.out.println("---- lastName: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
basicInformation.setLastName(
basicInformation.getFirstName() + " " + cell.getStringCellValue());
customer.setName(basicInformation.getLastName());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 2) {
// birthday
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- birthday " + cell.getNumericCellValue());
basicInformation.setBirthday(String.valueOf(cell.getNumericCellValue()));
} else {
System.out.println("---- birthday " + cell.getStringCellValue());
basicInformation.setBirthday(cell.getStringCellValue().toString());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 3) {
// gender
try {
System.out.println("---- gender: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
basicInformation.setGender(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 4) {
// indentityCard
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- indentityCard: " + cell.getNumericCellValue());
basicInformation.setPersonalId(String.valueOf(cell.getNumericCellValue()));
} else {
System.out.println("---- indentityCard: " + cell.getStringCellValue());
basicInformation.setPersonalId((cell.getStringCellValue()));
}
} catch (Exception e) {
e.printStackTrace();
}
}
// address
if (cell.getColumnIndex() == 5) {
System.out.println("---- address: " + cell.getStringCellValue());
contact.setAddressNumber(cell.getStringCellValue());
}
// telephone
if (cell.getColumnIndex() == 6) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- telephone1: " + cell.getNumericCellValue());
String[] phone = String.valueOf(cell.getNumericCellValue()).split(",");
contact.setPhone(phone);
} else {
System.out.println("---- telephone2: " + cell.getStringCellValue());
String[] phone = cell.getStringCellValue().split(",");
contact.setPhone(phone);
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 7) {
// mobile
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- mobile1: " + cell.getNumericCellValue());
String[] arraymobile = String.valueOf(cell.getNumericCellValue()).split(",");
contact.setMobile(arraymobile);
} else {
System.out.println("---- mobile2: " + cell.getStringCellValue());
String[] arraymobile = cell.getStringCellValue().split(",");
contact.setMobile(arraymobile);
}
} catch (Exception e) {
}
}
// Email
if (cell.getColumnIndex() == 8) {
try {
String string = new Date().getTime() + "";
int str1 = cell.getStringCellValue().length();
String str2 = cell.getStringCellValue();
System.out.println("---- Email: " + cell.getStringCellValue());
if (str1 > 4) {
account.setEmail(str2);
} else {
account.setEmail(string + "@gmail.com");
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Description
if (cell.getColumnIndex() == 9) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- telephone1: " + cell.getNumericCellValue());
String phone = String.valueOf(cell.getNumericCellValue());
customer.setDescription(phone);
} else {
System.out.println("---- telephone2: " + cell.getStringCellValue());
customer.setDescription(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
try {
Profile profileBasic = new Profile();
BasicInformation basic = this.basicInformation;
profileBasic.put(BasicInformation.LAST_NAME, basic.getLastName());
profileBasic.put(BasicInformation.GENDER, basic.getGender());
profileBasic.put(BasicInformation.BIRTHDAY, basic.getBirthday());
profileBasic.put(BasicInformation.PERSONAL_ID, basic.getPersonalId());
Profiles profiles = new Profiles();
profiles.setBasic(profileBasic);
account.setProfiles(profiles);
List<Contact> contacts = new ArrayList<Contact>();
contacts.add(contact);
account.setContacts(contacts);
Account acc = AccountModelManager.getInstance().saveAccount(account);
customer.setLoginId(acc.getLoginId());
customer.setType("Cá nhân");
if (contact.getMobile().length > 0) {
customer.setMobile(contact.getMobile()[0]);
}
try {
customer.setBirthDay(new SimpleDateFormat("dd/MM/yyyy").parse(basic.getBirthday()));
} catch (Exception e) {
}
CustomerModelManager.getInstance().saveCustomer(customer);
} catch (Exception e) {
}
}
}
}
}
// sheet DN
private void MapTableOrganization(Sheet sheet) throws IOException {
System.out.println("Table : " + sheet.getSheetName());
String tableName = sheet.getSheetName();
List<String> list = new ArrayList<String>();
if (tableName.equals("DN")) {
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
account = new Account();
account.setLoginId(new Date().getTime() + "");
account.setPassword("0000");
account.setType(Type.ORGANIZATION);
account.setLastLoginTime(new Date());
organizationBasic = new OrganizationBasic();
contact = new Contact();
customer = new Customer();
customer.setOrganizationLoginId(ManagerAuthenticate.getInstance().getOrganizationLoginId());
Row row = rowIterator.next(); //
// Every row has columns, get the column iterator and iterate over
// them
Iterator<Cell> cellIterator = row.cellIterator();
if (row.getRowNum() > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getRow().getRowNum() == 0) {
System.out.println("---- Column name:" + cell.getStringCellValue());
} else {
if (cell.getColumnIndex() == 0) {
// enterpriseName
try {
System.out.println("STT " + cell.getRowIndex());
System.out.println("---- Enterprise Name: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
customer.setName(cell.getStringCellValue());
organizationBasic.setName(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 1) {
// enterpriseCode
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- Enterprise code: " + cell.getNumericCellValue());
if (list.indexOf(String.valueOf(cell.getNumericCellValue())) > 0) {
customer.setCode(String.valueOf(new Date().getTime()));
} else {
customer.setCode(String.valueOf(cell.getNumericCellValue()));
list.add(customer.getCode());
}
} else {
System.out.println("---- Enterprise code: " + cell.getStringCellValue());
if (list.indexOf(cell.getStringCellValue()) > 0) {
customer.setCode(String.valueOf(new Date().getTime()));
} else {
customer.setCode(cell.getStringCellValue());
list.add(customer.getCode());
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Slogan
if (cell.getColumnIndex() == 2) {
try {
System.out.println("---- Slogan: " + cell.getStringCellValue());
organizationBasic.setSlogan(cell.getStringCellValue());
} catch (Exception e) {
e.printStackTrace();
}
}
// Address
if (cell.getColumnIndex() == 3) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- Address: " + cell.getNumericCellValue());
contact.setAddressNumber(String.valueOf(cell.getNumericCellValue()));
} else {
System.out.println("---- Address: " + cell.getStringCellValue());
contact.setAddressNumber(cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Telephone
if (cell.getColumnIndex() == 4) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- Telephone: " + cell.getNumericCellValue());
String[] arrayPhone = String.valueOf(cell.getNumericCellValue()).split(",");
contact.setPhone(arrayPhone);
if (arrayPhone.length > 0) {
customer.setMobile(arrayPhone[0]);
}
} else {
System.out.println("---- Telephone: " + cell.getStringCellValue());
String[] arrayPhone = cell.getStringCellValue().split(",");
contact.setPhone(arrayPhone);
if (arrayPhone.length > 0) {
customer.setMobile(arrayPhone[0]);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Email
if (cell.getColumnIndex() == 6) {
try {
String str = new Date().getTime() + "";
int str1 = cell.getStringCellValue().length();
String str2 = cell.getStringCellValue();
System.out.println("---- Email: " + cell.getStringCellValue());
if (str1 > 4) {
account.setEmail(str2);
} else {
account.setEmail(str + "@gmail.com");
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Website
if (cell.getColumnIndex() == 7) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
customer.setDescription(String.valueOf(cell.getNumericCellValue()));
} else {
if (cell.getStringCellValue() != null) {
customer.setDescription(cell.getStringCellValue());
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Description
if (cell.getColumnIndex() == 8) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println("---- Description: " + cell.getNumericCellValue());
organizationBasic.setDescription(String.valueOf(cell.getNumericCellValue()));
} else {
System.out.println("---- Description: " + cell.getStringCellValue());
if (cell.getStringCellValue() != null) {
if (cell.getStringCellValue().length() > 100) {
organizationBasic.setDescription(
cell.getStringCellValue().substring(0, 100));
} else {
organizationBasic.setDescription(cell.getStringCellValue());
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
try {
if (checkAccount(account)) {
Profile profileOrgBasic = new Profile();
OrganizationBasic orgBasic = this.organizationBasic;
profileOrgBasic.put(OrganizationBasic.NAME, orgBasic.getName());
profileOrgBasic.put(OrganizationBasic.SLOGAN, orgBasic.getSlogan());
profileOrgBasic.put(OrganizationBasic.DESCRIPTION, orgBasic.getDescription());
Profiles profiles = new Profiles();
profiles.setBasic(profileOrgBasic);
account.setProfiles(profiles);
List<Contact> contacts = new ArrayList<Contact>();
contacts.add(contact);
account.setContacts(contacts);
Account acc = AccountModelManager.getInstance().saveAccount(account);
customer.setLoginId(acc.getLoginId());
customer.setType("Doanh nghiệp");
customer.setAddress(contact.getAddressNumber());
CustomerModelManager.getInstance().saveCustomer(customer);
}
} catch (Exception e) {
}
}
}
}
}
// sheet Invoice
private void MapTableInvoice(Sheet sheet) {
System.out.println("Table : " + sheet.getSheetName());
String tableName = sheet.getSheetName();
if (tableName.equals("invoice")) {
try {
AccountGroup acc1 = new AccountGroup();
acc1.setName("Hành Chính");
acc1.setLabel("Hành Chính");
acc1.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
acc1.setParent(HRModelManager.getInstance().getRootDepartment());
AccountModelManager.getInstance().saveGroup(acc1);
AccountGroup acc2 = new AccountGroup();
acc2.setName("Hội Đồng Quản Trị");
acc2.setLabel("Hội Đồng Quản Trị");
acc2.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
acc2.setParent(HRModelManager.getInstance().getRootDepartment());
AccountModelManager.getInstance().saveGroup(acc2);
AccountGroup acc3 = new AccountGroup();
acc3.setName("Kinh Doanh");
acc3.setLabel("Kinh Doanh");
acc3.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
acc3.setParent(HRModelManager.getInstance().getRootDepartment());
AccountModelManager.getInstance().saveGroup(acc3);
AccountGroup acc4 = new AccountGroup();
acc4.setName("Ban Lãnh Đạo");
acc4.setLabel("Ban Lãnh Đạo");
acc4.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
acc4.setParent(HRModelManager.getInstance().getRootDepartment());
AccountModelManager.getInstance().saveGroup(acc4);
AccountGroup acc5 = new AccountGroup();
acc5.setName("Nghiên Cứu");
acc5.setLabel("Nghiên Cứu");
acc5.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
acc5.setParent(HRModelManager.getInstance().getRootDepartment());
AccountModelManager.getInstance().saveGroup(acc5);
} catch (Exception e1) {
e1.printStackTrace();
}
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Get the row object
InvoiceDetail invoicedetail = new InvoiceDetail();
invoicedetail.setType(AccountingModelManager.typeThuChi);
invoicedetail.setStatus(Status.Paid);
invoicedetail.setCurrencyUnit("VND");
invoicedetail.setCurrencyRate(1);
Row row = rowIterator.next(); //
// Every row has columns, get the column iterator and iterate over
// them
Iterator<Cell> cellIterator = row.cellIterator();
if (row.getRowNum() > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getRow().getRowNum() == 0) {
System.out.println("---- Column name:" + cell.getNumericCellValue());
} else {
if (cell.getColumnIndex() == 0) {
// Tên phòng
try {
// System.out.println("STT " + cell.getRowIndex());
// System.out.println("---- Accountgroup name: " +
// cell.getStringCellValue());
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// AccountGroup accountGroup =
// AccountModelManager.getInstance().getGroupByName(String.valueOf(cell.getNumericCellValue()));
// System.out.println("---- aaaaaaaaaaaa:" +
// cell.getNumericCellValue()+" "+accountGroup.getPath());
String path = "hkt/Phòng ban/" + String.valueOf(cell.getNumericCellValue());
invoicedetail.setDepartmentCode(path);
} else {
// AccountGroup accountGroup =
// AccountModelManager.getInstance().getGroupByName(cell.getStringCellValue());
// System.out.println("---- aaaaaaaaaaaa:" +
// cell.getStringCellValue()+" "+accountGroup.getPath());
String path = "hkt/Phòng ban/" + cell.getStringCellValue();
invoicedetail.setDepartmentCode(path);
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (cell.getColumnIndex() == 1) {
// Mã dự án
try {
if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
// System.out.println("---- Project code: " +
// cell.getStringCellValue());
invoicedetail.setProjectCode("/" + cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Operation Name
if (cell.getColumnIndex() == 2) {
try {
System.out.println("---- Operation Name: " + cell.getStringCellValue());
if (cell.getStringCellValue().length() > 100) {
invoicedetail.setInvoiceName(cell.getStringCellValue().substring(0, 100));
} else {
invoicedetail.setInvoiceName(cell.getStringCellValue());
}
if (invoicedetail.getInvoiceName().trim().length() == 0) {
continue;
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Operation code
if (cell.getColumnIndex() == 3) {
try {
String str = DateUtil.asCompactDateId(new Date()) + ":";
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// System.out.println("---- Operation Code: " +
// cell.getNumericCellValue());
invoicedetail.setInvoiceCode(
"TC" + str + "TC" + String.valueOf(cell.getNumericCellValue()));
} else {
// System.out.println("---- Operation Code2: " +
// cell.getStringCellValue());
invoicedetail.setInvoiceCode("TC" + str + "TC" + cell.getStringCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
// description
if (cell.getColumnIndex() == 4) {
// System.out.println("---- Description: " +
// cell.getStringCellValue());
// invoicedetail.setDescription(String.valueOf(cell.getStringCellValue()));
}
// idOperationType
if (cell.getColumnIndex() == 5) {
try {
// System.out.println("---- ActivityType: " +
// cell.getStringCellValue());
if (cell.getStringCellValue().equals("5")) {
invoicedetail.setActivityType(ActivityType.Receipt);
} else if (cell.getStringCellValue().equals("6")) {
invoicedetail.setActivityType(ActivityType.Payment);
} else {
invoicedetail.setActivityType(ActivityType.Payment);
invoicedetail.setType(AccountingModelManager.typeSanXuat);
}
} catch (Exception e) {
e.printStackTrace();
}
}
// TotalBeforeDiscount
if (cell.getColumnIndex() == 6) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// System.out.println("---- Total: " +
// cell.getNumericCellValue());
invoicedetail.setTotal(cell.getNumericCellValue());
} else {
// System.out.println("---- Total: " +
// cell.getStringCellValue());
invoicedetail.setTotal(Double.parseDouble(cell.getStringCellValue()));
}
} catch (Exception e) {
e.printStackTrace();
}
}
// PercenDiscount
if (cell.getColumnIndex() == 7) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// System.out.println("---- Percent Discount: " +
// cell.getNumericCellValue());
invoicedetail.setDiscountRate(cell.getNumericCellValue());
} else {
// System.out.println("---- Percent Discount: " +
// cell.getStringCellValue());
invoicedetail.setDiscountRate(Double.parseDouble(cell.getStringCellValue()));
}
} catch (Exception e) {
e.printStackTrace();
}
}
// MoneyDiscount
if (cell.getColumnIndex() == 8) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// System.out.println("---- MoneyDiscount: " +
// cell.getNumericCellValue());
invoicedetail.setDiscount(cell.getNumericCellValue());
} else {
// System.out.println("---- MoneyDiscount: " +
// cell.getStringCellValue());
invoicedetail.setDiscount(Double.parseDouble(cell.getStringCellValue()));
}
} catch (Exception e) {
e.printStackTrace();
}
}
// MoneyTax
if (cell.getColumnIndex() == 9) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// System.out.println("---- Total Money After Tax " +
// cell.getNumericCellValue());
invoicedetail.setTotalTax(cell.getNumericCellValue());
} else {
// System.out.println("---- Total Money After Tax " +
// cell.getStringCellValue());
invoicedetail.setTotalTax(Double.parseDouble(cell.getStringCellValue()));
}
} catch (Exception e) {
e.printStackTrace();
}
}
// TotalMoneyAfterDiscount
if (cell.getColumnIndex() == 10) {}
// TotalMoneyAfterTax
if (cell.getColumnIndex() == 11) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// System.out.println("---- Total Money After tax " +
// cell.getNumericCellValue());
invoicedetail.setFinalCharge(cell.getNumericCellValue());
} else {
// System.out.println("---- Total Money After tax " +
// cell.getStringCellValue());
invoicedetail.setFinalCharge(Double.parseDouble(cell.getStringCellValue()));
}
} catch (Exception e) {
e.printStackTrace();
}
}
// DateExcute
if (cell.getColumnIndex() == 12) {
try {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// System.out.println("---- Date Execute " +
// cell.getNumericCellValue());
Date startDate = df.parse(String.valueOf(cell.getNumericCellValue()));
// System.out.println(startDate);
invoicedetail.setStartDate(startDate);
invoicedetail.setEndDate(startDate);
} else {
Date startDate = df.parse(cell.getStringCellValue());
// System.out.println(startDate);
invoicedetail.setStartDate(startDate);
invoicedetail.setEndDate(startDate);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
try {
if (checkInvoiDetail(invoicedetail)) {
// invoicedetail.calculate(new DefaultInvoiceCalculator());
invoicedetail.setTotalPaid(invoicedetail.getFinalCharge());
InvoiceTransaction transactions = new InvoiceTransaction();
transactions.setTransactionType(TransactionType.CreditCard);
transactions.setDepartmentCode(invoicedetail.getDepartmentCode());
transactions.setLocationCode(invoicedetail.getLocationCode());
transactions.setTableCode(invoicedetail.getTableCode());
transactions.setCustomerCode(invoicedetail.getCustomerCode());
transactions.setProjectCode(invoicedetail.getProjectCode());
transactions.setCreatedBy(invoicedetail.getDepartmentCode());
transactions.setCurrencyRate(1);
transactions.setCurrencyUnit(invoicedetail.getCurrencyUnit());
transactions.setTotal(invoicedetail.getFinalCharge());
transactions.setTransactionDate(invoicedetail.getStartDate());
if (invoicedetail.getActivityType().equals(ActivityType.Receipt)) {
transactions.setActivityType(InvoiceTransaction.ActivityType.Receipt);
} else if (invoicedetail.getActivityType().equals(ActivityType.Payment)) {
transactions.setActivityType(InvoiceTransaction.ActivityType.Payment);
} else {
transactions.setActivityType(null);
}
invoicedetail.add(transactions);
invoicedetail.setLocationCode("other");
invoicedetail.setTableCode("other");
invoicedetail.setCustomerCode("hkt/Khách hàng/groupCustomer-other");
// if
// (invoicedetail.getType().equals(AccountingModelManager.typeSanXuat))
// {
AccountingModelManager.getInstance().saveInvoice(invoicedetail);
// }
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
private static void load() {
List studentList = new ArrayList();
String FILE_PATH =
"C:/Users/g656736/Desktop/Workspaces/Training/Mukundh/Sample/Resources/Input.xlsx";
FileInputStream fis = null;
String tempKey = "";
String tempValue = "";
int cellCount = 0;
try {
fis = new FileInputStream(FILE_PATH);
// Using XSSF for xlsx format, for xls use HSSF
Workbook workbook = new XSSFWorkbook(fis);
int numberOfSheets = workbook.getNumberOfSheets();
// looping over each workbook sheet
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
Iterator rowIterator = sheet.iterator();
// iterating over each row
while (rowIterator.hasNext()) {
Row row = (Row) rowIterator.next();
Iterator cellIterator = row.cellIterator();
// Iterating over each cell (column wise) in a particular row.
while (cellIterator.hasNext()) {
Cell cell = (Cell) cellIterator.next();
// The Cell Containing String will is name.
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (cellCount == 0)
tempKey = new Integer(new Double(cell.getNumericCellValue()).intValue()).toString();
else tempValue = cell.getStringCellValue();
cellCount++;
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
if (cellCount == 0) tempKey = cell.getStringCellValue();
else tempValue = cell.getStringCellValue();
cellCount++;
// The Cell Containing numeric value will contain marks
}
}
cellCount = 0;
dataMap.put(tempKey, tempValue);
}
// end iterating a row, add all the elements of a row in list
}
fis.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
void excelCopy() {
FileInputStream file1;
int lastRowUpdated = 0, currentRow = 0;
int i = 0;
try {
file1 =
new FileInputStream(
"C:\\Users\\SharvaP\\Desktop\\Excel Format\\LookUp for Transaction Name.xls");
int columnIndex = 0;
Workbook lookupWorkbook = new HSSFWorkbook(file1);
Sheet lookupSheet = lookupWorkbook.getSheetAt(0);
FileInputStream file2 =
new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round1_temp.xls");
Workbook round1Workbook = new HSSFWorkbook(file2);
Sheet round1Sheet = round1Workbook.getSheetAt(0);
FileInputStream file3 =
new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\template.xls");
Workbook templateWorkbook = new HSSFWorkbook(file3);
Sheet templateSheet = templateWorkbook.getSheetAt(0);
FileInputStream file4 =
new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round2_temp.xls");
Workbook round2Workbook = new HSSFWorkbook(file4);
Sheet round2Sheet = round2Workbook.getSheetAt(0);
int averageRow = round1Sheet.getPhysicalNumberOfRows() - 2;
int percentileRow = round1Sheet.getPhysicalNumberOfRows() - 1;
System.out.println("Average row1:" + averageRow);
int averageRow2 = round2Sheet.getPhysicalNumberOfRows() - 2;
int percentileRow2 = round2Sheet.getPhysicalNumberOfRows() - 1;
/*Row round1Row = round1Sheet.getRow(averageRow);
Row round2Row = round2Sheet.getRow(averageRow2);
Cell tempRound1Cell=round1Row.getCell(columnIndex);
Cell tempRound2Cell=round2Row.getCell(columnIndex);
CellStyle cellStyle = templateWorkbook.createCellStyle();
CreationHelper createHelper=templateWorkbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
*/
Iterator<Row> rowIterator1 = lookupSheet.iterator();
while (rowIterator1.hasNext()) {
Row lookupRow = rowIterator1.next();
if (lookupRow.getRowNum() > 0) {
Cell lookupCell = lookupRow.getCell(1);
String transationNameTemplate = lookupCell.getStringCellValue();
// System.out.println("NAme:"+transationNameTemplate);
// System.out.println(templateSheet.getPhysicalNumberOfRows());
for (int count = lastRowUpdated;
count < templateSheet.getPhysicalNumberOfRows() - 1;
count++) {
Row templateRow = templateSheet.getRow(count);
Cell templateCell = templateRow.getCell(0);
Cell templateCellFormulae1 = templateRow.getCell(5);
Cell templateCellFormulae2 = templateRow.getCell(6);
String templateCellName = templateCell.getStringCellValue();
// System.out.println("template cell name:"+templateCellName);
currentRow = templateRow.getRowNum();
if (templateCellName.equals(transationNameTemplate)) {
// System.out.println("Transac Name:"+templateCellName+", row num:"+currentRow+", last
// updated row:"+lastRowUpdated);
Row round1RowAverage = round1Sheet.getRow(averageRow);
Cell round1CellAverage = round1RowAverage.getCell(columnIndex);
Row round2RowAverage = round2Sheet.getRow(averageRow2);
Cell round2CellAverage = round2RowAverage.getCell(columnIndex);
Cell templateCellRound1Average = templateRow.getCell(1);
Cell templateCellRound2Average = templateRow.getCell(3);
templateCellFormulae1.setCellFormula(
"IF(B"
+ (currentRow + 1)
+ ">D"
+ (currentRow + 1)
+ ",((B"
+ (currentRow + 1)
+ "-D"
+ (currentRow + 1)
+ ")/B"
+ (currentRow + 1)
+ ")*100,((B"
+ (currentRow + 1)
+ "-D"
+ (currentRow + 1)
+ ")/D"
+ (currentRow + 1)
+ ")*100)");
System.out.println(round1CellAverage.getNumericCellValue());
templateCellRound1Average.setCellValue(round1CellAverage.getNumericCellValue());
templateCellRound2Average.setCellValue(round2CellAverage.getNumericCellValue());
Row round1RowPercentile = round1Sheet.getRow(percentileRow);
Cell round1CellPercentile = round1RowPercentile.getCell(columnIndex);
Row round2RowPercentile = round2Sheet.getRow(percentileRow2);
Cell round2CellPercentile = round2RowPercentile.getCell(columnIndex);
Cell templateCellRound1Percentile = templateRow.getCell(2);
Cell templateCellRound2Percentile = templateRow.getCell(4);
templateCellFormulae2.setCellFormula(
"IF(C"
+ (currentRow + 1)
+ ">E"
+ (currentRow + 1)
+ ",((C"
+ (currentRow + 1)
+ "-E"
+ (currentRow + 1)
+ ")/C"
+ (currentRow + 1)
+ ")*100,((C"
+ (currentRow + 1)
+ "-E"
+ (currentRow + 1)
+ ")/E"
+ (currentRow + 1)
+ ")*100)");
templateCellRound1Percentile.setCellValue(round1CellPercentile.getNumericCellValue());
templateCellRound2Percentile.setCellValue(round2CellPercentile.getNumericCellValue());
// System.out.println("round1 avg value:"+round1CellAverage.getNumericCellValue());
// System.out.println("round2 avg value:"+round2CellAverage.getNumericCellValue());
// System.out.println("Match Found"+templateRow.getCell(1).getNumericCellValue());
columnIndex++;
lastRowUpdated = currentRow;
break;
}
}
}
}
FileOutputStream out =
new FileOutputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\Template.xls");
templateWorkbook.write(out);
out.close();
System.out.println("Finished....");
file1.close();
file2.close();
file3.close();
file4.close();
} catch (FileNotFoundException e) {
System.out.println(e.getMessage());
e.printStackTrace();
} catch (IOException e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
