public List <ExcelJson> GetExcelFile(string filepath)
{
try
{
//create a list to hold all the values
List <string> excelData = new List <string>();
List <ExcelJson> newJson = new List <ExcelJson>();
ExcelJson exjs = new ExcelJson();
//read the Excel file as byte array
byte[] bin = File.ReadAllBytes(filepath);
//or if you use asp.net, get the relative path
//byte[] bin = File.ReadAllBytes(Server.MapPath("ExcelDemo.xlsx"));
//create a new Excel package in a memorystream
using (MemoryStream stream = new MemoryStream(bin))
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
//loop all worksheets
foreach (ExcelWorksheet worksheet in excelPackage.Workbook.Worksheets)
{
//loop all rows
for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++)
{
//loop all columns in a row
for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
{
//add the cell data to the List
//if (worksheet.Cells[i, j].Value != null)
//{
//excelData.Add(worksheet.Cells[i, j].Value.ToString());
newJson.Add(new ExcelJson {
Tweet = worksheet.Cells[i, j].Value.ToString(), Location = worksheet.Cells[i, j + 1].Value.ToString(), Amount = worksheet.Cells[i, j + 2].Value.ToString()
});
j += 2;
//}
}
}
}
}
//var jsonData = JsonConvert.SerializeObject(excelData);
return(newJson);
//return jsonData;
//return excelData;
}
catch (Exception)
{
throw;
}
}
private static void saveJson2File(JContainer jc, String fileName)
{
String saveFile = Path.Combine(Application.StartupPath, "json_save/" + fileName);
String saveDir = Path.GetDirectoryName(saveFile);
if (!Directory.Exists(saveDir))
{
Directory.CreateDirectory(saveDir);
}
String fileContent = jc.ToString();
using (StreamWriter tw = new StreamWriter(saveFile, false, Encoding.UTF8))
{
tw.Write(fileContent);
}
ExcelJson.ConvertJson2Excel(saveFile);
}
public ActionResult Visualize([FromQuery] string filename)
{
var fileName = $"{filename}.xlsx";
var filepath = $"ExcelFiles/{fileName}";
var excelToJson = _helper.GetExcelFile(filepath);
var countryStates = ReadJson();
//the node for showing my tweet and children
List <ExcelNodes> excelNodes = new List <ExcelNodes>();
List <ExcelNodesChild> excelNodeChild = new List <ExcelNodesChild>();
//the json with my tweets
List <ExcelJson> tweetJsons = new List <ExcelJson>();
foreach (var country in countryStates)
{
foreach (var state in country.States)
{
foreach (var tweet in excelToJson)
{
if (tweet.Location.Contains(","))
{
string[] loc = tweet.Location.Split(',');
if (loc[0] == state)
{
var newTweet = new ExcelJson()
{
Tweet = tweet.Tweet, Location = loc[0], Amount = tweet.Amount
};
tweetJsons.Add(newTweet);
}
}
}
var stateTweet = tweetJsons.Where(x => x.Location == state);
int total = 0;
if (stateTweet.Any())
{
foreach (var stt in stateTweet)
{
total += Convert.ToInt32(stt.Amount);
}
var newStateTweet = new ExcelNodesChild()
{
State = state, Country = country.Country, Total = total, ShowChildren = false, Children = stateTweet.ToList()
};
excelNodeChild.Add(newStateTweet);
}
}
var countryTweet = excelNodeChild.Where(x => x.Country == country.Country);
if (countryTweet.Any())
{
var newCountryTweet = new ExcelNodes()
{
Country = country.Country, Children = countryTweet.ToList(), ShowChildren = false
};
excelNodes.Add(newCountryTweet);
}
}
return(Ok(excelNodes));
}
