com.mycompany.corevaluecontest.InsertEmployee_Excecl.java Source code

Introduction

Here is the source code for com.mycompany.corevaluecontest.InsertEmployee_Excecl.java

Source

package com.mycompany.corevaluecontest;

import connect.Database;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author C13.207
 */
@ManagedBean(name = "InsertEmployee_Excecl")

public class InsertEmployee_Excecl {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs;
    String SQL_Str;

    public void uploadExcel() {
        try {

            conn = Database.getConnection();

            // Use excel file insert to employee
            FileInputStream input = new FileInputStream("D:\\Excel\\Corevalue\\Employee.xls");
            //System.out.println("+++++++++++++++++");
            POIFSFileSystem fs = new POIFSFileSystem(input);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row;
            HSSFCell id, firstname, lastname, division, section, grp, position;

            int a = 0;
            int b = 2;
            int c = 3;
            int d = 5;
            int e = 6;
            int f = 7;
            int g = 8;
            int as = 0;
            String grpvalue = null;
            int x = sheet.getLastRowNum();
            //System.out.println("+++++++2+++++");
            for (int i = 1; i <= x; i++) {
                row = sheet.getRow(i);

                if (row.getCell(a).toString() != null) {
                    as++;
                    //System.out.println("%%%%%%%%" + as + "//////////" + x);
                }

            }
            System.out.println("++++++3++++++++");
            for (int i = 1; i <= as; i++) {
                row = sheet.getRow(i);

                id = row.getCell(a);

                String str = id.getStringCellValue().toString();
                String ans = str.substring(str.length() - 4, str.length());

                firstname = row.getCell(b);
                String firstvalue = firstname.getStringCellValue().toString();

                lastname = row.getCell(c);
                String lastvalue = lastname.getStringCellValue().toString();

                division = row.getCell(d);
                String divisionvalue = division.getStringCellValue().toString();

                section = row.getCell(e);
                String sectionvalue = section.getStringCellValue().toString();

                if (row.getCell(f) != null) {
                    grp = row.getCell(f);
                    grpvalue = grp.getStringCellValue();
                } else {
                    grpvalue = "";
                }

                position = row.getCell(g);
                String positionvalue = position.getStringCellValue();

                String last2 = lastvalue.substring(0, 1) + "." + firstvalue;

                String sql = "INSERT INTO tblMaster_User (UserID)VALUES('" + ans + "')";

                ps = (PreparedStatement) conn.prepareStatement(sql);
                ps.execute();
                /*
                 SQL_Str = "DELETE FROM PositionMaster";
                 conn = Database.getConnection();
                 ps = (PreparedStatement) conn.prepareStatement(SQL_Str);
                 ps.execute();
                    
                 String sql2 = "INSERT INTO PositionMaster (P_Name) VALUES ('" + positionvalue + "')";
                    
                 ps = (PreparedStatement) conn.prepareStatement(sql2);
                 ps.execute();
                 */
                System.out.println("Import rows " + i);
            }

            //<p:commandButton value="Submitxx" update="@all" icon="ui-icon-check" action="#{InsertEmployee_Excecl.uploadExcel()}"/>
            conn.commit();
            ps.close();
            conn.close();
            input.close();
            System.out.println("Success import excel to mysql table");

        } catch (SQLException ex) {
            System.out.println(ex);
        } catch (IOException ioe) {
            System.out.println(ioe);
        }

    }
}