eta_data_analysis/services/kpler/excel.go

package kpler

import (
	"encoding/json"
	"fmt"
	"net/url"
	"regexp"
	"strings"

	"github.com/xuri/excelize/v2"
)

// ExcelData represents structured data extracted from an Excel file
type ExcelData struct {
	Headers     []string
	Rows        [][]string
	SheetName   string
}

// KplerFormulaData represents parsed data from a Kpler formula
type KplerFormulaData struct {
	Function    string
	Parameters  []string
	FullFormula string
}

// ParseExcel reads and parses data from an Excel file
func ParseExcel(filePath string) (*ExcelData, error) {
	// Open the Excel file
	f, err := excelize.OpenFile(filePath)
	if err != nil {
		return nil, fmt.Errorf("error opening Excel file: %w", err)
	}
	defer f.Close()

	// Get the first sheet by default
	sheetName := f.GetSheetList()[0]
	
	// Get all rows from the sheet
	rows, err := f.GetRows(sheetName)
	if err != nil {
		return nil, fmt.Errorf("error reading rows from sheet %s: %w", sheetName, err)
	}

	// Check if there's data
	if len(rows) == 0 {
		return nil, fmt.Errorf("no data found in sheet %s", sheetName)
	}

	// Create structured data
	excelData := &ExcelData{
		SheetName:    sheetName,
		Headers:      rows[0],
		Rows:         rows[1:],
	}

	return excelData, nil
}

// ParseKplerFormula extracts the function name and parameters from a Kpler Excel formula
func ParseKplerFormula(formula string) (*KplerFormulaData, error) {
	result := &KplerFormulaData{
		FullFormula: formula,
	}

	// Regular expression to match the function name and parameters
	// This pattern looks for: =@'path\to\file.xlam'!FunctionName(param1,param2,...)
	re := regexp.MustCompile(`=@'[^']*'!([A-Za-z0-9_]+)\((.*)\)`)
	matches := re.FindStringSubmatch(formula)
	
	if len(matches) < 3 {
		// Try another pattern without the @' prefix
		re = regexp.MustCompile(`=([A-Za-z0-9_]+)\((.*)\)`)
		matches = re.FindStringSubmatch(formula)
		
		if len(matches) < 3 {
			return nil, fmt.Errorf("could not parse Kpler formula: %s", formula)
		}
	}
	
	// Extract function name
	result.Function = matches[1]
	
	// Extract parameters
	paramsStr := matches[2]
	
	// Split parameters, handling commas inside quotes
	var params []string
	inQuote := false
	currentParam := ""
	
	for _, char := range paramsStr {
		switch char {
		case '"':
			inQuote = !inQuote
			currentParam += string(char)
		case ',':
			if inQuote {
				currentParam += string(char)
			} else {
				params = append(params, strings.TrimSpace(currentParam))
				currentParam = ""
			}
		default:
			currentParam += string(char)
		}
	}
	
	// Add the last parameter
	if currentParam != "" {
		params = append(params, strings.TrimSpace(currentParam))
	}
	
	result.Parameters = params
	return result, nil
}


// ScanSheetForFormulas scans an entire sheet for formulas
func ScanSheetForFormulas(filePath, sheetName string) (map[int]map[int]string, error) {
	// Open the Excel file
	f, err := excelize.OpenFile(filePath)
	if err != nil {
		return nil, fmt.Errorf("error opening Excel file: %w", err)
	}
	defer f.Close()

	formulas := make(map[int]map[int]string)
	
	// Get sheet dimensions
	dimension, err := f.GetSheetDimension(sheetName)
	if err != nil {
		return nil, fmt.Errorf("error getting sheet dimension: %w", err)
	}
	
	// Parse dimension to get the range (e.g., "A1:K42")
	parts := strings.Split(dimension, ":")
	if len(parts) != 2 {
		// Use a default range if dimension is not in expected format
		parts = []string{"A1", "Z100"}
	}
	
	// Extract the column letters and row numbers
	startCol, startRow, err := excelize.CellNameToCoordinates(parts[0])
	if err != nil {
		startCol, startRow = 1, 1
	}
	
	endCol, endRow, err := excelize.CellNameToCoordinates(parts[1])
	if err != nil {
		endCol, endRow = 26, 100 // Default to Z100
	}
	
	// Scan cells for formulas
	for row := startRow; row <= endRow; row++ {
		for col := startCol; col <= endCol; col++ {
			colName, err := excelize.ColumnNumberToName(col)
			if err != nil {
				continue
			}
			
			cellCoord := fmt.Sprintf("%s%d", colName, row)
			formula, err := f.GetCellFormula(sheetName, cellCoord)
			
			if err == nil && formula != "" {
				// Store all formulas or only Kpler-related ones (adjust as needed)
				//if strings.Contains(formula, "kpler") || strings.Contains(formula, "GetFlows") {
				if strings.Contains(formula, "GETFLOWS") {
					// fmt.Println("row: ", row)
					// fmt.Println("col: ", col)
					// fmt.Println("GetCellFormula: ", formula)
					if _, ok := formulas[row-1]; !ok {
						formulas[row-1] = make(map[int]string)
					}
					formulas[row-1][col-1] = formula
				}
			}
		}
	}
	
	return formulas, nil
}




