D:\CRM_Project\CRM_Metadata_Structure\NetBeamsICM_TestProject\ICM_DB_TestComponents\src\java\unstructured_data_parser\Excel2DAO_Mapper.java
  1 /*
  2  * To change this license header, choose License Headers in Project Properties.
  3  * To change this template file, choose Tools | Templates
  4  * and open the template in the editor.
  5  */
  6 package unstructured_data_parser;
  7 
  8 import constants.MainConstants;
  9 import java.io.File;
 10 import java.io.FileInputStream;
 11 import java.nio.file.Files;
 12 import java.util.Iterator;
 13 import org.apache.commons.io.FilenameUtils;
 14 import org.apache.poi.hssf.usermodel.HSSFSheet;
 15 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 16 import org.apache.poi.ss.usermodel.Cell;
 17 import org.apache.poi.ss.usermodel.Row;
 18 import org.apache.poi.xssf.usermodel.XSSFSheet;
 19 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 20 import java.util.ArrayList;
 21 import unstructured_data_idao.TokenValueDAO;
 22 import java.util.StringTokenizer;
 23 import unstructured_data_idao.ExcelCellMap2DAO;
 24 import unstructured_data_idao.ExcelDAO_Constants;
 25 import unstructured_data_idao.OneRow_ExcelCells;
 26 
 27 /**
 28  *
 29  * @author sameldin
 30  */
 31 public class Excel2DAO_Mapper {
 32     
 33     //String classDefaultInputExcelFile =  "SamTestIDAO_1.xlsx";
 34     String classDefaultInputExcelFile =  "SimpleInputExcelSheet.xlsx";
 35     
 36     ArrayList <OneRow_ExcelCells>classAllExcelRowsDAO_ArrayList = null;
 37     
 38     public Excel2DAO_Mapper(){
 39         
 40         //this("SamTestIDAO_1.xlsx");
 41         this("SimpleInputExcelSheet.xlsx");
 42         //this("SimpleInputExcelSheet_2.xlsx");
 43         
 44     }
 45     /*
 46     
 47     */
 48     public Excel2DAO_Mapper(String passedFileName){
 49 
 50         
 51                 String fileType = "Undetermined";
 52                 int success = MainConstants.FAILED;
 53                 String inputFolder = MainConstants.EXCEL_PATH;
 54                 String inputExcelFile = inputFolder + "\\" + passedFileName;     
 55                 try
 56                 {
 57                     
 58                     File testfile = new File(inputExcelFile);
 59                     fileType = Files.probeContentType(testfile.toPath());
 60                     String fileExtension = FilenameUtils.getExtension(testfile.getName());
 61                     FileInputStream file = new FileInputStream(new File(inputExcelFile));
 62                     if(fileExtension.equalsIgnoreCase(MainConstants.EXCEL_XLSX_EXTENSION_STRING)) {
 63                         success = parseXSSFWorkbook(file);
 64                     }
 65                     else if(fileExtension.equalsIgnoreCase(MainConstants.EXCEL_XLS_EXTENSION_STRING)) {
 66                         success = parseHSSFWorkbook(file);
 67                     }
 68                     else if(fileExtension.equalsIgnoreCase("csv")){
 69                         success = parseHSSFWorkbook(file);                    
 70                     }
 71                     else {
 72                         System.out.print("\nfileType = " + fileExtension);
 73                         System.out.print("\nfileType not Known ");
 74                     }
 75                    System.out.print("\n\n +++++++++++++++  success = " + success + " +++++++++++++++\n");
 76                     
 77                 }
 78                 catch (Exception e) {
 79                         e.printStackTrace();
 80                 }
 81     }
 82         /*
 83         
 84         */
 85         public String getExcelFileName(){
 86             
 87             return(classDefaultInputExcelFile);
 88         }
 89         /*
 90         
 91         */
 92         int parseXSSFWorkbook(FileInputStream file){
 93             
 94             // create all the excel rows as class variable   
 95             classAllExcelRowsDAO_ArrayList    = new ArrayList<OneRow_ExcelCells>();
 96             
 97             try{
 98                 //Create Workbook instance holding reference to .xlsx file
 99                 //Get first/desired sheet from the workbook
100                 XSSFWorkbook workbook =  new XSSFWorkbook(file);
101                 XSSFSheet sheet = workbook.getSheetAt(0);
102                 //int lastRowNumber = sheet.getLastRowNum();
103                 //int lastCoumnNumber = sheet.
104                 //Iterate through each rows one by one
105                 Iterator<Row> rowIterator = sheet.iterator();
106                 while (rowIterator.hasNext()) 
107                 {
108                         Row row = rowIterator.next();
109                         // get one row of excel cells from OneRow_ExcelCells object
110                         // OneRow_ExcelCells object is an arrayList of ExcelCellMap2DAO objects
111                         // must stantiate each ExcelCellMap2DAO object
112                         OneRow_ExcelCells localOneRow_ExcelCells = new OneRow_ExcelCells();
113                         ArrayList <ExcelCellMap2DAO> classExcelOneRowDAO_ArrayList = localOneRow_ExcelCells.getOneRowDAOArrayList();
114                         // make sure it is not null
115                         if(null == classExcelOneRowDAO_ArrayList) {
116                             classExcelOneRowDAO_ArrayList  = new ArrayList<ExcelCellMap2DAO>();
117                         }
118                         // add this row of one to all rows
119                         classAllExcelRowsDAO_ArrayList.add(localOneRow_ExcelCells);
120                         // need to add word object to the class of one row
121                         //==============================
122                         //For each row, iterate through all the columns
123                         Iterator<Cell> cellIterator = row.cellIterator();
124                         int rowNumber = row.getRowNum();// + 1;
125                         int physicalNumberOfCells = row.getPhysicalNumberOfCells();
126                         int lastCellNum = row.getLastCellNum();
127                         System.out.print("\n=========> Row + " + rowNumber + " \t ");                                                              
128                         System.out.print("physicalNumberOfCells = " + physicalNumberOfCells + "\t");                               
129                         System.out.print("lastCellNum = " + lastCellNum + "<=============\n");
130                         //===========================
131                         
132                         while (cellIterator.hasNext())  {
133                             Cell cell = cellIterator.next();
134                             // create the cell object
135                             ExcelCellMap2DAO tempExcelCellMap2DAO = new ExcelCellMap2DAO();
136                             // add the row number
137                             tempExcelCellMap2DAO.setRowIndex(rowNumber);
138                             // add the cell object to one row arrayList
139                             classExcelOneRowDAO_ArrayList.add(tempExcelCellMap2DAO);                          
140                             //Check the cell type and format accordingly
141                             int cellColumnIndex = cell.getColumnIndex();
142                             tempExcelCellMap2DAO.setColumnIndex(cellColumnIndex);
143                             // populate the cell object with values
144                             switch (cell.getCellType()) 
145                             {
146                                 case Cell.CELL_TYPE_NUMERIC:
147                                         tempExcelCellMap2DAO.setCellType(ExcelDAO_Constants.EXCEL_CELL_TYPE_NUMERIC);
148                                         double tempDoubleValue = cell.getNumericCellValue();
149                                         tempExcelCellMap2DAO.setDoubleCellValue(tempDoubleValue); 
150                                         // check if currency
151                                         tempExcelCellMap2DAO.setComment(cell.getCellStyle().getDataFormatString());
152                                         tempExcelCellMap2DAO.setCellFormat(cell.getCellStyle().getDataFormat()); 
153                                         System.out.print(cell.getNumericCellValue() + ",  \t");
154                                         break;
155                                 case Cell.CELL_TYPE_STRING:
156                                         tempExcelCellMap2DAO.setCellType(ExcelDAO_Constants.EXCEL_CELL_TYPE_STRING);
157                                         tempExcelCellMap2DAO.setStringCellvalue(cell.getStringCellValue());
158                                         tempExcelCellMap2DAO.setXmlTagContentString(cell.getStringCellValue());
159                                         tempExcelCellMap2DAO.setComment("String value found");
160                                         System.out.print(cell.getStringCellValue() + "\t");
161                                         break;
162                                 case Cell.CELL_TYPE_BLANK:
163                                         tempExcelCellMap2DAO.setCellType(ExcelDAO_Constants.EXCEL_CELL_TYPE_BLANK);
164                                         tempExcelCellMap2DAO.setComment("Blank ??");
165                                         System.out.print("blank" + "\t");
166                                         break;
167                                 case Cell.CELL_TYPE_BOOLEAN:
168                                         tempExcelCellMap2DAO.setCellType(ExcelDAO_Constants.EXCEL_CELL_TYPE_BOOLEAN);
169                                         tempExcelCellMap2DAO.setComment(" Boolean ??");
170                                         System.out.print(Boolean.toString(cell.getBooleanCellValue()) + "\t");
171                                         break;
172                                 case Cell.CELL_TYPE_FORMULA:
173                                         tempExcelCellMap2DAO.setCellType(ExcelDAO_Constants.EXCEL_CELL_TYPE_FORMULA);
174                                         String formulaStringValue = cell.getCellFormula();
175                                         int formulaResultType = cell.getCachedFormulaResultType();
176                                         String formulaCellEvaluation =
177                                                 "formulaStringValue = "   + formulaStringValue + ";"
178                                                 + "formulaResultType = "   + formulaResultType ;
179                                         tempExcelCellMap2DAO.setCellEvaluationString(formulaCellEvaluation);
180                                         tempExcelCellMap2DAO.setFormulaCellvalue(formulaStringValue); 
181                                         tempExcelCellMap2DAO.setComment(cell.getCellStyle().getDataFormatString());
182                                         tempExcelCellMap2DAO.setCellFormat(cell.getCellStyle().getDataFormat()); 
183                                         System.out.print("=" + cell.getCellFormula() + "\t");
184                                         break;
185                                 case Cell.CELL_TYPE_ERROR:
186                                         tempExcelCellMap2DAO.setCellType(ExcelDAO_Constants.EXCEL_CELL_TYPE_ERROR);
187                                         tempExcelCellMap2DAO.setComment("Error ??");
188                                         System.out.print("error" + "\t");
189                                         break;
190                                 default:
191                                         tempExcelCellMap2DAO.setCellType(ExcelDAO_Constants.EXCEL_CELL_TYPE_ERROR);
192                                         tempExcelCellMap2DAO.setComment(" non of the above ??");
193                                         System.out.print("Default" + "\t");
194                                         break;
195                                 }
196                             } // end of one row of cells
197                         } // end of the excel sheet
198                     file.close();
199                 } 
200                 catch (Exception e) 
201                 {
202                         e.printStackTrace();
203                          return(MainConstants.FAILED);
204                 }
205             return(MainConstants.SUCCESS);
206         }
207         /*
208         
209         */
210         int parseHSSFWorkbook(FileInputStream file){
211             
212             try{
213                 //Create Workbook instance holding reference to .xlsx file
214                 //Get first/desired sheet from the workbook
215                 HSSFWorkbook workbook =  new HSSFWorkbook(file);
216                 HSSFSheet sheet = workbook.getSheetAt(0);
217                 //Iterate through each rows one by one
218                 Iterator<Row> rowIterator = sheet.iterator();
219                 while (rowIterator.hasNext()) 
220                 {
221                         Row row = rowIterator.next();
222                         //For each row, iterate through all the columns
223                         Iterator<Cell> cellIterator = row.cellIterator();
224                         int rowNumber = row.getRowNum() + 1;
225                         int physicalNumberOfCells = row.getPhysicalNumberOfCells();
226                         int lastCellNum = row.getLastCellNum();
227                         System.out.print("\n=========> Row + " + rowNumber + " \t ");                                                              
228                         System.out.print("physicalNumberOfCells = " + physicalNumberOfCells + "\t");                               
229                         System.out.print("lastCellNum = " + lastCellNum + "<=============\n");                               
230                         while (cellIterator.hasNext())  {
231                             Cell cell = cellIterator.next();
232                             //Check the cell type and format accordingly
233                             int cellIndex = cell.getColumnIndex();
234                             switch (cell.getCellType()) 
235                             {
236                                 case Cell.CELL_TYPE_NUMERIC:
237                                         System.out.print(cell.getNumericCellValue() + ",  \t");
238                                         break;
239                                 case Cell.CELL_TYPE_STRING:
240                                         System.out.print(cell.getStringCellValue() + "\t");
241                                         break;
242                                 case Cell.CELL_TYPE_BLANK:
243                                         System.out.print("blank" + "\t");
244                                         break;
245                                 case Cell.CELL_TYPE_BOOLEAN:
246                                         System.out.print(Boolean.toString(cell.getBooleanCellValue()) + "\t");
247                                         break;
248                                 case Cell.CELL_TYPE_FORMULA:
249                                         System.out.print("=" + cell.getCellFormula() + "\t");
250                                         break;
251                                 case Cell.CELL_TYPE_ERROR:
252                                         System.out.print("error" + "\t");
253                                         break;
254                                 default:
255                                         System.out.print("Default" + "\t");
256                                         //break;
257                                     }
258                                 }
259 //                              System.out.println("\n========= Row + " + rowNumber + " =============");
260                         }
261                         file.close();
262                 } 
263                 catch (Exception e) 
264                 {
265                         e.printStackTrace();
266                          return(MainConstants.FAILED);
267                 }
268             return(MainConstants.SUCCESS);
269         }
270         /*
271         
272         */
273         public ArrayList <OneRow_ExcelCells> getAllSheetRows(){
274                 
275                 return(classAllExcelRowsDAO_ArrayList);                       
276         }
277         /*
278         
279         */
280         public static void main(String[] args){
281             
282             Excel2DAO_Mapper localExcel2DAO_Mapper = new Excel2DAO_Mapper();
283             ArrayList <OneRow_ExcelCells> localAllRows = localExcel2DAO_Mapper.getAllSheetRows();
284             if(null == localAllRows){
285                 System.out.print("rows arrayList  is null ");
286                 return;
287             }
288             OneRow_ExcelCells localCellRowObject = localAllRows.get(2);
289             ArrayList <ExcelCellMap2DAO> oneArrayListCells = localCellRowObject.getOneRowDAOArrayList();
290             ExcelCellMap2DAO tempCellDAO = oneArrayListCells.get(9);
291             LoadTokenValueArrayList tempLoadTokenValueArrayList = new LoadTokenValueArrayList();
292             ArrayList <TokenValueDAO> localTokenValueDAOArrayList = tempLoadTokenValueArrayList.getTokenValueArrayList();
293             
294             
295             Excel2DAO_CellParserBusinessRule_1 localExcel2DAO_CellParserBusinessRule_1 
296                     = new Excel2DAO_CellParserBusinessRule_1(tempCellDAO, localTokenValueDAOArrayList);
297             int debugger_stop_here = 0;
298             
299         }    
300 }
301