Excel Data Extract

By | October 25th 2019 08:53:41 PM | viewed 36 times
package com.allClass.ukmExcel;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

public class UkmExcel {


    public ArrayList importExcel(FileInputStream inputStream, String fileExt) {

        ArrayList allData;
        allData = new ArrayList();

        if(fileExt.equals(".xls")){
            allData = this.processXl(inputStream);
        }else if(fileExt.equals(".xlsx")){
            allData = this.processXlx(inputStream);
        }else{
            allData = this.processXl(inputStream);
        }

         return allData;


    }

    public ArrayList importExcelProdDesc(FileInputStream inputStream,String fileExt) {

        ArrayList allData = new ArrayList();

        if(fileExt.equals(".xls")){
            allData = this.processXlProdDesc(inputStream);
        }else if(fileExt.equals(".xlsx")){
            allData = this.processXlxProdDesc(inputStream);
        }else{
            allData = this.processXlxProdDesc(inputStream);
        }

        return allData;


    }

   
    public ArrayList processXl(FileInputStream inputStream) {

        ArrayList allData = new ArrayList();
        Map excelHead = new HashMap();



        try{

            HSSFWorkbook wb = new HSSFWorkbook(inputStream);
            HSSFSheet sheet=wb.getSheetAt(0);

            FormulaEvaluator formulaEvaluator=wb.getCreationHelper().createFormulaEvaluator();

            int i = 0;
            for( Row row: sheet)     //iteration over row using for each loop
            {

                Map excelData = new HashMap();
                for(Cell cell: row)    //iteration over cell using for each loop
                {
                    //System.out.println(cell.getRowIndex()+"-"+cell.getColumnIndex());
                    if(cell.getRowIndex()==0){
                        excelHead.put(cell.getColumnIndex(),cell.getStringCellValue());
                    }

                    if(cell.getRowIndex()!=0){
                        switch(formulaEvaluator.evaluateInCell(cell).getCellType())
                        {
                            case Cell.CELL_TYPE_NUMERIC:   //field that represents numeric cell type

                                //System.out.print(cell.getColumnIndex()+"->"+cell.getNumericCellValue()+ "\t\t");
                                //excelData.put(excelHead.get(cell.getColumnIndex()),cell.getNumericCellValue());
                                excelData.put(excelHead.get(cell.getColumnIndex())+"_int",cell.getNumericCellValue());
                                break;
                            case Cell.CELL_TYPE_STRING:    //field that represents string cell type

                                //System.out.print(cell.getColumnIndex()+"->"+cell.getStringCellValue()+ "\t\t");
                                excelData.put(excelHead.get(cell.getColumnIndex()),cell.getStringCellValue());
                                break;
                        }


                    }



                }
                if(i != 0){
                    allData.add(excelData);
                }

                i++;
            }  // end main loop


        }catch(Exception e){
            System.out.println(e.fillInStackTrace());
        }

        return allData;
    }



    public ArrayList processXlx(FileInputStream inputStream) {

        ArrayList allData = new ArrayList();
        Map excelHead = new HashMap();

        try{

            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = wb.getSheetAt(0);     //creating a Sheet object to retrieve object
            Iterator itr = sheet.iterator();    //iterating over excel file

            int i = 0;
            while (itr.hasNext())
            {

                Map excelData = new HashMap();

                Row row = itr.next();
                Iterator cellIterator = row.cellIterator();   //iterating over each column

                while (cellIterator.hasNext())
                {
                    Cell cell = cellIterator.next();
                    //System.out.println(cell.getRowIndex()+"-"+cell.getColumnIndex());
                    if(cell.getRowIndex()==0){
                        excelHead.put(cell.getColumnIndex(),cell.getStringCellValue());
                    }

                    if(cell.getRowIndex()!=0){
                        switch(cell.getCellType())
                        {
                            case Cell.CELL_TYPE_NUMERIC:   //field that represents numeric cell type

                                //System.out.print(cell.getColumnIndex()+"->"+cell.getNumericCellValue()+ "\t\t");
                                excelData.put(excelHead.get(cell.getColumnIndex())+"_int",cell.getNumericCellValue());
                                break;
                            case Cell.CELL_TYPE_STRING:    //field that represents string cell type

                                //System.out.print(cell.getColumnIndex()+"->"+cell.getStringCellValue()+ "\t\t");
                                excelData.put(excelHead.get(cell.getColumnIndex()),cell.getStringCellValue());
                                break;
                        }


                    }



                }
                if(i != 0){
                    allData.add(excelData);
                }

                i++;
            }  // end main loop


        }catch(Exception e){
            System.out.println(e.fillInStackTrace());
        }

        return allData;
    }