// ProcessKplerData demonstrates how to parse and process Kpler crude flow data
func ProcessKplerData() error {
	// Path to the Kpler crude flow Excel file
	//filePath := "services/kpler/Kpler crude flow (自动保存的).xlsx"
	filePath := "services/kpler/最新版kpler插件.xlsx"
	// First, list all sheets in the Excel file
	// Open the Excel file
	f, err := excelize.OpenFile(filePath)
	if err != nil {
		return fmt.Errorf("error opening Excel file: %w", err)
	}
	// Return the list of sheets
	sheets := f.GetSheetList()
	if err != nil {
		return fmt.Errorf("error listing sheets: %w", err)
	}
	defer f.Close()
	
	fmt.Println("Available sheets:")
	for i, sheet := range sheets {
		fmt.Printf("%d. %s\n", i+1, sheet)
	}
	
	// Parse the Excel file using the first sheet (default)
	data, err := ParseExcel(filePath)
	if err != nil {
		return fmt.Errorf("error parsing Excel file: %w", err)
	}
	
	// Print the headers and a sample of data rows
	// fmt.Println("\nHeaders found in the sheet:")
	// for i, header := range data.Headers {
	// 	fmt.Printf("%d. %s\n", i+1, header)
	// }
	
	// Look for Kpler formulas
	fmt.Println("\nLooking for Kpler formulas across the sheet...")
	formulas, err := ScanSheetForFormulas(filePath, data.SheetName)
	if err != nil {
		fmt.Printf("Error scanning for formulas: %v\n", err)
	} else {
		fmt.Printf("Found %d formulas in the sheet.\n", len(formulas))
	}
	
	fmt.Println("\nSample data (first 5 rows):")
	rowCount := 5
	if len(data.Rows) < rowCount {
		rowCount = len(data.Rows)
	}
	
	for i := 0; i < rowCount; i++ {
		fmt.Printf("Row %d:\n", i+1)
		row := data.Rows[i]
		for j, cell := range row {
			// fmt.Println("i: ", i)
			// fmt.Println("j: ", j)
			// //fmt.Println("data.Headers[j]: ", data.Headers[j])
			// fmt.Println("cell: ", cell)
			if i == 1 {
				formula, ok:= formulas[i][j]
				if ok {
					fmt.Printf("  %s formula: %s\n", data.Headers[j], formula)
					// 解析公式
					ParseSpecificKplerFormulaV2(formula)
				}
			}
			if j < len(data.Headers) && cell != "" {
				fmt.Printf("  %s: %s\n", data.Headers[j], cell)
			}
		}
		fmt.Println()
	}
	
	// Print total number of data rows
	fmt.Printf("Total data rows: %d\n", len(data.Rows))
	
	return nil
}



// ParseSpecificKplerFormula parses the specific Kpler formula provided by the user
func ParseSpecificKplerFormulaV1() {
	// The specific formula provided in the user's question
	specificFormula := `=@'F:\Desktop\kpler\kpler-excel-addin.xlam'!GetFlows("China,","Russian Federation,Venezuela,Iran,EOPL,",,,,,"import","weekly","Total","kbd",FALSE,,FALSE,FALSE,FALSE,FALSE,TRUE,"CPP",)`
	
	fmt.Println("Analyzing the specific Kpler formula from the user's question:")
	fmt.Println(specificFormula)
	
	parsedFormula, err := ParseKplerFormula(specificFormula)
	if err != nil {
		fmt.Printf("Error parsing formula: %v\n", err)
		return
	}
	
	fmt.Println("\nFormula breakdown:")
	fmt.Printf("Function name: %s\n", parsedFormula.Function)
	fmt.Println("Parameters list:")
	
	// Define parameter names for GetFlows function based on Kpler documentation
	paramNames := []string{
		"fromZones",            // 1. "Saudi Arabia,"
		"toZones",              // 2. empty
		"fromInstallations",        // 3. empty
		"toInstallations",          // 4. empty
		"fromRegions",          // 5. empty
		"toRegions",            // 6. empty
		"flowDirection",        // 7. "import"
		"granularity",          // 8. "weekly"
		"split",                // 9. "Total"
		"unit",                 // 10. "kbd"
		"withForecast",            // 14. FALSE
		"products",
		"withProductEstimation",             // 15. FALSE
		"withIntraCountry",   // 16. FALSE
		"withIntraRegion",   // 17. TRUE
		"withFreightView",  // false
		"withPeriodEndTime", // false
		"productFilter",        // 18. "CPP"
		"lastDataPoints",       // 19. empty
	}
	// Print each parameter with its meaning
	for i, param := range parsedFormula.Parameters {
		if i < len(paramNames) {
			paramName := paramNames[i]
			if param == "" {
				fmt.Printf("  %s: [empty]\n", paramName)
			} else {
				fmt.Printf("  %s: %s\n", paramName, param)
			}
		} else {
			fmt.Printf("  Parameter %d: %s\n", i+1, param)
		}
	}

}

