using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using ClosedXML.Excel;

public class ExcelReader
{
    public delegate void OnRow(int iRow, Dictionary<string, string> rowData);
	public static void ReadSheet(string excelFilePath,string sheetName,OnRow onRow)
	{
		XLWorkbook xlWorkBook = new XLWorkbook(excelFilePath);
		IXLWorksheet workSheet;
		bool got = xlWorkBook.Worksheets.TryGetWorksheet(sheetName, out workSheet);
		IXLRow firstRow = workSheet.FirstRow();
		int cellCount = firstRow.CellCount();
        List<string> columnNames = new List<string>();
		for (int iCell = 1; iCell <= cellCount; ++iCell)
		{
			IXLCell cell = firstRow.Cell(iCell);
			string cellValue = cell.GetString();
			//Debug.Log($"Row:1,Column:{iCell},Value:{cellValue}");
            columnNames.Add(cellValue);
            if (cellValue == "") break;
		}
        int rowCount = workSheet.RowCount();
        for (int iRow = 2; iRow < rowCount; ++iRow)
        {
            //Debug.Log($"iRow:{iRow}");
            Dictionary<string, string> rowData = new Dictionary<string, string>();
            IXLRow row = workSheet.Row(iRow);
            cellCount = columnNames.Count;
            bool rowAllEmpty = true;
            for (int iCell = 1; iCell <= cellCount; ++iCell)
            {
                IXLCell cell = row.Cell(iCell);
                string cellValue = cell.GetString();
                rowData[columnNames[iCell - 1]] = cellValue;
                if (cellValue != "") rowAllEmpty = false;
            }
            if (rowAllEmpty) break;
            onRow(iRow,rowData);
        }
    }
}
