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.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
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.kronus.core.Timeframe;
import br.com.sl.domain.dto.robo.ProfitTick;
import br.com.sl.domain.model.Candle;
import br.com.sl.domain.model.tipos.TipoPeriodoCandle;
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");
private static final DateTimeFormatter STRING_DATA_FORMATTER = DateTimeFormatter.
ofPattern("dd/MM/yyyy HH:mm:ss");
private static final DateTimeFormatter STRING_DATE_TIME_FORMATTER = DateTimeFormatter.
ofPattern("dd-MM-yyyy HH:mm:ss");
public ExcelProfitDataProvider
(Path excelFile,
String sheetName
) {
this.
excelFile = excelFile
;
this.
sheetName = sheetName
;
}
@
Override
public Map<String, ProfitTick
> readCurrentTicks
() {
// TODO Auto-generated method stub
return null;
}
/**
* Lê o arquivo Excel dentro do resources.
* Exemplo de uso:
* lerCandles("/dados/Dados Trade 20251117.xlsx");
* @throws IOException
*/
public List<Candle
> lerCandles
() throws IOException {
List<Candle
> candles =
new ArrayList<>();
try (FileInputStream fis =
new FileInputStream(excelFile.
toFile()); Workbook workbook =
new XSSFWorkbook
(fis
)) {
int numberOfSheets = workbook.
getNumberOfSheets();
for (int i =
0; i
< numberOfSheets
; i++
) {
Sheet sheet = workbook.
getSheetAt(i
);
if (sheet.
getSheetName().
equals(sheetName
)) {
boolean firstRow =
true;
for (Row row : sheet
) {
if (firstRow
) {
firstRow =
false; // pula o cabeçalho
continue;
}
// 0 = Contador
// 0 = Ativo
// 1 = Dia
// 2 = Hora
// 3 = Abertura
// 4 = Máxima
// 5 = Mínima
// 6 = Fechamento
// 7 = Volume
// 8 = ...
Cell contadorCell = row.
getCell(0);
Cell ativoCell = row.
getCell(1);
Cell dataCell = row.
getCell(2);
Cell horaCell = row.
getCell(3);
Cell aberturaCell = row.
getCell(4);
Cell maximaCell = row.
getCell(5);
Cell minimaCell = row.
getCell(6);
Cell fechamentoCell = row.
getCell(7);
Cell finalizadoCell = row.
getCell(8);
if (!isNumeric
(aberturaCell
) ||
!isNumeric
(maximaCell
)
||
!isNumeric
(minimaCell
) ||
!isNumeric
(fechamentoCell
)) {
continue;
}
Integer contador =
BigDecimal.
valueOf(contadorCell.
getNumericCellValue()).
intValue();
String ativoDescricao = ativoCell.
getStringCellValue();
// String data = dataCell.getStringCellValue();
// String hora = horaCell.getStringCellValue();
LocalDate data = lerData
(dataCell
);
LocalTime hora = lerHora
(horaCell
);
LocalDateTime dataHora = LocalDateTime.
of(data, hora
);
// Date data = diaCell.getDateCellValue();
// String dia = new SimpleDateFormat("dd/MM/yyyy").format(data);
// String hora = horaCell.getStringCellValue();
// LocalDateTime time = getLocalDateTime(dia + " " + hora, STRING_DATA_FORMATTER);
BigDecimal abertura =
BigDecimal.
valueOf(aberturaCell.
getNumericCellValue());
BigDecimal topo =
BigDecimal.
valueOf(maximaCell.
getNumericCellValue());
BigDecimal fundo =
BigDecimal.
valueOf(minimaCell.
getNumericCellValue());
BigDecimal fechamento =
BigDecimal.
valueOf(fechamentoCell.
getNumericCellValue());
String finalizado = finalizadoCell.
getStringCellValue();
if (finalizado.
equals("S")) {
Candle candle =
new Candle
(contador, ativoDescricao, dataHora, abertura, topo, fundo, fechamento, TipoPeriodoCandle.
M1.
getValor());
candles.
add(candle
);
}
}
}
}
} catch (EncryptedDocumentException e
) {
// TODO Auto-generated catch block
e.
printStackTrace();
}
// return adicionarContadores(inverterLista(candles));
return candles
;
}
public static List<Candle
> inverterLista
(List<Candle
> candles
) {
List<Candle
> invertida =
new ArrayList<>(candles
);
Collections.
reverse(invertida
);
return invertida
;
}
public static List<Candle
> adicionarContadores
(List<Candle
> candles
) {
Integer contador =
1;
List<Candle
> comContadores =
new ArrayList<>();
for (Candle candle : candles
) {
candle.
setContadorCandle(contador
);
comContadores.
add(candle
);
contador++
;
}
return comContadores
;
}
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;
}
/*
public List<Candle> lerCandles(String resourcePath, Timeframe selecionarTipoTemporizador) throws IOException {
List<Candle> candles = new ArrayList<>();
// Arquivo dentro de src/main/resources
InputStream is = getClass().getResourceAsStream(resourcePath);
if (is == null) {
throw new IOException("Arquivo não encontrado no resources: " + resourcePath);
}
try (Workbook workbook = WorkbookFactory.create(is)) {
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
String sheetName = sheet.getSheetName();
Timeframe tipoTemporizador = resolveTipoTemporizador(sheetName);
if (tipoTemporizador == selecionarTipoTemporizador) {
boolean firstRow = true;
for (Row row : sheet) {
if (firstRow) {
firstRow = false; // pula o cabeçalho
continue;
}
// 0 = Data (data+hora)
// 1 = Abertura
// 2 = Máxima
// 3 = Mínima
// 4 = Fechamento
Cell dataCell = row.getCell(0);
Cell aberturaCell = row.getCell(1);
Cell maximaCell = row.getCell(2);
Cell minimaCell = row.getCell(3);
Cell fechamentoCell = row.getCell(4);
if (!isNumeric(aberturaCell) || !isNumeric(maximaCell)
|| !isNumeric(minimaCell) || !isNumeric(fechamentoCell)) {
// linha vazia ou inválida
continue;
}
LocalDateTime time = getLocalDateTime(dataCell, STRING_DATE_TIME_FORMATTER);
if (time == null) {
// se não conseguir converter a data/hora, pode pular ou manter null
// aqui vou pular para evitar candle "incompleto"
continue;
}
BigDecimal abertura = BigDecimal.valueOf(aberturaCell.getNumericCellValue());
BigDecimal topo = BigDecimal.valueOf(maximaCell.getNumericCellValue());
BigDecimal fundo = BigDecimal.valueOf(minimaCell.getNumericCellValue());
BigDecimal fechamento = BigDecimal.valueOf(fechamentoCell.getNumericCellValue());
Candle candle = new Candle(time, abertura, topo, fundo, fechamento, TipoPeriodoCandle.M1.getValor());
candles.add(candle);
}
}
}
} catch (EncryptedDocumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return candles;
}
*/
/**
* 1 minuto = 1, 5 minutos = 2, 15 minutos = 3, 1 dia = 4
*/
private Timeframe resolveTipoTemporizador
(String sheetName
) {
if (sheetName ==
null) return null;
String name = sheetName.
toUpperCase(Locale.
ROOT);
if (name.
startsWith("1 MIN")) return Timeframe.
M1;
if (name.
startsWith("5 MIN")) return Timeframe.
M5;
if (name.
startsWith("15 MIN")) return Timeframe.
M15;
if (name.
startsWith("1 DIA")) return Timeframe.
D1;
return null;
}
private boolean isNumeric
(Cell cell
) {
if (cell ==
null) return false;
if (cell.
getCellType() == CellType.
NUMERIC.
getCode()) {
return true;
}
if (cell.
getCellType() == CellType.
STRING.
getCode()) {
try {
Double.
parseDouble(cell.
getStringCellValue().
replace(",",
"."));
return true;
} catch (NumberFormatException e
) {
return false;
}
}
return false;
}
/**
* Converte a célula de data/hora do Excel para LocalDateTime.
*/
private LocalDateTime getLocalDateTime
(Cell cell, DateTimeFormatter formatacaoDataTime
) {
if (cell ==
null) {
return null;
}
// Caso seja data/hora numérica do Excel
if (cell.
getCellType() == CellType.
NUMERIC.
getCode() && DateUtil.
isCellDateFormatted(cell
)) {
Date date = cell.
getDateCellValue(); // disponível em todas as versões
if (date ==
null) {
return null;
}
return date.
toInstant()
.
atZone(ZoneId.
systemDefault())
.
toLocalDateTime();
}
// Caso venha como TEXT (por exemplo num CSV importado)
if (cell.
getCellType() == CellType.
STRING.
getCode()) {
String text = cell.
getStringCellValue();
if (text ==
null || text.
trim().
isEmpty()) {
return null;
}
text = text.
trim();
try {
// Ajuste o pattern se seu Excel estiver em outro formato
return LocalDateTime.
parse(text, formatacaoDataTime
);
} catch (Exception e
) {
return null;
}
}
return null;
}
/**
* Converte a célula de data/hora do Excel para LocalDateTime.
*/
private LocalDateTime getLocalDateTime
(String cell, DateTimeFormatter formatacaoDataTime
) {
if (cell ==
null || cell.
trim().
isEmpty()) {
return null;
}
cell = cell.
trim();
try {
return LocalDateTime.
parse(cell, formatacaoDataTime
);
} catch (Exception e
) {
return null;
}
}
}