package br.com.sl.core;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.nio.file.Path;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import br.com.sl.domain.dto.robo.ProfitTick;
public class ExcelProfitDataProvider
implements ProfitDataProvider
{
private final Path excelFile
;
private final String sheetName
;
// índices de coluna (0 = A, 1 = B, etc.)
private final int colAsset =
0;
private final int colDate =
1;
private final int colTime =
2;
private final int colLast =
3;
// formatos da data e hora, conforme Excel
private final DateTimeFormatter fmtData = DateTimeFormatter.
ofPattern("dd/MM/yyyy");
private final DateTimeFormatter fmtHora = DateTimeFormatter.
ofPattern("HH:mm:ss");
public ExcelProfitDataProvider
(Path excelFile,
String sheetName
) {
this.
excelFile = excelFile
;
this.
sheetName = sheetName
;
}
@
Override
public Map<String, ProfitTick
> readCurrentTicks
() {
Map<String, ProfitTick
> ticks =
new HashMap<>();
try (FileInputStream fis =
new FileInputStream(excelFile.
toFile()); Workbook workbook =
new XSSFWorkbook
(fis
)) {
Sheet sheet =
(sheetName
!=
null)
? workbook.
getSheet(sheetName
)
: workbook.
getSheetAt(0);
if (sheet ==
null) {
System.
err.
println("[ExcelProfitDataProvider] Aba não encontrada: " + sheetName
);
return ticks
;
}
int firstRow = sheet.
getFirstRowNum() +
1; // pula cabeçalho
int lastRow = sheet.
getLastRowNum();
for (int i = firstRow
; i
<= lastRow
; i++
) {
Row row = sheet.
getRow(i
);
if (row ==
null) continue;
Cell cellAsset = row.
getCell(colAsset
);
if (cellAsset ==
null) continue;
String asset = cellAsset.
toString().
trim();
if (asset.
isEmpty()) continue;
LocalDate date = lerData
(row.
getCell(colDate
));
LocalTime time = lerHora
(row.
getCell(colTime
));
BigDecimal price = lerPreco
(row.
getCell(colLast
));
if (date ==
null || time ==
null || price ==
null) {
continue;
}
LocalDateTime dateTime = LocalDateTime.
of(date, time
);
ProfitTick tick =
new ProfitTick
(asset, dateTime, price
);
// Se tiver mais de uma linha por ativo, você pode decidir
// se guarda o último (maior horário) ou o primeiro; aqui guardo o último
ProfitTick tickExistente = ticks.
get(asset
);
if (tickExistente ==
null || tick.
getDateTime().
isAfter(tickExistente.
getDateTime())) {
ticks.
put(asset, tick
);
}
}
} catch (IOException e
) {
System.
err.
println("[ExcelProfitDataProvider] Erro ao ler Excel: " + e.
getMessage());
}
return ticks
;
}
private LocalDate lerData
(Cell cell
) {
if (cell ==
null) return null;
try {
int t = cell.
getCellType();
// 1) Se for fórmula RTD
if (t == CellType.
FORMULA.
getCode()) {
int ct = cell.
getCachedFormulaResultType();
if (ct == CellType.
NUMERIC.
getCode()) {
double dv = cell.
getNumericCellValue();
java.
util.
Date d = DateUtil.
getJavaDate(dv
);
return d.
toInstant()
.
atZone(java.
time.
ZoneId.
systemDefault())
.
toLocalDate();
}
if (ct == CellType.
STRING.
getCode()) {
String text = cell.
getStringCellValue().
trim();
if (text.
isEmpty()) return null;
DateTimeFormatter
[] patterns =
{
DateTimeFormatter.
ofPattern("dd/MM/yyyy"),
DateTimeFormatter.
ofPattern("dd-MM-yyyy"),
DateTimeFormatter.
ofPattern("yyyy-MM-dd"),
DateTimeFormatter.
ofPattern("yyyy/MM/dd")
};
for (DateTimeFormatter p : patterns
) {
try {
return LocalDate.
parse(text, p
);
} catch (Exception ignored
) {}
}
}
}
// 2) Se for numérico formatado como data
if (t == CellType.
NUMERIC.
getCode() && DateUtil.
isCellDateFormatted(cell
)) {
java.
util.
Date d = cell.
getDateCellValue();
return d.
toInstant()
.
atZone(java.
time.
ZoneId.
systemDefault())
.
toLocalDate();
}
// 3) Se vier como texto
String text = cell.
toString().
trim();
if (text.
isEmpty()) return null;
DateTimeFormatter
[] patterns =
{
DateTimeFormatter.
ofPattern("dd/MM/yyyy"),
DateTimeFormatter.
ofPattern("dd-MM-yyyy"),
DateTimeFormatter.
ofPattern("yyyy-MM-dd"),
DateTimeFormatter.
ofPattern("yyyy/MM/dd")
};
for (DateTimeFormatter p : patterns
) {
try {
return LocalDate.
parse(text, p
);
} catch (Exception ignored
) {}
}
} catch (Exception e
) {
System.
out.
println("[lerData] erro: " + e.
getMessage());
}
return null;
}
private LocalTime lerHora
(Cell cell
) {
if (cell ==
null) return null;
try {
// 1) Fórmula RTD
if (cell.
getCellType() == CellType.
FORMULA.
getCode()) {
if (cell.
getCachedFormulaResultType() == CellType.
NUMERIC.
getCode()) {
java.
util.
Date d = DateUtil.
getJavaDate(cell.
getNumericCellValue());
return d.
toInstant()
.
atZone(java.
time.
ZoneId.
systemDefault())
.
toLocalTime();
}
if (cell.
getCachedFormulaResultType() == CellType.
STRING.
getCode()) {
String text = cell.
getStringCellValue().
trim();
if (text.
isEmpty()) return null;
DateTimeFormatter
[] patterns =
{
DateTimeFormatter.
ofPattern("HH:mm:ss"),
DateTimeFormatter.
ofPattern("H:mm:ss"),
DateTimeFormatter.
ofPattern("HH:mm"),
DateTimeFormatter.
ofPattern("H:mm")
};
for (DateTimeFormatter p : patterns
) {
try { return LocalTime.
parse(text, p
); }
catch (Exception ignored
) {}
}
}
}
// 2) Numérico com formato de data/hora
if (cell.
getCellType() == CellType.
NUMERIC.
getCode() && DateUtil.
isCellDateFormatted(cell
)) {
java.
util.
Date d = cell.
getDateCellValue();
return d.
toInstant()
.
atZone(java.
time.
ZoneId.
systemDefault())
.
toLocalTime();
}
// 3) Texto simples
String text = cell.
toString().
trim();
if (text.
isEmpty()) return null;
DateTimeFormatter
[] patterns =
{
DateTimeFormatter.
ofPattern("HH:mm:ss"),
DateTimeFormatter.
ofPattern("H:mm:ss"),
DateTimeFormatter.
ofPattern("HH:mm"),
DateTimeFormatter.
ofPattern("H:mm")
};
for (DateTimeFormatter p : patterns
) {
try { return LocalTime.
parse(text, p
); }
catch (Exception ignored
) {}
}
} catch (Exception e
) {
System.
out.
println("[lerHora] erro: " + e.
getMessage());
}
return null;
}
private BigDecimal lerPreco
(Cell cell
) {
if (cell ==
null) return null;
try {
// 1) Fórmula RTD
if (cell.
getCellType() == CellType.
FORMULA.
getCode()) {
if (cell.
getCachedFormulaResultType() == CellType.
NUMERIC.
getCode()) {
return BigDecimal.
valueOf(cell.
getNumericCellValue());
}
if (cell.
getCachedFormulaResultType() == CellType.
STRING.
getCode()) {
String text = cell.
getStringCellValue().
trim();
if (text.
isEmpty()) return null;
text = text.
replace(".",
"").
replace(",",
".");
return new BigDecimal(text
);
}
}
// 2) Numérico simples
if (cell.
getCellType() == CellType.
NUMERIC.
getCode()) {
return BigDecimal.
valueOf(cell.
getNumericCellValue());
}
// 3) Texto
String text = cell.
toString().
trim();
if (text.
isEmpty()) return null;
text = text.
replace(".",
"").
replace(",",
".");
return new BigDecimal(text
);
} catch (Exception e
) {
System.
out.
println("[lerPreco] erro: " + e.
getMessage());
}
return null;
}
}