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

Introduction

Here is the source code for db.pj.util.excel.PenaltyImporter.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.PenaltyDao;
import db.pj.entity.License;
import db.pj.entity.Penalty;
import db.pj.util.IDGenerator;
import db.pj.util.MyDateUtil;

public class PenaltyImporter implements ExcelImporter {
    private File file;

    public PenaltyImporter(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
        LicenseDao licenseDao = DaoFactory.getLicenseDao();
        PenaltyDao penaltyDao = DaoFactory.getPenaltyDao();

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

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

            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 = licenseDao.queryLicenseByUserID(values[0]);

            if (license == null) {
                fail++;
                result.add("" + values[0] + "," + values[1] + "," + values[2]
                        + "");
            } else {

                Penalty penalty = new Penalty();
                penalty.setPnltID(IDGenerator.generateUUID());
                penalty.setPnltType(Integer.parseInt(values[3]));
                penalty.setPnltTime(MyDateUtil.str2Date(values[1]));
                penalty.setPnltInfo(values[2]);
                // set penalty point and limit
                if (penalty.getPnltType() == 5) {
                    penalty.setPnltPoint(Integer.parseInt(values[5]));
                    license.addPnltPoint(penalty.getPnltPoint());
                } else {
                    penalty.setPnltPoint(0);
                    license.setLicnValid(0);
                    if (penalty.getPnltType() == 3)
                        license.setLicnLimit(MyDateUtil.getLimitDate(penalty.getPnltTime(), 100));
                    else
                        license.setLicnLimit(
                                MyDateUtil.getLimitDate(penalty.getPnltTime(), Integer.parseInt(values[4])));
                }
                boolean re1 = licenseDao.updateLicense(license);
                penalty.setLicense(license);
                boolean re2 = penaltyDao.addPenalty(penalty);

                if (re1 && re2)
                    success++;
                else {
                    fail++;
                    result.add("" + values[0] + "," + values[1] + "," + values[2]
                            + "");
                }
            }

        }

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

}