db.pj.util.excel.ApplicationImporter.java Source code

Introduction

Here is the source code for db.pj.util.excel.ApplicationImporter.java

Source

package db.pj.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import db.pj.dao.DaoFactory;
import db.pj.dao.LicenseDao;
import db.pj.dao.UserDao;
import db.pj.entity.License;
import db.pj.entity.User;
import db.pj.util.IDGenerator;
import db.pj.util.MyDateUtil;
import db.pj.util.StringUtil;
import db.pj.util.xml.XMLRepertory;

public class ApplicationImporter implements ExcelImporter {
    private File file;

    public ApplicationImporter(File file) {
        this.file = file;
    }

    public File getFile() {
        return file;
    }

    public void setFile(File file) {
        this.file = file;
    }

    @Override
    public List<String> importExcel() {
        // get workbook
        FileInputStream inputStream = null;
        XSSFWorkbook workbook = null;
        List<String> result = new ArrayList<String>();
        result.add("");
        try {
            inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            result.set(0, "");
            return result;
        }

        int success = 0, fail = 0; // number of tuples
        UserDao userDao = DaoFactory.getUserDao();
        LicenseDao licenseDao = DaoFactory.getLicenseDao();

        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;

        int rows = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < rows; i++) {
            row = sheet.getRow(i);
            if (row == null)
                continue;

            int cells = row.getPhysicalNumberOfCells();
            String[] values = new String[cells];

            // read data to an array of strings
            for (short j = 0; j < cells; j++) {
                cell = row.getCell(j);
                if (cell == null)
                    values[j] = null;
                else {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            double d = cell.getNumericCellValue();
                            Date date = new Date(DateUtil.getJavaDate(d).getTime());
                            values[j] = date.toString();
                        } else {
                            values[j] = (int) (cell.getNumericCellValue()) + "";
                        }
                        break;
                    default:
                        values[j] = cell.getStringCellValue();
                        break;
                    }
                }
            }

            License license = null;
            User user = userDao.queryUserByIC(Integer.parseInt(values[0]), values[1]);
            if (user != null)
                license = licenseDao.queryLicenseByUserID(user.getUserID());

            if (user == null) {
                user = new User();
                user.setUserID(IDGenerator.generateUserID(values[0] + values[1]));
                user.setUserPwd(StringUtil.MD5(user.getUserID()));
                user.setUserName(values[2]);
                user.setUserGender(XMLRepertory.genderStr2Num(values[3]));
                user.setUserIctype(Integer.parseInt(values[0]));
                user.setUserIcno(values[1]);
                user.setUserBdate(MyDateUtil.str2Date(values[4]));
                user.setUserPhone("00000000");
                user.setUserNation(values[5]);
                user.setUserAddr(values[6]);
                user.setUserHead("user/default.png");
                user.setUserCensor(values[9]);
                user.setUserHealth(Integer.parseInt(values[7]));
                user.setUserPdate(MyDateUtil.str2Date(values[8]));
                // check health and age
                boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18)
                        && (user.getAge(user.getUserPdate()) <= 70);
                user.setUserState(ok ? 1 : 0);
                boolean re = userDao.addUser(user);
                if (re)
                    success++;
                else {
                    fail++;
                    String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno();
                    result.add("" + ic + "");
                }
            } else if (license == null) {
                user.setUserCensor(values[9]);
                user.setUserHealth(Integer.parseInt(values[7]));
                user.setUserPdate(MyDateUtil.str2Date(values[8]));
                // check health and age
                boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18)
                        && (user.getAge(user.getUserPdate()) <= 70);
                user.setUserState(ok ? 1 : 0);
                boolean re = userDao.updateUser(user);
                if (re)
                    success++;
                else {
                    fail++;
                    String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno();
                    result.add("" + ic + "");
                }
            } else {
                // user has a license
                if (license.getLicnValid() == 0) {
                    boolean nolimit = license.getLicnVinfo() == 12 && license.getLicnLimit() == null;
                    boolean outlimit = license.getLicnLimit() != null
                            && (license.getLicnLimit().getTime() - MyDateUtil.str2Date(values[8]).getTime() <= 0);
                    if (nolimit || outlimit) {
                        user.setUserCensor(values[9]);
                        user.setUserHealth(Integer.parseInt(values[7]));
                        user.setUserPdate(MyDateUtil.str2Date(values[8]));
                        // check health and age
                        boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18)
                                && (user.getAge(user.getUserPdate()) <= 70);
                        user.setUserState(ok ? 1 : 0);
                        boolean re = userDao.updateUser(user);
                        if (re)
                            success++;
                        else {
                            fail++;
                            String ic = (user.getUserIctype() == 1 ? "" : "")
                                    + user.getUserIcno();
                            result.add("" + ic + "");
                        }
                    } else {
                        fail++;
                        String ic = (user.getUserIctype() == 1 ? "" : "")
                                + user.getUserIcno();
                        result.add(ic + "");
                    }
                } else {
                    fail++;
                    String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno();
                    result.add(ic + "");
                }
            }

        }

        result.set(0, "" + success + "" + fail + "");
        return result;
    }
}