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); }