public static string GetJsonFromTable(ExcelWorksheet ws, string tableName = null)
{
ExcelTable table;
if (string.IsNullOrEmpty(tableName))
{
table = ws.Tables.FirstOrDefault();
}
else
{
table = ws.Tables[tableName];
}
string json = string.Empty;
if (table != null)
{
var TableStartRow = table.Address.Start.Row;
var TableEndtRow = table.Address.End.Row;
var dictionaryList = new List <Dictionary <string, string> >();
for (int i = TableStartRow + 1; i <= TableEndtRow; i++)
{
var valuesDictionary = new Dictionary <string, string>();
for (int j = table.Address.Start.Column; j <= table.Address.End.Column; j++)
{
//var headingPosition = string.Concat(GetColumnName(j), TableStartRow);
var cellTitle = ws.Cells[string.Concat(ExcelHelpers.GetColumnName(j), TableStartRow)].Value;
//var cellName = string.Concat(GetColumnName(j), i);
var cellValue = ws.Cells[string.Concat(ExcelHelpers.GetColumnName(j), i)].Value;
if (j == table.Address.Start.Column)
{
valuesDictionary.Add("Id", i.ToString());
}
if (valuesDictionary.ContainsKey(cellTitle.ToString()))
{
valuesDictionary.Add($"{cellTitle}_{string.Concat(ExcelHelpers.GetColumnName(j), i)}", cellValue?.ToString());
}
else
{
valuesDictionary.Add(cellTitle.ToString(), cellValue?.ToString());
}
}
dictionaryList.Add(valuesDictionary);
}
json = Newtonsoft.Json.JsonConvert.SerializeObject(dictionaryList);
}
return(json);
}
public static Dictionary <string, string> GetColumnsNameAdress(IEnumerable <string> categories, ExcelWorksheet workSheet, string tableName, int row = 0)
{
var dictionary = new Dictionary <string, string>();
var addressDictionary = ExcelHelpers.GetTableStartAdress(workSheet, tableName);
if (addressDictionary.Any())
{
foreach (var item in categories)
{
int idx = ExcelHelpers.GetIndexFromColumnName(workSheet, addressDictionary["row"], item);
if (idx > 0)
{
dictionary.Add(item, $"'{workSheet.Name}'!{ExcelHelpers.GetColumnName(idx)}{addressDictionary["row"]}");
}
}
}
return(dictionary);
}
public static void AddExcelCellValue(int row, int column, object value, ExcelWorksheet wsSheet, Color?color = null)
{
var cellAddress = $"{ExcelHelpers.GetColumnName(column)}{row}";
AddExcelCellValue(cellAddress, value, wsSheet, color);
}
public static void AddExcelCellFormula(int row, int column, string formula, ExcelWorksheet wsSheet, Color?color = null)
{
var cellAddress = $"{ExcelHelpers.GetColumnName(column)}{row}";
AddExcelCellFormula(cellAddress, formula, wsSheet, color);
}