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