/// <summary> /// Save current excel-Sheet as Json-File. /// </summary> /// <param name="control"></param> public void OnButtonPressed_SaveAsJson(IRibbonControl control) { this._app = (Excel.Application)ExcelDnaUtil.Application; this._activeWorksheet = (Excel.Worksheet) this._app.ActiveWorkbook.ActiveSheet; SaveFileDialog saveFileDialog = new SaveFileDialog { InitialDirectory = this._lastPath, Filter = "Json files (*.json)|*.json|txt files (*.txt)|*.txt|All files (*.*)|*.*", FilterIndex = 1, }; if (saveFileDialog.ShowDialog() == DialogResult.OK) { Branch root = null; try { root = ExcelJsonShow.ParseSheetToBranch(this._activeWorksheet); } catch (Exception ex) { MessageBox.Show("Error: Could not parse Data. File not saved. Original error: " + ex.Message, "Export", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (root == null || !root.Children.Any()) { MessageBox.Show("No Data", "Export", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } string jsonText = root.ToJsonString(); if (String.IsNullOrWhiteSpace(jsonText)) { MessageBox.Show("Error: No Data to parse. No File was saved.", "Export", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } try { JObject o = JObject.Parse(jsonText); File.WriteAllText(saveFileDialog.FileName, o.ToString()); this._lastPath = Path.GetDirectoryName(saveFileDialog.FileName); } catch (Exception ex) { MessageBox.Show("Error: Could not save file to disk. Original error: " + ex.Message, "Export", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
/// <summary> /// Parse current Excel-Sheet and reprint this on current Excel-Sheet. /// </summary> /// <param name="control"></param> public void OnButtonPressed_ParseToJson(IRibbonControl control) { this._app = (Excel.Application)ExcelDnaUtil.Application; this._activeWorksheet = (Excel.Worksheet) this._app.ActiveWorkbook.ActiveSheet; Branch root = null; try { root = ExcelJsonShow.ParseSheetToBranch(this._activeWorksheet); } catch (Exception ex) { MessageBox.Show("Error: Could not parse Data. Original error: " + ex.Message, "Parse", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } string jsonText = root.ToJsonString(); if (String.IsNullOrWhiteSpace(jsonText)) { MessageBox.Show("Error: No Data to parse.", "Parse", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } try { JObject jsonObj = JObject.Parse(jsonText); this._gapToShowValuesInSameColumn = jsonObj.FindMaxDepth(); int rowNumber = 1; foreach (KeyValuePair <string, JToken> jsonToken in jsonObj) { rowNumber = ExcelJsonShow.ShowTokenInSheet(jsonToken.Value, rowNumber, 2, new List <string> { jsonToken.Key }, this._activeWorksheet, this._gapToShowValuesInSameColumn); } } catch (Exception ex) { MessageBox.Show("Error: Could not Show Json in Excel: " + ex.Message, "Parse", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } }
/// <summary> /// Load Json-File and show there Data in current Excel-Sheet. /// </summary> /// <param name="control"></param> public void OnButtonPressed_LoadJsonFile(IRibbonControl control) { OpenFileDialog openFileDialog1 = new OpenFileDialog { InitialDirectory = this._lastPath, Filter = "Json files (*.json)|*.json|txt files (*.txt)|*.txt|All files (*.*)|*.*", FilterIndex = 1, RestoreDirectory = true, Multiselect = false, Title = "Select a file to import" }; this._app = (Excel.Application)ExcelDnaUtil.Application; this._activeWorksheet = (Excel.Worksheet) this._app.ActiveWorkbook.ActiveSheet; if (openFileDialog1.ShowDialog() == DialogResult.OK) { JObject jsonObj = null; try { jsonObj = JsonExcelLib.LoadJsonToJObject(openFileDialog1.FileName); this._lastPath = Path.GetDirectoryName(openFileDialog1.FileName); } catch (Exception ex) { MessageBox.Show(ex.Message, "Import", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } this._gapToShowValuesInSameColumn = jsonObj.FindMaxDepth(); int rowNumber = 1; foreach (KeyValuePair <string, JToken> jsonToken in jsonObj) { rowNumber = ExcelJsonShow.ShowTokenInSheet(jsonToken.Value, rowNumber, 2, new List <string> { jsonToken.Key }, this._activeWorksheet, this._gapToShowValuesInSameColumn); } } }