202 lines
8.9 KiB
Plaintext
Executable File
202 lines
8.9 KiB
Plaintext
Executable File
package com.fp.excel;
|
|
|
|
import java.math.BigDecimal;
|
|
import java.util.ArrayList;
|
|
import java.util.Date;
|
|
import java.util.HashMap;
|
|
import java.util.List;
|
|
import java.util.Map;
|
|
import org.apache.poi.ss.usermodel.Cell;
|
|
import org.apache.poi.ss.usermodel.CellValue;
|
|
import org.apache.poi.ss.usermodel.DateUtil;
|
|
import org.apache.poi.ss.usermodel.FormulaEvaluator;
|
|
import org.apache.poi.ss.usermodel.Row;
|
|
import org.apache.poi.ss.usermodel.Sheet;
|
|
import org.apache.poi.ss.usermodel.Workbook;
|
|
|
|
/**
|
|
*
|
|
* @author malmeida
|
|
*/
|
|
public class ExcelToMap implements ExcelToMapMessages {
|
|
|
|
private WorkbookFactory wf;
|
|
private WorkbookFactoryStreamBytes wfsb;
|
|
|
|
public ExcelToMap(String file_Path) {
|
|
this.wf = new WorkbookFactory(file_Path);
|
|
}
|
|
|
|
/**
|
|
*
|
|
* @param arrayBytesExcel
|
|
* @param extension
|
|
*/
|
|
public ExcelToMap(byte[] arrayBytesExcel, String extension) {
|
|
this.wfsb = new WorkbookFactoryStreamBytes(arrayBytesExcel, extension);
|
|
}
|
|
|
|
/**
|
|
*
|
|
* @return
|
|
*/
|
|
public String get_Workbook_Status() {
|
|
return this.wf.get_WorkbookFactory_Status();
|
|
}
|
|
|
|
public String getWorkbookStatusByte() {
|
|
return this.wfsb.getWorkbookFactoryStatus();
|
|
}
|
|
|
|
/**
|
|
*
|
|
* @return
|
|
*/
|
|
public Map<String, Object> getExcelMap() {
|
|
String workbookStatus = this.wfsb.getWorkbookFactoryStatus();
|
|
if (workbookStatus.equals(ExcelToMapMessages.WORKBOOK_CREATED)) {
|
|
Workbook wb = wfsb.getExcelWorkBook();
|
|
FormulaEvaluator fev = wb.getCreationHelper().createFormulaEvaluator();
|
|
int activeSheetIndex = wb.getActiveSheetIndex();
|
|
String activeSheetName = wb.getSheetName(activeSheetIndex);
|
|
Sheet activeSheet = wb.getSheet(activeSheetName);
|
|
Map<String, Object> cellList = new HashMap<String, Object>();
|
|
for (Row row : activeSheet) {
|
|
for (Cell cell : row) {
|
|
int cell_Row_Index = cell.getRowIndex();
|
|
int cell_Colum_Index = cell.getColumnIndex();
|
|
String MapKey = "" + cell_Row_Index + ":" + cell_Colum_Index;
|
|
switch (cell.getCellType()) {
|
|
case Cell.CELL_TYPE_BOOLEAN:
|
|
// System.out.println(MapKey);
|
|
String cell_value_boolean_str = cell.getStringCellValue();
|
|
Object bool_obj = Boolean.parseBoolean(cell_value_boolean_str);
|
|
cellList.put(MapKey, bool_obj);
|
|
break;
|
|
case Cell.CELL_TYPE_NUMERIC:
|
|
if (DateUtil.isCellDateFormatted(cell)) {
|
|
// System.out.println(MapKey);
|
|
Date cellValue_date = cell.getDateCellValue();
|
|
long cellValue_long = cellValue_date.getTime();
|
|
java.sql.Date sqlDate = new java.sql.Date(cellValue_long);
|
|
Object date_obj = sqlDate;
|
|
cellList.put(MapKey, date_obj);
|
|
} else {
|
|
// System.out.println(MapKey);
|
|
double cellValue_double = cell.getNumericCellValue();
|
|
Object cell_value_bd = new BigDecimal(cellValue_double);
|
|
cellList.put(MapKey, cell_value_bd);
|
|
}
|
|
break;
|
|
case Cell.CELL_TYPE_STRING:
|
|
// System.out.println(MapKey);
|
|
Object cell_value_str = cell.getStringCellValue();
|
|
cellList.put(MapKey, cell_value_str);
|
|
break;
|
|
case Cell.CELL_TYPE_BLANK:
|
|
// System.out.println(MapKey);
|
|
Object cell_value_blank_str = "";
|
|
cellList.put(MapKey, cell_value_blank_str);
|
|
break;
|
|
case Cell.CELL_TYPE_ERROR:
|
|
// System.out.println(MapKey);
|
|
Object cell_value_error_str = "";
|
|
cellList.put(MapKey, cell_value_error_str);
|
|
break;
|
|
case Cell.CELL_TYPE_FORMULA:
|
|
// System.out.println(MapKey);
|
|
CellValue cellValue = fev.evaluate(cell);
|
|
|
|
switch (cellValue.getCellType()) {
|
|
case Cell.CELL_TYPE_BOOLEAN:
|
|
// System.out.println(MapKey);
|
|
String cell_value_boolean_f_str = cell.getStringCellValue();
|
|
Object cell_value_boolean_f = Boolean.parseBoolean(cell_value_boolean_f_str);
|
|
cellList.put(MapKey, cell_value_boolean_f);
|
|
break;
|
|
case Cell.CELL_TYPE_NUMERIC:
|
|
if (DateUtil.isCellDateFormatted(cell)) {
|
|
// System.out.println(MapKey);
|
|
Date cellValue_date = cell.getDateCellValue();
|
|
long cellValue_long = cellValue_date.getTime();
|
|
java.sql.Date sqlDate = new java.sql.Date(cellValue_long);
|
|
Object date_obj = sqlDate;
|
|
cellList.put(MapKey, date_obj);
|
|
} else {
|
|
// System.out.println(MapKey);
|
|
double cellValue_double = cell.getNumericCellValue();
|
|
Object cell_value_bd = new BigDecimal(cellValue_double);
|
|
cellList.put(MapKey, cell_value_bd);
|
|
}
|
|
break;
|
|
case Cell.CELL_TYPE_STRING:
|
|
// System.out.println(MapKey);
|
|
Object cell_value_str_f = cell.getStringCellValue();
|
|
cellList.put(MapKey, cell_value_str_f);
|
|
break;
|
|
case Cell.CELL_TYPE_BLANK:
|
|
// System.out.println(MapKey);
|
|
Object cell_value_blank_str_f = "";
|
|
cellList.put(MapKey, cell_value_blank_str_f);
|
|
break;
|
|
case Cell.CELL_TYPE_ERROR:
|
|
// System.out.println(MapKey);
|
|
Object cell_value_error_str_f = "";
|
|
cellList.put(MapKey, cell_value_error_str_f);
|
|
break;
|
|
case Cell.CELL_TYPE_FORMULA:
|
|
// NUNCA OCURRE
|
|
break;
|
|
default:
|
|
// System.out.println(MapKey);
|
|
Object cell_value_indeterminado_str_f = "";
|
|
cellList.put(MapKey, cell_value_indeterminado_str_f);
|
|
break;
|
|
}
|
|
break;
|
|
default:
|
|
// System.out.println(MapKey);
|
|
Object cell_value_indeterminado_str = "";
|
|
cellList.put(MapKey, cell_value_indeterminado_str);
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
return cellList;
|
|
} else {
|
|
return null;
|
|
}
|
|
}
|
|
|
|
/**
|
|
*
|
|
* @param fila_init
|
|
* @param fila_final
|
|
* @param colum_init
|
|
* @param colum_final
|
|
* @return
|
|
*/
|
|
public List<Map<String, Object>> get_Especific_Data(int fila_init, int fila_final, int colum_init, int colum_final) {
|
|
Map<String, Object> cell_list = this.getExcelMap();
|
|
List<Map<String, Object>> ldata = new ArrayList<Map<String, Object>>();
|
|
for (int i = fila_init; i <= fila_final; i++) {
|
|
Map<String, Object> m = new HashMap<String, Object>();
|
|
for (int j = colum_init; j <= colum_final; j++) {
|
|
String Key = "" + i + ":" + j;
|
|
if (cell_list.containsKey(Key)) {
|
|
Object obj = cell_list.get(Key);
|
|
m.put(Key, obj);
|
|
// System.out.println(obj.toString());
|
|
} else {
|
|
Object obj = "blank";
|
|
m.put(Key, obj);
|
|
// System.out.println(obj.toString());
|
|
}
|
|
}
|
|
ldata.add(m);
|
|
}
|
|
|
|
return ldata;
|
|
}
|
|
}
|