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

Introduction

Here is the source code for db.pj.util.excel.LicenseImporter.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.AdministrationDao;
import db.pj.dao.DaoFactory;
import db.pj.dao.LicenseDao;
import db.pj.dao.UserDao;
import db.pj.entity.Administration;
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 LicenseImporter implements ExcelImporter {
    private File file;

    public LicenseImporter(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();
        AdministrationDao administrationDao = DaoFactory.getAdministrationDao();

        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;
                    }
                }
            }

            User user = new User();
            user.setUserID(IDGenerator.generateUserID(values[5] + values[6]));
            user.setUserPwd(StringUtil.MD5(user.getUserID()));
            user.setUserName(values[0]);
            user.setUserGender(XMLRepertory.genderStr2Num(values[1]));
            user.setUserIctype(Integer.parseInt(values[5]));
            user.setUserIcno(values[6]);
            user.setUserBdate(MyDateUtil.str2Date(values[2]));
            user.setUserPhone("00000000");
            user.setUserNation(values[3]);
            user.setUserAddr(values[4]);
            user.setUserHead("user/default.png");
            user.setUserHealth(0);
            user.setUserState(5);
            user.setUserPdate(new Date(System.currentTimeMillis()));

            License license = new License();
            license.setLicnID(values[11]);
            license.setLicnType(values[8]);
            license.setLicnGdate(MyDateUtil.str2Date(values[7]));
            license.setLicnVdlen(Integer.parseInt(values[9]));
            // if point>=12, set invalid
            license.setLicnVinfo(Integer.parseInt(values[12]));
            license.setLicnValid((license.getLicnVinfo() < 12) ? 1 : 0);
            // license is not valid, reset health and state
            if (license.getLicnValid() == 0) {
                user.setUserHealth(-1);
                user.setUserState(0);
            }
            user.setLicense(license);
            license.setUser(user);
            // get administration and check whether it is valid
            Administration administration = administrationDao.queryAdministrationByID(values[10]);
            if (administration == null) {
                fail++;
                result.add("" + values[11] + "");
            } else {
                license.setAdministration(administration);
                boolean re1 = userDao.addUser(user);
                boolean re2 = licenseDao.addLicense(license);

                if (re1 || re2)
                    success++;
                else {
                    fail++;
                    result.add("" + values[11] + "");
                }
            }
        }

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

}