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")
}
}
