D:\CRM_Project\CRM_Metadata_Structure\NetBeamsICM_TestProject\ICM_DB_TestComponents\src\java\unstructured_data_parser\Excel2DAO_Mapper.java |
1
2
3
4
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
30
31 public class Excel2DAO_Mapper {
32
33
34 String classDefaultInputExcelFile = "SimpleInputExcelSheet.xlsx";
35
36 ArrayList <OneRow_ExcelCells>classAllExcelRowsDAO_ArrayList = null;
37
38 public Excel2DAO_Mapper(){
39
40
41 this("SimpleInputExcelSheet.xlsx");
42
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
95 classAllExcelRowsDAO_ArrayList = new ArrayList<OneRow_ExcelCells>();
96
97 try{
98
99
100 XSSFWorkbook workbook = new XSSFWorkbook(file);
101 XSSFSheet sheet = workbook.getSheetAt(0);
102
103
104
105 Iterator<Row> rowIterator = sheet.iterator();
106 while (rowIterator.hasNext())
107 {
108 Row row = rowIterator.next();
109
110
111
112 OneRow_ExcelCells localOneRow_ExcelCells = new OneRow_ExcelCells();
113 ArrayList <ExcelCellMap2DAO> classExcelOneRowDAO_ArrayList = localOneRow_ExcelCells.getOneRowDAOArrayList();
114
115 if(null == classExcelOneRowDAO_ArrayList) {
116 classExcelOneRowDAO_ArrayList = new ArrayList<ExcelCellMap2DAO>();
117 }
118
119 classAllExcelRowsDAO_ArrayList.add(localOneRow_ExcelCells);
120
121
122
123 Iterator<Cell> cellIterator = row.cellIterator();
124 int rowNumber = row.getRowNum();
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
135 ExcelCellMap2DAO tempExcelCellMap2DAO = new ExcelCellMap2DAO();
136
137 tempExcelCellMap2DAO.setRowIndex(rowNumber);
138
139 classExcelOneRowDAO_ArrayList.add(tempExcelCellMap2DAO);
140
141 int cellColumnIndex = cell.getColumnIndex();
142 tempExcelCellMap2DAO.setColumnIndex(cellColumnIndex);
143
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
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 }
197 }
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
214
215 HSSFWorkbook workbook = new HSSFWorkbook(file);
216 HSSFSheet sheet = workbook.getSheetAt(0);
217
218 Iterator<Row> rowIterator = sheet.iterator();
219 while (rowIterator.hasNext())
220 {
221 Row row = rowIterator.next();
222
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
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
257 }
258 }
259
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