    public ArrayList processXlProdDesc(FileInputStream inputStream) {

        ArrayList allData = new ArrayList();
        Map excelHead = new HashMap();



        try{

            HSSFWorkbook wb = new HSSFWorkbook(inputStream);
            HSSFSheet sheet=wb.getSheetAt(0);

            FormulaEvaluator formulaEvaluator=wb.getCreationHelper().createFormulaEvaluator();

            int i = 0;
            for( Row row: sheet)     //iteration over row using for each loop
            {

                Map excelData = new HashMap();
                for(Cell cell: row)    //iteration over cell using for each loop
                {
                    //System.out.println(cell.getRowIndex()+"-"+cell.getColumnIndex());
                    if(cell.getRowIndex()==0){
                        excelHead.put(cell.getColumnIndex(),cell.getStringCellValue());
                    }

                    if(cell.getRowIndex()!=0){
                        switch(formulaEvaluator.evaluateInCell(cell).getCellType())
                        {
                            case Cell.CELL_TYPE_NUMERIC:   //field that represents numeric cell type

                                //System.out.print(cell.getColumnIndex()+"->"+cell.getNumericCellValue()+ "\t\t");
                                //excelData.put(excelHead.get(cell.getColumnIndex()),cell.getNumericCellValue());
                                excelData.put(excelHead.get(cell.getColumnIndex())+"_int",cell.getNumericCellValue());
                                break;
                            case Cell.CELL_TYPE_STRING:    //field that represents string cell type

                                //System.out.print(cell.getColumnIndex()+"->"+cell.getStringCellValue()+ "\t\t");
                                excelData.put(excelHead.get(cell.getColumnIndex()),cell.getStringCellValue());
                                break;
                        }


                    }



                }
                if(i != 0){
                    allData.add(excelData);
                }

                i++;
            }  // end main loop


        }catch(Exception e){
            System.out.println(e.fillInStackTrace());
        }

        return allData;
    }

    public ArrayList processXlxProdDesc(FileInputStream inputStream) {

        ArrayList allData = new ArrayList();
        Map excelHead = new HashMap();

        try{

            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = wb.getSheetAt(0);     //creating a Sheet object to retrieve object
            Iterator itr = sheet.iterator();    //iterating over excel file

            int i = 0;
            while (itr.hasNext())
            {

                Map excelData = new HashMap();

                Row row = itr.next();
                Iterator cellIterator = row.cellIterator();   //iterating over each column

                while (cellIterator.hasNext())
                {
                    Cell cell = cellIterator.next();
                    //System.out.println(cell.getRowIndex()+"-"+cell.getColumnIndex());
                    if(cell.getRowIndex()==0){
                        excelHead.put(cell.getColumnIndex(),cell.getStringCellValue());
                    }

                    if(cell.getRowIndex()!=0){
                        switch(cell.getCellType())
                        {
                            case Cell.CELL_TYPE_NUMERIC:   //field that represents numeric cell type

                                //System.out.print(cell.getColumnIndex()+"->"+cell.getNumericCellValue()+ "\t\t");
                                excelData.put(excelHead.get(cell.getColumnIndex())+"_int",cell.getNumericCellValue());
                                break;
                            case Cell.CELL_TYPE_STRING:    //field that represents string cell type

                                //System.out.print(cell.getColumnIndex()+"->"+cell.getStringCellValue()+ "\t\t");
                                excelData.put(excelHead.get(cell.getColumnIndex()),cell.getStringCellValue());
                                break;
                        }


                    }



                }
                if(i != 0){
                    allData.add(excelData);
                }

                i++;
            }  // end main loop


        }catch(Exception e){
            System.out.println(e.fillInStackTrace());
        }

        return allData;
    }

}

Excel Submit From

<form  action="importExlSizeChart" method="post" enctype="multipart/form-data">
               
				<div class="col-sm-6">
					 <input type="file" id="excelFile" name="excelFile" />
				</div>
                                       

                <div class="modal-footer">
                    <button type="submit" class="btn btn-primary checkextension">Upload
                    </button>
               </div>
    </form>

Excel Processing Method

void importExlSizeChart(){


        ArrayList allData

        FileInputStream inputStream

        File file = request?.getFiles('excelFile')[0];
        inputStream = file?.getProperties()?.inputStream;

        String ext = file?.originalFilename?.substring(file?.originalFilename?.lastIndexOf('.'))

        UkmExcel ukmExcel  = new UkmExcel();

        allData = ukmExcel.importExcelProdDesc(inputStream,ext);
        int count = 0;
        int total = allData.size();
        String dept_name
        

        for (int i = 0; i < allData.size(); i++) {
		
            //String aName = allData.get(i);

            dept_name = allData?.get(i)?.getAt("dept_name")
            dept_name = dept_name?.trim()
			count = count+1;

        }

        if(count > 0){
            flash.success = "Upload successful. ${count} data uploaded"
            redirect(controller: "TescoNonRfid", action: "addUpdateSizeChart")
        }else{
            flash.message = "No new data found on of ${total} data"
            redirect(controller: "TescoNonRfid", action: "addUpdateSizeChart")
        }

    }


bONEandALL
Visitor

Total : 9222

Today :7

Today Visit Country :

  • United States