// Main function for standalone testing
func GetKplerDataByExcel() {
	//fmt.Println("Testing Kpler formula parsing...")
//	FormulaExample()
fmt.Println("Starting Kpler data processing...")
//ParseSpecificKplerFormulaV2()
// First demonstrate the specific formula parsing
// ParseSpecificKplerFormula()

// Then process the Excel data
err := ProcessKplerData()
if err != nil {
	fmt.Println("error processing Excel data: %w", err)
}

fmt.Println("Kpler data processing completed successfully!")
}

func ParseSpecificKplerFormulaV2(specificFormula string) {
	specificFormula = strings.ReplaceAll(specificFormula, `"&"`, "")
	//specificFormula := `=Kpler.getFlows("%7B%22platform%22%3A%22liquids%22%2C%22origins%22%3A%5B%7B%22id%22%3A%22Angola%22%2C%22name%22%3A%22Angola%22%7D%5D%2C%22destinations%22%3A%5B%5D%2C%22fromInstallations%22%3A%5B%5D%2C%22toInstallations%22%3A%5B%5D%2C%22flowDirection%22%3A%22export%22%2C%2" &amp; "2products%22%3A%5B%7B%22id%22%3A%22Crude%22%2C%22name%22%3A%22Crude%22%7D%5D%2C%22unit%22%3A%22kbd%22%2C%22isProductEstimation%22%3Afalse%2C%22isIntracountry%22%3Afalse%2C%22isIntraRegion%22%3Afalse%2C%22isWithForecast%22%3Afalse%2C%22granularity%22%3A%22" &amp; "weeks%22%2C%22vesselClassification%22%3A%22CPP%22%2C%22vesselsTypes%22%3A%5B%5D%2C%22split%22%3A%22Total%22%2C%22isFreightView%22%3Afalse%2C%22isWithPeriodEndTime%22%3Atrue%2C%22projection%22%3A%22actual%22%2C%22selectedPreset%22%3A%223m%22%2C%22startDate" &amp; "%22%3Anull%2C%22endDate%22%3Anull%7D")`
	// 吧函数的入参解析成json串，并转成结构体
	 // 手动解码URL编码字符串以获取JSON
  // 这是完整的公式参数 - Excel中使用"&"连接字符串
  //encodedParam := `%7B%22platform%22%3A%22liquids%22%2C%22origins%22%3A%5B%7B%22id%22%3A%22Angola%22%2C%22name%22%3A%22Angola%22%7D%5D%2C%22destinations%22%3A%5B%5D%2C%22fromInstallations%22%3A%5B%5D%2C%22toInstallations%22%3A%5B%5D%2C%22flowDirection%22%3A%22export%22%2C%22products%22%3A%5B%7B%22id%22%3A%22Crude%22%2C%22name%22%3A%22Crude%22%7D%5D%2C%22unit%22%3A%22kbd%22%2C%22isProductEstimation%22%3Afalse%2C%22isIntracountry%22%3Afalse%2C%22isIntraRegion%22%3Afalse%2C%22isWithForecast%22%3Afalse%2C%22granularity%22%3A%22weeks%22%2C%22vesselClassification%22%3A%22CPP%22%2C%22vesselsTypes%22%3A%5B%5D%2C%22split%22%3A%22Total%22%2C%22isFreightView%22%3Afalse%2C%22isWithPeriodEndTime%22%3Atrue%2C%22projection%22%3A%22actual%22%2C%22selectedPreset%22%3A%223m%22%2C%22startDate%22%3Anull%2C%22endDate%22%3Anull%7D`
  // 获取括号里的内容
  re := regexp.MustCompile(`\((.*)\)`)
  matches := re.FindStringSubmatch(specificFormula)
  if len(matches) < 2 {
    fmt.Println("没有找到括号里的内容")
    return
  }
  encodedParam := matches[1]
  fmt.Println("encodedParam: ", encodedParam)
  // 解码URL编码的字符串
  decodedStr, err := url.QueryUnescape(encodedParam)
  if err != nil {
    fmt.Printf("Error decoding URL: %v\n", err)
    return
  } else {
    // 打印解码后的JSON字符串
    fmt.Println("Decoded parameter JSON:")
    fmt.Println(decodedStr)
    
    // 使解码后的JSON更易读
    var jsonObj interface{}
    if err := json.Unmarshal([]byte(decodedStr), &jsonObj); err == nil {
      prettyJSON, _ := json.MarshalIndent(jsonObj, "", "  ")
      fmt.Println("\nPretty JSON format:")
      fmt.Println(string(prettyJSON))
    } else {
      fmt.Printf("Error parsing JSON: %v\n", err)
      return
    }
  }
}